Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Would you increase the shared pool? --URGENT

RE: Would you increase the shared pool? --URGENT

From: Christopher Spence <cspence_at_FuelSpot.com>
Date: Tue, 29 May 2001 14:39:39 -0700
Message-ID: <F001.00312C10.20010529144032@fatcity.com>

  1. I would check to see if the buffer busy waits are on the same file/block. Perhaps the same object is being hit constantly. I would also base my action on the type of block being waited on.
  2. Tuning the redo generation would be a good thing, specially one of that size. I would recommend two raid 0 arrays of 3-6 disks each with quick io / raw devices. 3gb is fine, I have never had to work with a monstrocity of that, but I have heard 2gb+ redo logs a few times. That is a constant 800Kb/s second sustained.
  3. What is the block size of this database?
  4. I think possibly problem is not further sizing the sp but avoiding fragmentation by pinning large procedures and packages at startup.
  5. Do the statements use literals or bind variables. Are objects being reloaded because of different syntax or because of invalidations and aging?

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Tuesday, May 29, 2001 5:24 PM
To: Multiple recipients of list ORACLE-L

Hi Steve and List,

I'm still having some problem to understand this data.

I've taken Steve Adams advise to reduce Shared Pool. I reduced it from 200MB to 180MB. And off course, this reduction has impacted in SP latch hit ratio. It sleeps rate has improved from 0.88% to 0.55%.

But I still have some opposed data.

Here I past my wait events:

EVENT                                    TIME_WAITED
----------------------------------------------------
enqueue                                      1780156
buffer busy waits                             511713
log buffer space                              103542
latch free                                     71181
write complete waits                           35970
free buffer waits                               5246
library cache pin                               4797
log file switch completion                      1678
library cache load lock                          392
row cache lock                                    35


Shared Pool Info

RECURR TRANSIENT FLUSHED PINS ORA-4031 LAST ERROR S.



  6531 11538 260744 3555435 0 0

  BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST


    0     100528        2098           47        72
    1      70904         759           93       136
    2     223576        1252          178       224
    3        384           1          384       384
    4       6456          10          645       808
    5     218000         151         1443      2048
    6     185536          64         2899      3968


As far as I can see, transient list is not bigger than tree times recurrent list (so SP is not too big) and flushed chunks/pins and releases is more than 1/20 (0.07). This posible indicates that the shared Pool is too small.

Besides, "library cache load lock" event indicates that a lot of SQLs (or other objects) are being loaded to the LC, this may also indicate that the SP is small.

But on the other hand, We've got that list 0 (of the shared pool) is probably too big and that would indicate that the shared pool is too big.

what am I missing here?

I know I've got more serious trouble with enqueue and bbw events, I'm trying to resolve them. That's no problem.
I'm also aware that "log file switch completion" event is causing a lot of trouble here and I'm trying to get some disks to place redo logs and make them bigger. By the way, I'm going to create redo logs of 3GB each in order to get a switch every hour. Is 3GB some reasonable size for redo?

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

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 Tue May 29 2001 - 16:39:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US