Printable View
Hi Oracle Guru's
How to size the redo logs. What is the significance of LOG_CHECKPOINT_TIMEOUT AND LOG_CHECKPOINT_INTERVAL and how to set these parameters?
Presently I have 3 redolog groups, which 2 members each. Size is 5M. Would like to keep my log switch for every 30-60 Mins.
Please guide me in this regard.
Thanks In Advance.
Regards
LOG_CHECKPOINT_TIMEOUT and LOG_CHECKPOINT_INTERVAL have nothing to do with how often your redo logs switch. See http://technet.oracle.com/docs/produ...recov.htm#9764 , http://technet.oracle.com/docs/produ...0_io.htm#22462 , http://technet.oracle.com/docs/produ...ch190.htm#5517 , http://technet.oracle.com/docs/produ...h189.htm#55753 for details.
Hi, 10th Dec 2001 18:32 hrs Mumbai
I need more information to go in detail to explain you.
The Log switching not only depends upon the interval you are going to set definitely the type of system you are Using OLTP or DSS and volume of transactions (modifications) in DB also to be taken care.?
Also read this Metalink Article which will clear all your doubts
=======================================
Purpose
=====
This article provides the Database Administrator a better understanding of checkpoint processing and a description of four key initialization parameters used for checkpoint tuning:
CHECKPOINT_PROCESS
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINTS_TO_ALERT
It also explains how to interpret and handle checkpoint errors ....
Checkpoint not Complete
Cannot Allocate New Log
.... reported in the ALERT.LOG file.
Contents
======
What is a Checkpoint ?
The checkpoint process
Checkpoints and Performance
Redo logs and Checkpoint
Instance parameters related with the checkpoint process
Understanding Checkpoint Error messages
Oracle Release Information
8i Parameters that influence Checkpoints
What is a Checkpoint ?
===============
A Checkpoint is a database event which synchronizes the modifies data blocks in memory with the datafiles on disk. It offers Oracle the means for ensuring the consistency of data modified by transactions. The mechanism of writing modified blocks on disk in Oracle is not synchronized with the commit of the corresponding transactions.
A checkpoint has two purposes: (1) to establish data consistency, and (2) enable faster database recovery. How is recovery faster? Because all database changes up to the checkpoint have been recorded in the datafiles, making it unnecessary to apply redo log entries prior to the checkpoint. The checkpoint must ensure that all the modified buffers in the cache are really written to the corresponding datafiles to avoid the loss of data which may occur with a crash (instance or disk failure).
Oracle writes the dirty buffers to disk only on certain conditions:
A shadow process must scan more than one-quarter of DB_BLOCK_BUFFER
Every three seconds
When a checkpoint is produced
A checkpoint is realized on five types of events:
At each switch of the redo log files
When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
When the size in bytes corresponding to:
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
is written on the current redo log file.
Directly by the ALTER SYSTEM SWITCH LOGFILE command.
Directly with the ALTER SYSTEM CHECKPOINT command
During a checkpoint the following occurs:
---------------------------------------------
The database writer (DBWR) writes all modified database blocks in the buffer cache back to datafiles
Log writer (LGWR) updates both the controlfile and the datafiles to indicate when the last checkpoint occurred (SCN).
The checkpoint process
---------------------------
The CHECKPOINT_PROCESS init.ora parameter determines whether or not the optional CKPT background process will be started to perform LGWRs tasks during checkpoint operations of updating the datafile headers. LGWR is then free to perform its' primary function flushing the redo log buffer to the online redo logs.
The CKPT process can improve performance significantly and decrease the amount of time users have to wait for a checkpoint operation to complete.The overhead associated with starting another background process is not significant when compared to the performance benefit to be gained by enabling CKPT, therefore, Oracle recommends always enabling the checkpoint process (CKPT).
Please note that this parameter is obsolete starting with Oracle8 and now the CKPT process is always started as part of the background processes.
Checkpoints and Performance
----------------------------------
Checkpoints present a tuning dilemma for the Database Administrator. Frequent checkpoints will enable faster recovery, but can cause performance degradation. How then should the DBA address this?
Depending on the number of datafiles in a database, a checkpoint can be a highly resource intensive operation, since all datafile headers are frozen during the checkpoint. There is a performance trade-off regarding frequency of checkpoints. More frequent checkpoints enable faster database recovery after a crash. This is why some customer sites which have a very low tolerance for unscheduled system downtime will often choose this option. However, the performance degradation of frequent checkpoints may not justify this philosophy in many cases. Let's assume the database is up and running 95% of the time, and unavailable 5% of the time from infrequent instance crashes or hardware failures requiring database recovery. For most customer sites, it makes more sense to tune for the 95% case rather than the rare 5% downtime.
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 necessitates well-sized redo logs to avoid unnecessary checkpoints as a result of frequent log switches. The alert log is a valuable tool for monitoring the rate that log switches occur, and subsequently, checkpoints occur. Oracle recommends sizing the online redo logs such that switches occur no more than once per hour. The following is an example of quick log switches from the alert log:
Fri May 16 17:15:43 1997
Thread 1 advanced to log sequence 1272
Current log# 3 seq# 1272 mem# 0: /prod1/oradata/logs/redologs03.log
Thread 1 advanced to log sequence 1273
Current log# 1 seq# 1273 mem# 0: /prod1/oradata/logs/redologs01.log
Fri May 16 17:17:25 1997
Thread 1 advanced to log sequence 1274
Current log# 2 seq# 1274 mem# 0: /prod1/oradata/logs/redologs02.log
Thread 1 advanced to log sequence 1275
Current log# 3 seq# 1275 mem# 0: /prod1/oradata/logs/redologs03.log
Fri May 16 17:20:51 1997
Thread 1 advanced to log sequence 1276
Current log# 1 seq# 1276 mem# 0: /prod1/oradata/logs/redologs01.log
If redo logs switch every 3 minutes, you will see performance degradation. This indicates the redo logs are not sized large enough to efficiently handle the transaction load.
Instance parameters related with the checkpoint process
LOG_CHECKPOINT_INTERVAL
====================
The LOG_CHECKPOINT_INTERVAL init.ora parameter controls how often a checkpoint operation will be performed based upon the number of operating system blocks that have been written to the redo log.If this value is larger than the size of the redo log, then the checkpoint will only occur when Oracle performs a log switch from one group to another, which is preferred.
NOTE for Oracle 8.1
Starting with Oracle 8.1, LOG_CHECKPOINT_INTERVAL will be interpreted to mean that the incremental checkpoint should not lag the tail of the log by more than LOG_CHECKPOINT_INTERVAL number of redo blocks.
On most Unix systems the operating system block size is 512 bytes. This means that setting LOG_CHECKPOINT_INTERVAL to a value of 10,000 (the default setting), causes a checkpoint to occur after 5,120,000 (5M) bytes are written to the redo log. If the size of your redo log is 20M, you are taking 4
checkpoints for each log.
LOG_CHECKPOINT_INTERVAL influences when a checkpoint occurs, which means careful attention should be given to the setting of this parameter, keeping it updated as the size of the redo log files is changed. The checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that if the system crashes, more time will be needed for the database to recover. Shorter checkpoint intervals mean that the database will recover more quickly, at the expense of increased resource utilization during the checkpoint operation. This parameter also impacts the time required to complete a database recovery operation during the roll forward phase of recovery. The actual recovery time is dependent upon this time, and other factors, such as the type of failure (instance or system crash, media failure, etc.), and the number of archived redo logs which need to be applied.
LOG_CHECKPOINT_TIMEOUT
===================
The LOG_CHECKPOINT_TIMEOUT init.ora parameter controls how often a checkpoint will be performed based on the number of seconds that have passed since the last checkpoint.
NOTE for Oracle 8.1
LOG_CHECKPOINT_TIMEOUT will be interpreted to mean that the incremental checkpoint should be at the log position where the tail of the log was LOG_CHECKPOINT_TIMEOUT seconds ago.
Checkpoint frequency impacts the time required for the database to recover from an unexpected failure. Longer intervals between checkpoints mean that more time will be required during database recovery.
Oracle recommends using LOG_CHECKPOINT_INTERVAL to control the checkpoint interval rather than LOG_CHECKPOINT_TIMEOUT, which will initiate a checkpoint every "n" seconds, regardless of the transaction frequency.This can cause unnecessary checkpoints in cases where transaction volumes vary.Unnecessary checkpoints must be avoided whenever possible for optimal performance.
It is a misconception that setting LOG_CHECKPOINT_TIMEOUT to a given value will initiate a log switch at that interval, enabling a recovery window used for a stand-by database configuration. Log switches cause a checkpoint, but a checkpoint does not cause a log switch.The only way to cause a log switch is manually with ALTER SYSTEM SWITCH LOGFILE or resizing the redo logs to cause more frequent switches.This is controlled by operating system blocks, not a timed interval.
LOG_CHECKPOINTS_TO_ALERT
====================
The LOG_CHECKPOINTS_TO_ALERT init.ora parameter, when set to a value of TRUE, allows you to log checkpoint start and stop times in the alert log.This is very helpful in determining if checkpoints are occurring at the optimal frequency and gives a chronological view of checkpoints and other database activities occurring in the background.
Understanding Checkpoint Error messages
Sometimes, you can see in your alert.log file, the following corresponding
messages: “Cannot allocate new log” and “Checkpoint not complete”.
Thread 1 advanced to log sequence 248
Current log# 2 seq# 248 mem# 0:
/prod1/oradata/logs/redologs02.log
Thread 1 cannot allocate new log, sequence 249
Checkpoint not complete
This message indicates that Oracle wants to reuse a redo log file, but the corresponding checkpoint associated is not terminated. In this case, Oracle must wait until the checkpoint is completely realized. This situation may be encountered particularly when the transactional activity is important.
This situation may also be checked by tracing two statistics in the BSTAT/ESTAT report.txt file. The two statistics are:
Background checkpoint started.
Background checkpoint completed.
These two statistics must not be different more than once. If this is not true, your database hangs on checkpoints. LGWR is unable to continue writing the next transactions until the checkpoints complete.
Three reasons may explain this difference:
A frequency of checkpoints which is too high
A checkpoints are starting but not completing
A DBWR which writes too slowly.
The number of checkpoints completed and started as indicated by these statistics should be weighed against the duration of the bstat/estat report.Keep in mind the goal of only one log switch per hour, which ideally should equate to one checkpoint per hour as well.
The way to resolve incomplete checkpoints is through tuning checkpoints and logs:
Give the checkpoint process more time to cycle through the logs
Add more redo log groups
Increase the size of the redo logs
Reduce the frequency of checkpoints
Increase LOG_CHECKPOINT_INTERVAL
Increase size of online redo logs
Set LOG_CHECKPOINT_TIMEOUT = 0
This disables the checkpointing based on time interval
Oracle Release Information
==================
The CKPT process is optional in lower versions of Oracle7, but is mandatory in Oracle8.
In versions 7.0 - 7.3.2, the CKPT is an optional background process which is enabled by setting CHECKPOINT_PROCESS=TRUE in init.ora.
In versions 7.3.3 and 7.3.4, the CKPT process will be started automatically regardless of the CHECKPOINT_PROCESS setting if either of the following conditions exist:
a large value for DB_FILES (50 or higher)
a large value for DB_BLOCK_BUFFERS (10,000 or higher)
In version 8.0.3 and higher, the CKPT process is always enabled. Attempting to set CHECKPOINT_PROCESS in the init.ora will give the following error:
LM-101 "unknown parameter name checkpoint_process"
8i Parameters that influence Checkpoints
In Oracle8i, there are a couple of things that cause checkpointing to occur. They are:
FAST_START_IO_TARGET init.ora parameter
LOG_CHECKPOINT_TIMEOUT init.ora parameter
LOG_CHECKPOINT_INTERVAL init.ora parameter
Size of the smallest log
Oracle continually calculates where the checkpoint position must be in order to satisfy these things, and writes dirty buffers in order of age to advance the checkpoint position such that all of these targets are met. When it advances the checkpoint, it does not advance it to the end of the log. Rather, it advances it far enough to satisfy all of the targets corresponding to the items above.
FAST_START_IO_TARGET
Oracle continually calculates the number of IO operations that would be required to perform roll-forward should the database fail. If this number is greater than FAST_START_IO_TARGET, Oracle writes dirty buffers and advances the checkpoint to satisfy this target. FAST_START_IO_TARGET is only useable on 8i Entreprise Edition.
LOG_CHECKPOINT_TIMEOUT
Assume this is set to 60. Oracle continually calculates the address of the redo record that was written 60 seconds ago. In order to satisfy this parameter, the checkpoint position must advance at least as far as this redo record. Should the checkpoint position point to a redo record older than this target position (written over 60 seconds ago), Oracle writes dirty buffers and advances the checkpoint until it points at a redo record written less than 60 seconds ago. Should the checkpoint position point to a redo record newer than this target position (written less than 60 seconds ago), Oracle does nothing to satisfy this target for it is already satisfied.
LOG_CHECKPOINT_INTERVAL
Assume this is set to 1000. Oracle continually calculates the address of the redo record that was written 1000 records (OS blocks) ago. In order to satisfy this parameter, the checkpoint position must advance at least as far as this redo record. Should the checkpoint position point to a redo record written earlier than this target position (written over 1000 records before the record at the end of the log), Oracle writes dirty buffers and advances the checkpoint until it points at a redo record written less than 1000 records ago. Should the checkpoint position point to a redo record newer than this target position (written less than 1000 records ago), Oracle does nothing to satisfy this target for it is already satisfied.
Size of smallest REDO LOG
Oracle forces LOG_CHECKPOINT_INTERVAL to be no greater than 90% of the size of the smallest log file. This guarantees that when Oracle tries to do a log switch because it has filled a redo log file, the checkpoint position will have advanced into this current log. Should you only have two logs (the worst case), Oracle does not have to stall while the checkpoint advances out of the soon to be reused log.
So, when Oracle does a checkpoint due to a log switch, it does not really have to do anything. The checkpoint position is already advanced out of the soon to be reused log. The log switch checkpoint does not actually do anything right away. It is a "zero priority" checkpoint: it does not cause any writes to happen on its own, but is marked as complete only when checkpointing for other reasons causes the checkpoint position to advance beyond the rba that marked the end of the log when the checkpoint was initiated.
Example
Assume you have two logs, A and B. When B fills, Oracle initiates a zero priority checkpoint to advance the checkpoint position to the end of log B. However, because the checkpoint position is no more than 90% of the size of the smallest log behind the end of the log, the checkpoint position is roughly 10% of the way into log B when this checkpoint is initiated. There is no reason to do any writes, as log A can be reused immediately. So Oracle does nothing and lets the checkpoint position continue to advance based upon other factors. When the other factors cause the checkpoint position to advance beyond the end of log B, the zero priority checkpoint previously initiated is declared complete and you see the checkpoint complete message.
Note:
If you manually switch log files, the checkpoint position may not have advanced out of the log to be reused. In this case, Oracle initiates a real checkpoint and advances the checkpoint position up to the rba that marked the end of the log when the checkpoint was initiated.
===================================
Cheers
Padmam
Hi Padmam,
Thank you very much for your input. Here are some more details for your reference.
My system is running OLTP applications. More inserts and updates. Details are following :
My One week Log switches :
Date #Arch Files Size Mb
--------------- ----------- -----------
04-DEC-2001 21 105
05-DEC-2001 50 250
06-DEC-2001 39 195
07-DEC-2001 67 335
10-DEC-2001 18 90
11-DEC-2001 124 620
----------- -----------
319 1595
Log switch details from alert log :
Tue Dec 11 10:01:58 2001
Thread 1 advanced to log sequence 311849
Current log# 3 seq# 311849 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 311849 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Tue Dec 11 10:02:56 2001
Thread 1 advanced to log sequence 311850
Current log# 4 seq# 311850 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 311850 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Tue Dec 11 10:04:23 2001
Thread 1 advanced to log sequence 311851
Current log# 5 seq# 311851 mem#0: /u01/u05/oradata/log7massbej2.dbf
Current log# 5 seq# 311851 mem#1: /u01/app/oracle/product/8.1.6/dbs/log8massbej2.dbf
Tue Dec 11 10:05:21 2001
Thread 1 advanced to log sequence 311852
Current log# 3 seq# 311852 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 311852 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Tue Dec 11 10:06:22 2001
Thread 1 advanced to log sequence 311853
Current log# 4 seq# 311853 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 311853 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Tue Dec 11 10:07:27 2001
Thread 1 advanced to log sequence 311854
Current log# 5 seq# 311854 mem#0: /u01/u05/oradata/log7massbej2.dbf
Now, with the above details, can you focus some light, to determine the size of the redo log file, so that my log switchs occur every 30 mins.
Thanks in Advance
Regards
I am curious, for what reason do you want the log switch to be made every 30th minute? Why that precission? It is OK if you fix the size of the redologs in such a way that you get about 20-30 log switches per day.Quote:
Now, with the above details, can you focus some light, to determine the size of the redo log file, so that my log switchs occur every 30 mins.
Hi I'm new to the forum.
The methodology that I have been advised to use is to set your redo logs large enough NOT to fill up during your allotted time.
Set the log checkpopint timeout to 0 (disabled)
Set the log checkpoint interval to a value larger than the largest log. (this will prevent checkpoints occourring when not required)
Then set rman to backup then delete your archived logs when required (every 30 mins). In doing so rman will instgate a log switch, archive the current log and then back it up and delete it. A log switch will trigger a checkpoint.
This may affect recovery time.
I have read that Oracle suggests a log switch approx every 60 mins
Hi all,
I apologize for delay in response.
Julian:
You can observe data provided in my previous posts, my log switches are occurring very frequently, especially on 11-DEC-01, you can observe there is 124 switches occurred, it is just for two hours simulation run on my test database. You can just imagine if it is 24/7 database. This is the reason I would like to keep my log switches for every 30Mins or even more than that. If I am not wrong, due to this my archive logs also will be very high.
Once again I am providing the details:
My One week Log switches :
Date #Arch Files Size Mb
--------------- ----------- -----------
04-DEC-2001 21 105
05-DEC-2001 50 250
06-DEC-2001 39 195
07-DEC-2001 67 335
10-DEC-2001 18 90
11-DEC-2001 124 620 *******
----------- -----------
319 1595
I see, you have a high activity database. Is it always like that?
You might want to consider using LOG_CHECKPOINT_TIMEOUT.
Yes ! My Database is High activity database. It is for telecommunications, always the trafic is very high. Please guide me how to determine the size for redo log files, so that i can reduce my log switches, as well as my archive log file generation.
Presently my test database size is :
FILE TYPE MB
---------------- --------
DB Files Total : 7,070
Redo Log Total: 15
--------
sum 7,085
and I have 3 redo log groups with 2 members each of 5M size.
OLTP Application running, HIGH INSERT, UPDATE activities.
Please let me know you want any further information.
INIT.ORA PARAMETERS:
#log_checkpoint_interval = 10000
log_checkpoint_interval = 5242880 - changed
#log_checkpoint_timeout = 1800
log_checkpoint_timeout = 0 - changed
processes = 300
log_buffer = 163840
Thanks In Advance.
I detect small LOG_BUFFER. Tell me first what you get from that query:
select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';
If you get a number more than 1, let's start with ecreasing the LOG_BUFFER. Set it then to 1M.
I am also interested in this subject.Quote:
Originally posted by julian
I see, you have a high activity database. Is it always like that?
You might want to consider using LOG_CHECKPOINT_TIMEOUT.
How is the LOG_CHECKPOINT_TIMEOUT going to cause a log switch? Will it not only activate a checkpoint?
LOG_CHECKPOINT_TIMEOUT will not cause a log switch. I think julian was merely mentioning it so the poster would be protected if they had really large redo logs but with periods of very low activity.
LOG_CHECKPOINT_TIMEOUT will not cause a log switch. Read Jeff's reply. It might be considered so that no buffer will remain dirty in the cache for more than X seconds.Quote:
I am also interested in this subject.
How is the LOG_CHECKPOINT_TIMEOUT going to cause a log switch? Will it not only activate a checkpoint?
Hi Julian,
Here is the ratio :
select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';
Ratio
----------
1.41768764
SQL>
As you suggested I am going to increase my LOG_BUFFER to 1 M. And let you Know the result.
Thanks
Hi Julian,
I had changed my log_buffer to 1M, and restarted the database. Still my log switches are more?
alter database open
Thu Dec 13 12:01:33 2001
Thread 1 opened at log sequence 312181
Current log# 5 seq# 312181 mem#0: /u01/u05/oradata/log7massbej2.dbf
Current log# 5 seq# 312181 mem#1: /u01/app/oracle/product/8.1.6/dbs/log8massbej2.dbf
Successful open of redo thread 1.
Thu Dec 13 12:01:33 2001
SMON: enabling cache recovery
SMON: enabling tx recovery
Thu Dec 13 12:01:35 2001
Completed: alter database open
Thu Dec 13 12:07:04 2001
Thread 1 advanced to log sequence 312182
Current log# 3 seq# 312182 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 312182 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Thu Dec 13 12:07:44 2001
Thread 1 advanced to log sequence 312183
Current log# 4 seq# 312183 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 312183 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Thu Dec 13 12:08:23 2001
Thread 1 advanced to log sequence 312184
Current log# 5 seq# 312184 mem#0: /u01/u05/oradata/log7massbej2.dbf
Current log# 5 seq# 312184 mem#1: /u01/app/oracle/product/8.1.6/dbs/log8massbej2.dbf
Thu Dec 13 12:09:03 2001
Thread 1 advanced to log sequence 312185
Current log# 3 seq# 312185 mem#0: /u01/u03/oradata/massbej2log5massbej2.dbf
Current log# 3 seq# 312185 mem#1: /u01/app/oracle/product/8.1.6/dbs/log6massbej2.dbf
Thu Dec 13 12:09:43 2001
Thread 1 advanced to log sequence 312186
Current log# 4 seq# 312186 mem#0: /u01/u04/oradata/massbej2log2massbej2.dbf
Current log# 4 seq# 312186 mem#1: /u01/u04/oradata/massbej2masslog2massbej2.dbf
Thu Dec 13 12:10:22 2001
Ratio is :
Ratio
----------
1.87338914
These results are for taken, 15 mins later to my simulator start time.
Thanks
The redo log is small when compared to the SGA. Now it is 1M, right? More than 1M would not make sense. I suggest you increase now the size of the log files, can you do that, need help? How big are they now? How many groups and memebrs per group do you have?
Hi Julian,
Yes, I can do this, please tell me If I am wrong,
would like to increase my redo log files this way, first of all, I will create another 3 big redo log groups with 2 members each, let us say 5OM each group. And then I will switch all my old logs to new, by using alter system switch logfile and then I will drop my old redo log groups. Is this correct?
Presently I am having 3 redo log groups with 2 members each, size 5M.
Is it Okay! To resize my redologs to 50M, can it be reduce my frequent log switches? What is your recommended size?
Thanks In Advance.
Yes, you have the correct procedure. Add new log groups, swith logfiles so the small logs are not "active" or "current", and the drop the old log groups.
You can start with 50M and see how frequent your logswitch occurs.
Hi,
I have created 3 new redo log groups with 2 members each, with size of 100M.
Now my ratio is :
select (req.value*5000)/entries.value "Ratio"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';
Ratio
----------
.027092121
And Log switches are :
Fri Dec 14 14:42:52 2001
Thread 1 advanced to log sequence 312260
Current log# 7 seq# 312260 mem#0: /u01/u03/oradata/massbej2/massbej2_log7a.dbf
Current log# 7 seq# 312260 mem#1: /u01/u04/oradata/massbej2/massbej2_log7b.dbf
Fri Dec 14 14:56:31 2001
Thread 1 advanced to log sequence 312261
Current log# 8 seq# 312261 mem#0: /u01/u03/oradata/massbej2/massbej2_log8a.dbf
Current log# 8 seq# 312261 mem#1: /u01/u04/oradata/massbej2/massbej2_log8b.dbf
Fri Dec 14 15:11:13 2001
Thread 1 advanced to log sequence 312262
Current log# 6 seq# 312262 mem#0: /u01/u03/oradata/massbej2/massbej2_log6a.dbf
Current log# 6 seq# 312262 mem#1: /u01/u04/oradata/massbej2/massbej2_log6b.dbf
Now, I am happy with these results. One thing I would like know, is still required to increase my log buffer? presently it is 1M.
Thanks in Advance.
Probably not. In fact, very seldom (I might even say never) there is any use of a LOG_BUFFER > 1M.Quote:
Now, I am happy with these results. One thing I would like know, is still required to increase my log buffer? presently it is 1M.
Could you please tell me whet you get now from:
select (req.value*5000)/entries.value "Ratio (<1 on OK)"
from v$sysstat req, v$sysstat entries
where req.name = 'redo log space requests'
and entries.name = 'redo entries';
At the same time, you may run the following SQLs to see if something else might be wrong:
select (sum(pins)-sum(reloads))/sum(pins) as "LIBRARY CACHE HIT RATIO"
from v$librarycache;
select (sum(gets)-sum(getmisses))/sum(gets) as "ROW CACHE HIT RATIO"
from v$rowcache;
select gethitratio "GETHITRATIO >= .95 on OK" from v$librarycache where namespace = 'SQL AREA';
select (sum(gets)-sum(misses))*100/sum(gets) as "Latch Hit Ration (> 99 OK)" from v$latch;
select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
select sum(waits)* 100 / sum(gets) "RATIO < 5 on OK", sum(waits) "WAITS", sum(gets) "GETS "
from v$rollstat;
select class, count from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');
select sum(count) as "All waits" from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');
select sum(value) "Data Requests" from v$SYSSTAT
where name in ('db block gets', 'consistent gets');
Hi
Here are the results :
1.select (sum(pins)-sum(reloads))/sum(pins) as "LIBRARY CACHE HIT RATIO"
from v$librarycache;
LIBRARY CACHE HIT RATIO
-----------------------
.999986486
2.select (sum(gets)-sum(getmisses))/sum(gets) as "ROW CACHE HIT RATIO"
from v$rowcache;
ROW CACHE HIT RATIO
-------------------
.931820377
3. select gethitratio "GETHITRATIO >= .95 on OK" from v$librarycache where namespace = 'SQL AREA';
GETHITRATIO >= .95 on OK
------------------------
.998493985
4. select 1 - (phy.value / (cur.value + con.value)) "BUFFER HIT RATIO (> 0.90 OK)"
from v$sysstat cur, v$sysstat con, v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads';
BUFFER HIT RATIO (> 0.90 OK)
----------------------------
.999853683
5.select sum(waits)* 100 / sum(gets) "RATIO < 5 on OK", sum(waits) "WAITS", sum(gets) "GETS "
from v$rollstat;
RATIO < 5 on OK WAITS GETS
--------------- ---------- ----------
.00082572 4 484426
6.select class, count from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');
CLASS COUNT
------------------ ----------
system undo header 0
system undo block 0
undo header 860
undo block 1069
7.select sum(count) as "All waits" from v$WAITSTAT where class in
('undo header','undo block','system undo header',
'system undo block');
All waits
----------
1929
8. select sum(value) "Data Requests" from v$SYSSTAT
where name in ('db block gets', 'consistent gets');
Data Requests
-------------
13963115
Thanks in Advance
Very good values. You have very well written application GETHITRATIO is almost 1.
Since we started, let's check the sorts and the latch contention:
select name, value from v$sysstat where name like '%sort%';
select (sum(gets)-sum(misses))*100/sum(gets) as "Latch Hit Ration (> 99 OK)" from v$latch;
Hi
1.select name, value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 47292
sorts (disk) 6
sorts (rows) 115268
2. select (sum(gets)-sum(misses))*100/sum(gets) as "Latch Hit Ration (> 99 OK)" from v$latch;
Latch Hit Ration (> 99 OK)
--------------------------
99.6109578
Thank You Very much for your attention.
You are doing just fine here too. What values do you have for sort_area_size and sort_area_retained_size? You have even "too high" sort ratio. You may even decrease sort_area_size if you need the memory anywhere else but don't bother if you have the RAM.
Hi
Here are details:
$ prtconf
System Configuration: Sun Microsystems sun4u
Memory size: 1024 Megabytes
my sort_area_size is 165536
sort_area_retained_size is 165536
Thanks