STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ AHD 3712247982 ahd 1 9.2.0.1.0 NO SBGSDPRI Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 40 24-Jun-08 14:01:24 30 3.3 End Snap: 41 24-Jun-08 14:09:30 31 5.7 Elapsed: 8.10 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 656M Std Block Size: 8K Shared Pool Size: 152M Log Buffer: 768K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 11,339.00 1,916.78 Logical reads: 25,242.67 4,267.11 Block changes: 68.95 11.65 Physical reads: 13.95 2.36 Physical writes: 0.03 0.01 User calls: 428.71 72.47 Parses: 64.03 10.82 Hard parses: 1.02 0.17 Sorts: 12.52 2.12 Logons: 0.01 0.00 Executes: 68.06 11.50 Transactions: 5.92 % Blocks changed per Read: 0.27 Recursive Call %: 14.82 Rollback per transaction %: 0.00 Rows per Sort: 1438.85 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.94 In-memory Sort %: 100.00 Library Hit %: 98.64 Soft Parse %: 98.40 Execute to Parse %: 5.92 Latch Hit %: 97.06 Parse CPU to Parse Elapsd %: 80.13 % Non-Parse CPU: 98.14 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 27.88 38.34 % SQL with executions>1: 54.30 59.71 % Memory for SQL w/exec>1: 71.19 81.68 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 398 79.11 db file sequential read 6,773 89 17.71 log file sync 2,875 5 1.01 log file parallel write 2,993 4 .75 latch free 838 3 .59 ------------------------------------------------------------- Wait Events for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 ---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 6,773 0 89 13 2.4 log file sync 2,875 0 5 2 1.0 log file parallel write 2,993 2,936 4 1 1.0 latch free 838 665 3 4 0.3 buffer busy waits 191 0 2 12 0.1 control file parallel write 157 0 1 5 0.1 control file sequential read 540 0 1 1 0.2 SQL*Net more data to client 3,921 0 0 0 1.4 db file scattered read 8 0 0 15 0.0 direct path read 1 0 0 4 0.0 SQL*Net break/reset to clien 8 0 0 0 0.0 direct path write 1 0 0 0 0.0 LGWR wait for redo copy 2 0 0 0 0.0 SQL*Net message from client 208,284 0 6,278 30 72.4 virtual circuit status 16 16 480 29999 0.0 wakeup time manager 15 15 461 30730 0.0 SQL*Net message to client 208,285 0 0 0 72.4 SQL*Net more data from clien 15 0 0 0 0.0 ------------------------------------------------------------- Background Wait Events for DB: AHD Instance: ahd Snaps: 40 -41 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- log file parallel write 2,993 2,936 4 1 1.0 db file sequential read 304 0 3 8 0.1 control file parallel write 157 0 1 5 0.1 control file sequential read 114 0 0 1 0.0 log file sync 1 0 0 0 0.0 LGWR wait for redo copy 2 0 0 0 0.0 rdbms ipc message 7,173 3,715 2,734 381 2.5 SQL*Net message from client 1 0 480 ###### 0.0 smon timer 1 1 307 ###### 0.0 SQL*Net message to client 1 0 0 0 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 1,226,252 42 29,196.5 10.0 38.14 39.62 4421103 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.priority = :"SYS_B_00" AND ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = :"SYS_ B_01" OR call_req.type = :"SYS_B_02" OR call_req.type IS NULL ) AND call_req.active_flag = :"SYS_B_03" ) AND ( call_req.group_id 1,199,055 30 39,968.5 9.8 27.67 30.25 1276304808 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.severity = :"SYS_B_00" and call_req.group_id IN ( SELECT i d FROM ctct WHERE id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = : "SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"SYS _B_06" OR id = :"SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B_0 1,152,723 42 27,445.8 9.4 36.00 38.68 638823637 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.priority = :"SYS_B_00" AND ( call_req.assignee IS NULL AND call_req.group_id IS NULL ) AND ( call_req.type = :"SYS_B_01" O R call_req.type = :"SYS_B_02" OR call_req.type IS NULL ) AND cal l_req.active_flag = :"SYS_B_03" ) AND ( call_req.group_id IN ( S 986,688 30 32,889.6 8.0 26.25 30.81 3769385240 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.severity = :"SYS_B_00" and ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = :"SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"S YS_B_06" OR id = :"SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B 725,060 10 72,506.0 5.9 10.84 11.63 2057023058 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.group_id IN ( SELECT id FROM ctct WHERE id = :"SYS_B_00" O R id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = :"SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"SYS_B_06" OR id = : "SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B_09" OR id = :"SYS 691,716 24 28,821.5 5.6 21.95 22.48 3591074274 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.priority = :"SYS_B_00" AND ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = :"SYS_ B_01" OR call_req.type = :"SYS_B_02" OR call_req.type IS NULL ) AND call_req.active_flag = :"SYS_B_03" ) AND ( call_req.group_id 691,633 24 28,818.0 5.6 21.05 22.81 2935089992 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.priority = :"SYS_B_00" AND ( call_req.assignee IS NULL AND call_req.group_id IS NULL ) AND ( call_req.type = :"SYS_B_01" O R call_req.type = :"SYS_B_02" OR call_req.type IS NULL ) AND cal l_req.active_flag = :"SYS_B_03" ) AND ( call_req.group_id IN ( S 501,484 10 50,148.4 4.1 9.27 9.69 1665786442 SQL ordered by Gets for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.group_id IN ( SELECT id FROM ctct WHERE id = :"SYS_B_00" O R id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = :"SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"SYS_B_06" OR id = : "SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B_09" OR id = :"SYS 475,082 12 39,590.2 3.9 10.84 12.09 2516500536 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.severity = :"SYS_B_00" and call_req.group_id IN ( SELECT i d FROM ctct WHERE id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = : "SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"SYS _B_06" OR id = :"SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B_0 441,021 9 49,002.3 3.6 11.23 11.44 771782876 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho, ctct cn01 WHERE ( call_req.customer = ctct.i d AND ctct.c_l_id = loc.id AND loc.l_si_id = site.id AND site .z_si_zo_id = z_zo.id AND z_zo.zo_lho_id = z_lho.id AND z_lho. lho_name LIKE :"SYS_B_0" AND call_req.active_flag = :"SYS_B_1" 394,543 12 32,878.6 3.2 10.48 12.65 434765199 SELECT call_req.open_date, call_req.id FROM call_req WHERE ( cal l_req.severity = :"SYS_B_00" and ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = :"SYS_B_01" OR id = :"SYS_B_02" OR id = :"SYS_B_03" OR id = :"SYS_B_04" OR id = :"SYS_B_05" OR id = :"S YS_B_06" OR id = :"SYS_B_07" OR id = :"SYS_B_08" OR id = :"SYS_B 345,816 12 28,818.0 2.8 11.00 11.23 176311197 SELECT call_req.open_date, call_req.id FROM call_req WHERE call_ req.priority = :"SYS_B_0" AND ( call_req.assignee IS NOT NULL OR call_req.group_id IS NOT NULL ) AND ( call_req.type = :"SYS_B_1 " OR call_req.type = :"SYS_B_2" OR call_req.type IS NULL ) AND c all_req.active_flag = :"SYS_B_3" ORDER BY call_req.open_date DE 345,816 12 28,818.0 2.8 10.84 11.61 2099035354 SELECT call_req.open_date, call_req.id FROM call_req WHERE call_ req.priority = :"SYS_B_0" AND ( call_req.assignee IS NULL AND ca ll_req.group_id IS NULL ) AND ( call_req.type = :"SYS_B_1" OR ca ll_req.type = :"SYS_B_2" OR call_req.type IS NULL ) AND call_req ------------------------------------------------------------- SQL ordered by Reads for DB: AHD Instance: ahd Snaps: 40 -41 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 881 109 8.1 13.0 0.17 12.35 4067235415 SELECT act_log.time_stamp, act_log.id FROM act_log WHERE act_log .type = :"SYS_B_0" AND act_log.call_req_id = :"SYS_B_1" ORDER B Y act_log.time_stamp DESC 871 1 871.0 12.8 2.50 8.84 3496863033 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho, ctct cn01 WHERE ( call_req.customer = ctct.i d AND ctct.c_l_id = loc.id AND loc.l_si_id = site.id AND site .z_si_zo_id = z_zo.id AND z_zo.zo_lho_id = z_lho.id AND z_lho. lho_name LIKE :"SYS_B_00" AND call_req.status = :"SYS_B_01" AN 779 3,051 0.3 11.5 1.39 11.59 3026674282 SELECT act_log.action_desc , act_log.analyst , act_log.call_req_ id , act_log.description , act_log.internal , act_log.knowledge_ session , act_log.knowledge_tool , act_log.last_mod_dt , act_log .persid , act_log.system_time , act_log.time_spent , act_log.tim e_stamp , act_log.type FROM act_log WHERE act_log.id = :"SYS_B_0 625 6 104.2 9.2 3.42 8.03 632450130 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho, ctct cn01 WHERE ( call_req.customer = ctct.i d AND ctct.c_l_id = loc.id AND loc.l_si_id = site.id AND site .z_si_zo_id = z_zo.id AND z_zo.zo_lho_id = z_lho.id AND z_lho. lho_name LIKE :"SYS_B_0" AND call_req.status = :"SYS_B_1" AND 605 1 605.0 8.9 1.28 8.40 3376831664 BEGIN statspack.snap; END; 544 168 3.2 8.0 0.22 9.34 860563256 SELECT act_log.time_stamp, act_log.id FROM act_log WHERE ( act_l og.call_req_id = :"SYS_B_0" ) AND ( act_log.internal IS NULL OR act_log.internal != :"SYS_B_1" ) ORDER BY act_log.time_stamp DE SC 402 2,069 0.2 5.9 1.06 7.02 3346182257 SELECT call_req.active_flag , call_req.affected_rc , call_req.as signee , call_req.call_back_date , call_req.call_back_flag , cal l_req.category , call_req.change , call_req.charge_back_id , cal l_req.close_date , call_req.created_via , call_req.customer , ca ll_req.description , call_req.event_token , call_req.extern_ref 220 404 0.5 3.2 0.27 3.79 618252548 SELECT cr_prp.sequence, cr_prp.id FROM cr_prp WHERE cr_prp.ownin g_cr = :"SYS_B_0" ORDER BY cr_prp.sequence 139 126 1.1 2.1 0.19 2.27 2546835342 SELECT att_evt.fire_time, att_evt.id FROM att_evt WHERE att_evt. obj_id = :"SYS_B_0" AND att_evt.group_name = :"SYS_B_1" ORDER B Y att_evt.fire_time 136 515 0.3 2.0 0.42 2.40 2816620377 INSERT INTO att_evt ( cancel_time, event_tmpl, fire_time, first_ fire_time, group_name, last_mod_dt, num_fire, obj_id, persid, st SQL ordered by Reads for DB: AHD Instance: ahd Snaps: 40 -41 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- art_time, status_flag, user_smag, wait_time, id ) VALUES ( null , :"SYS_B_00" , :"SYS_B_01" , :"SYS_B_02" , :"SYS_B_03" , :"SYS_B_04" , :"SYS_B_05" , :"SYS_B_06" , :"SYS_B_07" , :"SY 112 371 0.3 1.7 0.39 3.20 749386807 SELECT call_req.open_date, call_req.id FROM call_req WHERE call_ req.customer = :"SYS_B_0" AND call_req.active_flag = :"SYS_B_1" ORDER BY call_req.open_date DESC 112 172 0.7 1.7 0.11 1.91 1898030344 SELECT ctct.id FROM ctct WHERE ( ctct.del = :"SYS_B_0" AND ctct. c_userid = :"SYS_B_1" ) AND ( ctct.alias = -:"SYS_B_2" ) 92 122 0.8 1.4 0.16 1.90 126555291 INSERT INTO not_log ( cmth_used, cntxt_obj, del, last_mod, nlh_a ck_time, nlh_c_addressee, nlh_cm_method, nlh_end, nlh_hdr, nlh_m sg, nlh_pri, nlh_start, nlh_status, nlh_transition, nlh_type, nl h_user_ack, id ) VALUES ( :"SYS_B_00" , :"SYS_B_01" , :"SYS_B _02" , :"SYS_B_03" , null , :"SYS_B_04" , :"SYS_B_05" , :"S 81 1,446 0.1 1.2 0.63 1.88 3034229510 SELECT cr_prp.description , cr_prp.label , cr_prp.last_mod_by , cr_prp.last_mod_dt , cr_prp.owning_cr , cr_prp.persid , cr_prp.r equired , cr_prp.sample , cr_prp.sequence , cr_prp.value FROM cr _prp WHERE cr_prp.id = :"SYS_B_0" 74 1,782 0.0 1.1 1.16 2.13 337254211 SELECT ctct.alias , ctct.c_acctyp_id , ctct.c_addr1 , ctct.c_add r2 , ctct.c_addr3 , ctct.c_addr4 , ctct.c_addr5 , ctct.c_addr6 , ctct.c_admin_org_id , ctct.c_available , ctct.c_beeper_phone , ctct.c_city , ctct.c_cm_id1 , ctct.c_cm_id2 , ctct.c_cm_id3 , ct ct.c_cm_id4 , ctct.c_contact_num , ctct.c_country , ctct.c_ctp_i 72 233 0.3 1.1 0.16 1.55 1719939797 DELETE FROM att_evt WHERE id = :"SYS_B_0" 71 114 0.6 1.0 0.13 1.37 1898758418 INSERT INTO act_log ( action_desc, analyst, call_req_id, descrip tion, internal, knowledge_session, knowledge_tool, last_mod_dt, persid, system_time, time_spent, time_stamp, type, id ) VALUES ( :"SYS_B_00" , null , :"SYS_B_01" , :"SYS_B_02" , :"SYS_B_0 3" , null , :"SYS_B_04" , :"SYS_B_05" , :"SYS_B_06" , :"SYS 67 161 0.4 1.0 0.11 1.28 1076399309 select * from CI_PREFERENCES where ANALYST_ID=:"SYS_B_0" ------------------------------------------------------------- SQL ordered by Executions for DB: AHD Instance: ahd Snaps: 40 -41 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 5,973 5,969 1.0 0.00 0.00 1060224445 SELECT grpmem.group_id , grpmem.manager_flag , grpmem.member , g rpmem.notify_flag FROM grpmem WHERE grpmem.id = :"SYS_B_0" 3,051 3,051 1.0 0.00 0.00 3026674282 SELECT act_log.action_desc , act_log.analyst , act_log.call_req_ id , act_log.description , act_log.internal , act_log.knowledge_ session , act_log.knowledge_tool , act_log.last_mod_dt , act_log .persid , act_log.system_time , act_log.time_spent , act_log.tim e_stamp , act_log.type FROM act_log WHERE act_log.id = :"SYS_B_0 2,069 2,068 1.0 0.00 0.00 3346182257 SELECT call_req.active_flag , call_req.affected_rc , call_req.as signee , call_req.call_back_date , call_req.call_back_flag , cal l_req.category , call_req.change , call_req.charge_back_id , cal l_req.close_date , call_req.created_via , call_req.customer , ca ll_req.description , call_req.event_token , call_req.extern_ref 1,782 1,781 1.0 0.00 0.00 337254211 SELECT ctct.alias , ctct.c_acctyp_id , ctct.c_addr1 , ctct.c_add r2 , ctct.c_addr3 , ctct.c_addr4 , ctct.c_addr5 , ctct.c_addr6 , ctct.c_admin_org_id , ctct.c_available , ctct.c_beeper_phone , ctct.c_city , ctct.c_cm_id1 , ctct.c_cm_id2 , ctct.c_cm_id3 , ct ct.c_cm_id4 , ctct.c_contact_num , ctct.c_country , ctct.c_ctp_i 1,446 1,446 1.0 0.00 0.00 3034229510 SELECT cr_prp.description , cr_prp.label , cr_prp.last_mod_by , cr_prp.last_mod_dt , cr_prp.owning_cr , cr_prp.persid , cr_prp.r equired , cr_prp.sample , cr_prp.sequence , cr_prp.value FROM cr _prp WHERE cr_prp.id = :"SYS_B_0" 1,185 1,185 1.0 0.00 0.00 1199698393 SELECT loc.alias , loc.del , loc.l_addr1 , loc.l_addr2 , loc.l_a ddr3 , loc.l_addr4 , loc.l_addr5 , loc.l_addr6 , loc.l_details , loc.l_name , loc.l_si_id , loc.last_mod , loc.persid , loc.z_cb _l1 , loc.z_cb_l2 , loc.z_cb_l3 , loc.z_l_code , loc.z_ro_code , loc.z_zo_code FROM loc WHERE loc.id = :"SYS_B_0" 1,012 1,011 1.0 0.00 0.00 3299996875 SELECT att_evt.cancel_time , att_evt.event_tmpl , att_evt.fire_t ime , att_evt.first_fire_time , att_evt.group_name , att_evt.las t_mod_dt , att_evt.num_fire , att_evt.obj_id , att_evt.persid , att_evt.start_time , att_evt.status_flag , att_evt.user_smag , a tt_evt.wait_time FROM att_evt WHERE att_evt.id = :"SYS_B_0" 810 810 1.0 0.00 0.00 4078915446 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = :"SYS_B_0" ) AND ( options.del = :"SYS_B_1" ) ORDER BY options.app_name 655 316 0.5 0.00 0.00 800192270 SELECT lrel.l_persid, lrel.l_attr, lrel.l_sql, lrel.r_persid, lr el.r_attr, lrel.r_sql, lrel.id FROM lrel WHERE lrel.l_persid = : "SYS_B_0" and lrel.l_attr = :"SYS_B_1" ORDER BY lrel.l_persid , SQL ordered by Executions for DB: AHD Instance: ahd Snaps: 40 -41 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- lrel.l_attr , lrel.l_sql 555 555 1.0 0.00 0.00 3426702642 SELECT usq.analyst , usq.expanded , usq.factory , usq.label , us q.parent , usq.persid , usq.query , usq.query_set , usq.sequence FROM usq WHERE usq.id = :"SYS_B_0" 517 517 1.0 0.00 0.00 321149819 INSERT INTO anima ( a_act, a_delta, a_lock, a_name, a_org, a_str ing, a_time, t_method, t_persid, t_type, id ) VALUES ( :"SYS_B_ 0" , :"SYS_B_1" , :"SYS_B_2" , :"SYS_B_3" , :"SYS_B_4" , nu ll , :"SYS_B_5" , :"SYS_B_6" , :"SYS_B_7" , :"SYS_B_8" , :" SYS_B_9" ) 515 515 1.0 0.00 0.00 2816620377 INSERT INTO att_evt ( cancel_time, event_tmpl, fire_time, first_ fire_time, group_name, last_mod_dt, num_fire, obj_id, persid, st art_time, status_flag, user_smag, wait_time, id ) VALUES ( null , :"SYS_B_00" , :"SYS_B_01" , :"SYS_B_02" , :"SYS_B_03" , :"SYS_B_04" , :"SYS_B_05" , :"SYS_B_06" , :"SYS_B_07" , :"SY 480 480 1.0 0.00 0.00 1317334374 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME=:"SYS_B_0" 426 425 1.0 0.00 0.00 1549656119 SELECT crsq.id FROM crsq WHERE crsq.code = :"SYS_B_0" 414 414 1.0 0.00 0.00 2149307702 SELECT evt.condition , evt.del , evt.delay_time , evt.descriptio n , evt.last_mod_dt , evt.modulo_time , evt.obj_type , evt.on_do ne_flag , evt.persid , evt.sym , evt.user_settime , evt.user_sma g , evt.work_shift FROM evt WHERE evt.id = :"SYS_B_0" 404 1,662 4.1 0.00 0.01 618252548 SELECT cr_prp.sequence, cr_prp.id FROM cr_prp WHERE cr_prp.ownin g_cr = :"SYS_B_0" ORDER BY cr_prp.sequence 386 386 1.0 0.00 0.00 3605948696 SELECT slatpl.del , slatpl.elapsed , slatpl.event , slatpl.last_ mod_by , slatpl.last_mod_dt , slatpl.object_type , slatpl.persid , slatpl.service_type , slatpl.sym FROM slatpl WHERE slatpl.id = :"SYS_B_0" ------------------------------------------------------------- SQL ordered by Parse Calls for DB: AHD Instance: ahd Snaps: 40 -41 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 5,968 5,973 19.18 1060224445 SELECT grpmem.group_id , grpmem.manager_flag , grpmem.member , g rpmem.notify_flag FROM grpmem WHERE grpmem.id = :"SYS_B_0" 3,050 3,051 9.80 3026674282 SELECT act_log.action_desc , act_log.analyst , act_log.call_req_ id , act_log.description , act_log.internal , act_log.knowledge_ session , act_log.knowledge_tool , act_log.last_mod_dt , act_log .persid , act_log.system_time , act_log.time_spent , act_log.tim e_stamp , act_log.type FROM act_log WHERE act_log.id = :"SYS_B_0 2,069 2,069 6.65 3346182257 SELECT call_req.active_flag , call_req.affected_rc , call_req.as signee , call_req.call_back_date , call_req.call_back_flag , cal l_req.category , call_req.change , call_req.charge_back_id , cal l_req.close_date , call_req.created_via , call_req.customer , ca ll_req.description , call_req.event_token , call_req.extern_ref 1,782 1,782 5.73 337254211 SELECT ctct.alias , ctct.c_acctyp_id , ctct.c_addr1 , ctct.c_add r2 , ctct.c_addr3 , ctct.c_addr4 , ctct.c_addr5 , ctct.c_addr6 , ctct.c_admin_org_id , ctct.c_available , ctct.c_beeper_phone , ctct.c_city , ctct.c_cm_id1 , ctct.c_cm_id2 , ctct.c_cm_id3 , ct ct.c_cm_id4 , ctct.c_contact_num , ctct.c_country , ctct.c_ctp_i 1,444 1,446 4.64 3034229510 SELECT cr_prp.description , cr_prp.label , cr_prp.last_mod_by , cr_prp.last_mod_dt , cr_prp.owning_cr , cr_prp.persid , cr_prp.r equired , cr_prp.sample , cr_prp.sequence , cr_prp.value FROM cr _prp WHERE cr_prp.id = :"SYS_B_0" 1,185 1,185 3.81 1199698393 SELECT loc.alias , loc.del , loc.l_addr1 , loc.l_addr2 , loc.l_a ddr3 , loc.l_addr4 , loc.l_addr5 , loc.l_addr6 , loc.l_details , loc.l_name , loc.l_si_id , loc.last_mod , loc.persid , loc.z_cb _l1 , loc.z_cb_l2 , loc.z_cb_l3 , loc.z_l_code , loc.z_ro_code , loc.z_zo_code FROM loc WHERE loc.id = :"SYS_B_0" 1,012 1,012 3.25 3299996875 SELECT att_evt.cancel_time , att_evt.event_tmpl , att_evt.fire_t ime , att_evt.first_fire_time , att_evt.group_name , att_evt.las t_mod_dt , att_evt.num_fire , att_evt.obj_id , att_evt.persid , att_evt.start_time , att_evt.status_flag , att_evt.user_smag , a tt_evt.wait_time FROM att_evt WHERE att_evt.id = :"SYS_B_0" 810 810 2.60 4078915446 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = :"SYS_B_0" ) AND ( options.del = :"SYS_B_1" ) ORDER BY options.app_name 651 655 2.09 800192270 SELECT lrel.l_persid, lrel.l_attr, lrel.l_sql, lrel.r_persid, lr el.r_attr, lrel.r_sql, lrel.id FROM lrel WHERE lrel.l_persid = : "SYS_B_0" and lrel.l_attr = :"SYS_B_1" ORDER BY lrel.l_persid , SQL ordered by Parse Calls for DB: AHD Instance: ahd Snaps: 40 -41 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- lrel.l_attr , lrel.l_sql 555 555 1.78 3426702642 SELECT usq.analyst , usq.expanded , usq.factory , usq.label , us q.parent , usq.persid , usq.query , usq.query_set , usq.sequence FROM usq WHERE usq.id = :"SYS_B_0" 517 517 1.66 321149819 INSERT INTO anima ( a_act, a_delta, a_lock, a_name, a_org, a_str ing, a_time, t_method, t_persid, t_type, id ) VALUES ( :"SYS_B_ 0" , :"SYS_B_1" , :"SYS_B_2" , :"SYS_B_3" , :"SYS_B_4" , nu ll , :"SYS_B_5" , :"SYS_B_6" , :"SYS_B_7" , :"SYS_B_8" , :" SYS_B_9" ) 515 515 1.65 2816620377 INSERT INTO att_evt ( cancel_time, event_tmpl, fire_time, first_ fire_time, group_name, last_mod_dt, num_fire, obj_id, persid, st art_time, status_flag, user_smag, wait_time, id ) VALUES ( null , :"SYS_B_00" , :"SYS_B_01" , :"SYS_B_02" , :"SYS_B_03" , :"SYS_B_04" , :"SYS_B_05" , :"SYS_B_06" , :"SYS_B_07" , :"SY 480 480 1.54 1317334374 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME=:"SYS_B_0" 418 426 1.34 1549656119 SELECT crsq.id FROM crsq WHERE crsq.code = :"SYS_B_0" 414 414 1.33 2149307702 SELECT evt.condition , evt.del , evt.delay_time , evt.descriptio n , evt.last_mod_dt , evt.modulo_time , evt.obj_type , evt.on_do ne_flag , evt.persid , evt.sym , evt.user_settime , evt.user_sma g , evt.work_shift FROM evt WHERE evt.id = :"SYS_B_0" 404 404 1.30 618252548 SELECT cr_prp.sequence, cr_prp.id FROM cr_prp WHERE cr_prp.ownin g_cr = :"SYS_B_0" ORDER BY cr_prp.sequence 386 386 1.24 3605948696 SELECT slatpl.del , slatpl.elapsed , slatpl.event , slatpl.last_ mod_by , slatpl.last_mod_dt , slatpl.object_type , slatpl.persid , slatpl.service_type , slatpl.sym FROM slatpl WHERE slatpl.id = :"SYS_B_0" ------------------------------------------------------------- SQL ordered by Sharable Memory for DB: AHD Instance: ahd Snaps: 40 -41 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Hash Value ---------------- ------------ ------- ------------ 17,560,036 133 9.5 1877135209 SELECT chg.open_date, chg.chg_ref_num, chg.id FROM chg WHERE ( c hg.affected_contact = :"SYS_B_0" and chg.active_flag = :"SYS_B_1 " ) AND ( chg.affected_contact = :"SYS_B_2" ) ORDER BY chg.open _date DESC 6,551,576 90 3.6 139964375 SELECT anima.a_name, anima.t_persid, anima.t_method, anima.id FR OM anima WHERE anima.t_persid LIKE :"SYS_B_0" ORDER BY anima. a_name ------------------------------------------------------------- SQL ordered by Version Count for DB: AHD Instance: ahd Snaps: 40 -41 -> End Version Count Threshold: 20 Version Count Executions Hash Value -------- ------------ ------------ 237 133 1877135209 SELECT chg.open_date, chg.chg_ref_num, chg.id FROM chg WHERE ( c hg.affected_contact = :"SYS_B_0" and chg.active_flag = :"SYS_B_1 " ) AND ( chg.affected_contact = :"SYS_B_2" ) ORDER BY chg.open _date DESC 114 90 139964375 SELECT anima.a_name, anima.t_persid, anima.t_method, anima.id FR OM anima WHERE anima.t_persid LIKE :"SYS_B_0" ORDER BY anima. a_name ------------------------------------------------------------- Instance Activity Stats for DB: AHD Instance: ahd Snaps: 40 -41 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 39,842 82.0 13.9 CPU used when call started 39,842 82.0 13.9 CR blocks created 48 0.1 0.0 Cached Commit SCN referenced 3,464,988 7,129.6 1,205.2 Commit SCN cached 0 0.0 0.0 SQL*Net roundtrips to/from client 208,269 428.5 72.4 active txn count during cleanout 62 0.1 0.0 background timeouts 736 1.5 0.3 buffer is not pinned count 11,275,424 23,200.5 3,921.9 buffer is pinned count 1,744,743 3,590.0 606.9 bytes received via SQL*Net from c 30,323,630 62,394.3 10,547.4 bytes sent via SQL*Net to client 54,729,850 112,612.9 19,036.5 calls to get snapshot scn: kcmgss 125,467 258.2 43.6 calls to kcmgas 3,175 6.5 1.1 calls to kcmgcs 61 0.1 0.0 change write time 58 0.1 0.0 cleanout - number of ktugct calls 97 0.2 0.0 cleanouts and rollbacks - consist 7 0.0 0.0 cleanouts only - consistent read 1 0.0 0.0 cluster key scan block gets 6,065 12.5 2.1 cluster key scans 5,937 12.2 2.1 commit cleanouts 11,804 24.3 4.1 commit cleanouts successfully com 11,804 24.3 4.1 commit txn count during cleanout 258 0.5 0.1 consistent changes 66 0.1 0.0 consistent gets 12,228,949 25,162.5 4,253.6 consistent gets - examination 957,597 1,970.4 333.1 cursor authentications 140 0.3 0.1 data blocks consistent reads - un 66 0.1 0.0 db block changes 33,508 69.0 11.7 db block gets 38,990 80.2 13.6 deferred (CURRENT) block cleanout 6,784 14.0 2.4 enqueue conversions 29 0.1 0.0 enqueue releases 7,041 14.5 2.5 enqueue requests 7,041 14.5 2.5 execute count 33,076 68.1 11.5 free buffer inspected 0 0.0 0.0 free buffer requested 7,250 14.9 2.5 immediate (CR) block cleanout app 8 0.0 0.0 immediate (CURRENT) block cleanou 2,040 4.2 0.7 index fast full scans (full) 30 0.1 0.0 index fetch by key 858,552 1,766.6 298.6 index scans kdiixs1 12,030 24.8 4.2 leaf node 90-10 splits 7 0.0 0.0 leaf node splits 46 0.1 0.0 logons cumulative 3 0.0 0.0 messages received 2,993 6.2 1.0 messages sent 2,993 6.2 1.0 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 11,173,884 22,991.5 3,886.6 opened cursors cumulative 3,692 7.6 1.3 parse count (failures) 4 0.0 0.0 parse count (hard) 498 1.0 0.2 parse count (total) 31,118 64.0 10.8 parse time cpu 742 1.5 0.3 parse time elapsed 926 1.9 0.3 Instance Activity Stats for DB: AHD Instance: ahd Snaps: 40 -41 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads 6,780 14.0 2.4 physical reads direct 15 0.0 0.0 physical writes 15 0.0 0.0 physical writes direct 15 0.0 0.0 physical writes non checkpoint 15 0.0 0.0 prefetched blocks 48 0.1 0.0 process last non-idle time 3,642,889,492 7,495,657.4 1,267,092.0 recursive calls 36,240 74.6 12.6 recursive cpu usage 171 0.4 0.1 redo blocks written 12,469 25.7 4.3 redo entries 17,966 37.0 6.3 redo size 5,510,756 11,339.0 1,916.8 redo synch time 487 1.0 0.2 redo synch writes 2,875 5.9 1.0 redo wastage 707,816 1,456.4 246.2 redo write time 396 0.8 0.1 redo writes 2,993 6.2 1.0 rollbacks only - consistent read 56 0.1 0.0 rows fetched via callback 65,441 134.7 22.8 session connect time 3,642,889,492 7,495,657.4 1,267,092.0 session logical reads 12,267,939 25,242.7 4,267.1 session pga memory 1,812,460 3,729.3 630.4 session pga memory max 46,834,980 96,368.3 16,290.4 session uga memory 1,534,828 3,158.1 533.9 session uga memory max 44,538,528 91,643.1 15,491.7 shared hash latch upgrades - no w 13,667 28.1 4.8 shared hash latch upgrades - wait 3 0.0 0.0 sorts (memory) 6,084 12.5 2.1 sorts (rows) 8,753,970 18,012.3 3,044.9 switch current to new buffer 62 0.1 0.0 table fetch by rowid 993,489 2,044.2 345.6 table fetch continued row 16 0.0 0.0 table scan blocks gotten 10,180,335 20,947.2 3,541.0 table scan rows gotten 145,498,110 299,378.8 50,608.0 table scans (long tables) 374 0.8 0.1 table scans (short tables) 1,400 2.9 0.5 user calls 208,351 428.7 72.5 user commits 2,875 5.9 1.0 workarea executions - optimal 6,392 13.2 2.2 ------------------------------------------------------------- Tablespace IO Stats for DB: AHD Instance: ahd Snaps: 40 -41 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ AHD1_IDX 4,106 8 12.0 1.0 0 0 183 12.6 AHD1_DATA 2,155 4 16.2 1.0 0 0 4 15.0 PERFSTAT 349 1 9.7 1.0 0 0 0 0.0 SYSTEM 140 0 14.4 1.0 0 0 0 0.0 TEMP 3 0 6.7 5.7 1 0 0 0.0 CWMLITE 2 0 0.0 1.0 0 0 0 0.0 DRSYS 2 0 0.0 1.0 0 0 0 0.0 EXAMPLE 2 0 0.0 1.0 0 0 0 0.0 INDX 2 0 0.0 1.0 0 0 0 0.0 ODM 2 0 0.0 1.0 0 0 0 0.0 RMAN 2 0 0.0 1.0 0 0 0 0.0 TOOLS 2 0 0.0 1.0 0 0 0 0.0 UNDOTBS1 2 0 0.0 1.0 0 0 0 0.0 USERS 2 0 0.0 1.0 0 0 0 0.0 XDB 2 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: AHD Instance: ahd Snaps: 40 -41 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ AHD1_DATA E:\ORACLE\ORADATA\AHD\AHD1_DATA.ORA 1,097 2 15.0 1.0 0 0 2 15.0 E:\ORACLE\ORADATA\AHD\AHD2_DATA.ORA 954 2 17.5 1.0 0 0 2 15.0 E:\ORACLE\ORADATA\AHD\AHD3_DATA.ORA 104 0 17.0 1.0 0 0 0 AHD1_IDX E:\ORACLE\ORADATA\AHD\AHD1_IDX.ORA 4,106 8 12.0 1.0 0 0 183 12.6 CWMLITE E:\ORACLE\ORADATA\AHD\CWMLITE01.DBF 2 0 0.0 1.0 0 0 0 DRSYS E:\ORACLE\ORADATA\AHD\DRSYS01.DBF 2 0 0.0 1.0 0 0 0 EXAMPLE E:\ORACLE\ORADATA\AHD\EXAMPLE01.DBF 2 0 0.0 1.0 0 0 0 INDX E:\ORACLE\ORADATA\AHD\INDX01.DBF 2 0 0.0 1.0 0 0 0 ODM E:\ORACLE\ORADATA\AHD\ODM01.DBF 2 0 0.0 1.0 0 0 0 PERFSTAT E:\ORACLE\ORADATA\AHD\PERFSTAT.ORA 349 1 9.7 1.0 0 0 0 RMAN E:\ORACLE\ORADATA\AHD\RMAN.ORA 2 0 0.0 1.0 0 0 0 SYSTEM E:\ORACLE\ORADATA\AHD\SYSTEM01.DBF 140 0 14.4 1.0 0 0 0 TEMP E:\ORACLE\ORADATA\AHD\TEMP01.DBF 3 0 6.7 5.7 1 0 0 TOOLS E:\ORACLE\ORADATA\AHD\TOOLS01.DBF 2 0 0.0 1.0 0 0 0 UNDOTBS1 E:\ORACLE\ORADATA\AHD\UNDOTBS01.DBF 2 0 0.0 1.0 0 0 0 USERS E:\ORACLE\ORADATA\AHD\USERS01.DBF 2 0 0.0 1.0 0 0 0 XDB E:\ORACLE\ORADATA\AHD\XDB01.DBF 2 0 0.0 1.0 0 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 82,082 100.0 22,419,030 6,810 0 0 0 191 ------------------------------------------------------------- Instance Recovery Stats for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 300 22 1443 5116 89977 184320 89977 E 300 37 3270 17574 101829 184320 101829 ------------------------------------------------------------- Buffer Pool Advisory for DB: AHD Instance: ahd End Snap: 41 -> 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 64 .1 8,008 168.33 9,270,388 D 128 .2 16,016 113.01 6,223,861 D 192 .3 24,024 82.57 4,547,298 D 256 .4 32,032 25.34 1,395,691 D 320 .5 40,040 10.52 579,266 D 384 .6 48,048 1.01 55,557 D 448 .7 56,056 1.00 55,074 D 512 .8 64,064 1.00 55,074 D 576 .9 72,072 1.00 55,074 D 640 1.0 80,080 1.00 55,074 D 656 1.0 82,082 1.00 55,074 D 704 1.1 88,088 1.00 55,074 D 768 1.2 96,096 1.00 55,074 D 832 1.3 104,104 1.00 55,074 D 896 1.4 112,112 1.00 55,074 D 960 1.5 120,120 1.00 55,074 D 1,024 1.6 128,128 1.00 55,074 D 1,088 1.7 136,136 1.00 55,074 D 1,152 1.8 144,144 1.00 55,074 D 1,216 1.9 152,152 1.00 55,074 D 1,280 2.0 160,160 1.00 55,074 ------------------------------------------------------------- Buffer wait Statistics for DB: AHD Instance: ahd Snaps: 40 -41 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 187 2 12 ------------------------------------------------------------- PGA Aggr Target Stats for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 2,002 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 350 299 25.5 0.3 1.3 100.0 .0 17,920 E 350 298 27.4 0.6 2.3 100.0 .0 17,920 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: AHD Instance: ahd Snaps: 40 -41 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 5,315 5,315 0 0 16K 32K 87 87 0 0 32K 64K 124 124 0 0 64K 128K 77 77 0 0 128K 256K 73 73 0 0 256K 512K 45 45 0 0 512K 1024K 126 126 0 0 1M 2M 189 189 0 0 4M 8M 275 275 0 0 8M 16M 1 1 0 0 16M 32M 1 1 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: AHD Instance: ahd End Snap: 41 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 44 0.1 3,321.9 397.8 89.0 0 88 0.3 3,321.9 337.9 91.0 0 175 0.5 3,321.9 32.2 99.0 0 263 0.8 3,321.9 32.2 99.0 0 350 1.0 3,321.9 0.0 100.0 0 420 1.2 3,321.9 0.0 100.0 0 490 1.4 3,321.9 0.0 100.0 0 560 1.6 3,321.9 0.0 100.0 0 630 1.8 3,321.9 0.0 100.0 0 700 2.0 3,321.9 0.0 100.0 0 1,050 3.0 3,321.9 0.0 100.0 0 1,400 4.0 3,321.9 0.0 100.0 0 2,100 6.0 3,321.9 0.0 100.0 0 2,800 8.0 3,321.9 0.0 100.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: AHD Instance: ahd Snaps: 40 -41 ->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 11.0 0.00 0 0 0 0 1 391.0 0.00 79,474 0 0 0 2 539.0 0.00 142,058 1 0 0 3 403.0 0.00 93,788 0 0 0 4 884.0 0.00 193,936 0 0 0 5 945.0 0.00 215,132 1 0 0 6 1,078.0 0.00 570,842 1 0 0 7 896.0 0.00 197,306 0 0 0 8 406.0 0.00 89,654 0 0 0 9 545.0 0.00 159,666 0 0 0 10 405.0 0.00 93,732 0 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: AHD Instance: ahd Snaps: 40 -41 ->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 3,268,608 0 3,268,608 2 2,220,032 11,714 2,220,032 3 3,268,608 0 3,268,608 4 3,268,608 0 3,268,608 5 4,317,184 104,857 4,317,184 6 3,268,608 104,857 3,268,608 7 3,268,608 0 3,268,608 8 2,220,032 0 2,220,032 9 2,220,032 0 2,220,032 10 2,220,032 0 2,220,032 ------------------------------------------------------------- Undo Segment Summary for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 213 2,270 82 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: AHD Instance: ahd Snaps: 40 -41 -> 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 ------------ ------------ -------- ------- -------- ------- ------ ------------- 24-Jun 14:04 213 2,270 82 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: AHD Instance: ahd Snaps: 40 -41 ->"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 2,993 0.0 0 0 FOB s.o list latch 44 0.0 0 0 SQL memory manager latch 1 0.0 0 157 0.0 SQL memory manager worka 14,195 0.0 0 0 active checkpoint queue 316 0.0 0 0 archive control 56 0.0 0 0 archive process latch 9 0.0 0 0 cache buffer handles 188 0.0 0 0 cache buffers chains 23,663,815 3.5 0.0 0 7,219 0.0 cache buffers lru chain 9,911,979 2.4 0.0 0 7,267 0.1 channel handle pool latc 5 0.0 0 0 channel operations paren 323 0.0 0 0 checkpoint queue latch 15,182 0.0 0 1,838 0.0 child cursor hash table 8,682 0.0 0 0 dml lock allocation 6,252 0.0 0 0 dummy allocation 5 0.0 0 0 enqueue hash chains 14,105 0.0 0.0 0 0 enqueues 6,643 0.0 0 0 event group latch 3 0.0 0 0 file number translation 5,140 0.0 0 0 hash table column usage 0 0 505,774 0.0 hash table modification 3 0.0 0 0 job_queue_processes para 8 0.0 0 0 ktm global data 1 0.0 0 0 kwqit: protect wakeup ti 15 0.0 0 0 lgwr LWN SCN 3,023 0.5 0.0 0 0 library cache 352,862 0.4 0.0 0 0 library cache load lock 1,084 0.0 0 0 library cache pin 204,839 0.1 0.0 0 0 library cache pin alloca 131,705 0.1 0.0 0 0 list of block allocation 32 0.0 0 0 messages 17,403 0.0 0 0 mostly latch-free SCN 3,068 1.4 0.0 0 0 multiblock read objects 20 0.0 0 0 ncodef allocation latch 8 0.0 0 0 object stats modificatio 128 0.0 0 0 post/wait queue 5,988 0.0 0 2,875 0.0 process allocation 3 0.0 0 3 0.0 process group creation 5 0.0 0 0 redo allocation 23,964 0.0 0.0 0 0 redo copy 0 0 17,970 0.0 redo writing 6,633 0.0 0 0 row cache enqueue latch 386,809 0.0 0.0 0 0 row cache objects 387,469 0.1 0.0 0 0 sequence cache 12 0.0 0 0 session allocation 10,234 0.0 0 0 session idle bit 421,632 0.0 0.0 0 0 session switching 8 0.0 0 0 session timer 167 0.0 0 0 shared pool 109,064 0.2 0.0 0 0 Latch Activity for DB: AHD Instance: ahd Snaps: 40 -41 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ sim partition latch 0 0 2 0.0 simulator hash latch 673,169 0.0 0.0 0 0 simulator lru latch 858 0.0 0 5,124 5.4 sort extent pool 1,248 0.0 0 0 trace latch 2 0.0 0 0 transaction allocation 20 0.0 0 0 transaction branch alloc 8 0.0 0 0 undo global data 10,528 0.0 0.0 0 0 user lock 10 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: AHD Instance: ahd Snaps: 40 -41 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 23,663,815 833,337 677 0/0/0/0/0 cache buffers lru chain 9,911,979 235,653 159 235496/155/2 /0/0 library cache 352,862 1,310 1 1309/1/0/0/0 simulator hash latch 673,169 214 1 213/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbgtcr: kslbegin excl 0 440 438 cache buffers chains kcbrls: kslbegin 0 219 219 cache buffers chains kcbgtcr: fast path 0 11 14 cache buffers chains kcbzwb 0 4 4 cache buffers chains kcbchg: kslbegin: bufs not 0 2 1 cache buffers chains kcbgtcr: kslbegin shared 0 1 0 cache buffers lru chain kcbzar: KSLNBEGIN 0 159 159 library cache kglpin: child: heap proces 0 1 0 simulator hash latch kcbsacc: lookup dba 0 1 1 ------------------------------------------------------------- Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 775 1,594,661 147,920 236 0/0/0/0/0 cache buffers chains 361 193,322 792 11 0/0/0/0/0 cache buffers chains 351 56,194 1,634 1 0/0/0/0/0 cache buffers chains 956 52,272 748 1 0/0/0/0/0 cache buffers chains 703 52,211 1,275 1 0/0/0/0/0 cache buffers chains 340 45,398 999 4 0/0/0/0/0 cache buffers chains 730 40,048 1,671 2 0/0/0/0/0 cache buffers chains 508 30,747 816 1 0/0/0/0/0 cache buffers chains 869 28,571 593 1 0/0/0/0/0 cache buffers chains 78 27,380 827 1 0/0/0/0/0 cache buffers chains 427 26,955 742 1 0/0/0/0/0 cache buffers chains 765 26,659 785 1 0/0/0/0/0 cache buffers chains 451 26,426 762 4 0/0/0/0/0 cache buffers chains 531 26,059 830 1 0/0/0/0/0 cache buffers chains 183 25,864 707 3 0/0/0/0/0 cache buffers chains 21 25,575 846 2 0/0/0/0/0 cache buffers chains 163 25,304 684 1 0/0/0/0/0 cache buffers chains 431 25,189 637 1 0/0/0/0/0 cache buffers chains 563 25,188 792 1 0/0/0/0/0 cache buffers chains 968 24,757 666 2 0/0/0/0/0 cache buffers chains 996 24,752 823 3 0/0/0/0/0 cache buffers chains 387 24,729 785 1 0/0/0/0/0 cache buffers chains 862 24,701 748 2 0/0/0/0/0 cache buffers chains 863 24,598 767 2 0/0/0/0/0 cache buffers chains 1017 24,427 809 3 0/0/0/0/0 cache buffers chains 574 24,412 780 1 0/0/0/0/0 cache buffers chains 169 24,343 766 1 0/0/0/0/0 cache buffers chains 184 24,283 657 1 0/0/0/0/0 cache buffers chains 164 24,278 699 1 0/0/0/0/0 cache buffers chains 565 24,249 786 2 0/0/0/0/0 cache buffers chains 167 24,242 772 1 0/0/0/0/0 cache buffers chains 939 24,184 740 3 0/0/0/0/0 cache buffers chains 424 24,177 759 2 0/0/0/0/0 cache buffers chains 210 24,154 745 1 0/0/0/0/0 cache buffers chains 422 24,012 762 1 0/0/0/0/0 cache buffers chains 418 23,938 754 2 0/0/0/0/0 cache buffers chains 1018 23,875 765 1 0/0/0/0/0 cache buffers chains 566 23,808 754 1 0/0/0/0/0 cache buffers chains 509 23,804 836 1 0/0/0/0/0 cache buffers chains 539 23,800 854 2 0/0/0/0/0 cache buffers chains 865 23,782 763 2 0/0/0/0/0 cache buffers chains 559 23,707 783 1 0/0/0/0/0 cache buffers chains 445 23,652 720 2 0/0/0/0/0 cache buffers chains 205 23,627 761 1 0/0/0/0/0 cache buffers chains 166 23,608 688 4 0/0/0/0/0 cache buffers chains 450 23,599 748 1 0/0/0/0/0 cache buffers chains 171 23,584 756 2 0/0/0/0/0 cache buffers chains 208 23,503 737 1 0/0/0/0/0 cache buffers chains 534 23,502 823 1 0/0/0/0/0 cache buffers chains 209 23,480 729 2 0/0/0/0/0 cache buffers chains 860 23,415 723 2 0/0/0/0/0 cache buffers chains 660 23,361 719 1 0/0/0/0/0 cache buffers chains 859 23,285 730 1 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 567 23,281 709 1 0/0/0/0/0 cache buffers chains 502 23,278 754 4 0/0/0/0/0 cache buffers chains 16 23,273 758 2 0/0/0/0/0 cache buffers chains 28 23,214 757 4 0/0/0/0/0 cache buffers chains 975 23,160 811 1 0/0/0/0/0 cache buffers chains 653 23,102 753 1 0/0/0/0/0 cache buffers chains 193 23,016 594 3 0/0/0/0/0 cache buffers chains 558 23,004 758 1 0/0/0/0/0 cache buffers chains 581 22,927 757 1 0/0/0/0/0 cache buffers chains 38 22,904 702 2 0/0/0/0/0 cache buffers chains 170 22,844 742 2 0/0/0/0/0 cache buffers chains 354 22,755 766 1 0/0/0/0/0 cache buffers chains 191 22,743 576 1 0/0/0/0/0 cache buffers chains 843 22,699 762 1 0/0/0/0/0 cache buffers chains 417 22,673 717 1 0/0/0/0/0 cache buffers chains 652 22,657 688 2 0/0/0/0/0 cache buffers chains 769 22,613 710 1 0/0/0/0/0 cache buffers chains 33 22,604 711 1 0/0/0/0/0 cache buffers chains 985 22,568 733 1 0/0/0/0/0 cache buffers chains 631 22,566 708 3 0/0/0/0/0 cache buffers chains 846 22,549 673 1 0/0/0/0/0 cache buffers chains 444 22,546 730 1 0/0/0/0/0 cache buffers chains 577 22,516 699 1 0/0/0/0/0 cache buffers chains 970 22,514 719 3 0/0/0/0/0 cache buffers chains 651 22,475 703 3 0/0/0/0/0 cache buffers chains 160 22,470 665 1 0/0/0/0/0 cache buffers chains 419 22,466 710 1 0/0/0/0/0 cache buffers chains 483 22,453 702 1 0/0/0/0/0 cache buffers chains 845 22,433 672 1 0/0/0/0/0 cache buffers chains 82 22,418 733 1 0/0/0/0/0 cache buffers chains 81 22,396 745 2 0/0/0/0/0 cache buffers chains 920 22,363 795 2 0/0/0/0/0 cache buffers chains 504 22,349 772 2 0/0/0/0/0 cache buffers chains 507 22,298 797 1 0/0/0/0/0 cache buffers chains 630 22,231 696 2 0/0/0/0/0 cache buffers chains 987 22,231 690 1 0/0/0/0/0 cache buffers chains 751 22,186 787 1 0/0/0/0/0 cache buffers chains 347 22,153 656 1 0/0/0/0/0 cache buffers chains 999 22,117 785 2 0/0/0/0/0 cache buffers chains 607 22,115 748 1 0/0/0/0/0 cache buffers chains 612 22,095 682 2 0/0/0/0/0 cache buffers chains 214 22,057 654 1 0/0/0/0/0 cache buffers chains 579 22,042 621 1 0/0/0/0/0 cache buffers chains 989 21,982 650 1 0/0/0/0/0 cache buffers chains 476 21,957 764 1 0/0/0/0/0 cache buffers chains 969 21,928 632 1 0/0/0/0/0 cache buffers chains 991 21,922 700 2 0/0/0/0/0 cache buffers chains 591 21,899 771 2 0/0/0/0/0 cache buffers chains 600 21,875 812 1 0/0/0/0/0 cache buffers chains 536 21,841 796 1 0/0/0/0/0 cache buffers chains 649 21,839 696 1 0/0/0/0/0 cache buffers chains 658 21,834 664 1 0/0/0/0/0 cache buffers chains 199 21,724 682 2 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 771 21,691 743 1 0/0/0/0/0 cache buffers chains 1010 21,672 620 1 0/0/0/0/0 cache buffers chains 735 21,625 656 1 0/0/0/0/0 cache buffers chains 133 21,620 666 2 0/0/0/0/0 cache buffers chains 585 21,554 655 1 0/0/0/0/0 cache buffers chains 261 21,508 714 1 0/0/0/0/0 cache buffers chains 767 21,507 679 2 0/0/0/0/0 cache buffers chains 1013 21,483 673 2 0/0/0/0/0 cache buffers chains 598 21,453 768 1 0/0/0/0/0 cache buffers chains 745 21,447 738 1 0/0/0/0/0 cache buffers chains 919 21,444 753 1 0/0/0/0/0 cache buffers chains 709 21,425 660 1 0/0/0/0/0 cache buffers chains 530 21,394 728 1 0/0/0/0/0 cache buffers chains 998 21,369 739 1 0/0/0/0/0 cache buffers chains 638 21,366 610 5 0/0/0/0/0 cache buffers chains 669 21,360 712 1 0/0/0/0/0 cache buffers chains 70 21,320 712 1 0/0/0/0/0 cache buffers chains 925 21,317 752 1 0/0/0/0/0 cache buffers chains 1000 21,314 760 1 0/0/0/0/0 cache buffers chains 893 21,309 719 1 0/0/0/0/0 cache buffers chains 635 21,250 560 1 0/0/0/0/0 cache buffers chains 634 21,247 624 2 0/0/0/0/0 cache buffers chains 146 21,225 674 1 0/0/0/0/0 cache buffers chains 637 21,208 608 1 0/0/0/0/0 cache buffers chains 746 21,204 732 1 0/0/0/0/0 cache buffers chains 381 21,201 682 1 0/0/0/0/0 cache buffers chains 556 21,181 719 1 0/0/0/0/0 cache buffers chains 938 21,173 646 1 0/0/0/0/0 cache buffers chains 588 21,151 708 1 0/0/0/0/0 cache buffers chains 667 21,144 707 4 0/0/0/0/0 cache buffers chains 150 21,071 628 1 0/0/0/0/0 cache buffers chains 136 21,056 780 1 0/0/0/0/0 cache buffers chains 840 21,042 723 1 0/0/0/0/0 cache buffers chains 85 21,028 666 2 0/0/0/0/0 cache buffers chains 597 20,937 740 2 0/0/0/0/0 cache buffers chains 401 20,934 674 1 0/0/0/0/0 cache buffers chains 147 20,916 647 1 0/0/0/0/0 cache buffers chains 438 20,903 688 1 0/0/0/0/0 cache buffers chains 158 20,857 579 1 0/0/0/0/0 cache buffers chains 144 20,854 711 1 0/0/0/0/0 cache buffers chains 742 20,825 690 1 0/0/0/0/0 cache buffers chains 458 20,817 630 1 0/0/0/0/0 cache buffers chains 466 20,768 679 2 0/0/0/0/0 cache buffers chains 475 20,762 727 2 0/0/0/0/0 cache buffers chains 555 20,762 753 2 0/0/0/0/0 cache buffers chains 201 20,752 674 1 0/0/0/0/0 cache buffers chains 472 20,747 736 1 0/0/0/0/0 cache buffers chains 664 20,738 697 1 0/0/0/0/0 cache buffers chains 256 20,712 683 4 0/0/0/0/0 cache buffers chains 755 20,703 741 1 0/0/0/0/0 cache buffers chains 244 20,667 693 1 0/0/0/0/0 cache buffers chains 455 20,666 626 1 0/0/0/0/0 cache buffers chains 811 20,659 661 1 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 662 20,634 672 1 0/0/0/0/0 cache buffers chains 813 20,622 684 1 0/0/0/0/0 cache buffers chains 138 20,544 727 1 0/0/0/0/0 cache buffers chains 308 20,536 740 1 0/0/0/0/0 cache buffers chains 309 20,520 718 3 0/0/0/0/0 cache buffers chains 60 20,463 656 2 0/0/0/0/0 cache buffers chains 752 20,459 742 1 0/0/0/0/0 cache buffers chains 705 20,458 565 3 0/0/0/0/0 cache buffers chains 143 20,451 710 3 0/0/0/0/0 cache buffers chains 245 20,448 708 1 0/0/0/0/0 cache buffers chains 148 20,443 644 2 0/0/0/0/0 cache buffers chains 269 20,433 571 3 0/0/0/0/0 cache buffers chains 482 20,425 692 1 0/0/0/0/0 cache buffers chains 747 20,424 712 1 0/0/0/0/0 cache buffers chains 854 20,420 591 2 0/0/0/0/0 cache buffers chains 249 20,413 734 1 0/0/0/0/0 cache buffers chains 40 20,406 665 2 0/0/0/0/0 cache buffers chains 259 20,372 665 1 0/0/0/0/0 cache buffers chains 324 20,304 656 2 0/0/0/0/0 cache buffers chains 360 20,289 699 1 0/0/0/0/0 cache buffers chains 74 20,262 722 3 0/0/0/0/0 cache buffers chains 307 20,248 684 2 0/0/0/0/0 cache buffers chains 459 20,235 587 1 0/0/0/0/0 cache buffers chains 758 20,230 611 1 0/0/0/0/0 cache buffers chains 817 20,184 693 1 0/0/0/0/0 cache buffers chains 700 20,180 605 1 0/0/0/0/0 cache buffers chains 717 20,084 655 1 0/0/0/0/0 cache buffers chains 285 20,055 607 1 0/0/0/0/0 cache buffers chains 524 20,040 668 1 0/0/0/0/0 cache buffers chains 551 20,034 673 2 0/0/0/0/0 cache buffers chains 544 20,028 740 1 0/0/0/0/0 cache buffers chains 848 20,013 612 2 0/0/0/0/0 cache buffers chains 895 19,999 646 1 0/0/0/0/0 cache buffers chains 674 19,998 690 2 0/0/0/0/0 cache buffers chains 950 19,981 794 1 0/0/0/0/0 cache buffers chains 359 19,975 693 2 0/0/0/0/0 cache buffers chains 831 19,925 633 1 0/0/0/0/0 cache buffers chains 614 19,886 573 2 0/0/0/0/0 cache buffers chains 314 19,874 678 2 0/0/0/0/0 cache buffers chains 233 19,811 570 1 0/0/0/0/0 cache buffers chains 500 19,809 709 1 0/0/0/0/0 cache buffers chains 41 19,808 637 1 0/0/0/0/0 cache buffers chains 75 19,803 674 1 0/0/0/0/0 cache buffers chains 14 19,796 723 1 0/0/0/0/0 cache buffers chains 95 19,777 657 1 0/0/0/0/0 cache buffers chains 39 19,768 641 1 0/0/0/0/0 cache buffers chains 486 19,759 634 2 0/0/0/0/0 cache buffers chains 461 19,666 567 1 0/0/0/0/0 cache buffers chains 810 19,665 608 1 0/0/0/0/0 cache buffers chains 265 19,659 601 1 0/0/0/0/0 cache buffers chains 675 19,646 663 1 0/0/0/0/0 cache buffers chains 721 19,624 600 2 0/0/0/0/0 cache buffers chains 913 19,592 689 1 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 257 19,586 667 1 0/0/0/0/0 cache buffers chains 952 19,541 758 2 0/0/0/0/0 cache buffers chains 809 19,533 651 4 0/0/0/0/0 cache buffers chains 464 19,478 636 1 0/0/0/0/0 cache buffers chains 1011 19,392 588 1 0/0/0/0/0 cache buffers chains 753 19,384 731 2 0/0/0/0/0 cache buffers chains 332 19,369 643 1 0/0/0/0/0 cache buffers chains 436 19,340 612 1 0/0/0/0/0 cache buffers chains 334 19,336 667 1 0/0/0/0/0 cache buffers chains 367 19,329 632 1 0/0/0/0/0 cache buffers chains 412 19,318 615 1 0/0/0/0/0 cache buffers chains 836 19,284 693 1 0/0/0/0/0 cache buffers chains 304 19,257 675 1 0/0/0/0/0 cache buffers chains 882 19,228 659 1 0/0/0/0/0 cache buffers chains 399 19,221 627 1 0/0/0/0/0 cache buffers chains 286 19,202 600 1 0/0/0/0/0 cache buffers chains 594 19,197 712 1 0/0/0/0/0 cache buffers chains 727 19,180 611 1 0/0/0/0/0 cache buffers chains 64 19,155 702 2 0/0/0/0/0 cache buffers chains 890 19,146 661 1 0/0/0/0/0 cache buffers chains 954 19,018 705 1 0/0/0/0/0 cache buffers chains 97 19,000 645 1 0/0/0/0/0 cache buffers chains 692 18,991 644 1 0/0/0/0/0 cache buffers chains 957 18,958 715 1 0/0/0/0/0 cache buffers chains 704 18,944 537 1 0/0/0/0/0 cache buffers chains 326 18,930 672 2 0/0/0/0/0 cache buffers chains 279 18,907 558 1 0/0/0/0/0 cache buffers chains 266 18,896 557 2 0/0/0/0/0 cache buffers chains 272 18,748 511 1 0/0/0/0/0 cache buffers chains 933 18,726 629 1 0/0/0/0/0 cache buffers chains 936 18,701 605 1 0/0/0/0/0 cache buffers chains 876 18,699 608 1 0/0/0/0/0 cache buffers chains 643 18,694 524 3 0/0/0/0/0 cache buffers chains 930 18,656 642 2 0/0/0/0/0 cache buffers chains 404 18,651 598 1 0/0/0/0/0 cache buffers chains 875 18,618 592 1 0/0/0/0/0 cache buffers chains 293 18,593 603 1 0/0/0/0/0 cache buffers chains 720 18,591 611 1 0/0/0/0/0 cache buffers chains 128 18,582 626 1 0/0/0/0/0 cache buffers chains 298 18,582 623 1 0/0/0/0/0 cache buffers chains 852 18,579 549 2 0/0/0/0/0 cache buffers chains 316 18,562 624 1 0/0/0/0/0 cache buffers chains 91 18,525 511 2 0/0/0/0/0 cache buffers chains 873 18,488 566 3 0/0/0/0/0 cache buffers chains 619 18,486 560 2 0/0/0/0/0 cache buffers chains 68 18,459 668 1 0/0/0/0/0 cache buffers chains 10 18,458 665 1 0/0/0/0/0 cache buffers chains 824 18,458 627 2 0/0/0/0/0 cache buffers chains 738 18,447 586 1 0/0/0/0/0 cache buffers chains 491 18,432 672 4 0/0/0/0/0 cache buffers chains 545 18,378 684 3 0/0/0/0/0 cache buffers chains 827 18,343 594 2 0/0/0/0/0 cache buffers chains 328 18,284 639 1 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 40 -41 -> only latches with sleeps/gets > 1/100000 are shown -> ordered by name, gets desc Child Get Spin & Latch Name Num Requests Misses Sleeps Sleeps 1->4 ---------------------- ------- ------------ ----------- ---------- ------------ cache buffers chains 710 18,254 584 1 0/0/0/0/0 cache buffers chains 911 18,148 605 2 0/0/0/0/0 cache buffers chains 624 18,074 533 1 0/0/0/0/0 cache buffers chains 621 17,990 514 1 0/0/0/0/0 cache buffers chains 66 17,847 654 2 0/0/0/0/0 cache buffers chains 48 17,810 538 2 0/0/0/0/0 cache buffers chains 98 17,793 596 1 0/0/0/0/0 cache buffers chains 11 17,733 658 2 0/0/0/0/0 cache buffers chains 522 17,730 568 3 0/0/0/0/0 cache buffers chains 281 17,645 565 1 0/0/0/0/0 cache buffers chains 226 17,632 545 3 0/0/0/0/0 cache buffers chains 13 17,602 651 2 0/0/0/0/0 cache buffers chains 493 17,570 624 1 0/0/0/0/0 cache buffers chains 495 17,559 625 1 0/0/0/0/0 cache buffers chains 53 17,077 535 1 0/0/0/0/0 cache buffers chains 47 17,069 544 1 0/0/0/0/0 cache buffers chains 490 17,050 629 1 0/0/0/0/0 cache buffers chains 317 17,027 590 1 0/0/0/0/0 cache buffers chains 722 16,972 557 2 0/0/0/0/0 cache buffers chains 626 16,941 528 2 0/0/0/0/0 cache buffers chains 521 16,872 549 1 0/0/0/0/0 cache buffers chains 879 16,806 528 1 0/0/0/0/0 cache buffers chains 908 16,649 552 1 0/0/0/0/0 cache buffers chains 121 16,586 570 1 0/0/0/0/0 cache buffers chains 89 16,481 461 2 0/0/0/0/0 cache buffers chains 901 15,900 475 2 0/0/0/0/0 cache buffers chains 57 15,732 537 1 0/0/0/0/0 cache buffers chains 4 15,484 527 1 0/0/0/0/0 cache buffers lru chai 7 3,309,877 79,949 39 79912/35/2/0 /0 cache buffers lru chai 9 3,301,133 79,689 68 79621/68/0/0 /0 cache buffers lru chai 8 3,300,969 76,015 52 75963/52/0/0 /0 simulator hash latch 41 42,521 5 1 4/1/0/0/0 ------------------------------------------------------------- Top 5 Logical Reads per Segment for DB: AHD Instance: ahd Snaps: 40 -41 -> End Segment Logical Reads Threshold: 10000 Subobject Obj. Logical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- AHD AHD1_DATA CALL_REQ TABLE 9,739,136 80.76 AHD AHD1_IDX SYS_C003707 INDEX 1,623,088 13.46 AHD AHD1_DATA CTCT TABLE 444,608 3.69 AHD AHD1_DATA CNOTE TABLE 218,208 1.81 AHD AHD1_IDX CALL_REQ_X5 INDEX 18,688 .15 ------------------------------------------------------------- Top 5 Physical Reads per Segment for DB: AHD Instance: ahd Snaps: 40 -41 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- AHD AHD1_IDX CALL_REQ_X5 INDEX 585 65.88 AHD AHD1_IDX SYS_C003707 INDEX 140 15.77 AHD AHD1_IDX CALL_REQ_X1 INDEX 137 15.43 AHD AHD1_IDX CALL_REQ_X8 INDEX 12 1.35 AHD AHD1_IDX CALL_REQ_X11 INDEX 10 1.13 ------------------------------------------------------------- Top 5 Buf. Busy Waits per Segment for DB: AHD Instance: ahd Snaps: 40 -41 -> End Segment Buffer Busy Waits Threshold: 100 Buffer Subobject Obj. Busy Owner Tablespace Object Name Name Type Waits %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- AHD AHD1_IDX CALL_REQ_X1 INDEX 118 95.93 AHD AHD1_IDX SYS_C003304 INDEX 5 4.07 ------------------------------------------------------------- Dictionary Cache Stats for DB: AHD Instance: ahd Snaps: 40 -41 ->"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_files 16 100.0 0 0 16 dc_histogram_defs 3,640 7.7 0 0 1,478 dc_object_ids 28,611 0.1 0 0 713 dc_objects 1,286 21.3 0 0 683 dc_profiles 3 0.0 0 0 1 dc_rollback_segments 42 0.0 0 0 22 dc_segments 5,624 0.3 0 0 763 dc_tablespace_quotas 6 33.3 0 3 3 dc_tablespaces 76,997 0.0 0 0 15 dc_user_grants 41 4.9 0 0 12 dc_usernames 235 0.4 0 0 6 dc_users 77,189 0.0 0 0 15 ------------------------------------------------------------- Library Cache Activity for DB: AHD Instance: ahd Snaps: 40 -41 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 6 66.7 6 66.7 0 0 CLUSTER 68 0.0 92 0.0 0 0 INDEX 60 0.0 60 0.0 0 0 SQL AREA 30,791 0.7 100,133 0.7 11 0 TABLE/PROCEDURE 3,325 11.2 4,961 14.2 0 0 TRIGGER 5 0.0 5 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: AHD Instance: ahd End Snap: 41 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 88 .6 33 5,199 212 1.0 47,403 104 .7 33 5,199 212 1.0 47,403 120 .8 33 5,199 212 1.0 47,403 136 .9 33 5,199 212 1.0 47,403 152 1.0 33 5,199 212 1.0 47,403 168 1.1 33 5,199 212 1.0 47,403 184 1.2 33 5,199 212 1.0 47,403 200 1.3 33 5,199 212 1.0 47,403 216 1.4 33 5,199 212 1.0 47,403 232 1.5 33 5,199 212 1.0 47,403 248 1.6 33 5,199 212 1.0 47,403 264 1.7 33 5,199 212 1.0 47,403 280 1.8 33 5,199 212 1.0 47,403 296 1.9 33 5,199 212 1.0 47,403 312 2.1 33 5,199 212 1.0 47,403 ------------------------------------------------------------- SGA Memory Summary for DB: AHD Instance: ahd Snaps: 40 -41 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 687,865,856 Fixed Size 455,216 Redo Buffers 929,792 Variable Size 301,989,888 ---------------- sum 991,240,752 ------------------------------------------------------------- SGA breakdown difference for DB: AHD Instance: ahd Snaps: 40 -41 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 75,497,472 75,497,472 0.00 large free memory 41,943,040 41,943,040 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 846,912 846,912 0.00 shared FileIdentificatonBlock 323,292 323,292 0.00 shared FileOpenBlock 914,532 914,532 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 2,597,336 2,917,560 12.33 shared KQR M PO 1,186,864 1,486,896 25.28 shared KQR S PO 198,740 208,224 4.77 shared KQR S SO 3,072 3,584 16.67 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 142,364 142,364 0.00 shared PL/SQL DIANA 409,968 516,788 26.06 shared PL/SQL MPCODE 125,344 323,064 157.74 shared PLS non-lib hp 2,068 2,068 0.00 shared VIRTUAL CIRCUITS 350,080 350,080 0.00 shared character set object 274,508 274,508 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared enqueue 220,916 220,916 0.00 shared event statistics per sess 2,274,300 2,274,300 0.00 shared fixed allocation callback 300 300 0.00 shared free memory 133,104,152 113,785,460 -14.51 shared joxs heap init 244 244 0.00 shared kgl simulator 696,876 891,732 27.96 shared library cache 9,492,972 14,835,496 56.28 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 6,785,124 7,060,012 4.05 shared parameters 1,044 6,264 500.00 shared processes 192,000 192,000 0.00 shared sessions 543,600 543,600 0.00 shared sim memory hea 378,044 378,044 0.00 shared sql area 16,628,444 29,349,052 76.50 shared table definiti 1,512 3,248 114.81 shared transaction 264,132 264,132 0.00 shared trigger defini 1,696 1,696 0.00 shared trigger inform 1,076 1,076 0.00 shared trigger source 160 160 0.00 buffer_cache 687,865,856 687,865,856 0.00 fixed_sga 455,216 455,216 0.00 log_buffer 918,528 918,528 0.00 ------------------------------------------------------------- init.ora Parameters for DB: AHD Instance: ahd Snaps: 40 -41 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 audit_sys_operations TRUE audit_trail OS background_dump_dest E:\oracle\admin\ahd\bdump compatible 9.2.0.0.0 control_files E:\oracle\oradata\ahd\CONTROL01.C core_dump_dest E:\oracle\admin\ahd\cdump cursor_sharing SIMILAR db_block_size 8192 db_cache_size 687865856 db_domain db_file_multiblock_read_count 16 db_name ahd db_writer_processes 2 dispatchers (PROTOCOL=TCP) (SERVICE=ahdXDB) fast_start_mttr_target 300 hash_join_enabled TRUE instance_name ahd java_pool_size 75497472 job_queue_processes 10 large_pool_size 41943040 log_archive_dest_1 location=c:\archive log_archive_format arc%d_%t_%s.arc log_archive_start TRUE open_cursors 300 pga_aggregate_target 367001600 processes 200 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE shared_pool_size 159383552 sort_area_size 10485760 star_transformation_enabled FALSE timed_statistics TRUE undo_management AUTO undo_retention 3600 undo_tablespace UNDOTBS1 user_dump_dest E:\oracle\admin\ahd\udump ------------------------------------------------------------- End of Report