Tune SQL with SQL plan baseline

Goal
----
Tune SQL plan without change the source code(SQL text),

A:\SQL\CBO\tune_sql_by_baseline01.sql

| Modification History:
| Date Who What
| 11-Nov-2009: Charlie(Yi): Create the file,
| 18-Nov-2009: Charlie(Yi): Show binding variable value,


Solution
--------
* tune SQL execution plan by SQL plan baseline, load_plans_from_cursor_cache to
load the hint/changed resulting plan(s) into the SQL plan baseline of the original SQL statement,

Step
----

REM display current "bad" sql plan,

SQL>
set serveroutput off
set linesize 300

var l_bad_sql_id varchar2(30);
var l_sql_text varchar2(200);
exec :l_bad_sql_id := 'cbzfmb3mc9047';
exec :l_sql_text := q'{FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2}';

REM 2nd option to get SQL_ID,
SELECT b.executions EXEC, INST_ID, b.sql_id, CHILD_NUMBER,
ROUND(b.ROWS_PROCESSED/b.executions,1) rows_per,
ROUND(b.BUFFER_GETS/b.executions,1) gets_per,
ROUND(b.ELAPSED_TIME/b.executions/1000000,3) ela_per,
b.module, parse_calls, first_load_time, disk_reads,
b.sql_text
FROM gv$sql b
WHERE UPPER(b.sql_text) LIKE UPPER('%'||:l_sql_text||'%')
AND b.executions > 0
and b.sql_text not like 'SELECT b.executions EXEC%'
ORDER BY b.executions DESC;


select * from table(dbms_xplan.display_awr(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));

or

select * from table(dbms_xplan.display_cursor(sql_id=>:l_bad_sql_id, format=>'basic rows cost partition note last'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

SQL_ID cbzfmb3mc9047
--------------------
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

Plan hash value: 1289812832

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 363 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 38233 | 363 (0)|
| 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 530 | 12 (0)|
----------------------------------------------------------------------------

REM - show binding variable value,
select name, position,datatype_string, last_captured, value_string, b.child_address, b.address
from v$sql_bind_capture b
where sql_id = :l_bad_sql_id
and was_captured <> 'NO';


REM create sql plan baseline for the SQL,

declare
l_pls number;
begin
l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_bad_sql_id,
plan_hash_value => NULL,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans loaded:'||l_pls);

end;
/

REM get sql text, sql_handle
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines
where sql_text like '%'||:l_sql_text||'%';

var l_sql_handle varchar2(30);
exec :l_sql_handle := 'SYS_SQL_ce7588e9ccd926d0';

REM change sql hint to tune SQL execution plan,
set serveroutput off
set linesize 300
var l_sql_id varchar2(30);
var l_sql_plan_hash_value number;
var l_sql_child_number number;

var B1 number;
exec :B1 := 123;
var B2 varchar2(30);
exec :B2 := '1S & 2ND KINGS';

SELECT --+ index_rs_asc(wants WANTSCLIENTID)
* FROM WANTS WHERE CLIENTID = :B1 AND ROWDF = 'F' AND UPPERTITLE >= :B2 ORDER BY UPPERTITLE;

REM capture new SQL_ID and plan_hash_value,
begin
with m as (select sid from v$mystat where rownum <= 1)
select prev_sql_id, prev_child_number
into :l_sql_id, :l_sql_child_number
from m, v$session s
where s.sid = m.sid;
select plan_hash_value into :l_sql_plan_hash_value from v$sql
where sql_id = :l_sql_id
and rownum <= 1;

end;
/

print :l_sql_id;
print :l_sql_plan_hash_value;
print :l_sql_child_number;

REM verify the new SQL plan,
select * from table(dbms_xplan.display_cursor(sql_id=>:l_sql_id, cursor_child_no=>:l_sql_child_number,
format=>'basic rows cost partition note last'));

REM load new sql plan into plan baseline, with same sql_handle,
declare
l_pls number;
begin

l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :l_sql_id,
plan_hash_value => :l_sql_plan_hash_value,
sql_handle => :l_sql_handle,
fixed => 'NO',
enabled => 'NO'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM display sql plan baseline,
SELECT *
FROM table(dbms_xplan.display_sql_plan_baseline(
sql_handle => :l_sql_handle,
format => 'typical'
))
;


PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_ce7588e9ccd926d0
SQL text: SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE
>= :2 ORDER BY UPPERTITLE
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0604f1815
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1289812832

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 17021 (1)| 00:03:08 |
|* 1 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 17021 (1)| 00:03:08 |
|* 2 | INDEX RANGE SCAN | WANTSUPPERTITLE | 19756 | | 361 (0)| 00:00:04 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("CLIENTID"=TO_NUMBER(:1) AND "ROWDF"='F')
2 - access("UPPERTITLE">=:2)

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_ccd926d0ff894fe1
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1555025420

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 217 | 8 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 217 | 8 (13)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| WANTS | 1 | 217 | 7 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WANTSCLIENTID | 7 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("UPPERTITLE">=:2 AND "ROWDF"='F')
3 - access("CLIENTID"=TO_NUMBER(:1))


REM enable new plan in plan baseline,
REM changes an attribute of a single plan or all plans,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'enabled',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM fixed new plan in plan baseline,
REM if it is realy a good plan,
declare
l_pls number;
begin

l_pls := dbms_spm.ALTER_SQL_PLAN_BASELINE(
sql_handle => :l_sql_handle,
plan_name => 'SYS_SQL_PLAN_ccd926d0ff894fe1',
attribute_name => 'fixed',
attribute_value => 'YES'
);
u$err.trc('Number of plans:'||l_pls);
end;
/

REM verify attribute ENABLED and FIXED,
select sql_text, sql_handle, plan_name, enabled, accepted, fixed, autopurge
from dba_sql_plan_baselines
where sql_handle = :l_sql_handle;


REM verify new plan is picked up by CBO optimizer,
explain plan for
SELECT * FROM WANTS WHERE CLIENTID = :1 AND ROWDF = 'F' AND UPPERTITLE >= :2 ORDER BY UPPERTITLE;

select * from table(dbms_xplan.display());