How I Create SQL Baseline to fix query with an Execution Plan
creating SQL baseline and force
query to use better execution plan (plan hash value).
Here we are sharing this scenario where the query which
was running fine till yesterday now suddenly running long . So it is very
important to check the plan for a particular sqlid.
Note my DB version in 12.2.0.1.0
Query to check the multiple
hash plans for sql id.
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA)
EXECUTIONS
, sum(ROWS_PROCESSED_DELTA)
CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60)
CPU_MINS
,
trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in ('&sql_id')
--repalce sqlid with your sqlid
group by SQL_ID ,
PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS
SQL_ID PLAN_HASH_VALUE EXECUTIONS CROWS
CPU_MINS ELA_MINS
------------- ---------------
---------- ---------- ---------- ----------
99v5172d7sg2r 2819387955 2 167 1 1
99v5172d7sg2r 3788285656 2
184 1 1
99v5172d7sg2r 2728488078 3 218 1 1
99v5172d7sg2r 1528540721 4 332 1 1
99v5172d7sg2r 1565483063 7 699 7
8
99v5172d7sg2r 1370745820 17 1236 8 9
99v5172d7sg2r 221645537 0 0 36 51
99v5172d7sg2r 1573610442 3 0 74 75
8 rows selected.
we find out the best execution
plan (Plan_hash_value) and force the query to use that plan. Below are the
steps I did to create and fix bad queries by creating SQL baseline.
STEP 1: GENERATE ALL PREVIOUS
HISTORY RUN DETAILS OF SQL_ID FROM AWR
break off sdate
set lines 2000
set linesize 2000
col SDATE format a10
col STIME format a10
select
to_char(begin_interval_time,'YYYY/MM/DD')
SDATE,to_char(begin_interval_time,'HH24:MI') STIME,s.snap_id,
sql_id, plan_hash_value PLAN,
ROUND(elapsed_time_delta/1000000,2) ET_SECS,
nvl(executions_delta,0) execs,
ROUND((elapsed_time_delta/decode(executions_delta,null,1,0,1,executions_delta))/1000000,2)
ET_PER_EXEC,
ROUND((buffer_gets_delta/decode(executions_delta,null,1,0,1,executions_delta)),
2) avg_lio,
ROUND((CPU_TIME_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000,
2) avg_cpu_ms,
ROUND((IOWAIT_DELTA/decode(executions_delta,null,1,0,1,executions_delta))/1000,
2) avg_iow_ms,
ROUND((DISK_READS_DELTA/decode(executions_delta,null,1,0,1,executions_delta)),
2) avg_pio,
ROWS_PROCESSED_DELTA num_rows
from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
where s.sql_id = '&sql_id'
and ss.snap_id =S.snap_id
and ss.instance_number =
S.instance_number
order by sdate,stime;
col
begin_interval_time for a35
select ss.snap_id,
ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0)
execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
from DBA_HIST_SQLSTAT
S, DBA_HIST_SNAPSHOT SS
where s.sql_id =
nvl('&sql_id','99v5172d7sg2r')
and ss.snap_id =
S.snap_id
and ss.instance_number
= S.instance_number
and executions_delta
> 0 --and plan_hash_value=3099046451
order by 1, 2, 3
/
Enter value for sql_id: 99v5172d7sg2r
SNAP_ID
NODE BEGIN_INTERVAL_TIME
SQL_ID PLAN_HASH_VALUE EXECS
AVG_ETIME AVG_LIO
---------- ----------
----------------------------------- ------------- --------------- ----------
---------- ----------
234043
1 31-OCT-22 06.15.10.441
AM 99v5172d7sg2r 1565483063 1
44.977487 1275694
234724 1 07-NOV-22 08.30.15.419 AM 99v5172d7sg2r 1370745820 1
15.671934 485334
235398 1 14-NOV-22 09.00.14.042 AM 99v5172d7sg2r 1370745820 1
63.020552 747894
236069 1 21-NOV-22 08.45.39.541 AM 99v5172d7sg2r 2728488078 1
29.948022 918476
236740 1 28-NOV-22 08.30.50.784 AM 99v5172d7sg2r 3788285656 1
37.30876 1127835
236935 1 30-NOV-22 09.15.45.852 AM 99v5172d7sg2r 3788285656 1
41.008219 1206658
237416 1 05-DEC-22 09.30.56.546 AM 99v5172d7sg2r 1370745820 1
14.640457 439093
238086 1 12-DEC-22 09.00.56.011 AM 99v5172d7sg2r 1370745820 1
23.707833 719818
238778 1 19-DEC-22 02.00.28.302 PM 99v5172d7sg2r 1370745820 1
42.164343 1024287
239436 1 26-DEC-22 10.30.20.445 AM 99v5172d7sg2r 1573610442 1 840.569558 15504539
239438 1 26-DEC-22 11.00.35.118 AM 99v5172d7sg2r 1573610442 1 978.608431 17804033
SNAP_ID
NODE BEGIN_INTERVAL_TIME
SQL_ID PLAN_HASH_VALUE EXECS
AVG_ETIME AVG_LIO
---------- ----------
----------------------------------- ------------- --------------- ----------
---------- ----------
239439 1 26-DEC-22 11.15.42.999 AM 99v5172d7sg2r 1573610442 1 1790.28067 30838064
/*In this scenario sql_id=>99v5172d7sg2r and plan_hash_value for good plan that we want to force is 1370745820.*/
Follow below steps to create
sql baseline for sql_id
SELECT sql_handle,
plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines
WHERE signature IN
(SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID') order by accepted,enabled;
2 Enter value for sql_id: 99v5172d7sg2r
old 2: WHERE signature IN (SELECT
exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
new 2: WHERE signature IN (SELECT
exact_matching_signature FROM v$sql WHERE sql_id='99v5172d7sg2r')
no rows selected
STEP 2: DROP SQL TUNING SET (STS) IF EXISTS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name >
'SQL_FOR_99v5172d7sg2r');
END;
STEP 3: CREATE SQL TUNING
SET
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name
> 'SQL_FOR_99v5172d7sg2r ',
description
> 'SQL tuning set for 99v5172d7sg2r ');
END;
/
/* Populate STS from AWR by
specifying snapshot for a desired plan which we found using the above query.
In this scenario snap id's are 239439
and 237416 and change plan_hash_value accordingly.*/
DECLARE
l_cursor
DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM
TABLE (DBMS_SQLTUNE.select_workload_repository (
23483, -- begin_snap
23484, -- end_snap
q'<sql_id in ('6hxw283cyw0ub') and
plan_hash_value in (1370745820)>', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
100) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset
(
sqlset_name
> 'SQL_FOR_99v5172d7sg2r ',
populate_cursor
> l_cursor);
END;
/
STEP 4: CHECK SQL SET
DETAILS
column text format a20
select sqlset_name,
sqlset_owner, sqlset_id, sql_id,substr(sql_text,1,20)
text,elapsed_time,buffer_gets,
parsing_schema_name,
plan_hash_value, bind_data from dba_sqlset_statements where sqlset_name
='SQL_FOR_99v5172d7sg2r';
STEP 5: LOAD DESIRED PLAN FROM
STS AS SQL PLAN BASELINE
DECLARE
L_PLANS_LOADED
PLS_INTEGER;
BEGIN
L_PLANS_LOADED :
DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME >
'SQL_FOR_99v5172d7sg2r ');
END;
STEP 6: CHECK SQL PLAN BASELINE
DETAILS
SELECT sql_handle,
plan_name,enabled,accepted,fixed FROM dba_sql_plan_baselines
WHERE signature IN (SELECT
exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
order by accepted,enabled;
STEP 7: ENABLE FIXED=YES
var pbsts varchar2(30);
exec :pbsts :
dbms_spm.alter_sql_plan_baseline('SQL_64e76e773c55f1c4','SQL_PLAN_99v5172d7sg2r 0a6275e','FIXED','YES');
STEP 8: PURGE OLD EXECUTION
PLAN FROM SHARED POOL
Find below two parameter which are required to purge specific sql from the shared pool.
select address||','||hash_value from gv$sqlarea where sql_id ='99v5172d7sg2r ';
ADDRESS||','||HASH_VALUE
---------------------------------------------------------
C00000016FF92618,2591833175
Now use below command to purge
sql from shared pool.
exec
sys.dbms_shared_pool.purge(' C00000016FF92618,2591833175','C',1);
Re-run query or program to test
=====================
Transfer
SQL plans by using a SQL plan baseline
A particular SQL query might perform
poorly on one database (such as production) but work fine on another database
(such as development). This situation might occur if the same query has a
different execution plan on each instance. This blog shows how to transfer the
execution plan from an instance, where query works fine, to another instance,
where query behaves poorly, by using the SQL plan baseline feature that Oracle®
Database® first introduced in version 11g.
Introduction to SQL Plan Management
Oracle SQL Plan Management (SPM) is a
feature in Oracle Database that captures all the historical execution plans for
a query. With that, you can create a baseline for the good plan from the
execution plans available in SPM and enable that baseline to ensure that the
system picks only the good plan from the baseline.
To take advantage of this feature, you
must identify the sql_id of the query
that is performing well on one instance and performing poorly on another
instance. You must also capture the good execution plan ID, the plan_hash_value, of the query on the instance where it
works well.
Copy the SQL base plan from one instance to
another
Use the following steps to copy the SQL
base plan from the source instance to the target instance:
1. Run the query on
the source instance, where the query works well, so that the query exists in
the cursor cache.
2. On the source
instance, load the SQL execution plan for the query from the cursor cache to
SPM as a baseline.
3. Create a staging
table on the source instance. This table is used to migrate the execution plan
from the source instance to the target instance.
4. Pack the source
execution plan, or baseline, in the staging table on the source instance.
5. Transfer the
staging table from the source instance to the target instance by using the
export/import utility.
6. Unpack the SQL plan
on the target instance from the staging table to SPM.
7. Verify that
baseline created on target instance is fixed and accepted for the query to
select it on next run.
8. Test the SQL that
had a performance issue on the target instance and verify that it picks the
transferred baseline.
Example execution
Executing the preceding steps results
in output similar to the following examples.
STEP
1: RUN QUERY ON THE SOURCE INSTANCE
Run the SQL on the source instance and
identify the sql_id and plan_hash_value. Examine the cursor cache to get the
values. In this case, they are the following values:
·
sql_id: 9xva48wpnsmp6
·
plan_hash_value: 1572948408)
Execute the following query on the
source instance:
SQL> select distinct plan_hash_value from v$sql where
sql_id='9xva48wpnsmp6';
PLAN_HASH_VALUE
---------------
1572948408
STEP
2: LOAD THE PLAN TO SPM
Execute the following query to load
this good query execution plan from the cursor cache to SPM as a baseline:
SQL> set serveroutput on
SQL> declare
2 ret binary_integer;
l_sql_id varchar2(13);
3
4 l_plan_hash_value
number;
5 l_fixed varchar2(3);
6 l_enabled varchar2(3);
7 Begin
8 l_sql_id :=
'&&sql_id';
9 l_plan_hash_value :=
to_number('&&plan_hash_value');
10 l_fixed := 'Yes';
11 l_enabled := 'Yes';
12 ret :=
dbms_spm.load_plans_from_cursor_cache(
13
sql_id=>l_sql_id,
14
plan_hash_value=>l_plan_hash_value,
15 fixed=>l_fixed,
16 enabled=>l_enabled);
17 end;
18 /
Enter value for sql_id: 9xva48wpnsmp6
old 8: l_sql_id := '&&sql_id';
new 8: l_sql_id := '9xva48wpnsmp6';
Enter value for plan_hash_value: 1572948408
old 9: l_plan_hash_value :=
to_number('&&plan_hash_value');
new 9: l_plan_hash_value := to_number('1572948408');
PL/SQL procedure successfully completed.
Execute the following defined queries
to verify that you created the SQL baseline on the source instance. Note the
following details for later reference.
SQL> select count(*) from dba_sql_plan_baselines ;
COUNT(*)
--------
1
SQL> select SQL_HANDLE, PLAN_NAME from dba_sql_plan_baselines;
SQL_HANDLE
PLAN_NAME
------------------------------ ------------------------------
SQL_d344aac395f978a4
SQL_PLAN_d6j5asfazky54868c96c3
STEP
3: CREATE A STAGING TABLE ON THE SOURCE INSTANCE
Execute the following query to create a
staging table on the source instance:
SQL> sho user
USER is "SYS"
SQL> BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'SPM_STAGETAB',
table_owner => 'APPS',
tablespace_name =>
'SYSAUX');
END;
2 3 4
5 6 7
8 /
PL/SQL procedure successfully completed.
STEP
4: PACK THE BASELINE
Execute the following query to pack the
baseline in the staging table on the source instance:
SQL> DECLARE
2 my_plans number;
3 BEGIN
4 my_plans :=
DBMS_SPM.PACK_STGTAB_BASELINE(
table_name =>
'SPM_STAGETAB',
enabled =>
'yes',
5
6
7 table_owner =>
'APPS',
8 plan_name =>
'SQL_PLAN_d6j5asfazky54868c96c3',
9 sql_handle =>
'SQL_d344aac395f978a4');
10 END;
11 /
PL/SQL procedure successfully completed.
STEP
5: TRANSFER THE STAGING TABLE FROM THE SOURCE TO THE TARGET INSTANCE
Execute the following command to take
an export backup of the staging table on the source instance:
exp file=SPM_STAGETAB.dmp tables=APPS.SPM_STAGETAB
log=SPM_STAGETAB.log compress=n
Export: Release 11.2.0.4.0 - Production on Sun Jun 3 13:14:50 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system/*******
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR
character set
About to export specified tables via Conventional Path ...
Current user changed to APPS
. . exporting table
SPM_STAGETAB 1 rows exported
Export terminated successfully without warnings.
Now, execute the following command on
the target instance to transfer the export backup of the staging table to the
target instance’s host and import the table in the target instance:
imp system file=SPM_STAGETAB.dmp log=imp_SPM_STAGETAB.log
fromuser=apps touser=apps
Import: Release 11.2.0.4.0 - Production on Sun Jun 3 14:16:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release
11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR
character set
. importing APPS's objects into APPS
. . importing table
"SPM_STAGETAB" 1 rows imported
Import terminated successfully without warnings.
STEP
6: UNPACK THE BASELINE
Execute the following commands to
unpack the baseline from staging table to the target instance’s SPM. In the
following example, take a count before unpacking the baseline to verify that
the baseline was imported properly on the target.
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
--------
2
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_plans_unpacked PLS_INTEGER;
3 BEGIN
4 l_plans_unpacked
:= DBMS_SPM.unpack_stgtab_baseline(
5
table_name =>
'SPM_STAGETAB',
6
table_owner => 'APPS');
7
8
DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
9 END;
10 /
Plans Unpacked: 1
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_sql_plan_baselines;
COUNT(*)
--------
3
STEP
7: VERIFY THE BASELINE
Run the following commands on the
target instance to verify that the baseline is accepted and fixed.
SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed,
origin FROM dba_sql_plan_baselines;
SQL_HANDLE
PLAN_NAME ENA
ACC FIX ORIGIN
--------------------- ------------------------------ --- --- ---
------------
SQL_d344aac395f978a4
SQL_PLAN_d6j5asfazky54868c96c3 YES YES NO MANUAL-LOAD
SQL>
The preceding output shows that the
baseline was imported on target instance but that it is not fixed. Run the
following query to fix the baseline and enable the optimizer to pick only this
plan.
SQL> DECLARE
2 l_plans_altered PLS_INTEGER;
3 BEGIN
4 l_plans_altered :=
DBMS_SPM.alter_sql_plan_baseline(
5 sql_handle => 'SQL_d344aac395f978a4',
6 PLAN_NAME => 'SQL_PLAN_d6j5asfazky54868c96c3',
7 ATTRIBUTE_NAME => 'fixed',
8 attribute_value =>
'YES');
9
10 DBMS_OUTPUT.put_line('Plans
Altered: ' || l_plans_altered);
11 END;
12 /
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, plan_name, enabled, accepted, fixed,
origin FROM dba_sql_plan_baselines;
SQL_HANDLE
PLAN_NAME ENA ACC FIX ORIGIN
--------------------- ------------------------------ --- --- ---
------------
SQL_d344aac395f978a4
SQL_PLAN_d6j5asfazky54868c96c3 YES YES YES MANUAL-LOAD
SQL>
STEP
8: TEST THE SQL QUERY ON THE TARGET INSTANCE
Execute the following command on the
target instance to verify that it picks up the new baseline:
SQL> select SQL_PLAN_BASELINE from v$sql where
sql_id='9xva48wpnsmp6';
SQL_PLAN_BASELINE
------------------------------
SQL_PLAN_d6j5asfazky54868c96c3
How the SQL plan is selected
The following image shows how a SQL
plan is selected when a baseline plan exists:
Image source: Metalink Note Automatic SQL Plan Baselines (Doc ID
1930525.1)
Conclusion
Use the steps in this post if you have
to transfer the baseline for a single query. You can also generate SQL
baselines for all the queries for upgrades, migrations, and so on. Use an SQL
plan baseline to have consistent SQL execution plans and to avoid any
performance issues.
No comments:
Post a Comment