SQL Profile Script
Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1)
var sqlid varchar2(30)
begin :sqlid := '87gaftwrm2h68'; end; ---- sql id name here!!!!
/
select 'exec sys.dbms_shared_pool.purge('''||address||', '||hash_value||''', ''c'')'
from gv$sql
where sql_id = :sqlid
--and child_number=&childnr
;
Value Kind of Object to keep
-- ----- ----------------------
-- P package/procedure/function
-- Q sequence
-- R trigger
-- T type
-- JS java source
-- JC java class
-- JR java resource
-- JD java shared data
-- C cursor
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';
exec DBMS_SHARED_POOL.PURGE ('00000005DGEC9DE0, 257655674', 'C');
set verify off
set pagesize 999
col username format a13
col prog format a22
col sql_text format a41
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col etime format 9,999,999.99
select sql_id, child_number, plan_hash_value plan_hash, executions execs, elapsed_time/1000000 etime,(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, u.username,
sql_text
from gv$sql s, dba_users u
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from v$sql where sql_text like nvl(%'
and sql_id like nvl(':sqlid',sql_id)
and u.user_id = s.parsing_user_id
/
select sql_id, count(distinct plan_hash_value) distinct_plans, sql_text from gv$sql
group by sql_id, sql_text
having count(distinct plan_hash_value) >= &how_many
/
====================================
@coe_xfr_sql_profile.sql dkz7v96ym42c6 3302976337
Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (Doc ID 1955195.1)
SPO coe_xfr_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $
REM
REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM carlos.sierra@oracle.com
REM
REM SCRIPT
REM coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM This script generates another that contains the commands to
REM create a manual custom SQL Profile out of a known plan from
REM memory or AWR. The manual custom profile can be implemented
REM into the same SOURCE system where the plan was retrieved,
REM or into another similar TARGET system that has same schema
REM objects referenced by the SQL that generated the known plan.
REM
REM PRE-REQUISITES
REM 1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM 1. SQL_ID (required)
REM 2. Plan Hash Value for which a manual custom SQL Profile is
REM needed (required). A list of known plans is presented.
REM You may choose from list provided or enter a valid phv
REM from a version of the SQL modified with Hints.
REM
REM EXECUTION
REM 1. Connect into SQL*Plus as user with access to data dictionary.
REM Do not use SYS.
REM 2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM plan hash value (parameters can be passed inline or until
REM requested).
REM
REM EXAMPLE
REM # sqlplus system
REM SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;
REM SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;
REM SQL> START coe_xfr_sql_profile.sql;
REM
REM NOTES
REM 1. For possible errors see coe_xfr_sql_profile.log
REM 2. If SQLT is installed in SOURCE, you can use instead:
REM sqlt/utl/sqltprofile.sql
REM 3. Be aware that using DBMS_SQLTUNE requires a license for
REM Oracle Tuning Pack.
REM 4. Use a DBA user but not SYS.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id = '&1';
PRO
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
PRO
DEF plan_hash_value = '&2';
PRO
PRO Values passed to coe_xfr_sql_profile:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID : "&&sql_id."
PRO PLAN_HASH_VALUE: "&&plan_hash_value."
PRO
SET TERM OFF ECHO ON;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
-- trim parameters
COL sql_id NEW_V sql_id FOR A30;
COL plan_hash_value NEW_V plan_hash_value FOR A30;
SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;
VAR sql_text CLOB;
VAR sql_text2 CLOB;
VAR other_xml CLOB;
EXEC :sql_text := NULL;
EXEC :sql_text2 := NULL;
EXEC :other_xml := NULL;
-- get sql_text from memory
DECLARE
l_sql_text VARCHAR2(32767);
BEGIN -- 10g see bug 5017909
FOR i IN (SELECT DISTINCT piece, sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = TRIM('&&sql_id.')
ORDER BY 1, 2)
LOOP
IF :sql_text IS NULL THEN
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
END IF;
-- removes NUL characters
l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');
-- adds a NUL character at the end of each line
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
END LOOP;
-- if found in memory then sql_text is not null
IF :sql_text IS NOT NULL THEN
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text FROM DUAL;
-- get sql_text from awr
DECLARE
l_sql_text VARCHAR2(32767);
l_clob_size NUMBER;
l_offset NUMBER;
BEGIN
IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN
SELECT sql_text
INTO :sql_text2
FROM dba_hist_sqltext
WHERE sql_id = TRIM('&&sql_id.')
AND sql_text IS NOT NULL
AND ROWNUM = 1;
END IF;
-- if found in awr then sql_text2 is not null
IF :sql_text2 IS NOT NULL THEN
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);
l_offset := 1;
DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);
DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);
-- store in clob as 64 character pieces plus a NUL character at the end of each piece
WHILE l_offset < l_clob_size
LOOP
IF l_clob_size - l_offset > 64 THEN
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');
ELSE -- last piece
l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');
END IF;
DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));
l_offset := l_offset + 64;
END LOOP;
DBMS_LOB.CLOSE(:sql_text);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);
:sql_text := NULL;
END;
/
SELECT :sql_text2 FROM DUAL;
SELECT :sql_text FROM DUAL;
-- validate sql_text
SET TERM ON;
BEGIN
IF :sql_text IS NULL THEN
RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
END IF;
END;
/
SET TERM OFF;
-- get other_xml from memory
BEGIN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from memory from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM gv$sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
child_number, id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);
:other_xml := NULL;
END;
/
-- get other_xml from awr from modified SQL
BEGIN
IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN
FOR i IN (SELECT other_xml
FROM dba_hist_sql_plan
WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))
AND other_xml IS NOT NULL
ORDER BY
id)
LOOP
:other_xml := i.other_xml;
EXIT; -- 1st
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);
:other_xml := NULL;
END;
/
SELECT :other_xml FROM DUAL;
-- validate other_xml
SET TERM ON;
BEGIN
IF :other_xml IS NULL THEN
RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
END IF;
END;
/
SET TERM OFF;
-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.
SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;
SET SERVEROUT ON SIZE UNL FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;
DECLARE
l_pos NUMBER;
l_clob_size NUMBER;
l_offset NUMBER;
l_sql_text VARCHAR2(32767);
l_len NUMBER;
l_hint VARCHAR2(32767);
BEGIN
DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');
DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM AUTHOR');
DBMS_OUTPUT.PUT_LINE('REM carlos.sierra@oracle.com');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
DBMS_OUTPUT.PUT_LINE('REM coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
DBMS_OUTPUT.PUT_LINE('REM This script is generated by coe_xfr_sql_profile.sql');
DBMS_OUTPUT.PUT_LINE('REM It contains the SQL*Plus commands to create a custom');
DBMS_OUTPUT.PUT_LINE('REM SQL Profile for SQL_ID &&sql_id. based on plan hash');
DBMS_OUTPUT.PUT_LINE('REM value &&plan_hash_value..');
DBMS_OUTPUT.PUT_LINE('REM The custom SQL Profile to be created by this script');
DBMS_OUTPUT.PUT_LINE('REM will affect plans for SQL commands with signature');
DBMS_OUTPUT.PUT_LINE('REM matching the one for SQL Text below.');
DBMS_OUTPUT.PUT_LINE('REM Review SQL Text and adjust accordingly.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
DBMS_OUTPUT.PUT_LINE('REM None.');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
DBMS_OUTPUT.PUT_LINE('REM SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('REM NOTES');
DBMS_OUTPUT.PUT_LINE('REM 1. Should be run as SYSTEM or SYSDBA.');
DBMS_OUTPUT.PUT_LINE('REM 2. User must have CREATE ANY SQL PROFILE privilege.');
DBMS_OUTPUT.PUT_LINE('REM 3. SOURCE and TARGET systems can be the same or similar.');
DBMS_OUTPUT.PUT_LINE('REM 4. To drop this custom SQL Profile after it has been created:');
DBMS_OUTPUT.PUT_LINE('REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');
DBMS_OUTPUT.PUT_LINE('REM 5. Be aware that using DBMS_SQLTUNE requires a license');
DBMS_OUTPUT.PUT_LINE('REM for the Oracle Tuning Pack.');
DBMS_OUTPUT.PUT_LINE('REM 6. If you modified a SQL putting Hints in order to produce a desired');
DBMS_OUTPUT.PUT_LINE('REM Plan, you can remove the artifical Hints from SQL Text pieces below.');
DBMS_OUTPUT.PUT_LINE('REM By doing so you can create a custom SQL Profile for the original');
DBMS_OUTPUT.PUT_LINE('REM SQL but with the Plan captured from the modified SQL (with Hints).');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');
DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');
DBMS_OUTPUT.PUT_LINE('REM');
DBMS_OUTPUT.PUT_LINE('DECLARE');
DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');
DBMS_OUTPUT.PUT_LINE('h SYS.SQLPROF_ATTR;');
DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');
DBMS_OUTPUT.PUT_LINE('END wa;');
DBMS_OUTPUT.PUT_LINE('BEGIN');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');
DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');
DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');
DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');
l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);
l_offset := 1;
WHILE l_offset < l_clob_size
LOOP
l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);
IF l_pos > 0 THEN
l_len := l_pos - l_offset;
ELSE -- last piece
l_len := l_clob_size - l_pos + 1;
END IF;
l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);
/* cannot do such 3 replacement since a line could end with a comment using "--"
l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP
l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP
l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP
*/
l_offset := l_offset + l_len + 1;
IF l_len > 0 THEN
IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN
l_sql_text := '['||l_sql_text||']';
ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN
l_sql_text := '{'||l_sql_text||'}';
ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN
l_sql_text := '<'||l_sql_text||'>';
ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN
l_sql_text := '('||l_sql_text||')';
ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN
l_sql_text := '"'||l_sql_text||'"';
ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN
l_sql_text := '|'||l_sql_text||'|';
ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN
l_sql_text := '~'||l_sql_text||'~';
ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN
l_sql_text := '^'||l_sql_text||'^';
ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN
l_sql_text := '@'||l_sql_text||'@';
ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN
l_sql_text := '#'||l_sql_text||'#';
ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN
l_sql_text := '%'||l_sql_text||'%';
ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN
l_sql_text := '$'||l_sql_text||'$';
ELSE
l_sql_text := CHR(96)||l_sql_text||CHR(96);
END IF;
DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');
DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');
FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint
FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)
LOOP
l_hint := i.hint;
WHILE NVL(LENGTH(l_hint), 0) > 0
LOOP
IF LENGTH(l_hint) <= 500 THEN
DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');
l_hint := NULL;
ELSE
l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);
DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');
l_hint := ' '||SUBSTR(l_hint, l_pos);
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');
DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');
DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');
DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
DBMS_OUTPUT.PUT_LINE('sql_text => sql_txt,');
DBMS_OUTPUT.PUT_LINE('profile => h,');
DBMS_OUTPUT.PUT_LINE('name => ''coe_&&sql_id._&&plan_hash_value.'',');
DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');
DBMS_OUTPUT.PUT_LINE('category => ''DEFAULT'',');
DBMS_OUTPUT.PUT_LINE('validate => TRUE,');
DBMS_OUTPUT.PUT_LINE('replace => TRUE,');
DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');
DBMS_OUTPUT.PUT_LINE('END;');
DBMS_OUTPUT.PUT_LINE('/');
DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');
DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');
DBMS_OUTPUT.PUT_LINE('PRINT signature');
DBMS_OUTPUT.PUT_LINE('PRINT signaturef');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');
DBMS_OUTPUT.PUT_LINE('SPO OFF;');
DBMS_OUTPUT.PUT_LINE('PRO');
DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');
END;
/
SPO OFF;
SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;
SET SERVEROUT OFF;
PRO
PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
PRO on TARGET system in order to create a custom SQL Profile
PRO with plan &&plan_hash_value linked to adjusted sql_text.
PRO
UNDEFINE 1 2 sql_id plan_hash_value
CL COL
PRO
PRO COE_XFR_SQL_PROFILE completed.
=====
Monday, 14 November 2022
SQL Profile Script
Sunday, 13 November 2022
How To Fix the Best Plan from Cursor Cache in Oracle
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
/
Saturday, 12 November 2022
gv$sql with objects
sql_id and objects
efine sql_id='7jycxu86n60qh' -------
SET HEADING ON
SET PAGESIZE 1000 LINESIZE 500
COL text FOR A50 wrap
COL ctext FOR A50 wrap
col PARSING_SCHEMA_NAME for a20
col LAST_LOAD_TIME for a27
col FIRST_LOAD_TIME for a27
col OWNER for a20
col OBJECT_NAME for a20
select vs.sql_id,vs.last_load_time, ao.OWNER, parsing_schema_name, first_load_time , ao.OBJECT_NAME, vs.program_line#, executions exe, vs.sqltype, vs.sql_id, vs.rows_processed rows_processed, concurrency_wait_time, elapsed_time/1000000 elapsed_secs, elapsed_time/1000000/(case when executions = 0 then 1 else executions end) elap_per_exec_secs, vs.sql_text ctext
from gv$sql vs, all_objects ao
where vs.PROGRAM_ID = ao.OBJECT_ID and parsing_schema_name not in ('SYS','SYSTEM')
--and parsing_schema_name in ('')
--and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','MDSYS')
and vs.sql_id='&sql_id'
order by vs.last_load_time desc , vs.parsing_schema_name, vs.first_load_time desc , program_id, vs.program_line#;
Oracle SQL Plan Management (SPM) from AWR !!!
Oracle SQL Plan Management (SPM) from AWR !!!
How to Generate an AWR Report and Create Baselines (Doc ID 748642.1) How to Create A SQL Plan Baseline From A Historical Execution Plan In The Automatic Workload Repository (AWR) [RDBMS Version 12.2 or Higher] (Doc ID 2885167.1)
Oracle version=>12.2 SQL> def DEFINE _DATE = "12-NOV-22" (CHAR) DEFINE _CONNECT_IDENTIFIER = "rac1" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR) ========================================================= define sql_id='8cnh50qfgwg73' ----- For this sql !!!! 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' col PARSING_SCHEMA_NAME for a20 select 'gv$dba_hist_sqlstat' source,h.snap_id,h.instance_number, h.CON_ID, 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, h.PARSING_SCHEMA_NAME, 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 1=1 and 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 --and PARSING_SCHEMA_NAME!='SYS' ; Plan Execs Elap Secs CPU Secs IO Secs Clus Secs App Secs Conc Secs PLSQL Secs Java Secs SOURCE SNAP_ID Inst CON_ID SNAP_TIME End Time SQL_ID Hash Value Total PARSING_SCHEMA_NAME Rows Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec ------------------- ---------- ----- ---------- ---------------- ---------------- ------------- ---------- -------- -------------------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ gv$dba_hist_sqlstat 9720 1 0 09-11-2022 20:00 09-11-2022 21:00 8cnh50qfgwg73 2772691065 17,496 SVCDBEM7ADM 1 1.990 0.638 1.227 0.417 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9748 1 0 11-11-2022 00:00 11-11-2022 01:00 8cnh50qfgwg73 2772691065 10,368 SVCDBEM7ADM 1 1.907 0.634 1.147 0.417 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9770 1 0 11-11-2022 22:00 11-11-2022 23:00 8cnh50qfgwg73 2772691065 384 SVCDBEM7ADM 1 1.910 0.648 1.148 0.408 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9578 1 0 03-11-2022 23:00 04-11-2022 00:00 8cnh50qfgwg73 2772691065 9,984 SVCDBEM7ADM 1 1.913 0.633 1.155 0.415 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9633 1 0 06-11-2022 05:00 06-11-2022 06:00 8cnh50qfgwg73 2772691065 3,840 SVCDBEM7ADM 1 1.874 0.634 1.127 0.402 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9640 1 0 06-11-2022 12:00 06-11-2022 13:00 8cnh50qfgwg73 2772691065 6,528 SVCDBEM7ADM 1 1.867 0.639 1.114 0.405 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9738 1 0 10-11-2022 14:00 10-11-2022 15:00 8cnh50qfgwg73 2772691065 6,528 SVCDBEM7ADM 1 1.924 0.633 1.165 0.416 0.000 0.000 0.000 0.000 gv$dba_hist_sqlstat 9745 1 0 10-11-2022 21:00 10-11-2022 22:00 8cnh50qfgwg73 2772691065 9,216 SVCDBEM7ADM 1 1.907 0.631 1.149 0.417 0.000 0.000 0.000 0.000 or
define sql_id='8cnh50qfgwg73' ----- For this sql !!!! SET LINESIZE 2000 PAGESIZE 20000 SET LONG 99999 select SNAP_ID, TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24') begin_interval_time, TO_CHAR(end_interval_time,'DD-MON-YYYY HH24') end_interval_time, SQL_ID, round((round((avg(ELAPSED_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as ELAPSED_TIME_SECS, round((round((avg(CPU_TIME_DELTA)/1000000),2)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as CPU_TIME_SECS, round((avg(BUFFER_GETS_DELTA)/(case when avg(EXECUTIONS_DELTA)=0 then 1 else avg(EXECUTIONS_DELTA) end)),2) as GETS_PER_EXEC from dba_hist_snapshot natural join dba_hist_sqlstat natural join dba_hist_sqltext where (elapsed_time_delta > 0 or elapsed_time_delta is not null) and SQL_ID = '&sql_id' group by SNAP_ID, TO_CHAR(begin_interval_time,'DD-MON-YYYY HH24'), TO_CHAR(end_interval_time,'DD-MON-YYYY HH24'), SQL_ID order by snap_id asc / SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SQL_ID ELAPSED_TIME_SECS CPU_TIME_SECS GETS_PER_EXEC ---------- ----------------------- ----------------------- -------------- ----------------- ------------- ------------- 9574 03-NOV-2022 19 03-NOV-2022 20 8cnh50qfgwg73 1.86 .63 10988.01 9575 03-NOV-2022 20 03-NOV-2022 21 8cnh50qfgwg73 1.89 .63 10966.74 9576 03-NOV-2022 21 03-NOV-2022 22 8cnh50qfgwg73 1.85 .62 10978.95 9577 03-NOV-2022 22 03-NOV-2022 23 8cnh50qfgwg73 1.94 .66 10980.97 9578 03-NOV-2022 23 04-NOV-2022 00 8cnh50qfgwg73 1.9 .65 10982.64 9579 04-NOV-2022 00 04-NOV-2022 01 8cnh50qfgwg73 1.9 .65 10983.89 -- to check sql text !!!! col sql_text for a50 wrap select CON_ID,sql_text from dba_hist_sqltext where 1=1 and sql_id ='&sql_id' and rownum<3; CON_ID SQL_TEXT ---------- -------------------------------------------------- 0 SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE W HERE TABLESPACE_NAME = :B1 --- change the value for sql_id and plan_hash_value variable x number begin :x := dbms_spm.load_plans_from_awr( begin_snap=>9738,end_snap=>9745, basic_filter=>q'[ sql_id='8cnh50qfgwg73' and plan_hash_value='2772691065' ]' ); end; / print x X ---------- 1 set linesize 300 col CREATOR for a20 col PLAN_NAME for a30 col PARSING_SCHEMA_NAME for a25 col CREATED for a30 col ENABLED for a15 col ACCEPTED for a15 col REPRODUCED for a15 select SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED from dba_sql_plan_baselines where 1=1 --and origin like 'MANUAL-LOAD%' and CREATED >sysdate -1 -- and SQL_HANDLE='SQL_7a5adea0a1422e62' order by created desc; SQL_HANDLE PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTED REPRODUCED CREATED ------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------ SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS MANUAL-LOAD-FROM-AWR SVCDBEM7ADM YES YES NO 12-NOV-22 03.14.49.000000 AM or col sql_id format a14 col sql_handle format a22 col plan_name format a32 col sql_text format a40 col ENABLED for a15 col ACCEPTED for a15 col SQL_TEXT for a40 wrap select DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id, sql_handle , plan_name , enabled , accepted , SQL_TEXT from dba_sql_plan_baselines where DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '8cnh50qfgwg73'; SQL_ID SQL_HANDLE PLAN_NAME ENABLED ACCEPTED SQL_TEXT -------------- ---------------------- -------------------------------- --------------- --------------- ---------------------------------------- 8cnh50qfgwg73 SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a YES YES SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FR EE_SPACE WHERE TABLESPACE_NAME = :B1 ==== with sql text !!!! define sql_id='8cnh50qfgwg73' set linesize 400 col CREATOR for a20 col PLAN_NAME for a30 col PARSING_SCHEMA_NAME for a25 col CREATED for a30 col ENABLED for a15 col ACCEPTED for a15 col REPRODUCED for a15 col SQL_TEXT for a50 wrap select sql_id,plan_name,SQL_HANDLE,PLAN_NAME,CREATOR,ORIGIN,PARSING_SCHEMA_NAME, ENABLED,ACCEPTED,REPRODUCED,CREATED,substr(sa.SQL_TEXT,1,50) SQL_TEXT from dba_sql_plan_baselines bl, dba_hist_sqltext sa where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text) and sa.sql_id = '&sql_id' and origin like 'MANUA%' and created > sysdate -1; SQL_ID PLAN_NAME SQL_HANDLE PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTEDREPRODUCED CREATED ------------- ------------------------------ ------------------------------ ------------------------------ -------------------- ----------------------------- ------------------------- --------------- --------------- --------------- ------------------------------ 8cnh50qfgwg73 SQL_PLAN_br1hhyyh6324xf1ad1f6a SQL_bb8610f7a061889d SQL_PLAN_br1hhyyh6324xf1ad1f6a SYS MANUAL-LOAD-FROM-AWR SVCDBEM7ADM YES YES NO 12-NOV-22 03.14.49.000000 AM -- to check col PLAN_TABLE_OUTPUT for a120 select t2.* from dba_sql_plan_baselines t1, table( dbms_xplan.display_sql_plan_baseline(t1.sql_handle,t1.plan_name) ) t2 where 1=1 and SQL_HANDLE ='&SQL_HANDLE' --and PLAN_NAME='SQL_PLAN_br1hhyyh6324xf1ad1f6a' ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------- SQL handle: SQL_bb8610f7a061889d SQL text: SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_br1hhyyh6324xf1ad1f6a Plan id: 4054654826 Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-FROM-AWR Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 2772691065 ====== Check if possible!!!!!! alter session set current_schema=SVCDBEM7ADM; var B1 varchar2(10); begin :B1 := 'abcdf'; end; / set autotrace traceonly explain SELECT NVL(SUM(BYTES),0) FROM SYS.DBA_FREE_SPACE WHERE TABLESPACE_NAME = :B1 . . . . . . Note ----- - SQL plan baseline "SQL_PLAN_br1hhyyh6324xf1ad1f6a" used for this statement <<<<<<<<<<< - this is an adaptive plan
=======2nd method
show parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
define sql_id='87gaftwrm2h68'
col BEGIN_INTERVAL_TIME for a30
SELECT SS.SNAP_ID,
SS.INSTANCE_NUMBER,
BEGIN_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,OPTIMIZER_COST,
DISK_READS_TOTAL,
BUFFER_GETS_TOTAL,
ROWS_PROCESSED_TOTAL,
CPU_TIME_TOTAL,
ELAPSED_TIME_TOTAL,
IOWAIT_TOTAL,
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 1=1
and 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;
SNAP_ID INSTANCE_NUMBER BEGIN_INTERVAL_TIME SQL ID PLAN_HASH_VALUE OPTIMIZER_COST DISK_READS_TOTAL BUFFER_GETS_TOTAL ROWS_PROCESSED_TOTAL CPU_TIME_TOTAL ELAPSED_TIME_TOTAL IOWAIT_TOTAL EXECS AVG_ETIME AVG_LIO
---------- --------------- ------------------------------ ---------------- --------------- -------------- ---------------- ----------------- -------------------- -------------- ------------------ ------------ ---------- ---------- ----------
9819 1 13-NOV-22 11.00.22.179 PM 87gaftwrm2h68 1072382624 3 16 2861552 135012 67293418 67261553 1537478 4476 .000033331 2.02033065
9819 2 13-NOV-22 11.00.22.135 PM 87gaftwrm2h68 1072382624 3 26 3421346 112091 56183976 56243522 403516 6090 .000027044 2.06962233
9820 1 14-NOV-22 12.00.30.958 AM 87gaftwrm2h68 1072382624 3 16 2872967 135125 67518772 67434327 1538665 5650 .000030579 2.02035398
--Create STS.
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'STS_87gaftwrm2h68',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
--Load STS
DECLARE
cur sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>9819, end_snap=>9820,basic_filter=>'sql_id = ''87gaftwrm2h68''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_87gaftwrm2h68', populate_cursor=>cur);
CLOSE cur;
END;
set linesize 2000
col SQL_TEXT for a50 wrap
col x for a200
SELECT
first_load_time ,
executions as execs ,
parsing_schema_name ,
elapsed_time / 1000000 as elapsed_time_secs ,
cpu_time / 1000000 as cpu_time_secs ,
buffer_gets ,
disk_reads ,
direct_writes ,
rows_processed ,
fetches ,
optimizer_cost ,
sql_plan ,
plan_hash_value ,
sql_id ,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_87gaftwrm2h68')
);
FIRST_LOAD_TIME EXECS PARSING_SCHEMA_NAME ELAPSED_TIME_SECS CPU_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES OPTIMIZER_COST
------------------- ---------- ------------------------- ----------------- ------------- ----------- ---------- ------------- -------------- ---------- --------------
SQL_PLAN(STATEMENT_ID, PLAN_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_HASH_VALUE SQL ID SQL_TEXT
--------------- ---------------- --------------------------------------------------
12521 SYS .387451 .43431 25561 0 0 443 12521 3
SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'CHOOSE', 0, 0, NULL, 0, 3, 3, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'TABLE ACCESS', 'BY INDEX ROWID BATCHED', NULL, 'SYS', 'OBJ$', 'O@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 3, 1, 107, NULL, NULL, NULL, NULL, NULL, 21954, 3, NULL, NULL, NULL, NULL, 1, 'SEL$1', '<other_xml><info type="db_version">12.2.0.1</info><info type="parse_schema"><![C'), SQL_PLAN_ROW_TYPE(NULL, NULL, '02-JUN-17', NULL, 'INDEX', 'RANGE SCAN', NULL, 'SYS', 'I_OBJ1', 'O@SEL$1', NULL, 'INDEX (UNIQUE)', NULL, 1, 2, 1, 2, 1, 2, 1, NULL, NULL, NULL, NULL, NULL, NULL, 14443, 2, NULL, NULL, NULL, NULL, 1, 'SEL$1', NULL))
1072382624 87gaftwrm2h68 select o.owner#,o.name,o.namespace,o.remoteowner,o
.linkname,o.subname from obj$
DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'STS_87gaftwrm2h68',
basic_filter=>'plan_hash_value = ''1072382624'''
);
END;
col sql_id format a14
col sql_handle format a22
col plan_name format a32
col sql_text format a40
col ENABLED for a15
col ACCEPTED for a15
col SQL_TEXT for a40 wrap
select
DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) as sql_id,
sql_handle ,
plan_name ,
enabled ,
accepted ,
FIXED,
ORIGIN,
SQL_TEXT
from dba_sql_plan_baselines
where DBMS_SQL_TRANSLATOR.SQL_ID(sql_text) = '87gaftwrm2h68'
and origin like 'MANUA%'
--and created > sysdate -3
;
SQL ID SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIX ORIGIN SQL_TEXT
-------------- ---------------------- -------------------------------- --------------- --------------- --- ----------------------------- ----------------------------------------
87gaftwrm2h68 SQL_3e0587805c3ad254 SQL_PLAN_3w1c7h1f3pnkn77dddc30 YES YES NO MANUAL-LOAD-FROM-STS select o.owner#,o.name,o.namespace,o.rem
oteowner,o.linkname,o.subname from obj$
============3rd method
define sql_id='3kqrku32p6sfn' ----- For this sql !!!!
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'
col PARSING_SCHEMA_NAME for a20
select 'gv$dba_hist_sqlstat' source,h.snap_id,h.instance_number,
h.CON_ID,
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,
h.PARSING_SCHEMA_NAME,
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 1=1
and 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
--and PARSING_SCHEMA_NAME!='SYS'
;
Plan Execs Elap Secs CPU Secs IO Secs Clus Secs App Secs Conc Secs PLSQL Secs Java Secs
SOURCE SNAP_ID Inst CON_ID SNAP_TIME End Time SQL_ID Hash Value Total PARSING_SCHEMA_NAME Rows Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec Per Exec
------------------- ---------- ----- ---------- ---------------- ---------------- ------------- ---------- -------- -------------------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
gv$dba_hist_sqlstat 9656 1 0 07-11-2022 04:00 07-11-2022 05:00 3kqrku32p6sfn 1774581179 15 SYS 0 0.144 0.139 0.000 0.000 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9650 1 0 06-11-2022 22:00 06-11-2022 23:00 3kqrku32p6sfn 1774581179 3 SYS 0 0.125 0.121 0.002 0.000 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9651 1 0 06-11-2022 23:00 07-11-2022 00:00 3kqrku32p6sfn 1774581179 2 SYS 0 0.290 0.286 0.000 0.000 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9653 1 0 07-11-2022 01:00 07-11-2022 02:00 3kqrku32p6sfn 1774581179 3 SYS 0 0.165 0.160 0.000 0.000 0.000 0.000 0.000 0.000
gv$dba_hist_sqlstat 9655 1 0 07-11-2022 03:00 07-11-2022 04:00 3kqrku32p6sfn 1774581179 11 SYS 0 0.150 0.146 0.000 0.000 0.000 0.000 0.000 0.000
define v_sql_id='3kqrku32p6sfn'
define v_fixed='YES'
define v_enabled='YES'
define v_begin_snap=9650
define v_end_snap=9651
define v_plan_hash_value=1774581179
set serveroutput on
declare
rc integer;
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
v_sql_handle varchar2(30);
v_plan_name varchar2(30);
begin
-- Step 1 : Create SQL Tuning SET
dbms_sqltune.create_sqlset(
sqlset_name => '&v_sql_id'||'_spm',
description => 'SQL Tuning Set to create SQL baseline for '||'&v_sql_id');
-- Step 2 : Select sql_id and plan_hash_value from AWR
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => '&v_begin_snap',
end_snap => '&v_end_snap',
basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
attribute_list => 'ALL')) p;
-- Step 3 : Load the AWR cursor into SQLSET
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name=>'&v_sql_id'||'_spm',
populate_cursor=> baseline_ref_cur);
--+ Step 3+: Close cursor and check
close baseline_ref_cur;
select count(*)
into rc
from dba_sqlset_statements
where sqlset_name = '&v_sql_id' || '_spm'
and sql_id = '&v_sql_id'
and plan_hash_value = &v_plan_hash_value;
if rc = 0
then DBMS_SQLTUNE.drop_sqlset('fxgzfhx4fr9rv'||'_spm');
raise NO_DATA_FOUND;
end if;
-- Step 4 : Create baseline; that is loading plans from sqlset into SPM
rc := dbms_spm.load_plans_from_sqlset(
sqlset_name => '&v_sql_id'||'_spm',
basic_filter => 'sql_id='||CHR(39)||'&v_sql_id'||CHR(39)||' and plan_hash_value=&v_plan_hash_value',
fixed => '&v_fixed',
enabled => '&v_enabled');
--+ Step 5: Get baseline names
select sql_handle, plan_name
into v_sql_handle, v_plan_name
from dba_sql_plan_baselines bl, dba_hist_sqltext sa
where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
and sa.sql_id = '&v_sql_id'
--and origin = 'MANUAL-LOAD'
and created > sysdate - 15/24/60/60;
dbms_output.put_line('');
dbms_output.put_line('Baseline '||v_sql_handle||' '||v_plan_name||' was created from AWR');
dbms_output.put_line('for SQL_ID='||'&v_sql_id'||', SQL_PLAN_HASH='||'&v_plan_hash_value');
end;
/
Baseline SQL_80f7845a69e12ff7 SQL_PLAN_81xw4b9ny2bzrf28fb0a6 was created from AWR
for SQL_ID=3kqrku32p6sfn, SQL_PLAN_HASH=1774581179
col sql_text for a50 wrap
col enabled for a15
col accepted for a15
col fixed for a15
col CREATED for a30
col PLAN_NAME for a30
select CREATED,sa.sql_id,sql_handle, plan_name,origin, enabled, accepted, fixed, sa.sql_text from dba_sql_plan_baselines bl, dba_hist_sqltext sa
where DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(bl.sql_text) = DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sa.sql_text)
and sa.sql_id = '&v_sql_id'
--and origin = 'MANUAL-LOAD'
and created > sysdate - 1;
CREATED SQL_ID SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED FIXED SQL_TEXT
------------------------------ ------------- ------------------------------ ------------------------------ ----------------------------- --------------- --------------- --------------- --------------------------------------------------
15-NOV-22 05.50.22.000000 AM 3kqrku32p6sfn SQL_80f7845a69e12ff7 SQL_PLAN_81xw4b9ny2bzrf28fb0a6 MANUAL-LOAD-FROM-STS YES YES YES MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force'
'false') */ INTO OPTSTAT_USER_
Friday, 11 November 2022
Oracle long running SQL
Oracle long running SQL ...
long running SQL long running SQL queries
set pages 300 lines 300
col OPNAME for a10
col SID form 9999
col SERIAL form 9999999
col PROGRAM for a10
col USERNAME for a10
col SQL_TEXT for a40
col START_TIME for a10
col LAST_UPDATE_TIME for a10
col TARGET for a25
col MESSAGE for a25
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
SELECT
--inst_id,sid, serial#,
''''||sid ||','||serial#||',@'||inst_id ||'''' kill,
sql_id, opname, username, target, sofar, totalwork, start_time,last_update_time,round(time_remaining/60,2) "REMAIN MINS", round(elapsed_seconds/60,2) "ELAPSED MINS", round((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS", ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE", message
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND sofar<>totalwork
AND time_remaining > 0
/
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A50
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
col kill for a17
SELECT
--s.sid, s.serial#,
''''||s.sid ||','||s.serial#||',@'||s.inst_id ||'''' kill,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM gv$session s, gv$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND TOTALWORK != 0;
set linesize 500 pagesize 500
col MESSAGE for a50
col "USERNAME| SID,SERIAL#,inst" for a40
col "STARTED|MIN_ELAPSED|REMAIN" for a25
select USERNAME||'| '||''''||sid ||','|| serial#||',@'||inst_id ||'''' "USERNAME| SID,SERIAL#,inst",SQL_ID,round(SOFAR/TOTALWORK*100,2) "%DONE"
,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) "STARTED|MIN_ELAPSED|REMAIN" ,SQL_ID,MESSAGE
from gv$session_longops
where SOFAR/TOTALWORK*100 <>'100'
and TOTALWORK <> '0'
-- and MESSAGE not like 'RMAN:%'
order by "STARTED|MIN_ELAPSED|REMAIN" desc, "USERNAME| SID,SERIAL#,inst";
set lines 500
col opname format a35
col target format a25
col units format a10
col kill for a17
col message for a20
col kill for a17
select * from (
select
''''||sid ||','||serial#||',@'||inst_id ||'''' kill , sql_id,
opname, target, sofar, totalwork, round(sofar/totalwork, 4)*100 pct, units, round(elapsed_seconds/60,2) elap_min, round(time_remaining/60,2) remaining_min
,sql_plan_hash_value, sql_plan_operation, sql_plan_options, sql_plan_line_id, to_char(sql_exec_start, 'dd-mm-yyyy hh24:mi:ss') sql_exec_start
,message
from gv$session_longops
WHERE sofar < totalwork
order by start_time desc)
/
=====
set linesize 200 pages 9999
column sid format 9999999
column RT format a10
column ET format a10
column opname format a30
column target format a20
column pct_complete format 09D00 heading '%%%%'
col kill for a17
select
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill ,con_id, sql_id, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, opname, round((100 * sofar)/totalwork, 2) pct_complete,
( extract(day from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)))
) ET,
( extract(day from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)))
) RT
from gv$session_longops
where time_remaining > 0
;
====without con_id
set linesize 200 pages 9999
column sid format 9999999
column RT format a10
column ET format a10
column opname format a30
column target format a20
column pct_complete format 09D00 heading '%%%%'
col kill for a17
select
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill
--,con_id
, sql_id, to_char(start_time, 'DD/MM/YYYY HH24:MI:SS') start_time, opname, round((100 * sofar)/totalwork, 2) pct_complete,
( extract(day from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(elapsed_seconds, 'second') - systimestamp)))
) ET,
( extract(day from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ' ' ||
extract(hour from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
extract(minute from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)) || ':' ||
round(extract(second from (systimestamp + numtodsinterval(time_remaining, 'second') - systimestamp)))
) RT
from gv$session_longops
where time_remaining > 0
;
=======define 1='dgq1rfu9bzasw'
select sql_id,dbms_lob.substr(SQL_FULLTEXT,4000) sql_text from v$sql where sql_id like '%&&1%'
and rownum <2
union all
select sql_id,dbms_lob.substr(sql_text,4000) sql_text from dba_hist_sqltext where sql_id like '%&&1%'
and rownum <2
;
ORA-12537: TNS:connection closed
ORA-12537: TNS:connection closed
sqlplus 'sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=xyrac02.***.***.*******.com)(Port=1521))(CONNECT_DATA=(SID=xyrac2))) as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 11 06:50:24 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: ERROR: ORA-12537: TNS:connection closed <<<< [oracle@xyrac02 tmp]$ cd $ORACLE_HOME [oracle@xyrac02 dbhome_1]$ ls -lrt bin/oracle -rwxrwsr-x 1 oracle oinstall 408898240 Apr 19 2019 bin/oracle [oracle@xyrac02 dbhome_1]$ chmod 6751 bin/oracle <<<< change to !!!!!!!!!!!!!!!!!!! [oracle@xyrac02 dbhome_1]$ ls -lrt bin/oracle -rwsr-s--x 1 oracle oinstall 408898240 Apr 19 2019 bin/oracle tail -100f $ORACLE_BASE/diag/tnslsnr/$(hostname -s)/listener/trace/listener.log
now working !!!! sqlplus 'sys@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=xyrac02.***.***.*******.com)(Port=1521))(CONNECT_DATA=(SID=xyrac2))) as sysdba' SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 11 06:57:00 2022 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Oracle Hub Report
Oracle Performance Hub Report.
Performance Hub Report
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later <<<<<<<<<<<<<<
Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)
alter session set "_push_join_predicate" = FALSE ; --- if report running slow try this!!!
@?/rdbms/admin/perfhubrpt.sql
===
select DBID from v$database;
-- mm/dd/yyyy
@?/rdbms/admin/perfhubrpt.sql all 1825264339 1 '11/06/2022 08:00:00' '11/06/2022 09:00:00' 1_PerfHub_06.html
???
set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool perfhub_history.html
select dbms_perf.report_perfhub(is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
spool off
set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool sql_details_history.html
select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;
spool off
Subscribe to:
Posts (Atom)
