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" <[EMAIL PROTECTED]> 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: [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: Wiegand, Kurt
> 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).
__________________________________________________
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: [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: Post, Ethan
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).
