<!-- Oracle Remote Diagnostic Agent / Data Collection Results 4.23 -->
<!-- Module:S110PERF Version:2.05 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_03', 'DP_EXP');  END; | 4| 2681.2|
|SELECT '&#124;' &#124;&#124;	     v.status &#124;&#124; '&#124;' &#124;&#124;	d.file_name &#124;&#124; '&#124;' &#124;&#124;	      d.tablespace_name &#124;&#124; '&#124; ' &#124;&#124;	   d.file_id &#124;&#124; '&#124; ' &#124;&#124;	d.relative_fno &#124;&#124; '&#124; ' &#124;&#124;	  TO_CHAR((d.bytes / 1048576),		      '99999990D000') &#124;&#124; '&#124; ' &#124;&#124;	 TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0), 	       '99999990D000') &#124;&#124; '&#124; ' &#124;&#124;	  TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100),		    '990D00') &#124;&#124; '&#124;' &#124;&#124;	NVL(d.autoextensible, 'NO') &#124;&#124; '&#124; ' &#124;&#124;        d.maxblocks &#124;&#124; '&#124;'  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|
[[#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('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 '&#124;' &#124;&#124;	     v.status &#124;&#124; '&#124;' &#124;&#124;	d.file_name &#124;&#124; '&#124;' &#124;&#124;	      d.tablespace_name &#124;&#124; '&#124; ' &#124;&#124;	   d.file_id &#124;&#124; '&#124; ' &#124;&#124;	d.relative_fno &#124;&#124; '&#124; ' &#124;&#124;	  TO_CHAR((d.bytes / 1048576),		      '99999990D000') &#124;&#124; '&#124; ' &#124;&#124;	 TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0), 	       '99999990D000') &#124;&#124; '&#124; ' &#124;&#124;	  TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100),		    '990D00') &#124;&#124; '&#124;' &#124;&#124;	NVL(d.autoextensible, 'NO') &#124;&#124; '&#124; ' &#124;&#124;        d.maxblocks &#124;&#124; '&#124;'  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 &lt; :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 &lt; :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|
[[#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 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|
[[#Top][Back to top]]
