EasyReliableDBA: December 2022

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);

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(*)

--------

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',

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

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

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(*)

--------

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');

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(*)

--------

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');

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.

EasyReliableDBA

Monday 26 December 2022

How I Create SQL Baseline to fix query with an Execution Plan

Search This Blog