Oracle Checkpoints
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Oracle Checkpoints

  1. #1
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Hi Mates,

    My query is about checkpoints. Ia m running oracle 7.3.4 in Sun Solaris. According to oracle checkpotints occures primiraily when (While database working normally)

    1. When a logswitch occures
    2. based on the parameter value LOG_CHECKPOINT_INTERVAL
    3. based on the parameter value LOG_CHECKPOINT_TIMEOUT


    Well, now my redo log size is 100MB
    LOG_CHECKPOINT_TIMEOUT = 0
    LOG_CHECKPOINT_INTERVAL=20000000 (Which is grater than the size of the redolog file)

    Normally, there are 15 - 20 arcgive log files created every day. ( 15 - 20 logswitches). Bur I found the value of checkpoints is 700 for today. LOG_CHECKPOINT_TIMEOUT=0, ie Time based checkpoint is disabled. LOG_CHECKPOINT_INTERVAL is higher than redo log size. Again 15 -20 logswitches.

    My question, What is the reason for this many number of checkpoints.??

    Please hel me.


    Regards


    Thomas

    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  2. #2
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    How did you find the checkpoint number? and what is your redo log buffer size?
    -nagarjuna

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Is there any possibility that following commands are being issued manually or by some jobs..

    ALTER SYSTEM SWITCH LOGFILE;
    or
    ALTER SYSTEM CHECKPOINT;

    Sanjay

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    quote
    ----------
    nagarjuna How did you find the checkpoint number? and what is your redo log buffer size?
    -------------

    u can find checkpoint number from the following query

    select distinct checkpoint_change# from v$datafile;

    we can get from check point details from alert log file also if u have set the
    LOG_CHECKPOINTS_TO_ALERT = TRUE .

    just count how many check points are happening in a single day .
    siva prakash
    DBA

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    checkpoint from log switch and LOG_CHECKPOINTS_TO_ALERT are so called official checkpoints or interval checkpoints, internally Oracle performs many incremental checkpoints (from Oracle 8.x) which is quite difficult to follow (at least I dont know where are these incrmenetal checkpoints recorded)

    Only way I know if incremental checkpoint has occured is by looking v$sysstat DBWR checkpoint buffers written

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    checkpoints

    Hi all

    Thanks for the replies.

    Well, I think my calculation was wrong. I used the value DBWR checkpoints in t V$SYSSTAT to calculate. But later I understand that these are the number of times DBWR requested for a checkpoint, not an actual checkpoint.

    According to Mr. Pando, These are the incremental check points. I have no idea about that..!

    I appreciate your replies.

    Regards

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hey note i said from Oracle 8.x

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Cool


    Thanks pando

    Thomas
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    checkpoints

    Hi Mates

    Checkpoints still not ends here!

    Now I found big values (compared to the number fo checkpoints) in 'DBWR checkpoints' in v$sysstat. These are the requests by the DBWR for a checkpoint. Why the DBWR is requesting for so many checkcpoints? Is this high value is a sysmptom of a problem? How DBWR decide to request for a check point? If this is a problem, how can I correct it?

    Thanks


    Thomas P S
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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