SQL execution steps in Oracle.

Below are the steps which are involved in when a SQL statement executes in Oracle.
Syntax Checking Phase

• Whether the all keywords present "select . . . from", etc . .
• DB semantic check against the data dictionary of the database.
• Whether the used table names spelled correctly, and present in db dictionary.

Parsing Phase

• Parse call does not return an error if the statement is not syntactically correct.
• Creation of all possible ways of query execution with different costs as per CBO and identifying and generation of query execution plan with lowest cost.
• This step uses database client/server cache, SGA, db datafiles, etc. all possible places from where data transition occurs.
• This is where the table - tablespace - datafile translation occurs.
• Once the execution plan is created, it is stored in Shared pool - library cache to facilitate re-execution. There are two types of parses:

Hard parse :

• A new SQL statement must be parsed from scratch. 
• Parsing can be a very expensive operation that takes a lot of resources to execute, when there is no previously parsed version of the SQL to reuse.

Soft parse :

• A reused SQL statement where the only unique feature are host variables.
• The best-case scenario is a parse to execute ratio of 100% which would indicates that the application if fully using bind or host variables in it.
• It’s like parses SQL once and executes many times.

Bind Phase

• In this phase, Binding lowest costs execution plan in db.
• Once the execution plan is syntactically created, Oracle gathers the parameters from the client/application for the execution. It makes the addresses of the program/host/bind variables are known to Oracle.

Execute Phase

• During the execute phase, Oracle executes the statement, reports any possible errors, and if everything normal, displays the result set. Unless the SQL statement being executed successfully, this is the last step of the  query execution.

Define Phase

• The Oracle OCI interface defines addresses of the output variables known to the Oracle process in order to make it possible to the fetch call to know where to put the output variables.

Fetch Phase

• During the fetch phase, Oracle displays the resultset to the application.

Once more, the define and fetch phases are relevant for queries only.The Oracle OCI interface module contain calls to facilitate each of those phases.

Unix - AWK Command by examples. Part 2

In this part, we will learn on AWK Built in Variables:

FS - Input field separator variable:

By default, AWK assumes that fields in a file are separated by space.
If the fields in the file are separated by any other column separator, we can use the FS variable for that.

Example 5. awk 'BEGIN {FS=":"} {print $2}' test_file
OR
awk -F: '{print $2} test_file

output will be as below.

39 AAAA
15 BBBB
39 EEEE
39 GGGG

We can use -F option for to change column separator in command to display output.

awk -F: '{if ($2 == "") print $1 ": no password!"}'

We can easily move from one column separator to another column separator using awk.

#!/bin/awk -f
{
if ($1 == "#START") {
FS=":";
} else if ($1 == "#STOP") {
FS=" ";
} else {
#print the Roman number in column 3
print $3
}

}


OFS - Output field separator variable: 

Example 6. awk '{print $4,$5}' test_file

When we use print statement in awk, it uses default column seperator while displaying result.

The output of this command will be
unixrun 0
unixrun 175
unixrun 430
unixrun 428

We can change this default column seperator by using the OFS variable as

awk 'BEGIN {OFS=":"} {print $4,$5}' test_file

output will be as below.

unixrun:0
unixrun:175
unixrun:430
unixrun:428

If I run same command in this way, it will not work same as earlier, Here no column separator showing in output.

awk 'BEGIN {OFS=":"} {print $4$5}' test_file

unixrun0
unixrun175
unixrun430
unixrun428

NF - Number of fields variable:

The NF used to get the number of fields in line of a file.

Example 7. awk '{print NF}' test_file

This will display the number of columns in each row, output as below.

9
9
9
9

NR - number of records variable: 

The NR used to get the line number or no of lines in the file.

Example 8. awk '{print NR}' test_file

This will display the line numbers from 1.

1

Example 9. awk 'END {print NR}' test_file

This will display the total number of lines in the file, here output will be.

4

FILENAME - The Current Filename Variable

The last variable known to regular AWK is "FILENAME", which tells you the name of the file.

#!/bin/awk -f
# reports which file is being read
BEGIN {
f="";
}
{ if (f != FILENAME) {
print "reading", FILENAME;
f=FILENAME;
}
print;
}