|Oracle With Examples : SQL tuning in Oracle|
-> SQL tuning in Oracle
SQL tuning in Oracle
This article treat the following items:
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:
NOTE: OPTIMIZER_MODE initialization parameter set the Optimizer to be RBO or CBO. The Oracle 10g is set to CBO by default.
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.
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.
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.
Here are some tips for writing efficient SQL statements:
1. Use WHERE instead of HAVING for record filtering
WHEREDEPTNO = 100
HAVINGDEPTNO = 10;
2. Use SQL Analytic functions for reporting SQL statements. Here is a very good example: http://www.orafaq.com/node/56
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
FROMEMP e, Average_Salary
WHEREAverage_Salary.deptno = e.deptno
ANDe.sal > Average_Sal;
7. Run the select bellow to find the top intensive I/O SQL statement:
ORDER BY 2 desc;
-> SQL tuning in Oracle
Copyright (c) 2018 www.oracle-with-examples.com | 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.