DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: extent checkpoints

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    Question 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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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
  •  


Click Here to Expand Forum to Full Width