STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ MIS 1915756509 MIS 1 9.2.0.8.0 NO bykasi22 Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 3667 28-Jun-10 01:00:04 13 5.8 End Snap: 3670 28-Jun-10 19:00:03 13 10.3 Elapsed: 1,079.98 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 1,312M Std Block Size: 8K Shared Pool Size: 512M Log Buffer: 2,048K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 15,159.25 51,681.19 Logical reads: 4,720.97 16,094.82 Block changes: 953.79 3,251.66 Physical reads: 535.84 1,826.80 Physical writes: 47.57 162.17 User calls: 1.30 4.42 Parses: 0.36 1.23 Hard parses: 0.02 0.07 Sorts: 0.31 1.07 Logons: 0.04 0.15 Executes: 12.71 43.33 Transactions: 0.29 % Blocks changed per Read: 20.20 Recursive Call %: 95.74 Rollback per transaction %: 1.21 Rows per Sort: ######## Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 89.03 In-memory Sort %: 99.90 Library Hit %: 99.73 Soft Parse %: 94.50 Execute to Parse %: 97.16 Latch Hit %: 99.99 Parse CPU to Parse Elapsd %: 37.62 % Non-Parse CPU: 99.12 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 84.97 87.42 % SQL with executions>1: 6.20 6.07 % Memory for SQL w/exec>1: 8.01 7.73 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- db file scattered read 3,803,947 26,815 26.85 SQL*Net more data from dblink 418,122 20,920 20.95 db file sequential read 3,105,411 20,141 20.17 SQL*Net message from dblink 110,718 13,148 13.16 db file parallel write 23,594 4,961 4.97 ------------------------------------------------------------- Wait Events for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 scattered read 3,803,947 0 26,815 7 200.1 SQL*Net more data from dblin 418,122 0 20,920 50 22.0 db file sequential read 3,105,411 0 20,141 6 163.4 SQL*Net message from dblink 110,718 0 13,148 119 5.8 db file parallel write 23,594 0 4,961 210 1.2 direct path write 134,954 0 3,206 24 7.1 log file parallel write 88,028 0 2,718 31 4.6 log file sequential read 14,416 0 2,067 143 0.8 direct path read 390,890 0 2,023 5 20.6 db file parallel read 49,296 0 942 19 2.6 control file parallel write 26,498 0 482 18 1.4 control file sequential read 198,893 0 480 2 10.5 log file sync 18,133 0 278 15 1.0 async disk IO 26,248 0 205 8 1.4 log buffer space 1,089 4 163 150 0.1 enqueue 33 10 52 1585 0.0 log file switch completion 363 0 45 124 0.0 local write wait 78 30 32 405 0.0 control file single write 667 0 7 10 0.0 log file single write 544 0 3 5 0.0 single-task message 3 0 2 706 0.0 latch free 695 36 2 2 0.0 LGWR wait for redo copy 1,225 38 1 1 0.1 db file single write 58 0 1 12 0.0 SQL*Net message to dblink 110,718 0 1 0 5.8 SQL*Net break/reset to clien 184 0 1 3 0.0 process startup 15 0 1 38 0.0 SQL*Net more data to client 17,279 0 1 0 0.9 buffer busy waits 43 0 0 1 0.0 undo segment extension 3,127 3,126 0 0 0.2 row cache lock 1 0 0 2 0.0 SQL*Net break/reset to dblin 1 0 0 0 0.0 SQL*Net message from client 83,812 0 74,761 892 4.4 jobq slave wait 304 292 881 2899 0.0 SQL*Net more data from clien 92,371 0 9 0 4.9 SQL*Net message to client 83,811 0 0 0 4.4 ------------------------------------------------------------- Background Wait Events for DB: MIS Instance: MIS Snaps: 3667 -3670 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file parallel write 23,594 0 4,961 210 1.2 log file parallel write 88,027 0 2,718 31 4.6 log file sequential read 14,416 0 2,067 143 0.8 control file parallel write 25,717 0 473 18 1.4 async disk IO 26,248 0 205 8 1.4 direct path write 11,654 0 53 5 0.6 enqueue 27 10 52 1934 0.0 control file sequential read 33,158 0 25 1 1.7 db file scattered read 762 0 10 13 0.0 log buffer space 96 1 7 72 0.0 direct path read 20,134 0 5 0 1.1 db file sequential read 384 0 3 7 0.0 log file single write 544 0 3 5 0.0 LGWR wait for redo copy 1,225 38 1 1 0.1 rdbms ipc reply 737 0 1 1 0.0 latch free 210 12 0 2 0.0 buffer busy waits 21 0 0 1 0.0 db file single write 4 0 0 2 0.0 rdbms ipc message 128,279 62,180 353,755 2758 6.7 pmon timer 21,864 21,864 63,225 2892 1.2 smon timer 267 203 61,281 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- 45,236,209 1 45,236,209.0 14.8 2186.60 8272.48 1043773953 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 1 79 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 44,733,628 1 44,733,628.0 14.6 1259.75 5158.59 4104228692 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 8 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 40,213,706 1 40,213,706.0 13.1 1047.86 4993.00 381274261 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 5 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 24,139,614 115 209,909.7 7.9 3323.43 23585.69 1771606953 Module: SQL*Plus SELECT COUNT(1) FROM MIS_BBV_ACTION_LOG WHERE ACTION_DATE >= :B4 AND ACTION_DATE < :B4 + 1 AND ACTION = :B3 AND ACCOUNT_TYPE = : B2 AND ORIGIN = :B1 19,913,861 1 19,913,861.0 6.5 516.16 2637.46 2975103072 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 2 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 7,595,258 1 7,595,258.0 2.5 372.56 15076.14 2565502423 Module: SQL*Plus INSERT INTO MIS_BBV_CALL_DATA_PERIOD_DAYS (CALL_DATE ,CUSTOMER_I D ,ORIGIN ,ACCOUNT_TYPE ,DURATION) (SELECT CALL_DATE ,CUSTOMER_I D ,ORIGIN ,ACCOUNT_TYPE ,DURATION FROM MIS_BBV_CALL_DATA WHERE T RUNC(CALL_DATE) = :B1 ) 7,282,092 1 7,282,092.0 2.4 632.66 28862.31 2755957210 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_fvc_pkg_import.mis_fvc_daily_ import; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 2,617,149 1 2,617,149.0 0.9 61.97 106.50 4163092066 insert into mis_bbv_call_log_temp (select * from mis_bbv_call_lo g where trunc(starttime)=to_date('27-06-2010','DD/mm/yy') ) 2,077,627 17,523 118.6 0.7 90.94 188.90 1962733079 Module: sqlldr@dybrzm03 (TNS V1-V3) INSERT INTO MIS_BBV_CALL_LOG (SEQUENCE_NBR,CALL_FROM,CLI,CONTA SQL ordered by Gets for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- CT,CALL_TO,DESTINATIONNUMBER,CALL_GROUP,CALL_TYPE,SERVICETYPE,CO DECINFO,FACILITIESUSED,REDIRECTING,FORWARDING,ORIGINALTO,STARTTI ME,ENDTIME,STARTCHG,STOPCHG,DURATION,DURATIONMILLIS,ENDREASON,SI PRTC,CALLRESULT,PARTIALSEQUENCENBR,PARTIALRECORDREASON,INVITEES, 1,805,581 2 902,790.5 0.6 224.15 1665.33 1864926000 SELECT NVL(COUNT(CALLING_NUMBER),0) TOTAL_CALLS FROM MIS_FVC_CAL L_DATA B WHERE B.CALL_GROUP=:B3 AND UPPER(B.CODECINFO)=UPPER(:B2 ) AND TRUNC(B.CALL_DATE) = TRUNC(:B1 ) AND EXISTS (SELECT CLI F ROM MIS_FVC_90_DAY_ACTIVE A WHERE TRUNC(A.CALL_DATE) = TRUNC(:B1 ) AND A.CLI=B.CALLING_NUMBER) 1,805,465 2 902,732.5 0.6 226.85 1462.24 4132914533 SELECT NVL(SUM(NVL(B.DURATION,0)),0) TOTAL_DURATION FROM MIS_FVC _CALL_DATA B WHERE B.CALL_GROUP=:B3 AND UPPER(B.CODECINFO)=UPPER (:B2 ) AND TRUNC(B.CALL_DATE) = TRUNC(:B1 ) AND EXISTS (SELECT C LI FROM MIS_FVC_90_DAY_ACTIVE A WHERE TRUNC(A.CALL_DATE) = TRUNC (:B1 ) AND A.CLI=B.CALLING_NUMBER) 1,500,611 1 1,500,611.0 0.5 33.45 36.41 3797448188 Module: SQL*Plus SELECT * FROM MIS_BBV_CALL_DATA WHERE TRUNC(CALL_DATE) = TO_DATE (:B1 ,'dd-mm-yyyy') 1,302,987 3 434,329.0 0.4 24.48 111.80 3604419422 Module: sqlplus@dybrzm03 (TNS V1-V3) BEGIN kpms.kpms_collector.kp_collect_source(cpu_count=>0,oracle_ sid=>'MIS',host_name=>'dybrzm03',physical_memory=>'8589934592',o s=>'SunOS 5.8'); END; 902,441 1 902,441.0 0.3 201.97 995.60 3998093154 INSERT INTO MIS_FVC_CALL_DATA_PERIOD_DAYS(CALL_DATE,CUSTOMER_ID, ACCOUNT_TYPE,CALL_GROUP,DURATION) (SELECT CALL_DATE,CUSTOMER_ID, ACCOUNT_TYPE,CALL_GROUP,DURATION FROM MIS_FVC_CALL_DATA WHERE TR UNC(CALL_DATE)= :B1 ) 902,439 1 902,439.0 0.3 277.95 1086.11 2103402816 SELECT CALLING_NUMBER ,TRUNC(CALL_DATE) START_TIME,CALL_GROUP,CO DECINFO,DURATION FROM MIS_FVC_CALL_DATA WHERE TRUNC(CALL_DATE) = TRUNC(:B2 ) AND CALL_GROUP = :B1 827,064 1 827,064.0 0.3 43.06 10933.04 2581615779 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_pkg_stardust.mis_stardust_dai ly_summary; :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 616,473 1 616,473.0 0.2 83.52 584.58 1243741831 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_octopus_pkg_import.mis_octp_s SQL ordered by Gets for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 --------------- ------------ -------------- ------ -------- --------- ---------- p_daily_import; :mydate := next_date; IF broken THEN :b := 1; EL SE :b := 0; END IF; END; 555,375 3 185,125.0 0.2 27.76 120.07 1466199794 Module: sqlplus@dybrzm03 (TNS V1-V3) BEGIN perfstat.statspack.snap(i_ucomment =>'',i_snap_level=>10); END; 522,017 3 174,005.7 0.2 5.45 11.10 1440470760 Module: sqlplus@dybrzm03 (TNS V1-V3) INSERT into stats$sqltext ( hash_value , text_subset , piece ------------------------------------------------------------- SQL ordered by Reads for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 23,999,271 115 208,689.3 69.1 3323.43 23585.69 1771606953 Module: SQL*Plus SELECT COUNT(1) FROM MIS_BBV_ACTION_LOG WHERE ACTION_DATE >= :B4 AND ACTION_DATE < :B4 + 1 AND ACTION = :B3 AND ACCOUNT_TYPE = : B2 AND ORIGIN = :B1 1,836,894 1 1,836,894.0 5.3 632.66 28862.31 2755957210 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_fvc_pkg_import.mis_fvc_daily_ import; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 1,799,191 2 899,595.5 5.2 224.15 1665.33 1864926000 SELECT NVL(COUNT(CALLING_NUMBER),0) TOTAL_CALLS FROM MIS_FVC_CAL L_DATA B WHERE B.CALL_GROUP=:B3 AND UPPER(B.CODECINFO)=UPPER(:B2 ) AND TRUNC(B.CALL_DATE) = TRUNC(:B1 ) AND EXISTS (SELECT CLI F ROM MIS_FVC_90_DAY_ACTIVE A WHERE TRUNC(A.CALL_DATE) = TRUNC(:B1 ) AND A.CLI=B.CALLING_NUMBER) 1,797,712 2 898,856.0 5.2 226.85 1462.24 4132914533 SELECT NVL(SUM(NVL(B.DURATION,0)),0) TOTAL_DURATION FROM MIS_FVC _CALL_DATA B WHERE B.CALL_GROUP=:B3 AND UPPER(B.CODECINFO)=UPPER (:B2 ) AND TRUNC(B.CALL_DATE) = TRUNC(:B1 ) AND EXISTS (SELECT C LI FROM MIS_FVC_90_DAY_ACTIVE A WHERE TRUNC(A.CALL_DATE) = TRUNC (:B1 ) AND A.CLI=B.CALLING_NUMBER) 1,534,962 1 1,534,962.0 4.4 2186.60 8272.48 1043773953 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 1 79 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 902,372 1 902,372.0 2.6 201.97 995.60 3998093154 INSERT INTO MIS_FVC_CALL_DATA_PERIOD_DAYS(CALL_DATE,CUSTOMER_ID, ACCOUNT_TYPE,CALL_GROUP,DURATION) (SELECT CALL_DATE,CUSTOMER_ID, ACCOUNT_TYPE,CALL_GROUP,DURATION FROM MIS_FVC_CALL_DATA WHERE TR UNC(CALL_DATE)= :B1 ) 892,917 1 892,917.0 2.6 277.95 1086.11 2103402816 SELECT CALLING_NUMBER ,TRUNC(CALL_DATE) START_TIME,CALL_GROUP,CO DECINFO,DURATION FROM MIS_FVC_CALL_DATA WHERE TRUNC(CALL_DATE) = TRUNC(:B2 ) AND CALL_GROUP = :B1 777,622 1 777,622.0 2.2 1259.75 5158.59 4104228692 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 8 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 642,390 1 642,390.0 1.9 372.56 15076.14 2565502423 Module: SQL*Plus INSERT INTO MIS_BBV_CALL_DATA_PERIOD_DAYS (CALL_DATE ,CUSTOMER_I D ,ORIGIN ,ACCOUNT_TYPE ,DURATION) (SELECT CALL_DATE ,CUSTOMER_I D ,ORIGIN ,ACCOUNT_TYPE ,DURATION FROM MIS_BBV_CALL_DATA WHERE T SQL ordered by Reads for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- RUNC(CALL_DATE) = :B1 ) 528,847 1 528,847.0 1.5 1047.86 4993.00 381274261 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 5 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 261,685 1 261,685.0 0.8 516.16 2637.46 2975103072 Module: SQL*Plus SELECT ACCOUNT_TYPE ,ORIGIN ,COUNT(DISTINCT(CUSTOMER_ID)) AS TOT AL FROM MIS_BBV_CALL_DATA_PERIOD_DAYS WHERE CALL_DATE >= :B1 - 2 9 AND CALL_DATE < :B1 + 1 GROUP BY ACCOUNT_TYPE ,ORIGIN 71,871 1 71,871.0 0.2 23.77 76.65 3977368950 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_octopus_pkg_process.mis_sp_po p_oct_summary_tables; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 55,997 1 55,997.0 0.2 39.86 125.01 2037630368 Module: SQL*Plus select DISTINCT(LOGIN_ID) from MIS_FVC_PROFILE_LOG WHERE TRUNC(T IMESTAMP)=TRUNC(SYSDATE-1) 50,140 1 50,140.0 0.1 83.52 584.58 1243741831 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN mis_octopus_pkg_import.mis_octp_s p_daily_import; :mydate := next_date; IF broken THEN :b := 1; EL SE :b := 0; END IF; END; 44,039 1 44,039.0 0.1 6.70 47.53 3121085904 Module: SQL*Plus INSERT INTO MIS_HILTON_SUMMARY (SELECT SEQUENCE_NBR ,CLI ,DESTIN ATIONNUMBER ,CODECINFO ,STARTTIME ,ENDTIME ,STARTCHG ,STOPCHG ,D URATION ,DURATIONMILLIS ,:B1 FROM MIS_BBV_CALL_LOG_BBV WHERE DES TINATIONNUMBER IN (SELECT * FROM MIS_PREMIUM_NUMBERS)) 35,764 1 35,764.0 0.1 15.15 345.64 1109865508 Module: SQL*Plus SELECT CALL_TYPE ,COUNT(1) AS CALLS FROM MIS_BBV_CALL_DATA WHERE CALL_DATE >= :B1 - 6 AND CALL_DATE < :B1 + 1 AND ACCOUNT_TYPE = 'BBV_CON' GROUP BY CALL_TYPE 25,864 1 25,864.0 0.1 61.97 106.50 4163092066 insert into mis_bbv_call_log_temp (select * from mis_bbv_call_lo g where trunc(starttime)=to_date('27-06-2010','DD/mm/yy') ) 24,611 1 24,611.0 0.1 13.52 40.50 429426708 insert into MIS_BBV_CALL_LOG_DAILY (select * from mis_bbv_call_l og where trunc(starttime)=to_date('27-06-2010','DD/mm/yy') ) 24,058 1 24,058.0 0.1 13.57 57.45 1700108056 INSERT INTO mis_octopus_call_log (SELECT * FROM mis_bbv_call_log SQL ordered by Reads for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Disk Reads Threshold: 1000 CPU Elapsd Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- WHERE trunc(starttime)=to_date('27-06-2010','dd/mm/yy') ) 20,478 1 20,478.0 0.1 6.68 41.24 2010596112 INSERT INTO mis_octopus_period_days (call_date ------------------------------------------------------------- SQL ordered by Executions for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 17,523 517,415 29.5 0.01 0.01 1962733079 Module: sqlldr@dybrzm03 (TNS V1-V3) INSERT INTO MIS_BBV_CALL_LOG (SEQUENCE_NBR,CALL_FROM,CLI,CONTA CT,CALL_TO,DESTINATIONNUMBER,CALL_GROUP,CALL_TYPE,SERVICETYPE,CO DECINFO,FACILITIESUSED,REDIRECTING,FORWARDING,ORIGINALTO,STARTTI ME,ENDTIME,STARTCHG,STOPCHG,DURATION,DURATIONMILLIS,ENDREASON,SI PRTC,CALLRESULT,PARTIALSEQUENCENBR,PARTIALRECORDREASON,INVITEES, 12,927 12,927 1.0 0.00 0.00 1693927332 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) 12,784 5 0.0 0.00 0.00 1038353885 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 3,484 3,484 1.0 0.00 0.00 3948238198 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13 , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2 and block#=:3 3,450 3,450 1.0 0.00 0.00 1380021873 update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv 2=:7,priv3=:8 where ts#=:1 and user#=:2 2,692 0 0.0 0.00 0.00 3611653548 Module: sqlplus@dybrzm03 (TNS V1-V3) ALTER SESSION SET TIME_ZONE='+01:00' 2,499 82,180 32.9 0.00 0.00 1254950678 select file# from file$ where ts#=:1 2,334 2,334 1.0 0.00 0.00 2262110760 Module: httpd@dybrzm03 (TNS V1-V3) SELECT value_name,value_type,num_val,string_val,date_val,updated ,updated_by FROM gen_data_store WHERE value_name=:b1 1,092 1,092 1.0 0.00 0.00 999253772 SELECT COUNT(1) FROM mis_bbv_onnet WHERE on net_flag = 'Y' AND range_flag = 'Y' AND (:b1 LIKE '1%' OR :b1 LIKE '2%') AND (to_number(:b1) >= to_number(called _number_from) AND to_number(:b1) <= 897 897 1.0 0.00 0.00 4021594778 INSERT INTO mis_octopus_primary_call_data (call_data _id ,call_date ,customer_id ,call_type ,calling_number ,called_numbe r ,duration ,duration_millis ,partial_cdrs ,termination_code ,termin SQL ordered by Executions for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 750 750 1.0 0.00 0.00 2679275305 Module: SQL*Plus SELECT VALUE FROM TEMP_FLAG WHERE FLAG = 'insert' 667 667 1.0 0.00 0.00 3997906522 select user# from sys.user$ where name = 'OUTLN' 643 35,015 54.5 0.00 0.00 1356713530 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0 638 638 1.0 0.00 0.00 1922262634 INSERT INTO mis_octopus_addt_call_data (call_data_id ,call_date ,customer_id ,ca ll_type ,calling_number ,called_number ,duration ,duration_millis ,p artial_cdrs ,termination_code ,terminati 569 569 1.0 0.00 0.00 2363027855 Module: sqlldr@dybrzm03 (TNS V1-V3) SELECT 'X' FROM ALL_OBJECTS WHERE OBJECT_NAME = :1 AND OWNER = :2 AND OBJECT_TYPE = 'TABLE' 569 569 1.0 0.00 0.00 4167087773 Module: sqlldr@dybrzm03 (TNS V1-V3) SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL 544 0 0.0 0.00 0.00 1053795750 Module: sqlplus@dybrzm03 (TNS V1-V3) COMMIT 516 516 1.0 0.00 0.00 2460106338 Module: sqlplus@dybrzm03 (TNS V1-V3) select /* UCPS_ORACLE */ 'SVRLIT1', to_char(startup_time, 'DDMMYYHHMMSS') from v$instance 316 316 1.0 0.00 0.00 2330517268 INSERT INTO mis_octopus_primary_call_data (call_data _id ,call_date ,customer_id ,call_type ,calling_number ,called_numbe r ,duration ,duration_millis ,partial_cdrs ,termination_code ,termin 272 272 1.0 0.00 0.00 3876449241 Module: Symantec i3 - oracle.focal SELECT 1 FROM DUAL 216 216 1.0 0.00 0.00 130926350 select count(*) from sys.job$ where next_date < :1 and (field1 = :2 or (field1 = 0 and 'Y' = :3)) 213 213 1.0 0.00 0.01 448063177 SQL ordered by Executions for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Executions Threshold: 100 CPU per Elap per Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_SERVER:'||user from user_users 213 213 1.0 0.00 0.00 1529902582 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_USER:'||username from dba_users where username='KPMS' 213 213 1.0 0.00 0.00 3363079587 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_INSTANCE:'||status||':'||substr(version,0,(instr(vers ion,'.')-1))||':'||substr(substr(version,(instr(version,'.')+1)) ,1,1)||':'||version||':'||instance_number||':'||instance_name fr om v$instance 212 212 1.0 0.00 0.00 198257919 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_RAC:'||min(instance_number)||':'||count(instance_numb ------------------------------------------------------------- SQL ordered by Parse Calls for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 3,484 3,484 14.91 3948238198 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13 , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17) where ts#=:1 and file#=:2 and block#=:3 3,450 3,450 14.76 1380021873 update tsq$ set blocks=:3,maxblocks=:4,grantor#=:5,priv1=:6,priv 2=:7,priv3=:8 where ts#=:1 and user#=:2 2,692 2,692 11.52 3611653548 Module: sqlplus@dybrzm03 (TNS V1-V3) ALTER SESSION SET TIME_ZONE='+01:00' 2,499 2,499 10.69 1254950678 select file# from file$ where ts#=:1 667 667 2.85 3997906522 select user# from sys.user$ where name = 'OUTLN' 643 643 2.75 1356713530 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0 569 569 2.43 2363027855 Module: sqlldr@dybrzm03 (TNS V1-V3) SELECT 'X' FROM ALL_OBJECTS WHERE OBJECT_NAME = :1 AND OWNER = :2 AND OBJECT_TYPE = 'TABLE' 569 569 2.43 4167087773 Module: sqlldr@dybrzm03 (TNS V1-V3) SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL 565 17,523 2.42 1962733079 Module: sqlldr@dybrzm03 (TNS V1-V3) INSERT INTO MIS_BBV_CALL_LOG (SEQUENCE_NBR,CALL_FROM,CLI,CONTA CT,CALL_TO,DESTINATIONNUMBER,CALL_GROUP,CALL_TYPE,SERVICETYPE,CO DECINFO,FACILITIESUSED,REDIRECTING,FORWARDING,ORIGINALTO,STARTTI ME,ENDTIME,STARTCHG,STOPCHG,DURATION,DURATIONMILLIS,ENDREASON,SI PRTC,CALLRESULT,PARTIALSEQUENCENBR,PARTIALRECORDREASON,INVITEES, 544 544 2.33 1053795750 Module: sqlplus@dybrzm03 (TNS V1-V3) COMMIT 516 516 2.21 2460106338 Module: sqlplus@dybrzm03 (TNS V1-V3) select /* UCPS_ORACLE */ 'SVRLIT1', to_char(startup_time, 'DDMMYYHHMMSS') from v$instance 272 272 1.16 3876449241 Module: Symantec i3 - oracle.focal SQL ordered by Parse Calls for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- SELECT 1 FROM DUAL 213 213 0.91 448063177 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_SERVER:'||user from user_users 213 213 0.91 1529902582 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_USER:'||username from dba_users where username='KPMS' 213 213 0.91 3363079587 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_INSTANCE:'||status||':'||substr(version,0,(instr(vers ion,'.')-1))||':'||substr(substr(version,(instr(version,'.')+1)) ,1,1)||':'||version||':'||instance_number||':'||instance_name fr om v$instance 212 212 0.91 198257919 Module: sqlplus@dybrzm03 (TNS V1-V3) select 'DB_RAC:'||min(instance_number)||':'||count(instance_numb er) from gv$instance 203 203 0.87 2095543314 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 149 149 0.64 360058000 select job, nvl2(last_date, 1, 0) from sys.job$ where next_date <= :1 and (field1 = :2 or (field1 = 0 and 'Y' = :3)) order by ne xt_date, job 149 12,784 0.64 1038353885 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 149 149 0.64 2201872251 SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD = :1 149 149 0.64 3743621015 UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM E_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1 126 126 0.54 1235539213 Module: sqlplus@dybrzm03 (TNS V1-V3) insert into kpms.ucps_segstat values(:instance,:sn,:owner,:obn,: sobn,:obt,:lio,:pio,:shr,:bgs) 114 114 0.49 2293415029 SQL ordered by Parse Calls for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Parse Calls Threshold: 1000 % Total Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- SELECT SYSDATE FROM DUAL 108 108 0.46 528380237 Module: Symantec i3 - oracle.focal SELECT /*+ RULE */ ws.username || '(' || TO_CHAR(ws.sid) || ',' || TO_CHAR(ws.serial#) || ')' as "locked", hs.user name || '(' || TO_CHAR(hs.sid) || ',' || TO_CHAR(hs.serial#) || ')' as "locked_by", TO_CHAR(wl.ctime) as "wait_time " FROM v$lock wl, v$lock hl , v$session ws, v$session hs 75 75 0.32 3468666020 select text from view$ where rowid=:1 72 72 0.31 1185766971 Module: Symantec i3 - oracle.focal BEGIN dbms_application_info.set_module (:1,:2); END; 72 72 0.31 3184176114 Module: sqlplus@dybrzm03 (TNS V1-V3) SELECT /*+ RULE */ /* ORABACKUP_ORAREDO */ 'SVRLIT1:'|| TO_CHAR(SYSDATE-10,'YYYYMMDDHH24MISS')||':' FROM sys.dual 54 0 0.23 260339297 insert into sys.col_usage$ values ( :objn, :coln, decode(bit and(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decode(b itand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode ------------------------------------------------------------- Instance Activity Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 115,581 1.8 6.1 CPU used when call started 37,272 0.6 2.0 CR blocks created 7,020 0.1 0.4 Cached Commit SCN referenced 250,490 3.9 13.2 Commit SCN cached 12 0.0 0.0 DBWR buffers scanned 503,921 7.8 26.5 DBWR checkpoint buffers written 1,820,852 28.1 95.8 DBWR checkpoints 140 0.0 0.0 DBWR free buffers found 430,564 6.6 22.7 DBWR lru scans 1,557 0.0 0.1 DBWR make free requests 1,557 0.0 0.1 DBWR revisited being-written buff 139 0.0 0.0 DBWR summed scan depth 503,921 7.8 26.5 DBWR transaction table writes 1,302 0.0 0.1 DBWR undo block writes 590,769 9.1 31.1 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 0 0.0 0.0 OS All other sleep time 0 0.0 0.0 OS Chars read and written 0 0.0 0.0 OS Data page fault sleep time 0 0.0 0.0 OS Input blocks 0 0.0 0.0 OS Involuntary context switches 0 0.0 0.0 OS Major page faults 0 0.0 0.0 OS Other system trap CPU time 0 0.0 0.0 OS Output blocks 0 0.0 0.0 OS Process heap size 0 0.0 0.0 OS Process stack size 0 0.0 0.0 OS System call CPU time 0 0.0 0.0 OS System calls 0 0.0 0.0 OS Text page fault sleep time 0 0.0 0.0 OS User level CPU time 0 0.0 0.0 OS User lock wait sleep time 0 0.0 0.0 OS Voluntary context switches 0 0.0 0.0 OS Wait-cpu (latency) time 0 0.0 0.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 Parallel operations downgraded 25 0 0.0 0.0 Parallel operations not downgrade 0 0.0 0.0 SQL*Net roundtrips to/from client 73,386 1.1 3.9 SQL*Net roundtrips to/from dblink 110,718 1.7 5.8 active txn count during cleanout 400,247 6.2 21.1 background checkpoints completed 137 0.0 0.0 background checkpoints started 136 0.0 0.0 background timeouts 77,851 1.2 4.1 branch node splits 475 0.0 0.0 buffer is not pinned count 202,362,380 3,122.9 10,646.7 buffer is pinned count 369,395,906 5,700.6 19,434.7 bytes received via SQL*Net from c 211,743,846 3,267.7 11,140.3 bytes received via SQL*Net from d 968,187,499 14,941.4 50,938.5 bytes sent via SQL*Net to client 64,330,800 992.8 3,384.6 bytes sent via SQL*Net to dblink 1,239,086 19.1 65.2 calls to get snapshot scn: kcmgss 1,148,269 17.7 60.4 calls to kcmgas 256,019 4.0 13.5 calls to kcmgcs 299,735 4.6 15.8 change write time 65,854 1.0 3.5 cleanout - number of ktugct calls 633,021 9.8 33.3 Instance Activity Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 105,356 1.6 5.5 cluster key scan block gets 325,222 5.0 17.1 cluster key scans 221,006 3.4 11.6 commit cleanout failures: block l 10,203 0.2 0.5 commit cleanout failures: buffer 2,201 0.0 0.1 commit cleanout failures: callbac 5,288 0.1 0.3 commit cleanout failures: cannot 0 0.0 0.0 commit cleanout failures: hot bac 0 0.0 0.0 commit cleanouts 745,364 11.5 39.2 commit cleanouts successfully com 727,672 11.2 38.3 commit txn count during cleanout 371,448 5.7 19.5 consistent changes 8,011 0.1 0.4 consistent gets 206,337,524 3,184.3 10,855.9 consistent gets - examination 1,753,878 27.1 92.3 current blocks converted for CR 0 0.0 0.0 cursor authentications 16 0.0 0.0 data blocks consistent reads - un 7,124 0.1 0.4 db block changes 61,804,331 953.8 3,251.7 db block gets 99,576,794 1,536.7 5,239.0 deferred (CURRENT) block cleanout 207,667 3.2 10.9 dirty buffers inspected 69,378 1.1 3.7 enqueue conversions 242,977 3.8 12.8 enqueue releases 335,296 5.2 17.6 enqueue requests 335,503 5.2 17.7 enqueue timeouts 203 0.0 0.0 enqueue waits 23 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 823,562 12.7 43.3 failed probes on index block recl 0 0.0 0.0 free buffer inspected 70,568 1.1 3.7 free buffer requested 34,662,266 534.9 1,823.7 hot buffers moved to head of LRU 1,516,098 23.4 79.8 immediate (CR) block cleanout app 105,356 1.6 5.5 immediate (CURRENT) block cleanou 380,835 5.9 20.0 index crx upgrade (found) 0 0.0 0.0 index crx upgrade (positioned) 42,395 0.7 2.2 index fast full scans (direct rea 0 0.0 0.0 index fast full scans (full) 1,097 0.0 0.1 index fast full scans (rowid rang 0 0.0 0.0 index fetch by key 1,737,066 26.8 91.4 index scans kdiixs1 359,648 5.6 18.9 leaf node 90-10 splits 7,941 0.1 0.4 leaf node splits 126,707 2.0 6.7 logons cumulative 2,785 0.0 0.2 messages received 84,461 1.3 4.4 messages sent 84,461 1.3 4.4 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 202,605,338 3,126.7 10,659.5 number of drop segment calls unde 0 0.0 0.0 opened cursors cumulative 23,424 0.4 1.2 parse count (failures) 65 0.0 0.0 parse count (hard) 1,285 0.0 0.1 parse count (total) 23,368 0.4 1.2 parse time cpu 1,013 0.0 0.1 parse time elapsed 2,693 0.0 0.1 Instance Activity Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads 34,721,940 535.8 1,826.8 physical reads direct 1,158,132 17.9 60.9 physical reads direct (lob) 0 0.0 0.0 physical writes 3,082,359 47.6 162.2 physical writes direct 1,142,304 17.6 60.1 physical writes non checkpoint 2,439,773 37.7 128.4 pinned buffers inspected 0 0.0 0.0 prefetched blocks 26,831,613 414.1 1,411.7 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 61,201 0.9 3.2 queries parallelized 0 0.0 0.0 recursive calls 1,886,522 29.1 99.3 recursive cpu usage 1,223,506 18.9 64.4 redo blocks written 27,977,995 431.8 1,472.0 redo buffer allocation retries 1,402 0.0 0.1 redo entries 31,889,527 492.1 1,677.8 redo log space requests 448 0.0 0.0 redo log space wait time 4,602 0.1 0.2 redo ordering marks 453 0.0 0.0 redo size 982,304,376 15,159.3 51,681.2 redo synch time 28,694 0.4 1.5 redo synch writes 18,196 0.3 1.0 redo wastage 11,163,268 172.3 587.3 redo write time 277,255 4.3 14.6 redo writer latching time 143 0.0 0.0 redo writes 44,024 0.7 2.3 rollback changes - undo records a 454,332 7.0 23.9 rollbacks only - consistent read 6,885 0.1 0.4 rows fetched via callback 70,022 1.1 3.7 session connect time 0 0.0 0.0 session logical reads 305,914,319 4,721.0 16,094.8 session pga memory 13,261,480 204.7 697.7 session pga memory max 8,892,528 137.2 467.9 session uga memory 17,221,052,592 265,761.1 906,037.4 session uga memory max 285,714,608 4,409.2 15,032.1 shared hash latch upgrades - no w 43,265 0.7 2.3 shared hash latch upgrades - wait 0 0.0 0.0 sorts (disk) 20 0.0 0.0 sorts (memory) 20,279 0.3 1.1 sorts (rows) 517,040,431 7,979.1 27,202.6 summed dirty queue length 566,162 8.7 29.8 switch current to new buffer 19,157 0.3 1.0 table fetch by rowid 268,716,917 4,146.9 14,137.8 table fetch continued row 0 0.0 0.0 table scan blocks gotten 31,132,142 480.4 1,637.9 table scans (direct read) 0 0.0 0.0 table scans (long tables) 170 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 4,695 0.1 0.3 transaction rollbacks 143 0.0 0.0 transaction tables consistent rea 3 0.0 0.0 transaction tables consistent rea 536 0.0 0.0 user calls 83,989 1.3 4.4 user commits 18,777 0.3 1.0 user rollbacks 230 0.0 0.0 write clones created in backgroun 16 0.0 0.0 Instance Activity Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ write clones created in foregroun 70,036 1.1 3.7 ------------------------------------------------------------- Tablespace IO Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ DATA 6,092,966 94 6.8 5.3 839,049 13 0 0.0 INDEXES 964,463 15 9.4 1.0 511,856 8 0 0.0 TEMP 739,548 11 4.0 1.5 193,818 3 0 0.0 UNDO 1,061 0 7.4 1.0 592,996 9 43 0.7 SYSTEM 11,066 0 8.8 1.9 1,176 0 0 0.0 STATSPACK_DATA 6,140 0 12.1 1.4 5,762 0 0 0.0 SYMANTEC_I3_ORCL 964 0 4.1 1.0 268 0 0 0.0 USERS 277 0 4.1 1.1 206 0 0 0.0 SYMANTEC_I3_V8_ORCL 295 0 2.7 1.0 134 0 0 0.0 DBA_UTILITIES 150 0 1.9 1.0 134 0 0 0.0 TOOLS 142 0 2.1 1.0 134 0 0 0.0 SYMANTEC_I3_ORCL_TMP 13 0 2.3 1.0 0 0 0 0.0 SYMANTEC_I3_V8_ORCL_TEMP 13 0 1.5 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ DATA /breezem/oradata01/MIS/data_01.dbf 236,706 4 7.8 7.1 6,921 0 0 /breezem/oradata01/MIS/data_02.dbf 324,433 5 7.3 7.7 8,048 0 0 /breezem/oradata01/MIS/data_03.dbf 291,968 5 7.3 7.7 8,031 0 0 /breezem/oradata01/MIS/data_04.dbf 217,414 3 7.3 7.6 6,445 0 0 /breezem/oradata01/MIS/data_05.dbf 327,508 5 7.6 7.7 7,120 0 0 /breezem/oradata01/MIS/data_06.dbf 207,356 3 7.6 7.6 8,829 0 0 /breezem/oradata02/MIS/data_07.dbf 124,094 2 6.3 7.5 7,589 0 0 /breezem/oradata02/MIS/data_08.dbf 89,579 1 6.0 6.0 7,870 0 0 /breezem/oradata02/MIS/data_09.dbf 98,642 2 7.1 5.8 6,995 0 0 /breezem/oradata02/MIS/data_10.dbf 58,405 1 6.5 6.3 8,458 0 0 /breezem/oradata02/MIS/data_17.dbf 65,719 1 5.8 3.6 6,671 0 0 /breezem/oradata02/MIS/data_25.dbf 66,531 1 4.9 5.5 15,535 0 0 /breezem/oradata02/MIS/data_55.dbf 271,776 4 8.4 1.2 8,724 0 0 /breezem/oradata02/MIS/data_58.dbf 58,651 1 3.2 1.1 22,669 0 0 /breezem/oradata02/MIS/data_59.dbf 49,095 1 2.7 1.1 24,394 0 0 /breezem/oradata03/MIS/data_11.dbf 46,292 1 7.6 7.1 7,878 0 0 /breezem/oradata03/MIS/data_12.dbf 100,391 2 8.2 5.6 9,254 0 0 /breezem/oradata03/MIS/data_13.dbf 68,318 1 7.3 4.7 7,523 0 0 /breezem/oradata03/MIS/data_14.dbf 81,994 1 7.1 3.9 6,236 0 0 /breezem/oradata03/MIS/data_15.dbf 30,332 0 5.7 3.8 6,954 0 0 /breezem/oradata03/MIS/data_16.dbf 54,817 1 6.5 4.6 6,531 0 0 /breezem/oradata03/MIS/data_18.dbf 53,671 1 4.7 4.4 7,046 0 0 /breezem/oradata03/MIS/data_19.dbf 100,501 2 6.4 3.5 7,729 0 0 /breezem/oradata03/MIS/data_20.dbf 112,250 2 6.7 4.0 7,988 0 0 /breezem/oradata03/MIS/data_21.dbf 90,827 1 5.6 2.5 8,538 0 0 /breezem/oradata03/MIS/data_22.dbf 83,598 1 6.4 3.6 8,075 0 0 File IO Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ DATA /breezem/oradata03/MIS/data_23.dbf 56,396 1 5.0 5.5 8,742 0 0 /breezem/oradata03/MIS/data_24.dbf 39,014 1 3.5 4.5 9,943 0 0 /breezem/oradata04/MIS/data_26.dbf 67,551 1 5.7 6.0 14,851 0 0 /breezem/oradata04/MIS/data_27.dbf 56,942 1 5.0 5.6 34,647 1 0 /breezem/oradata04/MIS/data_28.dbf 84,578 1 5.7 6.1 31,041 0 0 /breezem/oradata04/MIS/data_29.dbf 96,496 1 6.7 5.0 17,195 0 0 /breezem/oradata04/MIS/data_30.dbf 52,234 1 5.3 5.8 29,938 0 0 /breezem/oradata04/MIS/data_31.dbf 69,749 1 7.4 4.4 14,504 0 0 /breezem/oradata04/MIS/data_32.dbf 102,171 2 6.8 5.0 19,908 0 0 /breezem/oradata04/MIS/data_33.dbf 111,467 2 7.1 5.1 14,477 0 0 /breezem/oradata04/MIS/data_34.dbf 73,537 1 5.4 5.6 25,406 0 0 /breezem/oradata04/MIS/data_35.dbf 104,878 2 7.5 4.6 15,104 0 0 /breezem/oradata04/MIS/data_36.dbf 57,009 1 6.5 6.9 10,336 0 0 /breezem/oradata04/MIS/data_37.dbf 54,527 1 5.8 6.5 25,008 0 0 /breezem/oradata04/MIS/data_38.dbf 138,344 2 6.6 7.6 14,324 0 0 /breezem/oradata04/MIS/data_39.dbf 35,180 1 5.4 5.4 17,824 0 0 /breezem/oradata04/MIS/data_45.dbf 182,035 3 7.3 5.5 26,006 0 0 /breezem/oradata05/MIS/data_40.dbf 134,095 2 5.4 7.4 21,206 0 0 /breezem/oradata05/MIS/data_41.dbf 42,517 1 4.9 5.7 23,585 0 0 /breezem/oradata05/MIS/data_42.dbf 41,176 1 4.7 4.8 24,350 0 0 /breezem/oradata05/MIS/data_43.dbf 33,469 1 4.2 5.3 14,014 0 0 /breezem/oradata05/MIS/data_44.dbf 142,248 2 5.6 7.4 19,959 0 0 /breezem/oradata05/MIS/data_56.dbf 311,577 5 9.3 1.0 13,830 0 0 /breezem/oradata05/MIS/data_57.dbf 289,438 4 9.5 1.0 18,367 0 0 /breezem/oradata06/MIS/data_46.dbf 21,008 0 2.8 3.2 21,575 0 0 /breezem/oradata06/MIS/data_47.dbf 153,234 2 6.7 7.0 20,052 0 0 File IO Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ DATA /breezem/oradata06/MIS/data_48.dbf 14,058 0 1.0 1.3 18,512 0 0 /breezem/oradata07/MIS/data_49.dbf 135,812 2 4.4 7.4 16,409 0 0 /breezem/oradata07/MIS/data_50.dbf 13,099 0 1.5 2.1 14,390 0 0 /breezem/oradata07/MIS/data_51.dbf 18,639 0 3.4 3.8 13,412 0 0 /breezem/oradata07/MIS/data_52.dbf 13,053 0 1.4 2.0 10,957 0 0 /breezem/oradata07/MIS/data_53.dbf 18,513 0 2.9 3.8 10,926 0 0 /breezem/oradata07/MIS/data_54.dbf 18,054 0 2.5 3.6 10,200 0 0 DBA_UTILITIES /breezem/oradata01/MIS/dba_utilities.dbf 150 0 1.9 1.0 134 0 0 INDEXES /breezem/oradata01/MIS/indexes_01.dbf 46,726 1 9.4 1.0 22,850 0 0 /breezem/oradata01/MIS/indexes_02.dbf 56,159 1 10.5 1.0 31,406 0 0 /breezem/oradata01/MIS/indexes_03.dbf 59,229 1 9.1 1.0 25,635 0 0 /breezem/oradata02/MIS/indexes_04.dbf 76,188 1 8.9 1.0 30,336 0 0 /breezem/oradata03/MIS/indexes_05.dbf 85,880 1 9.3 1.0 28,424 0 0 /breezem/oradata03/MIS/indexes_06.dbf 59,312 1 7.5 1.0 37,227 1 0 /breezem/oradata03/MIS/indexes_07.dbf 53,763 1 5.1 1.0 19,525 0 0 /breezem/oradata04/MIS/indexes_15.dbf 8,968 0 1.1 1.0 7,428 0 0 /breezem/oradata05/MIS/indexes_08.dbf 67,228 1 8.8 1.0 31,621 0 0 /breezem/oradata05/MIS/indexes_09.dbf 64,257 1 7.8 1.0 29,854 0 0 /breezem/oradata05/MIS/indexes_10.dbf 98,784 2 9.7 1.0 36,327 1 0 /breezem/oradata05/MIS/indexes_11.dbf 59,986 1 12.0 1.0 36,643 1 0 /breezem/oradata05/MIS/indexes_12.dbf 78,985 1 11.8 1.0 65,874 1 0 /breezem/oradata05/MIS/indexes_13.dbf 46,404 1 10.9 1.0 26,203 0 0 /breezem/oradata05/MIS/indexes_14.dbf 23,686 0 14.7 1.0 31,857 0 0 /breezem/oradata06/MIS/indexes_16.dbf 50,494 1 10.7 1.0 36,002 1 0 /breezem/oradata07/MIS/indexes_17.dbf 28,414 0 6.8 1.0 14,644 0 0 File IO Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ STATSPACK_DATA /breezem/oradata01/MIS/statspack_data_01.dbf 4,030 0 12.8 1.6 3,338 0 0 /breezem/oradata06/MIS/statspack_data_02.dbf 2,110 0 10.7 1.2 2,424 0 0 SYMANTEC_I3_ORCL /breezem/oradata01/MIS/SYMANTEC_I3_ORCL.dbf 660 0 4.8 1.0 134 0 0 /breezem/oradata05/MIS/SYMANTEC_I4_ORCL.dbf 304 0 2.6 1.0 134 0 0 SYMANTEC_I3_ORCL_TMP /breezem/oradata01/MIS/SYMANTEC_I3_ORCL_TMP.dbf 13 0 2.3 1.0 0 0 0 SYMANTEC_I3_V8_ORCL /breezem/oradata01/MIS/SYMANTEC_I3_V8_ORCL.dbf 295 0 2.7 1.0 134 0 0 SYMANTEC_I3_V8_ORCL_TEMP /breezem/oradata01/MIS/SYMANTEC_I3_V8_ORCL_TEMP_01.d 13 0 1.5 1.0 0 0 0 SYSTEM /breezem/oradata01/MIS/system_01.dbf 11,066 0 8.8 1.9 1,176 0 0 TEMP /breezem/cdrfiles/temp_dir/temp04.dbf 1,426 0 7.8 3.9 987 0 0 /breezem/oradata01/MIS/temp_01.dbf 682,374 11 4.1 1.5 177,565 3 0 /breezem/oradata01/MIS/temp_02.dbf 53,365 1 2.1 1.5 13,722 0 0 /breezem/oradata02/MIS/temp_03.dbf 2,383 0 5.0 3.7 1,544 0 0 TOOLS /breezem/oradata01/MIS/tools_01.dbf 142 0 2.1 1.0 134 0 0 UNDO /breezem/oradata01/MIS/undo_01.dbf 594 0 5.9 1.0 291,371 4 41 0.7 /breezem/oradata02/MIS/undo_02.dbf 467 0 9.4 1.0 301,625 5 2 0.0 USERS /breezem/oradata01/MIS/users_01.dbf 277 0 4.1 1.1 206 0 0 ------------------------------------------------------------- Buffer Pool Statistics for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 162,770###### 305,911,531############ 1,940,234 0 0 43 ------------------------------------------------------------- Instance Recovery Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 68 25 5859 68293 184320 184320 433222 E 68 25 3788 64758 68545 184320 68545 ------------------------------------------------------------- Buffer Pool Advisory for DB: MIS Instance: MIS End Snap: 3670 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate (default block size first) Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ------------ ----- ---------------- ------------- ------------------ D 128 .1 15,880 1.14 20,057,519 D 256 .2 31,760 1.11 19,511,230 D 384 .3 47,640 1.07 18,770,125 D 512 .4 63,520 1.04 18,322,077 D 640 .5 79,400 1.03 18,048,638 D 768 .6 95,280 1.02 17,913,660 D 896 .7 111,160 1.01 17,721,252 D 1,024 .8 127,040 1.00 17,648,330 D 1,152 .9 142,920 1.00 17,609,565 D 1,280 1.0 158,800 1.00 17,573,493 D 1,312 1.0 162,770 1.00 17,562,895 D 1,408 1.1 174,680 1.00 17,546,263 D 1,536 1.2 190,560 1.00 17,529,085 D 1,664 1.3 206,440 1.00 17,513,965 D 1,792 1.4 222,320 0.39 6,818,913 D 1,920 1.5 238,200 0.38 6,707,376 D 2,048 1.6 254,080 0.38 6,624,045 D 2,176 1.7 269,960 0.37 6,475,382 D 2,304 1.8 285,840 0.36 6,326,599 D 2,432 1.9 301,720 0.35 6,213,533 D 2,560 2.0 317,600 0.35 6,106,687 ------------------------------------------------------------- Buffer wait Statistics for DB: MIS Instance: MIS Snaps: 3667 -3670 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- undo header 41 0 1 undo block 2 0 0 ------------------------------------------------------------- Enqueue activity for DB: MIS Instance: MIS Snaps: 3667 -3670 -> Enqueue stats gathered prior to 9i should not be compared with 9i data -> ordered by Wait Time desc, Waits desc Avg Wt Wait Eq Requests Succ Gets Failed Gets Waits Time (ms) Time (s) -- ------------ ------------ ----------- ----------- ------------- ------------ CF 33,722 33,705 17 20 2,677.40 54 TC 20 20 0 3 .33 0 ------------------------------------------------------------- Rollback Segment Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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 221.0 0.00 0 0 0 0 1 3,480,003.0 0.00 769,348,556 236 24 219 2 44,272.0 0.00 44,507,628 52 5 30 3 18,150.0 0.02 45,452,526 62 5 39 4 20,734,443.0 0.00 -1,833,326,644 230 28 225 5 25,361.0 0.00 35,391,574 49 3 18 6 19,754.0 0.05 53,135,128 16 10 3 7 12,476.0 0.02 29,398,026 38 3 7 8 41,215.0 0.00 157,116,622 110 5 91 9 726,607.0 0.00 146,424,322 4 1 0 10 21,883.0 0.06 45,001,736 29 10 14 ------------------------------------------------------------- Rollback Segment Storage for DB: MIS Instance: MIS Snaps: 3667 -3670 ->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 8,511,488 1,409,283,731 2,885,804,032 2 8,577,024 4,477,711 2,885,869,568 3 8,511,488 947,459 3,892,436,992 4 75,620,352 771,052,788 2,952,978,432 5 11,722,752 1,525,818 3,020,021,760 6 20,045,824 101,066,408 2,885,869,568 7 9,560,064 945,349 2,885,804,032 8 174,186,496 246,980,736 3,087,130,624 9 134,340,608 1,245,080,680 4,240,564,224 10 17,948,672 8,914,497 2,885,804,032 ------------------------------------------------------------- Undo Segment Summary for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 589,472 ########## 24,310 4 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 ------------ ------------ -------- ------- -------- ------- ------ ------------- 28-Jun 19:04 404 ######## 24,310 2 0 0 0/0/0/0/0/0 28-Jun 18:54 421 ######## 23,846 3 0 0 0/0/0/0/0/0 28-Jun 18:44 806 ######## 23,136 4 0 0 0/0/0/0/0/0 28-Jun 18:34 0 ######## 22,568 1 0 0 0/0/0/0/0/0 28-Jun 18:24 544 ######## 22,002 2 0 0 0/0/0/0/0/0 28-Jun 18:14 822 ######## 21,416 2 0 0 0/0/0/0/0/0 28-Jun 18:04 861 ######## 20,829 2 0 0 0/0/0/0/0/0 28-Jun 17:54 369 ######## 20,209 3 0 0 0/0/0/0/0/0 28-Jun 17:44 745 ######## 19,612 2 0 0 0/0/0/0/0/0 28-Jun 17:34 0 ######## 19,009 1 0 0 0/0/0/0/0/0 28-Jun 17:24 427 ######## 18,413 3 0 0 0/0/0/0/0/0 28-Jun 17:14 747 ######## 17,739 2 0 0 0/0/0/0/0/0 28-Jun 17:04 3 ######## 17,074 2 0 0 0/0/0/0/0/0 28-Jun 16:54 486 ######## 16,641 2 0 0 0/0/0/0/0/0 28-Jun 16:44 804 ######## 15,943 2 0 0 0/0/0/0/0/0 28-Jun 16:34 1 ######## 15,254 2 0 0 0/0/0/0/0/0 28-Jun 16:24 519 ######## 14,757 2 0 0 0/0/0/0/0/0 28-Jun 16:14 801 ######## 14,032 3 0 0 0/0/0/0/0/0 28-Jun 16:04 2 ######## 13,542 3 0 0 0/0/0/0/0/0 28-Jun 15:54 388 ######## 12,856 2 0 0 0/0/0/0/0/0 28-Jun 15:44 21,077 ######## 12,400 4 0 0 0/0/0/0/0/0 28-Jun 15:34 198 ######## 16,825 2 0 0 0/0/0/0/0/0 28-Jun 15:24 1,006 ######## 11,173 2 0 0 0/0/0/0/0/0 28-Jun 15:14 869 ######## 10,596 4 0 0 0/0/0/0/0/0 28-Jun 15:04 281 ######## 9,975 2 0 0 0/0/0/0/0/0 28-Jun 14:54 914 ######## 9,378 2 0 0 0/0/0/0/0/0 28-Jun 14:44 977 ######## 8,800 3 0 0 0/0/0/0/0/0 28-Jun 14:34 328 ######## 8,167 3 0 0 0/0/0/0/0/0 28-Jun 14:24 858 ######## 7,524 2 0 0 0/0/0/0/0/0 28-Jun 14:14 925 ######## 6,859 3 0 0 0/0/0/0/0/0 28-Jun 14:04 342 ######## 6,289 2 0 0 0/0/0/0/0/0 28-Jun 13:54 612 ######## 5,661 2 0 0 0/0/0/0/0/0 28-Jun 13:44 856 ######## 5,224 2 0 0 0/0/0/0/0/0 28-Jun 13:34 193 ######## 4,548 2 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: MIS Instance: MIS Snaps: 3667 -3670 ->"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 44,186 0.0 0 0 FAL request queue 1,291 0.0 0 0 FIB s.o chain latch 1,962 0.0 0 0 FOB s.o list latch 14,131 0.0 0 0 SQL memory manager worka 201 0.0 0 0 active checkpoint queue 64,459 0.0 0 0 alert log latch 655 0.0 0 0 archive control 2,852 0.0 0 0 archive process latch 2,267 1.9 0.8 0 0 begin backup scn array 4 0.0 0 0 cache buffer handles 30,616 0.0 0 0 cache buffers chains 787,939,024 0.0 0.1 1 116,616,969 0.0 cache buffers lru chain 36,748,261 0.2 0.0 1 85,604,951 0.1 channel handle pool latc 5,639 0.0 0 0 channel operations paren 52,845 0.0 0 0 checkpoint queue latch 6,384,935 0.0 0.0 0 1,940,243 0.0 child cursor hash table 9,018 0.0 0 0 dictionary lookup 40 0.0 0 0 dml lock allocation 53,974 0.0 0 0 dummy allocation 5,567 0.0 0 0 enqueue hash chains 913,799 0.0 0.1 0 0 enqueues 345,387 0.0 0.1 0 0 event group latch 2,779 0.0 0 0 global tx hash mapping 110 0.0 0 0 hash table column usage 227 0.0 0 1,861 0.0 job workq parent latch 0 0 10 0.0 job_queue_processes para 1,083 0.0 0 0 ktm global data 287 0.0 0 0 lgwr LWN SCN 56,483 0.0 0.0 0 0 library cache 2,556,083 0.0 0.1 0 1,654 0.0 library cache load lock 150 0.0 0 0 library cache pin 1,806,745 0.0 0.0 0 0 library cache pin alloca 129,118 0.0 0 0 list of block allocation 20,737 0.0 0 0 loader state object free 8,130 0.0 0 0 longop free list parent 2,374 0.0 0 159 0.0 message pool operations 110 0.0 0 0 messages 405,968 0.1 0.2 0 0 mostly latch-free SCN 56,896 0.5 0.0 0 0 multiblock read objects 10,581,664 0.0 0.0 0 0 ncodef allocation latch 1,042 0.0 0 0 object stats modificatio 5,970 0.1 0.0 0 0 post/wait queue 29,608 0.0 0.0 0 19,589 0.0 process allocation 5,559 0.0 0 2,779 0.0 process group creation 5,559 0.0 0 0 redo allocation 31,992,247 0.0 0.0 0 0 redo copy 0 0 31,899,207 0.0 redo writing 241,856 0.0 0.1 0 0 row cache enqueue latch 1,066,744 0.0 0.0 0 0 row cache objects 1,135,756 0.0 0.0 0 0 Latch Activity for DB: MIS Instance: MIS Snaps: 3667 -3670 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ sequence cache 2,080 0.0 0 0 session allocation 245,805 0.0 0.1 0 0 session idle bit 193,909 0.0 0 0 session switching 1,042 0.0 0 0 session timer 21,864 0.0 0 0 shared pool 131,586 0.0 0.0 0 0 sim partition latch 0 0 11,675 0.0 simulator hash latch 21,246,717 0.0 0.0 0 0 simulator lru latch 420,598 0.0 0.0 0 2,259,222 0.0 sort extent pool 7,172 0.0 0 0 transaction allocation 261,247 0.0 0 0 transaction branch alloc 1,066 0.0 0 0 undo global data 25,332,577 0.0 0.1 0 0 user lock 2,579 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: MIS Instance: MIS Snaps: 3667 -3670 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ cache buffers lru chain 36,748,261 74,187 392 73795/392/0/ 0/0 redo allocation 31,992,247 3,439 114 3328/108/3/0 /0 multiblock read objects 10,581,664 1,097 34 1063/34/0/0/ 0 checkpoint queue latch 6,384,935 886 23 863/23/0/0/0 mostly latch-free SCN 56,896 279 1 278/1/0/0/0 cache buffers chains 787,939,024 244 36 208/36/0/0/0 messages 405,968 205 44 161/44/0/0/0 library cache 2,556,083 136 10 126/10/0/0/0 archive process latch 2,267 42 34 8/34/0/0/0 undo global data 25,332,577 34 2 32/2/0/0/0 enqueue hash chains 913,799 21 2 19/2/0/0/0 enqueues 345,387 9 1 8/1/0/0/0 session allocation 245,805 9 1 8/1/0/0/0 redo writing 241,856 8 1 7/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: MIS Instance: MIS Snaps: 3667 -3670 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- archive process latch kcrrpa 0 34 0 cache buffers chains kcbbxsv 0 9 6 cache buffers chains kcbgcur: kslbegin 0 4 8 cache buffers chains kcbget: pin buffer 0 4 2 cache buffers chains kcbgtcr: kslbegin excl 0 3 3 cache buffers chains kcbzib: multi-block read: 0 3 0 cache buffers chains kcbchg: kslbegin: bufs not 0 2 6 cache buffers chains kcbrls: kslbegin 0 2 2 cache buffers chains kcbnew 0 2 0 cache buffers chains kcbgtcr: fast path 0 2 0 cache buffers chains kcbchg: kslbegin: call CR 0 1 1 cache buffers chains kcbzgb: scan from tail. no 0 1 0 cache buffers chains kcbzib: finish free bufs 0 1 2 cache buffers chains kcbget: exchange 0 1 0 cache buffers chains kcbget: exchange rls 0 1 0 cache buffers lru chain kcbzgb: wait 34,939 288 360 cache buffers lru chain kcbzar: KSLNBEGIN 9,857 43 0 cache buffers lru chain kcbbiop: lru scan 200 29 2 cache buffers lru chain kcbbxsv: move to being wri 61 12 2 cache buffers lru chain kcbzgm 75 7 0 cache buffers lru chain kcbbic2 142 5 15 cache buffers lru chain kcbzgb: posted for free bu 182 5 13 cache buffers lru chain kcbbwlru 32 3 0 checkpoint queue latch kcbklbc: Link buffer into 0 18 0 checkpoint queue latch kcbbcthc: check thread que 0 1 13 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 0 checkpoint queue latch kcbswcu: Switch buffers 0 1 1 checkpoint queue latch kcbnlc: Link buffers into 0 1 0 checkpoint queue latch kcbbxsv: move to being wri 0 1 3 enqueue hash chains ksqcnl 0 1 2 enqueue hash chains ksqgtl3 0 1 0 enqueues ksqgel: create enqueue 0 1 0 lgwr LWN SCN kcs023 0 1 0 library cache kglic 0 8 0 library cache kglhdiv: child 0 1 0 library cache kglpndl: child: before pro 0 1 1 messages ksaamb: after wakeup 0 44 0 multiblock read objects kcbzib: mbr get 0 21 9 multiblock read objects kcbzib: normal mbr free 0 13 25 redo allocation kcrfwr 0 112 52 redo allocation kcrfwi: more space 0 2 49 redo writing kcrfws: in loop 0 1 0 session allocation ksuprc 0 1 0 undo global data ktudba: KSLBEGIN 0 2 0 ------------------------------------------------------------- Parent Latch Statistics DB: MIS Instance: MIS Snaps: 3667 -3670 -> only latches with sleeps are shown -> ordered by name Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 ------------------------ -------------- ----------- ---------- ------------ archive process latch 2,267 42 34 8/34/0/0/0 enqueues 344,950 9 1 8/1/0/0/0 messages 405,749 205 44 161/44/0/0/0 mostly latch-free SCN 56,883 280 1 279/1/0/0/0 multiblock read objects 10,593,244 1,104 34 1070/34/0/0/ 0 redo writing 241,772 8 1 7/1/0/0/0 session allocation 245,581 9 1 8/1/0/0/0 ------------------------------------------------------------- Child Latch Statistics DB: MIS Instance: MIS Snaps: 3667 -3670 -> 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 11123 92,622 1 1 0/1/0/0/0 cache buffers chains 1183 47,396 1 1 0/1/0/0/0 cache buffers chains 2037 45,633 1 1 0/1/0/0/0 cache buffers chains 3711 42,089 1 1 0/1/0/0/0 cache buffers chains 13969 41,636 1 1 0/1/0/0/0 cache buffers chains 15790 41,403 1 1 0/1/0/0/0 cache buffers chains 15699 40,643 1 1 0/1/0/0/0 cache buffers chains 4028 39,626 1 1 0/1/0/0/0 cache buffers chains 3572 38,628 1 1 0/1/0/0/0 cache buffers chains 3466 38,559 1 1 0/1/0/0/0 cache buffers chains 4816 38,221 1 1 0/1/0/0/0 cache buffers chains 14499 37,598 1 1 0/1/0/0/0 cache buffers chains 14433 37,581 1 1 0/1/0/0/0 cache buffers chains 4784 36,958 1 1 0/1/0/0/0 cache buffers chains 14570 36,204 1 1 0/1/0/0/0 cache buffers chains 4978 35,385 1 1 0/1/0/0/0 cache buffers chains 8484 33,325 1 1 0/1/0/0/0 cache buffers chains 8295 32,399 1 1 0/1/0/0/0 cache buffers chains 5320 30,875 1 1 0/1/0/0/0 cache buffers chains 10598 30,293 1 1 0/1/0/0/0 cache buffers chains 10747 29,206 1 1 0/1/0/0/0 cache buffers lru chai 3 36,785,439 74,397 400 73997/400/0/ 0/0 library cache 3 406,876 43 5 38/5/0/0/0 library cache 1 312,916 45 4 41/4/0/0/0 ------------------------------------------------------------- Top 5 Logical Reads per Segment for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Segment Logical Reads Threshold: 10000 Subobject Obj. Logical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- MIS_OWNER DATA MIS_BBV_CALL_DATA_PE TABLE 149,342,800 71.45 MIS_OWNER DATA MIS_BBV_ACTION_LOG TABLE 24,219,520 11.59 MIS_OWNER DATA MIS_BBV_CALL_DATA TABLE 20,288,368 9.71 MIS_OWNER DATA MIS_FVC_CALL_DATA TABLE 5,788,688 2.77 MIS_OWNER INDEXES MIS_BBV_CALL_DATA_PR INDEX 1,542,848 .74 ------------------------------------------------------------- Top 5 Physical Reads per Segment for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Owner Tablespace Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- MIS_OWNER DATA MIS_BBV_ACTION_LOG TABLE 24,078,729 73.01 MIS_OWNER DATA MIS_FVC_CALL_DATA TABLE 5,764,200 17.48 MIS_OWNER DATA MIS_BBV_CALL_DATA_PE TABLE 1,315,069 3.99 MIS_OWNER INDEXES MIS_BBV_CALL_DATA_PR INDEX 754,099 2.29 MIS_OWNER DATA MIS_BBV_CALL_DATA TABLE 436,375 1.32 ------------------------------------------------------------- Top 5 Row Lock Waits per Segment for DB: MIS Instance: MIS Snaps: 3667 -3670 -> End Segment Row Lock Waits Threshold: 100 Row Subobject Obj. Lock Owner Tablespace Object Name Name Type Waits %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- MIS_OWNER DATA MIS_BBV_CALL_LOG_IDX INDEX 43 57.33 PERFSTAT STATSPACK_ STATS$LATCH_PK INDEX 4 5.33 PERFSTAT STATSPACK_ STATS$LATCH_CHILDREN INDEX 4 5.33 PERFSTAT STATSPACK_ STATS$LATCH_MISSES_S INDEX 4 5.33 PERFSTAT STATSPACK_ STATS$SQL_SUMMARY_PK INDEX 4 5.33 ------------------------------------------------------------- Dictionary Cache Stats for DB: MIS Instance: MIS Snaps: 3667 -3670 ->"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_database_links 71 0.0 0 0 2 dc_files 1,044 0.0 0 0 105 dc_histogram_defs 926 1.5 0 0 1,676 dc_object_ids 2,913 1.2 0 0 570 dc_objects 2,391 0.5 0 37 1,605 dc_profiles 1,242 0.0 0 0 3 dc_rollback_segments 6,629 0.0 0 0 16 dc_segments 6,520 0.0 0 3,484 626 dc_sequences 51 0.0 0 51 5 dc_tablespace_quotas 3,450 0.0 0 3,450 6 dc_tablespaces 463,166 0.0 0 0 16 dc_user_grants 35,922 0.0 0 0 45 dc_usernames 2,520 0.0 0 0 80 dc_users 43,857 0.0 0 0 51 ------------------------------------------------------------- Library Cache Activity for DB: MIS Instance: MIS Snaps: 3667 -3670 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 439 0.0 439 0.0 0 0 CLUSTER 136 0.0 108 0.0 0 0 INDEX 28 0.0 28 0.0 0 0 SQL AREA 22,694 4.4 893,864 0.3 274 100 TABLE/PROCEDURE 4,470 0.2 8,555 0.9 55 0 ------------------------------------------------------------- Shared Pool Advisory for DB: MIS Instance: MIS End Snap: 3670 -> 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 ----------- ----- ---------- ------------ ------------ ------- --------------- 256 .5 257 59,728 61,385 1.0 4,930,187 320 .6 320 77,836 61,386 1.0 4,930,262 384 .8 383 102,057 61,388 1.0 4,930,398 448 .9 446 126,278 61,390 1.0 4,930,561 512 1.0 509 138,192 61,400 1.0 4,931,403 576 1.1 555 147,659 61,409 1.0 4,933,156 640 1.3 555 147,659 61,409 1.0 4,933,156 704 1.4 555 147,659 61,409 1.0 4,933,156 768 1.5 555 147,659 61,409 1.0 4,933,156 832 1.6 555 147,659 61,409 1.0 4,933,156 896 1.8 555 147,659 61,409 1.0 4,933,156 960 1.9 555 147,659 61,409 1.0 4,933,156 1,024 2.0 555 147,659 61,409 1.0 4,933,156 ------------------------------------------------------------- SGA Memory Summary for DB: MIS Instance: MIS Snaps: 3667 -3670 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 1,375,731,712 Fixed Size 731,864 Redo Buffers 2,383,872 Variable Size 822,083,584 ---------------- sum 2,200,931,032 ------------------------------------------------------------- SGA breakdown difference for DB: MIS Instance: MIS Snaps: 3667 -3670 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 167,772,160 167,772,160 0.00 large free memory 67,108,864 67,108,864 0.00 shared 1M buffer 2,098,176 2,098,176 0.00 shared Checkpoint queue 1,281,280 1,281,280 0.00 shared FileIdentificatonBlock 874,192 874,192 0.00 shared FileOpenBlock 2,977,384 2,977,384 0.00 shared KGK heap 16,600 16,600 0.00 shared KGLS heap 2,516,104 2,609,104 3.70 shared KQR L PO 2,474,848 2,474,848 0.00 shared KQR M PO 1,479,008 1,481,568 0.17 shared KQR S SO 14,632 14,632 0.00 shared KQR X PO 5,152 5,152 0.00 shared KSXR pending messages que 853,952 853,952 0.00 shared KSXR receive buffers 1,034,000 1,034,000 0.00 shared MTTR advisory 454,840 454,840 0.00 shared PL/SQL DIANA 1,373,920 1,489,552 8.42 shared PL/SQL MPCODE 4,682,592 4,864,696 3.89 shared PL/SQL PPCODE 12,128 12,128 0.00 shared PLS non-lib hp 3,208 3,208 0.00 shared PX subheap 16,160 16,160 0.00 shared db_block_hash_buckets 9,927,248 9,927,248 0.00 shared dictionary cache 4,274,432 4,274,432 0.00 shared errors 60,632 60,632 0.00 shared event statistics per sess 1,932,560 1,932,560 0.00 shared fixed allocation callback 2,040 2,040 0.00 shared free memory 88,260,040 73,865,480 -16.31 shared joxs heap init 4,240 4,240 0.00 shared library cache 246,275,224 249,512,304 1.31 shared message pool freequeue 771,984 771,984 0.00 shared miscellaneous 41,441,776 41,441,712 -0.00 shared parameters 103,520 123,352 19.16 shared sim memory hea 1,338,872 1,338,872 0.00 shared sql area 170,623,376 181,367,328 6.30 shared table definiti 17,312 17,312 0.00 shared trigger defini 272 272 0.00 shared trigger inform 704 1,168 65.91 shared trigger source 152 152 0.00 buffer_cache 1,375,731,712 1,375,731,712 0.00 fixed_sga 731,864 731,864 0.00 log_buffer 2,360,320 2,360,320 0.00 ------------------------------------------------------------- Resource Limit Stats for DB: MIS Instance: MIS End Snap: 3670 -> only rows with Current or Maximum Utilization > 80% of Limit are shown -> ordered by resource name Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- parallel_max_servers 0 5 6 6 ------------------------------------------------------------- init.ora Parameters for DB: MIS Instance: MIS Snaps: 3667 -3670 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- background_core_dump partial background_dump_dest /breezem/orabase/admin/MIS/bdump backup_tape_io_slaves TRUE compatible 9.2.0.0.0 control_file_record_keep_time 40 control_files /breezem/oradata01/MIS/control1.c core_dump_dest /breezem/orabase/admin/MIS/cdump db_block_size 8192 db_cache_size 1375731712 db_domain breezem db_file_multiblock_read_count 8 db_files 500 db_name MIS fast_start_mttr_target 300 fast_start_parallel_rollback false global_names FALSE instance_name MIS java_pool_size 167772160 job_queue_processes 10 large_pool_size 67108864 log_archive_dest /breezem/oraarch01/MIS/ log_archive_format LOG_%t_%S.arc log_archive_start TRUE log_buffer 2097152 log_checkpoints_to_alert TRUE max_dump_file_size 10240 nls_date_format HH24:MI DD/MM/YYYY open_cursors 400 optimizer_mode CHOOSE os_roles FALSE processes 150 remote_login_passwordfile EXCLUSIVE remote_os_authent FALSE resource_limit TRUE sga_max_size 2200931032 shadow_core_dump partial shared_pool_reserved_size 26214400 shared_pool_size 536870912 sort_area_size 1048576 timed_statistics TRUE undo_management AUTO undo_retention 10800 undo_tablespace UNDO user_dump_dest /breezem/orabase/admin/MIS/udump utl_file_dir /appl/oracle/logs, /breezem/cdrfi ------------------------------------------------------------- End of Report