| 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 -> DB File Sequential Read Waits
Anyone have suggestions on how do reduce the number of db file
sequential reads?
Also any suggestions on improving IO on an EMC array (concatenated
versus striped volumes)
I have a CRM application running on Oracle 8.1.6.3 Enterprise Edition. It is in archive log mode and is running in a Sun Solaris 2.8 machine with 2 dedicated CPU's.
I am in the process of testing a new disk configuration on the backend of this server (EMC) I have the data index files assigned to respective file systems /u02 and /u03. The breakdown of the physical disk on the array is done in 8G chunks on 73G hardrives. The stripe size across the disk is 1M.
The test I am performing is strictly a read-only set against the DB using most often used queries from the production database. A similar test was run against the production DB during off hours.
I am seeing no real improvement in the total IO time between the
production system test (concatenated volumes) and the test server
(striped volumes) both is amount of IO and the actual times.
The application is totally random on its access to the data. There are not really any hot blocks other than those used by a small set of lookup tables.
For the most part index scans are being utilized. Note optimizer_index_cost_adj is set at 5. Also db_file_direct_io_count is at 16. Do these not have opposing methods by the CBO, i.e. less costly index versus full table scan.
There is primarily 1 table that is accessed frequently and this table is partitioned based upon each month. IO seems to be evenly distributed.
I have included that latest statspack after having just re-analyzed all tables and indexes and running test again. The only significant waits that I am seeing are the db file sequential reads which I am attributing to the random pattern of data access and indexes being used. I am experimenting with diff buffer pools now.
Any insight or direction is appreciated.
Regards,
Bob
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host ---------- ----------- ---------- -------- ---------- ----
Snap
Length
Start Id End Id Start Time End Time
-------- -------- -------------------- --------------------
-----------
9711 9714 29-Dec-03 10:09:28 29-Dec-03 11:09:44
60.27
Cache Sizes
db_block_buffers: 80000
db_block_size: 8192
log_buffer: 2048000
shared_pool_size: 300M
Load Profile
Per Second Per Transaction
--------------- ---------------
Redo size: 550.98 31,624.57
Logical reads: 270.19 15,508.00
Block changes: 0.93 53.60
Physical reads: 213.04 12,227.81
Physical writes: 0.46 26.40
User calls: 35.57 2,041.38
Parses: 9.04 519.13
Hard parses: 8.14 467.22
Sorts: 11.99 688.22
Transactions: 0.02
Rows per Sort: 41.03
Pct Blocks changed / Read: 0.35
Recursive Call Pct: 38.74
Rollback / transaction Pct: 0.00
Instance Efficiency Percentages (Target 100%)
Buffer Nowait Ratio: 99.99
Buffer Hit Ratio: 21.15
Library Hit Ratio: 79.99
Redo NoWait Ratio: 100.00
In-memory Sort Ratio: 99.99
Soft Parse Ratio: 10.00
Latch Hit Ratio: 99.88
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait
% Total
Event Waits Time (cs)
Wt Time
-------------------------------------------- ------------ ------------
-------
db file sequential read 767,473 2,974,183
99.64
control file parallel write 1,155 7,301
.24
buffer busy waits 140 608
.02
SQL*Net more data to client 47,884 188
.01
latch free 293 137
.00
-------------------------------------------------------------
Wait Events for DB: PRODTEST Instance: PRODTEST Snaps: 9711 -
9714
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- -----
------
db file sequential read 767,473 0 2,974,183 39
######
control file parallel write 1,155 0 7,301 63
18.3
db file parallel write 57 0 2,122 372
0.9
buffer busy waits 140 0 608 43
2.2
SQL*Net more data to client 47,884 0 188 0
760.1
latch free 293 219 137 5
4.7
sort segment request 1 1 102 1020
0.0
log file sync 67 0 85 13
1.1
db file scattered read 177 0 62 4
2.8
file open 982 0 56 1
15.6
log file parallel write 70 0 28 4
1.1
direct path write 29 0 27 9
0.5
direct path read 417 0 21 1
6.6
control file sequential read 2,667 0 9 0
42.3
refresh controlfile command 255 0 8 0
4.0
SQL*Net message from client 98,761 0 6,931,777 702
######
SQL*Net message to client 98,784 0 22 0
######
-------------------------------------------------------------
Background Wait Events for DB: PRODTEST Instance: PRODTEST Snaps:
9711 -
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- -----
------
control file parallel write 1,155 0 7,301 63
18.3
db file scattered read 177 0 62 4
2.8
log file parallel write 70 0 28 4
1.1
db file sequential read 30 0 10 3
0.5
control file sequential read 765 0 3 0
12.1
rdbms ipc message 14,671 14,073 5,067,010 3454
232.9
pmon timer 1,174 1,174 361,537 3080
18.6
smon timer 12 11 339,195 #####
0.2
-------------------------------------------------------------
SQL ordered by Gets for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 971
Gets % of
Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
4,354 14 311.0 .4 1714733582 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
1,104 368 3.0 .1 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(l
906 115 7.9 .1 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
363 121 3.0 .0 189272129 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataob
340 308 1.1 .0 313510536 select job from sys.job$ where next_date < sysdate and (field1 = :1 or (fiel
317 188 1.7 .0 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
261 132 2.0 .0 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
131 316 0.4 .0 3759542639 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; -207 78 -2.7 -.0 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
-229 128 -1.8 -.0 395844583 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
-291 86 -3.4 -.0 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
-760 356 -2.1 -.1 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
-784 21 -37.3 -.1 957616262 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$
-822 21 -39.1 -.1 3218356218 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wh
-871 21 -41.5 -.1 1428100621 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wh
-894 250 -3.6 -.1 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sampl
-1,073 2 -536.5 -.1 2913840444 select length from fet$ where file#=:1 and block#=:2 and ts#=:3
Physical Reads % of
Reads Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
1,631 14 116.5 .2 1714733582 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
7 132 0.1 .0 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
3 121 0.0 .0 189272129 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataob
3 368 0.0 .0 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(l
2 316 0.0 .0 3759542639 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; 1 308 0.0 .0 313510536 select job from sys.job$ where next_date < sysdate and (field1 = :1 or (fiel
1 188 0.0 .0 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
0 115 0.0 .0 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
-2 128 -0.0 -.0 395844583 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(
-3 2 -1.5 -.0 2913840444 select length from fet$ where file#=:1 and block#=:2 and ts#=:3
-4 78 -0.1 -.0 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
-7 356 -0.0 -.0 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
-12 86 -0.1 -.0 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
-26 250 -0.1 -.0 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sampl
-47 21 -2.2 -.0 1428100621 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wh
-55 21 -2.6 -.0 957616262 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$
-59 21 -2.8 -.0 3218356218 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wh
Rows Rows
Processed Executes per Exec Hash Value
-------------- ------------ ------------ ------------
SQL statement
1,540 14 110.0 1714733582 select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
368 368 1.0 4059714361 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(l
338 115 2.9 4195740643 select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
316 316 1.0 3759542639 BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END; 193 188 1.0 1536916657 select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
121 121 1.0 189272129 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataob
66 132 0.5 114078687 select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1
1 2 0.5 2913840444 select length from fet$ where file#=:1 and block#=:2 and ts#=:3
0 308 0.0 313510536 select job from sys.job$ where next_date < sysdate and (field1 = :1 or (fiel
-53 86 -0.6 199702406 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,i.p
-69 78 -0.9 2216582187 select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),audit$
-186 21 -8.9 957616262 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$
-190 356 -0.5 2085632044 select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1
-195 21 -9.3 1428100621 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wh
-201 21 -9.6 3218356218 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wh
-280 250 -1.1 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sampl
-386 27 -14.3 955191413 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from ob
Statistic Total per Second perTrans
--------------------------------- ---------------- ------------ ------------ CPU used by this session 64,676 17.9 1,026.6 CPU used when call started 61,962 17.1 983.5 CR blocks created 3 0.0 0.1 DBWR buffers scanned 17,399 4.8 276.2 DBWR free buffers found 17,309 4.8 274.8 DBWR lru scans 59 0.0 0.9 DBWR make free requests 68 0.0 1.1 DBWR summed scan depth 17,399 4.8 276.2 DBWR undo block writes 165 0.1 2.6 SQL*Net roundtrips to/from client 98,549 27.3 1,564.3 background timeouts 14,316 4.0 227.2 branch node splits 1 0.0 0.0 buffer is not pinned count 876,845 242.5 13,918.2 buffer is pinned count 1,012,251 279.9 16,067.5 bytes received via SQL*Net from c 18,855,545 5,214.5 299,294.4 bytes sent via SQL*Net to client 137,544,933 38,037.9 2,183,252.9 calls to get snapshot scn: kcmgss 40,854 11.3 648.5 calls to kcmgas 103 0.0 1.6 calls to kcmgcs 144 0.0 2.3 change write time 6 0.0 0.1 cleanouts only - consistent read 72 0.0 1.1 cluster key scan block gets 2,736 0.8 43.4 cluster key scans 860 0.2 13.7 commit cleanouts 346 0.1 5.5 commit cleanouts successfully com 346 0.1 5.5 consistent changes 3 0.0 0.1 consistent gets 964,521 266.7 15,309.9 cursor authentications 81 0.0 1.3 data blocks consistent reads - un 3 0.0 0.1 db block changes 3,377 0.9 53.6 db block gets 12,483 3.5 198.1 deferred (CURRENT) block cleanout 168 0.1 2.7 dirty buffers inspected 384 0.1 6.1 enqueue releases 1,650 0.5 26.2 enqueue requests 1,651 0.5 26.2 execute count 32,880 9.1 521.9 free buffer inspected 398 0.1 6.3 free buffer requested 769,442 212.8 12,213.4 hot buffers moved to head of LRU 29,882 8.3 474.3 immediate (CR) block cleanout app 72 0.0 1.1 immediate (CURRENT) block cleanou 82 0.0 1.3 leaf node splits 34 0.0 0.5 logons cumulative 558 0.28.9
messages received 519 0.1 8.2 messages sent 519 0.1 8.2 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 824,937 228.1 13,094.2 opened cursors cumulative 3,490 1.055.4
parse count (hard) 29,435 8.1 467.2 parse count (total) 32,705 9.0 519.1 parse time cpu 16,926 4.7 268.7 parse time elapsed 17,505 4.8 277.9 physical reads 770,352 213.0 12,227.8 physical reads direct 1,189 0.318.9
Statistic Total per Second perTrans
--------------------------------- ---------------- ------------
------------
physical writes 1,663 0.5
26.4
physical writes direct 1,189 0.3
18.9
physical writes non checkpoint 1,663 0.5
26.4
prefetched blocks 1,513 0.4
24.0
process last non-idle time 92,253,226,642 ############
############
recursive calls 81,320 22.5
1,290.8
recursive cpu usage 349 0.1
5.5
redo blocks written 4,041 1.1
64.1
redo entries 1,864 0.5
29.6
redo size 1,992,348 551.0
31,624.6
redo synch time 85 0.0
1.4
redo synch writes 67 0.0
1.1
redo wastage 12,448 3.4
197.6
redo write time 104 0.0
1.7
redo writer latching time 0 0.0
0.0
redo writes 70 0.0
1.1
rollbacks only - consistent read 3 0.0
0.1
rows fetched via callback 16,089 4.5
255.4
session connect time 92,253,226,642 ############
############
session logical reads 977,004 270.2
15,508.0
session pga memory 110,464,284 30,548.8
1,753,401.3
session pga memory max 110,464,284 30,548.8
1,753,401.3
session uga memory 3,839,192 1,061.7
60,939.6
session uga memory max 17,525,932 4,846.8
278,189.4
sorts (disk) 3 0.0
0.1
sorts (memory) 43,355 12.0
688.2
sorts (rows) 1,779,167 492.0
28,240.8
summed dirty queue length 318 0.1
5.1
table fetch by rowid 931,163 257.5
14,780.4
table fetch continued row 19,407 5.4
308.1
table scan blocks gotten 2,331 0.6
37.0
table scan rows gotten 2,681 0.7
42.6
table scans (short tables) 300 0.1
4.8
total file opens 982 0.3
15.6
user calls 128,607 35.6
2,041.4
user commits 63 0.0
1.0
-------------------------------------------------------------
Tablespace IO Summary for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 9
Avg Read Total
Avg Wait
Tablespace Reads (ms) Writes Waits
(ms)
------------------------- ----------- -------- ----------- ----------
--------
DATA_Y2003_M8 46,668 34.9 0 4
110.0
DATA_Y2002_M8 33,270 45.7 0 9
81.1
DATA_Y2003_M10 32,770 40.2 0 3
103.3
DATA_Y2003_M7 28,712 45.4 0 0
0.0
DATA_Y2003_M9 26,060 40.7 0 2
45.0
DATA_Y2002_M9 25,034 38.5 0 0
0.0
DATA_Y2003_M1 23,662 44.5 0 0
0.0
DATA_Y2002_M10 23,634 41.5 0 2
5.0
DATA_Y2003_M11 23,109 43.2 0 2
50.0
DATA_Y2002_M12 22,282 40.3 0 1
10.0
DATA_Y2003_M3 21,905 42.8 0 0
0.0
DATA_Y2000_Q4 20,638 32.3 0 0
0.0
DATA_Y2003_M4 20,280 44.0 0 0
0.0
DATA_Y2003_M6 19,977 41.9 0 0
0.0
DATA_Y2002_M11 19,894 40.8 0 1
100.0
DATA_Y2003_M2 19,890 43.1 0 0
0.0
DATA_Y2002_M7 19,656 36.7 0 0
0.0
DATA_Y2003_M5 18,902 40.4 0 0
0.0
DATA_Y2002_M5 17,927 32.6 0 0
0.0
DATA_Y2002_M1 17,216 35.0 0 0
0.0
DATA_Y2002_M6 17,096 39.0 0 1
70.0
DATA_Y2003_M12 16,153 40.3 0 0
0.0
CCT_GLOBAL_IDX1B 15,861 3.2 0 69
15.4
DATA_Y2002_M4 15,829 36.7 0 0
0.0
DATA_Y2002_M3 15,380 38.5 0 0
0.0
DATA_Y2001_M8 15,106 35.2 0 0
0.0
DATA_Y2001_M11 14,934 35.2 0 0
0.0
DATA_Y2001_M10 13,927 34.8 0 0
0.0
DATA_Y2002_M2 13,588 38.1 0 0
0.0
DATA_Y2001_M7 13,472 39.4 0 0
0.0
DATA_Y2001_M12 12,932 35.8 0 0
0.0
DATA_Y1998 12,794 28.6 0 36
69.4
DATA_Y2001_M9 12,520 31.0 0 0
0.0
DATA_Y2000_Q3 11,475 47.3 0 1
0.0
DATA_Y2001_M6 11,451 38.2 0 0
0.0
DATA_Y2001_M5 10,547 36.2 0 0
0.0
DATA_Y2001_M4 9,913 33.2 0 0
0.0
DATA_Y2001_M3 9,251 29.7 0 0
0.0
DATA_Y1999 9,129 71.1 0 8
70.0
DATA_Y2000_Q2 9,073 50.5 0 0
0.0
DATA_Y2001_M1 8,513 28.9 0 0
0.0
DATA_Y2000_Q1 7,973 55.8 0 0
0.0
DATA_Y2001_M2 7,230 31.8 0 1
100.0
CCT_DATA_LG 664 2.8 0 0
0.0
STATSDATA 288 2.8 261 0
0.0
SYSTEM 482 2.5 51 0
0.0
CCT_INDEX_LG 528 2.0 0 0
0.0
RBS 44 0.2 162 0
0.0
INDX 2 15.0 0 0
0.0
CCT_DATA1 1 0.0 0 0
0.0
-------------------------------------------------------------
File IO Statistics for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 9714
Tablespace Filename
Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg
Wait (ms)
-------------- ----------- ----------- -------------- ----------
CCT_DATA1 /u02/oradata/PRODTEST/CCT_DATA1_01.dbf
1 1.0 0.0 0 0
CCT_DATA_LG /u02/oradata/PRODTEST/CCT_DATA_LG1.dbf
276 1.0 0.8 0 0
CCT_DATA_LG /u02/oradata/PRODTEST/CCT_DATA_LG2.dbf
388 1.0 4.3 0 0
CCT_GLOBAL_IDX1B /u03/oradata/PRODTEST/CCT_GLOBAL_IDX1A1.dbf
15,861 1.0 3.2 0 69
15.4
CCT_INDEX_LG /u04/oradata/PRODTEST/CCT_INDEX_LG1.dbf
244 1.0 1.8 0 0
CCT_INDEX_LG /u04/oradata/PRODTEST/CCT_INDEX_LG2.dbf
283 1.0 2.2 0 0
CCT_INDEX_LG /u04/oradata/PRODTEST/CCT_INDEX_LG3.dbf
1 1.0 0.0 0 0
DATA_Y1998 /u02/oradata/PRODTEST/data_Y1998_1.dbf
12,794 1.0 28.6 0 36
69.4
DATA_Y1999 /u02/oradata/PRODTEST/data_Y1999_1.dbf
9,129 1.0 71.1 0 8
70.0
DATA_Y2000_Q1 /u02/oradata/PRODTEST/data_Y2000_q1_1.dbf
7,973 1.0 55.8 0 0
DATA_Y2000_Q2 /u02/oradata/PRODTEST/data_Y2000_q2_1.dbf
9,073 1.0 50.5 0 0
DATA_Y2000_Q3 /u02/oradata/PRODTEST/data_Y2000_q3_1.dbf
11,475 1.0 47.3 0 1
0.0
DATA_Y2000_Q4 /u02/oradata/PRODTEST/data_Y2000_q4_1.dbf
20,638 1.0 32.3 0 0
DATA_Y2001_M1 /u02/oradata/PRODTEST/data_Y2001_m1_1.dbf
8,513 1.0 28.9 0 0
DATA_Y2001_M10 /u02/oradata/PRODTEST/data_Y2001_m10_1.dbf
13,927 1.0 34.8 0 0
DATA_Y2001_M11 /u02/oradata/PRODTEST/data_Y2001_m11_1.dbf
14,934 1.0 35.2 0 0
DATA_Y2001_M12 /u02/oradata/PRODTEST/data_Y2001_m12_1.dbf
12,932 1.0 35.8 0 0
DATA_Y2001_M2 /u02/oradata/PRODTEST/data_Y2001_m2_1.dbf
7,230 1.0 31.8 0 1
100.0
File IO Statistics for DB: PRODTEST Instance: PRODTEST Snaps: 9711 - 9714
Tablespace Filename
Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg
Wait (ms)
-------------- ----------- ----------- -------------- ----------
DATA_Y2001_M3 /u02/oradata/PRODTEST/data_Y2001_m3_1.dbf
9,251 1.0 29.7 0 0
DATA_Y2001_M4 /u02/oradata/PRODTEST/data_Y2001_m4_1.dbf
9,913 1.0 33.2 0 0
DATA_Y2001_M5 /u02/oradata/PRODTEST/data_Y2001_m5_1.dbf
10,547 1.0 36.2 0 0
DATA_Y2001_M6 /u02/oradata/PRODTEST/data_Y2001_m6_1.dbf
11,451 1.0 38.2 0 0
DATA_Y2001_M7 /u02/oradata/PRODTEST/data_Y2001_m7_1.dbf
13,472 1.0 39.4 0 0
DATA_Y2001_M8 /u02/oradata/PRODTEST/data_Y2001_m8_1.dbf
15,106 1.0 35.2 0 0
DATA_Y2001_M9 /u02/oradata/PRODTEST/data_Y2001_m9_1.dbf
12,520 1.0 31.0 0 0
DATA_Y2002_M1 /u02/oradata/PRODTEST/data_Y2002_m1_1.dbf
17,216 1.0 35.0 0 0
DATA_Y2002_M10 /u02/oradata/PRODTEST/data_Y2002_m10_1.dbf
23,634 1.0 41.5 0 2
5.0
DATA_Y2002_M11 /u02/oradata/PRODTEST/data_Y2002_m11_1.dbf
19,894 1.0 40.8 0 1
100.0
DATA_Y2002_M12 /u02/oradata/PRODTEST/data_Y2002_m12_1.dbf
22,282 1.0 40.3 0 1
10.0
DATA_Y2002_M2 /u02/oradata/PRODTEST/data_Y2002_m2_1.dbf
13,588 1.0 38.1 0 0
DATA_Y2002_M3 /u02/oradata/PRODTEST/data_Y2002_m3_1.dbf
15,380 1.0 38.5 0 0
DATA_Y2002_M4 /u02/oradata/PRODTEST/data_Y2002_m4_1.dbf
15,829 1.0 36.7 0 0
DATA_Y2002_M5 /u02/oradata/PRODTEST/data_Y2002_m5_1.dbf
17,927 1.0 32.6 0 0
DATA_Y2002_M6 /u02/oradata/PRODTEST/data_Y2002_m6_1.dbf
17,096 1.0 39.0 0 1
70.0
DATA_Y2002_M7 /u02/oradata/PRODTEST/data_Y2002_m7_1.dbf
19,656 1.0 36.7 0 0
DATA_Y2002_M8 /u02/oradata/PRODTEST/data_Y2002_m8_1.dbf
33,270 1.0 45.7 0 9
81.1
File IO Statistics for DB: PRODTEST Instance: PRODTEST Snaps: 9711 - 9714
Tablespace Filename
Reads Avg Blks Rd Avg Rd (ms) Writes Tot Waits Avg
Wait (ms)
-------------- ----------- ----------- -------------- ----------
DATA_Y2002_M9 /u02/oradata/PRODTEST/data_Y2002_m9_1.dbf
25,034 1.0 38.5 0 0
DATA_Y2003_M1 /u02/oradata/PRODTEST/data_Y2003_m1_1.dbf
23,662 1.0 44.5 0 0
DATA_Y2003_M10 /u02/oradata/PRODTEST/data_Y2003_m10_1.dbf
32,770 1.0 40.2 0 3
103.3
DATA_Y2003_M11 /u02/oradata/PRODTEST/data_Y2003_m11_1.dbf
23,109 1.0 43.2 0 2
50.0
DATA_Y2003_M12 /u02/oradata/PRODTEST/DATA_Y2003_M121.dbf
16,153 1.0 40.3 0 0
DATA_Y2003_M2 /u02/oradata/PRODTEST/data_Y2003_m2_1.dbf
19,890 1.0 43.1 0 0
DATA_Y2003_M3 /u02/oradata/PRODTEST/data_Y2003_m3_1.dbf
21,905 1.0 42.8 0 0
DATA_Y2003_M4 /u02/oradata/PRODTEST/data_Y2003_m4_1.dbf
20,280 1.0 44.0 0 0
DATA_Y2003_M5 /u02/oradata/PRODTEST/data_Y2003_m5_1.dbf
18,902 1.0 40.4 0 0
DATA_Y2003_M6 /u02/oradata/PRODTEST/data_Y2003_m6_1.dbf
19,977 1.0 41.9 0 0
DATA_Y2003_M7 /u02/oradata/PRODTEST/data_Y2003_m7_1.dbf
28,712 1.0 45.4 0 0
DATA_Y2003_M8 /u02/oradata/PRODTEST/data_Y2003_m8_1.dbf
46,668 1.0 34.9 0 4
110.0
DATA_Y2003_M9 /u02/oradata/PRODTEST/data_Y2003_m9_1.dbf
26,060 1.0 40.7 0 2
45.0
INDX /u03/oradata/PRODTEST/indx01.dbf
2 1.0 15.0 0 0
RBS /u05/oradata/PRODTEST/rbs01.dbf
44 1.0 0.2 162 0
STATSDATA /u02/oradata/PRODTEST/statsdata1.dbf
288 1.0 2.8 261 0
SYSTEM /u01/oradata/PRODTEST/system01.dbf
482 4.1 2.5 51 0
-------------------------------------------------------------
Buffer wait Statistics for DB: PRODTEST Instance: PRODTEST Snaps:
9711 -
Tot Wait Avg
Class Waits Time (cs) Time (cs)
------------------ ----------- ---------- ---------
data block 140 608 4
-------------------------------------------------------------
Rollback Segment Stats for DB: PRODTEST Instance: PRODTEST Snaps:
9711 -
Trans Table Pct Undo Bytes
RBS No Gets Waits Written Wraps Shrinks Extends
------ ------------ ------- --------------- -------- -------- --------
0 15.0 0.00 0 0 0 0
1 89.0 0.00 173,642 0 0 0
2 64.0 0.00 137,626 1 0 0
3 22.0 0.00 1,438 0 0 0
4 22.0 0.00 1,198 0 0 0
5 22.0 0.00 1,194 0 0 0
6 25.0 0.00 1,594 0 0 0
7 23.0 0.00 1,462 0 0 0
8 22.0 0.00 1,466 0 0 0
9 22.0 0.00 1,532 0 0 0
10 22.0 0.00 1,462 0 0 0
11 31.0 0.00 28,452 0 0 0
12 20.0 0.00 980 0 0 0
13 20.0 0.00 712 0 0 0
14 20.0 0.00 1,046 0 0 0
15 20.0 0.00 1,042 0 0 0
16 20.0 0.00 980 0 0 0
17 20.0 0.00 976 0 0 0
18 20.0 0.00 976 0 0 0
19 20.0 0.00 1,046 0 0 0
20 20.0 0.00 1,042 0 0 0
21 57.0 0.00 976 0 0 0
22 20.0 0.00 976 0 0 0
23 20.0 0.00 976 0 0 0
24 20.0 0.00 1,112 0 0 0
25 20.0 0.00 976 0 0 0
26 78.0 0.00 171,936 1 0 0
27 20.0 0.00 980 0 0 0
28 20.0 0.00 980 0 0 0
29 20.0 0.00 976 0 0 0
-------------------------------------------------------------
Rollback Segment Storage for DB: PRODTEST Instance: PRODTEST Snaps:
9711 -
RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- ----------- --------------- ---------------
0 581,632 0 581,632
1 4,186,112 0 4,194,304 4,186,112
2 4,186,112 52,428 4,194,304 4,186,112
3 4,186,112 0 4,194,304 4,186,112
4 4,186,112 0 4,194,304 4,186,112
5 4,186,112 0 4,194,304 4,186,112
6 4,186,112 0 4,194,304 4,186,112
7 4,186,112 0 4,194,304 4,186,112
8 4,186,112 0 4,194,304 4,186,112
9 4,186,112 0 4,194,304 4,186,112
10 4,186,112 0 4,194,304 4,186,112
11 4,186,112 0 4,194,304 4,186,112
12 4,186,112 0 4,194,304 4,186,112
13 4,186,112 0 4,194,304 4,186,112
14 4,186,112 0 4,194,304 4,186,112
15 4,186,112 0 4,194,304 4,186,112
16 4,186,112 0 4,194,304 4,186,112
17 4,186,112 0 4,194,304 4,186,112
18 4,186,112 0 4,194,304 4,186,112
19 4,186,112 0 4,194,304 4,186,112
20 4,186,112 0 4,194,304 4,186,112
21 4,186,112 0 4,194,304 4,186,112
22 4,186,112 0 4,194,304 4,186,112
23 4,186,112 0 4,194,304 4,186,112
24 4,186,112 0 4,194,304 4,186,112
25 4,186,112 0 4,194,304 4,186,112
26 4,186,112 52,428 4,194,304 4,186,112
27 4,186,112 0 4,194,304 4,186,112
28 4,186,112 0 4,194,304 4,186,112
29 4,186,112 0 4,194,304 4,186,112
-------------------------------------------------------------
Latch Activity for DB: PRODTEST Instance: PRODTEST Snaps: 9711 -
9714
Pct Avg
Pct
Get Get Sleeps Nowait
Nowait
Latch Name Requests Miss /Miss Requests
Miss
------------------------------- ------------ ------ ------ -----------
------
Active checkpoint queue latch 1,367 0.0 0
Checkpoint queue latch 17,473 0.0 0
Token Manager 982 0.0 8,944
0.0
cache buffer handles 53 0.0 0
cache buffers chains 2,629,523 0.0 0.0 770,633
0.0
cache buffers lru chain 1,169 0.0 769,442
0.1
channel handle pool latch 86 0.0 63
0.0
channel operations parent latch 172 0.0 63
0.0
dml lock allocation 242 0.0 0
enqueue hash chains 3,295 0.0 0
enqueues 36,757 0.0 0
event group latch 86 0.0 0
file number translation table 23 0.0 0
job_queue_processes parameter l 56 0.0 0
ktm global data 12 0.0 0
latch wait list 151 0.0 150
0.0
library cache 1,507,947 0.0 0.4 121,127
0.0
library cache load lock 1,242 0.0 0
list of block allocation 218 0.0 0
loader state object freelist 46 0.0 0
longop free list 4 0.0 0
messages 32,290 0.0 0.0 0
multiblock read objects 402 0.0 1
0.0
ncodef allocation latch 56 0.0 0
process allocation 86 0.0 86
0.0
process group creation 149 0.0 0
redo allocation 3,114 0.0 0
redo writing 5,127 0.0 0
row cache objects 52,503,711 0.1 0.0 8,915
0.3
sequence cache 275 0.0 0
session allocation 63,757 0.0 0
session idle bit 287,621 0.0 0
session switching 56 0.0 0
shared java pool 583 0.0 0
shared pool 2,641,158 0.1 0.1 0
sort extent pool 169 0.0 0
transaction allocation 332 0.0 0
transaction branch allocation 56 0.0 0
undo global data 1,471 0.0 0
user lock 298 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 9
Get Spin
&
Latch Name Requests Misses Sleeps
Sleeps 1-4
---------------------------- ------------ ----------- -----------
------------
row cache objects 52,503,711 71,764 72
71695/66/3/0
/0
shared pool 2,641,158 2,220 131
2135/42/40/3
/0
library cache 1,507,947 253 90
193/32/27/1/
0
-------------------------------------------------------------
Latch Miss Sources for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 9714
No Wait
Latch Name Where Misses
Sleeps
------------------------------ -------------------------- -------
-----------
library cache kglpnal: child: alloc spac 0
4
library cache kglhdgc: child: 0
2
library cache kglhdgn: child: 0
2
library cache kglic 0
2
library cache kgllkdl: child: cleanup 0
1
library cache kglupc: child 0
1
row cache objects kqrpre: find obj 0
56
row cache objects kqreqd: rel enqueue 0
11
row cache objects kqreqd 0
5
shared pool kghfrunp: parent clatch: w 0
73
shared pool kghfrunp: alloc: wait 0
72
shared pool kghfrunp: alloc: clatch no 0
50
shared pool kghalo 0
6
shared pool kghfrunp: clatch: wait 0
3
shared pool kghfre 0
2
shared pool kghfrunp: clatch: nowait 0
2
shared pool kghalp 0
1
-------------------------------------------------------------
Buffer Pool Sets for DB: PRODTEST Instance: PRODTEST Snaps: 9711
- 9714
Free Write
Buffer
Set Buffer Consistent Physical Physical Buffer Complete
Busy
Id Gets Gets Reads Writes Waits Waits
Waits
->"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 Pct
Cache Requests Miss Requests Miss Req
Usage SGA
---------------------- ----------- ------ -------- ----- --------
------ ----
dc_constraints 0 0 0
0 0
dc_database_links 0 0 0
0 0
dc_files 0 0 0
0 0
dc_free_extents 1,213 0.2 1 0.0 3
111 96
dc_global_oids 0 0 0
0 0
dc_histogram_data 0 0 0
0 0
dc_histogram_data_valu 0 0 0
0 0
dc_histogram_defs 1,838,083 0.0 0 0
539 99
dc_object_ids 7,865,977 0.0 0 0
310 100
dc_objects 29,970 0.2 0 0
476 98
dc_outlines 0 0 0
0 0
dc_profiles 86 0.0 0 0
1 17
dc_rollback_segments 768 0.0 0 0
33 92
dc_segments 7,616,591 0.0 0 1
541 100
dc_sequence_grants 0 0 0
0 0
dc_sequences 6 0.0 0 4
3 60
dc_synonyms 56 28.6 0 0
27 79
dc_tablespace_quotas 4 50.0 0 1
2 67
dc_tablespaces 50 4.0 0 0
4 80
dc_used_extents 1 100.0 0 1
1 50
dc_user_grants 537 0.0 0 0
11 23
dc_usernames 352 0.0 0 0
7 33
dc_users 147,878 0.0 0 0
14 70
-------------------------------------------------------------
Library Cache Activity for DB: PRODTEST Instance: PRODTEST Snaps:
9711 -
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 144 6.3 144 6.3 0
0
CLUSTER 69 0.0 106 0.0 0
0
INDEX 134,060 0.0 134,060 0.0 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 32,844 89.4 69,447 84.7 93
0
TABLE/PROCEDURE 47,250 0.3 93,980 0.8 268
0
TRIGGER 0 0 0
0
-------------------------------------------------------------
SGA Memory Summary for DB: PRODTEST Instance: PRODTEST
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 655,360,000
Fixed Size 69,312
Redo Buffers 2,064,384
Variable Size 506,396,672
----------------
sum 1,163,890,368
-------------------------------------------------------------
SGA breakdown difference for DB: PRODTEST Instance: PRODTEST Snaps: 9711 -
Name Begin value End valueDifference
----------------------------------- ------------ ------------
------------
DML locks 114,608 114,608
0
KGFF heap 80,532 80,532
0
KGK heap 6,456 6,456
0
KQLS heap 1,121,636 651,988
-469,648
PL/SQL DIANA 364,592 269,264
-95,328
PL/SQL MPCODE 142,632 118,212
-24,420
PLS non-lib hp 2,096 2,096
0
PX msg pool 7,077,900 7,077,900
0
PX subheap 82,676 82,676
0
SYSTEM PARAMETERS 62,944 62,944
0
State objects 247,504 247,504
0
db_block_buffers 666,240,000 666,240,000
0
db_block_hash_buckets 1,411,080 1,411,080
0
db_files 72,496 72,496
0
db_handles 100,000 100,000
0
dictionary cache 540,556 798,756
258,200
enqueue_resources 86,976 86,976
0
event statistics per sess 763,200 763,200
0
fixed allocation callback 1,920 1,920
0
fixed_sga 69,312 69,312
0
free memory 475,622,156 258,701,484
-216,920,672
joxs heap init 468 468
0
ktlbk state objects 105,716 105,716
0
library cache 1,781,928 21,273,144
19,491,216
log_buffer 2,112,000 2,112,000
0
long op statistics array 99,000 99,000
0
memory in use 2,600,960 212,992
-2,387,968
message pool freequeue 231,152 231,152
0
miscellaneous 710,000 864,584
154,584
processes 153,600 153,600
0
sessions 475,200 475,200
0
sql area 993,212 201,045,872
200,052,660
table columns 22,304 19,160
-3,144
table definiti 1,040 1,524
484
transactions 220,324 220,324
0
trigger inform 640 520
-120
-------------------------------------------------------------
init.ora Parameters for DB: PRODTEST Instance: PRODTEST Snaps:
9711 - 971
End
value
Parameter Name Begin value (if
different)
----------------------------- ---------------------------------
_backup_disk_io_slaves 0
background_dump_dest /u01/app/oracle/admin/PRODTEST/bdu
backup_tape_io_slaves TRUE
compatible 8.1.6.3
control_files /u01/oradata/PRODTEST/control01.ct
core_dump_dest /u01/app/oracle/admin/PRODTEST/cdu
db_block_buffers 80000
db_block_size 8192
db_file_direct_io_count 16
db_file_multiblock_read_count 16
db_name PRODTEST
disk_asynch_io TRUE
distributed_transactions 10
instance_name PRODTEST
java_pool_size 20971520
job_queue_interval 60
job_queue_processes 4
large_pool_size 170M
log_archive_dest_1 location=/u08/oradata/PRODTEST/arc
log_archive_format arch_%t_%s.arc
log_archive_max_processes 10
log_archive_start TRUE
log_buffer 2048000
log_checkpoint_interval 10000
log_checkpoint_timeout 0
max_enabled_roles 30
max_rollback_segments 40
open_cursors 500
open_links 4
optimizer_index_cost_adj 5
os_authent_prefix
parallel_automatic_tuning TRUE
processes 200
remote_login_passwordfile EXCLUSIVE
service_names PRODTEST
shared_pool_reserved_size 76800000
shared_pool_size 300M
sort_area_retained_size 65536
sort_area_size 10485760
timed_statistics TRUE
user_dump_dest /u01/app/oracle/admin/PRODTEST/udu
utl_file_dir /tmp
-------------------------------------------------------------
End of Report Received on Mon Dec 29 2003 - 11:45:27 CST
 |
 |