Re: How to identify processes that cause ORA-4030 errors

From: Deepak Thapliyal <deepakthapliyal_at_yahoo.com>
Date: Mon, 12 Nov 2001 12:39:27 -0800
Message-ID: <F001.003C2B48.20011112113024@fatcity.com>

Rajendra

The best advise for avoiding this would be to have thorough knowledge of your application SQL by performing detailed sql trace analysis and ensuring that statements are shared and use bind variables. The tkprof process is painfull but will help in long run .. its much better than a blind chase .. thats for sure! Also consider compartmentalizing ur shared pool by using the reserved pool feature, especially for large application packages and procedures. You also consider using ABORTED_REQUEST_THRESHOLD procedure from the dbms_shared_pool package to localize the effect of 4030 errors. (search ML more for details of this proc)

Deepak

PS: in case u are in the dark about what is causing 4030 errors and want to perform diagnostics .. consider setting the event 4030 at ur init level. This will basically generate a trace dump whenever you encounter this error.

• "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote:
  > How does one identify the processes that will
  > (likely) cause these errors? I
  > am running 9i, 16G RAM, 700M SGA. Due to a bunch of
  > these errors we had a
  > system crash last week. So I want to put in a
  > monitoring process that will
  > identify the process, sql it is executing at that
  > time.
  >
  > Other than just selecting from v$sesstat is there
  > anything else that I can
  > monitor? Will statspack help in this issue? I
  > haven't used it yet, but about
  > to install on my database.
  >
  > Thanks in advance
  > Raj
  >
  

  ---

  
  > Rajendra Jamadagni MIS, ESPN Inc.
  > Rajendra dot Jamadagni at ESPN dot com
  > Any opinion expressed here is personal and doesn't
  > reflect that of ESPN Inc.
  >
  > QOTD: Any clod can have facts, but having an opinion
  > is an art !
  >
  > >
  >
  *********************************************************************1
  >
  > This e-mail message is confidential, intended only
  > for the named recipient(s) above and may contain
  > information that is privileged, attorney work
  > product or exempt from disclosure under applicable
  > law. If you have received this message in error, or
  > are not the named recipient(s), please immediately
  > notify corporate MIS at (860) 766-2000 and delete
  > this e-mail message from your computer, Thank you.
  >
  >
  *********************************************************************1
  >
  >
  

  ---

Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

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

Received on Mon Nov 12 2001 - 14:39:27 CST