Re: Performance Tuning Help Questions

On Feb 9, 7:39 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Dereck L. Dietz 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
>
> > *** Buffer Cache Numbers ***
>
> > Buffer Cache Hit Ratio
> > ----------------------
> > .926011193
>
> > NAME VALUE
> > ---------------------------------------------------------------- ----------
> > buffer busy waits 4288
> > free buffer inspected 88629781
>
> > *** Buffer Pool Information ***
>
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ---------------------------
> > ---
> > sga_max_size big integer 5904M
>
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ---------------------------
> > ---
> > db_cache_size big integer 2960M
>
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ---------------------------
> > ---
> > db_keep_cache_size big integer 0
>
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ---------------------------
> > ---
> > db_recycle_cache_size big integer 0
>
> > NAME BLOCK_SIZE CURRENT_SIZE
> > -------------------- ---------- ------------
> > DEFAULT 8192 2960
>
> > Buffer Pool Buffer Pool Hit Ratio
> > -------------------- ---------------------
> > DEFAULT .926599622
>
> > *** 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 Checkpoints Started and Completed.
>
> > NAME VALUE
> > ---------------------------------------------------------------- ----------
> > background checkpoints started 113
> > background checkpoints completed 113
>
> > Display Instance Recovery
>
> > TARGET_MTTR ESTIMATED_MTTR
> > ----------- --------------
> > 35 20
>
> > Measure Redo Log Activity
>
> > event TOTAL_WAITS AVERAGE_WAIT
> > -------------------------------------------------- ----------- ------------
> > log file parallel write 3658333 .34
> > log file switch completion 516 19.37
>
> > *** Segment Information ***
>
> > Determine Existance of Any Chained or Migrated Rows (page 404-408)
>
> > NAME VALUE
> > ---------------------------------------------------------------- ----------
> > table fetch continued row 149938
>
> > Many tables found with empty blocks above the High Water Mark as well as
> > below. Most
> > tables are not deleted from so how do the empty blocks happen below the High
> > Water Mark?
>
> > *** IO ***
>
> > Determine How Often Full Table Scans are Being Performed
>
> > NAME VALUE
> > ---------------------------------------------------------------- ----------
> > table scans (long tables) 37163
>
> > Display Long Operations
>
> > Username Operation Pct
> > Remaining
> > --------------- -------------------------------------------------- ---------
> > ----
> > RPT Table Scan
> > 14.88
>
> > Measure Database Writer Performance
>
> > event TOTAL_WAITS AVERAGE_WAIT
> > -------------------------------------------------- ----------- ------------
> > buffer busy waits 4293 1.42
> > db file parallel write 615803 .23
>
> Given your stated skill set my question to you would be: "Who is
> complaining and about what?"
>
> If no one's complaining get all of the books you can find by Tom Kyte,
> Cary Millsap, and Jonathan Lewis and begin reading them after first
> reading the online concept docs athttp://tahiti.oracle.com. And also
> look at the 2 Day DBA docs.
>
> If there are no fires ... read!
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel is right in that you are going about the problem wrong. Unless there have been complaints spend your effort on studying rather than looking at random factors. If there has been a complaint on a specific task then look at it rather than the database as a whole.

Take a couple of statspack snapshots during peak times and save reports to be used as baselines against future runs of the utility.

If you have not already done so you should read the Concepts and DBA Administration manuals cover to cover. Follow these with Backup and Recovery since ensuring that the database is properly backed up and can be restored is your most important job. Then the first few chapters of the SQL manual (everything up to the syntax diagrams), the PL/SQL manual, and the Performance and Tuning manual.

For outside books I recommend Tom Kyte's new series (from Apress with yellow covers) and Jonathan Lewis book on the CBO which are the same books Daniel recommended.

HTH -- Mark D Powell -- Received on Sat Feb 10 2007 - 18:42:27 CST