SQL tuning with real time project example, Learn whom to engage and how to engage. Understand what is in scope of a developer and what should falls in DBAs bucket. Most of time, I have seen that SQL tuning efforts goes waste because generally innocent developer doesn't even aware that DBA has to be engaged. They feel that SQL Tuning is there task because DBA tells them that we don't do development we just support DB system. This is not a truth.
One of the most common issues that any application face is its SQL statement or PL/SQL programs (if any). How should you identify and tune SQL statements in your project. In a different way, if you have given a task to tune your application / project. How you can identify and target the heavy loaded SQLs or in simple words SQLs subject to SQL Tuning.
How to Identify the Long running SQLs in your project for SQL Tuning ?
- Your DBA should be able to provide you the resource killing SQL / programs in case you have a integrated DBA environment.
- But if your DBA is cranky he will say that there are thousands of queries run at a time and i don't have time to give you that list and if you still need it get me the some big manager's approval for me to give you that. Moreover for your application you can see it from your log as well why you want me to give you that.
- No Problem we have solution for cranky DBA as well. Request him to execute below queries and to share the result so you can get SQL subject to SQL Tuning.
- If not, identify your target statement for tuning with below queries. Below query can be used to identify tasks subject to SQL Tuning.
SELECT a.sql_text,TRUNC(a.disk_reads / decode(a.executions, 0, 1, a.executions)) reads_per_execution,a.buffer_gets, a.disk_reads,a.executions, a.sorts, a.address
FROM v$sqlarea a
WHERE A.PARSING_SCHEMA_NAME = 'USD'
ORDER BY 2 DESC
SELECT P.*, SA.sql_text,TRUNC(SA.disk_reads / decode(SA.executions, 0, 1, SA.executions)) reads_per_execution,SA.buffer_gets, SA.disk_reads, SA.executions, SA.sorts, SA.address
FROM V$SQL_PLAN_MONITOR P, V$SQLAREA SA
WHERE P.SQL_ID = SA.SQL_ID
AND SA.PARSING_SCHEMA_NAME = 'USD'
SELECT S.sql_text,TRUNC(S.disk_reads / decode(S.executions, 0, 1, S.executions)) reads_per_execution,S.buffer_gets, S.disk_reads,S.executions, S.sorts
FROM V$SQLSTATS S
ORDER BY 2 DESC
How to Identify Performance bottleneck in those SQLs to consider them for SQL Tuning
- Identify indices on tables and check them.
- Check if there is any view referred in the SQL, check its definition too.
- Check execution plan for the statement.
How to use Explain Plan:
(Note: Execution plan snapshot below is not the result of below query, I added a different plan to discuss few optimization cases pertaining it.)
EXPLAIN PLAN SET statement_id = 'TESTEMP'
INTO plan_table FOR –EXPLAIN PLAN FOR
SELECT e.deptno, d.dname, d.loc,
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY e.deptno, d.dname, d.loc
Check the execution plan, Identify the need of index
Ensure Below for good execution plan or better performance
- Full table scan – Ideal for huge data, small table.
- i. Large amount of data is being reported- okay.
- ii. Small set of data from a table is active- Might require an index.
- Transforming columns to be get to minimum.
- i. For example, using unnecessary string transformation can make the query expensive.
- Consider partitioning of tables if not already in place.
- Use appropriate Optimizer hints to change default execution plan.
- i. Index hints to force Optimizer to apply index scan.
- ii. Join Order, Merge hints – Leading, Merge, USE_NL, USE_NL_WITH_INDX etc.
- Sub queries can be replaced with Joins (However oracle optimizer does it automatically during execution but it’s a good point to discuss in developing a optimized SQL statement).
- Few other points to be taken care of – No TABLE.*, using WHERE over HAVING clause, EXIST over IN etc etc.