| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: library cache pin wait
real quick thought from mostly a lurker ....
Your soft parsing % probably indicates what you suspected in string literals. The % of your physical reads is way to high versus logical reads. Check you SQL area and see if their are any obvious "dogs" in there. Your second wait latch free could be indicative of string literal problems.
Your memory usage should be lower than what it is on the shared pool side say down around 75% is ideal, but this may be indicative of the problem above as you have to load up all those diffent SQL statements into the pool. If that percentage gets to high then you may be aging out "good SQL" requiring you to hard parse SQL all over again if it is needed.
Gotta clean up those applications. If Cursor_sharing=Force works well in 9+, it will just make folks more lazy as you will bail them out. Gotta get tough like a parent and force them to rewrite their software. You will win no friends, but Oh well.
Also, ask the applications developers or users if they have noticed any kind of a slowdown during this 60 minute session you are looking at. Consider setting a 10046 trace on a users session and you should see the wait in there and the competing problem. Also, see www.hotsos.com for some information on 10046 data.
Also, use Gaja and Kirti's most excellent book Oracle Performance tuning 101 to drill down on those library cache pin and latch free waits.
Also, Tom Kyte's most excellent book has a pretty good explanation in Chapter 10 I believe on using Statspack and what to look for in the report.
Focus on the Soft Parsing % first by tracking down bad SQL, then revisit this report again after some time and see if that helped. Talk with Millsap, Kyte, Gaja, Anjo and Kirti on this stuff.
They are all studs.
Good Luck with it.
Mike
-----Original Message-----
Sent: Wednesday, June 04, 2003 12:40 AM
To: Multiple recipients of list ORACLE-L
For a Hybrid Banking Application , following waits are Observed :-
Oracle ver 9.2 (Dedicated Server connections)
Solaris 8
M/c SF15K
Application mostly uses Static Queries (i.e. with Bind variables) ,
though there may be some amount of Literal SQLs too
DB Size = 250 GB
Qs How may the following waits be approached ?
Qs Since we have just moved to 9.2 , any advisable standard init.ora
parameters with 9.2 ?
Qs Should CURSOR_SHARING = FORCE or SIMILAR be considered ? Which is better
& what may be the Overhead of these on production ?
Shall answer any Clarifications .
Thanks
--
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- -------
------------
TBASUN 1820705732 tbasun 1 9.2.0.3.0 NO IB15KDB
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- ---------
-------------------
Begin Snap: 71 03-Jun-03 12:00:05 2,953 53.2
End Snap: 91 03-Jun-03 13:00:05 3,030 55.2
Elapsed: 60.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 703M Std Block Size: 8K
Shared Pool Size: 400M Log Buffer: 6,144K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 251,510.31 1,682.23
Logical reads: 35,458.62 237.17
Block changes: 1,226.80 8.21
Physical reads: 2,391.08 15.99
Physical writes: 155.51 1.04
User calls: 8,556.91 57.23
Parses: 495.46 3.31
Hard parses: 71.17 0.48
Sorts: 201.79 1.35
Logons: 8.80 0.06
Executes: 5,949.80 39.80
Transactions: 149.51
% Blocks changed per Read: 3.46 Recursive Call %: 43.86
Rollback per transaction %: 55.51 Rows per Sort: 45.29
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 93.35 In-memory Sort %: 99.94
Library Hit %: 98.14 Soft Parse %: 85.64
Execute to Parse %: 91.67 Latch Hit %: 98.81
Parse CPU to Parse Elapsd %: 3.56 % Non-Parse CPU: 90.92
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.23 86.67
% SQL with executions>1: 54.37 75.05
% Memory for SQL w/exec>1: 47.47 70.06
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
library cache pin 97,608 95,845
40.95
latch free 2,788,119 70,018
29.91
CPU time 25,145
10.74
library cache load lock 19,686 19,419
8.30
db file sequential read 6,334,694 6,715
2.87
-------------------------------------------------------------
-------------------------------------------------------------
Latch Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91
->"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
------------------------ -------------- ------ ------ ------ ------------
------
library cache 69,587,442 3.3 0.7 53315 663,932
50.5
row cache objects 29,587,406 2.8 0.1 2549 128,096
18.3
Library Cache Activity for DB: TBASUN Instance: tbasun Snaps: 71 -91
->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
--------------- ------------ ------ -------------- ------ ----------
--------
BODY 3,466 11.3 3,532 17.9 187
0
CLUSTER 12,921 0.1 14,953 0.1 0
0
INDEX 38,620 19.0 38,618 24.5 8
0
SQL AREA 1,627,354 6.3 26,006,472 0.8 152,116
8
TABLE/PROCEDURE 1,206,367 0.3 1,875,867 15.4 158,738
0
TRIGGER 1,405 0.2 1,713 44.4 542
0
-------------------------------------------------------------
shared_pool_reserved_size 104857600
shared_pool_size 419430400
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: VIVEK_SHARMA
INET: VIVEK_SHARMA_at_infosys.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johnson, Michael
INET: Michael.Johnson_at_oln-afmc.af.mil
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jun 04 2003 - 12:46:47 CDT
 |
 |