| |
 |
|
Library cache overload tips
Server Tips by Donald BurlesonFebruary 7, 2015
|
This is the health check I thought was particularly interesting. they
have a shared_pool_size of more than 3g with an sga_max_size of
6g, and over 140,000 queries having gone through their shared pool during the
sample period.
Even with a 3g shared_pool_size, their library hit ratio was only
81%. their db_cache_size is being decreased in favor of increasing
their
shared_pool_size.
This is a perfect example where "throwing hardware at the problem" might be
the wise thing to do.
Rather than spend tens of thousands of dollars in consulting services to tune
140,000 individual SQL statements, this Windows server could be upgraded.
You can get a
16G RAM Dual CPU Dell server w/ 64 bit Windows 2003 for $6,272 from Dell.
(It's also interesting to note that more than half that price is for the Windows
OS).
This database had several issues:
- This server has a severe RAM shortage (a Windows-based 8 gig
mini-server).
- The db_cache_size parameter was not explicitly set.
- The library cache may be clogged with non-reusable SQL (recommending
testing
cursor_sharing=force in TEST instance).
- The MTS is being used due to the RAM shortage.
- There were 36,409 table fetch continued row actions during this period,
indicating a possible reorg opportunity.
- Oracle estimates that doubling the data buffer size (by increasing
db_cache_size) will reduce disk reads to 6,706,805, a 43.37% decrease.
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 1,760M 1,760M Std Block Size:
16K
Shared Pool Size: 3,472M 3,472M Log Buffer:
14,368K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 7,730.21 1,253.21
Logical reads: 10,789.18 1,749.13
Block changes: 50.09 8.12
Physical reads: 202.36 32.81
Physical writes: 6.48 1.05
User calls: 710.47 115.18
Parses: 151.25 24.52
Hard parses: 38.62 6.26
Sorts: 44.52 7.22
Logons: 2.29 0.37
Executes: 221.30 35.88
Transactions: 6.17
% Blocks changed per Read: 0.46 Recursive Call %: 29.46
Rollback per transaction %: 26.51 Rows per Sort: 40.84
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 98.13 In-memory Sort %: 100.00
Library Hit %: 81.11 Soft Parse %: 74.47
Execute to Parse %: 31.66 Latch Hit %: 99.53
Parse CPU to Parse Elapsd %: 97.38 % Non-Parse CPU: 70.63
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 83.27 83.60
% SQL with executions>1: 50.84 42.52
% Memory for SQL w/exec>1: 49.93 39.60
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait
Class
------------------------------ ------------ ----------- ------ ------
----------
CPU time 2,287 61.5
db file sequential read 464,784 1,300 3 34.9 User
I/O
SQL*Net more data to client 124,791 216 2 5.8
Network
db file scattered read 62,694 137 2 3.7 User
I/O
SQL*Net more data from client 19,153 128 7 3.4
Network
. . .
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute
elapsed time 2,976.0 80.0
DB
CPU 2,287.3 61.5
parse time
elapsed 695.9 18.7
hard parse
elapsed time 638.0 17.1
. . .
Buffer Pool
Advisory DB/Inst: DBSERV4/dbserv4 Snap: 2228
-> Only rows with
estimated physical reads >0 are displayed
-> ordered by
Block Size, Buffers For Estimate
Est
Phys
Size for
Size Buffers for Read Estimated
P Est (M)
Factor Estimate Factor Physical Reads
--- --------
------ ---------------- ------ ------------------
D 176
.1 11,055 6.9 81,795,711
D 352
.2 22,110 5.2 62,053,564
D 528
.3 33,165 3.9 46,742,706
D 704
.4 44,220 3.0 35,642,309
D 880
.5 55,275 2.3 27,610,783
D 1,056
.6 66,330 1.8 21,869,777
D 1,232
.7 77,385 1.5 17,823,897
D 1,408
.8 88,440 1.3 14,999,174
D 1,584
.9 99,495 1.1 13,091,025
D 1,760 1.0 110,550 1.0 11,843,618
D 1,936
1.1 121,605 0.9 11,059,738
D 2,112
1.2 132,660 0.9 10,606,937
D 2,288
1.3 143,715 0.9 10,468,715
D 2,464
1.4 154,770 0.9 10,311,667
D 2,640
1.5 165,825 0.9 10,107,694
D 2,816
1.6 176,880 0.8 9,812,963
D 2,992
1.7 187,935 0.8 9,374,878
D 3,168
1.8 198,990 0.7 8,740,714
D 3,344
1.9 210,045 0.7 7,855,100
D 3,520 2.0 221,100 0.6
6,706,805
. . .
Shared Pool
Advisory DB/Inst: DBSERV4/dbserv4 Snap: 2228
-> 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
---------- -----
-------- ------------ ------- ------ ------- ------ -----------
1,008
.3 362 33,701 ####### 1.0 44,804 2.6 21,162,018
1,360
.4 713 44,398 ####### 1.0 40,566 2.3 21,328,978
1,712
.5 1,064 55,229 ####### 1.0 36,985 2.1 21,452,040
2,064
.6 1,415 66,785 ####### 1.0 33,733 1.9 21,541,605
2,416
.7 1,766 79,413 ####### 1.0 30,562 1.7 21,607,376
2,768
.8 2,117 91,824 ####### 1.0 27,091 1.5 21,657,040
3,120
.9 2,468 105,262 ####### 1.0 22,847 1.3 21,695,904
3,472
1.0 2,819 116,829 ####### 1.0 17,515 1.0 21,728,015
3,824
1.1 3,170 129,551 ####### 1.0 11,148 .6 21,755,574
4,176
1.2 3,521 140,414 ####### 1.0 4,178 .2 21,780,014
4,528
1.3 3,872 153,339 ####### 1.0 1 .0 21,802,168
4,880
1.4 4,223 165,895 ####### 1.0 1 .0 21,822,587
5,232
1.5 4,574 176,887 ####### 1.1 1 .0 21,841,530
5,584
1.6 4,925 188,373 ####### 1.1 1 .0 21,859,404
5,936
1.7 5,276 199,737 ####### 1.1 1 .0 21,876,191
6,288
1.8 5,627 212,322 ####### 1.1 1 .0 21,892,180
6,640
1.9 5,978 223,927 ####### 1.1 1 .0 21,907,982
6,992
2.0 6,328 235,864 ####### 1.1 1 .0 21,923,666
-------------------------------------------------------------
Top 5 Events
|
| Event |
Percentage of Total Timed Events |
| CPU time |
61.5% |
| db file sequential read |
34.9% |
| SQL*Net more data to client |
5.8% |
| db file scattered read |
3.7% |
| SQL*Net more data from client |
3.4% |
| |
Top 5 Events
|
| Event |
Percentage of Total Timed Events |
| CPU time |
61.5% |
| CPU time is the amount of time that the Oracle
database spent processing SQL statements, parsing statements, or
managing the buffer cache. |
If this is the main timed event,
tuning SQL statements and/or increasing server CPU resources will
provide the greatest performance improvement. |
| db file sequential read |
34.9% |
| The sequential read event occurs when Oracle
reads single blocks of a table or index. Look at the
tablespace IO section of the report for tablespaces with
less than 2 average blocks per read, high response time, and a large
percentage of the total IO. Improving the response time of these
tables with
faster storage will help reduce this wait event and speed up the
database. |
Moving the data files with the
largest amount of time spend waiting on single-block reads to faster
storage can significantly reduce the amount of time spent waiting on
this event. By reducing the time spent waiting on this event, the
database performance could increase 54%. |
| SQL*Net more data to client |
5.8% |
| Other |
|
| db file scattered read |
3.7% |
| The scattered read events occur when Oracle reads
multiple blocks of a table or index. Look at the
tablespace IO section of the report for tablespaces with
more than 2 average blocks per read, high response time, and a large
percentage of the total IO. Improving the response time of these
tables with
faster storage will help reduce this wait event and speed up the
database. Full table scan of a data tables can cause these events. |
Moving the data files with largest
amount of time spent waiting on multi-block reads to faster storage
can significantly reduce the amount of time spent waiting on this
event. By reducing the time spent waiting on this event, the
database performance could increase 4%. |
| SQL*Net more data from client |
3.4% |
| Other |
|
| |
Tablespace I/O Stats
|
| Tablespace |
Read/s |
Av Rd(ms) |
Blks/Rd |
Writes/s |
Read% |
% Total IO |
| USERS |
143 |
2.7 |
1.4 |
2 |
99% |
98.37% |
| SYSAUX |
1 |
4 |
1.2 |
0 |
75% |
0.69% |
| SYSTEM |
1 |
4.1 |
1.2 |
0 |
97% |
0.68% |
| |
Tablespace I/O Stats
|
| Tablespace |
Wait (s) |
Read/s |
Av Rd(ms) |
Blks/Rd |
Writes/s |
Read% |
% Total IO |
| USERS |
1,428 |
143 |
2.7 |
1.4 |
2 |
99% |
98.37% |
| SYSAUX |
15 |
1 |
4 |
1.2 |
0 |
75% |
0.69% |
| SYSTEM |
15 |
1 |
4.1 |
1.2 |
0 |
97% |
0.68% |
| |
Load Profile
|
| Logical reads: |
10,789/s |
|
Parses: |
151.25/s |
|
| Physical reads: |
202/s |
|
Hard parses: |
38.62/s |
|
| Physical writes: |
6/s |
|
Transactions: |
6.17/s |
|
| Rollback per transaction: |
26.51% |
|
Buffer Nowait: |
99.99% |
|
| 3 Recommendations: |
| You have more than 140,400 unique SQL statements entering
your shared pool, with the resulting overhead of continuous RAM
allocation and freeing within the shared pool. A
hard parse
is expensive because each incoming SQL statement must be re-loaded
into the shared pool; with the associated overhead involved in
shared pool RAM allocation and memory management. Once loaded, the
SQL must then be completely re-checked for syntax & semantics and an
executable generated.
Excessive hard parsing can occur when your shared_pool_size is
too small (and reentrant SQL is paged out) or when you have
non-reusable SQL statements without host variables. See the
cursor_sharing parameter for an easy way to make SQL reentrant and
remember that you should always use host variables in you SQL so
that they can be reentrant. |
| Set the cursor_sharing parameter for an easy way to make
SQL reentrant and remember that you should always use host variables
in you SQL so that they can be reentrant. |
You may have an application issue causing excessive rollbacks
with 26.51% rollbacks per transaction. Due to Oracle's assumption of
a commit, the rollback process is very expensive and should only be
used when necessary. You can identify the specific SQL and user
session that is executing the rollbacks by querying the
v$sesstat view.
Remember that some applications may automatically perform rollback
operations (commit-then-rollback or rollback-then-exit) after each
commit. If this is the case, speak with your application developers
to find out if there is a way to disable this. While these "empty
rollbacks" do not incur performance expense, it will case this
metric to appear very high. |
| |
Instance Efficiency
|
| Buffer Hit: |
98.13% |
|
In-memory Sort: |
100% |
|
| Library Hit: |
81.11% |
|
Latch Hit: |
99.53% |
|
| Memory Usage: |
83.6% |
|
Memory for SQL: |
39.6% |
|
| 1 Recommendations: |
| Your Library Hit ratio is 81.11%. A
low library
cache hit percentage could mean SQL is prematurely aging out of
the shared pool as the shared pool may be small or that
unsharable SQL is being used. In addition, compare it with the
soft parse ratio. If they are both low, then investigate whether
there is a parsing issue. Since you never know in-advance how many
SQL statements need to be cached, the Oracle DBA must set
shared_pool_size large enough to prevent excessive re-parsing of
SQL. |
| |
Wait Events
|
| Event |
Waits |
Wait Time (s) |
Avg Wait (ms) |
Waits/txn |
| db file sequential read |
464,784 |
1,300 |
3 |
20.7 |
| SQL*Net more data to client |
124,791 |
216 |
2 |
5.6 |
| db file scattered read |
62,694 |
137 |
2 |
2.8 |
| SQL*Net more data from clien |
19,153 |
128 |
7 |
0.9 |
| SQL*Net message to client |
2,544,038 |
8 |
0 |
113.4 |
| read by other session |
4,475 |
7 |
2 |
0.2 |
| latch: shared pool |
15,963 |
5 |
0 |
0.7 |
| log file sync |
15,952 |
3 |
0 |
0.7 |
| log file parallel write |
17,540 |
1 |
0 |
0.8 |
| db file parallel write |
4,904 |
1 |
0 |
0.2 |
Instance Activity Stats
|
| Statistic |
Total |
per Second |
per Trans |
| consistent gets |
39,050,982 |
10,741.5 |
1,741.4 |
| consistent gets - examination |
1,067,047 |
293.5 |
47.6 |
| db block changes |
182,103 |
50.1 |
8.1 |
| execute count |
804,533 |
221.3 |
35.9 |
| parse count (hard) |
140,400 |
38.6 |
6.3 |
| parse count (total) |
549,854 |
151.3 |
24.5 |
| physical reads |
735,666 |
202.4 |
32.8 |
| physical reads direct |
242 |
0.1 |
0.0 |
| physical writes |
23,575 |
6.5 |
1.1 |
| physical writes direct |
12,120 |
3.3 |
0.5 |
| redo writes |
17,533 |
4.8 |
0.8 |
| session cursor cache hits |
197,184 |
54.2 |
8.8 |
| sorts (disk) |
3 |
0.0 |
0.0 |
| sorts (memory) |
161,859 |
44.5 |
7.2 |
| table fetch continued row |
36,409 |
10.0 |
1.6 |
| table scans (long tables) |
874 |
0.2 |
0.0 |
| table scans (short tables) |
70,826 |
19.5 |
3.2 |
| workarea executions - onepass |
3 |
0.0 |
0.0 |
| 4 Recommendations: |
| You have high update activity with 50.1 db block changes
per second. The DB
block changes are a rough indication of total database work.
This statistic indicates (on a per-transaction level) the rate at
which buffers are being dirtied and you may want to optimize your
database
writer (DBWR) process. You can determine which sessions and SQL
statements have the
highest db block changes by querying the v$session and
v$sessatst views. |
| You have 3 disk sorts during this period. Disk sorts are
very expensive and
increasing your PGA (sort_area_size or pga_aggregate_target) may
allow you to perform these sorts in RAM. |
| You have 36,409 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 19.5 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. |
| |
Latch Activity
|
| Latch |
Get Requests |
% Get Miss |
% NoWait Miss |
Wait Time (s) |
| shared pool |
17,301,311 |
0.9 |
N/A |
5 |
Buffer Pool Advisory
|
| Current: |
11,843,618 disk reads |
|
| Optimized: |
6,706,805 disk reads |
|
| Improvement: |
43.37% fewer |
|
| The Oracle buffer cache advisory utility indicates 11,843,618
disk reads during the sample interval. Oracle estimates that
doubling the data buffer size (by increasing db_cache_size) will
reduce disk reads to 6,706,805, a 43.37% decrease. |
| |
Init.ora Parameters
|
|
| Parameter |
Value |
|
| db_block_size |
16KB |
|
| db_file_multiblock_read_count |
16 |
|
| pga_aggregate_target |
200MB |
|
| session_cached_cursors |
128 |
|
| shared_servers |
50 |
|
| _optimizer_cost_model |
cpu |
|
| cursor_sharing |
exact |
|
| 3 Recommendations: |
| 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. |
| You are using the Multi-threaded Server (Shared Server).
Oracle states that connections with the MTS are slower than a
dedicated server, and you may consider switching to dedicated
database connections. |
| 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
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
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.
