Oracle AWR: differenze tra le versioni
Jump to navigation
Jump to search
(Creata pagina con "<pre> -- -- https://rajiboracle.blogspot.com/2017/05/how-to-load-sql-plan-baseline-from-awr.html -- sql_id hash_plan_value snap_begin snap_end exec DBMS_SQLTUNE.DROP_SQLSET (...") |
Nessun oggetto della modifica |
||
Riga 1: | Riga 1: | ||
==Cercare piani di esecuzione storici== |
|||
-- |
|||
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&1')); |
|||
==Cercare sqlid in snapshot== |
|||
set pagesize 200 |
|||
set linesize 200 |
|||
col BEGIN_INTERVAL_TIME for a25 |
|||
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 SQL_ID = '&1' |
|||
AND SS.SNAP_ID = S.SNAP_ID |
|||
AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER |
|||
AND EXECUTIONS_DELTA > 0 |
|||
ORDER BY 1, 2, 3; |
|||
==forzare piano di esecuzione== |
|||
<pre> |
<pre> |
||
-- |
-- |
Versione delle 09:56, 15 mag 2020
Cercare piani di esecuzione storici
-- select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&1'));
Cercare sqlid in snapshot
set pagesize 200 set linesize 200 col BEGIN_INTERVAL_TIME for a25 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 SQL_ID = '&1' AND SS.SNAP_ID = S.SNAP_ID AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER AND EXECUTIONS_DELTA > 0 ORDER BY 1, 2, 3;
forzare piano di esecuzione
-- -- https://rajiboracle.blogspot.com/2017/05/how-to-load-sql-plan-baseline-from-awr.html -- sql_id hash_plan_value snap_begin snap_end exec DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_&1'); exec DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'STS_&1', description => 'sts from awr emilio &1 &2 &3 &4'); DECLARE cur sys_refcursor; BEGIN OPEN cur FOR SELECT VALUE(P) FROM TABLE( dbms_sqltune.select_workload_repository(begin_snap=>&3, end_snap=>&4,basic_filter=>'sql_id = ''&1''',attribute_list=>'ALL') ) p; DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_&1', populate_cursor=>cur); CLOSE cur; END; / DECLARE my_plans pls_integer; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'STS_&1', basic_filter=>'plan_hash_value = ''&2''', sqlset_owner => 'SYS', fixed => 'YES', enabled => 'YES' ); END; /