-
extent checkpoints
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
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
-------------------------------------------------------------
The following is the cache size for the database:
Code:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,344M Std Block Size: 16K
Shared Pool Size: 1,344M Log Buffer: 5,120K
On querying v$enqueue_stat I found the following:
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
Too high value for "CI" lock type.
From a JL article:
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
From Oracle 8 onwards oracle addresses this issue by object based checkpoints instead of extent based checkpoints.
But on the footnote of the article:
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
Yes there are some regular truncate operations that goes on the database.
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:
Code:
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
Quite unusual
Any help on what should be the cache size (640M?)
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Hi
Who are the users who complained about this ?
were they the one who were running truncate command ?
when the tables are being truncated whats the size of these tables ?
regards
Hrishy
-
Basically all the users were complaining.I too myself found out that login to the database was extremely slow
The table were of size 10M
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Hi
Anything in the smon trace in bdump directory ?
10m tables i dont think so that they should cause much problem while truncating.
regards
Hrishy
-
Truncate are not the ones that are problem.But they may be the root of the problem.Signalling data flush calls to smon=>high waits in Cross Interface call.
No, nothing in the smon trace.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Each of the log file sync waits was about a quarter of a second -- if you add that on to the time taken for a parallel query, a truncate or a drop then I doubt that you have anything noticeable.
One approach in reducing log file sync waits is to reduce the size of your log buffer. This makes the log writer more aggressive in writing to the redo logs (in the background) and thise can reduce the (foreground) log file sync waits. What log buffer size do you have?
Another possibility is that you application is commiting/rolling back too frequently.
-
Originally posted by slimdave
One approach in reducing log file sync waits is to reduce the size of your log buffer. This makes the log writer more aggressive in writing to the redo logs (in the background) and thise can reduce the (foreground) log file sync waits. What log buffer size do you have?
But I increased it after facing log buffer waits (sometime ago).
Originally posted by slimdave
Another possibility is that you application is commiting/rolling back too frequently.
That's true. Toomuch commits/commits within loop.Recommended the change to apps team.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Hi
Anything in /var/adm/message file..Ia m just wundering why is your server slow whats the oupt of sar and vmstats when the database was slow ?
regards
Hrishy
-
Originally posted by simply_dba
But I increased it after facing log buffer waits (sometime ago).
That's true. Toomuch commits/commits within loop.Recommended the change to apps team.
Don't forget that log buffer waits are background events -- until they start causing log sync waits they are probably not affecting your foreground processes.
-
Originally posted by hrishy
Hi
Anything in /var/adm/message file..Ia m just wundering why is your server slow whats the oupt of sar and vmstats when the database was slow ?
regards
Hrishy
sar, vmstats quite ok.The main problem is the db is not performing upto it's benchmark.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|