--- how to find SQL plan changes
select to_char(min(s.end_interval_time),'YYYY-MM-DD HH24:MI') sample_end
--, s.snap_id
, q.sql_id
, q.plan_hash_value
, q.instance_number inst_id
, sum(q.EXECUTIONS_DELTA) execs9
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000/1000),3) ela_sec
, round((sum(CPU_TIME_delta)/greatest(sum(executions_delta),1)/1000/1000),3) cpu_sec
, round((sum(rows_processed_delta)/greatest(sum(executions_delta),1)),1) avg_rows
, round((sum(fetches_delta)/greatest(sum(executions_delta),1)),1) avg_fetches
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1)) lio_per_exec
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1)) pio_per_exec
, round((sum(iowait_delta)/greatest(sum(executions_delta),1)/1000/1000),1) iowait_sec
-- , round((sum(clwait_delta)/greatest(sum(executions_delta),1)/1000),1) clwait_sec
-- , round((sum(apwait_delta)/greatest(sum(executions_delta),1)/1000),1) apwait_sec
-- , round((sum(ccwait_delta)/greatest(sum(executions_delta),1)/1000),1) ccnwait_sec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&1')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and s.end_interval_time between ( sysdate - &2 ) and ( sysdate - &2 + &3 )
group by s.snap_id, q.sql_id, q.plan_hash_value, q.instance_number
order by s.snap_id, q.sql_id, q.plan_hash_value, q.instance_number
-- if you find a bad plan then try purging it
select inst_id,plan_hash_value,'exec sys.DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from gV$SQL where sql_id in ('1pfb281d5f37c') order by plan_hash_value;
-- To check stats and collect stats if stale
SELECT a.object_owner,
a.object_name,
a.object_type,
b.stale_stats
FROM v$sql_plan a
JOIN dba_tab_statistics b
ON a.object_owner = b.owner AND a.object_name = b.TABLE_NAME
WHERE b.object_type = 'TABLE'
AND a.sql_id = TRIM ('&1')
AND (stale_stats IS NULL OR stale_stats = 'YES')
UNION ALL
SELECT a.object_owner,
a.object_name,
a.object_type,
b.stale_stats
FROM v$sql_plan a
JOIN dba_tab_statistics b
ON a.object_owner = b.owner AND a.object_name = b.PARTITION_NAME
WHERE b.object_type = 'PARTITION'
AND a.sql_id = TRIM ('&1')
AND (stale_stats IS NULL OR stale_stats = 'YES')
UNION ALL
SELECT a.object_owner,
a.object_name,
b.object_type,
b.stale_stats
FROM v$sql_plan a
JOIN dba_ind_statistics b
ON a.object_owner = b.owner AND a.object_name = b.index_name
WHERE b.object_type!='SUBPARTITION' and a.sql_id = TRIM ('&1')
AND (stale_stats IS NULL OR stale_stats = 'YES');
-- if you decide to create a SQL baseline after all analysis
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('17n0jry7qhtdx'));
-- look up AWR history to find SQL detailed statistics. Pick up the snapshots in which the SQL has had a good or better plan.
SELECT ss.snap_id,
ss.instance_number,
sn.end_interval_time,
ss.plan_hash_value,
ss.elapsed_time_delta / 1000 / 1000 / ss.executions_delta
avg_time_per_run,
elapsed_time_total / 1000 / 1000 total_elapsed_time,
ss.executions_delta exe_times,
ss.fetches_delta,
ss.rows_processed_delta processed_rows,
iowait_delta / ss.executions_delta per_IO_wait,
ss.cpu_time_delta / ss.executions_delta Per_CPU_time,
disk_reads_delta / ss.executions_delta Per_disk_R,
buffer_gets_delta / ss.executions_delta Per_buffer_R
FROM dba_hist_sqlstat ss, dba_hist_snapshot sn
WHERE ss.snap_id = sn.snap_id
AND ss.instance_number = sn.instance_number
AND ss.sql_id = '&sql_id'
AND ss.executions_delta > 0
ORDER BY ss.snap_id DESC, 1;
-- snap_id= 121097
-- create a sql tuning set. You can name it to whatever that is easy to find its purpose.
set serveroutput on
exec dbms_sqltune.create_sqlset(sqlset_name => '&SQL_set_name',description => '&SQL_set_name');
-- load SQLs that you want to tune in the above created SQL tuning set. Enter the snapshot ID in which the target sql had a good plan. Enter the ID of the SQL.
set serveroutput on
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select VALUE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&snapshot_start, &snapshot_end,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&SQL_set_name', baseline_ref_cur);
end;
/
declare
my_int pls_integer;
begin
my_int := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&SQL_set_name',
basic_filter => 'sql_id=''&sql_id''',
sqlset_owner => 'AIUDKC3',
fixed => 'NO',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_int);
end;
/
select * from dba_sql_plan_baselines order by created desc;
-- Below is mostly not needed but if you came to a conclusion and looking to copy SQL profile from one env. to other env.
-- Run it in theta from where you want to export sql profile
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB_86dxp1c32fd42');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_036f7c7ee4560000', staging_table_name => 'PROFILE_STGTAB_86dxp1c32fd42');
select * from PROFILE_STGTAB;
-- export and improt above table
expdp tables=PROFILE_STGTAB_86dxp1c32fd42 dumpfile=PROFILE_STGTAB_86dxp1c32fd42.dmp directory=scripts;
impdp tables=PROFILE_STGTAB_86dxp1c32fd42 dumpfile=PROFILE_STGTAB_86dxp1c32fd42.dmp directory=conv_dir;
-- ON target database
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB');
select * from PROFILE_STGTAB; -- PROFILE_6zhhcaxya0z18
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace=>true,staging_table_name => 'PROFILE_STGTAB');
--- if there is no good plan in AWR snaps and you found a good baseline in some other env. and decide to copy here
select * from dba_sql_plan_baselines where plan_name='SQL_PLAN_7mduu8xwt7wzx8c7c867d';
-- create staging table on source
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE (table_name => 'SPB_86dxp1c32fd42',
table_owner => 'AIUDKC3',
tablespace_name => 'USERS');
END;
-- packing SQL baseline to staging table
DECLARE
my_plans NUMBER;
BEGIN
my_plans :=
DBMS_SPM.PACK_STGTAB_BASELINE (
table_name => 'SPB_86dxp1c32fd42',
enabled => 'yes',
table_owner => 'AIUDKC3',
plan_name => 'SQL_PLAN_7mduu8xwt7wzx8c7c867d',
sql_handle => 'SQL_79b75a477993f3fd');
END;
/
select * from SPB_86dxp1c32fd42;
-- export and improt above table
select * from dba_directories where directory_name='EXPDP_DUMP';
expdp tables=SPB_86dxp1c32fd42 dumpfile=SPB_86dxp1c32fd42.dmp directory=EXPDP_DUMP;
impdp tables=SPB_86dxp1c32fd42 dumpfile=SPB_86dxp1c32fd42.dmp directory=EXPDP_DUMP;
-- unpack sql baseline
SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked :=
DBMS_SPM.unpack_stgtab_baseline (table_name => 'SPB_86dxp1c32fd42',
table_owner => 'AIUDKC3');
DBMS_OUTPUT.put_line ('Plans Unpacked: ' || l_plans_unpacked);
END;
/
select inst_id,plan_hash_value,'exec sys.DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from gV$SQL where sql_id in ('86dxp1c32fd42') order by plan_hash_value;
