Overview
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables. In general, it is recommended to get the snapshots intervals be 15 minutes in length. This allows fine-grained reporting when hangs are suspected/detected. Following is the compilation from metalink document. The statspack report shown here is generated on a machine, which has resources way lower than the minimum requirements to install Oracle10g, dont get carried away by the negative and advisory numbers; this report was pulled out of 10gr2 (10.2.0.4) database.
Timed_statistics must be set to true prior to the creation of a snapshot. If it is not, the data within statspack will not be relevant. You can tell if timed_statistics was not set by looking at the total times columns in the report. If these are zero then timed_statistics was not set.
Snapshots during which the instance was recycled will not contain accurate information and should not be included in a statspack report.
When a value is too large for the statspack field it will be represented by a series of pound signs such as #######. Should this occur and you need to see the value in the field you will need to decrease the number of snapshots in the report until the field can be read. Should there only be one snapshot in the report, then you will need to decrease the snapshot interval.
Summary Information
This part of the report is completely informational – It shows the database name, id, instance name, instance number, time the database was last started, Oracle binary release information, whether the instance is RAC or not, host name, number of CPUs in the host, amount of total physical memory available in the box, snapshort interval information like begin and end time and total elapsed time between two snaps.
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3610930252 pdb10 1 13-Jul-09 09:03 10.2.0.4.0 NO
Host Name: dblx131 Num CPUs: 1 Phys Memory (MB): 376
~~~~
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 1 13-Jul-09 20:24:33 16 4.1
End Snap: 2 13-Jul-09 21:33:28 17 5.5
Elapsed: 68.92 (mins)
Cache Information
This section provides information on buffer cache, shared pool size, std block size (indicates the primary blocksize of the instance) and log buffer size. Note that the buffer cache size is that of the standard buffer cache. If you have multiple buffer caches, you will need to calculate the others separately.
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 56M Std Block Size: 8K
Shared Pool Size: 92M Log Buffer: 2,812K
Load Profile
The load profile information is next; the load profile is measuring in bytes.
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 308,329.39 2,684,088.50
Logical reads: 1,432.85 12,473.30
Block changes: 2,372.47 20,652.94
Physical reads: 3.01 26.25
Physical writes: 17.68 153.95
User calls: 0.84 7.31
Parses: 2.28 19.83
Hard parses: 0.06 0.52
Sorts: 1.85 16.08
Logons: 0.04 0.35
Executes: 3.25 28.32
Transactions: 0.11
% Blocks changed per Read: 165.58 Recursive Call %: 96.52
Rollback per transaction %: 0.00 Rows per Sort: 4.14
Where:
- Redo size: This is the amount of redo generated during this report.
- Logical Reads: This is calculated as Consistent Gets + DB Block Gets = Logical Reads
- Block changes: The number of blocks modified during the sample interval
- Physical Reads: The number of requests for a block that caused a physical I/O.
- Physical Writes: The number of physical writes issued.
- User Calls: The number of queries generated
- Parses: Total of all parses: both hard and soft
- Hard Parses: Those parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
- Soft Parses: Not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse and hence consumes far fewer resources.
- Sorts, Logons, Executes and Transactions are all self explanatory
Instance Efficiency Percentages
Hit ratios are calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning never must be driven by hit ratios. They only provide additional information to understand how the instance is operating. For example, in a DSS systems a low cache hit ratio may be acceptable due the amount of recycling needed due the large volume of data accesed. So if you increase the size of the buffer cache based on this number, the corrective action may not take affect and you may be wasting memory resources
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.79 In-memory Sort %: 100.00
Library Hit %: 95.67 Soft Parse %: 97.38
Execute to Parse %: 30.00 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 52.80 % Non-Parse CPU: 98.63
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 87.51 86.21
% SQL with executions>1: 88.88 94.80
% Memory for SQL w/exec>1: 83.27 92.31
Top 5 Timed Events
This section shows the Top 5 timed events that must be considered to focus the tuning efforts. Before Oracle 9.2 this section was called “Top 5 Wait Events”. It was renamed in Oracle 9.2 to “Top 5 Timed Events” to include the “CPU Time” based on the ‘CPU used by this session’. This information will allow you to determine SQL tuning problems.
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 2,787 52.6
log file parallel write 2,063 1,040 504 19.6
control file parallel write 1,828 731 400 13.8
enq: TX - row lock contention 64 203 3174 3.8
log file switch completion 186 145 778 2.7
-------------------------------------------------------------
CPU and Memory Statistics
Host CPU (CPUs: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.35 0.72 53.91 18.00 19.30 7.38
Note: There is a 8% discrepancy between the OS Stat total CPU time and
the total CPU time estimated by Statspack
OS Stat CPU time: 3798(s) (BUSY_TIME + IDLE_TIME)
Statspack CPU time: 4135(s) (Elapsed time * num CPUs in end snap)
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 76.64
% of busy CPU for Instance: 94.97
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 376.5 376.5
SGA use (MB): 160.0 160.0
PGA use (MB): 39.0 39.7
% Host Mem used for SGA+PGA: 52.8 53.1
-------------------------------------------------------------
Time Model System Statistics
This section show where your time is going with respect to system stats
Time Model System Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
sql execute elapsed time 6,194.1 98.7
DB CPU 2,751.7 43.8
PL/SQL execution elapsed time 1,589.3 25.3
parse time elapsed 77.5 1.2
hard parse elapsed time 67.6 1.1
connection management call elapsed 3.2 .1
PL/SQL compilation elapsed time 1.3 .0
repeated bind elapsed time 0.0 .0
failed parse elapsed time 0.0 .0
DB time 6,278.5
background elapsed time 2,995.0
background cpu time 159.0
-------------------------------------------------------------
Wait Event (Foreground)
Foreground wait events are those associated with a session or client process waiting for a resource.
Wait Events DB/Inst: PDB10/pdb10 Snaps: 1-2
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 2,063 0 1,040 504 4.3
control file parallel write 1,828 0 731 400 3.8
enq: TX - row lock contention 64 97 203 3174 0.1
log file switch completion 186 48 145 778 0.4
log buffer space 119 61 104 877 0.3
db file sequential read 6,120 0 93 15 12.9
log file sync 223 13 76 342 0.5
db file scattered read 673 0 43 63 1.4
latch: redo allocation 200 0 14 69 0.4
Streams AQ: qmn coordinator waiti 2 100 10 5132 0.0
enq: RO - fast object reuse 5 60 10 2042 0.0
buffer busy waits 112 0 9 81 0.2
os thread startup 8 50 6 757 0.0
local write wait 54 0 6 102 0.1
control file sequential read 3,514 0 4 1 7.4
latch free 1 0 4 3986 0.0
log file single write 50 0 4 79 0.1
LGWR wait for redo copy 308 63 3 8 0.6
latch: redo copy 22 0 2 109 0.0
latch: cache buffers chains 24 0 2 80 0.1
latch: shared pool 7 0 1 117 0.0
rdbms ipc reply 60 0 0 7 0.1
enq: FB - contention 1 0 0 420 0.0
enq: CF - contention 2 0 0 117 0.0
log file sequential read 50 0 0 3 0.1
enq: TX - contention 1 0 0 98 0.0
latch: object queue header operat 1 0 0 93 0.0
latch: redo writing 2 0 0 44 0.0
direct path write 170 0 0 0 0.4
undo segment extension 421 100 0 0 0.9
latch: library cache 4 0 0 6 0.0
direct path read 168 0 0 0 0.4
read by other session 1 0 0 2 0.0
SQL*Net break/reset to client 4 0 0 1 0.0
Streams AQ: qmn slave idle wait 140 1 4,161 29719 0.3
Streams AQ: qmn coordinator idle 275 52 4,027 14644 0.6
virtual circuit status 126 100 4,001 31758 0.3
jobq slave wait 1,279 95 3,966 3101 2.7
SQL*Net message from client 2,449 0 2,540 1037 5.2
Streams AQ: waiting for time mana 2 50 406 ###### 0.0
SQL*Net message to client 2,450 0 0 0 5.2
-------------------------------------------------------------
Background Wait Events
Background Wait Events DB/Inst: PDB10/pdb10 Snaps: 1-2
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file parallel write 2,062 0 1,040 504 4.3
control file parallel write 1,828 0 731 400 3.8
db file sequential read 2,204 0 37 17 4.6
events in waitclass Other 415 47 14 33 0.9
db file scattered read 247 0 13 52 0.5
os thread startup 8 50 6 757 0.0
log file single write 50 0 4 79 0.1
control file sequential read 2,505 0 4 2 5.3
latch: shared pool 6 0 1 135 0.0
latch: cache buffers chains 10 0 0 22 0.0
log file sequential read 50 0 0 3 0.1
latch: redo writing 2 0 0 44 0.0
latch: redo copy 2 0 0 32 0.0
buffer busy waits 13 0 0 1 0.0
direct path read 168 0 0 0 0.4
direct path write 168 0 0 0 0.4
rdbms ipc message 14,251 79 32,038 2248 30.0
Streams AQ: qmn slave idle wait 135 0 4,039 29921 0.3
pmon timer 1,419 100 4,029 2839 3.0
Streams AQ: qmn coordinator idle 275 52 4,027 14644 0.6
smon timer 34 18 3,924 ###### 0.1
Streams AQ: waiting for time mana 2 50 406 ###### 0.0
-------------------------------------------------------------
Examples of background system processes are LGWR and DBWR. An example of a non-system background process would be a parallel query slave. Note that it is possible for a wait event to appear in both the foreground and background wait events statistics. Examples of this are the enqueue and latch free events. The idle wait events appear at the bottom of both sections and can generally safely be ignored. Typically these type of events keep record of the time while the client is connected to the database but not requests are being made to the server.
- The idle wait events associated with pipes are often a major source of concern for some DBAs. Pipe gets and waits are entirely application dependent.
- global cache cr request: (OPS) This wait event shows the amount of time that an instance has waited for a requested data block for a consistent read and the transferred block has not yet arrived at the requesting instance.
- Buffer busy waits, write complete waits, db file parallel writes and enqueue waits: If all of these are in the top wait events the client may be experiencing disk saturation.
- log file switch, log file sync or log switch/archive: If the waits on these events appears excessive check for checkpoint tuning issues.
- write complete waits, free buffer waits or buffer busy waits: If any of these wait events is high, the buffer cache may need tuning.
- latch free: If high, the latch free wait event indicates that there was contention on one or more of the primary latches used by the instance.
Wait Event Histogram
The total wait events are bucketed as <1ms, <2ms, <4ms, <8ms, <16ms, <32ms, <=1s and >1s
Wait Event Histogram DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
LGWR wait for redo copy 308 24.7 1.6 3.2 4.5 61.0 3.9 1.0
SQL*Net break/reset to cli 4 75.0 25.0
Streams AQ: qmn coordinato 2 100.0
buffer busy waits 112 37.5 62.5
control file parallel writ 1828 1.7 91.2 7.1
control file sequential re 3514 98.2 .1 .3 .1 .3 .5 .5
db file scattered read 673 38.3 15.6 4.2 1.6 1.2 1.9 36.1 1.0
db file sequential read 6132 61.8 .0 7.9 6.5 9.3 8.2 6.1 .1
direct path read 168 100.0
direct path write 170 99.4 .6
enq: CF - contention 2 100.0
enq: FB - contention 1 100.0
enq: RO - fast object reus 5 20.0 20.0 60.0
enq: TX - contention 1 100.0
enq: TX - row lock content 64 1.6 98.4
latch free 1 100.0
latch: In memory undo latc 1 100.0
latch: cache buffers chain 23 39.1 4.3 4.3 52.2
latch: cache buffers lru c 4 100.0
latch: library cache 4 75.0 25.0
latch: messages 1 100.0
latch: object queue header 1 100.0
latch: redo allocation 178 23.0 .6 .6 .6 4.5 3.4 67.4
latch: redo copy 18 5.6 5.6 11.1 77.8
latch: redo writing 2 50.0 50.0
latch: shared pool 7 14.3 28.6 57.1
local write wait 54 1.9 98.1
log buffer space 119 1.7 78.2 20.2
log file parallel write 2063 .1 .4 6.4 4.5 5.8 74.5 8.2
log file sequential read 50 94.0 2.0 2.0 2.0
log file single write 50 14.0 6.0 32.0 46.0 2.0
log file switch completion 186 2.7 82.8 14.5
log file sync 223 4.9 2.2 4.9 6.3 3.1 70.0 8.5
os thread startup 8 75.0 25.0
rdbms ipc reply 60 88.3 3.3 3.3 5.0
read by other session 1 100.0
undo segment extension 421 99.8 .2
SQL*Net message from clien 2449 4.2 44.8 17.1 18.8 6.4 1.9 6.4 .4
SQL*Net message to client 2450 100.0
Streams AQ: qmn coordinato 275 48.0 .4 51.6
Streams AQ: qmn slave idle 140 100.0
Streams AQ: waiting for ti 2 50.0 50.0
dispatcher timer 63 100.0
jobq slave wait 1280 .1 2.3 97.6
pmon timer 1419 10.1 .2 .2 .2 .1 89.1
rdbms ipc message 14K 4.1 .1 .1 .3 .4 .8 27.2 67.1
smon timer 34 32.4 67.6
virtual circuit status 126 100.0
-------------------------------------------------------------
SQL Information
The SQL that is stored in the shared pool SQL area (Library cache) is reported to the user via
- SQL ordered by Buffer Gets
This section reports the contents of the SQL area ordered by the number of buffer gets and can be used to identify CPU Heavy SQL. The statements of interest are those with a large number of gets per execution especially if the number of executions is high. High buffer gets generally correlates with heavy CPU usage.
CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value ------------ ------------ -------------- ------ -------- --------- ----------
- SQL ordered by Reads
This section reports the contents of the SQL area ordered by the number of reads from the data files and can be used to identify SQL causing IO bottlenecks which consume the following resources.
- CPU time needed to fetch unnecessary data.
- File IO resources to fetch unnecessary data.
- Buffer resources to hold unnecessary data.
- Additional CPU time to process the query once the data is retrieved into the buffer.
CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ----------
- SQL ordered by Executions
This section reports the contents of the SQL area ordered by the number of query executions. It is primarily useful in identifying the most frequently used SQL within the database so that they can be monitored for efficiency. Generally speaking, a small performance increase on a frequently used query provides greater gains than a moderate performance increase on an infrequently used query
CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ----------
- SQL ordered by Parse Calls
This section shows the number of times a statement was parsed as compared to the number of times it was executed. One to one parse/executions may indicate that:
- Bind variables are not being used.
- Parameter session_cached_cursors was not set properly
- The shared pool may be too small and the parse is not being retained long enough for multiple executions
- cursor_sharing is set to exact (this should NOT be changed without considerable testing on the part of the client).
% Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ----------
Instance Level Statistics
The statistics section shows the overall database statistics.
Instance Activity Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 278,674 67.4 586.7
CPU used when call started 275,504 66.6 580.0
CR blocks created 5,132 1.2 10.8
... output ommited for brevity...
DBWR checkpoint buffers written 9,414 2.3 19.8
... output ommited for brevity...
IMU CR rollbacks 1 0.0 0.0
... output ommited for brevity...
OS Involuntary context switches 115,027 27.8 242.2
... output ommited for brevity...
SMON posted for undo segment shri 15 0.0 0.0
SQL*Net roundtrips to/from client 2,079 0.5 4.4
active txn count during cleanout 16,598 4.0 34.9
application wait time 21,334 5.2 44.9
background checkpoints completed 24 0.0 0.1
... output ommited for brevity...
cleanout - number of ktugct calls 15,198 3.7 32.0
cleanouts and rollbacks - consist 1,714 0.4 3.6
cleanouts only - consistent read 428 0.1 0.9
cluster key scan block gets 4,364 1.1 9.2
cluster key scans 540 0.1 1.1
commit batch/immediate performed 1 0.0 0.0
commit batch/immediate requested 1 0.0 0.0
commit cleanout failures: block l 98 0.0 0.2
... output ommited for brevity...
consistent changes 378,563 91.6 797.0
consistent gets 489,611 118.4 1,030.8
... output ommited for brevity...
enqueue conversions 907 0.2 1.9
enqueue releases 35,828 8.7 75.4
enqueue requests 35,830 8.7 75.4
enqueue timeouts 2 0.0 0.0
enqueue waits 6 0.0 0.0
... output ommited for brevity...
physical reads 12,467 3.0 26.3
physical reads cache 12,299 3.0 25.9
physical reads cache prefetch 5,506 1.3 11.6
... output ommited for brevity...
recursive calls 96,233 23.3 202.6
recursive cpu usage 7,845 1.9 16.5
redo blocks written 2,577,344 623.3 5,426.0
redo buffer allocation retries 232 0.1 0.5
... output ommited for brevity...
rollback changes - undo records a 189,610 45.9 399.2
... output ommited for brevity...
session cursor cache hits 5,061 1.2 10.7
... output ommited for brevity...
sorts (rows) 31,648 7.7 66.6
sql area evicted 279 0.1 0.6
sql area purged 3 0.0 0.0
... output ommited for brevity...
table scan rows gotten 12,138,217 2,935.5 25,554.1
table scans (long tables) 93 0.0 0.2
table scans (short tables) 2,413 0.6 5.1
... output ommited for brevity...
workarea executions - optimal 3,000 0.7 6.3
write clones created in backgroun 61 0.0 0.1
write clones created in foregroun 210 0.1 0.4
... output ommited for brevity...
logons current 16 17
opened cursors current 65 94
session cursor cache count 12,768 15,424
... output ommited for brevity...
Instance Activity Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
--------------------------------- ------------------ ---------
log switches (derived) 25 21.77
-------------------------------------------------------------
OS Statistics
OS Statistics DB/Inst: PDB10/pdb10 Snaps: 1-2
-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name
Statistic Total
------------------------- ----------------------
BUSY_TIME 306,488
IDLE_TIME 73,286
IOWAIT_TIME 28,010
NICE_TIME 3,416
SYS_TIME 68,362
USER_TIME 204,750
LOAD 1
PHYSICAL_MEMORY_BYTES 394,760,192
NUM_CPUS 1
-------------------------------------------------------------
Tablespace IO Stats
IO ActivityInput/Output (IO) statistics for the instance – Ordered by total IO per tablespace.
Tablespace IO Stats DB/Inst: PDB10/pdb10 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)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS1
1,637 0 2.0 1.0 18,689 5 219 858.7
SYSTEM
12,812 3 21.8 1.7 1,912 0 4 70.0
SYSAUX
4,804 1 56.2 1.1 5,652 1 0 0.0
File IO Stats
IO ActivityInput/Output (IO) statistics for the instance – Ordered alphabetically by tablespace, filename.
File IO Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
->Mx Rd Bkt: Max bucket time for single block read
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Mx Av
Av Rd Rd Av Av Buffer BufWt
Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms)
-------------- ------- ----- --- ------- ------------ -------- ---------- ------
EXAMPLE /u01/app/oradata/pdb10/example01.dbf
35 0 3.4 1.0 27 0 0
SYSAUX /u01/app/oradata/pdb10/sysaux01.dbf
4,804 1 56.2 ### 1.1 5,652 1 0
SYSTEM /u01/app/oradata/pdb10/system01.dbf
12,812 3 21.8 ### 1.7 1,912 0 4 70.0
Note that Oracle considers average read times of greater than 20 ms unacceptable. If a datafile consistently has average read times of 20 ms or greater then:
- The queries against the contents of the owning tablespace should be examined and tuned so that less data is retrieved.
- If the tablespace contains indexes, another option is to compress the indexes so that they require less space and hence, less IO.
- The contents of that datafile should be redistributed across several disks/logical volumes to more easily accommodate the load.
- If the disk layout seems optimal, check the disk controller layout. It may be that the datafiles need to be distributed across more disk sets.
File Read Histogram Stats
This shows the number of single block reads in each time range
File Read Histogram Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
->Number of single block reads in each time range
->ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms
------------ ------------ ------------ ------------ ------------ ------------
UNDOTBS1 /u01/app/oradata/pdb10/undotbs01.dbf
1,551 10 1 1 2 14
SYSAUX /u01/app/oradata/pdb10/sysaux01.dbf
49 105 158 253 290 110
Buffer Pool Statistics
This section can have multiple entries if multiple buffer pools are allocated. A baseline of the database’s buffer pool statistics should be available to compare with the current statspack buffer pool statistics. A change in that pattern unaccounted for by a change in workload should be a cause for concern.
Buffer Pool Statistics DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
-> Buffers: the number of buffers. Units of K, M, G are divided by 1000
Free Writ Buffer
Pool Buffer Physical Physical Buffer Comp Busy
P Buffers Hit% Gets Reads Writes Waits Wait Waits
--- ------- ---- -------------- ------------ ----------- ------- ---- ----------
D 6986 100 5,925,923 12,282 72,944 0 0 113
-------------------------------------------------------------
Instance Recovery Stats
This section is useful for monitoring the recovery and redo information.
Instance Recovery Stats DB/Inst: PDB10/pdb10 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 0 44 356 1565 5816 184320 5816
E 0 52 1894 176216 184320 184320 573047
-------------------------------------------------------------
Buffer Pool Advisory
This section shows you what will be the estimated physical reads, read time db time for reads, if you change the sizing factor.
Buffer Pool Advisory DB/Inst: PDB10/pdb10 End Snap: 2
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 4 .1 0 2.3 163 3,220 25.6
D 8 .1 1 1.4 100 1,500 11.9
D 12 .2 1 1.3 95 1,354 10.8
D 16 .3 2 1.2 87 1,149 9.1
D 20 .4 2 1.2 83 1,030 8.2
D 24 .4 3 1.1 79 928 7.4
D 28 .5 3 1.1 77 861 6.8
D 32 .6 4 1.1 75 813 6.5
D 36 .6 4 1.0 74 774 6.1
D 40 .7 5 1.0 73 755 6.0
D 44 .8 5 1.0 72 733 5.8
D 48 .9 6 1.0 71 711 5.6
D 52 .9 6 1.0 71 695 5.5
D 56 1.0 7 1.0 70 685 5.4
D 60 1.1 7 1.0 70 667 5.3
D 64 1.1 8 1.0 69 660 5.2
D 68 1.2 8 1.0 69 650 5.2
D 72 1.3 9 1.0 69 638 5.1
D 76 1.4 9 1.0 68 630 5.0
D 80 1.4 10 1.0 67 607 4.8
-------------------------------------------------------------
Buffer wait Statistics
This section shows a breakdown of each type of object waited for
Buffer wait Statistics DB/Inst: PDB10/pdb10 Snaps: 1-2
-> ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
---------------------- ----------- ------------------- -------------
data block 94 8 83
undo header 17 0 26
1st level bmb 2 0 115
-------------------------------------------------------------
PGA Aggr Target Stats
This section shows PGA aggregate target statistics and advices if it is low:
PGA Aggr Target Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
-> B: Begin snap E: End snap (rows identified with B or E contain data
which is absolute i.e. not diffed over the interval)
-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
-> Auto PGA Target - actual workarea memory target
-> W/A PGA Used - amount of memory used for all Workareas (manual + auto)
-> %PGA W/A Mem - percentage of PGA memory allocated to workareas
-> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
-> %Man W/A Mem - percentage of workarea memory under manual control
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
--------------- ---------------- -------------------------
100.0 43 0
Warning: pga_aggregate_target was set too low for current workload, as this
value was exceeded during this interval. Use the PGA Advisory view
to help identify a different value for pga_aggregate_target.
%PGA %Auto %Man
PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem
Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K)
- --------- --------- ---------- ---------- ------ ------ ------ ----------
B 16 4 39.0 0.0 .0 .0 .0 3,276
E 16 4 39.7 0.0 .0 .0 .0 3,276
-------------------------------------------------------------
PGA Aggregate Target Histogram
This section shows low, high optimal, total executions, optimal executions, one pass executions, and multipass executions.
PGA Aggr Target Histogram DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Optimal Executions are purely in-memory operations
Low High
Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- ------------- ------------ ------------
2K 4K 2,906 2,906 0 0
64K 128K 13 13 0 0
128K 256K 2 2 0 0
256K 512K 2 2 0 0
512K 1024K 48 48 0 0
-------------------------------------------------------------
PGA Memory Advisory
This section provides the sizing factor and what if analysis on pga cache hit and etc.
PGA Memory Advisory DB/Inst: PDB10/pdb10 End Snap: 2
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
where Estd PGA Overalloc Count is 0
Estd Extra Estd PGA Estd PGA
PGA Target Size W/A MB W/A MB Read/ Cache Overalloc
Est (MB) Factr Processed Written to Disk Hit % Count
---------- ------- ---------------- ---------------- -------- ----------
12 0.8 398.1 69.5 85.0 29
16 1.0 398.1 0.0 100.0 29
19 1.2 398.1 0.0 100.0 29
22 1.4 398.1 0.0 100.0 15
26 1.6 398.1 0.0 100.0 2
29 1.8 398.1 0.0 100.0 0
32 2.0 398.1 0.0 100.0 0
48 3.0 398.1 0.0 100.0 0
64 4.0 398.1 0.0 100.0 0
96 6.0 398.1 0.0 100.0 0
128 8.0 398.1 0.0 100.0 0
-------------------------------------------------------------
Process Memory Summary Stats
Process Memory Summary Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
-> B: Begin snap E: End snap
-> All rows below contain absolute values (i.e. not diffed over the interval)
-> Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of
processes. For Category lines, it is the number of allocations
-> ordered by Begin/End snapshot, Alloc (MB) desc
Hist Num
Avg Std Dev Max Max Procs
Alloc Used Freeabl Alloc Alloc Alloc Alloc or
Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs
- -------- --------- --------- -------- -------- ------- ------- ------ ------
B -------- 39.0 18.5 4.3 2.2 5.0 22 22 18
Other 34.1 1.9 5.0 22 22 18
Freeable 4.3 .0 .7 .3 1 6
SQL .6 .3 .1 .1 0 2 8
PL/SQL .1 .1 .0 .0 0 0 16
E -------- 39.7 19.0 4.2 2.1 4.9 22 22 19
Other 34.9 1.8 4.9 22 22 19
Freeable 4.2 .0 .6 .3 1 7
SQL .6 .3 .1 .1 0 2 9
PL/SQL .1 .1 .0 .0 0 0 17
-------------------------------------------------------------
Top Process Memory (by component)
Top Process Memory (by component) DB/Inst: PDB10/pdb10 Snaps: 1-2
-> ordered by Begin/End snapshot, Alloc (MB) desc
Alloc Used Freeabl Max Hist Max
PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB)
- ------ ------------- ------- ------- -------- ---------- ----------
B 6 LGWR -------- 21.8 10.3 .0 21.8 21.8
Other 21.8 21.8 21.8
PL/SQL .0 .0 .0 .0
19 ------------ 3.8 2.1 .7 3.8 3.8
Other 3.0 3.0 3.0
Freeable .7 .0 .7
SQL .2 .1 .2 1.3
PL/SQL .0 .0 .0 .0
Enqueue activity
Enqueue activity DB/Inst: PDB10/pdb10 Snaps: 1-2
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason)
------------------------------------------------------------------------------
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
------------ ------------ ----------- ----------- ------------ --------------
TX-Transaction (row lock contention)
2 2 0 2 190 94,755.00
RO-Multiple Object Reuse (fast object reuse)
9 9 0 1 10 9,930.00
FB-Format Block
224 224 0 1 0 290.00
CF-Controlfile Transaction
1,428 1,426 2 1 0 170.00
TX-Transaction
1,041 1,041 0 1 0 100.00
-------------------------------------------------------------
An enqueue is simply a locking mechanism. The action to take depends on the lock type that is causing the most problems. The most common lock waits are generally for:
TX – Transaction Lock: Generally due to application concurrency mechanisms, or table setup issues.
TM – DML enqueue: Generally due to application issues, particularly if foreign key constraints have not been indexed.
ST – Space management enqueue: Usually caused by too much space management occurring. For example: create table as select on large tables on busy instances, small extent sizes, lots of sorting, etc.
Undo Segment Summary
Undo Segment Summary DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out Of Space count
-> Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/
TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU
---- ---------- --------------- -------- ---------- --------- ----- -----------
1 59.7 1,063 49 6 15/15 0/0 0/0/0/4/307
2/0
-------------------------------------------------------------
Undo Segment Stats
Undo Segment Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
-> Most recent 35 Undostat rows, ordered by End Time desc
Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/
End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- -----------
13-Jul 21:33 9,186 136 0 3 15 0/0 0/0/0/4/307
2/0
13-Jul 21:23 10,101 159 0 3 15 0/0 0/0/0/0/0/0
13-Jul 21:13 9,848 84 0 3 15 0/0 0/0/0/0/0/0
13-Jul 21:03 9,277 185 49 5 15 0/0 0/0/0/0/0/0
13-Jul 20:53 8,359 71 0 4 15 0/0 0/0/0/0/0/0
13-Jul 20:43 8,364 112 0 6 15 0/0 0/0/0/0/0/0
13-Jul 20:33 4,563 316 0 3 15 0/0 0/0/0/0/0/0
-------------------------------------------------------------
Latch Activity
This information should be checked whenever the “latch free” wait event or other latch wait events experience long waits. Latch information is provided in the following three sections.
1. Latch Activity
Latch Activity DB/Inst: PDB10/pdb10 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
Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
ASM db client latch 2,381 0.0 0 0
AWR Alerted Metric Eleme 12,216 0.0 0 0
Consistent RBA 2,088 0.0 0 0
2. Latch Sleep breakdown
atch Sleep breakdown DB/Inst: PDB10/pdb10 Snaps: 1-2
-> ordered by misses desc
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
redo allocation 9,878 178 200 0
cache buffers chains 30,717,269 23 24 0
redo copy 108 18 22 0
3. Latch Miss Sources
Latch Miss Sources DB/Inst: PDB10/pdb10 Snaps: 1-2
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
In memory undo latch ktichg: child 0 1 0
cache buffers chains kcbchg: kslbegin: bufs not 0 8 3
cache buffers chains kcbgcur: kslbegin 0 8 7
Dictionary Cache Stats
This section is self-explanatory
Dictionary Cache Stats DB/Inst: PDB10/pdb10 Snaps: 1-2
->"Pct Misses" should be very low (< 2% in most cases)
->"Final Usage" is the number of cache entries being used in End Snapshot
Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_awr_control 69 0.0 0 2 1
dc_database_links 95 0.0 0 0 1
dc_global_oids 1,925 0.0 0 0 21
dc_histogram_data 520 2.1 0 0 821
Library Cache Activity
Library Cache Activity DB/Inst: PDB10/pdb10 Snaps: 1-2
->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
SQL AREA 4,021 10.1 19,952 3.7 12 141
TABLE/PROCEDURE 1,687 6.2 6,880 7.4 28 0
BODY 1,126 0.0 1,957 0.0 0 0
TRIGGER 67 0.0 210 0.0 0 0
INDEX 4 0.0 30 10.0 3 0
CLUSTER 2 0.0 5 20.0 1 0
-------------------------------------------------------------
Rule Sets
Rule Sets DB/Inst: PDB10/pdb10 Snaps: 1-2
-> * indicates Rule Set activity (re)started between Begin/End snaps
-> Top 25 ordered by Evaluations desc
No-SQL SQL
Rule * Eval/sec Reloads/sec Eval % Eval %
----------------------------------- - ------------ ----------- ------ ------
Shared Pool Advisory
Shared Pool Advisory DB/Inst: PDB10/pdb10 End Snap: 2
-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid
Est LC Est LC Est LC Est LC
Shared SP Est LC Time Time Load Load Est LC
Pool Size Size Est LC Saved Saved Time Time Mem
Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
56 .6 13 1,208 14,897 .8 5,076 13.8 224,298
68 .7 23 2,355 18,236 .9 1,737 4.7 227,669
80 .9 35 3,295 19,580 1.0 393 1.1 229,225
92 1.0 47 4,338 19,605 1.0 368 1.0 230,193
104 1.1 59 5,937 19,611 1.0 362 1.0 230,439
116 1.3 71 7,404 19,612 1.0 361 1.0 230,515
128 1.4 74 8,290 19,612 1.0 361 1.0 230,517
140 1.5 74 8,290 19,612 1.0 361 1.0 230,517
152 1.7 74 8,290 19,612 1.0 361 1.0 230,517
164 1.8 74 8,290 19,612 1.0 361 1.0 230,517
176 1.9 74 8,290 19,612 1.0 361 1.0 230,517
188 2.0 74 8,290 19,612 1.0 361 1.0 230,517
-------------------------------------------------------------
SGA Target Advisory
SGA Target Advisory DB/Inst: PDB10/pdb10 End Snap: 2
SGA Target SGA Size Est DB Est DB Est Physical
Size (M) Factor Time (s) Time Factor Reads
---------- -------- -------- ----------- --------------
80 .5 17,992 1.4 96,234
120 .8 12,813 1.0 77,968
160 1.0 12,591 1.0 70,172
200 1.3 12,527 1.0 68,579
240 1.5 12,525 1.0 68,579
280 1.8 12,525 1.0 68,579
320 2.0 12,525 1.0 68,579
-------------------------------------------------------------
SGA Memory Summary
SGA Memory Summary DB/Inst: PDB10/pdb10 Snaps: 1-2
End Size (Bytes)
SGA regions Begin Size (Bytes) (if different)
------------------------------ -------------------- --------------------
Database Buffers 58,720,256
Fixed Size 1,266,392
Redo Buffers 2,924,544
Variable Size 104,860,968
-------------------- --------------------
sum 167,772,160
-------------------------------------------------------------
SGA breakdown difference
SGA breakdown difference DB/Inst: PDB10/pdb10 Snaps: 1-2 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- -------- java p free memory 4.0 4.0 0.00 large PX msg pool .3 .3 0.00 large free memory 3.8 3.8 0.00 shared ASH buffers 2.0 2.0 0.00 shared CCursor 5.3 5.5 3.27
SQL Memory Statistics
SQL Memory Statistics DB/Inst: PDB10/pdb10 Snaps: 1-2
Begin End % Diff
-------------- -------------- --------------
init.ora Parameters
init.ora Parameters DB/Inst: PDB10/pdb10 Snaps: 1-2
End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
