Re: Performance Tuning Help Questions

On Feb 9, 6:11 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> I have spent the day at work attempting to gather information about the
> datawarehouse
> database to determine what may - or may not need - possible tuning.
>
> I'm not an experienced DBA so I'm at a loss to recognize when a value may be
> out of
> the norm. An example is whether or not 1,330 Redo Log Space Requests
> constitutes a
> large value or a normal value.
>
> Also I'm wondering if it is normal to not have any memory allocated to the
> KEEP and
> RECYCLE buffer pools.
>
> Any comments and suggestions of what I should focus my attention on or also
> look into
> would be appreciated. I didn't get all what I wanted checked out but the
> below is
> what I did get.
>
> I don't know if this would make a difference but the database is bounced on
> Wednedays along with on the weekend so the instance is never up for longer
> than 3 days.
>
> Thanks.
>
> Version Numbers:
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
> PL/SQL Release 10.2.0.1.0 - Production
> CORE 10.2.0.1.0 Production
> TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
> NLSRTL Version 10.2.0.1.0 - Production
>
> NAME VALUE
> ---------------------------------------------------------------- ----------
> buffer busy waits 4288
> free buffer inspected 88629781
>
> *** Redo Log Information ***
>
> Show the size of the Redo Log Buffer
>
> NAME TYPE VALUE
> ------------------------------------ ----------- ---------------------------
> ---
> log_buffer integer 4194304
>
> Calculate Redo Log Buffer Retry Ratio
>
> Redo Log Buffer Retry Ratio
> ---------------------------
> .000328132
>
> Measure how often LGWR waits for Redo Log switch to occur
>
> NAME VALUE
> ---------------------------------------------------------------- ----------
> redo log space requests 1330
>
> Measure Redo Log Buffer Performance per User
>
> no rows selected
>
> Measure Checkpoint activity
>
> event TOTAL_WAITS AVERAGE_WAIT
> -------------------------------------------------- ----------- ------------
> checkpoint completed 7 .14
> enq: KO - fast object checkpoint 76 1.95
> log file switch completion 516 19.37
> latch: checkpoint queue latch 258 .01
>
> Measure Redo Log Activity
>
> event TOTAL_WAITS AVERAGE_WAIT
> -------------------------------------------------- ----------- ------------
> log file parallel write 3658333 .34
> log file switch completion 516 19.37

I agree with the comments by Daniel Morgan and Mark Powell. I agree with Mark's suggested reading list.

It is normal, but possibly not desirable to have the KEEP and RECYCLE buffer pools set at 0. It does not make sense to just set these buffer pools to some arbitrary value, as doing so just wastes memory. You must also carefully determine which objects are best suited to these buffer pools. The objects that you wish to keep in the KEEP buffer pool should be those that are used frequently, yet are also not able to remain in the default buffer cache for very long due to other objects that must later also be read into the default buffer pool. The recycle pool should be used for those objects that are infrequently used, yet have a tendency to flush a large number of other object's blocks from the buffer pool when read. The Oracle documentation and the various books that have been suggested will be helpful.

Paraphrased from the Oracle 10g R2 Performance Tuning Manual: The V$SYSSTAT statistic "redo log space requests" indicates how many times a server process has had to wait for space in the online redo buffer. A significant value for this statistic and the wait events should be used as an indication that checkpoints, DBWR, or archiver activity should be tuned, not LGWR. Increasing the size of the log buffer, in such a case, does not help.

Depending on the amount of work (changes/updates/inserts/deletes) being performed by the server, the value of this statistic should be reasonably close to 0. Reasonable must be determined by taking a close look at the system. The value that you report may be appropriate. How large are your log files?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Feb 12 2007 - 06:05:42 CST