Masood's Blog

it is me

2005-11-22T08:53:00.000-08:00

it is me

Redo logs for oracle8i versus checkpoint

2005-11-18T21:47:00.000-08:00

Redo logs and Checkpoint
A checkpoint occurs at every log switch. If a previous checkpoint is already in progress, the checkpoint forced by the log switch will override the current checkpoint.
This necessiates well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches.

Experts say u must change redologs every 20 minutes.
We have a well tuned redolog if we change once per hour.

If we increase the redo files then we can decrease performance by 2 ways:

Bigger files are slower
Copying bigger files to archivelog take more time.

Checkpoint plays a very important role in database activity. So DBAs must have to monitor its efficiency during peak database activities. Log switches cause a checkpoint, but checkpoint does not cause a log switch.

A checkpoint is the act of flushing modified, cached database blocks to disk. Normally, when we make a change to a block -- the modifications of that block are made to a memory copy of the block. When we commit -- the block is not written (but the REDO LOG is -- that makes it so we can "replay" the transaction in the event of a failure). Eventually, the system will checkpoint the modified blocks to disk.

Checkpoint synchronizes the datablock in memory with the datafiles on disk. When a checkpoint occurs:

DBWR writes all modified database blocks from the buffer cache to datafiles.

                 (DBWR)

BUFFER CACHE-----------------------------> DATAFILES

LGWR also updates both the controlfile and datafiles to indicate last SCN (checkpoint)

                 (LGWR)

Record SCN Number -------------------------> CONTROLFILE & DATAFILES

CKPT process performs the operation of LGWR if it is enabled. CKPT is enable as default in version 8.0

Checkpoint occurs:

At redo log switch.
LOG_CHECKPOINT_TIMEOUT has expired.
LOG_CHECKPOINT_INTERVAL has reached.
Forcefully by DBA.

Log_checkpoint_interval is defined in terms of OS Block size.
Suppose the following setting is defined in database environment :-

LOG_CHECKPOINT_INTERVAL = 10000
OS block size = 512 bytes
Redo log file Size = 20M

Calculation of checkpoint = (20 * 1024*1024)/(512*10000) = 4

Four checkpoints will be raised per redo log file . One checkpoint will be raised when (10000*512) bytes are to be written by the LGWR from cache to redo log files.

If we will define log_checkpoint_interval = 0 (zero), it means we are setting this value to infinity and causes the parameter to be ignored.
If the value of the (log_checkpoint_interval * OS BLOCKSIZE) > redolog file size , checkpoint will occur at the redolog switch.
You can set this parameter dynamically by alter system as

SQL> ALTER SYSTEM SET LOG_CHECKPOINT_INTERVAL=100000;

The error "checkpoint not complete ".What does it indicate ?

It means Oracle is ready to recycle the redo logs but it can not because the checkpoint in the previous log is still in progress.
We can also query the v$sysstat system view to determine the value of background_checkpoint_completed and background_checkpoint_started as

SQL > select name,value from sys.v$sysstate where name like 'background checkpoints%';

If the difference of these two values are grater than 1 you must do :

Add more redo log groups or increase the size of redo logs.
Reduce the frequency of checkpoints by increasing LOG_CHECKPOINT_INTERVAL.

Multiversion Concurrency Control

2005-11-18T07:29:00.000-08:00

Oracle automatically provides read consistency to a query so that all the data that the query sees comes from a single point in time (statement-level read consistency). Oracle can also provide read consistency to all of the queries in a transaction (transaction-level read consistency).
Oracle uses the information maintained in its rollback segments to provide these consistent views. The rollback segments contain the old values of data that have been changed by uncommitted or recently committed transactions. Image below shows how Oracle provides statement-level read consistency using data in rollback segments.

As a query enters the execution stage, the current system change number (SCN) is determined; in image, this system change number is 10023. As data blocks are read on behalf of the query, only blocks written with the observed SCN are used. Blocks with changed data (more recent SCNs) are reconstructed from data in the rollback segments, and the reconstructed data is returned for the query. Therefore, each query returns all committed data with respect to the SCN recorded at the time that query execution began. Changes of other transactions that occur during a query's execution are not observed, guaranteeing that consistent data is returned for each query.

In rare situations, Oracle cannot return a consistent set of results (often called a snapshot) for a long-running query. This occurs because not enough information remains in the rollback segments to reconstruct the older data. Usually, this error is produced when a lot of update activity causes the rollback segment to wrap around and overwrite changes needed to reconstruct data that the long-running query requires.

In this event, error 1555 will result:

ORA-1555: snapshot too old (rollback segment too small) .
We can avoid this error by creating more or larger rollback segments. Alternatively, long-running queries can be issued when there are few concurrent transactions, or you can obtain a shared lock on the table you are querying, thus prohibiting any other exclusive locks during the transaction.

Let's blog

2005-10-25T18:48:00.000-07:00

Hey Guys.............. I would like it to be a technical blog...............
I will be comming up with a lot more