<!-- Oracle Remote Diagnostic Agent / Data Collection Results 4.17 -->
<!-- Module:S110PERF Version:1.39 Report:top_sql OS:linux -->
---+!! Top Resource SQL Statements
%TOC%
---+ SQL in Shared Pool with High Disk Reads to Executions Ratio
Where (disk_reads/executions) / 50 > 100%BR%
Order by executions descending%BR%
|*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&gt;= NVL(:1,processing_date) and processing_date&lt;= 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|
[[#Top][Back to top]]
---+ SQL in Shared Pool with High Buffer Gets to Executions Ratio
Where (buffer_gets/executions) / 500 > 100%BR%
Order by executions descending%BR%
|*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 &lt; :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&gt;= NVL(:1,processing_date) and processing_date&lt;= 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&gt;= NVL(:1,processing_date) and processing_date&lt;= 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|
[[#Top][Back to top]]
---+ SQL in Shared Pool with High Loads
Where in Shared Pool with more than 15 loads%BR%
Order by loads descending%BR%
|*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 &lt;&gt; 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 &lt;&gt; 0) endsn ,  X$KEHTIMMAP x WHERE	startsn.snap_id 	= :begin_snap_id   and	endsn.snap_id		= :end_snap_i| 23|
[[#Top][Back to top]]
