Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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
          -------------------------------------------------------------
 
 

Performance Summary

Physical Reads: 202/sec   MB per second: 3.16 MB/sec  
Physical Writes: 6/sec   MB per second: 0.09 MB/sec  
Single-block Reads: 127.85/sec   Avg wait: 2.8 ms  
Multi-block Reads: 17.25/sec   Avg wait: 2.19 ms  
Tablespace Reads: 145/sec   Writes: 2/sec  
 

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%
 

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%
 

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.
 

SQL Statistics

Click here to see all SQL data
 

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
0 Recommendations:
 

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
0 Recommendations:
 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.