Thursday, July 24, 2008

Sample statspack analysis report

STATSPACK report for

DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
xxxxxdb       3487274667 xxxxxdb             1 8.1.7.4.0   NO  VELSD1N0

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
Begin Snap:       8683 08-Jul-08 12:56:07    2,353
   End Snap:       8687 08-Jul-08 13:56:21    2,353
    Elapsed:                  60.23 (mins)

Cache Sizes
~~~~~~~~~~~
           db_block_buffers:    1310720          log_buffer:   15728640
              db_block_size:       8192    shared_pool_size: 3221225472

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            949,706.66              3,709.98
              Logical reads:            147,305.05                575.44
              Block changes:              5,792.76                 22.63
             Physical reads:             20,150.91                 78.72
            Physical writes:              1,740.11                  6.80
                 User calls:              2,465.61                  9.63
                     Parses:                429.09                  1.68
                Hard parses:                 17.73                  0.07
                      Sorts:                173.29                  0.68
                     Logons:                  5.74                  0.02
                   Executes:              4,808.72                 18.79
               Transactions:                255.99

  % Blocks changed per Read:    3.93    Recursive Call %:   76.85
Rollback per transaction %:   36.64       Rows per Sort:  378.89
************************************************************************
*  Recommandations:
Hard parses:                 17.73                  0.07       
Rewrite some of the top 5 sqls using more bind variables
Your database has relatively high logical I/O at 147,305.05 reads per second.
Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity.
CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility
or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking),
using faster CPU?s or adding more CPU?s to your system.
You are performing more than 20,150.91 disk reads per second. High disk latency can be caused by too-few physical disk spindles.
Compare your read times across multiple datafiles to see which datafiles are slower than others.
Disk read times may be improved if contention is reduced on  the datafile, even though read times may be high due to the file residing on a slow disk.
You should identify whether the SQL accessing the file can be tuned,  as well as the underlying characteristics of the hardware devices.
Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid-state disks.
You are performing more than 429.09 SQL parses per second. A parse is the process of executing your SQL, checking for proper security authorization,
checks for the existence of tables, columns, and other referenced objects, and generating an execution plan.
Your high parses suggest that your system has many incoming unique SQL statements or that your SQL is not reentrant
(i.e. literal values in the WHERE clause, not using bind variables). Confirm that the 429.09 parses per second is reasonable and
consider setting cursor_sharing=force if warranted.
Setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects.
[Hard parses:                 17.73                  0.07       
Rewrite some of the top 5 sqls using more bind variables]
You may have an application issue causing excessive rollbacks with 36.64% rollbacks per transaction.
Due to Oracle´s assumption of a commit, the Rollback process is very expensive and should only be used when necessary.
You can identify the specific SQL and user session that is executing the rollbacks by querying the v$sesstat view.

*************************************************************************

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.84       Redo NoWait %:  100.00
            Buffer  Hit   %:   86.32    In-memory Sort %:   99.99
            Library Hit   %:   99.59        Soft Parse %:   95.87
         Execute to Parse %:   91.08         Latch Hit %:   99.83
Parse CPU to Parse Elapsd %:   55.95     % Non-Parse CPU:   99.69

Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   95.64   95.69
    % SQL with executions>1:   15.43   21.89
  % Memory for SQL w/exec>1:   12.35   13.88

==

Top 5 Wait Events       
~~~~~~~~~~~~~~~~~                                             Wait     % Total       
Event                                               Waits  Time (cs)   Wt Time       
-------------------------------------------- ------------ ------------ -------       
db file sequential read                         9,942,694    5,443,778   34.05    IO bound   
db file scattered read                          5,203,808    5,231,619   32.72    IO bound    Enabling  OS level direct IO should help.
buffer busy waits                                 827,827    1,149,491    7.19        What RAID level is being used?
latch free                                        820,327    1,003,552    6.28       
SQL*Net more data to client                     1,984,422      979,786    6.13       

*******************************************************************************************************
* Recommandations:
A single-block read (i.e., index fetch by ROWID) .Increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O
Increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/O.
Network bottleneck causing TNS issues by making too many trips
*******************************************************************************************************

          -------------------------------------------------------------
