Performance Tuning Help Questions

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Fri, 9 Feb 2007 18:11:28 -0500
Message-ID: <mc7zh.53024$QU1.39143@newssvr22.news.prodigy.net>

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

Received on Fri Feb 09 2007 - 17:11:28 CST