> I'm going to place Occum's Razor above
> my screen in BIG LETTERS.
Just be careful you don't cut your throat with it.
;)
Jared
"Baker, Barbara" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
02/15/02 09:38 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: ERR: ORA-04031 unable to allocate
Dennis:
I DO have my reputation to consider. Cantankerous is probably the nicest
thing anyone's ever said about me.
I actually did find the problem. One of the tables was recently re-loaded
(via some Access piece-of-junk BEHIND the dba's back!), and the normaly
process that analyzes tables missed this one. So the table had no
statistics.
I've learned some valuable lessons. I'm going to place Occum's Razor
above
my screen in BIG LETTERS.
Thanks everyone for your replies.
Barb
(still cantankerous as ever)
> ----------
> From: DENNIS WILLIAMS[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, February 15, 2002 9:33 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ERR: ORA-04031 unable to allocate
>
> Barbara - Been there done that. I have stubbornly made the same
> statements.
> This is why some co-workers say DBAs are cantankerous. My best guess is
> that
> there is something about that query. Give up, increase SHARED_POOL_SIZE.
> Then your coworkers will start saying how easy you are to get along
with.
> Dennis Williams
> DBA (now the friendly DBA)
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -----Original Message-----
> Sent: Friday, February 15, 2002 9:14 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Jared:
> I did look at this article.
> I'd buy that I have a shared pool problem if I were seeing other
problems
> in
> the database.
> However, I'm still confused that I see the problem only with this 1
query,
> that it's so easily reproducible only for this query, and the the other
> 100-or-so users in the database have not experienced problems.
>
> The database was bounced last night. I did increase open_cursors, just
> for
> grins. I'll take a look this morning and see how things look.
> Thanks!
> Barb
>
>
> > ----------
> > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]]
> > Reply To: [EMAIL PROTECTED]
> > Sent: Thursday, February 14, 2002 6:08 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: ERR: ORA-04031 unable to allocate
> >
> > see
> >
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_dat
> > abase_id=NOT&p_id=146599.1
> >
> > Jared
> >
> >
> >
> >
> >
> >
> >
> > "Baker, Barbara" <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 02/14/02 04:03 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc:
> > Subject: ERR: ORA-04031 unable to allocate
> >
> >
> > Oracle 8.0.5
> > Solaris 2.6
> >
> > List:
> > One of our users is getting this error message running a query:
> >
> > ERROR:
> > ORA-04031: unable to allocate 4194304 bytes of shared memory ("shared
> > pool","unknown object","cursor work he","KKRH Hash Table")
> >
> > The query is very simple (see below). Happens consistently. Error
> occurs
> > after about 1 minute of execution. No other problems in the database
> (no
> > errors in alert, etc.)
> >
> > I'm seeing latch wait this for the sid involved:
> >
> >
> >
> >
> >
> > SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT
> > P3
> > ----- ---------------- ---------- ----------- -------- --------
--------
> > ------
> > 409 latch free address 2147519876 number 59 tries
> > 0
> > 1 pmon timer duration 300 0
> > 0
> > 6 smon timer sleep time 300 failed 0
> > 0
> >
> >
> >
> > and then I see this for the same sid in v$lock (resource is
> type+id1+id2,
> > query is from Steve Adams' site)
> >
> >
> >
> >
> > RESOURCE NSID SID HOLDING WANTING SECONDS
> > -------------------- ----- ---- ------- ------- ----------
> > CU--1595636348-0 409 409 X 0
> > RT-1-0 4 LGWR X 0
> > TS-1-8388610 6 SMON SX 55604
> >
> >
> >
> > According to Anjo Kolk's description, a CU is a Bind enqueue. Does
this
> > mean I'm running out of open_cursors?? (And if so, why are no other
> > processes affected?)
> >
> > Anyone seen this one before?
> >
> > Thanks for any help!
> > Barb
> >
> >
> > Here's the query:
> >
> > select distinct
> > v.sales_id,
> > a.receiver,
> > a.adno,
> > a.unet,
> > a.vno,
> > a.enddate
> > from advdb.ad a,
> > advdb.pub p,
> > advdb.voluntary_reps v
> > where a.adno = p.adno
> > and a.vno = p.vno
> > and a.receiver = v.name
> > and p.state = 'VAR'
> > and p.vnoflag = 'Y'
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Baker, Barbara
> > 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:
> > 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: Baker, Barbara
> 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: DENNIS WILLIAMS
> 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: Baker, Barbara
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:
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).
