BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Tablespace Activity Section

Oracle Tips by Burleson Consulting

This section shows disk read and write activity by tablespace.  It is useful for seeing which tablespaces are most active, but it will not provide information about the specific disks involved in a disk bottleneck unless you have a mapping of tablespaces to data files, and data file to disks.  Users with RAID 1 or RAID 5 will not find this report very useful, because they cannot translate the tablespace I/O back to specific disk spindles.

Tablespace IO Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
->ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSTEM
            20       0    2.5     1.0           71        0          0    0.0
PERFSTAT
             1       0    0.0     1.0           32        0          0    0.0
UNDOTBS
             1       0   10.0     1.0           29        0          0    0.0
CWMLITE
             1       0    0.0     1.0            0        0          0    0.0
DRSYS
             1       0    0.0     1.0            0        0          0    0.0
EXAMPLE
             1       0    0.0     1.0            0        0          0    0.0
INDX
             1       0    0.0     1.0            0        0          0    0.0
TOOLS
             1       0    0.0     1.0            0        0          0    0.0
USERS
             1       0    0.0     1.0            0        0          0    0.0
          -------------------------------------------------------------

File I/O Activity Section

The File I/O Activity section is useful only if the Oracle Remote DBA has a mapping between the data files and the physical disks.  It may be nice to see disk I/O at the data file level, but locating a “hot” data file is of no value if the Remote DBA cannot find a “cool” disk to which the data file can be re-located.

File IO Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
->ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av      Av     Av                    Av        Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
CWMLITE                  /opt/oracle/oradata/dia2/cwmlite01.dbf
             1       0    0.0     1.0            0        0          0

DRSYS                    /opt/oracle/oradata/dia2/drsys01.dbf
             1       0    0.0     1.0            0        0          0

EXAMPLE                  /opt/oracle/oradata/dia2/example01.dbf
             1       0    0.0     1.0            0        0          0

INDX                     /opt/oracle/oradata/dia2/indx01.dbf
             1       0    0.0     1.0            0        0          0

PERFSTAT                 /opt/oracle/oradata/dia2/perfstat.dbf
             1       0    0.0     1.0           32        0          0

SYSTEM                   /opt/oracle/oradata/dia2/system01.dbf
            20       0    2.5     1.0           71        0          0

TOOLS                    /opt/oracle/oradata/dia2/tools01.dbf
             1       0    0.0     1.0            0        0          0

UNDOTBS                  /opt/oracle/oradata/dia2/undotbs01.dbf
             1       0   10.0     1.0           29        0          0

USERS                    /opt/oracle/oradata/dia2/users01.dbf
             1       0    0.0     1.0            0        0          0

          -------------------------------------------------------------

Buffer Pool Activity Section

The Buffer Pool Activity section provides a buffer hit ratio for the default data pool, the KEEP data pool, and the RECYCLE data pool.  Of course, these data buffer hit ratios are very important; they are fully explained in Chapter 9.

Buffer Pool Statistics for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                                           Free    Write  Buffer
     Number of Cache      Buffer    Physical   Physical  Buffer Complete    Busy
P      Buffers Hit %        Gets       Reads     Writes   Waits    Waits   Waits
--- ---------- ----- ----------- ----------- ---------- ------- --------  ------
D        8,024  99.8      18,526          28      3,282       0        0       0
K        1,048 100.0       3,656       1,028        132       0        0       0
R        8,024  99.8      18,611         724      4,222       0        0       0
16k      2,048  99.3      13,620         128         53       0        0       0
          -------------------------------------------------------------

Instance Recovery Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> B: Begin snapshot,  E: End snapshot

  Targt Estd                                    Log File   Log Ckpt   Log Ckpt
  MTTR  MTTR   Recovery    Actual     Target      Size     Timeout    Interval
   (s)   (s)   Estd IOs  Redo Blks  Redo Blks  Redo Blks  Redo Blks  Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B    30    33       8024       1202      27705     184320      27705   27151
E    30    25       8024        910      28405     184320      28405 72512
          -------------------------------------------------------------

PGA Memory Activity Section

The PGA Memory Activity section shows all PGA memory activity during the snapshot interval.  In Oracle9i, all PGA memory is managed inside the SGA, and this section can tell you when you have a shortage in the pga_aggregate_target parameter.

PGA Memory Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> WorkArea (W/A) memory is used for: sort, bitmap merge, and hash join ops

Statistic                                  Begin (M)          End (M)     % Diff
----------------------------------- ---------------- ---------------- ----------
maximum PGA allocated                         11.209           12.222       9.03
total PGA allocated                           11.209           12.222       9.03
total PGA inuse                                8.011            8.321       3.88
          -------------------------------------------------------------

Rollback Segment Activity Section

