Would you increase the shared pool? --URGENT

From: Pablo ksksksk <p_rodri99_at_yahoo.es>
Date: Thu, 17 May 2001 09:02:42 -0700
Message-ID: <F001.00306471.20010517080116@fatcity.com>

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: 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).

Received on Thu May 17 2001 - 11:02:42 CDT