SQL Statements Tuning

Oracle Optimizer

     The Oracle Optimizer is an "engine" running in the database that is dedicated to creating a list of execution paths based on various conditions and then choosing the most efficient for running a query. The most efficient execution path will be chosen in function of the CPU utilization, hard disk I/O, memory consumption. However these values are not known, but estimated. 

     To estimated these values and to find "the best" execution plan Oracle Optimizer could use:

•  a set of rules (Rule Based Optimizer  (RBO)) :  The statistics are not necessary, the best execution plan is the one which "theoretically" is the best. For instance, if a query on a table (that has two columns) is searching for the exact match  in the where clause condition (one column being the primary key and the other column being a non-unique column), the RBO will prefer using the primary key. RBO was the preferred choice for most setups in earlier Oracle database releases (7, 8, 8i, 9i) as the execution paths were consistent and uniform. Queries would behave the same way if run on different databases of the same application.

•  the lower cost (Cost Based Optimizer  (CBO)) : All the estimations are based on statistics and having good statistics is essential for CBO. 2 identical databases with different statistics could have different execution plans for the same statement. The CBO is used by default in Oracle 10g. CBO has two available modes in which to run: ALL_ROWS (the execution plan with  minimal use of resources and best throughput is used) and FIRST_ROWS ( in this mode the response time has the prime importance).      

     NOTE: OPTIMIZER_MODE initialization parameter set the Optimizer to be RBO or CBO. The Oracle 10g is set to CBO by default.

Avoid unnecessary large table full scan

     One of the biggest performance issues is because the indexes are nor used. To find if a SQL statement use the indexes or not, the explain plan command could be used to put all the information on the execution plan into PLAN_TABLE table. This information could be seen by running the "select * from table(dbms_xplan.display);" command or by running utlxpls.sql script. 

     In this picture we can see a full table scan on the EMP1 table. To avoid this, an index must be added on the EMPNO column. 

Using a coding methodology

     If the SQL (or PL/SQL) statement is found in the Library Cache, the parsing phase is bypassed. However, to be found in the Library Cache, the statement must be identical (down to the number of spaces, tabs, capital or small letters) with another one which run before. For this reason, to improve the Library Cache hit ratio all the developers must use the same coding methodology. For instance all the key words must be in capitals and the tabulation must be identical. 

Using materialized views for remote big tables

     In some cases when a table join is done on a local database, the join could be used a huge table (supposing named A) from a remote database. In this case the huge table A will be ftp on the local database for the join and no indexes are used for this table. Perhaps we need only 10 rows from this (supposing) 50 million rows table; and for 10 rows the network traffic will increase, the local database will use more memory for the join and also the response time will be much bigger. Sometimes, the response time is too big or the memory (TEMP tablespace) is not big enough for the join and the fix is to use a materialized view on a local database for the remote table.    

More information about  this subject ( SQL Statements Tuning ) you can get from  www.in-oracle.com