The Rollback Segment Activity section show details of each rollback segment and the waits associated with each rollback segment.  This information is useful for sizing of the rollback segments within the RBS tablespace to ensure that you avoid rollback segment failed to extend messages.

Rollback Segment Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
  management, as RBS may be dynamically created and dropped as needed

        Trans Table       Pct   Undo Bytes
RBS No      Gets        Waits     Written        Wraps  Shrinks  Extends
------ -------------- ------- --------------- -------- -------- --------
     0            3.0    0.00               0        0        0        0
     1            9.0    0.00             716        0        0        0
     2           22.0    0.00           1,828        0        0        0
     3            5.0    0.00               0        0        0        0
     4           33.0    0.00          88,544        0        0        0
     5           12.0    0.00             426        0        0        0
     6            7.0    0.00             142        0        0        0
     7            7.0    0.00             126        0        0        0
     8            9.0    0.00             142        0        0        0
     9            9.0    0.00             392        0        0        0
    10           11.0    0.00             722        0        0        0
          -------------------------------------------------------------

Rollback Segment Activity Section

The Rollback Segment Activity section provides information about the amount of extension within your rollback segments. This information is especially useful if you are experiencing snapshot too old – rollback segment too small errors and you need to take a closer look at the details of the rollback segment.

Rollback Segment Storage for DB: DIA2  Instance: dia2  Snaps: 1 -2
->Optimal Size should be larger than Avg Active

RBS No    Segment Size      Avg Active    Optimal Size    Maximum Size
------ --------------- --------------- --------------- ---------------
     0         401,408               0                         401,408
     1      20,045,824         104,857                      20,045,824
     2      12,247,040               0                      12,247,040
     3      14,934,016               0                      14,934,016
     4      25,288,704         104,857                      25,288,704
     5      15,851,520               0                      15,851,520
     6      18,341,888         104,857                      18,341,888
     7      15,917,056               0                      15,917,056
     8      25,419,776               0                      25,419,776
     9      11,657,216               0                      11,657,216
    10      13,754,368               0                      13,754,368
          -------------------------------------------------------------

Latch Activity Section

The Latch Activity section gives a summary of latch activity during the snapshot period.  While latches often indicate shortages of Oracle resources, this section is seldom helpful in identifying performance problems.

Undo Segment Summary for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo           Undo        Num  Max Qry     Max Tx Snapshot Out of uS/uR/uU/
 TS#         Blocks      Trans  Len (s)   Concurcy  Too Old  Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
   1            253      1,786       12          3        0      0 0/0/0/0/0/0
          -------------------------------------------------------------


Undo Segment Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
-> ordered by Time desc

                     Undo      Num Max Qry   Max Tx  Snap   Out of uS/uR/uU/
End Time           Blocks    Trans Len (s)    Concy Too Old  Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
08-Dec 20:30          253    1,786      12        3       0      0 0/0/0/0/0/0

Latch Activity Section

The Latch Activity section describes all latch wait activity during the snapshot interval.  Oracle has dozens of latches, but only a few are exceptionally important to the Oracle Remote DBA.

  •  Redo copy latchesWaits with the Oracle9i log writer background process (LGWR) are often related to redo copy latches.  High values for this metric usually indicate a need for the size of the redo logs to be increased.

  • Library cache latchesA high value for this latch normally indicates that the library cache is loaded with non-reusable SQL statements. The use of bind variables (or setting cursor_sharing=force) to implement reusable SQL creates many fewer library cache objects and rapid hashing to the matching object. The library cache latch is thus released more quickly, reducing the latching impact of the library cache latch.

Latch Activity for DB: DIA2  Instance: dia2  Snaps: 1 -2
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
  willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0
-> ordered by Wait Time desc, Avg Slps/Miss, Pct NoWait Miss desc

                                           Pct    Avg   Wait                 Pct
                              Get          Get   Slps   Time       NoWait NoWait