Wait Events for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687
-> cs - centisecond -  100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events last)

                                                                    Avg
                                                     Total Wait    wait  Waits
Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
db file sequential read         9,942,694          0   5,443,778      5   10.7
db file scattered read          5,203,808          0   5,231,619     10    5.6
buffer busy waits                 827,827      1,253   1,149,491     14    0.9
latch free                        820,327    656,213   1,003,552     12    0.9
SQL*Net more data to client     1,984,422          0     979,786      5    2.1
enqueue                             5,221      2,742     871,868   1670    0.0
log file sync                     305,290        220     856,152     28    0.3
log file parallel write           247,783          0     249,084     10    0.3
SQL*Net message from dblink       196,497          0     154,163      8    0.2
SQL*Net more data from dblin    1,105,954          0      16,014      0    1.2
log file sequential read           49,164          0       8,979      2    0.1
PX Deq: Execution Msg                 973          3       3,499     36    0.0
SQL*Net break/reset to clien      201,409          0       3,084      0    0.2
local write wait                      155         17       2,509    162    0.0
file open                         267,907          0       2,473      0    0.3
PX Deq: Execute Reply                 595          2       1,332     22    0.0
control file parallel write         1,504          0         551      4    0.0
PX Deq Credit: send blkd           26,949          0         501      0    0.0
control file sequential read        3,283          0         441      1    0.0
log file switch completion             44          0         397     90    0.0
PX Deq: Signal ACK                    102         46         293     29    0.0
LGWR wait for redo copy             2,383         65         281      1    0.0
PX Deq: Table Q Normal              6,938          0         245      0    0.0
library cache pin                     229          0         212      9    0.0
db file parallel write            317,662          0         193      0    0.3
direct path read                  148,889          0         148      0    0.2
db file parallel read                  68          0         131     19    0.0
library cache load lock                58          0         123     21    0.0
PX Deq Credit: need buffer          7,834          0          88      0    0.0
SQL*Net message to dblink         196,497          0          72      0    0.2
process startup                        15          0          58     39    0.0
row cache lock                         83          0          52      6    0.0
SQL*Net more data to dblink         1,060          0          42      0    0.0
PX Deq: Join ACK                      127          0          35      3    0.0
refresh controlfile command            52          0          33      6    0.0
PX Deq: Parse Reply                    90          0          30      3    0.0
direct path write                  19,314          0          20      0    0.0
PX Deq: Msg Fragment                  258          0           6      0    0.0
log file single write                  12          0           2      2    0.0
file identify                          35          0           1      0    0.0
buffer deadlock                         8          8           0      0    0.0
SQL*Net message from client     8,986,093          0 ###########    430    9.7
PX Idle Wait                        7,618      7,486   1,545,427   2029    0.0
SQL*Net more data from clien      313,950          0       6,832      0    0.3
SQL*Net message to client       8,986,086          0       3,336      0    9.7
          -------------------------------------------------------------
Background Wait Events for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687
-> ordered by wait time desc, waits desc (idle events last)

*********************************************************
* Recommandations:

You have high average latch free waits of 12. The latch free wait occurs when the process is waiting for a latch held by another process.
Check the later section for the specific latch waits.
Latch free waits are usually due to SQL without bind variables,  but buffer chains and redo generation can also cause them.
You have  buffer busy waits with 14 on an average.
Buffer busy waits are most commonly caused  by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters,
tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition.
Using super-fast SSD will also reduce buffer busy waits because transactions are completed many times faster.

                                                                    Avg
                                                     Total Wait    wait  Waits
Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file parallel write           247,775          0     249,067     10    0.3
latch free                         64,726     64,713     129,463     20    0.1
log file sequential read           49,164          0       8,979      2    0.1
control file parallel write         1,504          0         551      4    0.0
LGWR wait for redo copy             2,383         65         281      1    0.0
db file scattered read                154          0         228     15    0.0
db file parallel write            317,663          0         193      0    0.3
control file sequential read          884          0         158      2    0.0
file open                          26,829          0         153      0    0.0
buffer busy waits                     119          0          94      8    0.0
enqueue                                 1          0          16    160    0.0
direct path read                   14,076          0          14      0    0.0
db file sequential read                19          0          11      6    0.0
direct path write                   4,128          0           2      0    0.0
log file single write                  12          0           2      2    0.0
file identify                          24          0           1      0    0.0
rdbms ipc message               1,572,896      7,327   5,910,286     38    1.7
pmon timer                          1,204      1,151     361,216   3000    0.0
smon timer                          2,430          0     357,304   1470    0.0
          -------------------------------------------------------------
          -------------------------------------------------------------
