Oracle Database performance related.

Question

I am currently working on 9.2.0.8 Oracle database.I Have some questions related to Performace of Database that too related to Redo logs latches & contention. Answers from real practice will be highly appreciated. please help.

1. My data is currently having 25 redo log files with 2 members in each file. Each member is of size 100m. So Is this worth keeping 25 redo log file each with 2 members (100MB each).
2. My database is 24*7 with a min user of 275 & Max of 650. My database is having mostly SELECT's but very less INSERT/UPDATE/DELETE's . And since 1 month i started obsorving that my database is generating archives on an average of 17GB min to 28GB at MAX. But the LOGSWITCH is taking place on an average every 5-10 min. some times more frequently. And even some times 3 times in a min. But my SPFILE says log_checkpoint_timeout=1800 ( 30 min's).

3. And About Redo log latches & contention, when i isssue:- SELECT name, value FROM v$sysstat WHERE name = 'redo log space requests'; Output:-

       NAME                                                               VALUE
       -------------------------------------------------------------------- ----------
        redo log space requests                                               20422
   (This value is getting increased day by day)
   

4. Where as Oracle recommened's to have the redo log space request close to zero.
5. So i want to know why my database is going for log switch frequently. Is this Because of data Or Becoze of some thing else.
6. My doubt was, If i increase REDO LOG Buffer the Problem may resolve. And i increased redo log buffer from 8MB to 11MB. But i did'nt find much difference.
7. If i increase the size of REDO LOG FILE from 100MB to 200MB, Will it help. Will it help me to reduce the log switching time & bring the value of REDO LOG SPACE REQUEST close to zero.

Answer 1

17GB of logfiles per minute seems pretty high to me. Perhaps one of the tablespaces in your database is still in online backup mode.

Answer 2

It would probably help to look at which sessions are generating lots of redo, and which sessions are waiting on the redo log space the most.

SQL> l
  1  select name, sid, value
  2  from v$sesstat s, v$statname n
  3  where name in ('redo size','redo log space requests')
  4   and n.statistic# = s.statistic#
  5  and value > 0
  6* order by 1,2

Answer 3

Something about the information you supplied doesn't add up - if you were really generating around 20G/min of archive logs, then you would be switching your 100M log files at least 200 times per minute - not the 3 times/minute worst case that you mentioned. This also isn't consistent with your description of "... mostly SELECT's".

In the real world, I wouldn't worry about log switches every 5-10 minutes on average. With this much redo, none of the init parameters are coming into play for switching - it is happening because of the online redo logs filling up. In this case, the only way to control the switching rate is to resize the logs, e.g. doubling the log size will reduce the switching frequency by half.

Answer 4

thanks for answering!! but let me make 1 thing clear that DB is not generating 17GB of Archives per day not per minute.