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