unable to allocate XXXX bytes of shared memory ("shared pool","TEXTINDEXMETHODS"
Ken
Running oracle ver: 8.1.7.0.1 on Redhat Linux ver: 6.2
kernal version: 2.2.16-4.lfssmp on dual processor PIII 1 ghz
w/ 2 gig ram & 4 gig of swap
Doing lots of context searches on a table w/2.5 million records.
Get lots of these in the java log:
ORA-04031: unable to allocate 4124 bytes of shared memory ("shared
pool","TEXTINDEXMETHODS","pl/sql source","pl/sql source")
A few of these:
ORA-0431: unable to allocate 4096 bytes of shared memory ("shared
pool,"CTXQUERY","PL/SQL MPCODE","BAMIMA: Bam Buffer")
here's the stuff from init.ora
db_files = 1500
db_file_multiblock_read_count = 32
db_block_buffers = 3200
shared_pool_size = 200000000
shared_pool_reserved_size = 100000000
large_pool_size = 6144000
java_pool_size = 20971520
log_checkpoint_interval = 10000
processes = 200
parallel_max_servers = 8
log_buffer = 163840
max_dump_file_size = 10240
I can't figure out how to optimize memory setup for oracle - way to
complex for me. Thanks for any help!!
Ken
Mark D Powell
Ken, why do you have the reserved size set to half your pool? How
full is the reserved area? Query v$shared_pool_reserved. Do you have
sql causing flushing?
Here is some code to help gather information. Also there are at least
20 fixes to shared pool memory management between version 8.1.7.0 and
8.1.7.2 so upgrading may be wise. Once you run the query I would go
to metalink and perform an advanced search on 04031 skipping the forum
entries and look for any bug reports that may apply to your situation.
set echo off
--
-- SQL to show shared investigate shr pool memory prob
-- Based on Oracle support tar response
-- a) show stat for reserved area
-- b) show sql causing flushing
--
-- Note - You must be sys to run this code, uses x$ table.
--
-- 20010627 Mark D Powell Save code to show shr pool
--
col avg_free_size heading 'AVG|FREE|SIZE'
col avg_used_size heading 'AVG|USED|SIZE'
spool pool_flushed
SELECT
free_space
,avg_free_size
,used_space
,avg_used_size
,request_failures
,last_failure_size
FROM v$shared_pool_reserved;
-- code that causes code to be flushed from the shared pool
-- selecting against the ksmlru fixed table flushes its contents
SELECT
*
FROM X$KSMLRU
WHERE ksmlrsiz > 0;
spool off
If you find your reserved area has free space larger than the amount
being asked for when the error occurs then you may need to set the
threshold where an object is considered large, which is now an
underbar parameter in 8.1.7.2. See thread on
shared_pool_reserved_min_alloc for SQL to see default value.
HTH
-- Mark D Powell --
Ken
Thank you for the help!!
Using the query on v$shared_pool_reserved (in fail state), I get:
free_space: 244856
avg_free_space: 122428
free_count: 1
max_free_size: 244856
used_space: 5144
avg_used: 2572
used_count: 1
max_used_space: 5144
requests: 1
last_miss_s??: 0
max_miss_s??: 0
request_failures: 0
last_request_failure_size:111
aborted_re??: 4176
last_abort: 214748
Using the query on X$KSMLRU (this was run in non-failure state), I get
3 lines as follows:
addr: 50001C64
indx: 7
inst_id: 1
ksmlrcom: kgldahds
ksmlrsiz: 236
ksmlrnum: 8
ksmlrhon: select audit$, options from p...
ksmlrohv: 3348238861
ksmlrses: 57DA0F6C
and
addr: 50001CA8
indx: 8
inst_id: 1
ksmlrcom: BAMIMA: Bam Buffer
ksmlrsiz: 4132
ksmlrnum: 280
ksmlrhon: declare sel number; ...
ksmlrohv: 3437985264
ksmlrses: 57DAF6C
and
addr: 50001CEC
indx: 9
inst_id: 1
ksmlrcom: pl/sql source
ksmlrsiz: 4176
ksmlrnum: 960
ksmlrhon: TEXTINDEXMETHODS
ksmlrohv: 2154769808
ksmlrses: 57DA0F6C
Does this help at all?
I'll make sure and run the query on X$KSMLRU when it's in fail state
next time.
Thanks,
Ken
mark....@eds.com (Mark D Powell) wrote in message news:<178d2795.02012...@posting.google.com>...
Mark D Powell
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.
Ken
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;
end;
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
Mark D Powell
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 --
========= WAS CANCELLED BY =======:
From: mark....@eds.com (Mark D Powell)
Control: cancel <178d2795.02012...@posting.google.com>
Subject: cmsg cancel <178d2795.02012...@posting.google.com>
Date: Mon, 28 Jan 2002 00:53:53 GMT
Message-ID: <cancel.178d2795.0...@posting.google.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!news.fh-hannover.de!fu-berlin.de!area.cu.mi.it!news.mailgate.org!news2.euro.net!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40716344
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.
Ken
and
and
Thanks,
Ken
mark....@eds.com (Mark D Powell) wrote in message news:<178d2795.02012...@posting.google.com>...
========= WAS CANCELLED BY =======:
From: bi...@lone-star.net (Ken)
Control: cancel <150b6aa0.02012...@posting.google.com>
Subject: cmsg cancel <150b6aa0.02012...@posting.google.com>
Date: Mon, 28 Jan 2002 01:05:53 GMT
Message-ID: <cancel.150b6aa0.0...@posting.google.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!news.fh-hannover.de!comnets.rwth-aachen.de!news.rwth-aachen.de!news-koe1.dfn.de!news-fra1.dfn.de!newsfeed.hanau.net!fr.clara.net!heighliner.fr.clara.net!grolier!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40716502
Ken
-------------------------------------------------------------------------------
------------------------------------------------------------------------------
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 --
========= WAS CANCELLED BY =======:
From: bi...@lone-star.net (Ken)
Control: cancel <150b6aa0.02012...@posting.google.com>
Subject: cmsg cancel <150b6aa0.02012...@posting.google.com>
Date: Mon, 28 Jan 2002 00:14:54 GMT
Message-ID: <cancel.150b6aa0.0...@posting.google.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1
Path: news.uni-stuttgart.de!fu-berlin.de!cpk-news-hub1.bbnplanet.com!news.gtei.net!news.maxwell.syr.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40716218