RE: multiple extents are OK, dagnabbit!

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Thu, 24 Jan 2002 10:32:18 -0800
Message-ID: <F001.003FA317.20020124102026@fatcity.com>

I hit 1397603 (think that was the one) and with it you completely lose service to the database. By the way, my understanding is that purging the shared pool when you hit 4031 errors is not always going to solve the problem because if there was SQL available to age out Oracle would do it. I run this query which will return the "age" of the SQL in the shared pool. If you see 30-50% of the statements getting aged out within 10-30 minutes and you have a high parse rate it might just be your shared pool is too small to handle the load. If you were stating that your shared pool was going to be increased to 10.5 MB, that is very small. By the time your data dictionary et al. gets loaded there is not much room for SQL. Check V$SGASTAT for space used by SQL. You can modify the SQL to round to a more precise time period if you like.

• Ethan

select sql_statements, hours_in_pool,
round(sql_statements/total_statements*100,0) percent_of_total from (
select

   count(*) sql_statements,
   round((sysdate-(to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss')))* 1440 / 60,0) hours_in_pool,

   total_statements
from

   v$sqlarea a,
   (select count(*) total_statements from v$sqlarea) b group by
round((sysdate-(to_date(first_load_time, 'yyyy-mm-dd/hh24:mi:ss')))* 1440 / 60,0),
total_statements)
where
round(sql_statements/total_statements*100,0) > 0;

-----Original Message-----
Sent: Thursday, January 24, 2002 7:45 AM To: Multiple recipients of list ORACLE-L

Kurt,

If you're on 8.1.6.3, 8.1.7.0.0 or 8.1.7.1.0 this sounds suspiciouslly like either bug 1640583 or bug 1397603, both of which are fixed in 8.1.7.2+

The workaround for bug 1397603 is to set _db_handles_cached = 0 in the init.ora.

HTH,

• Anita
  • "Wiegand, Kurt" <Kurt.Wiegand_at_CWUSA.COM> wrote:
    > Sorry Jeremiah, I don't have a clue.......
    >
    > got the same error (after the same 2 hours) after
    > purging the
    > shared pool; there was no activity at all on the
    > database, so
    > I thought about increasing the size of the shared
    > pool (~10.5MB)
    > but had a need, and the option, of simply replacing
    > the database
    > with a backup.
    >
    > SQL> alter tablespace USR2 coalesce
    > *
    > ERROR at line 1:
    > ORA-04031: unable to allocate 4180 bytes of shared
    > memory ("shared
    > pool","unknown object","sga heap","state objects")
    >
    >
    >
    > -----Original Message-----
    > Sent: Wednesday, January 23, 2002 11:56 AM
    > To: Multiple recipients of list ORACLE-L
    >
    >
    > On Tue, 22 Jan 2002, Wiegand, Kurt wrote:
    >
    > > sort of on the subject.....I once had a table with
    > ~88000 extents
    > > (most 1 block!) it took 8 hours to delete and a
    > subsequent coalesce
    > > ran for 2 hours before failing as it ran out of
    > shared
    > > memory....(8.1.5).....
    >
    > Kurt,
    >
    > What component of the SGA becomes exhausted by a
    > long-running
    > coalesce?
    >
    > --
    > Jeremiah Wilton
    > http://www.speakeasy.net/~jwilton
    >
    > > -----Original Message-----
    > > Sent: Tuesday, January 22, 2002 11:55 AM
    > > To: Multiple recipients of list ORACLE-L
    > >
    > > The problem arose in the catalog upgrade script.
    > It would never
    > > return. My diary says we let one attempt run for
    > 36 hours. The process
    > > showed CPU usage and I/O but nothing happened.
    > Some of the Oracle guys
    > > figured the problem was with the $fet (or whatever
    > tables hold the
    > > extent
    > > info, I never bother with the internals of the
    > data dictionary) having
    > > problems while being restructured. Once the tables
    > were changed from 40K
    > > to 500M
    > > extents the upgrade took less than 2 hours.
    > >
    > > One of the suggestions I did not use was to edit
    > sql.bsq to provide much
    > > larger
    > > extents for the table holding the extent info.
    > Even though I do this for
    > > the SOURCE$ table I am a big fan of the KISS
    > principle and rebuilding
    > > the tables
    > > needed to be done anyways.
    >
    > --
    > Please see the official ORACLE-L FAQ:
    > http://www.orafaq.com
    > --
    > Author: Jeremiah Wilton
    > INET: jwilton_at_speakeasy.net
    >
    > 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: Wiegand, Kurt
    > INET: Kurt.Wiegand_at_CWUSA.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).
    

    ---

Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

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 Jan 24 2002 - 12:32:18 CST