抓SQL

简介: ---------------------------1------------sqlplus -S /nolog <conn / as sysdbavariable v_sqlset_name varchar2(30);exec :v_sqlset_name:='sts_up_10g';--create sts in 10gexec dbms_sqltune.

---------------------------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&dollar;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_TEXT

from 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_percent

from (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_TEXT

from 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_percent

from (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_text

from 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_percent

from (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)
    
目录
相关文章
|
SQL 存储 算法
SQL(及存储过程)跑得太慢怎么办?
SQL(及存储过程)跑得太慢怎么办?
106 0
|
SQL 机器学习/深度学习 关系型数据库
【SQL刷题】Day9----SQL过滤数据专项练习
【SQL刷题】Day9----SQL过滤数据专项练习
169 0
【SQL刷题】Day9----SQL过滤数据专项练习
|
SQL 缓存 监控
一个慢SQL引起的惨案
突然接到客服那边的消息,说接到大量用户投诉,页面打不开了。小齐心里一咯噔,最近就自己发布了新代码,加了一个新功能,不会是那部分代码出问题了吧?!!
467 0
|
SQL 存储 算法
来,教你写一手好SQL!(三)
导出日志后分析,主要原因竟然是没有命中索引和没有分页处理。其实这是非常低级的错误,我不禁后背一凉,团队成员的技术水平亟待提高啊。 改造这些 SQL 的过程中,总结了一些经验分享给大家,如果有错误欢迎批评指正。
来,教你写一手好SQL!(三)
|
SQL NoSQL 关系型数据库
来,教你写一手好SQL!(一)
导出日志后分析,主要原因竟然是没有命中索引和没有分页处理。其实这是非常低级的错误,我不禁后背一凉,团队成员的技术水平亟待提高啊。 改造这些 SQL 的过程中,总结了一些经验分享给大家,如果有错误欢迎批评指正。
来,教你写一手好SQL!(一)
|
SQL NoSQL 关系型数据库
|
SQL 存储 数据安全/隐私保护
推荐几个学SQL的好地方
推荐几个学SQL的好地方
推荐几个学SQL的好地方
|
SQL 关系型数据库 MySQL
来,教你写一手好SQL!(二)
导出日志后分析,主要原因竟然是没有命中索引和没有分页处理。其实这是非常低级的错误,我不禁后背一凉,团队成员的技术水平亟待提高啊。 改造这些 SQL 的过程中,总结了一些经验分享给大家,如果有错误欢迎批评指正。
|
存储 SQL 缓存
怎样才能写出高质量的SQL语句?
网上关于sql优化的答案数不胜数,可是篇幅太长,看一下滚动条就没有要看下去的欲望,这里自己总结精简了一下,全文1200字,阅读需要5分钟,还有很多不足之处望大佬多多指点~ 字段设计 价格使用定点数decimal 小单位大数额避免出现小数 单表字段不宜过多(最多30) 尽可能使用 not null 关联表的设计 一对多(使用外键) 多对多(单独新建一张表将多对多拆分成两个一对多) 一对一(如商品的基本信息(item)和商品的详细信息(item_intro),通常使用相同的主键或者增加一个外键字段(item_id)) 范式
151 0