Hi all
The following problem, which I am facing right now may dig up an oracle v7 issue.Well that's for you all to decide:
It started with the old story: users complaining db's too slow db's too slow....
A fifteen minute interval statspack report during peak time revealed the following top timed events
The following is the cache size for the database:Code:Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue 6,816 5,248 24.38
log file sync 16,825 4,178 19.41
db file sequential read 586,895 3,693 17.16
CPU time 1,699 7.89
local write wait 2,412 960 4.46
-------------------------------------------------------------
On querying v$enqueue_stat I found the following:Code:Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,344M Std Block Size: 16K
Shared Pool Size: 1,344M Log Buffer: 5,120K
Too high value for "CI" lock type.Code:EQ_TYPE TOTAL_REQ# TOTAL_WAIT# CUM_WAIT_TIME
CI 297393818 124372109 46706495314
HW 559099673 14279318 99242336
TC 185909 36904 50413500
CF 17088411 70873 38628381
WL 103073 840 23847996
SQ 957295 156528 607980
CU 60813476 482 2989
PS 2376 92 1741
RO 6296 14 707
DV 40357 4 281
JD 4004496 4 149
From a JL article:
From Oracle 8 onwards oracle addresses this issue by object based checkpoints instead of extent based checkpoints.Quote:
Extent checkpoints
When a Parallel Query runs in newer versions of Oracle, it has the option to use direct reads, bypassing the SGA completely (there is an exception to this, which will appear in an article on small tables and cache buffering). In general this is a good thing; however to avoid the risk of reading a block from disc which is OLDER than a block in the SGA, all blocks that are likely to be addressed by the query have to be flushed to disc before the query starts. This is achieved by a process called the Extent-based Checkpoint - a call goes to DBWR for every extent that the query may cover, and DBWR hunts out any dirty blocks from that extent in the SGA, and writes them to disc
This does not usually take much time, but if you have a large number of buffers, DBWR has to work through a large number of hash-chains checking for dirty blocks which have not yet been moved to the dirty list. If there are a lot of extents then the process has to be repeated a large number of times
But on the footnote of the article:
Yes there are some regular truncate operations that goes on the database.Quote:
Footnote:
The same extent-based checkpointing takes place when you drop or truncate an object, and with a large db_block_buffer the checkpointing can take more time than all the updates on the space management tables
Am I facing the same thing as mentioned over here(db version:9204)?
Will it be resolved if I deecrease the db_cache_size?
here is an output from the v$db_cache_advice:
Quite unusualCode:E_SIZE SIZE_FACTOR READ_FACTOR READS
128 0.0952 0.3994 1076192705
256 0.1905 0.1899 511607412
384 0.2857 0.1188 320188770
512 0.381 0.0693 186816120
640 0.4762 0.0257 69215411
768 0.5714 1.1629 3133396490
896 0.6667 1.1252 3031691830
1024 0.7619 1.0896 2935944847
1152 0.8571 1.0538 2839524795
1280 0.9524 1.0176 2741975344
1344 1 0.9991 2692158150
1408 1.0476 0.9813 2644203670
1536 1.1429 0.9449 2545878078
1664 1.2381 0.9117 2456648412
1792 1.3333 0.8809 2373611216
1920 1.4286 0.8533 2299137652
2048 1.5238 0.8277 2230140529
2176 1.619 0.8042 2166888975
2304 1.7143 0.7827 2109013064
2432 1.8095 0.7626 2054877563
2560 1.9048 0.7389 1990964437
Any help on what should be the cache size (640M?)