Latch                       Requests      Miss  /Miss    (s)     Requests   Miss
------------------------ -------------- ------ ------ ------ ------------ ------
cache buffers chains             57,583    0.0             0           72    0.0
cache buffers lru chain           1,456    0.0             0            1    0.0
hash table column usage               5    0.0             0           78    0.0
redo copy                             0                    0          359    0.0
process allocation                    1    0.0             0            1    0.0
kmcpvec latch                         0                    0            1    0.0
resmgr:schema config                136    0.0             0          250    0.0
FOB s.o list latch                   11    0.0             0            0
SQL memory manager worka             67    0.0             0            0
channel operations paren            245    0.0             0            0
child cursor hash table             184    0.0             0            0
enqueue hash chains               1,132    0.0             0            0
event group latch                     1    0.0             0            0
process group creation                1    0.0             0            0
post/wait queue latch                 4    0.0             0            0
ncodef allocation latch              12    0.0             0            0
messages                          1,511    0.0             0            0
list of block allocation             34    0.0             0            0
library cache load lock             102    0.0             0            0
library cache                     9,856    0.0             0            0
ktm global data                       3    0.0             0            0
kmcptab latch                         1    0.0             0            0
transaction branch alloc             12    0.0             0            0
transaction allocation               54    0.0             0            0
trace latch                           2    0.0             0            0
sort extent pool                     14    0.0             0            0
shared pool                       1,584    0.0             0            0
session timer                       250    0.0             0            0
session switching                    12    0.0             0            0
session idle bit                     72    0.0             0            0
session allocation                   29    0.0             0            0
user lock                             2    0.0             0            0
undo global data                    181    0.0             0            0
sequence cache                        6    0.0             0            0
row cache objects                 2,303    0.0             0            0
resmgr:resource group CP             10    0.0             0            0
resmgr:actses change gro              1    0.0             0            0
resmgr:actses active lis              1    0.0             0            0
resmgr group change latc            137    0.0             0            0
redo writing                      1,033    0.0             0            0
redo allocation                     619    0.0             0            0
file number translation              70    0.0             0            0
enqueues                          2,183    0.0             0            0
dml lock allocation                  76    0.0             0            0
checkpoint queue latch            6,297    0.0             0            0
channel handle pool latc              1    0.0             0            0
active checkpoint queue             252    0.0             0            0
cache buffer handles                 53    0.0             0            0

Dictionary Cache Statistics

The Dictionary Cache section shows the dictionary items that may be experiencing contention.  Especially important in this area is the dc_histograms_defs metric.  Often, naïve Oracle Remote DBAs will inadvertently analyze column histograms for un-skewed indexes, causing significant contention with the dictionary cache.

Dictionary Cache Stats for DB: DIA2  Instance: dia2  Snaps: 1 -2
->"Pct Misses"  should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA"     is the ratio of usage to allocated size for that cache

                                Get    Pct   Scan   Pct      Mod      Final  Pct
Cache                      Requests   Miss   Reqs  Miss     Reqs      Usage  SGA
---------------------- ------------ ------ ------ ----- -------- ---------- ----
dc_files                         18    0.0      0              0          9   90
dc_free_extents                   4    0.0      0              0          3   33
dc_global_oids                    7    0.0      0              0         16   50
dc_histogram_defs                44   56.8      0              0         25   68
dc_object_ids                   186    5.9      0              0        485   98
dc_objects                      141    6.4      0              0      1,422  100
dc_profiles                       1    0.0      0              0          1   25
dc_rollback_segments             44    0.0      0              0         12   67
dc_segments                      63    7.9      0              1        277   99
dc_tablespaces                  121    0.0      0              0         10   67
dc_user_grants                   16    0.0      0              0         15   88
dc_usernames                     74    0.0      0              0         12   57
dc_users                        144    0.0      0              0         28   93
          -------------------------------------------------------------

Library Cache Statistics

The Library Cache Statistics section shows details about the internals of the library cache during the snapshot period. The library cache miss ratio represents the ratio of the sum of library cache reloads to the sum of pins. It tells the Remote DBA whether space needs to be added to the shared pool. In general, if the library cache ratio is more than 1, you should consider adding to the shared_pool_size. Library cache misses occur during the parsing and preparation of the execution plans for SQL statements.

The most important columns in the section are the miss and reloads values. If the number of reloads is significant, then reusable information is being flushed from the SGA and thus having to be reloaded/rebuilt.

Library cache misses are an indication that the shared pool is not big enough to hold the shared SQL for all currently running programs. If you have no library cache misses (PINS = 0), you may get a small increase in performance by setting cursor_space_for_time = TRUE, which prevents ORACLE from de-allocating a shared SQL area while an associated application cursor is open. Library cache misses during the execute phase occur when the parsed representation exists in the library cache but has been bounced out of the shared pool.

The compilation of a SQL statement consists of two phases: the parse phase and the execute phase. When the time comes to parse a SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle allocates a shared SQL area within the library cache and then parses the SQL statement. At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle reparses and executes the statement.

Library Cache Activity for DB: DIA2  Instance: dia2  Snaps: 1 -2
->"Pct Misses"  should be very low

                         Get  Pct        Pin        Pct               Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads  dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY                       6    0.0              6    0.0          0        0
CLUSTER                   39    0.0             10    0.0          0        0
SQL AREA                 159    1.9            764    6.5          0        0
TABLE/PROCEDURE          290    2.4            447   19.7          0        0
TRIGGER                    1    0.0              1    0.0          0        0
          -------------------------------------------------------------

Instance Values

The Instance Values section is a simple printout of the existing Oracle parameter values for the entire Oracle instance.  This data is static unless changed by an alter system command.

