Tune Bad SQL Using SQLT
We are getting a good plan and bad plan for a sql. In production we have bad plan and getting very bad result so we are planning to export good plan from non production to production.
SQL history of SQL ID : 7yq9pgtsundq0
col begin_time for a25
col end_time for a11
col inst for 99999
col snapid for 999999
set lines 200
set pages 20000
select snap_id snapid,
(select substr(BEGIN_INTERVAL_TIME,1,18)||' '||substr(BEGIN_INTERVAL_TIME,24,2) from dba_hist_snapshot
b where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) begin_time
,(select substr(end_INTERVAL_TIME,11,8)||' '||substr(end_INTERVAL_TIME,24,2) from dba_hist_snapshot b
where b.snap_id=a.snap_id and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER) end_time
,INSTANCE_NUMBER inst , PLAN_HASH_VALUE,
EXECUTIONS_DELTA Executions,
ROWS_PROCESSED_DELTA rows1,
round( CPU_TIME_DELTA /1000000,0) cpu_time,round(IOWAIT_DELTA /1000000,0) io_wait,
round( ELAPSED_TIME_DELTA /1000000,0) elapsed
from sys.wrh$_sqlstat a where sql_id in('7yq9pgtsundq0')
order by snap_id, INSTANCE_NUMBER;
Above query will help you to get a good plan and bad plan from the history with respect to the SNAP_ID
Example
SNAPID
BEGIN_TIME END_TIME INST PLAN_HASH_VALUE EXECUTIONS ROWS1 CPU_TIME IO_WAIT
ELAPSED
------- ------------------------- ----------- ------
--------------- ---------- ---------- ---------- ---------- ----------
65849 16-FEB-16 09.00.07 AM
10.00.27 AM 1 857637189 9513 9512 2501 2 2539 =====
65850 16-FEB-16 10.00.27 AM
11.00.48 AM 1 857637189 13378 13378 3469 0 3519
65851 16-FEB-16 11.00.48 AM
12.00.08 PM 1 857637189 2568 2569 649 0 660
65855 16-FEB-16 03.00.10 PM 04.00.31
PM 1 857637189 3986 3985 1010 0 1023
65856 16-FEB-16 04.00.31 PM
05.00.51 PM 1 857637189 6042 6043 1520 0 1539 BAD PLAN
65875 17-FEB-16 10.58.22 AM
11.15.53 AM 1 1673898079 11271 11271 928 4 947
65876 17-FEB-16 11.15.53 AM
11.30.58 AM 1 1673898079 10955 10955 883 0 896
65877 17-FEB-16 11.30.58 AM
11.45.03 AM 1 1673898079 10207 10207 824 0 836
65878 17-FEB-16 11.45.03 AM
12.00.08 PM 1 1673898079 11115 11115 882 0 896
65879 17-FEB-16 12.00.08 PM
12.06.49 PM 1 1673898079 4029 4030 318 0 321 ======
65880 17-FEB-16 12.06.49 PM
12.15.13 PM 1 1749449121 349619 349619 12 0 12 ======
65881 17-FEB-16 12.15.13 PM
12.30.18 PM 1 1749449121 997986 997986 34 0 34
65885 17-FEB-16 01.15.34 PM
01.30.39 PM 1 1749449121 690501 690501 23 0 25 Good Plan
65886 17-FEB-16 01.30.39 PM
01.45.48 PM 1 1749449121 657170 657170 22 0 22
65894 17-FEB-16 03.30.28 PM
05.00.58 PM 1 1749449121 1347708 1347708 44 0 46
65901 17-FEB-16 11.00.03 PM
12.00.26 AM 1 1749449121 1347765 1347765 44 0 44 ======
From the above SQL history we identified “1749449121” is a Good plan and “1673898079” is a bad plan.
Look at the CPU Time and Elapsed Time for bad plan, should be very high percentage CPU utilization hence very high elapsed time.
So we are planning to pin the plan using sql profile.
1) Download SQLT from below link.
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=189245731844662&id=215187.1&_afrWindowMode=0&_adf.ctrlstate=
1dezgs4aky_4
2) Install SQLT as per the document.
3) Once it is installed, please follow below step to create SQL Profile.
SQL>@coe_xfr_sql_profile.sql 68gtz2b5hby5m 4270379085
It will create sql profile script in sqlt/utl coe_xfr_sql_profile_7yq9pgtsundq0_1749449121.sql
4) If you are using coe_gen_sql_profile.sql instead of coe_xfr_sql_profile.sql , you will get all possible sql profile from history and it will
includes good and bad execution plan with this profiles.
5) Once the SQL Profile script created you can able to create sql profile using the above script and check whether we have good result or bad after applying SQL profile using query mentioned earlier.
Now you have two choice, you can export the profile from non production or you can directly create the sql profile using sql which is created by sqlt.
Exporting SQL Profile
1) Create Staging table which will hold our SQL Profile.
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlprof(
table_name => 'SQL_PROF_29_02_2016',
schema_name => 'JYOTHISH' );
END;
/
2) Packing SQL Profile content to the staging Table.
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlprof(
profile_name => 'SQL_PROF_7yq9pgtsundq0',
staging_table_name => 'SQL_PROF_29_02_2016',
staging_schema_owner => 'JYOTHISH' );
END;
/
3) Export Staging Table using Data pump
4) Import dump file in production.
5) Unpack SQL Profile from Staging Table.
SQL>conn JYOTHISH/xxxxxx
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
replace => TRUE,
staging_table_name => 'SQL_PROF_29_02_2016');
END;
/
6) Validate newly created sql profile in production.
SQL> select name,status from dba_sql_profiles where profile_name like ‘SQL_PROF_7%’;
7) We need to flush shared pool or need to wait for the Database bounce for getting new plan from SQL Profile.
Check with Application team and select a convenient way for this step.
SQL> Alter system flush shared_pool;
If you would like to Extract Hints from a PLAN. Please see the below example.
Extracting Hint from Plan
Suppose if you don’t have licence to use the Database Tuning Pack license, Please use below query to extract hints and adding these hints in SQL
query will help you improve the SQL.
select
extractvalue(value(d), '/hint') as outline_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '7yq9pgtsundq0'
and plan_hash_value = 1749449121
and other_xml is not null
)
) d
Webpage Reference : https://carlos-sierra.net/2012/04/03/what-is-sqltxplain/
