| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> REPOST: Re: unable to allocate XXXX bytes of shared memory ("shared pool","TEXTINDEXMETHODS"
Mark,
Got a query on x$ksmlru in fail state as follows:
ADDR INDX INST_ID KSMLRCOM KSMLRSIZ KSMLRNUM KSMLRHON KSMLROHV KSMLRSES -------- ---------- ---------- -------------------- ---------- ---------- -------------------------------- ---------- -------- 50001A88 0 1 KQLS MEM BLOCK 2084 96 DR$INDEX 1644783660 57D9E550 50001ACC 1 1 HSHNEW:SYMTAB->HSH 2224 16 STANDARD 2679492314 57D9E550 50001B10 2 1 BAMIMA: Bam Buffer 2232 8 DBMS_UTILITY 4041615652 57D9E550 50001B54 3 1 BAMIMA: Bam Buffer 4036 48 TEXTOPTSTATS 931148392 57DA0F6C 50001B98 4 1 session param valu 4076 8 0 57D9FE94 50001BDC 5 1 BAMIMA: Bam Buffer 4128 48 DBMS_UTILITY 4041615653 57D9E550 50001C20 6 1 BAMIMA: Bam Buffer 4132 760 BEGIN :p := CTX_QUERY.PREFER... 935225534 57DA3988 50001C64 7 1 pl/sql source 4176 984 TEXTINDEXMETHODS 2154769808 57D9F628 50001CA8 8 1 library cache 4192 664 select /*+ index(idl_sb4$ i_... 181436173 57D9F628 50001CEC 9 1 library cache 4216 768 TYPE_MISC$ 3419236511 57D9C3A0
I don't suppose it will tell us what object names to pin?
Sadly client does not have support, so I can't get to metalink. I just have one client using oracle and don't pretend to be a dba.
I did find the following that may tell me how to pin stuff:
Tip: Pin PL/SQL objects into memory immediately upon starting the
database to
avoid insufficient memory errors later in the day. To accomplish
this, use the
DBMS_SHARED_POOL.KEEP procedure for PL/SQL object statements. Ensure
that the
STANDARD procedure is pinned soon after startup since it is so large.
(3)You may also pin all packages:
To pin all packages in the system, execute the following (from
Oracle's
Metalink):
declare
own varchar2(100);
nam varchar2(100);
cursor pkgs is
select owner, object_name
from dba_objects
where object_type = 'PACKAGE';
begin
open pkgs;
loop
fetch pkgs into own, nam;
exit when pkgs%notfound;
dbms_shared_pool.keep(own || '.' || nam, 'P');
end loop;
Common "problem packages" that are shipped with Oracle (and should be
'kept')
include 'STANDARD', 'DBMS_STANDARD', and 'DIUTIL'.
Tip: Use the DBMS_SHARED_POOL.KEEP procedure combined in PL/SQL to pin
all
packages when the database is started (if memory/shared pool permits)
and avoid
all errors involving loading packages in the future. See chapter 10
for
additional PL/SQL and pinning tips.
I'm guessing sql like:
execute dbms_shared_pool.keep ('owner.object_name');
would pin an object. Since CTXSYS seems to be the problem, should I
pin everything owned by CTXSYS?
Did a query on v$sgastat (not in failed state) and got:
select * from v$sgastat;
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 73888
db_block_buffers 131072000
log_buffer 66560
shared pool free memory 951892
shared pool miscellaneous 678460
shared pool message pool freequeue 116176
shared pool trigger inform 732
shared pool KGFF heap 15488
shared pool PLS non-lib hp 2096
shared pool type object de 17336
shared pool table definiti 320
shared pool db_block_hash_buckets 387096
shared pool sessions 247940
shared pool State objects 137024
shared pool VIRTUAL CIRCUITS 186872
shared pool PL/SQL DIANA 507108
shared pool db_files 72496
shared pool long op statistics array 50600
shared pool fixed allocation callback 640
shared pool table columns 17556
shared pool character set object 53808
shared pool pl/sql source 5076
shared pool dictionary cache 256828
shared pool KQLS heap 278996
shared pool joxs heap init 4248
shared pool KGK heap 4376
shared pool library cache 1482416
shared pool db_handles 50000
shared pool sql area 715584
shared pool PL/SQL MPCODE 73036
shared pool processes 79600
shared pool SYSTEM PARAMETERS 63956
shared pool db_block_buffers 2176000
shared pool event statistics per sess 399280
large pool free memory 6144000
java pool free memory 10584064
java pool memory in use 389120
I seem to remember that I set reserved pool size at 50% because I read somewhere that was the max I could set it to and was having problems with a long field in one of the tables I thought might be solved by this.
Looks like I need to find someone to do the upgrade to .2. Might fix several things, but I'd sure like to be able to figure out what the problem is before I go diving into an upgrade that doesn't fix my problem.
Thank you for the help thus far! If anything above points to a recommendation, please let me know.
Ken Biggs
>
> Ken, on instance start up are you pinning your large packages and the
> heavy hit Oracle provided packages? If not you should do this using
> dbms_shared_pool.keep procedure.
>
> If you have support go to metalink.oracle.com (support button off
> Oracle home site) and perform an advanced search on ORA-04031. Remove
> the forums from your search path to reduce the quanity of garbage
> returned and you will find some white papers on the subject.
>
> You did not answer my question on why you had the reserved pool sized
> at 50% of the shared pool. The default is only 10% but it is not
> unusual to need to make it 20% or maybe 30% but 50% seems unusual. I
> asked because you could be starving the small object section of the
> pool, but it is impossible for me to say without really looking at
> everything related to the matter and that is not possible.
>
> Once again there were over 20 fixes to space management problems
> between 8.1.7.0 and 8.1.7.2 so you should look at upgrading if you
> cannot isolate the problem. If this is a severe problem an iTar may
> be in order.
>
> -- Mark D Powell --
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 15:52:14 CST
 |
 |