Home > Oracle > Elapsed Checkpoint Time

Elapsed Checkpoint Time


Adieu to 2013 with this small post!  Happy new year to every one.

Oracle controls the incremental checkpoint frequency or interval based on the parameter FAST_START_MTTR_TARGET  or  LOG_CHECKPOINT_INTERVAL.  But, can you see the real checkpoint interval values?  What is the elapsed time between checkpoints?

Yes, you can.  The fixed table X$KCCDI holds SCN information about last checkpoint SCN and current SCN.  Column DICKP_SCN is the last checkpoint SCN and DICUR_SCN is the current SCN.

We get the CURRENT_SCN from v$database as,


SQL> select current_scn from v$database;

CURRENT_SCN

-----------

102707163

And looking at the X$KCCDI fixed tables, you can get the same SCN values.


SQL> select DICKP_SCN,DICUR_SCN from X$KCCDI;

DICKP_SCN        DICUR_SCN

---------------- ----------------

102711050        102711285

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

102711289

Using the SCN_TO_TIMESTAMP function, you can convert the SCN values to time stamps.


SQL> select scn_to_timestamp(DICKP_SCN), scn_to_timestamp(DICUR_SCN) from X$KCCDI;

SCN_TO_TIMESTAMP(DICKP_SCN)                                                 SCN_TO_TIMESTAMP(DICUR_SCN)

--------------------------------------------------------------------------- ---------------------------------------------------------------------------

31-DEC-13 01.18.54.000000000 AM                                             31-DEC-13 01.31.31.000000000 AM

Now, it is easy to find the elapsed time of last SCN.


SQL> select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:1:51.000000000

The checkpoint elapsed time is depends on the load on the database.    When the database is idle, it took 3 minutes between the checkpoints – but that is not a hard limit.


SQL> select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:03:00.000000000

SQL>  select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:00:00.000000000

But the elapsed time went down to few seconds when I increased the load.


SQL> select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:00:20.000000000

SQL>  select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:00:00.000000000

 

SQL>  select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:00:03.000000000

SQL> select scn_to_timestamp(DICUR_SCN) - scn_to_timestamp(DICKP_SCN)  from X$KCCDI;

SCN_TO_TIMESTAMP(DICUR_SCN)-SCN_TO_TIMESTAMP(DICKP_SCN)

---------------------------------------------------------------------------

+000000000 00:00:00.000000000

And a big good bye to 2013!

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: