STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ AHD 3665040272 ahd 1 9.2.0.1.0 NO SBGSD Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ -------- --------- ------------------- Begin Snap: 138 23-Nov-06 09:54:19 30 4.0 End Snap: 139 23-Nov-06 10:20:20 35 3.8 Elapsed: 26.02 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 624M Std Block Size: 8K Shared Pool Size: 144M Log Buffer: 768K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 6,068.52 2,568.59 Logical reads: 42,076.34 17,809.43 Block changes: 35.71 15.11 Physical reads: 12.05 5.10 Physical writes: 0.98 0.41 User calls: 107.44 45.48 Parses: 27.04 11.45 Hard parses: 13.57 5.74 Sorts: 5.76 2.44 Logons: 0.03 0.01 Executes: 30.64 12.97 Transactions: 2.36 % Blocks changed per Read: 0.08 Recursive Call %: 22.80 Rollback per transaction %: 0.00 Rows per Sort: 230.52 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.97 In-memory Sort %: 100.00 Library Hit %: 81.17 Soft Parse %: 49.81 Execute to Parse %: 11.74 Latch Hit %: 99.47 Parse CPU to Parse Elapsd %: 43.88 % Non-Parse CPU: 91.61 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 92.93 92.87 % SQL with executions>1: 19.50 20.72 % Memory for SQL w/exec>1: 19.45 20.35 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 477 79.30 db file sequential read 9,432 61 10.06 library cache load lock 54 51 8.40 db file scattered read 1,693 6 .93 control file sequential read 1,464 2 .34 ------------------------------------------------------------- Wait Events for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 9,432 0 61 6 2.6 library cache load lock 54 14 51 937 0.0 db file scattered read 1,693 0 6 3 0.5 control file sequential read 1,464 0 2 1 0.4 log file sync 3,692 0 2 0 1.0 log file parallel write 6,028 5,535 1 0 1.6 buffer busy waits 126 0 1 8 0.0 latch free 507 242 1 1 0.1 db file parallel write 462 231 0 1 0.1 control file parallel write 511 0 0 1 0.1 SQL*Net more data to client 4,846 0 0 0 1.3 direct path read 16 0 0 1 0.0 direct path write 16 0 0 0 0.0 LGWR wait for redo copy 31 0 0 0 0.0 SQL*Net message from client 167,716 0 25,145 150 45.5 virtual circuit status 52 52 1,560 30002 0.0 wakeup time manager 49 49 1,506 30727 0.0 SQL*Net message to client 167,721 0 0 0 45.5 SQL*Net more data from clien 36 0 0 0 0.0 ------------------------------------------------------------- Background Wait Events for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 6,028 5,535 1 0 1.6 control file sequential read 207 0 1 6 0.1 db file parallel write 462 231 0 1 0.1 control file parallel write 510 0 0 1 0.1 direct path read 16 0 0 1 0.0 direct path write 16 0 0 0 0.0 latch free 1 0 0 0 0.0 LGWR wait for redo copy 31 0 0 0 0.0 rdbms ipc message 14,325 8,366 11,182 781 3.9 smon timer 6 6 1,843 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 12,406,854 5 2,481,370.8 18.9 61.83 74.38 3522705919 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho WHERE ( call_req.customer = ctct.id 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 L IKE '%03972%' ) AND ( ( call_req.group_id != 14379066 ) and ( c 1,435,814 1 1,435,814.0 2.2 7.70 9.44 2400727834 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 '%03972%' AND call_req.group_id = cn01.id AND c 1,435,812 1 1,435,812.0 2.2 7.23 7.26 3520715236 SELECT count(*) FROM call_req, ctct, loc, site, z_zo, z_lho, ctc t cn01 WHERE ( call_req.customer = ctct.id AND ctct.c_l_id = lo c.id AND loc.l_si_id = site.id AND site.z_si_zo_id = z_zo.id A ND z_zo.zo_lho_id = z_lho.id AND z_lho.lho_name LIKE '%03972 %' AND call_req.group_id = cn01.id AND cn01.c_last_name LIKE 177,482 1 177,482.0 0.3 1.45 1.48 3489069906 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho WHERE ( call_req.customer = ctct.id 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 L IKE 'BHOPAL-03977' AND call_req.status = 'OP' ) AND ( ( call_re 136,967 1 136,967.0 0.2 1.19 1.21 64533992 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 070836' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 0.95 0.97 84881190 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31 893900' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 1.03 1.04 131702674 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31 906461' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 0.97 1.01 388668639 SQL ordered by Gets for DB: AHD Instance: ahd Snaps: 138 -139 -> 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.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 386242' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 1.02 1.02 546346894 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 161407' ) AND ( ( call_req.group_id IN ( SELECT group_id FROM gr pmem WHERE member = 26210124 ) ) or call_req.assignee = 26210124 or call_req.customer = 26210124 ) ORDER BY call_req.open_date 136,967 1 136,967.0 0.2 1.06 1.06 611910425 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:28 679182' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 0.98 0.98 701409222 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31 927715' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 0.97 1.00 743701367 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31 939995' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id = 695266 OR id = 695267 OR id = 695344 OR id = 695260 OR id = 69 136,967 1 136,967.0 0.2 1.02 1.02 835715413 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:31 928521' ) AND ( call_req.group_id IN ( SELECT id FROM ctct WHERE id = 682960 OR id = 682961 OR id = 682962 OR id = 682953 OR id ------------------------------------------------------------- SQL ordered by Reads for DB: AHD Instance: ahd Snaps: 138 -139 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 1,922 5 384.4 10.2 61.83 74.38 3522705919 SELECT call_req.open_date, call_req.id FROM call_req, ctct, loc, site, z_zo, z_lho WHERE ( call_req.customer = ctct.id 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 L IKE '%03972%' ) AND ( ( call_req.group_id != 14379066 ) and ( c 569 1 569.0 3.0 3.23 8.71 3376831664 BEGIN statspack.snap; END; 154 1 154.0 0.8 0.94 2.41 4059808258 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 146 1 146.0 0.8 0.67 0.79 555207968 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 122608' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.70 0.81 973004701 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 524755' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.67 0.74 1673392456 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 501887' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.64 0.84 1992123979 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 487072' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.48 0.55 2300246614 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 501507' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.81 1.00 3186805328 SELECT call_req.open_date, call_req.ref_num, call_req.template_n SQL ordered by Reads for DB: AHD Instance: ahd Snaps: 138 -139 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 497774' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 146 1 146.0 0.8 0.78 0.95 3476777981 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 404347' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 144 1 144.0 0.8 0.78 0.86 347971263 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 525243' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 140 1 140.0 0.7 7.70 9.44 2400727834 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 '%03972%' AND call_req.group_id = cn01.id AND c 92 1 92.0 0.5 0.59 0.80 2543565858 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 468510' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req .group_id != 965699 ) and ( call_req.group_id != 965943 ) and ( call_req.group_id != 965698 ) and ( call_req.group_id != 965694 56 1 56.0 0.3 0.73 0.90 2984580672 SELECT call_req.open_date, call_req.ref_num, call_req.template_n ame, call_req.id FROM call_req WHERE ( call_req.problem = 'cr:32 394503' ) AND ( ( call_req.group_id != 14379066 ) and ( call_req ------------------------------------------------------------- SQL ordered by Executions for DB: AHD Instance: ahd Snaps: 138 -139 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 1,268 1,268 1.0 0.00 0.00 3867437754 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'CL' 1,203 1,203 1.0 0.00 0.00 2625719491 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'OP' 1,078 1,078 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 1,013 1,013 1.0 0.00 0.00 327991440 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = 'Any_Contact' ) AND ( options.del = 0 ) ORD ER BY options.app_name 1,013 1,013 1.0 0.00 0.00 2628602813 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = 'no_hier_list' ) AND ( options.del = 0 ) OR DER BY options.app_name 750 750 1.0 0.00 0.00 140137628 Module: Spotlight On Oracle, classic SELECT DECODE(:b1,'BL','Buffer hash table instance lock','CF','C ontrol file schema global enqueue lock','CI','Cross-instance fun ction invocation instance lock','CS','Control file schema global enqueue lock','CU','Cursor bind lock','DF','Data file instance lock','DL','Direct loader parallel index create','DM','Mount/sta 417 601 1.4 0.00 0.00 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1 306 306 1.0 0.00 0.00 2960920928 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME='EBR_SEARCH_DEF_MODE' 305 0 0.0 0.00 0.00 2963598673 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n ext_date) and (next_date < :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5) ) and (this_date is null) order by next_date, job 305 305 1.0 0.00 0.00 2964743345 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) 250 0 0.0 0.00 0.00 126953594 Module: Spotlight On Oracle, classic INSERT INTO quest_soo_buffer_busy (timestamp, p1, p2, p 3) SELECT SYSDATE, p1, p2, p3 FROM v$session _wait WHERE event = 'buffer busy waits' 232 232 1.0 0.00 0.00 595805219 SQL ordered by Executions for DB: AHD Instance: ahd Snaps: 138 -139 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'RSCH' 224 3,808 17.0 0.00 0.00 511660291 SELECT cr_stat.sym, cr_stat.code FROM cr_stat WHERE ( cr_stat.de l = 0 ) AND ( cr_stat.cr_flag = 1 ) ORDER BY cr_stat.sym 206 206 1.0 0.00 0.00 1197511282 SELECT ctct.c_last_name FROM ctct WHERE ctct.id = 14379066 206 206 1.0 0.00 0.00 4191974412 SELECT pri.sym FROM pri WHERE pri.enum = 0 201 201 1.0 0.00 0.00 3658178244 SELECT cr_stat.sym FROM cr_stat WHERE cr_stat.code = 'CL' 196 196 1.0 0.00 0.00 1417278409 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'CRGROUP' 185 15,640 84.5 0.01 0.01 2760905359 Module: Spotlight On Oracle, classic SELECT KSLLTNUM INDX, SUM(NVL(KSLLTWGT, 0)) GETS, SUM(NVL(KSLLTWFF, 0)) MISSES, SUM(NVL(KSLLTWSL, 0 )) SLEEPS, SUM(NVL(KSLLTNGT, 0)) IMMEDIATE_GETS, SUM(NVL(KSLLTHST0, 0)) SPIN_GETS, COUNT(*) N_LATCH ES FROM X$KSLLT WHERE KSLLTWGT>0 AND inst_id = USERENV('IN 184 2,760 15.0 0.01 0.01 101044385 Module: Spotlight On Oracle, classic SELECT event, SUM(NVL(total_waits, 0)) total_waits, SUM(total_timeouts) total_timeouts, SUM(NVL(time_waited, 0)) time_waited, ROUND(SUM(time_waited)/SUM(total_waits), 3) average_wait FROM (SELECT quest_soo_pkg.event_category(ind 184 0 0.0 0.00 0.00 125218548 Module: Spotlight On Oracle, classic SELECT /*+ ORDERED ALL_ROWS*/ undo.name name, ts.n ame tablespace, us.name username, DECODE(undo.stat us$,1,'Invalid', 2,'Avail', 3,'In Use', 4,'Offl ine', 5,'Needs Recovery') status, 184 10,856 59.0 0.00 0.00 873114735 Module: Spotlight On Oracle, classic SELECT DECODE(name,'parse count (total)','parse count',name) nam e, NVL(value, 0) FROM v$sysstat WHERE name in ('consis tent gets', 'db block gets', 'physical reads', 'db block changes', 'physical writes', 'table scan blocks gotten', 'redo entries', 'redo size', 'redo wastage', 'redo writes', 'red 184 184 1.0 0.00 0.00 939306868 Module: Spotlight On Oracle, classic SELECT user FROM dual SQL ordered by Executions for DB: AHD Instance: ahd Snaps: 138 -139 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 184 184 1.0 0.00 0.00 2238558390 Module: Spotlight On Oracle, classic SELECT 1 dummy_key, ss.busy_servers-converted_servers, ss.converted_servers, ss.total_servers-ss.busy_serve rs idle_servers, ss.idle_time mts_idle_time, ss.bu sy_time mts_busy_time, q.queued, d.busy_time disp_ busy_time, d.idle_time disp_idle_time, ss.total_se 184 184 1.0 0.00 0.00 3216874312 Module: Spotlight On Oracle, classic SELECT l.total n_logs, l.mb size_mb, DECODE(d.log_mode,'A ------------------------------------------------------------- SQL ordered by Parse Calls for DB: AHD Instance: ahd Snaps: 138 -139 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 1,268 1,268 3.00 3867437754 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'CL' 1,203 1,203 2.85 2625719491 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'OP' 1,013 1,013 2.40 327991440 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = 'Any_Contact' ) AND ( options.del = 0 ) ORD ER BY options.app_name 1,013 1,013 2.40 2628602813 SELECT options.app_name, options.sym, options.id FROM options WH ERE ( options.sym = 'no_hier_list' ) AND ( options.del = 0 ) OR DER BY options.app_name 306 306 0.72 2960920928 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME='EBR_SEARCH_DEF_MODE' 232 232 0.55 595805219 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'RSCH' 224 224 0.53 511660291 SELECT cr_stat.sym, cr_stat.code FROM cr_stat WHERE ( cr_stat.de l = 0 ) AND ( cr_stat.cr_flag = 1 ) ORDER BY cr_stat.sym 206 206 0.49 1197511282 SELECT ctct.c_last_name FROM ctct WHERE ctct.id = 14379066 206 206 0.49 4191974412 SELECT pri.sym FROM pri WHERE pri.enum = 0 201 201 0.48 3658178244 SELECT cr_stat.sym FROM cr_stat WHERE cr_stat.code = 'CL' 196 196 0.46 1417278409 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'CRGROUP' 176 176 0.42 4095490172 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'FLD' 151 151 0.36 1847058277 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME='EBR_BOOLEAN_SEARCH_OR_AND_EXACT' 151 151 0.36 2670759216 Select PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE from CI_PROPER TIES where PROPERTY_NAME='EBR_SEARCH_FIELDS' 144 144 0.34 524423992 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH SQL ordered by Parse Calls for DB: AHD Instance: ahd Snaps: 138 -139 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- ERE act_type.code = 'LOG' 134 134 0.32 1476644376 SELECT ctct.alias, ctct.c_first_name, ctct.c_middle_name, ctct.c _last_name FROM ctct WHERE ctct.id = 0 133 133 0.32 2700503143 SELECT cr_stat.id FROM cr_stat WHERE cr_stat.code = 'WIP' 119 119 0.28 2504484013 select BANNER_HEIGHT FROM CI_DOC_TEMPLATES where ID = 0 117 117 0.28 2647054147 SELECT ct_ty.id FROM ct_ty WHERE ct_ty.id = 2307 114 114 0.27 453628525 select * FROM CI_DOC_TEMPLATES where ID = 0 114 114 0.27 862199570 select ID, CAPTION, DESCRIPTION, RELATIONAL_ID, HAS_CHILDREN fro m O_INDEXES where ID in (2) 114 114 0.27 1665956614 select ID,INDEX_PERMISSION_READ ,CAPTION from O_INDEXES where ID in (1,2) 114 114 0.27 3822900392 select PAGE_HTML FROM CI_DOC_TEMPLATES where ID = 0 110 110 0.26 1757448105 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'CL' 110 110 0.26 3797293206 select max(BU_ID) from BU_TRANS 108 108 0.26 4265878071 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'ST' 104 104 0.25 134509336 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'TR' 104 104 0.25 2830168420 SELECT ct_ty.id, ct_ty.sym FROM ct_ty WHERE ct_ty.del = 0 ORDER BY ct_ty.sym 103 103 0.24 229115367 SELECT ctct.alias, ctct.c_first_name, ctct.c_middle_name, ctct.c _last_name FROM ctct WHERE ctct.id = 14379066 103 103 0.24 1332871127 SELECT ct_ty.view_internal FROM ct_ty WHERE ct_ty.id = 2307 SQL ordered by Parse Calls for DB: AHD Instance: ahd Snaps: 138 -139 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 103 103 0.24 1727558880 SELECT impact.sym FROM impact WHERE impact.enum = 0 103 103 0.24 2394607180 SELECT ctct.c_ctp_id, ctct.id FROM ctct WHERE ctct.c_userid = '0 0-infosys' 103 103 0.24 3301356124 SELECT act_type.code, act_type.sym, act_type.id FROM act_type WH ERE act_type.code = 'INIT' 103 103 0.24 4286219438 SELECT srv_desc.sym FROM srv_desc WHERE srv_desc.code = 'sdsc:14 37949' 102 102 0.24 3291322082 SELECT tz.sym FROM tz WHERE tz.code = 'GMT+0530' 96 96 0.23 4215763101 select BANNER_HTML FROM CI_DOC_TEMPLATES where ID = 0 91 91 0.22 3564912414 select max(ID) from CI_AUDIT_TRAIL_DATA 90 90 0.21 2220015110 SELECT srv_desc.sym, srv_desc.code FROM srv_desc WHERE srv_desc. del = 0 ORDER BY srv_desc.sym 90 90 0.21 3778603869 SELECT cr_stat.sym, cr_stat.code FROM cr_stat WHERE cr_stat.del = 0 ORDER BY cr_stat.sym 86 104 0.20 931956286 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2) ------------------------------------------------------------- Instance Activity Stats for DB: AHD Instance: ahd Snaps: 138 -139 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 47,723 30.6 12.9 CPU used when call started 47,815 30.6 13.0 CR blocks created 71 0.1 0.0 DBWR checkpoint buffers written 1,508 1.0 0.4 DBWR checkpoints 0 0.0 0.0 DBWR transaction table writes 10 0.0 0.0 DBWR undo block writes 219 0.1 0.1 SQL*Net roundtrips to/from client 167,576 107.4 45.4 active txn count during cleanout 69 0.0 0.0 background checkpoints completed 1 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 2,368 1.5 0.6 branch node splits 0 0.0 0.0 buffer is not pinned count 66,980,837 42,908.9 18,161.8 buffer is pinned count 77,834,159 49,861.7 21,104.7 bytes received via SQL*Net from c 36,473,085 23,365.2 9,889.7 bytes sent via SQL*Net to client 53,372,529 34,191.2 14,471.9 calls to get snapshot scn: kcmgss 75,514 48.4 20.5 calls to kcmgas 7,254 4.7 2.0 calls to kcmgcs 212 0.1 0.1 change write time 57 0.0 0.0 cleanout - number of ktugct calls 111 0.1 0.0 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 2 0.0 0.0 cluster key scan block gets 1,562 1.0 0.4 cluster key scans 1,013 0.7 0.3 commit cleanouts 18,208 11.7 4.9 commit cleanouts successfully com 18,208 11.7 4.9 commit txn count during cleanout 226 0.1 0.1 consistent changes 72 0.1 0.0 consistent gets 65,625,826 42,040.9 17,794.4 consistent gets - examination 44,447,329 28,473.6 12,051.9 cursor authentications 3,506 2.3 1.0 data blocks consistent reads - un 72 0.1 0.0 db block changes 55,742 35.7 15.1 db block gets 55,346 35.5 15.0 deferred (CURRENT) block cleanout 11,920 7.6 3.2 enqueue conversions 29 0.0 0.0 enqueue releases 16,040 10.3 4.4 enqueue requests 16,040 10.3 4.4 enqueue waits 0 0.0 0.0 execute count 47,828 30.6 13.0 free buffer inspected 0 0.0 0.0 free buffer requested 20,433 13.1 5.5 hot buffers moved to head of LRU 5,322 3.4 1.4 immediate (CR) block cleanout app 2 0.0 0.0 immediate (CURRENT) block cleanou 2,114 1.4 0.6 index fast full scans (full) 61 0.0 0.0 index fetch by key 23,150,645 14,830.7 6,277.3 index scans kdiixs1 61,923 39.7 16.8 leaf node 90-10 splits 4 0.0 0.0 leaf node splits 61 0.0 0.0 logons cumulative 50 0.0 0.0 messages received 6,191 4.0 1.7 messages sent 6,191 4.0 1.7 no buffer to keep pinned count 0 0.0 0.0 Instance Activity Stats for DB: AHD Instance: ahd Snaps: 138 -139 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ no work - consistent read gets 21,125,533 13,533.3 5,728.2 opened cursors cumulative 5,727 3.7 1.6 parse count (hard) 21,187 13.6 5.7 parse count (total) 42,211 27.0 11.5 parse time cpu 4,002 2.6 1.1 parse time elapsed 9,121 5.8 2.5 physical reads 18,810 12.1 5.1 physical reads direct 16 0.0 0.0 physical writes 1,524 1.0 0.4 physical writes direct 16 0.0 0.0 physical writes non checkpoint 688 0.4 0.2 prefetched blocks 7,746 5.0 2.1 process last non-idle time 58,212,826,010 37,292,009.0 ############ recursive calls 49,525 31.7 13.4 recursive cpu usage 455 0.3 0.1 redo blocks written 21,375 13.7 5.8 redo buffer allocation retries 0 0.0 0.0 redo entries 29,438 18.9 8.0 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo size 9,472,960 6,068.5 2,568.6 redo synch time 166 0.1 0.1 redo synch writes 3,692 2.4 1.0 redo wastage 1,163,760 745.5 315.6 redo write time 108 0.1 0.0 redo writer latching time 0 0.0 0.0 redo writes 6,028 3.9 1.6 rollback changes - undo records a 2 0.0 0.0 rollbacks only - consistent read 71 0.1 0.0 rows fetched via callback 23,026,849 14,751.3 6,243.7 session connect time 58,212,826,010 37,292,009.0 ############ session logical reads 65,681,172 42,076.3 17,809.4 session pga memory max 10,424,100 6,677.8 2,826.5 session uga memory max 28,592,572 18,316.8 7,752.9 shared hash latch upgrades - no w 64,079 41.1 17.4 shared hash latch upgrades - wait 5 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 8,987 5.8 2.4 sorts (rows) 2,071,646 1,327.1 561.7 switch current to new buffer 1,078 0.7 0.3 table fetch by rowid 60,306,816 38,633.5 16,352.2 table fetch continued row 178 0.1 0.1 table scan blocks gotten 1,023,448 655.6 277.5 table scan rows gotten 22,126,811 14,174.8 5,999.7 table scans (long tables) 38 0.0 0.0 table scans (short tables) 5,337 3.4 1.5 transaction rollbacks 1 0.0 0.0 user calls 167,715 107.4 45.5 user commits 3,688 2.4 1.0 workarea executions - multipass 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 10,097 6.5 2.7 ------------------------------------------------------------- Tablespace IO Stats for DB: AHD Instance: ahd Snaps: 138 -139 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ AHDNEW_DATA 8,510 5 3.8 1.9 66 0 29 4.5 AHD1_IDX 956 1 14.1 1.0 844 1 97 8.9 AHD1_DATA 1,186 1 14.9 1.0 363 0 0 0.0 PERF_TAB 435 0 7.6 1.0 1 0 0 0.0 UNDOTBS1 1 0 40.0 1.0 232 0 0 0.0 SYSTEM 5 0 18.0 1.0 8 0 0 0.0 QUEST 1 0 50.0 1.0 2 0 0 0.0 CWMLITE 1 0 40.0 1.0 1 0 0 0.0 DRSYS 1 0 40.0 1.0 1 0 0 0.0 EXAMPLE 1 0 40.0 1.0 1 0 0 0.0 INDX 1 0 40.0 1.0 1 0 0 0.0 ODM 1 0 40.0 1.0 1 0 0 0.0 TOOLS 1 0 40.0 1.0 1 0 0 0.0 USERS 1 0 40.0 1.0 1 0 0 0.0 XDB 1 0 40.0 1.0 1 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: AHD Instance: ahd Snaps: 138 -139 ->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 966 1 14.9 1.0 212 0 0 E:\ORACLE\ORADATA\AHD\AHD2_DATA.ORA 220 0 14.6 1.0 151 0 0 AHD1_IDX E:\ORACLE\ORADATA\AHD\AHD1_IDX.ORA 956 1 14.1 1.0 844 1 97 8.9 AHDNEW_DATA E:\ORACLE\ORADATA\AHD\AHDNEW_DATA.ORA 8,510 5 3.8 1.9 66 0 29 4.5 CWMLITE E:\ORACLE\ORADATA\AHD\CWMLITE01.DBF 1 0 40.0 1.0 1 0 0 DRSYS E:\ORACLE\ORADATA\AHD\DRSYS01.DBF 1 0 40.0 1.0 1 0 0 EXAMPLE E:\ORACLE\ORADATA\AHD\EXAMPLE01.DBF 1 0 40.0 1.0 1 0 0 INDX E:\ORACLE\ORADATA\AHD\INDX01.DBF 1 0 40.0 1.0 1 0 0 ODM E:\ORACLE\ORADATA\AHD\ODM01.DBF 1 0 40.0 1.0 1 0 0 PERF_TAB E:\ORACLE\ORADATA\AHD\PERF_TAB.DBF 435 0 7.6 1.0 1 0 0 QUEST E:\ORACLE\ORADATA\AHD\QUEST1 1 0 50.0 1.0 2 0 0 SYSTEM E:\ORACLE\ORADATA\AHD\SYSTEM01.DBF 5 0 18.0 1.0 8 0 0 TOOLS E:\ORACLE\ORADATA\AHD\TOOLS01.DBF 1 0 40.0 1.0 1 0 0 UNDOTBS1 E:\ORACLE\ORADATA\AHD\UNDOTBS01.DBF 1 0 40.0 1.0 232 0 0 USERS E:\ORACLE\ORADATA\AHD\USERS01.DBF 1 0 40.0 1.0 1 0 0 XDB E:\ORACLE\ORADATA\AHD\XDB01.DBF 1 0 40.0 1.0 1 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 78,078 100.0 42,372,215 18,818 1,508 0 0 126 ------------------------------------------------------------- Instance Recovery Stats for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 28 1882 16862 16223 73728 16223 E 300 36 3034 24452 23846 73728 23846 ------------------------------------------------------------- Buffer Pool Advisory for DB: AHD Instance: ahd End Snap: 139 -> 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 32.61 51,867,802 D 128 .2 16,016 22.02 35,019,979 D 192 .3 24,024 12.64 20,100,480 D 256 .4 32,032 2.31 3,672,039 D 320 .5 40,040 1.12 1,789,018 D 384 .6 48,048 1.05 1,672,826 D 448 .7 56,056 1.05 1,667,377 D 512 .8 64,064 1.05 1,665,751 D 576 .9 72,072 1.02 1,617,325 D 624 1.0 78,078 1.00 1,590,632 D 640 1.0 80,080 1.00 1,590,616 D 704 1.1 88,088 0.99 1,577,749 D 768 1.2 96,096 0.99 1,571,942 D 832 1.3 104,104 0.98 1,563,174 D 896 1.4 112,112 0.98 1,558,912 D 960 1.5 120,120 0.98 1,552,341 D 1,024 1.6 128,128 0.98 1,550,861 D 1,088 1.7 136,136 0.97 1,549,982 D 1,152 1.8 144,144 0.97 1,548,762 D 1,216 1.9 152,152 0.97 1,548,713 D 1,280 2.1 160,160 0.97 1,547,851 ------------------------------------------------------------- Buffer wait Statistics for DB: AHD Instance: ahd Snaps: 138 -139 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- data block 126 1 8 ------------------------------------------------------------- PGA Aggr Target Stats for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 1,088 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 334 284 31.6 1.7 5.4 100.0 .0 17,105 E 334 284 31.4 0.0 .0 100.0 .0 17,105 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: AHD Instance: ahd Snaps: 138 -139 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 8K 16K 9,246 9,246 0 0 16K 32K 70 70 0 0 32K 64K 64 64 0 0 64K 128K 2 2 0 0 128K 256K 5 5 0 0 256K 512K 7 7 0 0 512K 1024K 124 124 0 0 1M 2M 577 577 0 0 4M 8M 2 2 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: AHD Instance: ahd End Snap: 139 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 42 0.1 17,195.1 46,825.1 27.0 0 84 0.3 17,195.1 45,454.9 27.0 0 167 0.5 17,195.1 41,416.7 29.0 0 251 0.8 17,195.1 41,381.4 29.0 0 334 1.0 17,195.1 41,381.4 29.0 0 401 1.2 17,195.1 41,381.4 29.0 0 468 1.4 17,195.1 11,692.9 60.0 0 535 1.6 17,195.1 11,692.9 60.0 0 601 1.8 17,195.1 11,692.9 60.0 0 668 2.0 17,195.1 11,692.9 60.0 0 1,002 3.0 17,195.1 11,692.9 60.0 0 1,336 4.0 17,195.1 11,692.9 60.0 0 2,005 6.0 17,195.1 11,551.3 60.0 0 2,673 8.0 17,195.1 11,551.3 60.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: AHD Instance: ahd Snaps: 138 -139 ->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 32.0 0.00 0 0 0 0 1 1,036.0 0.00 158,708 0 0 0 2 930.0 0.00 151,390 0 0 0 3 1,097.0 0.00 188,926 0 0 0 4 1,563.0 0.00 736,196 3 0 1 5 2,119.0 0.00 307,258 0 0 0 6 1,977.0 0.00 333,112 1 0 0 7 1,995.0 0.00 314,170 1 0 0 8 1,021.0 0.00 165,278 1 0 0 9 979.0 0.00 157,152 0 0 0 10 1,065.0 0.00 166,558 3 0 1 ------------------------------------------------------------- Rollback Segment Storage for DB: AHD Instance: ahd Snaps: 138 -139 ->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 1,171,456 96,648 2,220,032 2 1,171,456 96,648 3,268,608 3 1,171,456 173,088 3,268,608 4 2,220,032 277,358 4,317,184 5 2,220,032 272,983 4,317,184 6 2,220,032 305,403 4,317,184 7 2,220,032 305,403 3,268,608 8 1,171,456 260,636 3,268,608 9 1,171,456 173,088 3,268,608 10 2,220,032 222,829 3,268,608 ------------------------------------------------------------- Undo Segment Summary for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 419 338,529 19 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 ------------ ------------ -------- ------- -------- ------- ------ ------------- 23-Nov 10:19 137 115,330 19 1 0 0 0/0/0/0/0/0 23-Nov 10:09 81 112,539 18 1 0 0 0/0/0/0/0/0 23-Nov 09:59 201 110,660 15 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: AHD Instance: ahd Snaps: 138 -139 ->"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 6,028 0.0 0 0 FOB s.o list latch 147 0.0 0 0 SQL memory manager latch 1 0.0 0 506 0.0 SQL memory manager worka 42,967 0.0 0 0 active checkpoint queue 1,247 0.0 0 0 archive control 195 0.0 0 0 archive process latch 30 0.0 0 0 cache buffer handles 247 0.0 0 0 cache buffers chains 87,062,740 0.5 0.0 0 32,387 0.0 cache buffers lru chain 30,481 0.0 0.0 0 20,563 0.0 channel handle pool latc 95 0.0 0 0 channel operations paren 1,164 0.0 0 0 checkpoint queue latch 92,026 0.0 0 2,689 0.0 child cursor hash table 148,821 0.4 0.0 0 0 dml lock allocation 17,139 0.0 0.0 0 0 dummy allocation 95 0.0 0 0 enqueue hash chains 32,111 0.0 0 0 enqueues 9,546 0.0 0 0 event group latch 50 0.0 0 0 file number translation 162,951 0.4 0.0 0 0 hash table column usage 137 0.0 0 213,919 0.4 hash table modification 1 0.0 0 0 job_queue_processes para 26 0.0 0 0 ktm global data 6 0.0 0 0 kwqit: protect wakeup ti 49 0.0 0 0 lgwr LWN SCN 6,216 0.4 0.0 0 0 library cache 788,842 0.4 0.0 0 72,307 0.5 library cache load lock 1,416 0.8 0.0 0 0 library cache pin 346,964 0.0 0.0 0 0 library cache pin alloca 226,809 0.0 0.0 0 0 list of block allocation 123 0.0 0 0 loader state object free 12 0.0 0 0 messages 35,363 0.0 0.0 0 0 mostly latch-free SCN 6,280 1.2 0.0 0 0 multiblock read objects 10,842 0.0 0 0 ncodef allocation latch 25 0.0 0 0 object stats modificatio 181 0.0 0 0 post/wait queue 12,097 0.0 0 3,692 0.0 process allocation 50 0.0 0 50 0.0 process group creation 95 0.0 0 0 redo allocation 41,696 0.0 0.0 0 0 redo copy 0 0 29,517 0.1 redo writing 20,392 0.0 0 0 row cache enqueue latch 1,948,107 0.8 0.0 0 0 row cache objects 1,948,805 3.4 0.0 0 248 0.0 sequence cache 159 0.0 0 0 session allocation 21,590 0.0 0.0 0 0 session idle bit 342,150 0.0 0.0 0 0 session switching 25 0.0 0 0 session timer 531 0.0 0 0 Latch Activity for DB: AHD Instance: ahd Snaps: 138 -139 ->"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 915,864 1.9 0.0 0 0 sim partition latch 0 0 9 0.0 simulator hash latch 3,223,168 0.0 0.0 0 0 simulator lru latch 1,605 0.0 0 24,961 0.8 sort extent pool 31 0.0 0 0 trace latch 2 0.0 0 0 transaction allocation 113 0.0 0 0 transaction branch alloc 25 0.0 0 0 undo global data 21,800 0.0 0 0 user lock 230 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: AHD Instance: ahd Snaps: 138 -139 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers chains 87,062,740 410,878 249 0/0/0/0/0 row cache objects 1,948,805 66,546 7 66539/7/0/0/ 0 shared pool 915,864 17,525 238 17287/238/0/ 0/0 library cache 788,842 3,403 13 3390/13/0/0/ 0 ------------------------------------------------------------- Latch Miss Sources for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 175 156 cache buffers chains kcbrls: kslbegin 0 31 82 cache buffers chains kcbchg: kslbegin: bufs not 0 29 0 cache buffers chains kcbgtcr: fast path 0 9 11 cache buffers chains kcbnew 0 3 0 cache buffers chains kcbgtcr 0 2 0 library cache kglobpn: child: 0 4 1 library cache kgldti: 2child 0 2 1 library cache kglhdgn: child: 0 2 2 library cache kglpnc: child 0 1 0 row cache objects kqrpre: find obj 0 4 6 row cache objects kqrpfl: not dirty 0 3 1 shared pool kghalo 0 198 200 shared pool kghupr1 0 22 15 shared pool kghfrunp: alloc: wait 0 11 5 shared pool kghalp 0 5 4 shared pool kghfrunp: clatch: wait 0 4 6 shared pool kghfen: not perm alloc cla 0 2 23 ------------------------------------------------------------- Child Latch Statistics DB: AHD Instance: ahd Snaps: 138 -139 -> 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 1013 286,380 26,198 111 0/0/0/0/0 cache buffers chains 645 231,805 508 4 0/0/0/0/0 cache buffers chains 495 125,455 297 2 0/0/0/0/0 cache buffers chains 758 105,237 247 2 0/0/0/0/0 cache buffers chains 564 89,232 407 1 0/0/0/0/0 cache buffers chains 1024 89,005 605 1 0/0/0/0/0 cache buffers chains 457 88,982 250 1 0/0/0/0/0 cache buffers chains 8 88,213 501 1 0/0/0/0/0 cache buffers chains 768 78,361 294 1 0/0/0/0/0 cache buffers chains 617 78,203 641 1 0/0/0/0/0 cache buffers chains 253 77,558 500 1 0/0/0/0/0 cache buffers chains 288 76,138 558 1 0/0/0/0/0 cache buffers chains 771 73,692 418 1 0/0/0/0/0 cache buffers chains 636 72,997 275 1 0/0/0/0/0 cache buffers chains 789 71,294 468 1 0/0/0/0/0 cache buffers chains 849 68,037 398 26 0/0/0/0/0 cache buffers chains 883 67,618 385 1 0/0/0/0/0 cache buffers chains 434 66,559 511 2 0/0/0/0/0 cache buffers chains 255 66,316 531 1 0/0/0/0/0 cache buffers chains 527 64,761 384 1 0/0/0/0/0 cache buffers chains 579 64,669 292 1 0/0/0/0/0 cache buffers chains 767 64,484 389 1 0/0/0/0/0 cache buffers chains 834 61,401 346 1 0/0/0/0/0 cache buffers chains 977 60,413 363 1 0/0/0/0/0 cache buffers chains 995 57,741 305 1 0/0/0/0/0 cache buffers chains 93 57,530 486 1 0/0/0/0/0 cache buffers chains 293 57,325 358 1 0/0/0/0/0 cache buffers chains 672 57,091 435 1 0/0/0/0/0 cache buffers chains 900 56,967 585 1 0/0/0/0/0 cache buffers chains 946 55,775 374 1 0/0/0/0/0 cache buffers chains 585 55,766 378 2 0/0/0/0/0 cache buffers chains 996 55,514 438 2 0/0/0/0/0 cache buffers chains 978 54,889 427 1 0/0/0/0/0 cache buffers chains 210 54,714 750 1 0/0/0/0/0 cache buffers chains 100 54,592 451 1 0/0/0/0/0 cache buffers chains 642 54,529 455 1 0/0/0/0/0 cache buffers chains 68 54,149 534 1 0/0/0/0/0 cache buffers chains 4 54,088 272 1 0/0/0/0/0 cache buffers chains 548 53,986 430 1 0/0/0/0/0 cache buffers chains 887 53,959 499 1 0/0/0/0/0 cache buffers chains 952 53,108 473 2 0/0/0/0/0 cache buffers chains 872 52,562 667 1 0/0/0/0/0 cache buffers chains 384 52,114 599 1 0/0/0/0/0 cache buffers chains 601 51,743 596 1 0/0/0/0/0 cache buffers chains 885 51,703 413 1 0/0/0/0/0 cache buffers chains 868 51,614 556 3 0/0/0/0/0 cache buffers chains 949 51,554 440 1 0/0/0/0/0 cache buffers chains 336 51,198 352 2 0/0/0/0/0 cache buffers chains 272 50,982 465 1 0/0/0/0/0 cache buffers chains 874 50,929 413 1 0/0/0/0/0 cache buffers chains 906 50,896 508 2 0/0/0/0/0 cache buffers chains 933 49,939 597 1 0/0/0/0/0 cache buffers chains 914 49,884 270 1 0/0/0/0/0 Child Latch Statistics DB: AHD Instance: ahd Snaps: 138 -139 -> 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 83 49,701 353 1 0/0/0/0/0 cache buffers chains 206 49,632 375 1 0/0/0/0/0 cache buffers chains 926 49,582 332 1 0/0/0/0/0 cache buffers chains 701 49,545 465 1 0/0/0/0/0 cache buffers chains 934 49,494 510 1 0/0/0/0/0 cache buffers chains 120 49,112 314 1 0/0/0/0/0 cache buffers chains 168 48,817 272 3 0/0/0/0/0 cache buffers chains 400 48,779 463 1 0/0/0/0/0 cache buffers chains 205 48,360 480 1 0/0/0/0/0 cache buffers chains 301 48,295 257 1 0/0/0/0/0 cache buffers chains 314 47,815 352 1 0/0/0/0/0 cache buffers chains 72 47,666 501 1 0/0/0/0/0 cache buffers chains 488 47,598 292 2 0/0/0/0/0 cache buffers chains 383 47,514 639 1 0/0/0/0/0 cache buffers chains 71 47,280 351 2 0/0/0/0/0 cache buffers chains 888 47,085 393 1 0/0/0/0/0 cache buffers chains 1003 46,732 441 1 0/0/0/0/0 cache buffers chains 370 45,345 320 2 0/0/0/0/0 cache buffers chains 319 45,341 310 2 0/0/0/0/0 cache buffers chains 410 44,787 337 1 0/0/0/0/0 cache buffers chains 385 44,472 361 1 0/0/0/0/0 cache buffers chains 943 44,210 314 1 0/0/0/0/0 cache buffers chains 141 43,791 475 1 0/0/0/0/0 cache buffers chains 265 43,693 301 1 0/0/0/0/0 cache buffers chains 392 43,614 317 1 0/0/0/0/0 cache buffers chains 153 43,397 386 1 0/0/0/0/0 cache buffers chains 197 42,768 339 1 0/0/0/0/0 cache buffers chains 393 42,768 345 1 0/0/0/0/0 cache buffers chains 398 42,403 228 1 0/0/0/0/0 cache buffers chains 190 42,053 363 1 0/0/0/0/0 cache buffers chains 189 41,848 541 1 0/0/0/0/0 cache buffers chains 307 41,577 384 1 0/0/0/0/0 cache buffers chains 374 41,279 199 1 0/0/0/0/0 cache buffers chains 531 41,246 236 1 0/0/0/0/0 cache buffers chains 224 41,067 233 1 0/0/0/0/0 library cache 5 166,311 559 2 557/2/0/0/0 library cache 6 131,151 937 4 933/4/0/0/0 library cache 4 127,738 774 2 772/2/0/0/0 library cache 7 106,431 367 3 364/3/0/0/0 library cache 2 80,522 186 1 185/1/0/0/0 row cache objects 3 370,847 13,312 4 13308/4/0/0/ 0 shared pool 1 911,864 17,511 238 17273/238/0/ 0/0 ------------------------------------------------------------- Top 5 Logical Reads per Segment for DB: AHD Instance: ahd Snaps: 138 -139 -> End Segment Logical Reads Threshold: 10000 Subobject Obj. Logical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- AHD AHDNEW_DAT CALL_REQ TABLE 20,393,840 31.24 AHD AHD1_IDX SYS_C005989 INDEX 11,578,688 17.73 AHD AHDNEW_DAT CTCT TABLE 5,662,368 8.67 AHD AHD1_IDX SYS_C004404 INDEX 5,092,080 7.80 AHD AHD1_DATA LOC TABLE 5,078,560 7.78 ------------------------------------------------------------- Top 5 Physical Reads per Segment for DB: AHD Instance: ahd Snaps: 138 -139 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- AHD AHDNEW_DAT CTCT TABLE 10,798 62.13 AHD AHDNEW_DAT CALL_REQ TABLE 5,453 31.37 AHD AHD1_DATA ACT_LOG TABLE 683 3.93 AHD AHD1_IDX SYS_C004981 INDEX 114 .66 AHD AHD1_IDX CALL_REQ_X1 INDEX 89 .51 ------------------------------------------------------------- Top 5 Buf. Busy Waits per Segment for DB: AHD Instance: ahd Snaps: 138 -139 -> 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 97 76.98 AHD AHDNEW_DAT CALL_REQ TABLE 29 23.02 ------------------------------------------------------------- Dictionary Cache Stats for DB: AHD Instance: ahd Snaps: 138 -139 ->"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 0 dc_histogram_defs 229,491 0.1 0 0 1,180 dc_object_ids 502,280 0.0 0 0 1,061 dc_objects 23,293 0.5 0 0 761 dc_profiles 50 0.0 0 0 1 dc_rollback_segments 312 0.0 0 0 27 dc_segments 186,475 0.0 0 0 1,094 dc_sequences 3 0.0 0 3 1 dc_tablespaces 15,770 0.1 0 0 5 dc_user_grants 100 0.0 0 0 3 dc_usernames 244 0.0 0 0 13 dc_users 16,296 0.0 0 0 11 ------------------------------------------------------------- Library Cache Activity for DB: AHD Instance: ahd Snaps: 138 -139 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- CLUSTER 42 2.4 56 3.6 0 0 INDEX 343 0.0 343 0.0 0 0 SQL AREA 42,138 41.6 132,073 32.1 3,623 0 TABLE/PROCEDURE 44,742 0.3 95,966 0.7 188 0 TRIGGER 95 0.0 95 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: AHD Instance: ahd End Snap: 139 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 80 .6 81 19,675 9,151 1.0 866,370 96 .7 96 23,597 9,152 1.0 869,031 112 .8 111 28,012 9,153 1.0 873,029 128 .9 126 31,979 9,154 1.0 875,161 144 1.0 141 36,021 9,155 1.0 876,891 160 1.1 156 38,952 9,159 1.0 878,870 176 1.2 171 41,949 9,169 1.0 881,640 192 1.3 187 46,000 9,170 1.0 883,114 208 1.4 202 50,474 9,197 1.0 889,880 224 1.6 217 53,837 9,201 1.0 893,370 240 1.7 232 57,629 9,201 1.0 894,528 256 1.8 247 61,820 9,202 1.0 897,462 272 1.9 262 65,247 9,202 1.0 898,566 288 2.0 281 69,514 9,202 1.0 899,267 ------------------------------------------------------------- SGA Memory Summary for DB: AHD Instance: ahd Snaps: 138 -139 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 654,311,424 Fixed Size 455,056 Redo Buffers 929,792 Variable Size 268,435,456 ---------------- sum 924,131,728 ------------------------------------------------------------- SGA breakdown difference for DB: AHD Instance: ahd Snaps: 138 -139 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 67,108,864 67,108,864 0.00 large free memory 33,522,376 33,522,376 0.00 large session heap 32,056 32,056 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 695,504 695,504 0.00 shared KGK heap 3,756 3,756 0.00 shared KGLS heap 1,301,596 1,397,300 7.35 shared KQR M PO 2,108,620 2,168,740 2.85 shared KQR S PO 353,736 353,736 0.00 shared KQR S SO 768 768 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 144,008 144,008 0.00 shared PL/SQL DIANA 460,896 460,992 0.02 shared PL/SQL MPCODE 197,160 158,268 -19.73 shared PLS non-lib hp 2,068 2,068 0.00 shared VIRTUAL CIRCUITS 265,160 265,160 0.00 shared character set object 323,724 323,724 0.00 shared dictionary cache 1,610,880 1,610,880 0.00 shared errors 9,980 9,980 0.00 shared event statistics per sess 1,718,360 1,718,360 0.00 shared fixed allocation callback 300 300 0.00 shared free memory 11,869,392 11,954,164 0.71 shared joxs heap init 4,220 4,220 0.00 shared kgl simulator 8,282,200 8,282,200 0.00 shared library cache 37,888,628 38,346,088 1.21 shared message pool freequeue 834,752 834,752 0.00 shared miscellaneous 7,373,768 7,547,040 2.35 shared parameters 532 508 -4.51 shared sessions 410,720 410,720 0.00 shared sim memory hea 373,120 373,120 0.00 shared sql area 86,345,832 85,513,532 -0.96 shared subheap 46,884 46,884 0.00 shared table definiti 1,664 1,456 -12.50 shared trigger defini 340 340 0.00 shared trigger inform 1,076 1,076 0.00 shared trigger source 100 100 0.00 buffer_cache 654,311,424 654,311,424 0.00 fixed_sga 455,056 455,056 0.00 log_buffer 918,528 918,528 0.00 ------------------------------------------------------------- init.ora Parameters for DB: AHD Instance: ahd Snaps: 138 -139 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 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 db_block_size 8192 db_cache_size 654311424 db_domain db_file_multiblock_read_count 32 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 67108864 job_queue_processes 10 large_pool_size 33554432 log_archive_dest_1 location=c:\archive log_archive_format %d_%t_%s.arc log_archive_start TRUE open_cursors 300 pga_aggregate_target 350326592 processes 150 query_rewrite_enabled FALSE remote_login_passwordfile EXCLUSIVE shared_pool_size 150994944 sort_area_size 524288 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