STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ TESTDB 2367954213 testdb 1 9.2.0.1.0 NO IONTESTSRV2 Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 52 17-Mar-06 09:59:26 16 6.8 End Snap: 53 17-Mar-06 10:39:04 17 5.8 Elapsed: 39.63 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 24M Std Block Size: 8K Shared Pool Size: 48M Log Buffer: 512K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 16,803.48 3,079.67 Logical reads: 927.28 169.95 Block changes: 126.65 23.21 Physical reads: 0.83 0.15 Physical writes: 1.09 0.20 User calls: 367.80 67.41 Parses: 122.83 22.51 Hard parses: 13.34 2.45 Sorts: 3.15 0.58 Logons: 0.01 0.00 Executes: 125.97 23.09 Transactions: 5.46 % Blocks changed per Read: 13.66 Recursive Call %: 9.35 Rollback per transaction %: 68.84 Rows per Sort: 4.80 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.91 In-memory Sort %: 100.00 Library Hit %: 94.09 Soft Parse %: 89.14 Execute to Parse %: 2.49 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 97.49 % Non-Parse CPU: 63.41 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 88.80 84.25 % SQL with executions>1: 29.06 44.73 % Memory for SQL w/exec>1: 39.47 60.38 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 121 79.23 enqueue 55 14 9.46 db file sequential read 838 6 3.80 log file sync 4,100 4 2.36 control file sequential read 369 2 1.50 ------------------------------------------------------------- Wait Events for DB: TESTDB Instance: testdb Snaps: 52 -53 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- enqueue 55 0 14 263 0.0 db file sequential read 838 0 6 7 0.1 log file sync 4,100 0 4 1 0.3 control file sequential read 369 0 2 6 0.0 log file parallel write 7,255 7,111 2 0 0.6 db file parallel write 948 473 1 2 0.1 control file parallel write 780 0 1 1 0.1 db file scattered read 119 0 0 4 0.0 SQL*Net more data to client 13,268 0 0 0 1.0 direct path write (lob) 494 0 0 1 0.0 log file switch completion 3 0 0 32 0.0 latch free 135 7 0 0 0.0 log file sequential read 2 0 0 4 0.0 LGWR wait for redo copy 9 0 0 0 0.0 buffer busy waits 19 0 0 0 0.0 log file single write 2 0 0 0 0.0 SQL*Net message from client 609,776 0 19,011 31 47.0 virtual circuit status 79 79 3,562 45087 0.0 wakeup time manager 75 75 3,525 47004 0.0 SQL*Net more data from clien 10,695 0 2 0 0.8 SQL*Net message to client 609,777 0 1 0 47.0 ------------------------------------------------------------- Background Wait Events for DB: TESTDB Instance: testdb Snaps: 52 -53 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- control file sequential read 317 0 2 7 0.0 log file parallel write 7,255 7,111 2 0 0.6 db file parallel write 948 473 1 2 0.1 control file parallel write 780 0 1 1 0.1 db file scattered read 99 0 0 4 0.0 db file sequential read 37 0 0 3 0.0 log file sequential read 2 0 0 4 0.0 LGWR wait for redo copy 9 0 0 0 0.0 log file single write 2 0 0 0 0.0 latch free 10 0 0 0 0.0 buffer busy waits 1 0 0 0 0.0 rdbms ipc message 20,616 10,075 16,227 787 1.6 smon timer 7 7 3,207 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 310,349 548 566.3 14.1 2.08 2.38 1794416315 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_615711, T1.LEAF_CAT_ID C_61571 3, T1.A_EQP_EQUIPMENT C_615715, T1.A_EQP_QUALIFICATION C_615717, T2.NOTE_COUNT C_615704, T2.INTERNAL_ID C_615704_INTERNAL_ID, T2 .ATTACHMENT_COUNT C_615707, T2.INTERNAL_ID C_615707_INTERNAL_ID, 44,697 4,950 9.0 2.0 1.05 0.87 2810408022 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.NOTE_COUNT C_616181, T3.INTERNAL_ID C_616181 _INTERNAL_ID, T3.ATTACHMENT_COUNT C_616184, T3.INTERNAL_ID C_616 184_INTERNAL_ID, T3.CREATE_DATE C_616200, T3.INTERNAL_ID C_61620 0_INTERNAL_ID, T3.MODIFIED_DATE C_616206, T3.INTERNAL_ID C_61620 33,648 11,216 3.0 1.5 0.98 1.06 183688498 SELECT 1 FROM DUAL 32,942 548 60.1 1.5 1.03 1.20 1645430897 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.SITE_NAME C_629686, T3.INTERNAL_ID C_629686_ INTERNAL_ID, T1.BO_STATUS C_629645, T1.IS_ACTIVE C_629646, T1.IN TERNAL_ID C_629677, T1.A_EQP_EQUIPMENT_ID C_629678, T1.A_EQP_EQU IPMENT_NAME C_629679, T1.A_EQP_EQUIPMENT_TYPE C_629680, T1.A_EQP 31,441 466 67.5 1.4 0.38 0.52 1905166529 SELECT DISTINCT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.L EAF_CAT_ID C_LEAF_CAT_ID, T1.INTERNAL_ID C_608364, T1.LEAF_CAT_I D C_608366, T1.ALERT_DEF_NAME C_608373, T1.BO_CATEGORY C_608375, T1.BUSINESS_OBJECT C_608377, T1.TIMER_ID C_608379, T1.WEB_SERVI CES_ID C_608381, T1.WEB_SERVICES_OPERATION_ID C_608383, T1.TYPE 31,231 75 416.4 1.4 0.33 1.20 238087931 select t.schema, t.name, t.flags, q.name from system.aq$_queue_t ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 20,748 494 42.0 0.9 2.09 2.07 991956924 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_628937, T1.COMPANY C_628938, T 1.PARENT_SITE_ID C_628939, T1.SITE_ID C_628940, T1.SITE_NAME C_6 28941, T1.HAS_PRIVILEGE C_628942, T1.GMP C_628946, T2.COMPANY_NA ME C_628943, T2.INTERNAL_ID C_628943_INTERNAL_ID FROM D_ORG_SITE 15,040 470 32.0 0.7 0.27 0.29 910130945 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T12.NOTE_COUNT C_604411_603437, T12.ATTACHMENT_ COUNT C_604411_603440, T12.CREATE_DATE C_604411_603506, T12.MODI FIED_DATE C_604411_603512, T12.CREATE_USER C_604411_603518, T12. SQL ordered by Gets for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- MODIFIED_USER C_604411_603524, T3.NOTE_COUNT C_604383_600373, T3 14,912 1,864 8.0 0.7 0.34 0.28 1100326606 SELECT T0.INTERNAL_ID, T0.FROM_CAT_ALIAS, T0.TO_CAT_ALIAS, T0.VI EW_ID, T0.FROM_ATTR_ID, T0.TO_ATTR_ID, T0.JOIN_ORDER, T0.IS_REL_ PK_JOIN, T0.OCA, T2.CAT_ID FROM_CAT_ID, T2.COL_NAME FROM_COL_NAM E, T3.CAT_ID TO_CAT_ID, T3.COL_NAME TO_COL_NAME, T5.INTERNAL_ID LINK_ATTR_ID, T5.CAT_ID OWNER_CAT_ID FROM M_MET_VIEW_JOINS T0 13,981 964 14.5 0.6 0.48 0.94 1674990437 INSERT INTO JMSStore (recordHandle,recordState,record) VALUES (: 1,:2,:3) 13,791 1 13,791.0 0.6 0.20 0.25 4059808258 Module: SQL*Plus INSERT into stats$sqltext ( hash_value , text_subset , piece , sql_text , address , comman d_type , last_snap_id ) select st1.hash_value , ss.text_subset 13,413 4,455 3.0 0.6 0.39 0.38 2396368 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.NOTE_COUNT C_600017, T1.ATTACHMENT_COUNT C_6 00018, T1.INTERNAL_ID C_600020, T1.LEAF_CAT_ID C_600022, T1.CREA TE_DATE C_600024, T1.MODIFIED_DATE C_600026, T1.CREATE_USER C_60 0028, T1.MODIFIED_USER C_600030 FROM M_MET_BASE T1 WHERE T1.INTE 11,589 1,650 7.0 0.5 0.09 0.29 3460529092 select t.name, (select owner_instance from sys.aq$_queue_table_ affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 10,492 548 19.1 0.5 0.16 0.18 2028693828 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.SITE_NAME C_629686, T3.INTERNAL_ID C_629686_ INTERNAL_ID, T10.INTERNAL_ID C_629645_602708, T10.LEAF_CAT_ID C_ 629645_602710, T10.BUSINESS_OBJECT C_629645_602712, T10.STATUS_I D C_629645_602714, T10.STATUS_NAME C_629645_602716, T10.DESCRIPT 7,529 932 8.1 0.3 0.17 0.25 1184783705 DELETE FROM JMSStore WHERE recordHandle = :1 OR recordHandle = : 2 OR recordHandle = :3 OR recordHandle = :4 OR recordHandle = :5 ------------------------------------------------------------- SQL ordered by Reads for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 29 1 29.0 1.5 0.14 0.31 1457900244 Module: SQL*Plus INSERT into stats$snapshot ( snap_id, dbid, instance_n umber , snap_time, startup_time , session_id, snap_ level, ucomment , executions_th, parse_calls_th, disk_ reads_th , buffer_gets_th, sharable_mem_th , version_count_th, seg_phy_reads_th , seg_log_reads_th 22 184 0.1 1.1 0.02 0.18 2591785020 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespa ce=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)a nd(linkname=:5 or linkname is null and :5 is null)and(subname=:6 or subname is null and :6 is null) 22 494 0.0 1.1 0.38 0.41 3341056465 SELECT T0.INTERNAL_ID, T0.VIEW_NAME, T0.VIEW_LABEL, T0.VIEW_DESC , T0.LOC_KEY, T0.CAT_ID, T0.IS_SYSTEM, T0.IS_SEARCHABLE,T0.IS_PR EFERRED, T0.DISP_ORDER, T0.OCA FROM M_MET_VIEW T0 WHERE UP PER(T0.VIEW_NAME) = UPPER('Business Object Site View') AND T0.CA T_ID = 615607 16 494 0.0 0.8 2.09 2.07 991956924 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_628937, T1.COMPANY C_628938, T 1.PARENT_SITE_ID C_628939, T1.SITE_ID C_628940, T1.SITE_NAME C_6 28941, T1.HAS_PRIVILEGE C_628942, T1.GMP C_628946, T2.COMPANY_NA ME C_628943, T2.INTERNAL_ID C_628943_INTERNAL_ID FROM D_ORG_SITE 16 466 0.0 0.8 0.38 0.52 1905166529 SELECT DISTINCT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.L EAF_CAT_ID C_LEAF_CAT_ID, T1.INTERNAL_ID C_608364, T1.LEAF_CAT_I D C_608366, T1.ALERT_DEF_NAME C_608373, T1.BO_CATEGORY C_608375, T1.BUSINESS_OBJECT C_608377, T1.TIMER_ID C_608379, T1.WEB_SERVI CES_ID C_608381, T1.WEB_SERVICES_OPERATION_ID C_608383, T1.TYPE 14 241 0.1 0.7 0.05 0.25 2385919346 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decod e(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180 ,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedsto rage,nvl(deflength,0),default$,rowid,col#,property, nvl(charseti d,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh 14 148 0.1 0.7 0.02 0.13 3680293972 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clu cols,0),audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt, blkcnt,empcnt,avgspc,chncnt,avgrln,analyzetime, samplesize,cols, property,nvl(degree,1),nvl(instances,1),avgspc_flb,flbcnt,kernel cols,nvl(trigflag, 0),nvl(spare1,0),nvl(spare2,0),spare4,spare6 12 212 0.1 0.6 0.05 0.12 3844343967 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i .distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.sa SQL ordered by Reads for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- mplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt ,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),n 10 548 0.0 0.5 1.03 1.20 1645430897 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.SITE_NAME C_629686, T3.INTERNAL_ID C_629686_ INTERNAL_ID, T1.BO_STATUS C_629645, T1.IS_ACTIVE C_629646, T1.IN TERNAL_ID C_629677, T1.A_EQP_EQUIPMENT_ID C_629678, T1.A_EQP_EQU IPMENT_NAME C_629679, T1.A_EQP_EQUIPMENT_TYPE C_629680, T1.A_EQP 9 964 0.0 0.5 0.48 0.94 1674990437 INSERT INTO JMSStore (recordHandle,recordState,record) VALUES (: 1,:2,:3) 9 466 0.0 0.5 0.05 0.10 3300051588 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_608517, T1.LEAF_CAT_ID C_60851 9, T1.TRIGGER_ACTION C_608521, T1.ALERT_DEFINITION C_608523, T2. NOTE_COUNT C_608510, T2.INTERNAL_ID C_608510_INTERNAL_ID, T2.ATT ACHMENT_COUNT C_608513, T2.INTERNAL_ID C_608513_INTERNAL_ID, T2. 6 75 0.1 0.3 0.33 1.20 238087931 select t.schema, t.name, t.flags, q.name from system.aq$_queue_t ables t, sys.aq$_queue_table_affinities aft, system.aq$_que ues q where aft.table_objno = t.objno and aft.owner_instance = : 1 and q.table_objno = t.objno and q.usage = 0 and b itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft 6 548 0.0 0.3 2.08 2.38 1794416315 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_615711, T1.LEAF_CAT_ID C_61571 3, T1.A_EQP_EQUIPMENT C_615715, T1.A_EQP_QUALIFICATION C_615717, T2.NOTE_COUNT C_615704, T2.INTERNAL_ID C_615704_INTERNAL_ID, T2 .ATTACHMENT_COUNT C_615707, T2.INTERNAL_ID C_615707_INTERNAL_ID, 6 106 0.1 0.3 0.00 0.06 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user# ,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N ------------------------------------------------------------- SQL ordered by Executions for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 11,216 11,216 1.0 0.00 0.00 183688498 SELECT 1 FROM DUAL 4,950 4,950 1.0 0.00 0.00 2810408022 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.NOTE_COUNT C_616181, T3.INTERNAL_ID C_616181 _INTERNAL_ID, T3.ATTACHMENT_COUNT C_616184, T3.INTERNAL_ID C_616 184_INTERNAL_ID, T3.CREATE_DATE C_616200, T3.INTERNAL_ID C_61620 0_INTERNAL_ID, T3.MODIFIED_DATE C_616206, T3.INTERNAL_ID C_61620 4,455 4,455 1.0 0.00 0.00 2396368 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.NOTE_COUNT C_600017, T1.ATTACHMENT_COUNT C_6 00018, T1.INTERNAL_ID C_600020, T1.LEAF_CAT_ID C_600022, T1.CREA TE_DATE C_600024, T1.MODIFIED_DATE C_600026, T1.CREATE_USER C_60 0028, T1.MODIFIED_USER C_600030 FROM M_MET_BASE T1 WHERE T1.INTE 2,330 2,330 1.0 0.00 0.00 638567561 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 634620 1,864 0 0.0 0.00 0.00 1100326606 SELECT T0.INTERNAL_ID, T0.FROM_CAT_ALIAS, T0.TO_CAT_ALIAS, T0.VI EW_ID, T0.FROM_ATTR_ID, T0.TO_ATTR_ID, T0.JOIN_ORDER, T0.IS_REL_ PK_JOIN, T0.OCA, T2.CAT_ID FROM_CAT_ID, T2.COL_NAME FROM_COL_NAM E, T3.CAT_ID TO_CAT_ID, T3.COL_NAME TO_COL_NAME, T5.INTERNAL_ID LINK_ATTR_ID, T5.CAT_ID OWNER_CAT_ID FROM M_MET_VIEW_JOINS T0 1,864 1,864 1.0 0.00 0.00 3344693726 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 632102 1,864 1,864 1.0 0.00 0.00 4125947826 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 629759 1,650 1,650 1.0 0.00 0.00 3460529092 select t.name, (select owner_instance from sys.aq$_queue_table_ affinities where table_objno = t.objno) from system.aq$_queue _tables t where t.name = :1 and t.schema = :2 for update skip lo cked 990 990 1.0 0.00 0.00 2039389639 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_611838, T1.LEAF_CAT_ID C_61184 SQL ordered by Executions for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 0, T1.A_EQP_EQUIPMENT_ID C_611848, T1.A_EQP_EQUIPMENT_TYPE C_611 850, T1.A_EQP_EQUIPMENT_NAME C_611852, T1.A_EQP_DESCRIPTION C_61 1854, T1.A_EQP_EQUIPMENT_TAG_NUMBER C_611856, T1.A_EQP_MATERIAL_ 964 964 1.0 0.00 0.00 1674990437 INSERT INTO JMSStore (recordHandle,recordState,record) VALUES (: 1,:2,:3) 932 932 1.0 0.00 0.00 305017693 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 632108 932 932 1.0 0.00 0.00 1071914575 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 630884 932 932 1.0 0.00 0.00 1184783705 DELETE FROM JMSStore WHERE recordHandle = :1 OR recordHandle = : 2 OR recordHandle = :3 OR recordHandle = :4 OR recordHandle = :5 OR recordHandle = :6 OR recordHandle = :7 OR recordHandle = :8 OR recordHandle = :9 OR recordHandle = :10 OR recordHandle = :11 OR recordHandle = :12 OR recordHandle = :13 OR recordHandle = : 932 0 0.0 0.00 0.00 1289526885 SELECT T0.INTERNAL_ID, T0.FROM_CAT_ALIAS, T0.TO_CAT_ALIAS, T0.VI EW_ID, T0.FROM_ATTR_ID, T0.TO_ATTR_ID, T0.JOIN_ORDER, T0.IS_REL_ PK_JOIN, T0.OCA, T2.CAT_ID FROM_CAT_ID, T2.COL_NAME FROM_COL_NAM E, T3.CAT_ID TO_CAT_ID, T3.COL_NAME TO_COL_NAME, T5.INTERNAL_ID LINK_ATTR_ID, T5.CAT_ID OWNER_CAT_ID FROM M_MET_VIEW_JOINS T0 932 466 0.5 0.00 0.00 2764552033 DELETE FROM JMSState WHERE recordHandle = :1 AND recordGeneratio n = :2 OR recordHandle = :3 AND recordGeneration = :4 OR recordH ------------------------------------------------------------- SQL ordered by Parse Calls for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 11,216 11,216 3.84 183688498 SELECT 1 FROM DUAL 4,950 4,950 1.69 2810408022 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.NOTE_COUNT C_616181, T3.INTERNAL_ID C_616181 _INTERNAL_ID, T3.ATTACHMENT_COUNT C_616184, T3.INTERNAL_ID C_616 184_INTERNAL_ID, T3.CREATE_DATE C_616200, T3.INTERNAL_ID C_61620 0_INTERNAL_ID, T3.MODIFIED_DATE C_616206, T3.INTERNAL_ID C_61620 4,455 4,455 1.53 2396368 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.NOTE_COUNT C_600017, T1.ATTACHMENT_COUNT C_6 00018, T1.INTERNAL_ID C_600020, T1.LEAF_CAT_ID C_600022, T1.CREA TE_DATE C_600024, T1.MODIFIED_DATE C_600026, T1.CREATE_USER C_60 0028, T1.MODIFIED_USER C_600030 FROM M_MET_BASE T1 WHERE T1.INTE 2,330 2,330 0.80 638567561 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 634620 1,864 1,864 0.64 1100326606 SELECT T0.INTERNAL_ID, T0.FROM_CAT_ALIAS, T0.TO_CAT_ALIAS, T0.VI EW_ID, T0.FROM_ATTR_ID, T0.TO_ATTR_ID, T0.JOIN_ORDER, T0.IS_REL_ PK_JOIN, T0.OCA, T2.CAT_ID FROM_CAT_ID, T2.COL_NAME FROM_COL_NAM E, T3.CAT_ID TO_CAT_ID, T3.COL_NAME TO_COL_NAME, T5.INTERNAL_ID LINK_ATTR_ID, T5.CAT_ID OWNER_CAT_ID FROM M_MET_VIEW_JOINS T0 1,864 1,864 0.64 3344693726 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 632102 1,864 1,864 0.64 4125947826 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 629759 990 990 0.34 2039389639 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_611838, T1.LEAF_CAT_ID C_61184 0, T1.A_EQP_EQUIPMENT_ID C_611848, T1.A_EQP_EQUIPMENT_TYPE C_611 850, T1.A_EQP_EQUIPMENT_NAME C_611852, T1.A_EQP_DESCRIPTION C_61 1854, T1.A_EQP_EQUIPMENT_TAG_NUMBER C_611856, T1.A_EQP_MATERIAL_ 932 932 0.32 305017693 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 SQL ordered by Parse Calls for DB: TESTDB Instance: testdb Snaps: 52 -53 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 632108 932 932 0.32 1071914575 SELECT T0.INTERNAL_ID, T0.VIEW_ATTR_NAME, T0.VIEW_ATTR_LABEL, T0 .LOC_KEY, T0.DISP_ORDER, T0.IS_EDITABLE, T0.IS_HIDDEN, T0.ATTR_A LIAS, T0.VIEW_ATTR_TYPE,T0.VIEW_ATTR_DISP_TYPE, T0.VIEW_ID, T0.C AT_ATTR_ID, T0.VIEW_TABLE_ID, T0.OCA FROM M_MET_VIEW_ATTR T0 WHERE T0.INTERNAL_ID = 630884 932 932 0.32 1289526885 SELECT T0.INTERNAL_ID, T0.FROM_CAT_ALIAS, T0.TO_CAT_ALIAS, T0.VI EW_ID, T0.FROM_ATTR_ID, T0.TO_ATTR_ID, T0.JOIN_ORDER, T0.IS_REL_ PK_JOIN, T0.OCA, T2.CAT_ID FROM_CAT_ID, T2.COL_NAME FROM_COL_NAM E, T3.CAT_ID TO_CAT_ID, T3.COL_NAME TO_COL_NAME, T5.INTERNAL_ID LINK_ATTR_ID, T5.CAT_ID OWNER_CAT_ID FROM M_MET_VIEW_JOINS T0 602 602 0.21 3578508015 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T3.CAT_NAME C_630845, T3.INTERNAL_ID C_630845_I NTERNAL_ID, T1.IS_ACTIVE C_630840, T1.INTERNAL_ID C_630844, T1.A _EQP_EQUIPMENT_ID C_630848, T1.A_EQP_EQUIPMENT_TYPE C_630849, T1 .A_EQP_EQUIPMENT_NAME C_630850, T1.A_EQP_QUALIFICATION_STATUS C_ 558 964 0.19 1674990437 INSERT INTO JMSStore (recordHandle,recordState,record) VALUES (: 1,:2,:3) 557 557 0.19 2975563588 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I D C_LEAF_CAT_ID, T1.INTERNAL_ID C_615641, T1.LEAF_CAT_ID C_61564 3, T1.A_EQP_QUALIFICATION_ID C_615645, T1.A_EQP_QUALIFICATION_ST ATUS C_615647, T1.A_EQP_QUALIFICATION_START_DATE C_615649, T1.A_ EQP_QUALIFICATION_END_DATE C_615651, T1.A_EQP_QUALIFICATION_TYPE 556 556 0.19 1935198421 SELECT T1.INTERNAL_ID C_INTERNAL_ID, T1.OCA C_OCA, T1.LEAF_CAT_I ------------------------------------------------------------- Instance Activity Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 12,101 5.1 0.9 CPU used when call started 12,101 5.1 0.9 CR blocks created 25,865 10.9 2.0 DBWR buffers scanned 1,970 0.8 0.2 DBWR checkpoint buffers written 1,367 0.6 0.1 DBWR checkpoints 1 0.0 0.0 DBWR free buffers found 1,300 0.6 0.1 DBWR lru scans 17 0.0 0.0 DBWR make free requests 17 0.0 0.0 DBWR summed scan depth 1,970 0.8 0.2 DBWR transaction table writes 10 0.0 0.0 DBWR undo block writes 1,495 0.6 0.1 SQL*Net roundtrips to/from client 609,730 256.4 47.0 active txn count during cleanout 27,283 11.5 2.1 background checkpoints completed 0 0.0 0.0 background checkpoints started 1 0.0 0.0 background timeouts 2,790 1.2 0.2 branch node splits 2 0.0 0.0 buffer is not pinned count 1,417,326 596.0 109.2 buffer is pinned count 1,255,561 528.0 96.8 bytes received via SQL*Net from c 274,714,197 115,523.2 21,172.6 bytes sent via SQL*Net to client 328,469,347 138,128.4 25,315.6 calls to get snapshot scn: kcmgss 427,932 180.0 33.0 calls to kcmgas 37,283 15.7 2.9 calls to kcmgcs 198 0.1 0.0 change write time 104 0.0 0.0 cleanout - number of ktugct calls 26,202 11.0 2.0 cleanouts and rollbacks - consist 25,415 10.7 2.0 cleanouts only - consistent read 15 0.0 0.0 cluster key scan block gets 2,586 1.1 0.2 cluster key scans 1,271 0.5 0.1 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: callbac 16 0.0 0.0 commit cleanout failures: cannot 26 0.0 0.0 commit cleanouts 48,268 20.3 3.7 commit cleanouts successfully com 48,226 20.3 3.7 commit txn count during cleanout 660 0.3 0.1 consistent changes 38,499 16.2 3.0 consistent gets 1,899,006 798.6 146.4 consistent gets - examination 1,510,249 635.1 116.4 cursor authentications 1,927 0.8 0.2 data blocks consistent reads - un 36,500 15.4 2.8 db block changes 301,182 126.7 23.2 db block gets 306,074 128.7 23.6 deferred (CURRENT) block cleanout 19,064 8.0 1.5 dirty buffers inspected 67 0.0 0.0 enqueue conversions 1,143 0.5 0.1 enqueue releases 35,536 14.9 2.7 enqueue requests 35,537 14.9 2.7 enqueue timeouts 1 0.0 0.0 enqueue waits 55 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 299,567 126.0 23.1 free buffer inspected 69 0.0 0.0 free buffer requested 33,509 14.1 2.6 hot buffers moved to head of LRU 2,034 0.9 0.2 Instance Activity Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ immediate (CR) block cleanout app 25,430 10.7 2.0 immediate (CURRENT) block cleanou 2,863 1.2 0.2 index fast full scans (full) 0 0.0 0.0 index fetch by key 617,785 259.8 47.6 index scans kdiixs1 34,510 14.5 2.7 leaf node 90-10 splits 109 0.1 0.0 leaf node splits 179 0.1 0.0 logons cumulative 12 0.0 0.0 messages received 7,583 3.2 0.6 messages sent 7,583 3.2 0.6 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 197,757 83.2 15.2 opened cursors cumulative 268,938 113.1 20.7 parse count (failures) 0 0.0 0.0 parse count (hard) 31,734 13.3 2.5 parse count (total) 292,093 122.8 22.5 parse time cpu 4,428 1.9 0.3 parse time elapsed 4,542 1.9 0.4 physical reads 1,981 0.8 0.2 physical reads direct 0 0.0 0.0 physical writes 2,598 1.1 0.2 physical writes direct 0 0.0 0.0 physical writes direct (lob) 494 0.2 0.0 physical writes non checkpoint 2,375 1.0 0.2 pinned buffers inspected 0 0.0 0.0 prefetched blocks 1,033 0.4 0.1 process last non-idle time 13,710,853,263 5,765,707.9 1,056,713.2 recovery blocks read 0 0.0 0.0 recursive calls 90,210 37.9 7.0 recursive cpu usage 216 0.1 0.0 redo blocks written 83,871 35.3 6.5 redo buffer allocation retries 3 0.0 0.0 redo entries 163,615 68.8 12.6 redo log space requests 3 0.0 0.0 redo log space wait time 8 0.0 0.0 redo ordering marks 0 0.0 0.0 redo size 39,958,676 16,803.5 3,079.7 redo synch time 358 0.2 0.0 redo synch writes 4,103 1.7 0.3 redo wastage 1,599,708 672.7 123.3 redo write time 264 0.1 0.0 redo writer latching time 0 0.0 0.0 redo writes 7,255 3.1 0.6 rollback changes - undo records a 284 0.1 0.0 rollbacks only - consistent read 773 0.3 0.1 rows fetched via callback 613,700 258.1 47.3 session connect time 13,710,853,263 5,765,707.9 1,056,713.2 session logical reads 2,205,080 927.3 170.0 session uga memory 203,592 85.6 15.7 session uga memory max 7,928,168 3,334.0 611.0 shared hash latch upgrades - no w 88,531 37.2 6.8 shared hash latch upgrades - wait 40 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 7,498 3.2 0.6 sorts (rows) 35,982 15.1 2.8 summed dirty queue length 1,234 0.5 0.1 Instance Activity Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ switch current to new buffer 3,171 1.3 0.2 table fetch by rowid 1,183,014 497.5 91.2 table fetch continued row 27 0.0 0.0 table scan blocks gotten 108,905 45.8 8.4 table scan rows gotten 3,960,812 1,665.6 305.3 table scans (long tables) 2 0.0 0.0 table scans (short tables) 50,925 21.4 3.9 transaction rollbacks 55 0.0 0.0 user calls 874,633 367.8 67.4 user commits 4,043 1.7 0.3 user rollbacks 8,932 3.8 0.7 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 6,545 2.8 0.5 write clones created in foregroun 0 0.0 0.0 ------------------------------------------------------------- Tablespace IO Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ UNDOTBS1 3 0 3.3 1.0 1,571 1 12 0.0 APPTBSP 185 0 6.4 1.2 692 0 5 0.0 INDX 258 0 7.4 1.0 318 0 2 0.0 SYSTEM 331 0 4.6 4.0 17 0 0 0.0 TOOLS 167 0 8.4 1.1 117 0 0 0.0 TEMP 3 0 6.7 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ APPTBSP C:\ORACLE\ORADATA\TESTDB\PCMDATATBS1.DBF 185 0 6.4 1.2 692 0 5 0.0 INDX C:\ORACLE\ORADATA\TESTDB\INDX01.DBF 258 0 7.4 1.0 318 0 2 0.0 SYSTEM C:\ORACLE\ORADATA\TESTDB\SYSTEM01.DBF 331 0 4.6 4.0 17 0 0 TEMP C:\ORACLE\ORADATA\TESTDB\TEMP01.DBF 3 0 6.7 1.0 0 0 0 TOOLS C:\ORACLE\ORADATA\TESTDB\TOOLS01.DBF 167 0 8.4 1.1 117 0 0 UNDOTBS1 C:\ORACLE\ORADATA\TESTDB\UNDOTBS01.DBF 3 0 3.3 1.0 1,571 1 12 0.0 ------------------------------------------------------------- Buffer Pool Statistics for DB: TESTDB Instance: testdb Snaps: 52 -53 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 3,000 99.8 1,083,454 1,983 2,229 0 0 19 ------------------------------------------------------------- Instance Recovery Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- B 43 25 1540 47763 47274 184320 47274 E 43 29 1709 81076 80939 184320 80939 ------------------------------------------------------------- Buffer Pool Advisory for DB: TESTDB Instance: testdb End Snap: 53 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 4 .2 500 2.13 308,308 D 8 .3 1,000 1.59 229,577 D 12 .5 1,500 1.21 174,636 D 16 .7 2,000 1.09 157,424 D 20 .8 2,500 1.04 151,108 D 24 1.0 3,000 1.00 144,829 D 28 1.2 3,500 0.72 103,997 D 32 1.3 4,000 0.67 97,531 D 36 1.5 4,500 0.65 93,793 D 40 1.7 5,000 0.59 84,860 D 44 1.8 5,500 0.57 82,132 D 48 2.0 6,000 0.53 77,311 D 52 2.2 6,500 0.50 72,919 D 56 2.3 7,000 0.49 71,574 D 60 2.5 7,500 0.46 66,752 D 64 2.7 8,000 0.44 63,407 D 68 2.8 8,500 0.42 60,492 D 72 3.0 9,000 0.41 59,595 D 76 3.2 9,500 0.41 58,679 D 80 3.3 10,000 0.40 57,352 ------------------------------------------------------------- Buffer wait Statistics for DB: TESTDB Instance: testdb Snaps: 52 -53 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- undo block 8 0 0 data block 7 0 0 undo header 4 0 0 ------------------------------------------------------------- PGA Aggr Target Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 428 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 24 12 19.6 0.0 .0 .0 .0 1,228 E 24 12 15.9 0.0 .0 .0 .0 1,228 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: TESTDB Instance: testdb Snaps: 52 -53 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 5,100 5,100 0 0 16K 32K 947 947 0 0 32K 64K 1 1 0 0 64K 128K 1 1 0 0 512K 1024K 494 494 0 0 1M 2M 2 2 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: TESTDB Instance: testdb End Snap: 53 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 12 0.5 10,541.1 1,231.8 90.0 406 18 0.8 10,541.1 1.5 100.0 0 24 1.0 10,541.1 1.5 100.0 0 29 1.2 10,541.1 1.5 100.0 0 34 1.4 10,541.1 0.0 100.0 0 38 1.6 10,541.1 0.0 100.0 0 43 1.8 10,541.1 0.0 100.0 0 48 2.0 10,541.1 0.0 100.0 0 72 3.0 10,541.1 0.0 100.0 0 96 4.0 10,541.1 0.0 100.0 0 144 6.0 10,541.1 0.0 100.0 0 192 8.0 10,541.1 0.0 100.0 0 ------------------------------------------------------------- Enqueue activity for DB: TESTDB Instance: testdb Snaps: 52 -53 -> Enqueue stats gathered prior to 9i should not be compared with 9i data -> ordered by Wait Time desc, Waits desc Avg Wt Wait Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s) -- ------------ ------------ ----------- ----------- ------------- ------------ TX 8,313 8,313 0 55 262.20 14 ------------------------------------------------------------- Rollback Segment Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 0 8.0 0.00 0 0 0 0 1 1,560.0 0.00 892,416 1 0 1 2 2,380.0 0.04 1,318,802 1 0 1 3 2,194.0 0.00 1,444,150 4 0 2 4 1,547.0 0.00 764,448 2 0 1 5 2,402.0 0.00 1,296,268 3 0 2 6 2,212.0 0.00 1,470,656 1 0 1 7 1,553.0 0.00 1,097,666 3 0 1 8 2,495.0 0.00 1,427,234 2 0 2 9 2,251.0 0.00 1,509,684 2 0 2 10 1,527.0 0.00 990,258 2 0 2 ------------------------------------------------------------- Rollback Segment Storage for DB: TESTDB Instance: testdb Snaps: 52 -53 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 385,024 0 385,024 1 4,317,184 659,288 12,705,792 2 5,365,760 687,599 15,917,056 3 5,365,760 671,773 17,948,672 4 3,268,608 571,399 12,705,792 5 5,365,760 686,103 15,917,056 6 4,317,184 631,752 16,900,096 7 4,317,184 647,025 13,754,368 8 5,365,760 676,737 16,900,096 9 5,365,760 672,729 16,900,096 10 4,317,184 611,517 13,819,904 ------------------------------------------------------------- Undo Segment Summary for DB: TESTDB Instance: testdb Snaps: 52 -53 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU ---- -------------- ---------- -------- ---------- -------- ------ ------------- 1 1,705 1,759,475 4 2 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 -> ordered by Time desc Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU ------------ ------------ -------- ------- -------- ------- ------ ------------- 17-Mar 10:38 516 443,502 4 1 0 0 0/0/0/0/0/0 17-Mar 10:28 549 441,097 4 1 0 0 0/0/0/0/0/0 17-Mar 10:18 537 438,643 4 2 0 0 0/0/0/0/0/0 17-Mar 10:08 103 436,233 4 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: TESTDB Instance: testdb Snaps: 52 -53 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Consistent RBA 7,255 0.0 0 0 FIB s.o chain latch 4 0.0 0 0 FOB s.o list latch 1,073 0.0 0 0 SQL memory manager latch 1 0.0 0 772 0.0 SQL memory manager worka 52,963 0.0 0 0 active checkpoint queue 1,255 0.0 0 0 archive control 1 0.0 0 0 begin backup scn array 494 0.0 0 0 cache buffer handles 338 0.0 0 0 cache buffers chains 3,628,602 0.0 0.0 0 8,365 0.0 cache buffers lru chain 38,203 0.0 0.0 0 64,042 0.0 channel handle pool latc 23 0.0 0 0 channel operations paren 1,586 0.0 0 0 checkpoint queue latch 64,138 0.0 0.0 0 2,357 0.0 child cursor hash table 224,945 0.0 0.0 0 0 dml lock allocation 49,204 0.0 0 0 dummy allocation 23 0.0 0 0 enqueue hash chains 72,275 0.0 0.0 0 0 enqueues 26,883 0.0 0.0 0 0 event group latch 12 0.0 0 0 file number translation 115,998 0.0 0.0 0 0 hash table column usage 200 0.0 0 59,639 0.0 job_queue_processes para 39 0.0 0 0 ktm global data 7 0.0 0 0 kwqit: protect wakeup ti 75 0.0 0 0 lgwr LWN SCN 7,304 0.0 0 0 library cache 3,462,783 0.1 0.0 0 79,548 0.8 library cache load lock 1,890 0.0 0 0 library cache pin 1,953,794 0.0 0.0 0 0 library cache pin alloca 1,324,910 0.0 0.0 0 0 list of block allocation 985 0.0 0 0 loader state object free 1,976 0.0 0 0 messages 49,512 0.0 0.0 0 0 mostly latch-free SCN 7,304 0.0 0.0 0 0 multiblock read objects 344 0.0 0 0 ncodef allocation latch 38 0.0 0 0 object stats modificatio 4 0.0 0 0 post/wait queue 14,521 0.0 0 4,103 0.0 process allocation 12 0.0 0 12 0.0 process group creation 23 0.0 0 0 redo allocation 178,200 0.0 0.6 0 0 redo copy 0 0 163,644 0.0 redo writing 24,646 0.0 0 0 row cache enqueue latch 1,833,704 0.0 0.0 0 0 row cache objects 1,834,745 0.0 0.0 0 1,513 0.0 sequence cache 111 0.0 0 0 session allocation 57,449 0.0 0 0 session idle bit 1,770,751 0.0 0.0 0 0 session switching 38 0.0 0 0 session timer 810 0.0 0 0 Latch Activity for DB: TESTDB Instance: testdb Snaps: 52 -53 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ shared pool 2,424,629 0.1 0.0 0 0 sim partition latch 0 0 33 0.0 simulator hash latch 55,040 0.0 0 0 simulator lru latch 872 0.0 0 405 0.0 sort extent pool 45 0.0 0 0 transaction allocation 52 0.0 0 0 transaction branch alloc 38 0.0 0 0 undo global data 82,753 0.0 0.0 0 0 user lock 128 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: TESTDB Instance: testdb Snaps: 52 -53 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ library cache 3,462,783 3,604 77 3527/77/0/0/ 0 shared pool 2,424,629 1,327 39 1288/39/0/0/ 0 cache buffers chains 3,628,602 181 7 0/0/0/0/0 library cache pin 1,953,794 61 2 59/2/0/0/0 redo allocation 178,200 18 10 8/10/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: TESTDB Instance: testdb Snaps: 52 -53 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbchg: kslbegin: bufs not 0 5 4 cache buffers chains kcbget: pin buffer 0 1 1 cache buffers chains kcbgtcr: fast path 0 1 1 library cache kglpin: child: heap proces 0 13 0 library cache kglhdgn: child: 0 11 20 library cache kglpndl: child: before pro 0 9 0 library cache kglupc: child 0 7 28 library cache kgllkdl: child: cleanup 0 6 0 library cache kglpnc: child 0 6 24 library cache kgldti: 2child 0 4 0 library cache kglhdgc: child: 0 4 0 library cache kglobpn: child: 0 4 0 library cache kgldte: child 0 0 2 0 library cache pin kglpnal: child: alloc spac 0 1 0 library cache pin kglupc 0 1 1 redo allocation kcrfwr 0 10 0 shared pool kghalo 0 32 0 shared pool kghfrunp: clatch: nowait 0 10 0 shared pool kghupr1 0 6 39 shared pool kghfrunp: alloc: wait 0 1 0 shared pool kghfrunp: clatch: wait 0 1 2 ------------------------------------------------------------- Dictionary Cache Stats for DB: TESTDB Instance: testdb Snaps: 52 -53 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_histogram_defs 220,058 0.1 0 0 546 dc_object_ids 405,981 0.0 0 0 512 dc_objects 35,749 0.3 0 0 292 dc_profiles 12 0.0 0 0 1 dc_rollback_segments 294 0.0 0 0 22 dc_segments 194,768 0.1 0 0 386 dc_sequences 6 16.7 0 6 1 dc_tablespace_quotas 20 10.0 0 5 2 dc_tablespaces 10,915 0.0 0 0 4 dc_user_grants 92 0.0 0 0 4 dc_usernames 193 1.0 0 0 3 dc_users 48,992 0.0 0 0 5 ------------------------------------------------------------- Library Cache Activity for DB: TESTDB Instance: testdb Snaps: 52 -53 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- CLUSTER 200 2.5 296 3.4 0 0 INDEX 525 0.0 525 0.0 0 0 SQL AREA 292,084 10.8 882,777 7.2 299 0 TABLE/PROCEDURE 109,318 0.1 205,223 0.4 429 0 TRIGGER 23 34.8 23 34.8 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: TESTDB Instance: testdb End Snap: 53 -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 24 .5 24 4,597 23,101 1.0 6,896,421 32 .7 31 5,861 23,106 1.0 6,898,561 40 .8 38 7,964 23,109 1.0 6,899,165 48 1.0 45 10,271 23,109 1.0 6,899,388 56 1.2 52 12,601 23,109 1.0 6,899,655 64 1.3 59 14,167 23,113 1.0 6,900,591 72 1.5 66 15,447 23,123 1.0 6,901,236 80 1.7 73 16,633 23,125 1.0 6,902,935 88 1.8 80 18,063 23,125 1.0 6,903,179 96 2.0 88 19,150 23,125 1.0 6,903,332 ------------------------------------------------------------- SGA Memory Summary for DB: TESTDB Instance: testdb Snaps: 52 -53 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 25,165,824 Fixed Size 453,492 Redo Buffers 667,648 Variable Size 109,051,904 ---------------- sum 135,338,868 ------------------------------------------------------------- SGA breakdown difference for DB: TESTDB Instance: testdb Snaps: 52 -53 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 33,554,432 33,554,432 0.00 large free memory 8,388,608 8,388,608 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 282,304 282,304 0.00 shared FileIdentificatonBlock 323,292 323,292 0.00 shared FileOpenBlock 695,504 695,504 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 1,272,128 1,361,348 7.01 shared KQR M PO 1,353,308 1,069,336 -20.98 shared KQR S PO 387,644 251,736 -35.06 shared KQR S SO 8,460 8,460 0.00 shared KSXR large reply queue 166,104 166,104 0.00 shared KSXR pending messages que 841,036 841,036 0.00 shared KSXR receive buffers 1,033,000 1,033,000 0.00 shared MTTR advisory 8,456 8,456 0.00 shared PL/SQL DIANA 418,684 416,540 -0.51 shared PL/SQL MPCODE 64,624 64,988 0.56 shared PLS non-lib hp 2,068 2,068 0.00 shared VIRTUAL CIRCUITS 265,160 265,160 0.00 shared character set object 336,044 336,044 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared enqueue 171,860 171,860 0.00 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 220 220 0.00 shared free memory 7,515,312 10,569,212 40.64 shared joxs heap init 4,220 4,220 0.00 shared kgl simulator 3,132,300 3,132,300 0.00 shared ksm_file2sga region 148,652 148,652 0.00 shared library cache 14,378,136 12,768,184 -11.20 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 4,890,828 4,910,084 0.39 shared parameters 1,044 1,044 0.00 shared processes 144,000 144,000 0.00 shared sessions 410,720 410,720 0.00 shared sim memory hea 21,164 21,164 0.00 shared sql area 22,565,548 21,434,728 -5.01 shared table definiti 1,120 1,176 5.00 buffer_cache 25,165,824 25,165,824 0.00 fixed_sga 453,492 453,492 0.00 log_buffer 656,384 656,384 0.00 ------------------------------------------------------------- init.ora Parameters for DB: TESTDB Instance: testdb Snaps: 52 -53 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 background_dump_dest C:\oracle\admin\TESTDB\bdump compatible 9.2.0.0.0 control_files C:\oracle\oradata\TESTDB\CONTROL0 core_dump_dest C:\oracle\admin\TESTDB\cdump db_block_size 8192 db_cache_size 25165824 db_domain db_file_multiblock_read_count 16 db_name TESTDB dispatchers (PROTOCOL=TCP) (SERVICE=TESTDBXDB fast_start_mttr_target 300 hash_join_enabled TRUE instance_name TESTDB java_pool_size 33554432 job_queue_processes 10 large_pool_size 8388608 open_cursors 300 pga_aggregate_target 25165824 processes 150 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE shared_pool_size 50331648 sort_area_size 524288 star_transformation_enabled FALSE timed_statistics TRUE undo_management AUTO undo_retention 10800 undo_tablespace UNDOTBS1 user_dump_dest C:\oracle\admin\TESTDB\udump ------------------------------------------------------------- End of Report