| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Any Comments - StatsPack Report
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
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
Event Waits Time (s)Ela Time
db file sequential read 231,62497 42.67
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.65Wait 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
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 usedby
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 usedby
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
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
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 usedby
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
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) =
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
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
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
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.
% Total
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
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
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
10 10 0.25 3240259259 SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :b1 FOR U
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
Statistic Total per Secondper 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
Statistic Total per Secondper 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
Statistic Total per Secondper 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.0Tablespace IO Stats for DB: PRODDWDB Instance: proddwdb Snaps: 4375 -4376
-------------------------------------------------------------
Tablespace
Av Av Av Av Buffer
Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits
Wt(ms)
0 0 0.0 195 0
0 0.0
0 0 0.0 26 0
0 0.0
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
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 TimeoutInterval
(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
--- ------------ ----- ---------------- -------------335,907,001
------------------
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
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
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 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
Pct Avg
Wait Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Pct Avg
Wait Pct
Get Get Slps Time
NoWait NoWait
Latch Requests Miss /Miss (s)
Requests Miss
Get
Spin &
Latch Name Requests Misses Sleeps
Sleeps 1->4
-------------------------- -------------- ----------- -----------
------------
library cache 95,705 4 1
3/1/0/0/0
-4376 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where MissesSleeps Sleeps
library cache kglic 0 1 0Dictionary 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 0Shared Pool Advisory for DB: PRODDWDB Instance: proddwdb End Snap: 4376
-------------------------------------------------------------
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 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 Endvalue % Diff
------ ------------------------------ ----------------105,121,792 0.00
---------------- -------
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
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 - 00:51:14 CDT
 |
 |