How To Fix The Best Plan From Cursor Cache in Oracle
How to Get SQL_HANDLE And PLAN_NAME From DBA_SQL_PLAN_BASELINES (Doc ID 2242868.1)
http://anuj-singh.blogspot.com/2011/07/oracle-11g-baseline.html
Oracle version ..
SQL> def
DEFINE _DATE = "13-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
How to Get SQL_HANDLE And PLAN_NAME From DBA_SQL_PLAN_BASELINES (Doc ID 2242868.1)
http://anuj-singh.blogspot.com/2011/07/oracle-11g-baseline.html
SQL> def
DEFINE _DATE = "13-NOV-22" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
should be true
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
SQL>
var ENAME varchar2(10)
begin :ENAME := 'ALLEN'; end;
/
select * from emp where ENAME=:ENAME ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
aim to fix above sql
col sql_text for a50 wrap
col SQL_PLAN_BASELINE for a35
select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline,sql_text
from gv$sql
where lower(sql_text) like lower('%select * from emp where ENAME=:ENAME%')
-- and command_type = 3
and sql_text not like '%from gv$sql%';
SQL_ID PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE SQL_TEXT
------------- ----------------- ------------------------ ----------------------------------- --------------------------------------------------
7j5bb53huv8v1 3956160932 11343619050667859858 SQL_PLAN_9uv51dmr6krwkd8a279cc select * from emp where ENAME=:ENAME
COLUMN sql_text FORMAT A120
COLUMN sql_id FORMAT A13
COLUMN bind_name FORMAT A10
COLUMN bind_value FORMAT A26
SELECT sql_id, b.LAST_CAPTURED,
b.HASH_VALUE, b.name bind_name, b.value_string bind_value, t.sql_text sql_text,
FROM
gv$sql t JOIN gv$sql_bind_capture b using (sql_id)
WHERE b.value_string is not null
AND sql_id='&sqlid';
select * from table(dbms_xplan.display_cursor('&sqlid',0, format => 'TYPICAL +PEEKED_BINDS'));
VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '7j5bb53huv8v1',plan_hash_value => '3956160932');
before !!
col sql_text for a50 wrap
col enabled for a15
col accepted for a15
col fixed for a15
col CREATED for a30
select CREATED,sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines
where 1=1
and CREATED >sysdate - interval '1' hour;
CREATED SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED
------------------------------ -------------------- ------------------------------ -------------------------------------------------- --------------- --------------- ---------------
13-NOV-22 12.17.02.000000 PM SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc select * from emp where ENAME=:ENAME YES YES NO
define sql_id='7j5bb53huv8v1'
SELECT PLAN_TABLE_OUTPUT
FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
) t
WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
AND s.SQL_ID='&sql_id';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_9d6ca16cee695f92
SQL text: select * from emp where ENAME=:ENAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9uv51dmr6krwkd8a279cc Plan id: 3634526668
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 3956160932
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
col PLAN_TABLE_OUTPUT for a100
select * from table (DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_9d6ca16cee695f92', format=>'+adaptive'));
SQL> SQL>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_9d6ca16cee695f92
SQL text: select * from emp where ENAME=:ENAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9uv51dmr6krwkd8a279cc Plan id: 3634526668
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
var v_num number;
EXEC :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_9d6ca16cee695f92', plan_name => 'SQL_PLAN_9uv51dmr6krwkd8a279cc', attribute_name=> 'fixed', attribute_value=>'YES');
print v_num;
after !!!
col sql_text for a50 wrap
col enabled for a15
col accepted for a15
col fixed for a15
col CREATED for a30
select CREATED,sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines
where 1=1
and CREATED >sysdate - interval '1' hour;
CREATED SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACCEPTED FIXED
------------------------------ -------------------- ------------------------------ -------------------------------------------------- --------------- --------------- ---------------
13-NOV-22 12.17.02.000000 PM SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc select * from emp where ENAME=:ENAME YES YES YES
SELECT s.sql_id,b.sql_handle, b.sql_text, b.plan_name, b.enabled , b.accepted, b.fixed FROM dba_sql_plan_baselines b, gv$sql s
WHERE s.sql_id='7j5bb53huv8v1'
AND s.exact_matching_signature = b.signature;
SQL_ID SQL_HANDLE SQL_TEXT PLAN_NAME ENABLED ACCEPTED FIXED
------------- -------------------- -------------------------------------------------- ------------------------------ --------------- --------------- ---------------
7j5bb53huv8v1 SQL_9d6ca16cee695f92 select * from emp where ENAME=:ENAME SQL_PLAN_9uv51dmr6krwkd8a279cc YES YES YES
Our sql_id and PLAN_HASH_VALUE
set linesize 300 pagesize 300
col sql_text for a50 wrap
col SQL_PLAN_BASELINE for a25
col EXACT_MATCHING_SIGNATURE for 99999999999999999999999
col PLAN_HASH_VALUE for 9999999999999999
select distinct sql_id, plan_hash_value, s.exact_matching_signature, b.enabled, b.accepted, b.fixed ,s.sql_text from gv$sql s,dba_sql_plan_baselines b
where 1=1
--and sql_text like '%select * from emp where ENAME=:ENAME%'
and sql_id='7j5bb53huv8v1';
SELECT sql_handle, plan_name,ENABLED,ACCEPTED,FIXED,REPRODUCED,OPTIMIZER_COST,to_char(Created,'DD-MON-YY') Created
FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
define sql_id='7j5bb53huv8v1'
SELECT sql_handle, plan_name,ENABLED,ACCEPTED,FIXED,REPRODUCED,OPTIMIZER_COST,to_char(Created,'DD-MON-YY hh:mi') Created
FROM dba_sql_plan_baselines
WHERE signature IN (
SELECT exact_matching_signature FROM v$sql WHERE sql_id='&SQL_ID')
SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED REP OPTIMIZER_COST CREATED
-------------------- ------------------------------ --------------- --------------- --------------- --- -------------- ------------------------------
SQL_9d6ca16cee695f92 SQL_PLAN_9uv51dmr6krwkd8a279cc YES YES YES YES 3 13-NOV-22 12:17
=============
some command !!!!!!!!!
var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'7j5bb53huv8v1', plan_hash_value=>928732588, fixed =>'NO’, enabled=>'YES');
end;
/
var v_num number;
exec :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value', plan_name => 'plan_name_value', attribute_name=> 'enabled', attribute_value=>'YES');
print v_num;
var v_num number;
EXEC :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value', plan_name => 'plan_name_value', attribute_name=> 'fixed', attribute_value=>'YES');
print v_num;
Disable the SQL Plan in SQL plan Baseline in Oracle
var v_num number;
exec :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value', plan_name => 'plan_name_value', attribute_name=> 'enabled', attribute_value=>'NO');
print v_num;
var v_num number;
EXEC :v_num:=dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_handle_value', plan_name => 'plan_name_value', attribute_name=> 'fixed', attribute_value=>'NO');
print v_num;
ww
set serveroutput on
set line 999 pages 999
select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1046c141c5de11a8',plan_name => 'sql_plan_10jq1872xw4d8c079fdff') from dual;
SET SERVEROUTPUT ON LONG 10000
DECLARE
x clob;
BEGIN
x := dbms_spm.evolve_sql_plan_baseline('SQL_9d6ca16cee695f92','SQL_PLAN_9uv51dmr6krwkd8a279cc',
VERIFY=>'YES',
COMMIT=>'YES');
DBMS_OUTPUT.PUT_LINE(x);
END;
/
set serveroutput on
declare
v_sql_plan_id pls_integer;
begin
v_sql_plan_id := dbms_spm.alter_sql_plan_baseline(
sql_handle => 'sys_sql_1046c141c5de11a8',
plan_name => 'sql_plan_10jq1872xw4d8cf314e9e',
attribute_name => 'fixed',
attribute_value => 'YES');
end;
/
to check sql ...
from http://anuj-singh.blogspot.com/2021/02/ SQL Report .... / SQL info ...
var sqlid varchar2(30)
begin :sqlid := '7j5bb53huv8v1'; end; ---- sql id here!!!!
/
set long 50000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext from gv$sql where sql_id=:sqlid
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id=:sqlid
);
define sql_id='7jycxu86n60qh'
col plan_table_output for a150
select plan_table_output
from table(dbms_xplan.display_cursor('&sql_id', null, 'BASIC'))
union all
select * from table(dbms_xplan.display_awr('&sql_id', null, null, 'ALL'))
-- Purge the Shared Pool
select 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from gv$sql
where sql_id = :sqlid
--and child_number=&childnr
;
set linesize 300
col begin_interval_time for a28
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, 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,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio,
(rows_processed_delta/decode(nvl(rows_processed_delta,0),0,1,executions_delta)) avg_rows,
(CPU_TIME_DELTA/decode(nvl(CPU_TIME_DELTA,0),0,1,executions_delta))/1000000 avg_cpu_wait,
(IOWAIT_DELTA/decode(nvl(IOWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_user_io_wait,
(CLWAIT_DELTA/decode(nvl(CLWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_clu_wait,
(APWAIT_DELTA/decode(nvl(APWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_app_wait,
(CCWAIT_DELTA/decode(nvl(CCWAIT_DELTA,0),0,1,executions_delta))/1000000 avg_concurrent_wait
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = :sql_id --sql_id
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 order by 1, 2, 3
define sql_id='7jycxu86n60qh'
set lines 1000 pages 9999
col instance_number FOR 9999 HEA 'Inst'
col end_time HEA 'End Time'
col plan_hash_value HEA 'Plan|Hash Value'
col rows_per_exec HEA 'Rows Per Exec'
col et_secs_per_exec HEA 'Elap Secs|Per Exec'
col cpu_secs_per_exec HEA 'CPU Secs|Per Exec'
col io_secs_per_exec HEA 'IO Secs|Per Exec'
col cl_secs_per_exec HEA 'Clus Secs|Per Exec'
col ap_secs_per_exec HEA 'App Secs|Per Exec'
col cc_secs_per_exec HEA 'Conc Secs|Per Exec'
col pl_secs_per_exec HEA 'PLSQL Secs|Per Exec'
col ja_secs_per_exec HEA 'Java Secs|Per Exec'
col executions_total FOR 999,999 HEA 'Execs|Total'
select 'gv$dba_hist_sqlstat' source,h.instance_number,
to_char(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,
to_char(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions_total,
to_char(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,
to_char(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,
to_char(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,
to_char(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,
to_char(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,
to_char(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,
to_char(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,
to_char(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,
to_char(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM dba_hist_sqlstat h,dba_hist_snapshot s
WHERE h.sql_id = '&sql_id'
AND h.executions_total > 0
AND s.snap_id = h.snap_id
AND s.dbid = h.dbid
AND s.instance_number = h.instance_number
UNION ALL
SELECT 'gv$sqlarea_plan_hash' source,h.inst_id,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,
TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,
h.sql_id,
h.plan_hash_value,
h.executions,
to_char(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,
to_char(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,
to_char(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,
to_char(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,
to_char(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,
to_char(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,
to_char(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,
to_char(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec
FROM gv$sqlarea_plan_hash h
WHERE h.sql_id = '&sql_id'
--and h.inst_id=inst_id
AND h.executions > 0
order by source ;
col inst for 99999999
col sid for 9990
col serial# for 999990
col username for a12
col osuser for a16
col program for a10 trunc
col Locked for a6
col status for a1 trunc print
col "hh:mm:ss" for a8
col SQL_ID for a15
col seq# for 99990
col event heading 'Current/LastEvent' for a25 trunc
col state head 'State (sec)' for a14
col kill for a15
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, username,
ltrim(substr(osuser, greatest(instr(osuser, '\', -1, 1)+1,length(osuser)-14))) osuser,
substr(program,instr(program,'/',-1)+1,
decode(instr(program,'@'),0,decode(instr(program,'.'),0,length(program),instr(program,'.')-1),instr(program,'@')-1)) program, decode(lockwait,NULL,' ','L') locked, status,
to_char(to_date(mod(last_call_et,86400), 'sssss'), 'hh24:mi:ss') "hh:mm:ss",
SQL_ID, seq# , event,
decode(state,'WAITING','WAITING '||lpad(to_char(mod(SECONDS_IN_WAIT,86400),'99990'),6),'WAITED SHORT TIME','ON CPU','WAITED KNOWN TIME','ON CPU',state) state,substr(module,1,25) module, substr(action,1,20) action
from GV$SESSION
where type = 'USER'
and audsid != 0 -- to exclude internal processess
and sql_id= :sqlid
order by inst_id, status, last_call_et desc, sid
/