Instance Activity Stats for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session                34,492,558      9,544.2         37.3
CPU used when call started               2,430,780        672.6          2.6
CR blocks created                          200,151         55.4          0.2
Cached Commit SCN referenced                     0          0.0          0.0
DBWR buffers scanned                     8,793,138      2,433.1          9.5
DBWR checkpoint buffers written            742,662        205.5          0.8
DBWR checkpoints                                29          0.0          0.0
DBWR free buffers found                  8,407,230      2,326.3          9.1
DBWR lru scans                             194,034         53.7          0.2
DBWR make free requests                    378,968        104.9          0.4
DBWR revisited being-written buff                2          0.0          0.0
DBWR summed scan depth                   8,793,138      2,433.1          9.5
DBWR transaction table writes                  639          0.2          0.0
DBWR undo block writes                     150,529         41.7          0.2
DDL statements parallelized                      0          0.0          0.0
DFO trees parallelized                          26          0.0          0.0
PX local messages recv'd                    45,460         12.6          0.1
PX local messages sent                      45,460         12.6          0.1
Parallel operations downgraded 25               26          0.0          0.0
Parallel operations downgraded 50                0          0.0          0.0
Parallel operations downgraded 75                0          0.0          0.0
Parallel operations downgraded to                0          0.0          0.0
Parallel operations not downgrade                0          0.0          0.0
SQL*Net roundtrips to/from client        8,936,834      2,472.8          9.7
SQL*Net roundtrips to/from dblink          196,498         54.4          0.2
background checkpoints completed                 3          0.0          0.0
background checkpoints started                   3          0.0          0.0
background timeouts                         13,858          3.8          0.0
branch node splits                              25          0.0          0.0
buffer is not pinned count             356,177,766     98,555.0        385.0
buffer is pinned count                 296,354,776     82,001.9        320.3
bytes received via SQL*Net from c    1,947,857,133    538,975.4      2,105.5
bytes received via SQL*Net from d    2,523,328,693    698,209.4      2,727.5
bytes sent via SQL*Net to client     7,563,418,140  2,092,810.8      8,175.5
bytes sent via SQL*Net to dblink        81,842,927     22,646.1         88.5
calls to get snapshot scn: kcmgss       23,405,905      6,476.5         25.3
calls to kcmgas                            721,918        199.8          0.8
calls to kcmgcs                            305,914         84.7          0.3
change write time                           22,194          6.1          0.0
cleanouts and rollbacks - consist          149,031         41.2          0.2
cleanouts only - consistent read           404,737        112.0          0.4
cluster key scan block gets                266,766         73.8          0.3
cluster key scans                          177,118         49.0          0.2
commit cleanout failures: block l          109,493         30.3          0.1
commit cleanout failures: buffer                34          0.0          0.0
commit cleanout failures: callbac          176,981         49.0          0.2
commit cleanout failures: cannot             1,110          0.3          0.0
commit cleanout failures: hot bac                0          0.0          0.0
commit cleanouts                         3,004,558        831.4          3.3
commit cleanouts successfully com        2,716,941        751.8          2.9
consistent changes                      23,104,214      6,393.0         25.0
consistent gets                        503,690,156    139,371.9        544.5
current blocks converted for CR
cursor authentications                         222          0.1          0.0
data blocks consistent reads - un        2,961,257        819.4          3.2
db block changes                        20,935,037      5,792.8         22.6
Instance Activity Stats for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
db block gets                           28,674,519      7,934.3         31.0
deferred (CURRENT) block cleanout          853,972        236.3          0.9
dirty buffers inspected                    379,360        105.0          0.4
enqueue conversions                        480,045        132.8          0.5
enqueue releases                        10,791,726      2,986.1         11.7
enqueue requests                        10,799,839      2,988.3         11.7
enqueue timeouts                             7,844          2.2          0.0
enqueue waits                                   51          0.0          0.0
exchange deadlocks                               8          0.0          0.0
execute count                           17,378,704      4,808.7         18.8
free buffer inspected                      399,885        110.7          0.4
free buffer requested                   71,441,513     19,768.0         77.2
hot buffers moved to head of LRU         4,570,055      1,264.5          4.9
immediate (CR) block cleanout app          553,784        153.2          0.6
immediate (CURRENT) block cleanou        1,025,975        283.9          1.1
index fast full scans (full)                65,266         18.1          0.1
index fast full scans (rowid rang                0          0.0          0.0
leaf node splits                             7,403          2.1          0.0
logons cumulative                           20,749          5.7          0.0
logons current
messages received                        2,107,310        583.1          2.3
messages sent                            2,107,310        583.1          2.3
no buffer to keep pinned count                   0          0.0          0.0
no work - consistent read gets         258,951,240     71,652.3        279.9
opened cursors cumulative                  914,027        252.9          1.0
opened cursors current
parse count (hard)                          64,091         17.7          0.1
parse count (total)                      1,550,743        429.1          1.7
parse time cpu                             107,324         29.7          0.1
parse time elapsed                         191,812         53.1          0.2
physical reads                          72,825,384     20,150.9         78.7
physical reads direct                    2,145,627        593.7          2.3
physical writes                          6,288,765      1,740.1          6.8
physical writes direct                   4,781,858      1,323.2          5.2
physical writes non checkpoint           6,199,840      1,715.5          6.7
pinned buffers inspected                     8,431          2.3          0.0
prefetched blocks                       55,538,990     15,367.7         60.0
prefetched blocks aged out before              104          0.0          0.0
process last non-idle time        ################ ############ ############
queries parallelized                            26          0.0          0.0
recursive calls                         29,586,280      8,186.6         32.0
recursive cpu usage                      1,092,439        302.3          1.2
redo blocks written                      3,530,590        976.9          3.8
redo buffer allocation retries                  40          0.0          0.0
redo entries                            11,225,877      3,106.2         12.1
redo log space requests                         44          0.0          0.0
redo log space wait time                       397          0.1          0.0
redo ordering marks                            286          0.1          0.0
redo size                            3,432,239,880    949,706.7      3,710.0
redo synch time                            886,936        245.4          1.0
redo synch writes                          315,549         87.3          0.3
redo wastage                           125,962,000     34,853.9        136.2
redo write time                            249,131         68.9          0.3
redo writer latching time                      285          0.1          0.0
redo writes                                247,731         68.6          0.3
rollback changes - undo records a          161,372         44.7          0.2
Instance Activity Stats for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
rollbacks only - consistent read           121,670         33.7          0.1
rows fetched via callback               45,729,915     12,653.6         49.4
session connect time              ################ ############ ############
session cursor cache count                  25,143          7.0          0.0
session cursor cache hits                  992,340        274.6          1.1
session logical reads                  532,360,467    147,305.1        575.4
session pga memory                  31,216,772,800  8,637,734.6     33,742.9
session pga memory max              35,796,333,832  9,904,907.0     38,693.0
session uga memory                ################ ############  1,249,401.2
session uga memory max               4,702,581,528  1,301,212.4      5,083.1
sorts (disk)                                    38          0.0          0.0
sorts (memory)                             626,215        173.3          0.7
sorts (rows)                           237,282,358     65,656.4        256.5
summed dirty queue length                  124,217         34.4          0.1
switch current to new buffer
table fetch by rowid                   199,051,301     55,077.8        215.2
table fetch continued row                5,468,289      1,513.1          5.9
table scan blocks gotten               170,858,243     47,276.8        184.7
table scan rows gotten              13,484,541,053  3,731,195.6     14,575.7
table scans (direct read)                      399          0.1          0.0
table scans (long tables)                    1,237          0.3          0.0
table scans (rowid ranges)                     399          0.1          0.0
table scans (short tables)                 546,575        151.2          0.6
total file opens                           267,656         74.1          0.3
transaction rollbacks                        5,716          1.6          0.0
transaction tables consistent rea            5,183          1.4          0.0
transaction tables consistent rea       20,126,322      5,569.0         21.8
user calls                               8,910,702      2,465.6          9.6
user commits                               586,124        162.2          0.6
user rollbacks                             339,013         93.8          0.4
write clones created in backgroun              139          0.0          0.0
write clones created in foregroun            8,046          2.2          0.0
          -------------------------------------------------------------
   *******************************************************************************************************  
       * Recommandations:
       You have high network activity
       SQL*Net roundtrips to/from client        8,936,834      2,472.8          9.7
       SQL*Net roundtrips to/from dblink          196,498         54.4          0.2
       which is a high amount of traffic.
       Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces
       (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit
       from bulk collection by using PL/SQL "forall" or "bulk collect" operators.
       You have
       [ consistent gets                        503,690,156    139,371.9        544.5]
       139,371.9 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets.
       It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O.
       To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace.
       Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.
       You have high disk reads with  5,792.8 per second.
       [ db block changes                        20,935,037      5,792.8         22.6]
       Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage.
       You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity.
       You have 5,468,289 table fetch continued row actions during this period.
       [ Table fetch continued row                5,468,289      1,513.1          5.9]
       Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row"
       (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size),
       when we have tables with > 255 columns, and when PCTFREE is too small.
       You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.
       You have high small table full-table scans, at 151.2  per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes.
       Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans.

       table scans (long tables)                    1,237          0.3          0.0
       table scans (rowid ranges)                     399          0.1          0.0
       table scans (short tables)                 546,575        151.2          0.6
       ********************************************************

                                                Pct    Avg                 Pct
                                   Get          Get   Slps       NoWait NoWait
Latch Name                       Requests      Miss  /Miss     Requests   Miss
----------------------------- -------------- ------ ------ ------------ ------
sort extent pool                      43,393    0.0    0.3            0
temporary table state object               2    0.0                   0
transaction allocation            10,398,534    0.1    0.1            0
transaction branch allocation      2,106,097    0.0    0.1            0
undo global data                   4,583,227    0.3    0.4            0
user lock                             77,834    0.1    0.6            0
          -------------------------------------------------------------
Latch Sleep breakdown for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687
-> ordered by misses desc

                                Get                                  Spin &
Latch Name                    Requests         Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
checkpoint queue latch        400,450,816   1,328,318      56,544 1272016/5606
                                                                  2/238/2/0
cache buffers chains        1,033,370,254     773,388     294,205 513223/22800
                                                                  6/30428/1731
                                                                  /0
library cache                 116,523,744     349,433     251,412 173829/10778
                                                                  1/60664/7159
                                                                  /0
cache buffers lru chain        63,816,708     148,411     107,619 41398/106413
                                                                  /595/5/0
multiblock read objects        11,175,620     117,559       8,813 108814/8678/
                                                                  66/1/0
enqueues                       23,860,244      71,142       4,420 66883/4098/1
                                                                  61/0/0
row cache objects              38,292,769      50,935      26,331 26456/22820/
                                                                  1495/164/0
redo writing                    3,683,417      46,084      17,144 29209/16611/
                                                                  259/5/0
shared pool                     5,484,248      39,276      35,382 18831/7135/1
                                                                  2216/1094/0
messages                        8,086,376      34,509       3,781 30753/3731/2
                                                                  5/0/0
redo allocation                11,717,680      32,781       2,337 30497/2231/5
                                                                  3/0/0
undo global data                4,583,227      12,501       4,464 8064/4410/27
                                                                  /0/0
session allocation              3,246,941      11,918       2,794 9206/2638/67
                                                                  /7/0
transaction allocation         10,398,534      10,169         722 9486/644/39/
                                                                  0/0
dml lock allocation            18,444,219       9,950         598 9396/510/44/
                                                                  0/0
active checkpoint queue la      1,868,358       9,184         752 8436/744/4/0
                                                                  /0
enqueue hash chains            22,084,552       7,240       3,801 3902/2897/42
                                                                  3/18/0
latch wait list                   205,738       1,929         357 1690/122/116
                                                                  /1/0
mostly latch-free SCN           1,500,594         915          54 862/52/1/0/0
session idle bit               19,033,372         869          87 786/79/4/0/0
cache buffer handles            1,483,373         672          41 632/39/1/0/0
process queue reference           856,688         641          62 594/32/15/0/
                                                                  0
transaction branch allocat      2,106,097         640          70 572/66/2/0/0
list of block allocation        1,684,529         582          41 542/39/1/0/0
parallel query alloc buffe          1,820         111          21 91/19/1/0/0
global tx free list               185,532          59           7 52/7/0/0/0
user lock                          77,834          54          35 22/30/1/1/0
process allocation                 19,530          49          55 0/44/4/1/0
Token Manager                     286,259          29           1 28/1/0/0/0
parallel query stats                  208          25          18 8/16/1/0/0
query server freelists              1,070          21           5 16/5/0/0/0
sequence cache                    148,553          17           2 15/2/0/0/0
error message lists                   234           8           2 7/0/1/0/0
Latch Sleep breakdown for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687
-> ordered by misses desc

                                Get                                  Spin &
Latch Name                    Requests         Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
sort extent pool                   43,393           6           2 4/2/0/0/0
channel handle pool latch          39,079           2           1 1/1/0/0/0
process group creation             39,079           2           1 1/1/0/0/0
          -------------------------------------------------------------
          * Recommandations:
             You have high cache buffer chain latches with 1,033,370,254 get requests at 0.1% get miss.
             See MetaLink about increasing the hidden parameter _db_block_hash_buckets.
             [cache buffers chains           1,033,370,254    0.1    0.4  128,455,360    0.0]
         You have a high value for cache buffer LRU chain waits with 63,816,708 get requests at 0.2% get miss,
         and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer.
         Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block
         by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.
          [cache buffers lru chain           63,816,708    0.2    0.7   71,030,542    0.3]
         You have high library cache waits with 116,523,744 get requests at 0.3% get miss.
         Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.

             [ library cache                    116,523,744    0.3    0.7      356,936    0.6]
          ***********************************************************
          -------------------------------------------------------------
init.ora Parameters for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
O7_DICTIONARY_ACCESSIBILITY   FALSE
audit_trail                   DB
background_dump_dest          /oracleadmin/dump/xxxxxdb/bdump
buffer_pool_keep              131008
buffer_pool_recycle           12800
compatible                    8.1.7
control_files                 /xxxxxdb_redoctrl01/xxxxxdb/ctrl/
core_dump_dest                /oracleadmin/dump/xxxxxdb/cdump
db_block_buffers              1310720
db_block_lru_latches          192
db_block_size                 8192
db_file_multiblock_read_count 8                                 16
db_files                      4000
db_name                       xxxxxdb
db_writer_processes           10
enqueue_resources             32000
event                         32333 trace name context forever,
fast_start_parallel_rollback  low
hash_area_size                8192000
instance_name                 xxxxxdb
java_pool_size                20971520
job_queue_processes           20
large_pool_size               120485760
log_archive_dest              /xxxxxdb_arch/xxxxxdb
log_archive_format            arch_%t_%s.arc
log_archive_start             TRUE
log_buffer                    15728640
log_checkpoint_interval       204800
log_checkpoint_timeout        0
log_checkpoints_to_alert      TRUE
max_enabled_roles             60
nls_date_format               YYYYMMDD-HH24.MI.SS
nls_language                  AMERICAN
nls_numeric_characters        .
nls_territory                 AMERICA
open_cursors                  2448
open_links                    8
optimizer_index_caching       0
os_authent_prefix             ops$
processes                     6000
query_rewrite_enabled         TRUE
query_rewrite_integrity       TRUSTED
recovery_parallelism          10
remote_login_passwordfile     NONE
remote_os_authent             TRUE
replication_dependency_tracki FALSE
resource_limit                TRUE
service_names                 xxxxxdb
session_cached_cursors        400
shared_pool_size              3221225472
sort_area_retained_size       4096000
sort_area_size                8388608
timed_statistics              TRUE
transaction_auditing          FALSE
transactions_per_rollback_seg 5
init.ora Parameters for DB: xxxxxdb  Instance: xxxxxdb  Snaps: 8683 -8687

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
user_dump_dest                /oracleadmin/dump/xxxxxdb/udump
utl_file_dir                  /smsnote, /smsnote/output, /smsno
          -------------------------------------------------------------

End of Report
************************************************************************
*  Recommandations:

You have the default value for db_file_multiblock_read_count at 8.
The CBO uses this parameter to determine the cost of a full-table scan.
The default value is sometimes too large, and you can run scripts to determine the optimal setting.
If full-table scans are unavoidable, you may consider placing those tables on SSD.

You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.

Your shared pool is set at 3145728MB, which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance.
For further details, see the shared pool advisory.

You are not using your KEEP pool to cache frequently referenced tables and indexes.
This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer
get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in
the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.

Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer.
Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.

No comments:

Post a Comment