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)
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.
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)