Prerequisites for SQL Tuning

You will need to know the following to make use of this article

• Familiarity with database architecture: This means how oracle stores data, how it retrieves the data and how it produces the result set.
• Knowledge of SQL : what tables to use, when to join each table and how to join tables to get  fast results. For Example if you were to search on a date filed and it takes too much to return data, perhaps  you should index the column or maybe partition the table.
• Familiarity with SQL  tuning tools : SQL tuning tools help to identify what is wrong with the SQL. Typical tools used include TKPROF, explain plan and some other utilities.

Lets take a look at sql processing

The diagram above explains what happens when you issue a sql query.

Step 1  SQL Syntax: Check the syntax of the SQL statement is well formed.

Step 2: Semantic Check:  Check weather the SQL statement is valid, which means weather the columns and tables specified in the query is valid.

Step 3 Shared Pool Check:  Check is a similar SQL statement had been issued before, If so then directly execute the statement.

Step 4 Optimization : If it does not find a similar statement then it runs through query optimization, which means generation of multiple execution plans.

Step 5 Row Source Generation :  Based on the  multiple query execution plans it comes out with an optimal plan

step 6 Execution : Once the optimal plan is generated, it executes the plan.

Hard Parse Vs Soft Parse (see image above)

Soft Parse

During this parse, the database performs a shared pool check to see if an existing parsed statement already exist and then determines whether it can  skip resource  intensive steps of statement processing.

Hard Parse

If Oracle database cannot reuse existing code , then it must build a new executable version of the application code. This operation is called a hard parse

For all your application development needs, visit www.verbat.com for a fiscally conscious proposal that meets your needs ( So I can keep this blog going as well!!!!)

Alternatively click through the link   if you found this article interesting. (This will help the companies Search engine rankings)