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_02', 'DP_EXP'); END; 9 1597.4
call dbms_stats.gather_database_stats_job_proc ( ) 6 206
call dbms_space.auto_space_advisor_job_proc ( ) 6 130.9
SELECT TABLE_NAME,TABLE_KEY,USER_MODIF,FIELD_LABEL,FIELD_OLD_VALUE,FIELD_NEW_VALUE,SENSITIVE_OPERATION_RECORD,PROCESSING_DATE FROM mega_df WHERE processing_date>= NVL(:1,processing_date) and processing_date<= NVL(:2,processing_date) and table_name IN (select table_name from role_tab_privs where role in (select granted_role from dba_role_privs where grantee=:3)) order by processing_date desc ,table_key,field_label 4 100.6

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('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 12 257.9
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)) 9 421.4
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) 9 377.3
select s.synonym_name object_name, o.object_type from all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', :schema) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE') 9 120.5
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))) 9 509.5
BEGIN SYS.KUPW$WORKER.MAIN('SYS_EXPORT_FULL_02', 'DP_EXP'); END; 9 11681.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 9 180
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) 9 989.4
delete from sys.wri$_optstat_histgrm_history where savtime < :1 7 234.1
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 6 249.4
call dbms_stats.gather_database_stats_job_proc ( ) 6 2031.4
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))) 6 124.6
call dbms_space.auto_space_advisor_job_proc ( ) 6 291.9
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 '%' 5 171.8
SELECT TABLE_NAME,TABLE_KEY,USER_MODIF,FIELD_LABEL,FIELD_OLD_VALUE,FIELD_NEW_VALUE,SENSITIVE_OPERATION_RECORD,PROCESSING_DATE FROM mega_df WHERE processing_date>= NVL(:1,processing_date) and processing_date<= NVL(:2,processing_date) and table_name IN (select table_name from role_tab_privs where role in (select granted_role from dba_role_privs where grantee=:3)) order by processing_date desc ,table_key,field_label 4 141.7
/* OracleOEM */ SELECT /*+ INDEX(ts) */ o.object_type, u.name, o.object_name, o.partition, o.lob_column, o.seg_type, ts.name, 4 FROM ( SELECT 1 "OBJECT_TYPE", o.owner# "USER_NAME#", o.name "OBJECT_NAME", ' ' "PARTITION", ' ' "LOB_COLUMN", 1 "SEG_TYPE", t.ts# "TABLESPACE#" FROM sys.obj$ o, sys.tab$ t, sys.seg$ s WHERE o.obj# = t.obj# AND t.ts# in (:1,:2) AND t.file# = s.file# AND t.block# = s.block# AND t.ts# = s.ts# AND decode (bitand (t.property, 1), 1, 1, 0) = 0 AND decode (bitand (t.property, 32), 32, 1, 0) = 0 AND decode (bitand (t.property, 64), 64, 1, 0) = 0 AND decode (bitand (t.property, 512), 512, 1, 0) = 0 AND decode (bitand (t.property, 1024), 1024, 1, 0) = 0 UNION ALL SELECT 2, o.owner#, o.name, ' ', ' ', 2, i.ts# FROM sys.obj$ o, sys.ind$ i, sys.seg$ s WHERE o.obj# = i.obj# AND i.ts# in (:1,:2) AND i.file# = s.file# AND i.block# = s.block# AND i.ts# = s.ts# AND decode (bitand (i.property, 2), 2, 1, 0) 2 131
SELECT TABLE_NAME,TABLE_KEY,USER_MODIF,FIELD_LABEL,FIELD_OLD_VALUE,FIELD_NEW_VALUE,SENSITIVE_OPERATION_RECORD,PROCESSING_DATE FROM mega_df WHERE processing_date>= NVL(:1,processing_date) and processing_date<= NVL(:2,processing_date) and table_name IN (select table_name from role_tab_privs where role in (select granted_role from dba_role_privs where grantee=:3)) and (TABLE_NAME LIKE :4) order by processing_date desc ,table_key,field_label 2 101.4
DELETE VISA_ARDEF 1 469.9
BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_02', 'DP_EXP', 'KUPC$C_1_20110620030002', 'KUPC$S_1_20110620030002', 0); END; 1 216.4
BEGIN dbms_registry.loaded('EM'); END; 1 2153.8
BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_02', 'DP_EXP', 'KUPC$C_1_20110619030001', 'KUPC$S_1_20110619030001', 0); END; 1 202.6
begin DELETE VISA_ARDEF ; :ResponseCode := PCRD_VISA_LOAD_BIN_ARDEF . LOAD_ARDEF ; EXCEPTION WHEN OTHERS THEN :ResponseCode := 999 ; END ; 1 1075.5
begin :response_code := PCRD_VISA_LOAD_BIN_ARDEF . LOAD_BIN ; EXCEPTION WHEN OTHERS THEN :response_code := 999 ; END ; 1 872.9
DELETE VISA_BIN 1 427.1
BEGIN SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_02', 'DP_EXP', 'KUPC$C_1_20110621030016', 'KUPC$S_1_20110621030016', 0); END; 1 216.6
SELECT OWNER, TABLE_NAME TABLE_NAME, NULL PARTITION_NAME, 'TABLE' SEGMENT_TYPE, TABLESPACE_NAME, NULL LOBCOL_NAME, NULL LOBSEG_NAME, NULL LOBPART_NAME, NULL IOT_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = :B1 AND PARTITIONED = 'NO' AND TEMPORARY = 'N' AND NESTED = 'NO' UNION ALL SELECT TABLE_OWNER, TABLE_NAME, NULL PARTITION_NAME, 'TABLE' SEGMENT_TYPE, TABLESPACE_NAME, NULL LOBCOL_NAME, NULL LOBSEG_NAME, NULL LOBPART_NAME, NULL IOT_NAME FROM DBA_INDEXES WHERE TABLESPACE_NAME = :B1 AND INDEX_TYPE = 'IOT - TOP' UNION ALL SELECT TABLE_OWNER OWNER, TABLE_NAME TABLE_NAME, PARTITION_NAME, 'TABLE PARTITION' SEGMENT_TYPE, TABLESPACE_NAME, NULL LOBCOL_NAME, NULL LOBSEG_NAME, NULL LOBPART_NAME, NULL IOT_NAME FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME = :B1 AND COMPOSITE = 'NO' UNION ALL SELECT TABLE_OWNER OWNER, TABLE_NAME TABLE_NAME, SUBPARTITION_NAME, 'TABLE SUBPARTITION' SEGMENT_TYPE, TABLESPACE_NAME, NULL LOBCOL_NAME, NULL LOBSEG_NAME, NULL LOBPART_NAME, NULL IOT_NAME FROM DBA_TAB_SUBPAR 1 343.9

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 DESCRIPTION, POSH_PROFILE_CODE from posh_profile where bank_code=:1 order by upper(DESCRIPTION) 38
SELECT INTERNAL_STAN.NEXTVAL FROM DUAL 30
SELECT startsn.stat_id, x.keh_id, GREATEST( 0, (endsn.value - startsn.value) ) as value_diff FROM (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) startsn , (SELECT t1.* FROM WRH$_SYS_TIME_MODEL t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_SYS_TIME_MODEL_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) endsn , X$KEHTIMMAP x WHERE startsn.snap_id = :begin_snap_id and endsn.snap_id = :end_snap_i 23

Back to top