1. Check the No.of PHV with Timestamp:
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
col PLAN_HASH_VALUE for 999999999999999
select a.snap_id,a.sql_id,a.plan_hash_value,a.version_count,b.begin_interval_time,b.end_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.snap_id=b.snap_id
and a.sql_id='&SQL_ID'
order by a.snap_id;
#######################################################################################################
2. Listout No.of PHV:
SELECT PLAN_HASH_VALUE,
sum(EXECUTIONS_DELTA) as EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) cROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
FROM dba_hist_sqlstat
WHERE SQL_ID in ('&SQL_ID')
GROUP by PLAN_HASH_VALUE
ORDER BY 4;
SELECT PLAN_HASH_VALUE,
sum(EXECUTIONS_DELTA) as EXECUTIONS
,sum(END_OF_FETCH_COUNT_DELTA) as COMPLETED_EXECS
, sum(ROWS_PROCESSED_DELTA) cROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
FROM dba_hist_sqlstat
WHERE SQL_ID in ('&SQL_ID')
GROUP by PLAN_HASH_VALUE
ORDER BY 4;
PLAN_HASH_VALUE EXECUTIONS CROWS CPU_MINS ELA_MINS
---------------- ---------- ---------- ---------- ----------
3302568229 154 4 2 2
1437720039 1553928 588014 4829 5022--
993417189 467945 500808 14126 16326
1921343366 1084236 763391 21195 21484
####################################################################
3. Check best PHV:
=================
DEFINE vplan_hash_value
column phv FORMAT 9999999999 old_value vplan_hash_value
WITH best_plan_hash_value AS
(SELECT PLAN_HASH_VALUE,
sum(EXECUTIONS_DELTA) as EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) cROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
FROM dba_hist_sqlstat
WHERE SQL_ID in ('&SQL_ID')
GROUP by PLAN_HASH_VALUE
ORDER BY 4)
SELECT plan_hash_value AS phv FROM best_plan_hash_value
where executions > 0
and ROWNUM <2;
PHV
-----------
3302568229
###############################################################################
4. Create SQLSET for SQL_ID:
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET('&Tuning_Set_Name','&Description','&SCHEMA');
Enter value for tuning_set_name: g31uqsntjrayf_STS
Enter value for description: g31uqsntjrayf_1437720039_TUNING
Enter value for schema: SYS
PL/SQL procedure successfully completed.
###########################################################3
5. Capute SQL_PHV from AWR
SQL>
declare
baseline_ref_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cursor for
select VALUE(p)
from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&Begin_SnapId,&End_SnapId, 'sql_id='||CHR(39)||'&SQL_ID'||CHR(39)||' and plan_hash_value=&PHV',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET('&Tuning_Set_Name', baseline_ref_cursor, sqlset_owner => '&SCHEMA');
end;
/
36745
38411
SQL> 2 3 4 5 6 7 8 9 Enter value for begin_snapid: 33355
Enter value for end_snapid: 33356
Enter value for sql_id: g31uqsntjrayf
Enter value for phv: 1437720039
old 6: from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&Begin_SnapId,&End_SnapId, 'sql_id='||CHR(39)||'&SQL_ID'||CHR(39)||' and plan_hash_value=&PHV',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
new 6: from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(33355,33356, 'sql_id='||CHR(39)||'g31uqsntjrayf'||CHR(39)||' and plan_hash_value=1437720039',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;
Enter value for tuning_set_name: g31uqsntjrayf_STS
Enter value for schema: SYS
old 7: DBMS_SQLTUNE.LOAD_SQLSET('&Tuning_Set_Name', baseline_ref_cursor, sqlset_owner => '&SCHEMA');
new 7: DBMS_SQLTUNE.LOAD_SQLSET('g31uqsntjrayf_STS', baseline_ref_cursor, sqlset_owner => 'SYS');
PL/SQL procedure successfully completed.
##############################################################
6. Veriy SQL SET:
select name, owner, description, created, statement_Count from dba_sqlset;
select fixed, enabled, accepted,sql_handle, plan_name,to_char(created,'hh24:mi:ss dd-mon-yyyy'), origin from dba_sql_plan_baselines order by created;
select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where PLAN_HASH_VALUE=1437720039;
################################################################
7. Load SQL Plan from SQ SET
set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => '&Tuning_Set_Name',
sqlset_owner => '&SCHEMA',
fixed => 'YES',
enabled => 'NO');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/
select fixed, enabled, accepted,sql_handle, plan_name,to_char(created,'hh24:mi:ss dd-mon-yyyy'), origin from dba_sql_plan_baselines order by created;
##################################################################
8. Create Staging table:
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'MIGRATION_PROD',
table_owner => 'SYSTEM'
);
END;
/?
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'GEBISP06_PROD_BASE_STG',
table_owner => 'SYSTEM'
);
END;
/
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'CHICAGO_SBP_BKP',
table_owner => 'SYSTEM'
);
END;
/
######################################################
9. Pack SQL_plan to staging table and verify
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'GEBISP06_PROD_BASE_STG',
table_owner => 'SYSTEM',
sql_handle => '&SQL_HANDLE',
plan_name => '&PLAN_NAME');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
select count(*) from SYSTEM.GEBISP06_PROD_BASE_STG;
########################################################
10 .Export Staging table:
SQL> SQL>
FIX ENA ACC SQL_HANDLE PLAN_NAME TO_CHAR(CREATED,'HH24:MI:SSDD ORIGIN
--- --- --- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------------------------- -----------------------------
NO NO YES SQL_25f18c77f2eef3e0 SQL_PLAN_2bwccfztfxwz027537b17 15:09:02 02-feb-2023 MANUAL-LOAD-FROM-STS
NO YES NO SQL_25f18c77f2eef3e0 SQL_PLAN_2bwccfztfxwz045ece29b 15:09:06 02-feb-2023 AUTO-CAPTURE
SQL> set linesize 400
SQL> col PLAN_NAME for a40
SQL> /
FIX ENA ACC SQL_HANDLE PLAN_NAME TO_CHAR(CREATED,'HH24:MI:SSDD ORIGIN
--- --- --- ------------------------------ ---------------------------------------- ----------------------------- -----------------------------
NO NO YES SQL_25f18c77f2eef3e0 SQL_PLAN_2bwccfztfxwz027537b17 15:09:02 02-feb-2023 MANUAL-LOAD-FROM-STS
NO YES NO SQL_25f18c77f2eef3e0 SQL_PLAN_2bwccfztfxwz045ece29b 15:09:06 02-feb-2023 AUTO-CAPTURE
SQL>
SQL> select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_id='&SQL';
Enter value for sql: g31uqsntjrayf
old 1: select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_id='&SQL'
new 1: select sql_id, plan_hash_value, exact_matching_signature, sql_plan_baseline from v$sql where sql_id='g31uqsntjrayf'
SQL_ID PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SQL_PLAN_BASELINE
------------- ---------------- ------------------------ --------------------------------------------------------------------------------------------------------------------------------
g31uqsntjrayf 1921343366 2.7341E+18
g31uqsntjrayf 1921343366 2.7341E+18
select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SQL_25f18c77f2eef3e0'));
BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'g31uqsntjrayf_TEST',
table_owner => 'SYSTEM'
);
END;
/??
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'g31uqsntjrayf_TEST',
table_owner => 'SYSTEM',
sql_handle => 'SQL_fcaf8920b0661969',
plan_name => 'SQL_PLAN_gtbw942s6c6b96a527c22');
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'g31uqsntjrayf_TEST',
table_owner => 'SYSTEM',
sql_handle=> 'SQL_fcaf8920b0661969',
plan_name=> 'SQL_PLAN_gtbw942s6c6b96a527c22');?
DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/
SQL> select distinct(sql_id) from dba_hist_sqlstat where PLAN_HASH_VALUE='&phv';
Enter value for phv: 1437720039
old 1: select distinct(sql_id) from dba_hist_sqlstat where PLAN_HASH_VALUE='&phv'
new 1: select distinct(sql_id) from dba_hist_sqlstat where PLAN_HASH_VALUE='1437720039'
SQL_ID
-------------
crrya6w2pzs5j
g31uqsntjrayf
4abzfgms9u202
dtbx2wcmhrquk
cjn1djmq4j6y5
9db3sbbyd2nyb
0z3pj49gznvjy
43rvp0pxnjv52
8dd5whu064yq1
b2qyvhu1d911g
10 rows selected.
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'cjn1djmq4j6y5_STS');
END;
var pbsts varchar2(30);
exec :pbsts := dbms_spm.alter_sql_plan_baseline('SQL_f2c79d3d5ed40b28','SQL_PLAN_g5jwx7pgd82t806248af5','ENABLED','NO');
No comments:
Post a Comment