Top Resource SQL Statements

SQL in Shared Pool with High Disk Reads to Executions Ratio

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

Back to top

SQL in Shared Pool with High Buffer Gets to Executions Ratio

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

Back to top

SQL in Shared Pool with High Loads

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

Back to top