sqltop
SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- UP.MEMO_STATUS_ID and MEMO.MEMO_TYPE_ID = MEMO_TYPE_LKUP.MEMO_TYPE_ID and MEMO.TRADE_AGREEMENT_ID = :1 and MEMO.MEMO_STATUS_ID <> :2 AND MEMO.MEMO_TYPE_ID IN (:3,:4,:5,:6,:7,:8,:9) 12108 2 6054 12.108 UPDATE INVOICE_ACT A SET OUTBOUND_ENVELOPE_ID = :1, UPDATE_TS = TO_TIMESTAMP(:2, 'YYYY-MM-DD hh24 :mi:ss ff'), UPDATE_USER_TXT = :3 WHERE INVOICE_ID IN(SELECT DISTINCT A.INVOICE_ID FROM INVOICE B, TRADE_AGREEMENT C WHERE A.INVOICE_ID = B.INVOICE_ID AND B.TRADE_AGREEMENT_ID = C.TR ADE_AGREEMENT_ID AND C.BUYER_PARTY_ID = :4 AND A.INVOICE_ACT_TYPE_ID in (:5,:6) AND A.UPDATE_T S < TO_TIMESTAMP(:7, 'YYYY-MM-DD hh24:mi:ss ff') AND A.OUTBOUND_ENVELOPE_ID IS NULL) SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- 135053 1 135053 270.106 select owner, column_name , table_name||' '||data_type||'('|| decode(data_type, 'NUMBER' , data_precision, data_length)||')' "Characteristics" from all_tab_columns where (column_name, ow ner) IN (select column_name, owner from all_tab_columns group by column_name, owner having min(decode(data_type, 'NUMBER', data_precision, data_length)) < max(decode(data _type, 'NUMBER', data_precision, data_length)) ) and owner not in ('SYS', 'SYSTEM') 310632 1 310632 621.264 SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- select INVOICE.INVOICE_ID,INVOICE.HEADER_1_TXT,INVOICE.POD_PRINTED_COUNTER,INVOICE.DUE_DT,INVOICE.IN VOICE_DT,INVOICE.EXT_INVOICE_NBR_TXT,INVOICE.HEADER_2_TXT,INVOICE.MATCH_AMT,INVOICE.BUYER_NOTE_FLAG, INVOICE.HEADER_EXT_PO_NBR_TXT, INVOICE.POOL_KEY_TXT,PKG_RECEIPT.F_CHECK_RECEIPT(INVOICE.POOL_KEY_TXT )AS WITH_RECEIPT from INVOICE where INVOICE.TRADE_AGREEMENT_ID = :1 and INVOICE.RECON_STATUS_ID = :2 and INVOICE_CLASSIFICATION_ID = :3 and INVOICE.INVOICE_STATUS_ID <> :4 ORDER BY DUE_DT 10295 4 2573.75 5.1475 SELECT SUBSTR(x.owner,1,10) Owner, SUBSTR(x.name,1,30) Name, x.type, x.sharabl e_mem Share_Mem, x.executions, x.loads, SUBSTR(x.kept||' ',1,4) Kept, SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- y.status, to_char(y.last_ddl_time,'MM/DD/YY HH24:Mi:ss') LAST_DDL_time, x.locks FROM v$db_ object_cache x, all_objects y WHERE x.type in ( 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE') and x.name = y.object_name (+) and x.type = y.object_type(+) and x.owner = y.owner(+) ORDER BY owner,name,s tatus, type 128760 1 128760 257.52 SELECT OBJID, NAME, OWNER, OWNERID, TABLESPACE, TSNO, FILENO, BLOCKNO, AUDIT$, COMMENT$, CLUSTERFLAG, PCTFREE$, PCTUSED$, INITRANS, MAXTRANS, DEGREE, INSTANCES, CACHE, PROPERTY , DEFLOG, TSDEFLOG, ROID, ROWCNT, BLKCNT, AVGRLEN, TFLAGS, TRIGFLAG, OBJSTATUS FROM SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- SYS.EXU9TABU T$ WHERE NOT EXISTS ( SELECT NAME FROM SYS.E XU8NXPU N$ WHERE N$.NAME = T$.NAME AND N$.TYPE = 2) ORD ER BY T$.NAME 5977 1 5977 11.954 select substr(ds.tablespace_name,1,10) c1, substr(di.owner||'.'||di.table_name,1,30) c2, substr(di.index_name,1,20) c3, ds.bytes/1024 c4, ds.extents c5, di.max _extents c6, di.initial_extent/1024 c7, di.next_extent/1024 c8, di.pct_incre ase c9, di.pct_free c10 from sys.dba_segments ds, sys.dba_indexes di where ds.t SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- ablespace_name = di.tablespace_name and ds.owner = di.owner and ds.segment_name = di.index_n ame order by 1,2 61996 1 61996 123.992 SELECT IOBJID, IDOBJID, INAME, IOWNER, IOWNERID, ISPACE, ITSNO, IFILENO, IBLOCKNO, BTNAM E, BTOWNERID, BTOWNER, PROPERTY, CLUSTER$, PCTFREE$, INITRANS, MAXTRANS, BLEVEL, TYPE, D EFLOG, TSDEFLOG, BTOBJID, BTPROPERTY, BTCLUSTERFLAG, DEGREE, INSTANCES, ROWCNT, LEAFCNT, DISTKEY, LBLKKEY, DBLKKEY, CLUFAC, IFLAGS, SYSGENCONST FROM SYS.EXU9IND WHE RE SYSGENCONST = 0 AND (TYPE = 2 OR TYPE = 9 OR BITAND(PROPER SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- TY, 16) = 16) ORDER BY IOBJID 47038 1 47038 94.076 select invoice.invoice_id invoiceId, distribution.create_ts createDate, distribution.distribution_am t distributionAmount, distribution.gl_acct_unit_txt accountUnit, distribution.gl_acct_nbr account, d istribution.gl_sub_acct_nbr subAccount, aoc.code addOnCostCode from distribution inner join invoice on invoice.invoice_id = distribution.invoice_id inner join invoice_act on invoice_act.invoice_id = i nvoice.invoice_id left outer join aoc on aoc.aoc_id = distribution.aoc_id where invoice_act.outbound _envelope_id = :1 order by invoiceId SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- 46608 1 46608 93.216 select a.tablespace_name a1, a.file_name a2, a.avail a3, nvl(b.free,0) a4, nvl(round(((free/a vail)*100),2),0) a5 from (select tablespace_name, substr(file_name,1,45) file_name, fil e_id, round(sum(bytes/(1024*1024)),3) avail from sys.dba_data_files group by tablespa ce_name, substr(file_name,1,45), file_id) a, (select tablespace_name, file_id, round(sum(bytes/(1024*1024)),3) free from sys.dba_free_space group by ta blespace_name, file_id) b where a.file_id = b.file_id (+) order by 1, 2 4820 1 4820 9.64 SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- select substr(a.tablespace_name,1,13) tablespace, round(sum(a.total1)/1024/1024, 1) Total, round(sum (a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used, round(sum(a.sum1)/1024/1024, 1) free, round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free, round(sum(a.maxb)/1 024/1024, 1) largest, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, m ax(bytes) MAXB, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespac e_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tables pace_name 4622 1 4622 9.244 SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- SELECT SUBSTR(TS.NAME,1,12) "Tablespace", to_char(tf.file#,'990') "File ID", to_char(TF.BLOCKS*.00 78125,'99999990') "Blocks/MB", to_char(SUM(F.LENGTH*.0078125),'99999990') "Free/MB", to_char(sum(f .length)/tf.blocks*100,'990.99') "Percentage%", to_char(COUNT(*),'990') "Pieces", to_char(MAX(F.LE NGTH*.0078125),'99999990') "Largest/MB", to_char(MIN(F.LENGTH*.0078125),'99999990') "Smallest/MB", to_char(ROUND(AVG(F.LENGTH)*.0078125),'99999990') "Average/MB", to_char(SUM(DECODE(SIGN(F.LENGTH-5 ), -1, F.LENGTH,0)),'99999990') "Dead" FROM SYS.FET$ F, SYS.FILE$ TF, SYS.TS$ TS WHERE TS.TS# = F.TS # AND TS.TS# = TF.TS# AND f.file# = tf.file# GROUP BY TS.NAME,tf.file#, TF.BLOCKS 4602 3 1534 3.068 SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- select MEMO.MEMO_ID,MEMO.EXT_INVOICE_NBR_TXT,MEMO.DUE_DT,MEMO.HEADER_EXT_PO_NBR_TXT,MEMO.HEADER_1_TX T,MEMO.HEADER_2_TXT,MEMO.MEMO_AMT,MEMO_TYPE_LKUP.CODE,MEMO_STATUS_LKUP.SHORT_DESC,MEMO.COLLABORATION _STATUS_ID,REASON_CODE_LKUP.CODE,MEMO.SELLER_NOTE_FLAG from MEMO, MEMO_STATUS_LKUP, MEMO_TYPE_LKUP, REASON_CODE_LKUP where MEMO.MEMO_STATUS_ID = MEMO_STATUS_LKUP.MEMO_STATUS_ID AND MEMO.MEMO_TYPE_ID = MEMO_TYPE_LKUP.MEMO_TYPE_ID AND MEMO.REASON_CODE_ID = REASON_CODE_LKUP.REASON_CODE_ID and MEMO.MEMO _TYPE_ID = MEMO_TYPE_LKUP.MEMO_TYPE_ID and MEMO.REASON_CODE_ID = REASON_CODE_LKUP.REASON_CODE_ID and MEMO.TRADE_AGREEMENT_ID = :1 and MEMO.MEMO_STATUS_ID <> :2 AND MEMO.MEMO_TYPE_ID IN (:3,:4,:5,:6,:7 ,:8) SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- 5389 4 1347.25 2.6945 SELECT SUBSTR(s.segment_name,1,20) OBJECT_NAME, SUBSTR(s.segment_type,1,5) TYPE, SUBSTR(s.tablespace_name,1,10) T_SPACE, NVL(NVL(t.initial_extent, i.initial_extent),r.initia l_extent)/ 4096 FST_EXT, NVL(NVL(t.next_extent,i.next_extent),R.NEXT_EXTENT) / 4 096 NXT_EXT, s.extents - 1 tot_ext, s.blocks tot_blks FROM dba_rollback_segs R, dba_indexes I, dba_tables T, dba_segme nts S WHERE s.segment_name LIKE UPPER('') || '%' AND s.tablespace_name LIKE UPPER( '') || '%' AND s.extents > 1 AND s.owner = t.owner (+) SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- AND s.segment_name = t.table_name (+) AND s.tablespace_name = t.tablespace_name (+) AND s.owner = i.owner (+) AND s.segment_name = i.index_name ( +) AND s.tablespace_name = i.tablespace_name (+) AND s.owner = r.ow 19042 1 19042 38.084 /* OracleOEM */ SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_mana gement "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_C HAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024 , 0), '99999999.999') "Used (M)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990. SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- 00') "Used %" FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_fi les group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tabl espace_name(+) AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent M anagement", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes 4722 1 4722 9.444 SELECT -- Standalone procedures and functions NULL AS procedure_cat, owner AS procedure_schem, SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- object_name AS procedure_name, NULL, NULL, NULL, 'Standalone procedure or function' AS re marks, DECODE(object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0) AS procedure_type FROM all_objects WHERE (object_type = 'PROCEDURE' OR object_type = 'FUNCTIO N') AND owner LIKE :1 ESCAPE '/' AND object_name LIKE :2 ESCAPE '/' UNION ALL SELECT -- Packag ed procedures with no arguments package_name AS procedure_cat, owner AS procedure_schem, objec t_name AS procedure_name, NULL, NULL, NULL, 'Packaged procedure' AS remarks, 1 AS procedur e_type FROM all_arguments WHERE argument_name IS NULL AND data_type IS NULL AND package_name IS NOT NULL AND owner LIKE :3 ESCAPE '/' AND object_name LIKE :4 ESCAPE '/' UNION ALL SELECT -- P ackaged procedures with arguments package_name AS procedure_cat, owner AS procedure_schem, ob SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- 11539 4 2884.75 5.7695 select invoice.invoice_id invoiceId, invoice.ext_invoice_nbr_txt extInvoiceNbr, invoice.invoice_dt i nvoiceDate, invoice.header_ext_po_nbr_txt purchaseOrderId, invoice.in_store_flag inStorePurchaseOrde r, invoice.invoice_amt invoiceAmount, invoice.match_amt matchAmount, invoice.allowance_amt allowance Amount, invoice.charge_amt chargeAmount, invoice.discount_amt discountAmount, invoice.discountable_a mt discountableAmount, invoice.discount_dt discountDate, invoice.entered_due_dt dueDate, invoice.shi p_dt shipDate, invoice.received_dt receiveDate, invoice.pod_printed_counter proofOfDeliveryPrintedCo unt, invoice.retail_amt retailAmount, invoice.create_user_txt createUser, invoice.create_ts createDa SQL_TEXT ---------------------------------------------------------------------------------------------------- BUFFER_GETS EXECUTIONS AVERAGE RESPONSE ----------- ---------- ---------- ---------- te, invoice.update_user_txt lastUpdateUser, invoice.update_ts lastUpdateDate, invoice.approved_dt ap provalDate, invoice.due_dt bestDueDate, invoice.best_discount_amt bestDiscountAmount, invoice.best_t erms_txt bestTerm, invoice.invoice_terms_txt invoiceTerms, invoice.desc_txt invoiceDescription, inv 240663 1 240663 481.326