| 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 -> statspack comment is needed
Dear Gurus ,
My system is linux redhat 2.1 , ia64 (4 cpus) , 8 gb.ram , emc disk
array , oracle 9.2.0.5 rac with 2 nodes , datawarehouse , >1tb data. ,
1 iochannel per machine , ocfs (so direct io is in use automatically ,
no os cache is used ) ,
1.5gb total shared memory , 600mb.shared pool , 500mb.buffer cache ,
partitoned tables , parallel queries .
I suspect that the server is io bound since cpus are idle , and disks are utilized . In order to prove it , i get some statspack reports during our etl process which loads 1.5gb data everyday into datawarehouse .
Since this is a datawarehouse , i expect to see high cpu rates (tinking of in memory sorts , hash etc) but what i get is completely opposite .
The report contains 15min. interval belonging to a random part of etl process (it takes 8 hours to complete the etl process)
I have added my comments but i am not an expert about the statspack output, any comments will be appreciated .
Kind Regards,
tolga
This is the first node.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster
Host
------------ ----------- ------------ -------- ----------- -------
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 6 02-Sep-04 08:04:08 ####### 1.0
End Snap: 7 02-Sep-04 08:16:51 ####### 1.0
Elapsed: 12.72 (mins)
Cache Sizes (end)
Buffer Cache: 512M Std Block Size: 16K
Shared Pool Size: 656M Log Buffer: 2,048K
Load Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 5,774.36
9,394.11
Logical reads: 9,865.51
16,049.86
Block changes: 10.05
16.35
Physical reads: 145.31
236.41
Physical writes: 0.72
1.17
User calls: 1.93
3.14
Parses: 9.31
15.14
Hard parses: 0.10
0.17
Sorts: 8.27
13.45
Logons: 0.10
0.17
Executes: 10.84
17.63
Transactions: 0.61
% Blocks changed per Read: 0.10 Recursive Call %: 95.61 Rollback per transaction %: 0.21 Rows per Sort: 476.63
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.55 In-memory Sort %: 100.00
Library Hit %: 98.47 Soft Parse %: 98.87
Execute to Parse %: 14.15 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 17.49 % Non-Parse CPU: 99.46
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 100.00 100.00
% SQL with executions>1: 12.76 12.82
% Memory for SQL w/exec>1: 12.06 12.43
Top 5 Timed Events
Event Waits Time (s)Ela Time
db file sequential read 88,566 548
66.33
CPU time 137
16.64
PX Deq: Execute Reply 41,760 52
6.35
db file scattered read 1,587 30
3.67
latch free 481 19
2.28
-------------------------------------------------------------
Cluster Statistics for DB: TDWH Instance: TDWH1 Snaps: 6 -7
Global Cache Service - Workload Characteristics
Ave global cache get time (ms): 0.2 Ave global cache convert time (ms): 0.4 Ave build time for CR block (ms): 0.0 Ave flush time for CR block (ms): 0.0 Ave send time for CR block (ms): 0.1 Ave time to process CR block request (ms): 0.1 Ave receive time for CR block (ms): 1.1 Ave pin time for current block (ms): 1.3 Ave flush time for current block (ms): 0.4 Ave send time for current block (ms): 2.2 Ave time to process current block request (ms): 3.9 Ave receive time for current block (ms): 2.0 Global cache hit ratio: 1.5 Ratio of current block defers: 0.0 % of messages sent for buffer gets: 1.4 % of remote buffer gets: 0.0 Ratio of I/O for coherence: 1.0 Ratio of local vs remote work: 90.5 Ratio of fusion vs physical writes: 0.1
Global Enqueue Service Statistics
Ave global lock get time (ms): 2.2 Ave global lock convert time (ms): 0.0 Ratio of global lock gets vs global lock releases: 1.1
GCS and GES Messaging statistics
Ave message sent queue time (ms): 0.0
Ave message sent queue time on ksxp (ms): 0.2
Ave message received queue time (ms): 0.0
Ave GCS message process time (ms): 0.1
Ave GES message process time (ms): 0.0
% of direct sent messages: 42.9
% of indirect sent messages: 48.1
% of flow controlled messages: 9.0
-------------------------------------------------------------
GES Statistics for DB: TDWH Instance: TDWH1 Snaps: 6 -7
Statistic Total per Second perTrans
dynamically allocated gcs resourc 0 0.0 0.0 dynamically allocated gcs shadows 0 0.0 0.0 flow control messages received 1 0.0 0.0 flow control messages sent 0 0.0 0.0 gcs ast xid 0 0.0 0.0 gcs blocked converts 234 0.3 0.5 gcs blocked cr converts 937 1.2 2.0 gcs compatible basts 0 0.0 0.0 gcs compatible cr basts (global) 67 0.1 0.1 gcs compatible cr basts (local) 67 0.1 0.1 gcs cr basts to PIs 0 0.0 0.0 gcs cr serve without current lock 0 0.0 0.0 gcs error msgs 0 0.0 0.0 gcs flush pi msgs 36 0.0 0.1 gcs forward cr to pinged instance 0 0.0 0.0 gcs immediate (compatible) conver 362 0.5 0.8 gcs immediate (null) converts 1,424 1.9 3.0 gcs immediate cr (compatible) con 390 0.5 0.8 gcs immediate cr (null) converts 56,294 73.8 120.0 gcs msgs process time(ms) 573 0.8 1.2 gcs msgs received 4,435 5.8 9.5 gcs out-of-order msgs 0 0.0 0.0 gcs pings refused 0 0.0 0.0 gcs queued converts 1 0.0 0.0 gcs recovery claim msgs 0 0.0 0.0 gcs refuse xid 0 0.0 0.0 gcs retry convert request 0 0.0 0.0 gcs side channel msgs actual 546 0.7 1.2 gcs side channel msgs logical 53,908 70.7 114.9 gcs write notification msgs 1 0.0 0.0 gcs write request msgs 98 0.1 0.2 gcs writes refused 3 0.0 0.0 ges msgs process time(ms) 57 0.1 0.1 ges msgs received 3,857 5.1 8.2 global posts dropped 0 0.0 0.0 global posts queue time 0 0.0 0.0 global posts queued 0 0.0 0.0 global posts requested 0 0.0 0.0 global posts sent 0 0.0 0.0 implicit batch messages received 143 0.2 0.3 implicit batch messages sent 11,917 15.6 25.4 lmd msg send time(ms) 20 0.0 0.0 lms(s) msg send time(ms) 3 0.0 0.0 messages flow controlled 10,095 13.2 21.5 messages received actual 7,184 9.4 15.3 messages received logical 8,292 10.9 17.7 messages sent directly 48,182 63.1 102.7 messages sent indirectly 53,983 70.8 115.1 msgs causing lmd to send msgs 1,933 2.5 4.1 msgs causing lms(s) to send msgs 856 1.1 1.8 msgs received queue time (ms) 162 0.2 0.3 msgs received queued 8,292 10.9 17.7 msgs sent queue time (ms) 2,476 3.2 5.3 msgs sent queue time on ksxp (ms) 11,320 14.8 24.1 msgs sent queued 54,530 71.5 116.3 msgs sent queued on ksxp 65,873 86.3140.5
Statistic Total per Second perTrans
process batch messages received 118 0.2
0.3
process batch messages sent 2,479 3.2
5.3
-------------------------------------------------------------
Wait Events for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> 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
---------------------------- ------------ ---------- ---------- ------
188.8
PX Deq: Execute Reply 41,760 20,397 52 1
89.0
db file scattered read 1,587 0 30 19
3.4
latch free 481 94 19 39
1.0
global cache cr request 65,574 11,896 11 0
139.8
IPC send completion sync 35,713 27,853 7 0
76.1
PX Deq Credit: send blkd 45 2 5 102
0.1
PX Deq Credit: need buffer 1,504 0 4 3
3.2
DFS lock handle 657 4 3 4
1.4
KJC: Wait for msg sends to c 30 24 1 42
0.1
buffer busy global cache 17 0 1 72
0.0
PX Deq: reap credit 175,118 140,623 1 0
373.4
log file sync 156 0 1 6
0.3
PX Deq: Parse Reply 30 5 1 24
0.1
log file parallel write 422 0 1 2
0.9
control file sequential read 1,187 0 1 1
2.5
control file parallel write 248 0 1 2
0.5
buffer busy waits 105 0 1 5
0.2
enqueue 457 0 0 1
1.0
global cache open x 121 0 0 3
0.3
library cache pin 112 0 0 2
0.2
name-service call wait 2 0 0 73
0.0
global cache null to x 6 0 0 22
0.0
db file parallel read 8 0 0 15
0.0
PX Deq: Signal ACK 16 8 0 6
0.0
global cache open s 72 0 0 1
0.2
global cache s to x 275 0 0 0
0.6
PX qref latch 5 5 0 11
0.0
buffer busy global CR 18 0 0 3
0.0
row cache lock 104 0 0 0
0.2
CGS wait for IPC msg 7,536 7,536 0 0
16.1
library cache lock 271 0 0 0
0.6
PX Deq: Join ACK 37 8 0 0
0.1
ksxr poll remote instances 969 704 0 0
2.1
LGWR wait for redo copy 26 0 0 0
0.1
SQL*Net more data to client 51 0 0 0
0.1
global cache null to s 2 0 0 0
0.0
direct path read 137 0 0 0
0.3
PX Deq: Table Q qref 2 2 0 0
0.0
direct path write 4 0 0 0
0.0
buffer deadlock 1 1 0 0
0.0
PX Idle Wait 9,300 9,307 22,713 2442
19.8
jobq slave wait 2,017 1,825 5,850 2900
4.3
gcs remote message 21,665 21,580 1,485 69
46.2
queue messages 229 222 1,477 6451
0.5
SQL*Net message from client 1,160 0 1,097 946
2.5
ges remote message 21,564 21,418 743 34
46.0
wakeup time manager 24 24 699 29123
0.1
PX Deq: Execution Msg 639 213 535 838
1.4
-> 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
---------------------------- ------------ ---------- ---------- ------
35.3
SQL*Net more data from clien 1 0 0 4
0.0
SQL*Net message to client 1,160 0 0 0
2.5
-------------------------------------------------------------
Background Wait Events for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> 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 422 0 1 2
0.9
control file parallel write 248 0 1 2
0.5
control file sequential read 1,071 0 1 1
2.3
db file scattered read 38 0 0 11
0.1
latch free 29 8 0 2
0.1
CGS wait for IPC msg 7,535 7,535 0 0
16.1
DFS lock handle 363 0 0 0
0.8
db file sequential read 3 0 0 7
0.0
global cache cr request 18 0 0 1
0.0
ksxr poll remote instances 969 706 0 0
2.1
enqueue 16 0 0 0
0.0
LGWR wait for redo copy 26 0 0 0
0.1
rdbms ipc message 9,720 8,559 5,001 514
20.7
gcs remote message 21,666 21,581 1,485 69
46.2
ges remote message 21,561 21,415 743 34
46.0
pmon timer 271 266 742 2738
0.6
smon timer 4 2 384 96096
0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> End Buffer Gets Threshold: 10000
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
4,511,928 1 4,511,928.0 59.9 50.67 685.71
1530408297
Module: OWB Runtime Service - Component
declare l_env wb_rt_mapaudit.wb_rt_name_values;function bool_to
_char (val boolean) return varchar2 is begin if (val is null) th
en return null; else if (val) then return 'true'; else return 'f
alse'; end if; end if; end; begin l_env(1).param_name := 'AUDIT_
ID'; l_env(1).param_value := 986271; l_env(2).param_name := 'OPE
4,169,132 1 4,169,132.0 55.4 65.88 240.30
34177161
Module: OWB Runtime Service - Component
declare l_env wb_rt_mapaudit.wb_rt_name_values;function bool_to
_char (val boolean) return varchar2 is begin if (val is null) th
en return null; else if (val) then return 'true'; else return 'f
alse'; end if; end if; end; begin l_env(1).param_name := 'AUDIT_
ID'; l_env(1).param_value := 986279; l_env(2).param_name := 'OPE
4,015,222 1 4,015,222.0 53.3 61.94 69.04
4067229457
Module: OWB Runtime Service - Component
UPDATE /*+ APPEND NOPARALLEL (KAMPANYA_DETAY_FAYDA) */ "KAMPANY
A_DETAY_FAYDA" SET "DURUM" = :B3, "IPTAL_TARIHI" = :B2, "IPTAL_T
IPI" = :B1 WHERE "DWH_TRX_NO" = :B5 AND "BAR_KOD" = :B4 RETURNIN
G ROWID INTO :B0
209,489 4 52,372.3 2.8 1.19 1.24
3866859974
Module: OWB Runtime Service - Component
SELECT PARTITIONING_TYPE , SUBPARTITIONING_TYPE , INDEX_NAME FRO
M ALL_PART_INDEXES WHERE OWNER = :B2 AND TABLE_NAME = UPPER(:B1)
121,297 1 121,297.0 1.6 4.42 48.83
2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
116,073 1 116,073.0 1.5 1.53 1.82
1116368370
Module: SQL*Plus
INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S
QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT ST1.HAS
H_VALUE , SS.TEXT_SUBSET , ST1.PIECE , ST1.SQL_TEXT , ST1.ADDRES
S , ST1.COMMAND_TYPE , SS.SNAP_ID FROM V$SQLTEXT ST1 , STATS$SQL
_SUMMARY SS WHERE SS.SNAP_ID = :B3 AND SS.DBID = :B2 AND SS.INST
103,542 2 51,771.0 1.4 7.99 8.07
3563455573
Module: OWB Runtime Service - Component
SELECT STATUS FROM ALL_IND_PARTITIONS WHERE INDEX_NAME = :B3 AND
INDEX_OWNER = :B2 AND PARTITION_NAME = 'P' || :B1
86,499 3 28,833.0 1.1 8.77 509.82
4287266822
Module: OWB Runtime Service - Component
SELECT TARIH FROM TETTGENETARIHKONTROL WHERE UPPER(OZETADI) = UP
PER(:B1)
SQL ordered by Gets for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> 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
14,267 134 106.5 0.2 0.17 0.17
567598861
SELECT PROC_ID, PREVIOUS_PROC_IDS, PROC_PARAM, PROC_NAME, PROC_T
YPE FROM SCH_PLAN_JOBS_V2 WHERE PLAN_NAME = :B2 AND ETT_DATE = :
B1 AND STATUS = 'READY' AND NVL(START_AFTER,'0000') <= TO_CHAR(S
YSDATE,'HH24MI')
12,381 160 77.4 0.2 0.07 2.86
1053795750
COMMIT
11,080 20 554.0 0.1 4.26 4.95
1026229520
Module: JDBC Thin Client
SELECT DISTINCT SUBSTR(proc_type || ' - ' || proc_name, 1, 12) p
roc_prefix FROM sch_plan_jobs_v2 ORDER BY 1 ASC
8,499 22 386.3 0.1 0.24 0.25
1022128830
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN TANIDWH.p_sch_V2.par_exec_thread(
'DWH', to_date('02092004','ddmmyyyy') ,5 ); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
8,497 22 386.2 0.1 0.24 0.33
4020642181
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN TANIDWH.p_sch_V2.par_exec_thread(
'DWH', to_date('02092004','ddmmyyyy') ,1 ); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
8,496 22 386.2 0.1 0.24 0.36
3987596234
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN TANIDWH.p_sch_V2.par_exec_thread(
'DWH', to_date('02092004','ddmmyyyy') ,4 ); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
8,495 22 386.1 0.1 0.25 0.33
2166135725
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN TANIDWH.p_sch_V2.par_exec_thread(
'DWH', to_date('02092004','ddmmyyyy') ,2 ); :mydate := next_date
; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
6,816 3 2,272.0 0.1 0.65 7.34
3572335610
Module: JDBC Thin Client
SELECT COUNT(*) exec_count, AVG(elapse) exec_avg_time FROM tanid
whrow.wb_rt_audit_executions WHERE execution_object_name = 'METT
KAMPDETAYIPTAL10' AND return_result = 'OK'
6,481 3 2,160.3 0.1 0.15 0.18
1638965994
select obj#, dataobj#, part#, hiboundlen, hiboundval, ts#, file#
, block#, pctfree$, pctused$, initrans, maxtrans, flags, analyze
time, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln
, length(bhiboundval), bhiboundval from tabpart$ where bo# = :1
SQL ordered by Gets for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> 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
5,185 13 398.8 0.1 0.03 0.30
2900533548
select /*+ FIRST_ROWS */ tab.rowid, tab.msgid, tab.corrid, tab.
priority, tab.delay, tab.expiration, tab.retry_count, tab.exce
ption_qschema, tab.exception_queue, tab.chain_no, tab.local_or
der_no, tab.enq_time, tab.time_manager_info, tab.state, tab.en
q_tid, tab.step_no, tab.sender_name, tab.sender_address, tab.s
5,080 20 254.0 0.1 6.98 9.73
140271534
Module: JDBC Thin Client
SELECT DISTINCT plan_name || ' (' || TO_CHAR(ett_date, 'RRRR-MM-
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
118,861 1 118,861.0 107.2 50.67 685.71
1530408297
Module: OWB Runtime Service - Component
declare l_env wb_rt_mapaudit.wb_rt_name_values;function bool_to
_char (val boolean) return varchar2 is begin if (val is null) th
en return null; else if (val) then return 'true'; else return 'f
alse'; end if; end if; end; begin l_env(1).param_name := 'AUDIT_
ID'; l_env(1).param_value := 986271; l_env(2).param_name := 'OPE
64,446 3 21,482.0 58.1 8.77 509.82
4287266822
Module: OWB Runtime Service - Component
SELECT TARIH FROM TETTGENETARIHKONTROL WHERE UPPER(OZETADI) = UP
PER(:B1)
33,080 1 33,080.0 29.8 65.88 240.30
34177161
Module: OWB Runtime Service - Component
declare l_env wb_rt_mapaudit.wb_rt_name_values;function bool_to
_char (val boolean) return varchar2 is begin if (val is null) th
en return null; else if (val) then return 'true'; else return 'f
alse'; end if; end if; end; begin l_env(1).param_name := 'AUDIT_
ID'; l_env(1).param_value := 986279; l_env(2).param_name := 'OPE
6,794 3 2,264.7 6.1 0.65 7.34
3572335610
Module: JDBC Thin Client
SELECT COUNT(*) exec_count, AVG(elapse) exec_avg_time FROM tanid
whrow.wb_rt_audit_executions WHERE execution_object_name = 'METT
KAMPDETAYIPTAL10' AND return_result = 'OK'
4,182 1 4,182.0 3.8 61.94 69.04
4067229457
Module: OWB Runtime Service - Component
UPDATE /*+ APPEND NOPARALLEL (KAMPANYA_DETAY_FAYDA) */ "KAMPANY
A_DETAY_FAYDA" SET "DURUM" = :B3, "IPTAL_TARIHI" = :B2, "IPTAL_T
IPI" = :B1 WHERE "DWH_TRX_NO" = :B5 AND "BAR_KOD" = :B4 RETURNIN
G ROWID INTO :B0
3,939 160 24.6 3.6 0.07 2.86
1053795750
COMMIT
2,264 1 2,264.0 2.0 0.24 3.49
148134166
Module: JDBC Thin Client
SELECT COUNT(*) exec_count, AVG(elapse) exec_avg_time FROM tanid
whrow.wb_rt_audit_executions WHERE execution_object_name = 'METT
KAMPDETAYIPTAL20' AND return_result = 'OK'
2,128 4 532.0 1.9 1.17 3.60
2267657827
Module: JDBC Thin Client
SELECT TO_CHAR(MIN(start_date), 'RRRR-MM-DD HH24:MI:SS') min_dat
e, TO_CHAR(MAX(end_date), 'RRRR-MM-DD HH24:MI:SS') max_date FROM
sch_plan_jobs_v2 j WHERE plan_name || ' (' || TO_CHAR(ett_date,
'RRRR-MM-DD') || ')' = 'DWH (2004-09-02)'
1,737 20 86.9 1.6 6.98 9.73
140271534
Module: JDBC Thin Client
SELECT DISTINCT plan_name || ' (' || TO_CHAR(ett_date, 'RRRR-MM-
DD') || ')' plan_ett FROM sch_plan_jobs_v2 WHERE plan_name IS NO
SQL ordered by Reads for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
1,090 20 54.5 1.0 0.15 2.92
2627517935
Module: JDBC Thin Client
SELECT DISTINCT plan_name FROM sch_plan_template_v2 WHERE plan_n
ame IS NOT NULL ORDER BY 1 ASC
535 20 26.8 0.5 4.26 4.95
1026229520
Module: JDBC Thin Client
SELECT DISTINCT SUBSTR(proc_type || ' - ' || proc_name, 1, 12) p
roc_prefix FROM sch_plan_jobs_v2 ORDER BY 1 ASC
386 1 386.0 0.3 4.42 48.83
2522684317
Module: SQL*Plus
BEGIN statspack.snap; END;
246 2 123.0 0.2 0.02 0.46
2095543314
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
160 2 80.0 0.1 0.03 0.36
2352229386
SELECT SP.PARAMETER_NAME,SP.PARAMETER_TYPE,SP.PARAMETER_MODE,SP.
IS_REQUIRED,SP.IS_FIXED,SP.DEFAULT_VALUE SYSTEM_DEFAULT,PD.VALUE
_KIND,PD.VALUE TASK_DEFAULT FROM WB_RT_DEF_SYSTEM_PARAMETERS S
P,WB_RT_TASK_PARAMETER_DEFAULTS PD WHERE PD.SYSTEM_PARAMETER_ID
(+) = SP.SYSTEM_PARAMETER_ID AND PD.TASK_ID (+) = :b1 AND
134 2 67.0 0.1 0.02 0.38
1992331201
SELECT T.TASK_ID FROM WB_RT_TASKS T,WB_RT_WAREHOUSE_OBJECTS WO
,WB_RT_DEF_OBJECT_TYPES OT,WB_RT_DEF_EXECUTION_OPERATORS EO,WB_R
T_STORES S,WB_RT_STORES ES WHERE T.WAREHOUSE_OBJECT_ID = WO.WAR
EHOUSE_OBJECT_ID AND T.EXECUTION_OBJECT_UOID = WO.OBJECT_UOID
AND WO.OBJECT_TYPE_ID = OT.OBJECT_TYPE_ID AND WO.STORE_ID = S.S
128 8 16.0 0.1 0.02 0.24
124369507
124 2 62.0 0.1 0.01 0.19
3909547062
Module: OWB Runtime Service - Infrastructure
BEGIN wb_rt_execution_feedback.task_end( :1 ) ; END;
119 2 59.5 0.1 7.99 8.07
3563455573
Module: OWB Runtime Service - Component
SELECT STATUS FROM ALL_IND_PARTITIONS WHERE INDEX_NAME = :B3 AND
INDEX_OWNER = :B2 AND PARTITION_NAME = 'P' || :B1
108 1 108.0 0.1 1.53 1.82
1116368370
Module: SQL*Plus
INSERT INTO STATS$SQLTEXT ( HASH_VALUE , TEXT_SUBSET , PIECE , S
QL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) SELECT ST1.HAS
H_VALUE , SS.TEXT_SUBSET , ST1.PIECE , ST1.SQL_TEXT , ST1.ADDRES
SQL ordered by Reads for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s)
Hash Value
37 2 18.5 0.0 0.02 0.32
2143952137
Module: OWB Runtime Service - Infrastructure
BEGIN wb_rt_service_control.execute_done( :1 )
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
450 450 1.0 0.00 0.00 2698028815
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (261030)
450 450 1.0 0.00 0.00 3524521430
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2610
30)
432 4 0.0 0.00 0.00
3131258615
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
360 360 1.0 0.00 0.00 1541305590
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (251000)
360 360 1.0 0.00 0.00 3062474024
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2510
00)
178 178 1.0 0.00 0.00 3047008320
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (241070)
178 178 1.0 0.00 0.00 3800875436
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2410
70)
160 0 0.0 0.00 0.02
1053795750
COMMIT
154 154 1.0 0.00 0.00
4159180554
update tabpart$ set spare1 = :1 where obj# = :2 and bo# = :3
144 144 1.0 0.00 4.88
1182811714
Module: OWB Runtime Service - Infrastructure
BEGIN wb_rt_service_control.wait( :1 , :2 , :
3 ) ; END;
SQL ordered by Executions for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> End Executions Threshold: 100
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s)
Hash Value
144 144 1.0 0.00 0.00
1817891629
SELECT user from sys.dual
142 4,045 28.5 0.00 0.00
1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
138 128 0.9 0.00 0.00
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
134 134 1.0 0.00 0.00
297937389
update sys.job$ set this_date=:1 where job=:2
134 2,122 15.8 0.00 0.00
567598861
SELECT PROC_ID, PREVIOUS_PROC_IDS, PROC_PARAM, PROC_NAME, PROC_T
YPE FROM SCH_PLAN_JOBS_V2 WHERE PLAN_NAME = :B2 AND ETT_DATE = :
B1 AND STATUS = 'READY' AND NVL(START_AFTER,'0000') <= TO_CHAR(S
YSDATE,'HH24MI')
134 134 1.0 0.00 0.00
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
134 0 0.0 0.00 0.00
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'
134 134 1.0 0.00 0.00
4025703819
SELECT STATUS FROM SCH_PLAN_EXECUTERS_V2 WHERE PLAN_NAME = :B3 A
ND ETT_DATE = :B2 AND PARALLEL_GROUP = :B1
134 134 1.0 0.00 0.00
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
132 132 1.0 0.00 0.00
1505447700
UPDATE SCH_PLAN_EXECUTERS_V2 SET STATUS = DECODE(STATUS,'BLOCKED
','BLOCKED','IDLE') , LAST_DATE = SYSDATE , EXECUTING = NULL , S
ESSIONID = NULL , EXEC_COUNT = NVL(EXEC_COUNT,0) +1 WHERE PLAN_N
AME = :B3 AND ETT_DATE = :B2 AND PARALLEL_GROUP = :B1
% Total
450 450 6.34 2698028815
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (261030)
450 450 6.34 3524521430
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2610
30)
360 360 5.07 1541305590
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (251000)
360 360 5.07 3062474024
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2510
00)
178 178 2.51 3047008320
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (241070)
178 178 2.51 3800875436
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2410
70)
154 154 2.17 4159180554 update tabpart$ set spare1 = :1 where obj# = :2 and bo# = :3
143 160 2.01 1053795750 COMMIT 142 142 2.00 1356713530 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with (grantee#=:1 or grantee#= 1) and privilege#>0
136 144 1.92 1817891629 SELECT user from sys.dual
134 134 1.89 297937389 update sys.job$ set this_date=:1 where job=:2
134 134 1.89 2594425492 SQL ordered by Parse Calls for DB: TDWH Instance: TDWH1 Snaps: 6 -7 -> End Parse Calls Threshold: 1000
% Total
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
134 134 1.89 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' 134 134 1.89 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
132 134 1.86 567598861 SELECT PROC_ID, PREVIOUS_PROC_IDS, PROC_PARAM, PROC_NAME, PROC_T YPE FROM SCH_PLAN_JOBS_V2 WHERE PLAN_NAME = :B2 AND ETT_DATE = : B1 AND STATUS = 'READY' AND NVL(START_AFTER,'0000') <= TO_CHAR(S YSDATE,'HH24MI') 132 132 1.86 1505447700 UPDATE SCH_PLAN_EXECUTERS_V2 SET STATUS = DECODE(STATUS,'BLOCKED ','BLOCKED','IDLE') , LAST_DATE = SYSDATE , EXECUTING = NULL , S ESSIONID = NULL , EXEC_COUNT = NVL(EXEC_COUNT,0) +1 WHERE PLAN_N AME = :B3 AND ETT_DATE = :B2 AND PARALLEL_GROUP = :B1 132 132 1.86 1832767670 select SYSDATE+(1/(24*60*2)) from dual
132 134 1.86 4025703819 SELECT STATUS FROM SCH_PLAN_EXECUTERS_V2 WHERE PLAN_NAME = :B3 A ND ETT_DATE = :B2 AND PARALLEL_GROUP = :B1
90 90 1.27 34138514
SELECT COUNT(DISTINCT STATUS) FROM TANIDWH.SCH_PLAN_J
OBS_V2 WHERE PLAN_NAME = 'DWH' AND ETT_DATE
= TO_DATE('02092004','DDMMYYYY') AND PROC_ID IN (2510
00, 251010, 251020, 251030, 251040, 251050, 251060, 251070, 2510
80)
90 90 1.27 217222982
SELECT DISTINCT STATUS FROM TANIDWH.SCH_PLAN_JOB
S_V2 WHERE PLAN_NAME = 'DWH' AND E
TT_DATE = TO_DATE('02092004','DDMMYYYY') AND PRO
C_ID IN (251000, 251010, 251020, 251030, 251040, 251050, 251060,
251070, 251080)
Statistic Total per Secondper Trans
CPU used by this session 13,744 18.0
29.3
CPU used when call started 16,402 21.5
35.0
CR blocks created 81 0.1
0.2
Cached Commit SCN referenced 888,445 1,164.4
1,894.3
Commit SCN cached 13 0.0
0.0
DBWR buffers scanned 11,087 14.5
23.6
DBWR checkpoint buffers written 31 0.0
0.1
DBWR checkpoints 3 0.0
0.0
DBWR cross instance writes 0 0.0
0.0
DBWR free buffers found 10,620 13.9
22.6
DBWR fusion writes 36 0.1
0.1
DBWR lru scans 50 0.1
0.1
DBWR make free requests 63 0.1
0.1
DBWR revisited being-written buff 0 0.0
0.0
DBWR summed scan depth 11,087 14.5
23.6
DBWR transaction table writes 12 0.0
0.0
DBWR undo block writes 181 0.2
0.4
DDL statements parallelized 0 0.0
0.0
DFO trees parallelized 7 0.0
0.0
DML statements parallelized 0 0.0
0.0
PX local messages recv'd 17,199 22.5
36.7
PX local messages sent 17,199 22.5
36.7
PX remote messages recv'd 22,410 29.4
47.8
PX remote messages sent 70,876 92.9
151.1
Parallel operations downgraded to 0 0.0
0.0
Parallel operations not downgrade 7 0.0
0.0
RowCR attempts 0 0.0
0.0
RowCR hits 0 0.0
0.0
SQL*Net roundtrips to/from client 1,130 1.5
2.4
SQL*Net roundtrips to/from dblink 0 0.0
0.0
active txn count during cleanout 123 0.2
0.3
background checkpoints completed 0 0.0
0.0
background checkpoints started 0 0.0
0.0
background timeouts 1,161 1.5
2.5
branch node splits 0 0.0
0.0
buffer is not pinned count 5,740,580 7,523.7
12,240.0
buffer is pinned count 3,786,791 4,963.0
8,074.2
bytes received via SQL*Net from c 64,961 85.1
138.5
bytes received via SQL*Net from d 0 0.0
0.0
bytes sent via SQL*Net to client 220,717 289.3
470.6
bytes sent via SQL*Net to dblink 0 0.0
0.0
calls to get snapshot scn: kcmgss 1,402,321 1,837.9
2,990.0
calls to kcmgas 3,057 4.0
6.5
calls to kcmgcs 68 0.1
0.1
change write time 11 0.0
0.0
cleanout - number of ktugct calls 235 0.3
0.5
cleanouts and rollbacks - consist 10 0.0
0.0
cleanouts only - consistent read 75 0.1
0.2
cluster key scan block gets 349 0.5
0.7
cluster key scans 245 0.3
0.5
commit cleanout failures: block l 1 0.0
0.0
commit cleanout failures: buffer 0 0.0
0.0
commit cleanout failures: callbac 2 0.0
0.0
commit cleanout failures: cannot 63 0.1
0.1
commit cleanouts 1,420 1.9
3.0
commit cleanouts successfully com 1,354 1.8
2.9
Statistic Total per Secondper Trans
commit txn count during cleanout 414 0.5
0.9
consistent changes 104 0.1
0.2
consistent gets 7,522,064 9,858.5
16,038.5
consistent gets - examination 210,469 275.8
448.8
cursor authentications 22 0.0
0.1
data blocks consistent reads - un 99 0.1
0.2
db block changes 7,668 10.1
16.4
db block gets 6,772 8.9
14.4
deferred (CURRENT) block cleanout 577 0.8
1.2
dirty buffers inspected 170 0.2
0.4
enqueue conversions 199 0.3
0.4
enqueue releases 3,996 5.2
8.5
enqueue requests 3,998 5.2
8.5
enqueue timeouts 48 0.1
0.1
enqueue waits 268 0.4
0.6
exchange deadlocks 1 0.0
0.0
execute count 8,270 10.8
17.6
free buffer inspected 182 0.2
0.4
free buffer requested 111,888 146.6
238.6
gcs messages sent 109,047 142.9
232.5
ges messages sent 3,461 4.5
7.4
global cache blocks lost 0 0.0
0.0
global cache convert time 21 0.0
0.0
global cache converts 564 0.7
1.2
global cache cr block build time 3 0.0
0.0
global cache cr block flush time 1 0.0
0.0
global cache cr block receive tim 73 0.1
0.2
global cache cr block send time 7 0.0
0.0
global cache cr blocks received 660 0.9
1.4
global cache cr blocks served 1,250 1.6
2.7
global cache current block flush 8 0.0
0.0
global cache current block pin ti 24 0.0
0.1
global cache current block receiv 112 0.2
0.2
global cache current block send t 42 0.1
0.1
global cache current blocks recei 564 0.7
1.2
global cache current blocks serve 190 0.3
0.4
global cache defers 3 0.0
0.0
global cache freelist waits 0 0.0
0.0
global cache get time 2,380 3.1
5.1
global cache gets 110,261 144.5
235.1
global cache prepare failures 0 0.0
0.0
global cache skip prepare failure 221 0.3
0.5
global lock async converts 248 0.3
0.5
global lock async gets 303 0.4
0.7
global lock convert time 2 0.0
0.0
global lock get time 817 1.1
1.7
global lock releases 3,421 4.5
7.3
global lock sync converts 350 0.5
0.8
global lock sync gets 3,375 4.4
7.2
hot buffers moved to head of LRU 19,627 25.7
41.9
immediate (CR) block cleanout app 85 0.1
0.2
immediate (CURRENT) block cleanou 176 0.2
0.4
index fast full scans (direct rea 0 0.0
0.0
index fast full scans (full) 6 0.0
0.0
index fast full scans (rowid rang 0 0.0
0.0
index fetch by key 198,631 260.3
423.5
Instance Activity Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
Statistic Total per Secondper Trans
index scans kdiixs1 151,112 198.1
322.2
leaf node 90-10 splits 2 0.0
0.0
leaf node splits 42 0.1
0.1
logons cumulative 78 0.1
0.2
messages received 737 1.0
1.6
messages sent 737 1.0
1.6
no buffer to keep pinned count 0 0.0
0.0
no work - consistent read gets 5,882,146 7,709.2
12,541.9
opened cursors cumulative 7,047 9.2
15.0
parse count (failures) 6 0.0
0.0
parse count (hard) 80 0.1
0.2
parse count (total) 7,100 9.3
15.1
parse time cpu 74 0.1
0.2
parse time elapsed 423 0.6
0.9
physical reads 110,874 145.3
236.4
physical reads direct 1,471 1.9
3.1
physical reads direct (lob) 0 0.0
0.0
physical writes 549 0.7
1.2
physical writes direct 12 0.0
0.0
physical writes non checkpoint 489 0.6
1.0
pinned buffers inspected 11 0.0
0.0
prefetch clients - default 1 0.0
0.0
prefetched blocks 19,624 25.7
41.8
prefetched blocks aged out before 0 0.0
0.0
process last non-idle time 87,050,182,665 114,089,361.3
############
queries parallelized 5 0.0
0.0
recursive calls 32,024 42.0
68.3
recursive cpu usage 11,872 15.6
25.3
redo blocks written 9,322 12.2
19.9
redo buffer allocation retries 0 0.0
0.0
redo entries 4,514 5.9
9.6
redo log space requests 0 0.0
0.0
redo log space wait time 0 0.0
0.0
redo ordering marks 205 0.3
0.4
redo size 4,405,836 5,774.4
9,394.1
redo synch time 98 0.1
0.2
redo synch writes 168 0.2
0.4
redo wastage 138,324 181.3
294.9
redo write time 1 0.0
0.0
redo writer latching time 0 0.0
0.0
redo writes 422 0.6
0.9
remote instance undo block writes 0 0.0
0.0
rollback changes - undo records a 3 0.0
0.0
rollbacks only - consistent read 71 0.1
0.2
rows fetched via callback 7,504 9.8
16.0
session connect time 177,244,495,881 232,299,470.4
############
session logical reads 7,527,383 9,865.5
16,049.9
session pga memory max 2,372,075,680 3,108,880.3
5,057,730.7
session uga memory max 298,682,360 391,457.9
636,849.4
shared hash latch upgrades - no w 151,514 198.6
323.1
shared hash latch upgrades - wait 98 0.1
0.2
sorts (disk) 0 0.0
0.0
sorts (memory) 6,309 8.3
13.5
sorts (rows) 3,007,056 3,941.1
6,411.6
summed dirty queue length 624 0.8
1.3
switch current to new buffer 703 0.9
1.5
Statistic Total per Secondper Trans
table fetch by rowid 3,216,338 4,215.4
6,857.9
table fetch continued row 4,923 6.5
10.5
table scan blocks gotten 2,745,536 3,598.3
5,854.0
table scan rows gotten 88,486,298 115,971.6
188,670.1
table scans (cache partitions) 0 0.0
0.0
table scans (direct read) 137 0.2
0.3
table scans (long tables) 313 0.4
0.7
table scans (rowid ranges) 308 0.4
0.7
table scans (short tables) 442,652 580.2
943.8
transaction lock background gets 0 0.0
0.0
transaction lock foreground reque 0 0.0
0.0
transaction lock foreground wait 0 0.0
0.0
transaction rollbacks 0 0.0
0.0
transaction tables consistent rea 0 0.0
0.0
transaction tables consistent rea 0 0.0
0.0
user calls 1,471 1.9
3.1
user commits 468 0.6
1.0
user rollbacks 1 0.0
0.0
workarea executions - multipass 0 0.0
0.0
workarea executions - onepass 0 0.0
0.0
workarea executions - optimal 10,525 13.8
22.4
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: TDWH Instance: TDWH1 Snaps: 6 -7
->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)
0 0 0.0 5 0 0
0.0
-------------------------------------------------------------
File IO Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
->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)
ALISVERIS /oracle/data02/TDWH/alisveris20.dbf
746 1 4.5 1.0 0 0 0
/oracle/data03/TDWH/alisveris19.dbf
878 1 5.3 1.0 0 0 0
/oracle/data03/TDWH/alisveris21.dbf
1,643 2 4.9 1.1 0 0 0
/oracle/data03/TDWH/alisveris22.dbf
1,723 2 5.0 1.1 21 0 0
/oracle/data04/TDWH/alisveris01.dbf
558 1 3.4 1.2 1 0 0
/oracle/data04/TDWH/alisveris02.dbf
498 1 2.2 1.0 0 0 0
/oracle/data04/TDWH/alisveris03.dbf
540 1 2.2 1.0 0 0 0
/oracle/data04/TDWH/alisveris04.dbf
512 1 2.3 1.0 0 0 0
/oracle/data04/TDWH/alisveris06.dbf
1,033 1 2.7 1.0 0 0 0
/oracle/data04/TDWH/alisveris07.dbf
1,058 1 2.7 1.0 0 0 0
/oracle/data04/TDWH/alisveris08.dbf
966 1 2.8 1.0 0 0 0
/oracle/data04/TDWH/alisveris09.dbf
1,225 2 3.7 1.3 0 0 0
/oracle/data04/TDWH/alisveris10.dbf
505 1 2.1 1.0 0 0 0
/oracle/data04/TDWH/alisveris11.dbf
1,096 1 9.4 4.2 0 0 0
/oracle/data04/TDWH/alisveris13.dbf
709 1 4.0 1.3 0 0 0
/oracle/data04/TDWH/alisveris14.dbf
1,148 2 2.7 1.0 0 0 0
/oracle/data04/TDWH/alisveris16.dbf
1,343 2 3.8 1.0 0 0 0
/oracle/data04/TDWH/alisveris17.dbf
708 1 2.5 1.0 0 0 0
/oracle/data04/TDWH/alisveris18.dbf
689 1 6.1 1.3 0 0 0
/oracle/data04/TDWH/alisveris24.dbf
392 1 5.0 1.0 10 0 0
/oracle/data04/TDWH/alisveris_15.dbf
496 1 2.1 1.0 0 0 0
/oracle/data05/TDWH/alisveris12.dbf
539 1 2.8 1.1 0 0 0
/oracle/data05/TDWH/alisveris23.dbf
1,266 2 5.7 1.0 0 0 0
DIMENSION /oracle/data02/TDWH/dimension11.dbf
6,098 8 7.2 1.0 1 0 0
/oracle/data03/TDWH/dimension09.dbf
6,433 8 7.0 1.0 0 0 0
/oracle/data03/TDWH/dimension10.dbf
File IO Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
->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)
6,662 9 7.1 1.0 0 0 0
DIMENSION /oracle/data04/TDWH/dimension01.dbf
8,557 11 7.1 1.0 0 0 0
/oracle/data04/TDWH/dimension02.dbf
12,148 16 7.2 1.0 0 0 0
/oracle/data04/TDWH/dimension03.dbf
4,609 6 7.0 1.0 0 0 0
/oracle/data04/TDWH/dimension04.dbf
3,059 4 7.1 1.0 1 0 0
/oracle/data04/TDWH/dimension05.dbf
4,948 6 7.1 1.0 1 0 0
/oracle/data04/TDWH/dimension06.dbf
4,326 6 7.0 1.0 0 0 0
/oracle/data04/TDWH/dimension07.dbf
5,240 7 7.3 1.0 1 0 0
/oracle/data04/TDWH/dimension08.dbf
6,212 8 7.1 1.0 7 0 0
IDX_DIMENSIONS /oracle/data05/TDWH/idx_dimensions01.dbf
7 0 12.9 5.3 1 0 0
/oracle/data05/TDWH/idx_dimensions02.dbf
6 0 13.3 6.0 0 0 0
/oracle/data05/TDWH/idx_dimensions04.dbf
1 0 10.0 1.0 0 0 0
/oracle/data05/TDWH/idx_dimensions05.dbf
2 0 5.0 1.0 0 0 0
/oracle/data05/TDWH/idx_dimensions06.dbf
2 0 5.0 1.0 2 0 0
IDX_TUKETICI /oracle/data05/TDWH/idx_tuketici01.dbf
0 0 5 0 0
INDX2 /oracle/data01/TDWH/indx2_01.dbf
86 0 9.0 1.0 18 0 0
SYSTEM /oracle/data01/TDWH/system01.dbf
170 0 8.5 2.8 16 0 127
14.6
UNDOTBS1 /oracle/data01/TDWH/undotbs01.dbf
7 0 5.7 1.0 193 0 2
0.0
USERS /oracle/data01/TDWH/users01.dbf
1,788 2 14.4 9.6 263 0 11
0.0
Free
Write Buffer
Number of Cache Buffer Physical Physical Buffer
Complete Busy
P Buffers Hit % Gets Reads Writes Waits
Waits Waits
D 31,648 98.6 7,600,735 109,575 537 0
0 105
-------------------------------------------------------------
Instance Recovery Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7 -> 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 197 25 301 6996 7051 184320 7051
E 197 28 1147 13373 13374 184320 13374
-------------------------------------------------------------
Buffer Pool Advisory for DB: TDWH Instance: TDWH1 End Snap: 7 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate
Size for Size Buffers for Est Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical
Reads
--- ------------ ----- ---------------- -------------
------------------
D 48 .1 2,967 14.52 87,162,063 D 96 .2 5,934 2.99 17,923,075 D 144 .3 8,901 1.59 9,537,988 D 192 .4 11,868 1.08 6,453,512 D 240 .5 14,835 1.03 6,205,090 D 288 .6 17,802 1.02 6,112,550 D 336 .7 20,769 1.00 6,026,489 D 384 .8 23,736 0.98 5,908,964 D 432 .8 26,703 0.97 5,829,380 D 480 .9 29,670 0.96 5,759,744 D 512 1.0 31,648 0.95 5,719,304 D 528 1.0 32,637 0.95 5,694,226 D 576 1.1 35,604 0.94 5,643,931 D 624 1.2 38,571 0.93 5,605,435 D 672 1.3 41,538 0.93 5,579,847 D 720 1.4 44,505 0.93 5,563,653 D 768 1.5 47,472 0.92 5,545,377 D 816 1.6 50,439 0.92 5,533,994 D 864 1.7 53,406 0.92 5,527,146 D 912 1.8 56,373 0.92 5,517,059 D 960 1.9 59,340 0.92 5,500,171 -------------------------------------------------------------
Buffer wait Statistics for DB: TDWH Instance: TDWH1 Snaps: 6 -7 -> ordered by wait time desc, waits desc
Tot Wait Avg
Class Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
data block 133 2 14
file header block 5 0 0
undo block 2 0 0
-------------------------------------------------------------
PGA Aggr Target Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> B: Begin snap E: End snap (rows dentified with B or E contain
data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only
in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual +
auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto
Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual
control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 688 0
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A
Global Mem
PGA Aggr Target Histogram for DB: TDWH Instance: TDWH1 Snaps: 6 -7 -> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
16K 32K 10,336 10,336 0 0
32K 64K 37 37 0 0
64K 128K 21 21 0 0
128K 256K 4 4 0 0
256K 512K 19 19 0 0
512K 1024K 10 10 0 0
1M 2M 1 1 0 0
8M 16M 1 1 0 0
16M 32M 16 16 0 0
-------------------------------------------------------------
PGA Memory Advisory for DB: TDWH Instance: TDWH1 End Snap: 7 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd
PGA
PGA Target Size W/A MB W/A MB Read/ Cache
Overalloc
Est (MB) Factr Processed Written to Disk Hit %
Count
---------- ------- ---------------- ---------------- --------
----------
512 0.1 200,004.1 309,956.3 39.0
0
1,024 0.3 200,004.1 254,201.3 44.0
0
2,048 0.5 200,004.1 233,911.8 46.0
0
3,072 0.8 200,004.1 232,801.1 46.0
0
4,096 1.0 200,004.1 201,949.1 50.0
0
4,915 1.2 200,004.1 169,753.6 54.0
0
5,734 1.4 200,004.1 169,753.6 54.0
0
6,554 1.6 200,004.1 169,753.6 54.0
0
7,373 1.8 200,004.1 169,753.6 54.0
0
8,192 2.0 200,004.1 169,753.6 54.0
0
12,288 3.0 200,004.1 169,753.6 54.0
0
16,384 4.0 200,004.1 169,753.6 54.0
0
24,576 6.0 200,004.1 169,753.6 54.0
0
32,768 8.0 200,004.1 169,753.6 54.0
0
-------------------------------------------------------------
Enqueue activity for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> Enqueue stats gathered prior to 9i should not be compared with 9i
data
Avg Wt
Wait
Eq Requests Succ Gets Failed Gets Waits Time (ms)
Time (s)
TX 600 600 0 10 32.70
0
TM 1,190 1,190 0 172 .42
0
TS 28 28 0 18 .72
0
JQ 148 146 2 6 2.17
0
CF 1,233 1,233 0 6 1.83
0
TT 48 48 0 14 .43
0
HW 43 43 0 18 .22
0
TC 15 15 0 3 1.00
0
PS 244 200 44 9 .22
0
ST 5 5 0 5 .20
0
DL 2 2 0 2 .50
0
TA 4 4 0 3 .00
0
FB 22 22 0 2 .00
0
-------------------------------------------------------------
Rollback Segment Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
->A high value for "Pct Waits" suggests more rollback segments may be
required
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks
Extends
0 5.0 0.00 0 0 0 0
1 23.0 0.00 2,602 0 0 0
2 35.0 0.00 3,470 0 0 0
3 36.0 0.00 2,728 0 0 0
4 41.0 0.00 5,328 0 0 0
5 50.0 0.00 3,632 0 0 0
6 23.0 0.00 2,182 0 0 0
7 33.0 0.00 2,780 0 0 0
8 25.0 0.00 2,818 0 0 0
9 39.0 0.00 3,348 0 0 0
10 39.0 0.00 3,990 0 0 0
28 27.0 0.00 3,008 0 0 0
35 37.0 0.00 5,286 0 0 0
36 23.0 0.00 1,914 0 0 0
37 33.0 0.00 4,082 0 0 0
38 25.0 0.00 2,164 0 0 0
51 21.0 0.00 1,532 0 0 0
52 33.0 0.00 4,308 0 0 0
53 25.0 0.00 2,490 0 0 0
54 84.0 0.00 11,936 0 0 0
55 48.0 0.00 5,380 0 0 0
56 21.0 0.00 1,136 0 0 0
57 125.0 0.00 19,000 0 0 0
58 29.0 0.00 2,306 0 0 0
59 53.0 0.00 111,392 0 0 0
60 59.0 0.00 57,144 0 0 0
61 23.0 0.00 2,058 0 0 0
62 34.0 0.00 4,848 0 0 0
63 25.0 0.00 2,460 0 0 0
65 37.0 0.00 3,764 0 0 0
66 21.0 0.00 1,952 0 0 0
67 225.0 0.00 1,017,602 2 0 2
68 26.0 0.00 2,186 0 0 0
69 39.0 0.00 3,638 0 0 0
70 41.0 0.00 4,742 0 0 0
71 21.0 0.00 1,990 0 0 0
72 33.0 0.00 8,080 0 0 0
73 36.0 0.00 2,480 0 0 0
74 43.0 0.00 6,868 0 0 0
91 23.0 0.00 1,872 0 0 0
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- ---------------
0 442,368 0 442,368
1 75,612,160 52,689,869 159,563,776
2 4,440,064 587,863 6,406,144
3 8,503,296 373,139,773 176,275,456
4 8,503,296 173,831,182 340,901,888
5 302,104,576 624,624,423 302,104,576
6 4,177,920 281,393 4,177,920
7 8,503,296 332,217,989 176,275,456
8 302,104,576 624,588,208 302,104,576
9 8,503,296 287,516,370 201,441,280
10 12,697,600 30,895,416 176,275,456
28 75,612,160 57,682,543 669,106,176
35 302,104,576 656,430,002 302,104,576
36 3,260,416 4,158,765 17,940,480
37 4,374,528 4,118,259 20,037,632
38 8,503,296 15,888,540 139,575,296
51 92,389,376 1,017,010,667 1,342,291,968
52 293,715,968 661,308,921 293,715,968
53 13,746,176 7,068,582 22,200,320
54 5,423,104 1,983,651 18,006,016
55 8,503,296 292,517,940 335,659,008
56 30,523,392 64,143,547 293,781,504
57 8,503,296 200,904,951 302,170,112
58 8,503,296 177,908,398 392,282,112
59 176,275,456 416,694,349 302,104,576
60 8,503,296 551,065,896 302,170,112
61 8,503,296 454,332,470 302,104,576
62 22,200,320 114,807,542 293,715,968
63 26,329,088 6,509,034 26,329,088
65 45,203,456 35,828,910 45,203,456
66 176,275,456 460,653,034 176,275,456
67 6,406,144 1,242,296 6,406,144
68 8,503,296 296,568,811 167,886,848
69 8,503,296 411,536,914 226,607,104
70 12,697,600 813,611 12,697,600
71 8,503,296 182,362,105 243,449,856
72 9,617,408 1,074,770 9,617,408
73 293,781,504 669,222,958 293,781,504
74 310,558,720 691,136,120 310,558,720
91 4,308,992 421,577 4,308,992
-------------------------------------------------------------
Undo Segment Summary for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> Undo segment block stats:
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 163 417,657 184 2 0 0 0/0/0/0/0/0
Undo Segment Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7 -> 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
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 219,096 3,825 252
3590/219/15/
1/0
cache buffers chains 14,922,010 1,231 156
0/0/0/0/0
shared pool 44,027 798 35
764/33/1/0/0
KCL gc element parent latc 450,936 101 1
100/1/0/0/0
library cache pin allocati 37,011 98 1
97/1/0/0/0
session allocation 3,317 70 8
62/8/0/0/0
KCL freelist parent latch 332,419 60 4
57/2/1/0/0
ges resource hash list 20,923 45 15
31/13/1/0/0
gcs resource hash 227,848 15 3
13/1/1/0/0
row cache objects 16,456 12 2
10/2/0/0/0
user lock 321 6 1
5/1/0/0/0
ges resource table freelis 7,822 3 1
2/1/0/0/0
cache buffers lru chain 1,976 1 1
0/1/0/0/0
process allocation 7,492 1 1
0/1/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: TDWH Instance: TDWH1 Snaps: 6 -7
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
KCL freelist parent latc kclfget 0 3
2
KCL freelist parent latc kclfrem 0 1
0
KCL gc element parent la kclpdc_2 0 1
0
cache buffers chains kcbzib: multi-block read: 0 48
0
cache buffers chains kcbgtcr: fast path 0 44
7
cache buffers chains kcbzgb: scan from tail. no 0 31
0
cache buffers chains kcbgtcr: kslbegin excl 0 15
100
cache buffers chains kcbchg: kslbegin: bufs not 0 8
9
cache buffers chains kcbgcur: kslbegin 0 6
19
cache buffers chains kclebs_1 0 2
0
cache buffers chains kcbnlc 0 1
7
cache buffers chains kcbzwb 0 1
0
cache buffers lru chain kcbzgb: multiple sets nowa 1 1
0
cost function kzulgt: find user res 0 1
1
gcs resource hash kjbcropen 0 3
0
ges resource hash list kjrmas1: lookup master nod 0 15
0
ges resource table freel kjralc: resource allocatio 0 1
0
library cache kglic 0 97
15
library cache kgllkdl: child: cleanup 0 39
9
library cache kglpndl: child: before pro 0 26
30
library cache kglhdgn: child: 0 15
61
library cache kglupc: child 0 13
21
library cache kglpnc: child 0 12
35
library cache kgldte: child 0 0 11
24
library cache kglhdgc: child: 0 10
11
library cache kglobpn: child: 0 6
3
library cache kglpin: child: heap proces 0 6
3
library cache kglpndl: child: after proc 0 4
0
library cache kglget: child: KGLDSBRD 0 3
8
library cache kglpin 0 3
4
library cache kgllkal: child: multiinsta 0 1
2
library cache pin alloca kglpnal 0 1
0
process allocation ksucrp 0 1
0
row cache objects kqrigt 0 2
1
session allocation ksucri 0 6
4
session allocation ksuxds: KSUSFCLC not set 0 2
2
shared pool kghupr1 0 32
30
shared pool kghfrunp: clatch: nowait 0 5
0
shared pool kghalo 0 3
5
shared pool kghfrunp: clatch: wait 0 1
4
-------------------------------------------------------------
Dictionary Cache Stats for DB: TDWH Instance: TDWH1 Snaps: 6 -7
->"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_global_oids 24 0.0 0 0
143
dc_histogram_defs 55 1.8 0 0
12,348
dc_object_ids 330 0.9 0 0
2,073
dc_objects 303 10.9 0 15
4,087
dc_partition_scns 139 0.0 0 139
285
dc_profiles 151 0.0 0 0
2
dc_rollback_segments 886 0.0 0 0
158
dc_segments 4,086 0.5 0 49
29,771
dc_sequences 10 10.0 0 10
23
dc_table_scns 2 0.0 0 1
19
dc_tablespace_quotas 17 11.8 0 8
21
dc_tablespaces 186 0.0 0 0
133
dc_user_grants 246 0.0 0 0
38
dc_usernames 94 0.0 0 0
29
dc_users 1,177 0.0 0 0
52
-------------------------------------------------------------
GES GES GES
Cache Requests Conflicts Releases
------------------------- ------------ ------------ ------------
dc_global_oids 0 0 0
dc_histogram_defs 1 0 0
dc_object_ids 3 0 0
dc_objects 59 0 0
dc_partition_scns 0 0 0
dc_profiles 0 0 0
dc_rollback_segments 0 0 0
dc_segments 81 37 0
dc_sequences 20 1 0
dc_table_scns 2 0 0
dc_tablespace_quotas 18 1 0
dc_tablespaces 0 0 0
dc_user_grants 0 0 0
dc_usernames 0 0 0
dc_users 0 0 0
-------------------------------------------------------------
Library Cache Activity for DB: TDWH Instance: TDWH1 Snaps: 6 -7 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 354 0.6 354 0.6 0
0
CLUSTER 13 0.0 21 0.0 0
0
INDEX 24 0.0 28 0.0 0
0
SQL AREA 6,507 0.3 26,860 0.5 46
6
TABLE/PROCEDURE 642 17.6 2,565 13.1 139
0
TRIGGER 314 0.0 314 0.0 0
0
-------------------------------------------------------------
GES Lock GES Pin GES Pin GES Inval GES
Invali-
Namespace Requests Requests Releases Requests
dations
BODY 0 2 0 2
0
CLUSTER 13 0 0 0
0
INDEX 22 8 0 0
0
SQL AREA 0 0 0 0
0
TABLE/PROCEDURE 430 125 0 5
0
TRIGGER 0 0 0 0
0
-------------------------------------------------------------
Shared Pool Advisory for DB: TDWH Instance: TDWH1 End Snap: 7
-> 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
----------- ----- ---------- ------------ ------------ -------
336 .5 325 50,337 34,203 1.0 7,676,006
416 .6 404 63,439 34,203 1.0 7,676,116
496 .8 483 76,552 34,203 1.0 7,676,207
576 .9 562 89,539 34,203 1.0 7,676,228
656 1.0 641 102,380 34,203 1.0 7,676,255
736 1.1 720 115,196 34,203 1.0 7,676,276
816 1.2 799 128,399 34,203 1.0 7,676,276
896 1.4 878 141,735 34,203 1.0 7,676,305
976 1.5 957 153,449 34,206 1.0 7,677,175
1,056 1.6 1,036 162,190 34,206 1.0 7,677,415
1,136 1.7 1,115 177,120 34,222 1.0 7,677,829
1,216 1.9 1,194 183,744 34,268 1.0 7,678,773
1,296 2.0 1,273 187,527 34,269 1.0 7,678,958
1,376 2.1 1,314 190,752 34,275 1.0 7,680,675
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 536,870,912 Fixed Size 757,328 Redo Buffers 2,408,448 Variable Size 1,157,627,904 ---------------- sum 1,697,664,592 -------------------------------------------------------------
SGA breakdown difference for DB: TDWH Instance: TDWH1 Snaps: 6 -7
Pool Name Begin value Endvalue % Diff
------ ------------------------------ ----------------Resource Limit Stats for DB: TDWH Instance: TDWH1 End Snap: 7 -> only rows with Current or Maximum Utilization > 80% of Limit are shown
---------------- -------
java free memory 262,512,640 262,512,640 0.00 java memory in use 5,922,816 5,922,816 0.00 large PX msg pool 117,140,512 117,140,512 0.00 large free memory 300,000 300,000 0.00 shared KGK heap 32,600 32,600 0.00 shared KGLS heap 4,141,840 4,258,312 2.81 shared KQR L PO 6,365,416 6,393,152 0.44 shared KQR L SO 33,792 33,792 0.00 shared KQR M PO 22,137,440 22,137,952 0.00 shared KQR M SO 192,080 192,080 0.00 shared KQR S SO 82,792 82,792 0.00 shared KQR X PO 5,152 5,152 0.00 shared KTCCC OBJECT ################ ################ 0.00 shared MTTR advisory 97,568 97,568 0.00 shared PL/SQL DIANA 7,661,976 7,736,744 0.98 shared PL/SQL MPCODE 11,590,544 11,737,408 1.27 shared PLS non-lib hp 3,752 3,752 0.00 shared PX subheap 1,952,776 1,952,776 0.00 shared XDB Schema Cac 5,105,744 5,105,744 0.00 shared dictionary cache 3,229,952 3,229,952 0.00 shared errors 133,448 133,448 0.00 shared free memory 53,217,656 53,776,928 1.05 shared joxlod: in ehe 372,736 372,736 0.00 shared joxlod: init P ################ ################ 0.00 shared joxs heap init 4,240 4,240 0.00 shared library cache 157,411,568 157,035,632 -0.24 shared miscellaneous 183,523,328 183,305,728 -0.12 shared parameters 85,048 86,952 2.24 shared partitioning d 12,147,552 12,159,384 0.10 shared sim memory hea 254,464 254,464 0.00 shared sql area 301,837,200 301,488,432 -0.12 shared subheap 102,312 102,312 0.00 shared table definiti 17,928 20,808 16.06 shared trigger defini 4,528 4,528 0.00 shared trigger inform 2,208 2,272 2.90 shared trigger source 1,064 1,064 0.00 shared type object de 8,368 8,368 0.00 buffer_cache 536,870,912 536,870,912 0.00 fixed_sga 757,328 757,328 0.00 log_buffer 2,360,320 2,360,320 0.00 -------------------------------------------------------------
Current Maximum Initial
Resource Name Utilization Utilization Allocation
Limit
gcs_resources 30,093 34,223 34812
34812
-------------------------------------------------------------
init.ora Parameters for DB: TDWH Instance: TDWH1 Snaps: 6 -7
End
value
Parameter Name Begin value (if
different)
aq_tm_processes 1
background_dump_dest /home/oracle/9.2.0/admin/TDWH/bdu
cluster_database TRUE
cluster_database_instances 2
compatible 9.2.0.0.0
control_files /oracle/data01/TDWH/control01.ctl
core_dump_dest /home/oracle/9.2.0/admin/TDWH/cdu
db_block_size 16384
db_cache_size 536870912
db_domain
db_file_multiblock_read_count 16
db_files 1000
db_name TDWH
disk_asynch_io FALSE
enqueue_resources 3000
fast_start_mttr_target 300
fast_start_parallel_rollback FALSE
hash_join_enabled TRUE
instance_groups TUM, EXT
instance_name TDWH1
instance_number 1
java_pool_size 268435456
job_queue_processes 20
large_pool_size 117440512
local_listener LISTENER_TDWH1
log_archive_dest_1 LOCATION=/oracle/archive/TDWH/arc
log_archive_format %t_%s.arc
log_archive_start TRUE
log_buffer 2097152
open_cursors 1000
parallel_adaptive_multi_user FALSE
parallel_automatic_tuning TRUE
parallel_instance_group TUM
parallel_max_servers 225
parallel_min_servers 32
pga_aggregate_target 4294967296
processes 500
query_rewrite_enabled TRUE
query_rewrite_integrity TRUSTED
remote_listener LISTENERS_TDWH
remote_login_passwordfile EXCLUSIVE
resource_limit FALSE
shared_pool_size 687865856
sort_area_size 524288
star_transformation_enabled TRUE
thread 1
timed_statistics TRUE
undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1
user_dump_dest /home/oracle/9.2.0/admin/TDWH/udu
utl_file_dir *
workarea_size_policy AUTO
-------------------------------------------------------------
End of Report Received on Thu Sep 02 2004 - 03:49:50 CDT
 |
 |