| 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: Would you increase the shared pool? --URGENT
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: p_rodri99_at_yahoo.es 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: 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.com -- Author: Steve Adams INET: steve.adams_at_ixora.com.au 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: 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 Thu May 17 2001 - 23:01:44 CDT
 |
 |