--- 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 swhere 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_numberorder 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 staleSELECT 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 ALLSELECT 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 ALLSELECT 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. /* Formatted on 1/23/2018 10:06:30 AM (QP5 v5.300) */ 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 > 0ORDER 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 onexec 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 ondeclarebaseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;beginopen baseline_ref_cur forselect 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;/ declaremy_int pls_integer;beginmy_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 profileEXEC 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 tableexpdp 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 tableDECLARE 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/* Formatted on 4/2/2020 10:24:44 AM (QP5 v5.326) */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;
