---------------------------1------------
sqlplus -S /nolog <conn / as sysdba
variable v_sqlset_name varchar2(30);
exec :v_sqlset_name:='sts_up_10g';
--create sts in 10g
exec dbms_sqltune.create_sqlset(sqlset_name=>:v_sqlset_name,description=>'11g upgrade spa test sts',sqlset_owner=>'DBA_OWNER');
--create temp table to store sts
Begin
dbms_sqltune.create_stgtab_sqlset(table_name => 'STS_STGTAB' ,schema_name => 'DBA_OWNER');End;
/
--mark the curr scn
col CURRENT_SCN for 9999999999999999999999999999999999
select CURRENT_SCN from v$database;
--Step1：load sql from current cache to sts in 10g
DECLARE
STSCUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN STSCUR FOR
SELECT VALUE(P)
FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME IN (''EGISDSG'',''ELISTJSOPR'',''FUNDETL'',''INVETL'',''JTDSG'',''JTTJS'',''PARDBKTL'',''SECUETL'',''SMDBCDE'',''SMDBDATA'',''SMDBETL'',''SMDBFCDE'',''SMDBFDATA'',''SMDBFDOWNLOAD'',''SMDBFOPR'',''SMDBGCDE'',''SMDBGCU'',''SMDBGDATA'',''SMDBGDOWNLOAD'',
''SMDBGOPR'',''SMDBLDATA'',''SMDBLOGTMP'',''SMDBMCDE'',''SMDBMDATA'',''SMDBOPR'',''SMDBTDATA'',''SMDBTDOWNLOAD'',''SMDBTOPR'',''SMDBTRG'',''SMDBZCDE'',''SMDBZDATA'',
''SMDBZDOWNLOAD'',''SMDBZOPR'',''TCDMSDSG'',''TIRPETL'',''TPAMSDSG'',''TRMPETL'',''TWSSMDBDATA'',''TWSSMDBOPR'',''YHDSG'')
AND PLAN_HASH_VALUE <> 0 AND UPPER(SQL_TEXT) NOT LIKE ''INSERT%INTO%VALUES%'' AND UPPER(SQL_TEXT) NOT LIKE ''SELECT%NEXTVAL FROM DUAL%'' ')) P;-- POPULATE THE SQLSET
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => :v_sqlset_name,
POPULATE_CURSOR => STSCUR,
COMMIT_ROWS => 100,
SQLSET_OWNER => 'DBA_OWNER');CLOSE STSCUR;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;END;
/
--Step2:load sql from current cache by increase to sts in 10g ;the Sessions will continue for one day
BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(SQLSET_NAME => :v_sqlset_name,
TIME_LIMIT => 86400,
REPEAT_INTERVAL => 3600,
CAPTURE_OPTION => 'MERGE',
CAPTURE_MODE => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS,
BASIC_FILTER => 'PARSING_SCHEMA_NAME IN (''EGISDSG'',''ELISTJSOPR'',''FUNDETL'',''INVETL'',''JTDSG'',''JTTJS'',''PARDBKTL'',''SECUETL'',''SMDBCDE'',''SMDBDATA'',''SMDBETL'',''SMDBFCDE'',''SMDBFDATA'',''SMDBFDOWNLOAD'',''SMDBFOPR'',''SMDBGCDE'',''SMDBGCU'',''SMDBGDATA'',''SMDBGDOWNLOAD'',
''SMDBGOPR'',''SMDBLDATA'',''SMDBLOGTMP'',''SMDBMCDE'',''SMDBMDATA'',''SMDBOPR'',''SMDBTDATA'',''SMDBTDOWNLOAD'',''SMDBTOPR'',''SMDBTRG'',''SMDBZCDE'',''SMDBZDATA'',
''SMDBZDOWNLOAD'',''SMDBZOPR'',''TCDMSDSG'',''TIRPETL'',''TPAMSDSG'',''TRMPETL'',''TWSSMDBDATA'',''TWSSMDBOPR'',''YHDSG'')
AND PLAN_HASH_VALUE <> 0 AND UPPER(SQL_TEXT) NOT LIKE ''INSERT%INTO%VALUES%'' AND UPPER(SQL_TEXT) NOT LIKE ''SELECT%NEXTVAL FROM DUAL%''',
SQLSET_OWNER => 'DBA_OWNER');END;
/
--pack the sts to temp table
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name=>:v_sqlset_name,sqlset_owner=>'DBA_OWNER',staging_table_name=>'STS_STGTAB',staging_schema_owner => 'DBA_OWNER');
exit
SQLEnd
-----------------------------------------------------------------------------2------------------------------
---topsql_9i
create table TOPSQL_9i
(
HASH_VALUE VARCHAR2(100),
OPID INTEGER,
PLAN_HASH_VALUE VARCHAR2(100),
EXPLAIN_PLAN_OPERATION VARCHAR2(100),
EXPLAIN_PLAN_OBJECT VARCHAR2(100),
ROWS_PROCESSED VARCHAR2(100),
BUFFER_GETS VARCHAR2(100),
EXECUTIONS VARCHAR2(100),
CHILD_NUMBER VARCHAR2(100),
SQL_TEXT VARCHAR2(4000),
IS_EQUAL INT,
IS_11GEXCELLENT INT,
IS_TUNED INT
);
insert into topsql_9i (HASH_VALUE,OPID,PLAN_HASH_VALUE,EXPLAIN_PLAN_OPERATION,EXPLAIN_PLAN_OBJECT,ROWS_PROCESSED,BUFFER_GETS,EXECUTIONS,CHILD_NUMBER,SQL_TEXT)
select vs.HASH_VALUE,sp.ID,vs.PLAN_HASH_VALUE, lpad(' ', 1 * (depth - 1)) || operation||decode(options, null,null,' '|| options) as "EXPLAIN PLAN OPERATION" ,
object_name as "EXPLAIN_PLAN_OBJECT",vs.ROWS_PROCESSED,vs.BUFFER_GETS,vs.EXECUTIONS,vs.CHILD_NUMBER,vs.SQL_TEXTfrom v$sql_plan@to_gccsh sp,v$sql@to_gccsh vs,
(
---get top exec sql all:
Select distinct hash_value from (
select a.hash_value,a.sql_text,
a.executions,
buffer_gets,
per_buffer_gets,
round(a.pr/b.total_exec, 5) as cumulate_exec_percent,
round(a.re, 5) as exec_percentfrom (select hash_value,sql_text,
EXECUTIONS,
buffer_gets,
per_buffer_gets,
sum(executions) over(order by executions desc) pr,
RATIO_TO_REPORT(executions) OVER() as re
from (select *
from (select hash_value,sql_text, executions,buffer_gets,buffer_gets/decode(nvl(executions,1),0,1,executions) per_buffer_gets
from v$sql@to_gccsh
where PARSING_SCHEMA_ID in
(select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER') )
order by executions desc)
---where rownum < 1000
)
order by executions desc) A,
(select sum(executions) total_exec from v$sql@to_gccsh where PARSING_SCHEMA_ID in (select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))) B ) where cumulate_exec_percent<=0.99
union
---get top cpu sql all:
Select distinct hash_value
from (select a.hash_value,a.sql_text,
a.cpu_time,
round(a.pr / b.total_exec, 5) as cumulate_cpu_percent,
round(a.re, 5) as cpu_percent
from (select hash_value,sql_text,
cpu_time,
sum(cpu_time) over(order by cpu_time desc) pr,
RATIO_TO_REPORT(cpu_time) OVER() as re
from (select * from (select hash_value,sql_text, cpu_time
from v$sql@to_gccsh
where PARSING_SCHEMA_ID in
(select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))
order by cpu_time desc)
---where rownum < 1000
)
order by cpu_time desc) A,
(select sum(cpu_time) total_exec
from v$sql@to_gccsh
where PARSING_SCHEMA_ID in (select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))) B ) where cumulate_cpu_percent <= 0.99
union
---get top BUFFER_GETS sql all:
Select distinct hash_value
from (select a.hash_value,a.sql_text,
a.BUFFER_GETS,
round(a.pr / b.total_exec, 5) as cumulate_BUFFER_GETS_percent,
round(a.re, 5) as BUFFER_GETS_percent
from (select hash_value,sql_text,
BUFFER_GETS,
sum(BUFFER_GETS) over(order by BUFFER_GETS desc) pr,
RATIO_TO_REPORT(BUFFER_GETS) OVER() as re
from (select * from (select hash_value,sql_text, BUFFER_GETS
from v$sql@to_gccsh
where buffer_gets>0 and PARSING_SCHEMA_ID in
(select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))
order by BUFFER_GETS desc)
---where rownum < 1000
)
order by BUFFER_GETS desc) A,
(select sum(BUFFER_GETS) total_exec
from v$sql@to_gccsh
where buffer_gets>0 and PARSING_SCHEMA_ID in (select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))) B ) where cumulate_BUFFER_GETS_percent <= 0.99) p99topsql
where PARSING_SCHEMA_ID in (select user_id from dba_users@to_gccsh where username in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER'))
and sp.HASH_VALUE=vs.HASH_VALUE
and sp.CHILD_NUMBER=vs.CHILD_NUMBER
and sp.HASH_VALUE=p99topsql.HASH_VALUE
order by vs.HASH_VALUE,vs.CHILD_NUMBER,sp.ID;
---topsql_11g
create table topsql_11g
(
SQL_ID varchar2(100),
HASH_VALUE varchar2(100),
OLD_HASH_VALUE varchar2(100),
OPID int,
PLAN_HASH_VALUE varchar2(100),
EXPLAIN_PLAN_OPERATION varchar2(100),
EXPLAIN_PLAN_OBJECT varchar2(100),
ROWS_PROCESSED VARCHAR2(100),
BUFFER_GETS varchar2(100),
EXECUTIONS varchar2(100),
CHILD_NUMBER varchar2(100),
SQL_TEXT varchar2(4000)
);
insert into topsql_11g(SQL_ID,HASH_VALUE,OLD_HASH_VALUE,OPID,PLAN_HASH_VALUE,EXPLAIN_PLAN_OPERATION,EXPLAIN_PLAN_OBJECT,ROWS_PROCESSED,BUFFER_GETS,EXECUTIONS,CHILD_NUMBER,SQL_TEXT)
select vs.SQL_ID,vs.HASH_VALUE,vs.OLD_HASH_VALUE,sp.ID as OPID,vs.PLAN_HASH_VALUE, lpad(' ', 1 * (depth - 1)) || operation||decode(options, null,null,' '|| options) as EXPLAIN_PLAN_OPERATION,
object_name as EXPLAIN_PLAN_OBJECT,vs.ROWS_PROCESSED,vs.BUFFER_GETS,vs.EXECUTIONS,vs.CHILD_NUMBER,vs.SQL_TEXTfrom v$sql_plan sp,v$sql vs,(---get top exec sql all:
Select distinct hash_value from (
select a.hash_value,a.sql_text,
a.executions,
buffer_gets,
per_buffer_gets,
round(a.pr / b.total_exec, 5) as cumulate_exec_percent,
round(a.re, 5) as exec_percentfrom (select hash_value,sql_text,
EXECUTIONS,
buffer_gets,
per_buffer_gets,
sum(executions) over(order by executions desc) pr,
RATIO_TO_REPORT(executions) OVER() as re
from (select *
from (select hash_value,sql_text, executions,buffer_gets,buffer_gets/decode(nvl(executions,1),0,1,executions) per_buffer_gets
from v$sql
where PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')
order by executions desc)
---where rownum < 1000
)
order by executions desc) A,
(select sum(executions) total_exec from v$sql where PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')) B ) where cumulate_exec_percent<=0.99
union
---get top cpu sql all:
Select distinct hash_value
from (select a.hash_value,a.sql_text,
a.cpu_time,
round(a.pr/b.total_exec, 5) as cumulate_cpu_percent,
round(a.re, 5) as cpu_percent
from (select hash_value,sql_text,
cpu_time,
sum(cpu_time) over(order by cpu_time desc) pr,
RATIO_TO_REPORT(cpu_time) OVER() as re
from (select * from (select hash_value,sql_text, cpu_time
from v$sql
where PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')
order by cpu_time desc)
---where rownum < 1000
)
order by cpu_time desc) A,
(select sum(cpu_time) total_exec
from v$sql
where PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')) B ) where cumulate_cpu_percent <= 0.99
union
---get top BUFFER_GETS sql all:
Select distinct hash_value
from (select a.hash_value,a.sql_text,
a.BUFFER_GETS,
round(a.pr / b.total_exec, 5) as cumulate_BUFFER_GETS_percent,
round(a.re, 5) as BUFFER_GETS_percent
from (select hash_value,sql_text,
BUFFER_GETS,
sum(BUFFER_GETS) over(order by BUFFER_GETS desc) pr,
RATIO_TO_REPORT(BUFFER_GETS) OVER() as re
from (select * from (select hash_value,sql_text, BUFFER_GETS
from v$sql
where buffer_gets>0 and PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')
order by BUFFER_GETS desc)
---where rownum < 1000
)
order by BUFFER_GETS desc) A,
(select sum(BUFFER_GETS) total_exec
from v$sql
where buffer_gets>0 and PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')) B ) where cumulate_BUFFER_GETS_percent <= 0.99) p99topsql
where PARSING_SCHEMA_name in ('ATLAS','CPDOPR','EWAPPS','EWCODE','GCCCDE','GCCETL','GCCIVRUSER','GCCIWSCDE','GCCIWSDATA','GCCIWSOPR','GCCJOB','GCCLOGTMP','GCCOPR','GCCREPRTIVRTJS','GCCTRG','GENESYSCDE','GENESYSDATA','GENESYSDM_REP','GENESYSDSREP','GENESYSDS_REP','GENESYSOPR','GENESYSREP','GENESYSUSER','GENE_CFG81','IVRCDE','IVRDATA','IVROPR','MISETL','SZGENESYSDSREP','SZGENESYSREP','WITNESSUSER')
and upper(vs.SQL_TEXT) not like '%SYS.%'
and sp.HASH_VALUE=vs.HASH_VALUE
and sp.CHILD_NUMBER=vs.CHILD_NUMBER
and sp.HASH_VALUE=p99topsql.HASH_VALUE
order by vs.HASH_VALUE,vs.CHILD_NUMBER,sp.ID
;
--create table sql_plan_compare as
select u9i.hash_value,
u11g.sql_id,
u9i.child_number,
u9i.explain_plan_operation u9i_explain_plan_operation,
u9i.explain_plan_object u9i_explain_plan_object,
u9i.buffer_gets u9i_buffer_gets,
u9i.rows_processed u9i_rows_processed,
u9i.buffer_gets/decode(u9i.rows_processed,0,1) u9i_buf_get_per_row,
(case
when nvl(upper(trim(u9i.explain_plan_operation)), 0) =nvl(upper(trim(u11g.explain_plan_operation)), 0) and
nvl(upper(trim(u9i.explain_plan_object)), 0) = nvl(upper(trim(u11g.explain_plan_object)), 0) then
'相等'
else
'不等'
end) ifequal,
u11g.buffer_gets/decode(u11g.rows_processed,0,1) u11g_buf_get_per_row,
u11g.buffer_gets u11g_buffer_gets,
u11g.ROWS_PROCESSED u11g_ROWS_PROCESSED,
u11g.explain_plan_operation u11g_explain_plan_operation,
u11g.explain_plan_object u11g_explain_plan_object,
u11g.sql_textfrom topsql_9i u9i, topsql_11g u11g
where u9i.hash_value = u11g.old_hash_value(+)
and u9i.child_number = u11g.child_number(+)
and u9i.opid = u11g.opid(+)
and not (nvl(u9i.is_equal, 0) = 1 or nvl(u9i.is_11gexcellent, 0) = 1 or nvl(u9i.is_tuned, 0) = 1)
order by u9i.hash_value, u9i.child_number, u9i.opid;
select username,elapsed_time,executions,buffer_gets,rows_processed,module,hash_value,sql_text from sql_9i_full s ,dba_users u
where hash_value in
(
Select distinct hash_value
from (
select a.hash_value,a.sql_text,
a.executions,
buffer_gets,
per_buffer_gets,
round(a.pr/b.total_exec, 5) as cumulate_exec_percent,
round(a.re, 5) as exec_percentfrom (select hash_value,sql_text,
EXECUTIONS,
buffer_gets,
per_buffer_gets,
sum(executions) over(order by executions desc) pr,
RATIO_TO_REPORT(executions) OVER() as re
from (select *
from (select hash_value,sql_text, executions,buffer_gets,buffer_gets/decode(nvl(executions,1),0,1,executions) per_buffer_gets
from sql_9i_full
order by executions desc)
)
order by executions desc) A,
(select sum(executions) total_exec from sql_9i_full) b
where rownum<100)union
Select distinct hash_value
from (
select a.hash_value,a.sql_text,
a.cpu_time,
round(a.pr / b.total_exec, 5) as cumulate_cpu_percent,
round(a.re, 5) as cpu_percent
from (select hash_value,sql_text,
cpu_time,
sum(cpu_time) over(order by cpu_time desc) pr,
RATIO_TO_REPORT(cpu_time) OVER() as re
from (select * from (select hash_value,sql_text, cpu_time
from sql_9i_full
order by cpu_time desc) )
order by cpu_time desc) A,
(select sum(cpu_time) total_exec
from sql_9i_full ) B
where rownum<100 )union
Select distinct hash_value
from (
select a.hash_value,a.sql_text,
a.BUFFER_GETS,
round(a.pr / b.total_exec, 5) as cumulate_BUFFER_GETS_percent,
round(a.re, 5) as BUFFER_GETS_percent
from (select hash_value,sql_text,
BUFFER_GETS,
sum(BUFFER_GETS) over(order by BUFFER_GETS desc) pr,
RATIO_TO_REPORT(BUFFER_GETS) OVER() as re
from (select * from (select hash_value,sql_text, BUFFER_GETS
from sql_9i_full
where buffer_gets>0
order by BUFFER_GETS desc)
)
order by BUFFER_GETS desc) A,
(select sum(BUFFER_GETS) total_exec
from sql_9i_full
where buffer_gets>0 ) B
where rownum<100))
and s.parsing_schema_id=u.user_id
order by username,sql_text, elapsed_time desc
--------------------------------------------------------3-----------------------
Select *
From (Select b.*, t.parsing_schema_name,dbms_lob.substr(st.sql_text, 3000) sql_text
From SYS.Wrh$_Sqltext st, sys.WRI$_SQLSET_STATEMENTS t ,
(Select task_name,
sql_id,
sts_executions,
spa_executions,
detal_buffer_gets,
sts_buffer_gets,
spa_buffer_gets,
sts_plan_hash_value,
spa_plan_hash_value,
sts_rows_processed,
spa_rows_processed
From (select task_name,
sql_id,
sts_executions,
spa_executions,
round(spa_buffer_gets / spa_executions) - round(sts_buffer_gets / sts_executions) detal_buffer_gets,
round(sts_buffer_gets / sts_executions) sts_buffer_gets,
round(spa_buffer_gets / spa_executions) spa_buffer_gets,
sts_plan_hash_value,
spa_plan_hash_value,
round(sts_rows_processed / sts_executions,2) sts_rows_processed,
round(spa_rows_processed / spa_executions,2) spa_rows_processed
From (Select spa.task_name,
spa.sql_id,
sts.executions sts_executions,
spa.executions spa_executions,
sts.plan_hash_value sts_plan_hash_value,
sts.buffer_gets sts_buffer_gets,
sts.rows_processed sts_rows_processed,
spa.plan_hash_value spa_plan_hash_value,
spa.buffer_gets spa_buffer_gets,
spa.rows_processed spa_rows_processed
From dba_sqlset_statements sts ,
dba_advisor_sqlstats spa
Where spa.execution_name = 'spa12C'
And spa.task_name = 'TASK_12C'
And sts.sqlset_name = 'STS_UP12C_0316'
and sts.sqlset_owner = 'DBA_OWNER'
And sts.sql_id = spa.sql_id
and spa.plan_hash_value<>sts.plan_hash_value))
Where detal_buffer_gets > 0) b
Where st.sql_id(+) = b.sql_id
and b.sql_id = t.sql_id (+)
Order By detal_buffer_gets Desc)Where upper(sql_text) Not Like '%V$%'
and upper(sql_text) Not Like '%SYS.%'
and upper(sql_text) Not Like '%SYSTEM.%'
and upper(sql_text) Not Like '%DBA_%'
and upper(sql_text) Not Like '%EXPLAIN PLAN%'
and upper(sql_text) Not Like '%FROM DUAL%'
---------------------4-------------------------------
Select *
From (Select b.*, t.parsing_schema_name,dbms_lob.substr(st.sql_text, 3000) sql_text
From SYS.Wrh$_Sqltext st, sys.WRI$_SQLSET_STATEMENTS t ,
(Select task_name,
sql_id,
sts_executions,
spa_executions,
detal_buffer_gets_per_row,
sts_buffer_gets_per_row,
spa_buffer_gets_per_row,
sts_plan_hash_value,
spa_plan_hash_value,
sts_rows_processed,
spa_rows_processed
From (select task_name,
sql_id,
sts_executions,
spa_executions,
round(spa_buffer_gets / spa_rows_processed) - round(sts_buffer_gets / sts_rows_processed) detal_buffer_gets_per_row,
round(sts_buffer_gets / sts_rows_processed) sts_buffer_gets_per_row,
round(spa_buffer_gets / spa_rows_processed) spa_buffer_gets_per_row,
sts_plan_hash_value,
spa_plan_hash_value,
round(sts_rows_processed / sts_executions,2) sts_rows_processed,
round(spa_rows_processed / spa_executions,2) spa_rows_processed
From (Select spa.task_name,
spa.sql_id,
sts.executions sts_executions,
spa.executions spa_executions,
sts.plan_hash_value sts_plan_hash_value,
sts.buffer_gets sts_buffer_gets,
sts.rows_processed sts_rows_processed,
spa.plan_hash_value spa_plan_hash_value,
spa.buffer_gets spa_buffer_gets,
spa.rows_processed spa_rows_processed
From dba_sqlset_statements sts ,
dba_advisor_sqlstats spa
Where spa.execution_name = 'spa12C'
And spa.task_name = 'TASK_12C'
And sts.sqlset_name = 'STS_UP12C_0316'
and sts.sqlset_owner = 'DBA_OWNER'
And sts.sql_id = spa.sql_id
and spa.plan_hash_value<>sts.plan_hash_value
and sts.rows_processed>0
and spa.rows_processed>0))
Where detal_buffer_gets_per_row > 0) b
Where st.sql_id(+) = b.sql_id
and b.sql_id = t.sql_id (+)
Order By detal_buffer_gets_per_row Desc)Where upper(sql_text) Not Like '%V$%'
and upper(sql_text) Not Like '%SYS.%'
and upper(sql_text) Not Like '%SYSTEM.%'
and upper(sql_text) Not Like '%DBA_%'
and upper(sql_text) Not Like '%EXPLAIN PLAN%'
and upper(sql_text) Not Like '%FROM DUAL%'
------------------------------------------5-------------------------
select b.sql_id, b.sts_plan_hash_value , b.spa_plan_hash_value ,
b.val as equal_val,
( select count(1) from dba_sqlset_plans p where p.plan_hash_value =b.sts_plan_hash_value and sql_id = b.sql_id ) sts_val ,
( select count(1) from dba_advisor_sqlplans p where p.plan_hash_value = b.spa_plan_hash_value and sql_id = b.sql_id ) spa_val from (
select p.plan_hash_value sts_plan_hash_value, s.sql_id ,s.plan_hash_value spa_plan_hash_value /, p.operation/ ,count(1) val
from dba_sqlset_plans p ,dba_advisor_sqlplans s
where p.operation = s.operation
and nvl(p.options,'0') = nvl(s.options,'0')
and nvl(p.object_owner,'0') =nvl( s.object_owner ,'0')
and nvl(p.object_name,'0')= nvl(s.object_name,'0')
and nvl(p.object_alias,'0') = nvl( s.object_alias,'0')
and nvl(p.object_type,'0') = nvl( s.object_type,'0')
and nvl(p.ID,-1) = nvl(s.id,-1)
and nvl(p.PARENT_ID,-1) = nvl(s.PARENT_ID,-1)
and p.DEPTH = s.DEPTH
and p.sql_id=s.sql_id
group by p.plan_hash_value , s.plan_hash_value , s.sql_id ) b
--------------------------------------6----------------------
select task_name,
sql_id,
sts_executions,
spa_executions,
round(spa_buffer_gets / spa_executions) - round(sts_buffer_gets / sts_executions) detal_buffer_gets,
round(sts_buffer_gets / sts_executions) sts_buffer_gets,
round(spa_buffer_gets / spa_executions) spa_buffer_gets,
sts_plan_hash_value,
spa_plan_hash_value,
round(sts_rows_processed / sts_executions,2) sts_rows_processed,
round(spa_rows_processed / spa_executions,2) spa_rows_processed
From (Select spa.task_name,
spa.sql_id,
sts.executions sts_executions,
spa.executions spa_executions,
sts.plan_hash_value sts_plan_hash_value,
sts.buffer_gets sts_buffer_gets,
sts.rows_processed sts_rows_processed,
spa.plan_hash_value spa_plan_hash_value,
spa.buffer_gets spa_buffer_gets,
spa.rows_processed spa_rows_processed
From dba_sqlset_statements sts ,
dba_advisor_sqlstats spa
Where spa.execution_name = 'spa12C'
And spa.task_name = 'TASK_12C'
And sts.sqlset_name = 'STS_UP12C_0704'
and sts.sqlset_owner = 'DBA_OWNER'
And sts.sql_id = spa.sql_id
and spa.plan_hash_value=sts.plan_hash_value)
