SQL Tuning Techniques

Tuning has always been a hard nut to crack for Oracle DBA's. Every DBA who had spent time dealing with databases would have faced situations when he got complaints about slowness in the database or most of the time a query which was running fine in the past but has been degraded in performance. It is not always advisable to extract the AWR or statspack report and go through each section for digging into such issues. Oracle has provided some very useful database views which can help the administrators in such situations. Below are some steps and queries making use of these views which can help administrators to find the cause of the slowness in the database.

Rather than checking the full AWR report always identify the bottle necks and try fixing those.

Check for Active Wait Events in the Database.

select event,count(*) from  gv$session where event not like 'Streams%' and wait_class <> 'Idle' group by event order by 2;

Find the most executed sql’s which are waiting on active wait events.

select sql_id,sql_hash_value,count(*) from gv$session where event='&ev' group by sql_id,sql_hash_value order by 3;

Check the Gets/Execution for all the plan hash values of the problematic sql_id.

select PARSING_SCHEMA_NAME,BUFFER_GETS , EXECUTIONS , BUFFER_GETS/EXECUTIONS, ROWS_PROCESSED ,ROWS_PROCESSED/EXECUTIONS,SQL_PROFILE,CHILD_NUMBER,PLAN_HASH_VALUE from v$sql where sql_id='&sql_id' and EXECUTIONS<>0;

 If the result of above query shows more than one plan hash value for the same sql_id, find for which plan hash value the gets/execution is low. Get the history of the SQL plans generated for the SQL_ID. Find if the SQL plan is recently changed for the sql_id and if the changed plan is poor than the earlier plan.

Find different plan's for a SQL

select distinct plan_hash_value,timestamp from v$sql_plan where sql_id='&sqlid';

In case current plan is not giving the desired performance, check the output of the above query and also check with user about when the SQL was performing well. SQL profile using the good plan_hash_value can be created for sql_id so that it uses that good plan.

Steps to implement SQL Profile

•  If sql is in AWR.

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text      clob;
begin
select
extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint'  passing ( select xmltype(other_xml) as xmlval from dba_hist_sql_plan
where sql_id = '&&1' and     plan_hash_value = &&2 and  other_xml is not null)) d;

select sql_text into cl_sql_text from dba_hist_sqltext where sql_id = '&&1';

dbms_sqltune.import_sql_profile(sql_text    => cl_sql_text, profile     => ar_profile_hints
, category    => '&&3', name  => 'PROFILE_&&1' 
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&4
);
end;
/ 
============================================
input
==========

Enter value for 1: 6k4j4r9k777h6 (sql_id) 
Enter value for 2: 2421233948 (plan_hash_value) 
Enter value for 3:(default Category) 
Enter value for 4: true (force match)
================

• If the sql is in cursor(recently executed).

declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text      clob;
begin
select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from
xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from v$sql_plan
where sql_id = '&&1' and     child_number = &&2 and     other_xml is not null)) d;

select sql_fulltext into cl_sql_text from v$sql where sql_id = '&&1' and      child_number = &&2;

dbms_sqltune.import_sql_profile(
sql_text    => cl_sql_text
, profile     => ar_profile_hints
, category    => '&&3'
, name        => 'PROFILE_&&1'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&4
);
end;
/
/*============================================
--input
==========

--Enter value for 1: 6k4j4r9k777h6 (sql_id) 
Enter value for 2: 2421233948 (plan_hash_value) 
Enter value for 3: (default Category) 
Enter value for 4: true (force match) ================*/

•  Measure the performance after profile implementation and check if the profile is being used.

select PARSING_SCHEMA_NAME,BUFFER_GETS , EXECUTIONS , BUFFER_GETS/EXECUTIONS, ROWS_PROCESSED ,ROWS_PROCESSED/EXECUTIONS,SQL_PROFILE,CHILD_NUMBER,PLAN_HASH_VALUE from v$sql where sql_id='&sql_id' and EXECUTIONS<>0

• If performance has improved, keep the new profile, else profile can be dropped.

select name , sql_text from dba_sql_profiles where name like '&sqlid';

exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name => '&1');