| |
 |
|
Sample STATSPACK report (AWR) Analysis
Oracle Database Tips by Donald Burleson |
In the area of expert systems
we strive to codify human decision rules into software that mimics a human
expert. My latest effort in STATSPACK report analysis (in conjunction
with other Oracle tuning experts) is called
www.statspackanalyzer.com , a new
tool to help automate the analysis of Oracle STATSPACK and AWR reports.
Here is a sample output from
statspackanalyzer for a busy Oracle MRP database with over 165,000 logical reads
per second.
Top Timed Events
|
|
Event |
Percentage of Total Timed Events |
| db file sequential
read |
49% |
|
The sequential read event occurs when Oracle reads single blocks of
a table or index. Index reads typically cause this event. |
Moving your indexes to solid state disks can reduce the amount of
time spent waiting for this event. |
| CPU time |
48% |
|
CPU time is the amount of time that the Oracle database spent
processing SQL statements, parsing statements, or managing the
buffer cache. |
Solid state disks help to increase the CPU time by reducing IO
related wait events. If this is the main wait event, tuning SQL
statements and/or increasing server CPU resources will provide the
greatest performance improvement. |
| |
Load Profile
|
| Summary: |
| Logical reads: |
164,756/s |
|
Parses: |
1098/s |
|
| Physical reads: |
1,762/s |
|
Hard parses: |
0/s |
|
| Physical writes: |
30/s |
|
Transactions: |
0/s |
|
| Rollback per transaction
%: |
13.01% |
|
|
|
|
| |
| Custom
Recommendations: |
Your database has relatively high logical I/O at 164,756
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 1,762 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. Check you average disk read speed later in
this report and ensure that it is under 7ms. 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 1,098 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 1,098 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.
|
|
You may have an application issue causing excessive rollbacks
with 13.01% rollbacks per transaction. Due to Oracles 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
|
| Summary: |
| Buffer Hit: |
98% |
|
In-memory Sort: |
100% |
|
| Library Hit: |
99% |
|
Latch Hit: |
99% |
|
| Memory Usage: |
81% |
|
Memory for SQL: |
58% |
|
| |
Wait Events
|
|
Event |
Avg Wait (ms) |
Waits / txn |
|
latch free |
3 |
20.4 |
|
log file sync |
6 |
0.8 |
|
SQL*Net message from client |
11 |
? |
|
db file sequential read |
2 |
3661.3 |
|
db file scattered read |
1 |
244.3 |
|
buffer busy waits |
5 |
13.9 |
|
direct path read |
0 |
13.8 |
|
log file parallel write |
6 |
1.7 |
|
control file parallel write |
2 |
1.3 |
| |
| Custom
Recommendations: |
You have high latch free waits of 20.4 per transaction. 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 excessive buffer busy waits with 13.9 per
transaction. 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. |
| |
Instance Activity Stats
|
|
Statistic |
Total |
per Second |
per Trans |
|
SQL*Net roundtrips to/from client |
10,770,583 |
3,135 |
11,301.8 |
|
consistent gets - examination |
336,550,178 |
97,976 |
353,148.1 |
|
db block changes |
109,743 |
32 |
115.2 |
|
physical reads |
6,055,219 |
1,762 |
6,353.9 |
|
physical reads direct |
91,306 |
26 |
95.8 |
|
physical writes |
105,818 |
30 |
111 |
|
physical writes direct |
91,246 |
26 |
95.8 |
|
redo writes |
1,575 |
0.5 |
1.7 |
|
table fetch continued row |
11,362,529 |
3,307 |
11,922.9 |
|
table scans (long tables) |
432 |
0 |
0.5 |
|
table scans (short tables) |
58,636 |
17 |
61.5 |
| |
| Custom
Recommendations: |
You have high network activity with 3,135 SQL*Net roundtrips
to/from client per second, 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 97,976 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 1,762 per second. 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 11,362,529 table fetch continued row actions during
this period. 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 17 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. |
| |
Tablespace IO Stats
|
|
Tablespace |
Avg Reads (ms) |
% Reads |
% Writes |
% of Total I/O |
|
MDW
|
2.2 |
100% |
0% |
30.8% |
|
MDW_INDEX
|
1.5 |
100% |
0% |
29.69% |
|
MDW_LOB
|
0.1 |
100% |
0% |
2.34% |
|
TEMP02
|
0.1 |
50% |
50% |
0.45% |
|
PERFSTAT
|
6.5 |
56% |
44% |
0.11% |
| |
Latch Activity
|
|
Latch |
Get Requests |
% Get Miss |
% NoWait Miss |
|
cache buffers chains |
803,593,366 |
0.3% |
0% |
|
cache buffers lru chain |
30,611 |
0.2% |
0.2% |
|
library cache |
39,161,832 |
0.6% |
10% |
|
redo allocation |
63,537 |
0% |
?% |
|
redo copy |
0 |
0% |
0.1% |
| |
| Custom
Recommendations: |
You have high cache buffer chain latches with 803,593,366 get
requests at 0.3% get miss. See MOSC about increasing the hidden
parameter _db_block_hash_buckets.
|
You have a high value for cache buffer LRU chain waits with
30,611 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.
|
|
You have high library cache waits with 39,161,832 get
requests at 0.6% get miss. Consider pinning your frequently-used
packages in the library cache with dbms_shared_pool.keep. |
| |
Buffer Pool Advisory
|
| Summary: |
| Current: |
79,007,914
disk reads |
|
| Optimized: |
32,503,580
disk reads |
|
| Estimated Improvement: |
59% fewer |
|
Custom Recommendations: |
|
|
The Oracle buffer cache advisory utility indicates 79,007,914 disk
reads during the sample interval. Oracle estimates that doubling the
data buffer size (by increasing db_cache_size) will reduce disk
reads to 32,503,580, a 59% decrease. |
| |
PGA Memory Advisory
|
| Summary: |
| Current: |
100% cache
hit |
|
| Optimized: |
100% cache
hit |
|
| Estimated Improvement: |
0% higher |
|
| |
Shared Pool Advisory
|
| Summary: |
| Current: |
13,738 cached objects |
|
1,285,079
seconds saved |
|
| Optimized: |
26,108 cached objects |
|
1,285,089
seconds saved |
|
| Estimated Improvement: |
90% more |
|
10s (0% of
DB Time) |
|
Custom Recommendations: |
|
The Oracle shared pool advisory utility indicates 13,738 cached
memory objects during the sample interval. Oracle estimates that
doubling the shared pool size (by increasing shared_pool_size) will
allow for 90% more cached objects and save 0% of the DB Time. |
| |
Init.ora Parameters
|
| Summary: |
| db_block_size |
8,192
|
|
| db_cache_size |
671,088,640 |
|
|
db_file_multiblock_read_count |
16 |
|
| hash_join_enabled |
true |
|
| optimizer_mode |
choose
|
|
| pga_aggregate_target |
1,048,576,000 |
|
| query_rewrite_enabled |
true |
|
| shared_pool_size |
402,653,184 |
|
| sort_area_size |
1,024,000
|
|
| |
| Custom
Recommendations: |
You are using the default optimizer mode and you may consider
setting it to
first_rows or first_rows_n if you have an online transaction
processing system.
|
You have the default value for db_file_multiblock_read_count
at 16. 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 402MB, 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. |
| |
 |
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. |
|
and include the URL for the page.
