Oracle With Examples : SQL tuning in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site  -> Oracle DBA -> Oracle Performance Tuning

-> SQL tuning in Oracle


 SQL tuning in Oracle



This article treat the following items:

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. 


SQL Tuning


     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.    



Tips for writing efficient SQL statements 


     Here are some tips for writing efficient SQL statements:


      1. Use WHERE instead of HAVING for record filtering



                         SELECT DEPTNO,


                           FROM EMP

                         WHERE DEPTNO = 100

                    GROUP BY DEPTNO;



                             SELECT DEPTNO,


                           FROM EMP

                    GROUP BY DEPTNO

                        HAVING DEPTNO = 10;


     2. Use SQL Analytic functions for reporting SQL statements. Here is a very good example:


     3.  Take advantage of the Oracle parallel query option. Parallel query option should be used only for systems containing multiple disk drives.


        alter table emp1 parallel 4;  (to restore to "noparallel" : alter table emp1 noparallel; )

        update /*= PARALLEL (emp1, 4)*/ emp1 set sal = 2555;


     4. Avoid the LIKE predicate. If possible replace a "like" with an equality, when appropriate because the LIKE predicate force a full table scan.


    5. Avoid putting a column as an argument to a function in a WHERE clause. This will force a full table scan.


    6. Using the WITH clause to simplify complex SQL queries.

        Here is an example of query which return the employees which have the salary bigger then the department average they work in:


                    WITH Average_Salary AS

                                     (SELECT deptno, avg(sal) Average_Sal FROM EMP group by deptno)

                    SELECT e.empno, e.ename, e.sal

                       FROM EMP e, Average_Salary

                     WHERE Average_Salary.deptno = e.deptno

                          AND e.sal > Average_Sal;


                 7. Run the select bellow to find the top intensive I/O SQL statement:  


                           SELECT executions,

                                           disk_reads             physical_reads,



                    FROM v$sqlarea

                    ORDER BY 2 desc;  -> Oracle DBA -> Oracle Performance Tuning

-> SQL tuning in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...


     Copyright (c) 2018  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.