Hi Pablo,
Your shared pool is too big, not too small, and you are parsing too frequently
(probably some literal SQL). Your use of synonyms is an exacerbating factor.
Your reloads are due to the invalidations. They do not indicate that your shared
pool is too small.
Of course, you should worry about the major issues first.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Friday, 18 May 2001 2:01
To: Multiple recipients of list ORACLE-L
Hello Gurus,
I need help in this performance issue.
Oracle 7.3.4
HPUX 10.20
I 've got this ratios:
BC hit ratio 98
dictionary cache hit rate 100
library cache get hit ratio 91
library cache pin hit ratio 98
Mayor waits are enqueue (57%), buffer busy waits(34%),
especially p3=0 for some big tables (buffer being read
from disk), and latch free (6%).
I'm dealing with enqueue and bbw, but what would you
do with the low LC ratio and the shared pool in this
case.
Latch free waits details
LATCH TYPE IMPACT SLEEP RATE LATCH
---------
shared pool 123285 1.05% 27097
library cache 58841 0.06% 103
cache buffers chains 18108 0.00% 0
This is the library cache stat:
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDAT
----------------------------------------------------
SQL AREA .73288057 .97035171 47011 13891
TABLE/PROCEDURE .99150783 .99813894 11758 0
BODY .9975282 .9971979 32 0
TRIGGER .99816161 .99591602 61 0
INDEX .03401361 .02267574 5 0
CLUSTER .44444444 .33333333 0 0
OBJECT 1 1 0 0
PIPE .99999414 .99999415 0 0
The keep object summary
TYPE OBJECTS KEPT RELOADS OWNERS
-------------- --------- --------- --------- ---------
TABLE 389 0 1661 12
SYNONYM 348 0 2574 4
PACKAGE 294 293 131 4
PACKAGE BODY 292 291 23 4
TRIGGER 220 220 52 9
VIEW 170 0 373 3
SEQUENCE 108 108 131 11
NON-EXISTENT 68 2 153 8
PROCEDURE 60 60 47 2
FUNCTION 16 16 4 1
PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 100 81%
open_cursors 500 85%
Shared Pool
RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031
CHUNKS CHUNKS CHUNKS RELEASES ERRORS
--------- --------- --------- --------- ---------
3051 5374 655389 5351916 0
Shared pool free lists
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE
BIGGEST
--------- ---------- ----------- ------------
---------
0 79552 1707 46
72
1 45760 516 88
96
8 9480 1 9480
9480
9 41165488 1797 22907
32776
10 48335568 1093 44222
65424
11 20518744 242 84788
130072
12 4383200 26 168584
246496
13 807072 2 403536
447040
TIA
_______________________________________________________________
Do You Yahoo!?
Yahoo! Messenger: Comunicaci�n instant�nea gratis con tu gente -
http://messenger.yahoo.es
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
