Library Cache question
- From: Martin Brown <martinfbrown@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Feb 2009 10:12:58 -0500
GM,
I have question for the performance people. I am experimenting with
dbms_shared_pool.keep and packages, sequences, triggers & functions (no cursors
-YET). Looking at the AWRs over a 24 hour period, I see very high miss rates
for SQL AREA namespace. Looking at v$db_object_cache, I see a high number of
cursors that are not kept.
Is it possible that the % miss, reloads & invalidations are because of the
cursors? Unfortunately, our developers ar not fans of bind variables either.
This is a 2-node 10.2.0.3 RAC cluster used exclusively for training new
employees and X-training current employees, so it's not really a lot of
traffic.
Statistics from AWR:
Node 1:
Library Cache Activity
"Pct Misses" should be very low
Namespace
Get Requests
Pct Miss
Pin Requests
Pct Miss
Reloads
Invali- dations
BODY
6,503
0.20
1,212,587
0.00
0
0
CLUSTER
76
0.00
151
0.00
0
0
INDEX
161
13.04
518
8.11
21
0
SQL AREA
1,236
95.15
7,780,975
0.08
779
277
TABLE/PROCEDURE
13,341
3.17
2,116,001
0.12
1,127
0
TRIGGER
790
3.92
1,822
3.90
2
0
Node 2:
Library Cache Activity
"Pct Misses" should be very low
Namespace
Get Requests
Pct Miss
Pin Requests
Pct Miss
Reloads
Invali- dations
BODY
18,183
0.03
1,701,992
0.00
3
0
CLUSTER
163
0.00
363
0.00
0
0
INDEX
274
48.18
1,449
17.53
29
0
SQL AREA
1,175
95.40
14,504,556
0.09
3,521
1,102
TABLE/PROCEDURE
18,196
2.97
2,687,271
0.16
1,651
0
TRIGGER
1,506
1.86
4,298
0.91
11
0
_________________________________________________________________
Windows Live™: Keep your life in sync.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_022009
Other related posts:
