Important Topics in Tuning:
- SQL Optimizers
- Tuning Utilities
- Basic Performance Tuning
What happens when a SQL is executed?
A component of the database known as the optimizer must decide how best to access the data operated on by that statement.
Oracle supports two Optimizers:
- Rule-Based Optimizer : Execution plans are determined by order of ranks.
- Cost-Based Optimizer : Execution plans are determined on the basis of available resources and relative cost.
Optimizer Consider Followings:
- The syntax you’ve specified for the statement
- Any conditions that the data must satisfy (the WHERE clauses)
- The database tables your statement will need to access
- All possible indexes that can be used in retrieving data from the table
- The Oracle RDBMS version
- The current optimizer mode
- SQL statement hints
- All available object statistics (generated via the ANALYZE command)
- The physical table location
- INIT.ORA settings
Best of two Optimizers:
- Rule based is not advisable because there can be cases which may not be captured in the rules.
- RBO used only for applications from version 6 migrated to latest oracle versions.
- Cost based is the optimum one because the optimizer will decide the best execution path based on the cost of resources available.
Setting up Optimizer
- Instance – Set the initialization parameter OPTIMIZER_MODE = CHOOSE/RULE Session
Use the SQL command
- Alter session set Optimizer_Mode = CHOOSE/RULE/ALL_ROWS/FIRST_ROWS Query – Specify some special hints to the query
Introduction to Hints:
- Hints are suggestions that you give the optimizer for optimizing a SQL statement.
- Use hints to force the optimizer to use a specific chosen execution plan.
- Hints supersede the session and instance level Settings.
Hints for Optimization
Approaches and Goals
Hints for Access Methods
- HASH / HASH_AJ
- INDEX / INDEX_ASC
- INDEX_DESC / INDEX_FFS
Introduction to Tuning:
- Performance gain comes from tuning the applications communicating to database.
- Tuning the database begins after all of the SQL statements have been checked and tuned.
- To help in tuning, Oracle provides several tools and scripts that can evaluate individual components or entire system.
- EXPLAIN PLAN – A utility that displays the execution plan for a SQL statement
- SQLTRACE & TKPROF – These utilities that can monitor and report on database performance when executing one or more queries.
- SQLTRACE is used to gather statistics.
- TKPROF is a reporting tool that formats the output.
Introduction to EXPLAIN PLAN:
- EXPLAIN PLAN can be run by either the programmers or the DBAs.
- Use EXPLAIN PLAN to determine the most effective way to write queries and decide whether to index certain columns or use clusters.
- When running EXPLAIN PLAN, there are no calls to the database and no data is retrieved; Oracle simply tells you how the statement would be processed if it were run.
Output of EXPLAIN PLAN
- The type of query processed:SELECT, INSERT, UPDATE, or DELETE
- The cost assigned by the CBO if it is in use.
- The steps that are necessary to return the data.
- The internal operations that were performed for each step.
- The object accessed for each step.
EXPLAIN PLAN : Method 1
Create a plan table using the script utlxplan.sql.
Run the sql with explain plan
SQL> EXPLAIN PLAN
2 set statement_id=’test’ for
3 select * from Customer;
DISPLAYING THE EXECUTION PLAN
column “Query Plan” format a60;
select id, lpad(‘ ‘,2*level) ||
as “Query Plan“ from plan_table
EXPLAIN PLAN : Method 2
In the SQL Prompt Set AUTOTRACE on.
SQL> set autotrace on;
Now executing any query will show the Execution plan and Statistics.
NOTE :For autotrace on option plan_table has to be created by $ORACLE_HOME/rdbms/admin/utlxplan.sql and plustrace role has to be grant to the user.PLUSTRACE role can be created by $ORACLE_HOME/sqlplus/admin/plustrace.sql.
When SQL TRACE is set to true, oracle writes the execution details of all queries to a file for the session till SQL Trace is again set to false. This trace file will have all details as to the physical reads, I/O accesses, CPU time utilised, number of records processed, Total time taken for a query etc..
Start SQL Trace
SQL> ALTER SESSION SET SQL_TRACE = TRUE
Now execute all the required queries
Stop SQL Trace
SQL> ALTER SESSION SET SQL_TRACE = FALSE
l Identify the directory where the Trace file is generated
SQL> SELECT value
WHERE name = ‘user_dump_dest’;
l The path obtained above is a relative path from ORACLE_HOME
l The SQL Trace output is in hexadecimals codes and so is unreadable. For this we use a utility called TKPROF
l At O/S prompt
Tkprof <trace file path & name> <Output file path & name> explain plan <userid>/<password>
l This generates the Output in a readable format
Basic Performance Tuning
- On indexed column IS NULL or IS NOT NULL should be avoided.
- Similarly functions/ operators /calculations on an indexed column should be avoided.
- Use UNION instead of OR.
- DECODE minimizes the number of times a table has to be selected.
- Use joins in preference to subqueries.
- Use EXISTS in preference to Count(*) to find the existence of record.
- Avoid the use of HAVING; in general, use WHERE predicates instead.
- If a query is going to read most of the records in a table (more than 60%), use a full table scan.
- Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
- When using more than one table in a query, use table alias for each and every column in the select statement. A column may be in only one table in the query, but still use table alias. This will reduce parse time while executing the query. In addition, if in the future, a column with the same name is added to the other table then this query will with the message column ambiguously defined.