| |
 |
|
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 latches Waits
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 latches A
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. |
 |
|
