Re: Any Comments - StatsPack Report

From: Richard Piasecki <usenet2_at_ogoent.com>
Date: 5 Apr 2007 04:13:02 -0500
Message-ID: <08f9131obplv6eno3sslb6ktfhdgudu4sg@4ax.com>

Yep. You're right. It's a statspack report.

Rich

On 4 Apr 2007 22:51:14 -0700, ronald.brayan_at_gmail.com wrote:

>
>STATSPACK report for
>
>DB Name DB Id Instance Inst Num Release Cluster
>Host
>------------ ----------- ------------ -------- ----------- -------
>------------
>PRODDWDB 2881301062 proddwdb 1 9.2.0.7.0 NO
>dcdwdb01
>
> Snap Id Snap Time Sessions Curs/Sess Comment
> --------- ------------------ -------- ---------
>-------------------
>Begin Snap: 4375 02-Apr-07 14:00:02 26 4,277.3
> End Snap: 4376 02-Apr-07 15:00:05 28 3,977.3
> Elapsed: 60.05 (mins)
>
>Cache Sizes (end)
>~~~~~~~~~~~~~~~~~
> Buffer Cache: 512M Std Block Size:
>8K
> Shared Pool Size: 304M Log Buffer:
>102,400K
>
>Load Profile
>~~~~~~~~~~~~ Per Second Per
>Transaction
> ---------------
>---------------
> Redo size: 2,213.36
>1,702.91
> Logical reads: 475.92
>366.16
> Block changes: 18.70
>14.39
> Physical reads: 1,080.71
>831.48
> Physical writes: 865.00
>665.51
> User calls: 9.03
>6.95
> Parses: 1.12
>0.86
> Hard parses: 0.00
>0.00
> Sorts: 1.24
>0.95
> Logons: 0.01
>0.01
> Executes: 4.83
>3.72
> Transactions: 1.30
>
> % Blocks changed per Read: 3.93 Recursive Call %: 49.96
> Rollback per transaction %: 0.06 Rows per Sort: ########
>
>Instance Efficiency Percentages (Target 100%)
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 100.00 Redo NoWait %: 100.00
> Buffer Hit %: 45.10 In-memory Sort %: 73.43
> Library Hit %: 99.65 Soft Parse %: 99.55
> Execute to Parse %: 76.86 Latch Hit %: 100.00
>Parse CPU to Parse Elapsd %: 12.10 % Non-Parse CPU: 99.52
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 87.74 87.42
> % SQL with executions>1: 74.94 73.86
> % Memory for SQL w/exec>1: 49.10 49.06
>
>Top 5 Timed Events
>~~~~~~~~~~~~~~~~~~
>% Total
>Event Waits Time (s)
>Ela Time
>-------------------------------------------- ------------ -----------
>--------
>db file sequential read 231,624
>97 42.67
>CPU time
>71 31.11
>db file scattered read 111,197
>28 12.43
>SQL*Net more data to client 488,425
>12 5.43
>log file parallel write 4,802
>8 3.65
>
>-------------------------------------------------------------
>Wait Events for DB: PRODDWDB Instance: proddwdb Snaps: 4375 -4376
>-> 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 231,624 0 97
>0 49.5
>db file scattered read 111,197 0 28
>0 23.7
>SQL*Net more data to client 488,425 0 12
>0 104.3
>log file parallel write 4,802 0 8
>2 1.0
>log file sync 4,662 0 8
>2 1.0
>direct path read 1,564,337 0 1
>0 334.0
>control file parallel write 1,197 0 1
>1 0.3
>process startup 11 0 1
>64 0.0
>control file sequential read 457 0 0
>0 0.1
>direct path write 2,368 0 0
>0 0.5
>latch free 1 0 0
>0 0.0
>SQL*Net message from client 32,469 0 12,550
>387 6.9
>PX Idle Wait 1,792 1,792 3,517
>1963 0.4
>queue messages 719 719 3,514
>4888 0.2
>jobq slave wait 234 227 681
>2910 0.0
>SQL*Net more data from clien 133 0 2
>12 0.0
>SQL*Net message to client 32,471 0 0
>0 6.9
>
>-------------------------------------------------------------
>Background Wait Events for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> 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 4,802 0 8
>2 1.0
>db file scattered read 202 0 1
>6 0.0
>control file parallel write 1,197 0 1
>1 0.3
>db file sequential read 55 0 0
>1 0.0
>control file sequential read 360 0 0
>0 0.1
>rdbms ipc message 12,976 3,567 12,318
>949 2.8
>pmon timer 1,207 1,207 3,518
>2915 0.3
>smon timer 1,183 0 3,512
>2969 0.3
>
>-------------------------------------------------------------
>SQL ordered by Gets for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>-> 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
>--------------- ------------ -------------- ------ -------- ---------
>----------
> 76,533 121 632.5 4.5 19.96 42.73
>101665063
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 59,686 1,917 31.1 3.5 8.35 23.98
>3887054322
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 35,610 267 133.4 2.1 9.31 29.98
>2056020000
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 24,616 1,171 21.0 1.4 15.60 19.09
>4271742580
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_result_queue.job_num,ofsa_result_queue.result_type,o
>fsa_result_queue.result_message from ofsa_result_queue order by
>ofsa_result_queue.job_num, ofsa_result_queue.result_type
>
> 18,578 4,622 4.0 1.1 0.35 0.36
>2117262372
>Module: ? @dcdwdb01 (TNS V1-V3)
>update ofsa_result_queue set result_message = :result_message wh
>ere ofsa_result_queue.job_num = :job_num and ofsa_result_queue
>.result_type = :result_type
>
> 14,412 1,201 12.0 0.8 0.67 0.62
>2390665089
>Module: ? @dcdwdb01 (TNS V1-V3)
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 14,064 1,172 12.0 0.8 4.24 4.27
>2907753034
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>SQL ordered by Gets for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>-> 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
>--------------- ------------ -------------- ------ -------- ---------
>----------
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 10,130 1 10,130.0 0.6 8.69 8.50
>4166343902
>Module: T.O.A.D.
>select * from dba_jobs_running
>
> 6,336 668 9.5 0.4 0.03 0.04
>2120239928
>SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_row
>s(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SY
>S.CDEF$ CD WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON#
>= CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND C
>D.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND ROWNUM <= 1 UNION ALL SE
>
> 4,924 12 410.3 0.3 0.02 0.06
>1911606726
>SELECT /*+ rule */ C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETF
>ORM COL_CSF, C.DEFAULT$ COL_DEF, C.NULL$ COL_NULL, C.PROPERTY CO
>L_PROP, C.COL# COL_UNUM, C.INTCOL# COL_INUM, C.OBJ# COL_OBJ, C.S
>CALE COL_SCALE, H.BUCKET_CNT H_BCNT, H.DISTCNT H_PNDV, C.LENGTH
>COL_LEN, CU.TIMESTAMP CU_TIME, CU.EQUALITY_PREDS CU_EP, CU.EQUIJ
>
> 2,831 1 2,831.0 0.2 1.74 3.71
>333476733
>DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
>broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
>ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
>
> 2,160 720 3.0 0.1 0.60 3522.37
>1182811714
>Module: OWB Runtime Service - Infrastructure
>BEGIN wb_rt_service_control.wait( :1 , :2 , :
>3 ) ; END;
>
> 2,157 2,157 1.0 0.1 0.11
>0.10 27751290
>Module: OWB Runtime Service - Infrastructure
>select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, ta
>b.priority, tab.delay, tab.expiration, tab.retry_count, tab.ex
>ception_qschema, tab.exception_queue, tab.chain_no, tab.local_
>order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.
>enq_tid, tab.step_no, tab.sender_name, tab.sender_address, tab
>
> 2,121 707 3.0 0.1 0.07 0.07
>1316169839
>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
>
> 1,820 364 5.0 0.1 0.01 0.01
>1085462914
>SELECT SU.NAME, SO.NAME, A.STATSTYPE#, A.INTCOL# FROM ASSOCIATIO
>N$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU WHERE O.OWNER#=
>U.USER# AND A.OBJ#=O.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=A.INTC
>OL# AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=
>2 AND U.NAME=:B3 AND O.NAME=:B2 AND C.NAME=:B1
>SQL ordered by Gets for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>-> End Buffer Gets Threshold: 10000
>-> Note that resources reported for PL/SQL includes the resources used
>by
> all SQL statements called within the PL/SQL code. As individual
>SQL
> statements are also reported, it is possible and valid for the
>summed
> total % to exceed 100
>
> CPU Elapsd
> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s)
>Hash Value
>--------------- ------------ -------------- ------ -------- ---------
>----------
>
> 1,820 364 5.0 0.1 0.00 0.01
>2749947875
>SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATIO
>N$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, O
>BJ$ TY WHERE O.OWNER#=U.USER# AND A.OBJ#=TY.OBJ# AND O.OBJ#=C.OB
>
>
>-------------------------------------------------------------
>SQL ordered by Reads for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
>Hash Value
>--------------- ------------ -------------- ------ -------- ---------
>----------
> 73,532 121 607.7 1.9 19.96 42.73
>101665063
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 30,973 267 116.0 0.8 9.31 29.98
>2056020000
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 29,833 1,917 15.6 0.8 8.35 23.98
>3887054322
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 12,970 1,171 11.1 0.3 15.60 19.09
>4271742580
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_result_queue.job_num,ofsa_result_queue.result_type,o
>fsa_result_queue.result_message from ofsa_result_queue order by
>ofsa_result_queue.job_num, ofsa_result_queue.result_type
>
> 5,731 1 5,731.0 0.1 1.74 3.71
>333476733
>DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
>broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
>ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
>
> 2,806 1 2,806.0 0.1 0.72 1.64
>4043595143
>INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
> , TEXT_SUBSET , SHARABLE_MEM , SORTS , MODULE , LOADED_VERSIONS
> , FETCHES , EXECUTIONS , LOADS , INVALIDATIONS , PARSE_CALLS ,
>DISK_READS , BUFFER_GETS , ROWS_PROCESSED , COMMAND_TYPE , ADDRE
>SS , HASH_VALUE , VERSION_COUNT , CPU_TIME , ELAPSED_TIME , OUTL
>
> 2,678 1 2,678.0 0.1 0.70 1.63
>3970651392
>INSERT INTO STATS$SQL_STATISTICS ( SNAP_ID , DBID , INSTANCE_NUM
>BER , TOTAL_SQL , TOTAL_SQL_MEM , SINGLE_USE_SQL , SINGLE_USE_SQ
>L_MEM ) SELECT :B3 , :B2 , :B1 , COUNT(1) , SUM(SHARABLE_MEM) ,
>SUM(DECODE(EXECUTIONS, 1, 1, 0)) , SUM(DECODE(EXECUTIONS, 1, SHA
>RABLE_MEM, 0)) FROM STATS$V$SQLXS WHERE IS_OBSOLETE = 'N'
>
> 1,186 1 1,186.0 0.0 0.10 1.20
>4115672663
>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
>sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
> 1048576) and o.obj#=t.obj# and o.owner# = u.user#
>
>SQL ordered by Reads for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> End Disk Reads Threshold: 1000
>
> CPU Elapsd
> Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
>Hash Value
>--------------- ------------ -------------- ------ -------- ---------
>----------
> 128 1 128.0 0.0 0.03 0.14
>3070691348
>Module: Discoverer4
>SELECT DOC_ID, DOC_EU_ID, DOC_LENGTH, DOC_BATCH, DOC_CONTENT_TYP
>E, DOC_USER_PROP1, DOC_USER_PROP2, DOC_DEVELOPER_KEY, DOC_NAME,
>DOC_DESCRIPTION, DOC_CREATED_BY, DOC_CREATED_DATE, DOC_UPDATED_B
>Y, DOC_UPDATED_DATE, DOC_ELEMENT_STATE, NOTM FROM BOIDW.EUL4_DO
>CUMENTS WHERE (DOC_ID> :DOC_ID OR :DOC_ID is null) AND DOC_ID i
>
> 36 12 3.0 0.0 0.03 0.11
>2201872251
>SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
>:1
>
> 30 1,172 0.0 0.0 4.24 4.27
>2907753034
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 29 1,201 0.0 0.0 0.67 0.62
>2390665089
>Module: ? @dcdwdb01 (TNS V1-V3)
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 26 1 26.0 0.0 0.00 0.01
>536839608
>SELECT NVL(MAX(BEGIN_TIME), TO_DATE('01011900','DDMMYYYY')) FROM
> STATS$UNDOSTAT WHERE DBID = :B2 AND INSTANCE_NUMBER = :B1
>
> 21 1 21.0 0.0 0.01 0.06
>3170171180
>INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S
>QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT /*+ ord
>ered use_nl(vst) */ VST.HASH_VALUE , NEW_SQL.TEXT_SUBSET , VST.P
>IECE , VST.SQL_TEXT , VST.ADDRESS , VST.COMMAND_TYPE , NEW_SQL.S
>NAP_ID FROM (SELECT HASH_VALUE , ADDRESS , TEXT_SUBSET , SNAP_ID
>
> 11 1 11.0 0.0 0.01 0.02
>1223994815
>INSERT INTO STATS$LATCH_MISSES_SUMMARY ( SNAP_ID , DBID , INSTAN
>CE_NUMBER , PARENT_NAME , WHERE_IN_CODE , NWFAIL_COUNT , SLEEP_C
>OUNT , WTR_SLP_COUNT ) SELECT :B3 , :B2 , :B1 , PARENT_NAME , "W
>HERE" , SUM(NWFAIL_COUNT) , SUM(SLEEP_COUNT) , SUM(WTR_SLP_COUNT
>) FROM V$LATCH_MISSES WHERE SLEEP_COUNT > 0 GROUP BY :B3 , :B2 ,
>
> 8 12 0.7 0.0 0.02 0.06
>1911606726
>SELECT /*+ rule */ C.NAME COL_NAME, C.TYPE# COL_TYPE, C.CHARSETF
>
>
>-------------------------------------------------------------
>SQL ordered by Executions for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -437
>-> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
>Hash Value
>------------ --------------- ---------------- ----------- ----------
>----------
> 4,622 4,622 1.0 0.00 0.00
>2117262372
>Module: ? @dcdwdb01 (TNS V1-V3)
>update ofsa_result_queue set result_message = :result_message wh
>ere ofsa_result_queue.job_num = :job_num and ofsa_result_queue
>.result_type = :result_type
>
> 2,157 0 0.0 0.00 0.00
>27751290
>Module: OWB Runtime Service - Infrastructure
>select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, ta
>b.priority, tab.delay, tab.expiration, tab.retry_count, tab.ex
>ception_qschema, tab.exception_queue, tab.chain_no, tab.local_
>order_no, tab.enq_time, tab.time_manager_info, tab.state, tab.
>enq_tid, tab.step_no, tab.sender_name, tab.sender_address, tab
>
> 1,917 296,639 154.7 0.00 0.01
>3887054322
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 1,201 0 0.0 0.00 0.00
>2390665089
>Module: ? @dcdwdb01 (TNS V1-V3)
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 1,172 277,764 237.0 0.00 0.00
>2907753034
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 1,171 2,904,080 2,480.0 0.01 0.02
>4271742580
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_result_queue.job_num,ofsa_result_queue.result_type,o
>fsa_result_queue.result_message from ofsa_result_queue order by
>ofsa_result_queue.job_num, ofsa_result_queue.result_type
>
> 720 720 1.0 0.00 4.89
>1182811714
>Module: OWB Runtime Service - Infrastructure
>BEGIN wb_rt_service_control.wait( :1 , :2 , :
>3 ) ; END;
>
> 708 708 1.0 0.00 0.00
>1693927332
>select count(*) from sys.job$ where (next_date > sysdate) and (n
>ext_date < (sysdate+5/86400))
>
> 707 10 0.0 0.00 0.00
>1316169839
>SQL ordered by Executions for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -437
>-> End Executions Threshold: 100
>
> CPU per Elap per
> Executions Rows Processed Rows per Exec Exec (s) Exec (s)
>Hash Value
>------------ --------------- ---------------- ----------- ----------
>----------
>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
>
> 668 668 1.0 0.00 0.00
>2120239928
>SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_row
>s(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SY
>S.CDEF$ CD WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON#
>= CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND C
>D.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND ROWNUM <= 1 UNION ALL SE
>
> 364 0 0.0 0.00 0.00
>1085462914
>SELECT SU.NAME, SO.NAME, A.STATSTYPE#, A.INTCOL# FROM ASSOCIATIO
>N$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU WHERE O.OWNER#=
>U.USER# AND A.OBJ#=O.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=A.INTC
>OL# AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=
>2 AND U.NAME=:B3 AND O.NAME=:B2 AND C.NAME=:B1
>
> 364 364 1.0 0.00 0.00
>1921749835
>update hist_head$ set bucket_cnt=:3, row_cnt=:4, cache_cnt=:5,nu
>ll_cnt=:6, timestamp#=:7, sample_size=:8, minimum=:9, maximum=:1
>0,distcnt=:11, lowval=:12, hival=:13, density=:14, spare1=:15, s
>pare2=:16, avgcln=:17, col#=:18 where obj#=:1 and intcol#=:2
>
> 364 0 0.0 0.00 0.00
>2749947875
>SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATIO
>N$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, O
>BJ$ TY WHERE O.OWNER#=U.USER# AND A.OBJ#=TY.OBJ# AND O.OBJ#=C.OB
>J# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# AND CT.TOID=TY.OI
>D$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2
>
> 267 334,457 1,252.6 0.03 0.11
>2056020000
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>identity_code,a.as_of_date,a.accrual_basis_cd,a.amrt_term,a.amrt
>_term_mult,a.amrt_type_cd,a.cur_book_bal,a.cur_par_bal,a.cur_tp_
>per_adb,a.cur_gross_rate,a.cur_net_rate,a.cur_payment,a.deferred
>
> 264 260 1.0 0.00 0.00
>4274598960
>select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
>estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
>density, col#, spare1, spare2, avgcln from hist_head$ where obj#
>=:1 and intcol#=:2
>
> 121 746,447 6,169.0 0.16 0.35
>101665063
>Module: ? @dcdwdb01 (TNS V1-V3)
>select a.rowid,a.ORG_UNIT_ID,a.GL_ACCOUNT_ID,a.COMMON_COA_ID,a.R
>M_COA_ID,a.TPCOA_ID,a.PA_COA_ID,a.iso_currency_cd,a.id_number,a.
>
>
>-------------------------------------------------------------
>SQL ordered by Parse Calls for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -43
>-> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
>------------ ------------ -------- ----------
> 1,201 1,201 29.82 2390665089
>Module: ? @dcdwdb01 (TNS V1-V3)
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 1,172 1,172 29.10 2907753034
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_request_queue.job_num,ofsa_request_queue.login_name,
>ofsa_request_queue.status,ofsa_request_queue.process_id,ofsa_req
>uest_queue.return_code,ofsa_request_queue.priority,ofsa_request_
>queue.application,ofsa_request_queue.service_request,ofsa_reques
>t_queue.schedule_date,ofsa_request_queue.request_date,ofsa_reque
>
> 1,171 1,171 29.07 4271742580
>Module: C:\ORANT\OFSA45\TSCGLBL.DLL
>select ofsa_result_queue.job_num,ofsa_result_queue.result_type,o
>fsa_result_queue.result_message from ofsa_result_queue order by
>ofsa_result_queue.job_num, ofsa_result_queue.result_type
>
> 15 15 0.37 519146537
>Module: T.O.A.D.
>select * from dba_jobs
>
> 14 14 0.35 4143084494
>select privilege#,level from sysauth$ connect by grantee#=prior
>privilege# and privilege#>0 start with grantee#=:1 and privilege
>#>0
>
> 12 707 0.30 1316169839
>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
>
> 12 12 0.30 1403276364
>select parttype, partcnt, partkeycols, flags, defts#, defpctfree
>, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize,
> defminexts, defmaxexts, defextpct, deflists, defgroups, deflogg
>ing, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256)
>, 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpar
>
> 12 12 0.30 1932955448
>delete from superobj$ where subobj# = :1
>
> 12 12 0.30 2120805076
>update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null
>,:5),tab#=decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=
>decode(:9,0,null,:9),audit$=:10,flags=:11,pctfree$=:12,pctused$=
>:13,initrans=:14,maxtrans=:15,rowcnt=:16,blkcnt=:17,empcnt=:18,a
>vgspc=:19,chncnt=:20,avgrln=:21,analyzetime=:22,samplesize=:23,c
>
>SQL ordered by Parse Calls for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -43
>-> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
>------------ ------------ -------- ----------
> 12 12 0.30 2201872251
>SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
>:1
>
> 12 12 0.30 3541154470
>select u.name, o.name, o.namespace, o.type#, decode(bitand(i.pro
>perty,1024),0,0,1) from ind$ i,obj$ o,user$ u where i.obj#=:1 an
>d o.obj#=i.bo# and o.owner#=u.user#
>
> 12 12 0.30 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
>
>
> 12 12 0.30 3997906522
>select user# from sys.user$ where name = 'OUTLN'
>
> 11 11 0.27 297937389
>update sys.job$ set this_date=:1 where job=:2
>
> 11 11 0.27 1356713530
>select privilege#,level from sysauth$ connect by grantee#=prior
>privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
>1) and privilege#>0
>
> 11 11 0.27 2594425492
>select u1.user#, u2.user#, u3.user#, failures, flag, interval#,
> what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys
>.user$ u2, sys.user$ u3 where job=:1 and (next_date < sysdate o
>r :2 != 0) and lowner = u1.name and powner = u2.name and cowner
> = u3.name
>
> 11 11 0.27 2714695468
>alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA
>' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARAC
>TERS='.,' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICA
>N' NLS_SORT='BINARY'
>
> 11 11 0.27 4075357577
>update sys.job$ set failures=0, this_date=null, flag=:1, last_da
>te=:2, next_date = greatest(:3, sysdate), total=total+(sysdate
>-nvl(this_date,sysdate)) where job=:4
>
> 10 10 0.25 380450070
>select sysdate + (6/1440) from dual
>
> 10 10 0.25 1400704878
>SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM DUAL
>
> 10 10 0.25 1502226131
>DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
>broken BOOLEAN := FALSE; BEGIN wb_rti_service_job.check_service(
>1); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0;
>END IF; END;
>SQL ordered by Parse Calls for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -43
>-> End Parse Calls Threshold: 1000
>
> % Total
> Parse Calls Executions Parses Hash Value
>------------ ------------ -------- ----------
>
> 10 10 0.25 3240259259
>SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR U
>
>
>-------------------------------------------------------------
>SQL ordered by Version Count for DB: PRODDWDB Instance: proddwdb
>Snaps: 4375 -
>-> End Version Count Threshold: 20
>
> Version
> Count Executions Hash Value
>-------- ------------ ------------
> 22 1 3676244533
>Module: Discoverer4
>EXPLAIN PLAN SET STATEMENT_ID = '1' INTO BOIDW.EUL4_PLAN_TABLE F
>OR SELECT * FROM BOIDW.EUL4_PLAN_TABLE
>
>
>-------------------------------------------------------------
>Instance Activity Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>
>Statistic Total per Second
>per Trans
>--------------------------------- ------------------ --------------
>------------
>CPU used by this session 7,085
>2.0 1.5
>CPU used when call started 6,900
>1.9 1.5
>CR blocks created 37
>0.0 0.0
>Cached Commit SCN referenced 137,843
>38.3 29.4
>Commit SCN cached 2
>0.0 0.0
>DBWR buffers scanned 89,667
>24.9 19.2
>DBWR checkpoint buffers written 7
>0.0 0.0
>DBWR checkpoints 0
>0.0 0.0
>DBWR free buffers found 71,579
>19.9 15.3
>DBWR lru scans 365
>0.1 0.1
>DBWR make free requests 472
>0.1 0.1
>DBWR revisited being-written buff 0
>0.0 0.0
>DBWR summed scan depth 89,667
>24.9 19.2
>DBWR transaction table writes 0
>0.0 0.0
>DBWR undo block writes 326
>0.1 0.1
>DFO trees parallelized 0
>0.0 0.0
>DML statements parallelized 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 downgraded to 0
>0.0 0.0
>Parallel operations not downgrade 0
>0.0 0.0
>SQL*Net roundtrips to/from client 32,466
>9.0 6.9
>SQL*Net roundtrips to/from dblink 0
>0.0 0.0
>active txn count during cleanout 54
>0.0 0.0
>background checkpoints completed 0
>0.0 0.0
>background checkpoints started 0
>0.0 0.0
>background timeouts 4,309
>1.2 0.9
>branch node splits 0
>0.0 0.0
>buffer is not pinned count 917,658
>254.7 196.0
>buffer is pinned count 2,603,305
>722.5 555.9
>bytes received via SQL*Net from c 3,434,831
>953.3 733.5
>bytes received via SQL*Net from d 0
>0.0 0.0
>bytes sent via SQL*Net to client 987,527,302 274,084.7
>210,874.9
>bytes sent via SQL*Net to dblink 0
>0.0 0.0
>calls to get snapshot scn: kcmgss 47,439
>13.2 10.1
>calls to kcmgas 4,791
>1.3 1.0
>calls to kcmgcs 37
>0.0 0.0
>change write time 48
>0.0 0.0
>cleanout - number of ktugct calls 46,210
>12.8 9.9
>cleanouts and rollbacks - consist 0
>0.0 0.0
>cleanouts only - consistent read 46,160
>12.8 9.9
>cluster key scan block gets 2,880
>0.8 0.6
>cluster key scans 992
>0.3 0.2
>commit cleanout failures: block l 0
>0.0 0.0
>commit cleanout failures: buffer 0
>0.0 0.0
>commit cleanout failures: callbac 0
>0.0 0.0
>commit cleanout failures: cannot 0
>0.0 0.0
>commit cleanouts 4,887
>1.4 1.0
>commit cleanouts successfully com 4,887
>1.4 1.0
>commit txn count during cleanout 46,285
>12.9 9.9
>consistent changes 121
>0.0 0.0
>consistent gets 1,113,605
>309.1 237.8
>consistent gets - examination 68,442
>19.0 14.6
>current blocks converted for CR 0
>0.0 0.0
>cursor authentications 0
>0.0 0.0
>Instance Activity Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>
>Statistic Total per Second
>per Trans
>--------------------------------- ------------------ --------------
>------------
>data blocks consistent reads - un 45
>0.0 0.0
>db block changes 67,384
>18.7 14.4
>db block gets 601,120
>166.8 128.4
>deferred (CURRENT) block cleanout 2,447
>0.7 0.5
>dirty buffers inspected 17,271
>4.8 3.7
>enqueue conversions 25
>0.0 0.0
>enqueue releases 54,630
>15.2 11.7
>enqueue requests 54,652
>15.2 11.7
>enqueue timeouts 22
>0.0 0.0
>enqueue waits 0
>0.0 0.0
>exchange deadlocks 0
>0.0 0.0
>execute count 17,406
>4.8 3.7
>free buffer inspected 17,271
>4.8 3.7
>free buffer requested 943,221
>261.8 201.4
>hot buffers moved to head of LRU 18,158
>5.0 3.9
>immediate (CR) block cleanout app 46,160
>12.8 9.9
>immediate (CURRENT) block cleanou 63
>0.0 0.0
>index fast full scans (direct rea 0
>0.0 0.0
>index fast full scans (full) 1
>0.0 0.0
>index fast full scans (rowid rang 0
>0.0 0.0
>index fetch by key 8,241
>2.3 1.8
>index scans kdiixs1 7,389
>2.1 1.6
>leaf node 90-10 splits 3
>0.0 0.0
>leaf node splits 19
>0.0 0.0
>logons cumulative 24
>0.0 0.0
>messages received 5,269
>1.5 1.1
>messages sent 5,269
>1.5 1.1
>no buffer to keep pinned count 0
>0.0 0.0
>no work - consistent read gets 970,939
>269.5 207.3
>opened cursors cumulative 4,025
>1.1 0.9
>parse count (failures) 4
>0.0 0.0
>parse count (hard) 18
>0.0 0.0
>parse count (total) 4,028
>1.1 0.9
>parse time cpu 34
>0.0 0.0
>parse time elapsed 281
>0.1 0.1
>physical reads 3,893,806
>1,080.7 831.5
>physical reads direct 2,952,361
>819.4 630.4
>physical reads direct (lob) 0
>0.0 0.0
>physical writes 3,116,587
>865.0 665.5
>physical writes direct 3,081,221
>855.2 658.0
>physical writes direct (lob) 0
>0.0 0.0
>physical writes non checkpoint 3,116,346
>864.9 665.5
>pinned buffers inspected 0
>0.0 0.0
>prefetched blocks 598,631
>166.2 127.8
>prefetched blocks aged out before 0
>0.0 0.0
>process last non-idle time 3,603
>1.0 0.8
>queries parallelized 0
>0.0 0.0
>recursive calls 32,499
>9.0 6.9
>recursive cpu usage 230
>0.1 0.1
>redo blocks written 10,003
>2.8 2.1
>redo buffer allocation retries 0
>0.0 0.0
>redo entries 57,080
>15.8 12.2
>redo log space requests 0
>0.0 0.0
>redo log space wait time 0
>0.0 0.0
>redo ordering marks 0
>0.0 0.0
>redo size 7,974,736
>2,213.4 1,702.9
>Instance Activity Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>
>Statistic Total per Second
>per Trans
>--------------------------------- ------------------ --------------
>------------
>redo synch time 811
>0.2 0.2
>redo synch writes 4,662
>1.3 1.0
>redo wastage 2,147,752
>596.1 458.6
>redo write time 919
>0.3 0.2
>redo writer latching time 0
>0.0 0.0
>redo writes 4,802
>1.3 1.0
>rollback changes - undo records a 11
>0.0 0.0
>rollbacks only - consistent read 35
>0.0 0.0
>rows fetched via callback 1,071
>0.3 0.2
>session connect time 0
>0.0 0.0
>session logical reads 1,714,725
>475.9 366.2
>session pga memory 4,917,032
>1,364.7 1,050.0
>session pga memory max 6,516,512
>1,808.6 1,391.5
>session uga memory 47,244,963,640 13,112,673.8
>############
>session uga memory max 4,837,096
>1,342.5 1,032.9
>shared hash latch upgrades - no w 7,498
>2.1 1.6
>sorts (disk) 1,183
>0.3 0.3
>sorts (memory) 3,270
>0.9 0.7
>sorts (rows) 918,067,682 254,806.5
>196,042.6
>summed dirty queue length 90,032
>25.0 19.2
>switch current to new buffer 12
>0.0 0.0
>table fetch by rowid 1,382,832
>383.8 295.3
>table fetch continued row 19
>0.0 0.0
>table scan blocks gotten 748,706
>207.8 159.9
>table scan rows gotten 33,900,640
>9,409.0 7,239.1
>table scans (direct read) 0
>0.0 0.0
>table scans (long tables) 5
>0.0 0.0
>table scans (rowid ranges) 0
>0.0 0.0
>table scans (short tables) 5,083
>1.4 1.1
>transaction rollbacks 3
>0.0 0.0
>transaction tables consistent rea 4
>0.0 0.0
>transaction tables consistent rea 78
>0.0 0.0
>user calls 32,548
>9.0 7.0
>user commits 4,680
>1.3 1.0
>user rollbacks 3
>0.0 0.0
>write clones created in backgroun 0
>0.0 0.0
>write clones created in foregroun 0
>0.0 0.0
>
>-------------------------------------------------------------
>Tablespace IO Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>->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)
>-------------- ------- ------ ------- ------------ -------- ----------
>------
>TEMP
> 2,951,224 819 0.0 1.0 3,080,877 855
>0 0.0
>INDEX_TS
> 106,438 30 0.6 1.0 32,489 9
>0 0.0
>DATA_TS
> 124,989 35 0.3 1.0 159 0
>0 0.0
>OWB_TAR
> 59,680 17 0.2 5.0 48 0
>0 0.0
>OWB_TAR_BIG
> 30,746 9 0.3 8.0 0 0
>0 0.0
>OWB_TAR_MED
> 20,569 6 0.3 8.0 153 0
>0 0.0
>PERFSTAT
> 102 0 2.1 1.2 1,844 1
>0 0.0
>SYSTEM
> 298 0 4.9 4.2 125 0
>0 0.0
>SYS_UNDOTS
> 3 0 10.0 1.0 326 0
>0 0.0
>OWB_REPX
> 0 0 0.0 195 0
>0 0.0
>OWB_REP
> 0 0 0.0 26 0
>0 0.0
>
>-------------------------------------------------------------
>File IO Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375 -4376
>->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_TS /prodofsa/oradata/data/data_ts01.dbf
> 121 0 1.8 1.2 10 0 0
> /prodofsa/oradata/data/data_ts02.dbf
> 37 0 0.8 1.8 19 0 0
> /prodofsa/oradata/data/data_ts03.dbf
> 47 0 0.6 1.5 12 0 0
> /prodofsa/oradata/data/data_ts04.dbf
> 2 0 5.0 1.0 0 0 0
> /prodofsa/oradata/data/data_ts18.dbf
> 2 0 5.0 1.5 0 0 0
> /prodofsa/oradata/data/data_ts20.dbf
> 3 0 13.3 2.0 4 0 0
> /prodofsa/oradata/data/data_ts21.dbf
> 3 0 6.7 1.3 4 0 0
> /prodofsa/oradata/data/data_ts22.dbf
> 1 0 10.0 4.0 4 0 0
> /prodofsa/oradata/data/data_ts23.dbf
> 1 0 0.0 5.0 5 0 0
> /prodofsa/oradata/data/data_ts24.dbf
> 19 0 2.6 1.7 7 0 0
> /prodofsa/oradata/data/data_ts25.dbf
> 16,561 5 0.2 1.0 4 0 0
> /prodofsa/oradata/data/data_ts26.dbf
> 35,968 10 0.2 1.0 26 0 0
> /prodofsa/oradata/data/data_ts27.dbf
> 36,111 10 0.2 1.0 21 0 0
> /prodofsa/oradata/data/data_ts28.dbf
> 36,109 10 0.3 1.0 23 0 0
> /prodofsa/oradata/data/data_ts29.dbf
> 4 0 5.0 1.5 20 0 0
>
>INDEX_TS /prodofsa/oradata/data/index_ts01.dbf
> 14,911 4 0.4 1.0 4,980 1 0
> /prodofsa/oradata/data/index_ts02.dbf
> 16,189 4 0.5 1.0 4,905 1 0
> /prodofsa/oradata/data/index_ts03.dbf
> 16,202 4 0.8 1.0 4,895 1 0
> /prodofsa/oradata/data/index_ts04.dbf
> 15,646 4 0.6 1.0 4,929 1 0
> /prodofsa/oradata/data/index_ts05.dbf
> 19,252 5 0.8 1.0 4,954 1 0
> /prodofsa/oradata/data/index_ts06.dbf
> 19,216 5 0.8 1.0 4,956 1 0
> /prodofsa/oradata/data/index_ts07.dbf
> 5,022 1 0.2 1.0 2,870 1 0
>
>OWB_REP /prodofsa/oradata/data/owb_rep01.dbf
> 0 0 19 0 0
> /prodofsa/oradata/data/owb_rep02.dbf
> 0 0 7 0 0
>
>OWB_REPX /prodofsa/oradata/data/owb_repx01.dbf
>File IO Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375 -4376
>->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)
>-------------- ------- ------ ------- ------------ -------- ----------
>------
> 0 0 195 0 0
>
>OWB_TAR /prodofsa/oradata/data/owb_tar01.dbf
> 4,977 1 0.2 5.0 6 0 0
> /prodofsa/oradata/data/owb_tar02.dbf
> 4,975 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar03.dbf
> 4,973 1 0.2 5.0 5 0 0
> /prodofsa/oradata/data/owb_tar04.dbf
> 4,973 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar05.dbf
> 4,974 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar06.dbf
> 4,972 1 0.2 5.0 3 0 0
> /prodofsa/oradata/data/owb_tar07.dbf
> 4,972 1 0.3 5.0 3 0 0
> /prodofsa/oradata/data/owb_tar08.dbf
> 4,972 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar09.dbf
> 4,973 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar010.dbf
> 4,973 1 0.2 5.0 4 0 0
> /prodofsa/oradata/data/owb_tar11.dbf
> 4,973 1 0.2 5.0 3 0 0
> /prodofsa/oradata/data/owb_tar12.dbf
> 4,973 1 0.2 5.0 4 0 0
>
>OWB_TAR_BIG /prodofsa/oradata/data/owb_tar_big02.dbf
> 1,667 0 0.8 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big03.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big04.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big05.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big06.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big07.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big08.dbf
> 1,664 0 0.2 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big09.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big10.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big11.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big12.dbf
> 1,664 0 0.2 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big13.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big14.dbf
>File IO Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375 -4376
>->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)
>-------------- ------- ------ ------- ------------ -------- ----------
>------
> 1,664 0 0.2 8.0 0 0 0
>OWB_TAR_BIG /prodofsa/oradata/data/owb_tar_big15.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big16.dbf
> 1,664 0 0.2 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big17.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big18.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big19.dbf
> 1,664 0 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_big20.dbf
> 791 0 0.2 8.0 0 0 0
>
>OWB_TAR_MED /prodofsa/oradata/data/owb_tar_med01.dbf
> 1,793 0 0.2 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_med02.dbf
> 6,744 2 0.3 8.0 80 0 0
> /prodofsa/oradata/data/owb_tar_med03.dbf
> 5,376 1 0.3 8.0 0 0 0
> /prodofsa/oradata/data/owb_tar_med04.dbf
> 6,656 2 0.3 8.0 73 0 0
>
>PERFSTAT /prodofsa/oradata/data/perfstat_01.dbf
> 99 0 2.1 1.2 1,721 0 0
> /prodofsa/oradata/data/perfstat_02.dbf
> 3 0 0.0 1.0 123 0 0
>
>SYSTEM /prodofsa/oradata/data/system01.dbf
> 298 0 4.9 4.2 125 0 0
>
>SYS_UNDOTS /prodofsa/oradata/data/sys_undots01.dbf
> 2 0 10.0 1.0 151 0 0
> /prodofsa/oradata/data/sys_undots02.dbf
> 0 0 20 0 0
> /prodofsa/oradata/data/sys_undots03.dbf
> 1 0 10.0 1.0 78 0 0
> /prodofsa/oradata/data/sys_undots04.dbf
> 0 0 77 0 0
>
>TEMP /prodofsa/oradata/data/temp02.dbf
> 17,859 5 0.0 1.0 18,489 5 0
> /prodofsa/oradata/data/temp03.dbf
> 2,933,365 814 0.0 1.0 3,062,387 850 0
> /prodofsa/oradata/data/temp04.dbf
> 0 0 1 0 0
>
>
>-------------------------------------------------------------
>Buffer Pool Statistics for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> 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 63,520 45.1 1,714,573 941,502 35,366 0
>0 0
>
>-------------------------------------------------------------
>
>Instance Recovery Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> 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 0 44 3944 460703 460800 460800
>E 0 47 15031 424529 460800 460800
>
>-------------------------------------------------------------
>
>Buffer Pool Advisory for DB: PRODDWDB Instance: proddwdb End Snap:
>4376
>-> 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 48 .1 5,955 1.08
>399,123,288
>D 96 .2 11,910 1.05
>384,772,345
>D 144 .3 17,865 1.04
>381,853,472
>D 192 .4 23,820 1.03
>380,472,769
>D 240 .5 29,775 1.03
>378,716,322
>D 288 .6 35,730 1.02
>377,110,006
>D 336 .7 41,685 1.02
>375,216,883
>D 384 .8 47,640 1.02
>373,870,445
>D 432 .8 53,595 1.01
>371,393,234
>D 480 .9 59,550 1.00
>369,315,044
>D 512 1.0 63,520 1.00
>368,162,741
>D 528 1.0 65,505 1.00
>367,782,768
>D 576 1.1 71,460 0.99
>366,010,942
>D 624 1.2 77,415 0.99
>363,306,372
>D 672 1.3 83,370 0.98
>360,334,860
>D 720 1.4 89,325 0.97
>357,678,315
>D 768 1.5 95,280 0.96
>355,126,775
>D 816 1.6 101,235 0.96
>351,868,809
>D 864 1.7 107,190 0.95
>347,921,893
>D 912 1.8 113,145 0.94
>344,779,745
>D 960 1.9 119,100 0.91
>335,907,001
>
>-------------------------------------------------------------
>Rollback Segment Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>->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 1,185.0 0.00 0 0 0
>0
> 1 4,148.0 0.00 146,470 0 0
>0
> 2 4,149.0 0.00 110,134 0 0
>0
> 3 2,131.0 0.00 71,990 0 0
>0
> 4 3,609.0 0.00 305,560 3 0
>1
> 5 2,832.0 0.00 91,348 0 0
>0
> 6 4,889.0 0.00 76,904 1 0
>0
> 7 2,584.0 0.00 93,084 0 0
>0
> 8 1,891.0 0.00 73,792 0 0
>0
> 9 1,939.0 0.00 57,736 1 0
>0
> 10 3,359.0 0.00 77,964 0 0
>0
>
>-------------------------------------------------------------
>Rollback Segment Storage for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>->Optimal Size should be larger than Avg Active
>
>RBS No Segment Size Avg Active Optimal Size Maximum Size
>------ --------------- --------------- --------------- ---------------
> 0 401,408 8,192 401,408
> 1 3,268,608 2,045,229 343,007,232
> 2 1,171,456 966,964 134,340,608
> 3 2,220,032 673,693 636,674,048
> 4 2,220,032 899,664 1,208,082,432
> 5 1,171,456 854,193 193,060,864
> 6 1,171,456 331,772,016 310,501,376
> 7 1,171,456 2,694,641 4,227,981,312
> 8 8,511,488 2,045,669 3,624,001,536
> 9 253,952 455,055 172,089,344
> 10 2,220,032 4,548,730 193,060,864
>
>-------------------------------------------------------------
>Undo Segment Summary for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> 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 219 ########## 6,819 1 0 0
>0/0/0/0/0/0
>
>-------------------------------------------------------------
>
>
>Undo Segment Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>-> 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
>------------ ------------ -------- ------- -------- ------- ------
>-------------
>02-Apr 15:05 55 ######## 15 1 0 0
>0/0/0/0/0/0
>02-Apr 14:55 8 ######## 6,819 1 0 0
>0/0/0/0/0/0
>02-Apr 14:45 1 ######## 11 1 0 0
>0/0/0/0/0/0
>02-Apr 14:35 8 ######## 5,362 1 0 0
>0/0/0/0/0/0
>02-Apr 14:25 35 ######## 4,732 1 0 0
>0/0/0/0/0/0
>02-Apr 14:15 51 ######## 733 1 0 0
>0/0/0/0/0/0
>02-Apr 14:05 61 ######## 3,899 1 0 0
>0/0/0/0/0/0
>
>-------------------------------------------------------------
>Latch Activity for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>->"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 4,802 0.0
>0 0
>FOB s.o list latch 62 0.0
>0 0
>SQL memory manager worka 67 0.0
>0 0
>active checkpoint queue 1,744 0.0
>0 0
>archive control 1 0.0
>0 0
>cache buffers chains 6,268,476 0.0 0.0 0
>1,558,846 0.0
>cache buffers lru chain 73,774 0.0 0.0 0
>1,653,034 0.0
>cas latch 90,202 0.0
>0 0
>channel handle pool latc 2,390 0.0
>0 0
>channel operations paren 10,734 0.0
>0 0
>checkpoint queue latch 370,584 0.0 0.0 0
>46,462 0.0
>child cursor hash table 186 0.0
>0 0
>dml lock allocation 11,073 0.0
>0 0
>dummy allocation 46 0.0
>0 0
>enqueue hash chains 109,315 0.0
>0 0
>enqueues 94,244 0.0
>0 0
>event group latch 13 0.0
>0 0
>hash table column usage 16 0.0 0
>54 0.0
>job workq parent latch 0 0
>22 0.0
>job_queue_processes para 70 0.0
>0 0
>ktm global data 1,183 0.0
>0 0
>lgwr LWN SCN 5,616 0.0
>0 0
>library cache 95,705 0.0 0.3 0
>355 0.0
>library cache load lock 48 0.0
>0 0
>library cache pin 33,328 0.0
>0 0
>library cache pin alloca 19,831 0.0
>0 0
>list of block allocation 33 0.0
>0 0
>loader state object free 2,409 0.0
>0 0
>longop free list parent 1,213 0.0 0
>1,410,535 0.0
>message pool operations 2,366 0.0
>0 0
>messages 32,931 0.0
>0 0
>mostly latch-free SCN 10,408 0.0
>0 0
>multiblock read objects 222,602 0.0
>0 0
>ncodef allocation latch 57 0.0
>0 0
>object stats modificatio 5 0.0
>0 0
>post/wait queue 6,982 0.0 0
>4,662 0.0
>process allocation 24 0.0 0
>13 0.0
>process group creation 24 0.0
>0 0
>redo allocation 67,490 0.0
>0 0
>redo copy 0 0
>57,073 0.0
>redo on-disk SCN 21,888 0.0
>0 0
>redo writing 18,665 0.0
>0 0
>row cache enqueue latch 170,614 0.0
>0 0
>row cache objects 172,537 0.0
>0 0
>sequence cache 14 0.0
>0 0
>session allocation 19,788 0.0
>0 0
>session idle bit 70,510 0.0
>0 0
>session switching 57 0.0
>0 0
>session timer 1,207 0.0
>0 0
>shared pool 33,025 0.0 0.0
>0 0
>Latch Activity for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>->"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
>------------------------ -------------- ------ ------ ------
>------------ ------
>simulator hash latch 266,083 0.0
>0 0
>simulator lru latch 23,153 0.0 0
>59,048 0.0
>sort extent pool 8,983 0.0
>0 0
>transaction allocation 1,968,328 0.0
>0 0
>transaction branch alloc 57 0.0
>0 0
>undo global data 97,384 0.0
>0 0
>user lock 26 0.0
>0 0
>
>-------------------------------------------------------------
>Latch Sleep breakdown for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>-> ordered by misses desc
>
> Get
>Spin &
>Latch Name Requests Misses Sleeps
>Sleeps 1->4
>-------------------------- -------------- ----------- -----------
>------------
>library cache 95,705 4 1
>3/1/0/0/0
>
>-------------------------------------------------------------
>Latch Miss Sources for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>-> only latches with sleeps are shown
>-> ordered by name, sleeps desc
>
>
>NoWait Waiter
>Latch Name Where Misses
>Sleeps Sleeps
>------------------------ -------------------------- ------- ----------
>--------
>library cache kglic 0
>1 0
>
>-------------------------------------------------------------
>Dictionary Cache Stats for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>->"Pct Misses" should be very low (< 2% in most cases)
>->"Cache Usage" is the number of cache entries being used
>->"Pct SGA" is the ratio of usage to allocated size for that cache
>
> Get Pct Scan Pct
>Mod Final
>Cache Requests Miss Reqs Miss
>Reqs Usage
>------------------------- ------------ ------ ------- ----- --------
>----------
>dc_histogram_defs 789 33.5 0
>364 24,146
>dc_object_ids 51 3.9 0
>0 2,166
>dc_objects 40 10.0 0
>0 3,916
>dc_profiles 13 0.0 0
>0 1
>dc_rollback_segments 79,362 0.0 0
>0 35
>dc_segments 65 6.2 0
>2 5,495
>dc_sequences 1 0.0 0
>1 16
>dc_tablespace_quotas 2 0.0 0
>2 11
>dc_tablespaces 3,599 0.0 0
>0 17
>dc_user_grants 126 0.0 0
>0 47
>dc_usernames 37 0.0 0
>0 24
>dc_users 1,414 0.0 0
>0 83
>
>-------------------------------------------------------------
>
>
>Library Cache Activity for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>->"Pct Misses" should be very low
>
> Get Pct Pin Pct
>Invali-
>Namespace Requests Miss Requests Miss Reloads
>dations
>--------------- ------------ ------ -------------- ------ ----------
>--------
>BODY 48 0.0 48 0.0
>0 0
>INDEX 30 20.0 36 33.3
>0 0
>SQL AREA 3,954 0.3 8,485 0.3
>7 16
>TABLE/PROCEDURE 105 1.0 6,662 0.2
>1 0
>
>-------------------------------------------------------------
>Shared Pool Advisory for DB: PRODDWDB Instance: proddwdb End Snap:
>4376
>-> 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
>----------- ----- ---------- ------------ ------------ -------
>---------------
> 176 .6 167 29,403 331,925 1.0
>75,982,422
> 208 .7 198 40,288 331,927 1.0
>75,983,729
> 240 .8 229 56,494 331,930 1.0
>75,984,728
> 272 .9 260 67,338 331,932 1.0
>75,985,300
> 304 1.0 291 76,297 331,933 1.0
>75,985,686
> 336 1.1 322 89,605 331,933 1.0
>75,985,917
> 368 1.2 353 103,760 331,933 1.0
>75,986,156
> 400 1.3 384 117,292 331,933 1.0
>75,986,373
> 432 1.4 415 127,055 331,933 1.0
>75,986,623
> 464 1.5 446 139,572 331,934 1.0
>75,987,005
> 496 1.6 477 156,231 331,935 1.0
>75,987,445
> 528 1.7 508 168,382 331,936 1.0
>75,988,416
> 560 1.8 541 177,833 331,937 1.0
>75,989,535
> 592 1.9 572 180,336 331,939 1.0
>75,990,400
> 624 2.1 604 183,385 331,940 1.0
>75,991,004
>
>-------------------------------------------------------------
>SGA Memory Summary for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>
>SGA regions Size in Bytes
>------------------------------ ----------------
>Database Buffers 536,870,912
>Fixed Size 737,472
>Redo Buffers 105,132,032
>Variable Size 385,875,968
> ----------------
>sum 1,028,616,384
>
>-------------------------------------------------------------
>
>
>SGA breakdown difference for DB: PRODDWDB Instance: proddwdb Snaps:
>4375 -4376
>
>Pool Name Begin value End
>value % Diff
>------ ------------------------------ ----------------
>---------------- -------
>java free memory 28,073,984
>28,073,984 0.00
>java memory in use 5,480,448
>5,480,448 0.00
>shared 1M buffer 2,099,200
>2,099,200 0.00
>shared Checkpoint queue 4,101,120
>4,101,120 0.00
>shared FileIdentificatonBlock 699,400
>699,400 0.00
>shared FileOpenBlock 3,126,248
>3,126,248 0.00
>shared KGK heap 13,400
>13,400 0.00
>shared KGLS heap 8,652,816
>8,519,488 -1.54
>shared KQR L PO 6,256,616
>6,260,712 0.07
>shared KQR L SO 121,288
>121,288 0.00
>shared KQR M PO 15,323,088
>15,460,400 0.90
>shared KQR M SO 89,568
>89,568 0.00
>shared KQR S SO 12,112
>12,112 0.00
>shared KQR X PO 20,608
>20,608 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 PL/SQL DIANA 6,586,984
>6,586,984 0.00
>shared PL/SQL MPCODE 5,316,440
>5,316,440 0.00
>shared PL/SQL PPCODE 197,624
>197,624 0.00
>shared PLS non-lib hp 2,832
>2,832 0.00
>shared PX subheap 122,936
>122,936 0.00
>shared XDB Schema Cac 6,797,040
>6,797,040 0.00
>shared db_block_hash_buckets 2,376,880
>2,376,880 0.00
>shared dictionary cache 4,274,432
>4,274,432 0.00
>shared enqueue 478,488
>478,488 0.00
>shared enqueue resources 391,504
>391,504 0.00
>shared errors 97,992
>97,992 0.00
>shared event statistics per sess 2,552,400
>2,552,400 0.00
>shared fixed allocation callback 880
>880 0.00
>shared free memory 43,199,336
>44,339,624 2.64
>shared joxlod: in ehe 347,728
>347,728 0.00
>shared joxs heap init 4,240
>4,240 0.00
>shared ksm_file2sga region 370,496
>370,496 0.00
>shared library cache 76,122,576
>75,708,824 -0.54
>shared log_buffer 1,642,528
>1,642,528 0.00
>shared message pool freequeue 558,720
>558,720 0.00
>shared miscellaneous 35,334,200
>35,389,816 0.16
>shared parameters 83,400
>83,400 0.00
>shared pl/sql source 3,192
>3,192 0.00
>shared sessions 302,848
>302,848 0.00
>shared sim memory hea 255,816
>255,816 0.00
>shared sql area 122,346,000
>121,555,880 -0.65
>shared subheap 51,720
>51,720 0.00
>shared table definiti 18,808
>18,696 -0.60
>shared temporary tabl 4,224
>4,224 0.00
>shared trigger defini 5,448
>5,448 0.00
>shared trigger inform 2,560
>2,560 0.00
>shared trigger source 2,208
>2,208 0.00
>shared type object de 65,640
>65,640 0.00
> buffer_cache 536,870,912
>536,870,912 0.00
> fixed_sga 737,472
>737,472 0.00
> log_buffer 105,121,792
>105,121,792 0.00
>
>-------------------------------------------------------------
>Resource Limit Stats for DB: PRODDWDB Instance: proddwdb End Snap:
>4376
>-> 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 1 16
>17 17
>
>-------------------------------------------------------------
>init.ora Parameters for DB: PRODDWDB Instance: proddwdb Snaps: 4375
>-4376
>
> End
>value
>Parameter Name Begin value (if
>different)
>----------------------------- ---------------------------------
>--------------
>_column_elimination_off TRUE
>background_dump_dest /prodofsa/admin/bdump
>compatible 9.2.0
>control_files /prodofsa/oradata/control/ora_con
>core_dump_dest /prodofsa/admin/cdump
>cursor_space_for_time TRUE
>db_block_size 8192
>db_cache_size 536870912
>db_domain bankofindia.com
>db_file_multiblock_read_count 8
>db_files 400
>db_name proddwdb
>dml_locks 500
>enqueue_resources 3000
>global_names FALSE
>job_queue_processes 2
>log_archive_dest /prodofsaarch/archive
>log_buffer 104857600
>log_checkpoint_interval 0
>log_checkpoint_timeout 0
>max_dump_file_size 10240
>max_enabled_roles 100
>open_cursors 500
>parallel_max_servers 16
>parallel_min_servers 1
>processes 200
>remote_login_passwordfile SHARED
>shared_pool_reserved_size 31457280
>shared_pool_size 318767104
>undo_management AUTO
>user_dump_dest /prodofsa/admin/udump
>
>-------------------------------------------------------------
>
>End of Report
Received on Thu Apr 05 2007 - 04:13:02 CDT