ora-04031 2004-07-14 - By Spears, Brian
Ok seems attachements don 't make it..
Put this in a .sql file and fire it up. Haven 't checked if it need 9i
upgrading but I think it should be
Be of value.
rem pins.sql - Lists all packages and in-memory objects.
Brian Spears ... See below
spool c:\dba\lst\pins.txt
column executions format 999,999,999;
column Mem_used format 999,999,999;
col name format a30 wrap
col KEPT_YN format a12 wrap;
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PROMPT Column Numbers to Sort on: Most common is 4 - Executions
PROMPT
PROMPT . 2 3 4 5 6
PROMPT . TYPE Owner Executions Mem_used Kept? =20
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
select substr(owner,1,10) Owner
,substr(type,1,12) Type
,substr(name,1,60) Name
,executions
,sharable_mem Mem_used
,substr( ' '||kept|| ' ',1,12) KEPT_YN
from V$DB_OBJECT_CACHE
where type in ( 'TRIGGER '
, 'PROCEDURE '
, 'PACKAGE BODY '
, 'PACKAGE '
, 'SEQUENCE ')
order by &sort_by_column
/
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
PROMPT Useful sorts: Executions <--- Best in memory
PROMPT Mem_used <--- Large should be
PROMPT Kept? <--- Missing any ?
PROMPT =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
spool off
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Spears, Brian
Sent: Wednesday, July 14, 2004 12:01 PM
To: oracle-l@(protected)
Subject: RE: ORA-04031 (See ORA-04031.ora-code.com)=20
Here is my handy dandy script to help you quickly see which packages you
could pin... Cursor_sharing=3Dsimilar...often vastly reduces the amount =
of
memory used as well.
Brian Spears
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Powell, Mark D
Sent: Wednesday, July 14, 2004 11:18 AM
To: 'oracle-l@(protected) '
Subject: RE: ORA-04031 (See ORA-04031.ora-code.com)=20
> > It is currently set at 4400 - how much lower would I need to go????
> > < <
|
|