Again, much of the breakdown in this section is proprietary and unpublished, so only Oracle support can interpret the breakdowns. However, you can find some great clues about how Oracle allocated memory within the shared pool, large pool, and pga_aggregate_target memory. Especially important are changes to the values for shared library cache and shared SQL area values, because they tell us how Oracle has reallocated RAM memory during the snapshot interval.

SGA Memory Summary for DB: DIA2  Instance: dia2  Snaps: 1 -2

SGA regions                       Size in Bytes
------------------------------ ----------------
Database Buffers                     67,108,864
Fixed Size                              279,720
Redo Buffers                            532,480
Variable Size                       268,435,456
                               ----------------
sum                                 336,356,520
          -------------------------------------------------------------


SGA breakdown difference for DB: DIA2  Instance: dia2  Snaps: 1 -2

Pool   Name                                Begin value        End value  % Diff
------ ------------------------------ ---------------- ---------------- -------
java   free memory                         112,050,176      112,050,176    0.00
java   memory in use                         5,390,336        5,390,336    0.00
shared 1M buffer                             1,049,088        1,049,088    0.00
shared Checkpoint queue                        141,152          141,152    0.00
shared FileIdentificatonBlock                  319,452          319,452    0.00
shared FileOpenBlock                           695,504          695,504    0.00
shared KGK heap                                  3,756            3,756    0.00
shared KGLS heap                             1,613,312        1,677,136    3.96
shared KGSK scheduler                           52,260           52,260    0.00
shared KGSKI schedule                           18,072           18,072    0.00
shared KSXR pending messages que               225,836          225,836    0.00
shared KSXR receive buffers                  1,058,000        1,058,000    0.00
shared PL/SQL DIANA                          2,929,080        2,929,080    0.00
shared PL/SQL MPCODE                           332,132          332,132    0.00
shared PLS non-lib hp                            2,068            2,068    0.00
shared VIRTUAL CIRCUITS                        266,120          266,120    0.00
shared character set object                    320,836          320,836    0.00
shared dictionary cache                      1,163,912        1,182,732    1.62
shared enqueue                                 171,860          171,860    0.00
shared errors                                   55,408           55,408    0.00
shared event statistics per sess             1,366,120        1,366,120    0.00
shared fixed allocation callback                    60               60    0.00
shared free memory                         111,550,200      110,628,236   -0.83
shared joxlod: in ehe                          312,484          312,484    0.00
shared joxlod: in phe                          115,248          115,248    0.00
shared joxs heap init                            4,220            4,220    0.00
shared ksm_file2sga region                     148,652          148,652    0.00
shared library cache                         3,995,948        4,086,892    2.28
shared message pool freequeue                  767,192          767,192    0.00
shared miscellaneous                         2,636,536        2,666,780    1.15
shared parameters                               14,888           14,888    0.00
shared processes                               125,400          125,400    0.00
shared sessions                                395,080          395,080    0.00
shared sql area                              2,152,928        2,870,220   33.32
shared table definiti                            1,008            1,848   83.33
shared transaction                             181,624          181,624    0.00
shared trigger defini                            4,032            4,032    0.00
shared trigger inform                            1,704            1,704    0.00
shared trigger source                            3,264            3,264    0.00
shared type object de                           23,292           23,292    0.00
       db_block_buffers                     67,108,864       67,108,864    0.00
       fixed_sga                               279,720          279,720    0.00
       log_buffer                              524,288          524,288    0.00
          -------------------------------------------------------------

init.ora Parameters for DB: DIA2  Instance: dia2  Snaps: 1 -2

                                                                  End value
Parameter Name                Begin value                       (if different)
----------------------------- --------------------------------- --------------
background_dump_dest          /opt/oracle/admin/dia2/bdump
compatible                    9.0.0
control_files                 /opt/oracle/oradata/dia2/control0
core_dump_dest                /opt/oracle/admin/dia2/cdump
db_block_size                 8192
db_cache_size                 67108864
db_domain                     domain
db_name                       dia2
dispatchers                   (PROTOCOL=TCP)(SER=MODOSE), (PROT
fast_start_mttr_target        300
instance_name                 dia2
java_pool_size                117440512
large_pool_size               1048576
open_cursors                  300
processes                     150
remote_login_passwordfile     EXCLUSIVE
resource_manager_plan         SYSTEM_PLAN
shared_pool_size              117440512
sort_area_size                524288
timed_statistics              TRUE
undo_management               AUTO
undo_tablespace               UNDOTBS
user_dump_dest                /opt/oracle/admin/dia2/udump
          -------------------------------------------------------------

End of Report

Now that we have covered the standard STATSPACK report, let’s look at other customized reports that you can execute to get trend information from your database.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter