Where (disk_reads/executions) / 50 > 100
Order by executions descending
| SQL Text | Executions | Expected Response Time (in Seconds) |
|---|---|---|
| BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_03', 'DP_EXP'); END; | 4 | 2681.2 |
| SELECT '|' || v.status || '|' || d.file_name || '|' || d.tablespace_name || '| ' || d.file_id || '| ' || d.relative_fno || '| ' || TO_CHAR((d.bytes / 1048576), '99999990D000') || '| ' || TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0), '99999990D000') || '| ' || TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100), '990D00') || '|' || NVL(d.autoextensible, 'NO') || '| ' || d.maxblocks || '|' FROM sys.dba_data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id ) s WHERE s.file_id(+) = d.file_id AND d.file_name = v.name | 2 | 151.9 |
Where (buffer_gets/executions) / 500 > 100
Order by executions descending
| SQL Text | Executions | Expected Response Time (in Seconds) |
|---|---|---|
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('OBJGRANT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.GRANTEE ,KU$.GRANTOR ,KU$.PRIVNAME ,'OBJECT_GRANT' FROM SYS.KU$_OBJGRANT_VIEW KU$ WHERE KU$.GRANTOR=KU$.BASE_OBJ.OWNER_NAME AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) | 36 | 163.3 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('OBJGRANT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.GRANTEE ,KU$.GRANTOR ,KU$.PRIVNAME ,'OBJECT_GRANT' FROM SYS.KU$_OBJGRANT_VIEW KU$ WHERE KU$.GRANTOR!=KU$.BASE_OBJ.OWNER_NAME AND KU$.WGO=0 AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) | 36 | 138.1 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TRIGGER_T', '7')), KU$.OBJ_NUM ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TRIGGER' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_TRIGGER_VIEW KU$ WHERE NOT (KU$.BASE_OBJ IS NOT NULL AND BITAND(KU$.BASE_OBJ.FLAGS,128)!=0) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,4)=4 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' ORDER BY KU$.SCHEMA_OBJ.OWNER_NAME | 8 | 523.5 |
| BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_03', 'DP_EXP'); END; | 4 | 24906.1 |
| begin :response_code := PCRD_IPM_MPE . LOAD_MPE_FILE ; EXCEPTION WHEN OTHERS THEN :response_code := 999 ; END ; | 2 | 2741 |
| SELECT '|' || v.status || '|' || d.file_name || '|' || d.tablespace_name || '| ' || d.file_id || '| ' || d.relative_fno || '| ' || TO_CHAR((d.bytes / 1048576), '99999990D000') || '| ' || TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0), '99999990D000') || '| ' || TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100), '990D00') || '|' || NVL(d.autoextensible, 'NO') || '| ' || d.maxblocks || '|' FROM sys.dba_data_files d, v$datafile v, (SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id ) s WHERE s.file_id(+) = d.file_id AND d.file_name = v.name | 2 | 209.2 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PROCACTSCHEMA_T', '7')), 0 ,'PROCACT_SCHEMA' ,KU$.USER_NAME FROM SYS.KU$_PROCACT_SCHEMA_VIEW KU$ WHERE KU$.PLSQL IS NOT NULL AND KU$.USER_NAME LIKE '%' AND NOT (KU$.USER_NAME IN ('ORDSYS', 'EXFSYS', 'MDSYS','DMSYS', 'CTXSYS', 'ORDPLUGINS','LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA','DIP', 'DBSNMP', 'WMSYS', 'ORACLE_OCM')) AND KU$.PREPOST=1 | 1 | 342.7 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PACKAGE_BODY_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'PACKAGE_BODY' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_EXP_PKG_BODY_VIEW KU$ WHERE KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) | 1 | 318.2 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,'CONSTRAINT' ,KU$.OWNER_NAME FROM SYS.KU$_CONSTRAINT_VIEW KU$ WHERE NOT (KU$.CON1.CONTYPE=3 AND KU$.CON1.INTCOLS=1 AND KU$.CON1.OID_OR_SETID!=0) AND NOT (KU$.CON1.CONTYPE=2 AND BITAND(KU$.CON1.PROPERTY,64+4096)!=0) AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.OWNER_NAME LIKE '%' AND (KU$.CON1.CONTYPE NOT IN (2,5) OR KU$.CON1.IND.TYPE_NUM NOT IN (4,8,9)) | 1 | 868.6 |
| select output from table(dbms_workload_repository.awr_report_text( :dbid, :inst_num, :bid, :eid, :rpt_options )) | 1 | 194.1 |
| BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_03', 'DP_EXP', 'KUPC$C_1_20110621030002', 'KUPC$S_1_20110621030002', 0); END; | 1 | 295.2 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.PART_OBJ.PARTOBJ.DEFTS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_PHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND (BITAND(KU$.FLAGS,536870912)=0) | 1 | 132.9 |
| delete from sys.wri$_optstat_histhead_history where savtime < :1 | 1 | 109.2 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PACKAGE_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'PACKAGE' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_PKG_VIEW KU$ WHERE NOT (BITAND (KU$.SCHEMA_OBJ.FLAGS,16)=16) AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='PACKAGE_SPEC' AND A.NAME=KU$.SCHEMA_OBJ.NAME AND A.SCHEMA=KU$.SCHEMA_OBJ.OWNER_NAME) AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.SCHEMA_OBJ.OWNER_NAME) | 1 | 102.7 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('I_STAT_T', '7')), 0 ,KU$.BASE_TAB_OBJ.NAME ,KU$.BASE_TAB_OBJ.OWNER_NAME ,KU$.BASE_TAB_OBJ.TYPE_NAME ,to_char(KU$.BASE_IND_OBJ.FLAGS) ,KU$.BASE_IND_OBJ.NAME ,KU$.BASE_IND_OBJ.OWNER_NAME ,KU$.BASE_IND_OBJ.TYPE_NAME ,'INDEX_STATISTICS' ,to_char(KU$.TYPE_NUM) FROM SYS.KU$_IND_STATS_VIEW KU$ WHERE NOT BITAND(KU$.BASE_TAB_OBJ.FLAGS,128)!=0 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.TYPE_NUM NOT IN (2,9) AND BITAND(KU$.PROPERTY,16)!=16 | 1 | 151 |
| SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,'TABLE' ,to_char(KU$.BYTES_ALLOC) ,to_char(KU$.ET_PARALLEL) ,KU$.FGAC ,KU$.NONSCOPED_REF ,KU$.XMLSCHEMACOLS ,KU$.NAME ,KU$.NAME ,'TABLE_DATA' ,KU$.PART_NAME ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG ,decode(KU$.SCHEMA_OBJ.TYPE_NUM,2,decode(bitand(KU$.PROPERTY,8192),8192,'NESTED TABLE','TABLE'),19,'PARTITION',20,'PARTITION','SUBPARTITION') ,to_char(KU$.UNLOAD_METHOD) FROM SYS.KU$_TABLE_DATA_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT (BITAND (KU$.BASE_OBJ.FLAGS,16)=16) AND KU$.BASE_OBJ.OWNER_NAME LIKE '%' AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='TABLE' AND A.NAME=KU$.BASE_OBJ.NAME AND A.SCHEMA=KU$.BASE_OBJ.OWNER_NAME) AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.BASE_OBJ.OWNER_NAME) | 1 | 688.7 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('SYNONYM_T', '7')), KU$.OBJ_NUM ,KU$.SYN_LONG_NAME ,KU$.SCHEMA_OBJ.NAME ,'SYNONYM' ,KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_SYNONYM_VIEW KU$ WHERE NOT (BITAND (KU$.SCHEMA_OBJ.FLAGS,16)=16) AND KU$.SCHEMA_OBJ.OWNER_NAME != 'PUBLIC' AND KU$.OWNER_NAME LIKE '%' AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SYNONYM' AND A.SCHEMA=KU$.OWNER_NAME AND KU$.SCHEMA_OBJ.OWNER_NUM =1 ) AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.SCHEMA_OBJ.OWNER_NAME) | 1 | 145.6 |
| delete from sys.wri$_optstat_histgrm_history where savtime < :1 | 1 | 337.8 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('REF_CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,'REF_CONSTRAINT' ,KU$.OWNER_NAME FROM SYS.KU$_REF_CONSTRAINT_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.OWNER_NAME LIKE '%' | 1 | 346.1 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('INDEX_T', '7')), KU$.OBJ_NUM ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,to_char(KU$.FLAGS) ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'INDEX' ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,to_char(KU$.TYPE_NUM) FROM SYS.KU$_INDEX_VIEW KU$ WHERE NOT KU$.FOR_PKOID=1 AND NOT (KU$.TYPE_NUM=1 AND KU$.INTCOLS=1 AND KU$.OID_OR_SETID!=0) AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,4)=4 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' AND KU$.TYPE_NUM NOT IN (2,4,8,9) AND BITAND(KU$.PROPERTY,16)!=16 | 1 | 372.1 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.XMLSCHEMACOLS ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG FROM SYS.KU$_HTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND (BITAND(KU$.FLAGS,536870912)=0) | 1 | 2034.5 |
| SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, DECODE(COMPARE_OLD_ON_DELETE, 'YES', 1, 0) COMPARE_OLD_ON_DELETE, DECODE(COMPARE_OLD_ON_UPDATE, 'YES', 1, 0) COMPARE_OLD_ON_UPDATE, APPLY_DATABASE_LINK FROM DBA_APPLY_TABLE_COLUMNS ORDER BY OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK | 1 | 106.3 |
| SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('T_STAT_T', '7')), 0 ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,'TABLE_STATISTICS' FROM SYS.KU$_TAB_STATS_VIEW KU$ WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) | 1 | 1028.7 |
Where in Shared Pool with more than 15 loads
Order by loads descending
| SQL Text | Executions |
|---|---|
| SELECT ALL DCISC_OUT_REP_TABLE.SEQUENCE_ID, DCISC_OUT_REP_TABLE.DCISC_DATA_LABEL, DCISC_OUT_REP_TABLE.DCISC_DATA_1, DCISC_OUT_REP_TABLE.DCISC_DATA_2, DCISC_OUT_REP_TABLE.BATCH_ID, DCISC_OUT_REP_TABLE.NETWORK_REFERENCE_ID, DCISC_OUT_REP_TABLE.TRANSACTION_DATE, DCISC_OUT_REP_TABLE.TRANSACTION_TYPE, DCISC_OUT_REP_TABLE.TRANSACTION_AMOUNT, DCISC_OUT_REP_TABLE.MCC, DCISC_OUT_REP_TABLE.AUTHORIZATION_NBR FROM DCISC_OUT_REP_TABLE ORDER BY DCISC_OUT_REP_TABLE.SEQUENCE_ID ASC | 30 |
| INSERT INTO DCISC_OUT_REP_TABLE VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ) | 29 |
| SELECT * FROM DC_DETAIL WHERE DCD_FILE_ID = :B1 ORDER BY DCD_FILE_ID,DCD_CARD_NUMBER,DCD_TRANSACTION_DATE | 17 |