I am developing scripts which monitor shared pool % free and space free to avoid possible ora-04031 errors. Since

"select sum(bytes) from v$sgastat where pool = 'shared pool'"

and

"show parameter shared_pool_size"

give me two different answers, which are about 50M apart, which value should I use to monitor shared pool consumption.

Interestingly enough, OEM's performance manager seems to use SUM(bytes) from v$sgastat to monitor shared pool % free and Quest's Spotlite on Oracle uses the value from v$parameter.

Also, is there a way to measure the fragmentation level in the shared pool?

Throughout the day, our 900 MB shared pool goes down to about 2% free. Is a low shared pool %free cause for alarm? Or is it just LRU doing its job

I understand that its fragmentation, not free % that causes ora-04031.

We inherited a client-server VB on Sybase system 18 months ago, where all SQL was hardcoded strings sitting in the VB code. Bind variable -- what were they? To accomodate a considerable upsizing in volumes and functionality, the system was ported to 3-tier & Oracle.

Initally, after a straight Sybase ==> Oracle conversion (VB apps left as-is), lots of 4031's, SGA > 500MB, latch contention, inconsistent query performance, needed to use MTS, etc..

I wanted all SQL moved into packages - despite lots of push back from the MS/VB crowd. You know - *can't* possibly have business rules in the DB - must be run from COM+. (Not that you can't use bind variables from COM+ components - but with packages at least you can guarantee it!)

This incremental process, which is nearing completion, has resulted in the DB running quite comfortably < 150MB SGA, even with 3 times the number of users, quadrupling of data volumes and transaction throughput.

Now, no 4031's - haven't seen 'em for over a year. I use all that delicious memory for more important things - like block buffers... :-)

Hi,
I scheduled a full export of the database at midnight. No body work in the database at this time. But i was surprised by a message ORA-04031 :
. . export de la table DEF$_LOB
EXP-00008: Erreur ORACLE 4031 rencontré
ORA-04031: impossible d'affecter 4200 octets de mémoire partagée ("shared pool","unknown object","sga heap","state objects")
. . export de la table DEF$_TEMP$LOB 0 lignes exporées <
.
.
. export des contraintes d'intégrité référentielle
. export des synonymes
EXP-00008: Erreur ORACLE 4031 rencontré
ORA-04031: impossible d'affecter 4096 octets de mémoire partagée ("shared pool","DBMS_JAVA","PL/SQL MPCODE","BAMIMA: Bam Buffer")
EXP-00000: Procédure d'export terminée avec erreur

I looked at statspack reports and the result was :
Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 20,594,688 20,561,920 -32,768
java pool memory in use 376,832 409,600 32,768
large pool free memory 614,400 614,400 0
shared pool db_block_buffers 376,312 376,312 0
shared pool db_block_hash_buckets 175,528 175,528 0
shared pool db_files 370,988 370,988 0
shared pool dictionary cache 373,312 374,252 940
shared pool distributed_transactions 180,152 180,152 0
shared pool event statistics per ses 584,800 584,800 0
shared pool fixed allocation callbac 320 320 0
shared pool free memory 11,648,920 11,345,108 -303,812
shared pool joxs heap init 4,248 4,248 0
shared pool KGFF heap 9,160 9,160 0
shared pool KGK heap 17,556 17,556 0
shared pool KQLS heap 1,007,624 830,264 -177,360
shared pool library cache 1,126,940 1,458,176 331,236
shared pool miscellaneous 34,365,128 34,742,656 377,528
shared pool PLS non-lib hp 2,096 2,096 0
shared pool PL/SQL DIANA 362,128 353,196 -8,932
shared pool PL/SQL MPCODE 272,944 89,860 -183,084
shared pool sessions 366,520 366,520 0
shared pool sql area 1,560,156 1,523,088 -37,068
shared pool State objects 247,360 247,360 0
shared pool table columns 18,952 19,704 752
shared pool table definiti 2,160 2,024 -136
shared pool transaction_branches 368,000 368,000 0
shared pool transactions 166,804 166,804 0
shared pool trigger inform 656 592 -64
shared pool VIRTUAL CIRCUITS 275,752 275,752 0
db_block_buffers 22,667,264 22,667,264 0
fixed_sga 75,804 75,804 0
log_buffer 66,560 66,560 0

We changed shared_pool_size from 50Mo to 90Mo and the problem is solved. Our SGBD works well before with 50Mo with the same load. Now the new report of statspack is :
Pool Name Begin value End value Difference
----------- ------------------------ -------------- -------------- -----------
java pool free memory 20,971,520 20,971,520 0
large pool free memory 614,400 614,400 0
shared pool db_block_buffers 376,312 376,312 0
shared pool db_block_hash_buckets 175,528 175,528 0
shared pool db_files 370,988 370,988 0
shared pool db_handles 75,000 75,000 0
shared pool dictionary cache 6,047,224 6,333,084 285,860
shared pool distributed_transactions 180,152 180,152 0
shared pool DML locks 89,760 89,760 0
shared pool enqueue_resources 129,024 129,024 0
shared pool event statistics per ses 584,800 584,800 0
shared pool fixed allocation callbac 320 320 0
shared pool free memory 64,227,888 4,906,524 -59,321,364
shared pool KGFF heap 9,160 9,160 0
shared pool KGK heap 17,556 17,556 0
shared pool KQLS heap 4,875,680 1,826,984 -3,048,696
shared pool ktlbk state objects 80,036 80,036 0
shared pool library cache 8,445,060 27,538,796 19,093,736
shared pool long op statistics array 74,800 74,800 0
shared pool message pool freequeue 124,552 124,552 0
shared pool miscellaneous 787,092 1,371,128 584,036
shared pool PLS non-lib hp 2,096 2,096 0
shared pool PL/SQL DIANA 796,544 630,904 -165,640
shared pool PL/SQL MPCODE 656,700 583,136 -73,564
shared pool processes 121,200 121,200 0
shared pool sessions 366,520 366,520 0
shared pool sql area 4,082,840 46,772,692 42,689,852
shared pool State objects 247,360 247,360 0
shared pool table columns 49,000 63,572 14,572
shared pool table definiti 5,828 4,780 -1,048
shared pool transaction_branches 368,000 368,000 0
shared pool transactions 166,804 166,804 0
shared pool trigger defini 228 228 0
shared pool trigger inform 760 700 -60
shared pool trigger source 188 188 0
shared pool VIRTUAL CIRCUITS 275,752 275,752 0
db_block_buffers 22,667,264 22,667,264 0
fixed_sga 75,804 75,804 0
log_buffer 66,560 66,560 0
-------------------------------------------------------------


In your opinion :
1) What is the reasons of this. (i can't keep shared_pool_size at 90Mo). Can you explain the reasons of the ORA-4031 error(is LARGE_POOL_SIZE very small?, ...) and how evoid it.
2) How can i know if the shared pool is fragmented
3) I don't understand how is possible that the message ORA-4031 can happen (no space in the shared pool). I thought that when no space is available, Oracle unload some things from the shared pool and use this new space, but in this case the process is longer.

thank you

You said:

Followup:
to avoid 4031's -- use bind variables. I've

a) never monitored it
b) never hit a 4031 in my life

because

c) I reject code in my database that is written wrong


Outside of dynamic SQL, is it possible to write PL/SQL code where bind variables are not used? Could you please tell what to look for to identify non-use of bind variables?

Is 8.1.7.0.0 more prone to ORA-4031 error as compoared to previous versions of the database?

I have a question about one of the followups posted here.
===========
> We inherited a client-server VB on Sybase system
> 18 months ago, where all SQL was hardcoded strings
> sitting in the VB code.
=========

I have a lot of VB/ASP apps in my shop and, as this poster says, all of them dynamically construct their SQL using various user-inputs, Web-page form inputs, etc. Basically, they just glue together the query and submit to Oracle. Obviously, this doesnt use bind variables, so this is, according to you, the cardinal sin.

I can see the ill-effects of this in my database, very large SGA, lots of almost identical SQLs in the sql area, etc.

How exactly can these ASP/VB developers use bind variables? I am not a ASP/VB programmer, so, if you can, please provide some detail.

Would this entail a lot of surgery to convert existing apps to use bind variables?

Thanks a lot.

We are in the same position: we have a small Web application (ASP pages - Oracle 8.1.6) and we "build" SQL strings in the VB code: after reading this (and others) post of yours we are now switching to bind variables.

Is this one of those rare cases when it is advisable to delete the contents of v$sqlarea?

Also, do you think we should "move SQL into packages", as another poster suggested, or using parameters & exec via VB code is giving us the same advantages?

Thank you for your time.

Alessandro

flushing the shared pool is something you do only when forced.


I am of the opinion that the best java/vb programs do not contain the words select/insert/update/delete in them anywhere -- only "begin procedure; end;" calls.

It gives the coders less opportunities to make grievous, hard to fix errors and reduces the number of statements they MUST use bind variables for (since we can "hide" many sql statements in a single procedure -- they only have to bind to one procedure call instead of N sql statements)


Remember it is not only for PERFORMANCE but security.  Say you have an ASP page that validates a username and password and right now you put the string together like this:


"select count(*)
   from user_pw_table
  where username = '" + username_from_screen + "'
    and password = '" + password_from_screen + "'"

looks like if I put in username=scott password=tiger it would generate:

ops$tkyte@ORA920> select count(*)
  2    from dba_users
  3   where username = 'SCOTT'
  4     and password = 'TIGER'
  5  /

  COUNT(*)
----------
         0

but what if I use a username of scott and a password of

TIGER' or rownum = '1

hmmm, 

ops$tkyte@ORA920> select count(*)
  2    from dba_users
  3   where username = 'SCOTT'
  4     and password = 'TIGER' or rownum = '1'
  5  /

  COUNT(*)
----------
         1


search google for

"sql injection" 

and read on...  Just think of what the user might be able to do by modifying your SQL on the fly like that -- wow.  Accidently type in some garbarge and that

delete from table where x = 5

becomes

delete from table where x = 5 or 1=1

and so on.... 

you said ..

"I am of the opinion that the best java/vb programs do not contain the words select/insert/update/delete in them anywhere -- only "begin procedure; end;" calls."

I didnt get you. - is it really possible ? ..do you mean to say that use those calls inside begin -end; ?

Thanks

I could not agree more with this.
We are doing exactly the same thing.
We have One (in many cases many) PL/SQL packages developed for each of our applications and the VB code just calls one of the function/procedure from these packages to perform the action requested by the client.
No hardcoded SQL of any nature within the ASP and/or VB code is our mantra.

Thanks Tom and "Anonymous from New Jersey" ;-)

--- Quote ---
We have One (in many cases many) PL/SQL packages developed for each of our applications and the VB code just calls one of the function/procedure from these packages to perform the action requested by the client.
--- End Quote ---

Interesting, but I'm not quite sure how should it be implemented: do you have a function for each table and for each DML (InsertTableA, UpdateTableB, SelectTableC, etc.) or is it possible to code, for example, a generic Store(TableName...) that takes care of all the possibilities?

Can you provide links with examples specific to Oracle w/VB-ASP?

Thanks again.

Alessandro

Tom,

I am getting  these errors, do you think this issue is related to parsing? Should i increase the shared pool or it is sized appropriately.

Thanks for your help;

Here are the errors/values.


ORA-04031: unable to allocate 25784 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","session param values")
ORA-02063: preceding line from DBLK_ISIS


SQL> select name,value from v$sysstat where name like '%parse%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                      2909898
parse time elapsed                                                 79905694
parse count (total)                                                12810787
parse count (hard)                                                   687699
parse count (failures)                                                 1075

SQL> select STARTUP_TIME from v$instance;

STARTUP_T
---------
05-DEC-03


SQL> sho sga

Total System Global Area 3635141040 bytes
Fixed Size                   735664 bytes
Variable Size             402653184 bytes
Database Buffers         3221225472 bytes
Redo Buffers               10526720 bytes
SQL>


SQL>  sho parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 12582912
shared_pool_size                     big integer 251658240


Here's the breakdown of shared pool

SQL> select name,
  2                 round(bytes/1024/1024,2) mbytes,
  3                 mbytes_tot,
  4                 round(round(bytes/1024/1024,2)/mbytes_tot*100) pct,
  5                 round(to_number(value)/1024/1024) shared_pool_size
  6        from v$sgastat,
  7                 (select round(sum(bytes)/1024/1024) mbytes_tot
  8                        from v$sgastat
  9                       where pool = 'shared pool'),
 10                (select value , rownum
 11                       from v$parameter
 12                      where name = 'shared_pool_size' )
 13      where pool = 'shared pool'
 14  /

NAME                           MBYTES MBYTES_TOT        PCT SHARED_POOL_SIZE
-------------------------- ---------- ---------- ---------- ----------------
errors                             .5        368          0              240
KGK heap                          .01        368          0              240
KQR L PO                         1.36        368          0              240
KQR L SO                          .03        368          0              240
KQR M PO                         1.65        368          0              240
KQR M SO                          .05        368          0              240
KQR S SO                          .02        368          0              240
KQR X PO                          .03        368          0              240
sql area                           90        368         24              240
KGLS heap                        1.95        368          1              240
PX subheap                        .03        368          0              240
parameters                        .05        368          0              240
free memory                     16.99        368          5              240
gcs shadows                     37.38        368         10              240
PL/SQL DIANA                     2.84        368          1              240
ges enqueues                     9.23        368          3              240
PL/SQL MPCODE                    2.09        368          1              240
gcs resources                   56.26        368         15              240
ges resources                    5.69        368          2              240
library cache                   59.38        368         16              240
miscellaneous                      64        368         17              240
KCL name table                    2.1        368          1              240
MTTR advisory                     .81        368          0              240
PLS non-lib hp                      0        368          0              240
joxs heap init                      0        368          0              240
kgl simulator                    7.11        368          2              240
sim memory hea                   2.91        368          1              240
table definiti                      0        368          0              240
temporary tabl                    .05        368          0              240
trigger defini                      0        368          0              240
trigger inform                      0        368          0              240
trigger source                      0        368          0              240
dictionary cache                 3.08        368          1              240
event statistics per sess         2.4        368          1              240
fixed allocation callback           0        368          0              240

35 rows selected.


 

Sorry forgot to mention I am Oracle 9204 sun box.

Hi Tom,

I run 8.1.7.0.0 on Solaris 2.8.

I am writting a paper on a database : they dont use BIND, have a huge shared pool, bla, bla. I *know* that they should use BIND and reduce the shared pool size but i'd like to *prove* it (like a guy around there always tell about to prove something :-)).
I know how to prove that BIND rocks, that shared_pool_reserved_size is useless but i need some clues to prove that they should reduce the shared pool...

SQL> show parameter shared_pool

NAME                                 TYPE    VALUE
------------------------------------ ------- -----------
shared_pool_reserved_size            string  50000000
shared_pool_size                    string  200000000

SQL> select * from v$sgastat where pool = 'shared pool' order by bytes ;

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool trigger defini                    424
            trigger source                    508
            trigger inform                    592
            fixed allocation callback         960
            table definiti                   1840
            PLS non-lib hp                   2096
            joxs heap init                   4248
            KGK heap                         4372
            PL/SQL PPCODE                    6060
            PX subheap                       6448
            KGFF heap                        7760
            table columns                   22388
            PX msg pool                     45540
            DML locks                      108000
            State objects                  108444
            message pool freequeue         124552
            sessions                       187572
            event statistics per sess      302064
            enqueue_resources              360000
            PL/SQL MPCODE                  427356
            PL/SQL DIANA                   984812
            ktprhtnew3                    1144312
            db_block_hash_buckets         1179880
            dictionary cache              1658076
            KQLS heap                     2053700
            db_block_buffers              8912896
            library cache                23252764
            sql area                     25855652
            miscellaneous                67721764
            free memory                  77274992
***********                            ----------
sum                                     211760072

1/ shared pool library cache = the executable form of SQL => right ?

2/ free memory = 77 MB (50MB of reserved_size + 27MB of shared pool) => right ?

3/ "shared pool miscellaneous is the sum of all things which initially were too small to have an individual line item in v$sgastat" => ok but what is "in" this categorie ? (i have 67 MB of theses small things in my shared pool... ?)

4/ what contains "shared pool sql area" ? => the SQL code ?

5/ What is "shared pool db_block_buffers" ?

Thx by advance,

Fred 

We are on 9.2.0.3 on a Sunfire 6800 with 8G and 8CPU, Solaris 8.

I have just taken over as DBA for a large DWH/DSS database. It has 6 production schemas being used for various reporting functions. The DBA who I replaced did some horrible things in terms of the init.ora, which I now have to correct. The production schemas in total have almost 900 Million rows. The DB is currently using about 500G but is growing by about 8G each day, eventually will be over a terabyte. Various reporting tools are used (e.g. SAS) in addition to horribly written SQL*PLUS.

The developers here run a lot of stored procedures daily, and they all use bind variables. Some of the ad hoc query SQL would make your hair stand on end.

Some statistics from V$sgastat

POOL NAME Free Bytes
----------- ---------------------------- ------------
java pool free memory 117,440,512
large pool 16,777,216
shared pool 1M buffer 2,098,176
shared pool Checkpoint queue 10,490,880
shared pool DML lock 757,904
shared pool FileIdentificatonBlock 1,791,824
shared pool FileOpenBlock 16,104,056
shared pool KGK heap 33,368
shared pool KGLS heap 7,130,696
shared pool KQR L PO 4,358,728
shared pool KQR M PO 19,746,552
shared pool KQR S SO 23,216
shared pool KQR X PO 5,152
shared pool KSXR pending messages que 853,952
shared pool KSXR receive buffers 1,034,000
shared pool MTTR advisory 13,624
shared pool PL/SQL DIANA 38,310,296
shared pool PL/SQL MPCODE 4,681,408
shared pool PL/SQL PPCODE 626,608
shared pool PLS non-lib hp 2,088
shared pool PX subheap 25,976
shared pool VIRTUAL CIRCUITS 1,802,000
shared pool constraints 220,896
shared pool db_files 289,104
shared pool db_handles 928,000
shared pool dictionary cache 3,229,952
shared pool enqueue 1,588,616
shared pool errors 59,320
shared pool event statistics per sess 9,890,760
shared pool fixed allocation callback 912
shared pool free memory 84,036,336
shared pool joxs heap init 4,240
shared pool kgl simulator 9,369,448
shared pool library cache 56,404,296
shared pool message pool freequeue 222,480
shared pool miscellaneous 13,300,856
shared pool parameters 195,880
shared pool partitioning d 647,032
shared pool pl/sql source 3,032
shared pool processes 1,030,400
shared pool sessions 2,393,040
shared pool sim memory hea 68,200
shared pool sql area 207,847,864
shared pool table definiti 19,840
shared pool transaction 1,590,776
shared pool trigger defini 8,328
shared pool trigger inform 5,568
shared pool trigger source 3,648
shared pool type object de 67,152
buffer_cache 33,554,432
fixed_sga 731,416
log_buffer 16,516,096
*********** **************************** ------------
sum 688,336,152

CPU usage From session statistics:

SID CPU PROGRAM
---- ------------ --------------------------------
7 4146206 sqlplusw.exe
8 0 sas.exe
9 30180 dbsnmp@dwh-d-prd1 (TNS V1-V3)
10 15 JDBC Thin Client
11 76408 sas.exe
13 5 brioqry.exe
14 5245 TOAD.exe
15 1 sas.exe
16 23048 oracle@dwh-d-prd1 (P001)
18 19319 sas.exe
20 24173 oracle@dwh-d-prd1 (P004)
21 7 TOAD.exe
23 68707 brioqry.exe
24 2 sas.exe
27 22594 oracle@dwh-d-prd1 (P000)
28 39444 sas.exe
29 38642 sas.exe
30 21718 oracle@dwh-d-prd1 (P003)
31 1 sqlplus@dwh-d-prd1 (TNS V1-V3)
36 9816 sas.exe
38 15016 sas.exe
39 1733 sqlplusw.exe
40 4786 jrew.exe
43 25 toad.exe
44 8648 sqlplusw.exe
45 23271 oracle@dwh-d-prd1 (P002)


The DB hit ratio is over 95% and the gethit and pinhit ratios are all 99 or 100%. There is no contention for server resources. Statistics are computed weekly using 30% estimate for all tables.

There are only a few people I would ask for a sanity check on the changes I will make later today, and you are definitely one of them. The changes are as follows:

Manual Undo > Auto Undo and really big undotbs
pga_aggregate target from 25m to 400m (or bigger?)
undo_retention from 900 to 12000 (some really long queries)
sga_max_size from 680M to 800M
db_cache_size from 32M to 50M
java_pool_size from 117M to 6M (not used)
large_pool_size from 16M to 10M
parallel_max_servers from 5 to 8 (we have 8 cpus)
parallel_min_servers from 0 to 4 (we do have some tables with a paralled degree of either 4 or 8)
pre-page_sga to true
sort_area_retained_size from 0 to 4M (same as sort area size)
shared_pool_size from 419M (@%&*!?#) to 50M

That is all I am doing for starters. I will begin helping with beastly code as time allows. Please let me know if I am all wet on any of these changes.

The previous DBA did not install Statspack (*@!+~?$) , and I have not had time yet. Will Do ASAP.

They have been shutting down every Saturday for a cold backup of the DB. What a pathetic backup stragegy. No exports either. I will address that as well in the future.

I have to bounce it tonight to make the changes since there is no SPFILE and besides the *BELOVED* auditors want to have the audit trail turned on. It took a lot of persuading, but I told the manager no DBA worth anything would ever make changes to an init.ora and then not bounce the DB and watch it come back up. (As opposed to the praying it comes back up after the shutdown/startup on Saturday when I am not there).

Was not sure what you meant by "do you resize your SGA?".

Hi Tom,

I'm shocked by this and have no answer :-

> select * from v$sgastat;

POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 75804
db_block_buffers 545259520
log_buffer 1048576
shared pool free memory 46770968
shared pool miscellaneous 4286131916
shared pool KGFF heap 89356
shared pool trigger inform 180
shared pool transactions 220324
shared pool fixed allocation callback 960
shared pool PLS non-lib hp 2096
shared pool KQLS heap 24854604
shared pool enqueue_resources 118656
shared pool db_handles 100000
shared pool sessions 485100
shared pool table definiti 8320
shared pool State objects 279984
shared pool message pool freequeue 124552
shared pool db_files 231884
shared pool Checkpoint queue 92232
shared pool long op statistics array 99000
shared pool ktlbk state objects 105716
shared pool PL/SQL MPCODE 40244
shared pool table columns 38368
shared pool KGK heap 14176
shared pool dictionary cache 10455232
shared pool PX subheap 6860
shared pool DML locks 118560
shared pool PL/SQL DIANA 324412
shared pool db_block_hash_buckets 1196040
shared pool library cache 42162380
shared pool db_block_buffers 9052160
shared pool sql area 55429704
shared pool processes 161600
shared pool ktprhtnew3 9858688
shared pool PX msg pool 54152
shared pool event statistics per sess 774000
large pool free memory 25165824
java pool free memory 32768


ps8sys > show parameter shared

NAME TYPE VALUE
------------------------------------ ----------- ----------
hi_shared_memory_address integer 0
shared_memory_address integer 0
shared_pool_reserved_size string 9017753
shared_pool_size string 180355072


ps8sys > show sga

Total System Global Area 765536284 bytes
Fixed Size 75804 bytes
Variable Size 219144192 bytes
Database Buffers 545259520 bytes
Redo Buffers 1056768 bytes

The shared pool size is only 180 MB's but when I do a select on v$sgastat the miscellaneous = 4286131916,
how come it's 4GB's ???

Our server has 2GB RAM and it's oracle8i.

Also can you please see if you can spot something unusual?

Thanks as always Tom,
Regards,
RD.

Hi Tom,

Yes the oracle Production database is 8i (the client I am using to access it (remotely) is 9i). And I am logged in as system.

Please can you see if there are any other alarming abnormalities there too.

MANY Thanks as always.
Regards,
RD.

Hi Tom,

I will send it to support.
But why would it go to negative ??? What does that mean ?
and please can you see if there is anything alarming or unusual in the other sga parameters.
Wish I had your knowledge, experiance and brains.Sigh!!
Thanks lots as always.
Regards,
RD.

Hi,
What I meant in the above question is that what miscell.
is in context of shared pool? What is its function and is the above observation of it becomming negative any kind of emergency?
Also please could you check if the other sqa parameters are unusual or alarming.

Regards,
RD.

Hi Tom,

I used bind variable in all my coding. But, occasionally, i got ora-04031 error.
I think it's because of multi versioning (I've read somewhere in your site).

Here's the statspack report that confirm my guess :
SQL ordered by Version Count for DB: SD Instance: sd Snaps: 2601 -2613
-> End Version Count Threshold: 20

Version
Count Executions Hash Value
-------- ------------ ------------
87 27,450 369952216
INSERT INTO MTLOG ( LOGUSERID,LOGDATE,LOGTIME,LOGTABLENAME,LOGKE
YFIELD,LOGKEYVALUE,LOGDESC ) VALUES ( :b1,SYSDATE,TO_CHAR(SYSDA
TE,'hh24:mi:ss'),:b2,:b3,:b4,:b5 )

Here's the mtlog table :
create table MTLOG
(
LOGUSERID VARCHAR2(10) not null,
LOGDATE DATE not null,
LOGTIME VARCHAR2(8) not null,
LOGTABLENAME VARCHAR2(20) not null,
LOGKEYFIELD VARCHAR2(80) not null,
LOGDESC VARCHAR2(2000) not null,
LOGKEYVALUE VARCHAR2(80) not null
);

Then I add rpad function to each value that will be inserted, so all value will have the same size. But it doesn't work. The version count in the statspack report still increasing.
What do I miss here, Tom? I use Oracle 8.1.7.

Thank you in advance,
Eka

ora-4031 and multi-versioning are as far separated from eachother as you can get.  One is not related to the other at all.


87 versions of a query is not unusual -- but let me show you 

a) how to see what is different, why there are 87 of them
b) how you can "fix that"


Looking at that table, I would say it is due to different bind sizes.  the size of bind variables can affect query plans in general so there are separate thresholds setup.

<b>so, say you have a procedure like this:</b>


ops$tkyte@ORA9IR2> create or replace procedure do_insert( l1 in number, l2 in number, l3 in number, l4 in number, l5 in number )
  2  as
  3          l_loguserid    varchar2(10);
  4          l_logtablename varchar2(20);
  5          l_logkeyfield  varchar2(80);
  6          l_logdesc      varchar2(2000);
  7          l_logkeyvalue  varchar2(80);
  8  begin
  9      l_loguserid := rpad('x',l1,'x');
 10      l_logtablename := rpad('x',l2,'x');
 11      l_logkeyfield := rpad('x',l3,'x');
 12      l_logdesc := rpad('x',l4,'x');
 13      l_logkeyvalue := rpad('x',l5,'x');
 14
 15      INSERT INTO MTLOG
 16      ( LOGUSERID,
 17        LOGDATE,LOGTIME,
 18        LOGTABLENAME,
 19        LOGKEYFIELD,
 20        LOGKEYVALUE,
 21        LOGDESC
 22      )
 23      VALUES
 24      ( l_loguserid,
 25        SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'),
 26        l_logtablename,
 27        l_logkeyfield,
 28        l_logkeyvalue,
 29        l_logdesc
 30      );
 31  end;
 32  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
no rows selected
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 1, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
<b>right now, one copy in there.. but the bind metadata for this copy says "all small binds", if we change that:</b>
 
ops$tkyte@ORA9IR2> exec do_insert( 10, 20, 80, 2000, 80 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )

<b>we now have two copies -- they are different due to the large differences in bind variable values in this case (rpad or not, we bound varchar2's to this statement, no amount of functions on these binds will change the fact that the description field is 2000 charcters this time, as opposed to 1 character the first time)

This shows how to see what "is different", why the children exist - v$sql_shared_cursor has a ton of "_mismatch" columns that tell you why the child cursor was created:</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select kglhdpar, address, bind_mismatch
  2    from v$sql_shared_cursor
  3   where kglhdpar in
  4   ( select address
  5       from v$sql
  6      where upper(sql_text) like 'INSERT%MTLOG%' )
  7  /
 
KGLHDPAR ADDRESS  B
-------- -------- -
5D854274 5DCF5610 N
5D854274 5EDC6CAC Y
 
<b>Here that shows the the second cursor was created due to a bind mismatch with the prior cursor....

So, what are these thresholds?</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> alter system flush shared_pool;
 
System altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
no rows selected
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 1, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 32, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
<b>sizes 1..32 for that varible seem to share, but:</b>
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 33, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
<b>33 kicks in a new child, the next threshold is:</b>

ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 128, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 129, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )
 
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( :B5, SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'), :B4, :B
3, :B2, :B1 )

<b>apparently 128 and so on (no, i don't have the list, I just hunt and peck for them myself)

So, if we believe this insert is the cause of our problems AND we want to reduce this to one shareable cursor AND we are using PLSQL, then -- the following "semi hackish approach" works -- we use fixed sized bind variables:</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect /
ops$tkyte@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> alter system flush shared_pool;
 
System altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure do_insert( l1 in number, l2 in number, l3 in number, l4 in number, l5 in number )
  2  as
  3          l_loguserid    char(10);
  4          l_logtablename char(20);
  5          l_logkeyfield  char(80);
  6          l_logdesc      char(2000);
  7          l_logkeyvalue  char(80);
  8  begin
  9      l_loguserid := rpad('x',l1,'x');
 10      l_logtablename := rpad('x',l2,'x');
 11      l_logkeyfield := rpad('x',l3,'x');
 12      l_logdesc := rpad('x',l4,'x');
 13      l_logkeyvalue := rpad('x',l5,'x');
 14
 15      INSERT INTO MTLOG
 16      ( LOGUSERID,
 17        LOGDATE,LOGTIME,
 18        LOGTABLENAME,
 19        LOGKEYFIELD,
 20        LOGKEYVALUE,
 21        LOGDESC
 22      )
 23      VALUES
 24      ( <b>rtrim(</b> l_loguserid ),
 25        SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss'),
 26        rtrim( l_logtablename ),
 27        rtrim( l_logkeyfield ),
 28        rtrim( l_logkeyvalue ),
 29        rtrim( l_logdesc )
 30      );
 31  end;
 32  /
 
Procedure created.
 
<b>the rtrim's are needed since the CHAR is going to be fixed sized.  Now the bind variable lengths are 100% constant -- regardless of their values</b>


ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
no rows selected
 
ops$tkyte@ORA9IR2> exec do_insert( 1, 1, 1, 1, 1 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( RTRIM( :B5 ), SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss')
, RTRIM( :B4 ), RTRIM( :B3 ), RTRIM( :B2 ), RTRIM( :B1 ) )
 
 
ops$tkyte@ORA9IR2> exec do_insert( 10, 20, 80, 2000, 80 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select sql_text from v$sql where upper(sql_text) like 'INSERT%MTLOG%';
 
SQL_TEXT
-------------------------------------------------------------------------------
INSERT INTO MTLOG ( LOGUSERID, LOGDATE,LOGTIME, LOGTABLENAME, LOGKEYFIELD, LOGK
EYVALUE, LOGDESC ) VALUES ( RTRIM( :B5 ), SYSDATE,TO_CHAR(SYSDATE,'hh24:mi:ss')
, RTRIM( :B4 ), RTRIM( :B3 ), RTRIM( :B2 ), RTRIM( :B1 ) )
 
 

Thanks Tom, your explanation is very clear. That'll help me a lot.
Tom, you said "ora-4031 and multi-versioning are as far separated from eachother as you can get. One is not related to the other at all".
In my understanding, if I have too many query version, does it mean that it will take quite amount of shared pool memory? Then if the shared pool is not enough, it will lead to ora-4031?
Thank you.

On 64-bit Solaris 8 running Oracle 9iR2, assuming I set my set my shmsys:shminfo_shmmax parameter large enough, is there any Oracle limitation to the size of my SGA?

What is the significance of db_block_buffer = 0

sho parameter db_block_buffers

</code> http://www.quest-pipelines.com/pipelines/dba/tips03.htm <code>

Compliments of Mike Ault, DBA Pipeline SYSOP (AULTM@tusc.com)
For more online references from Mike Ault, check out ROBO Books International.

Note that if you do not set the SGA_MAX_SIZE parameter then it will be dynamically set as you change parameters and you don't have a big problem, otherwise:

Log in to OEM console as SYS AS SYSDBA to your database

Select instance

Select Configuration

Select All Initialization parameters

Set:
DB_BLOCK_BUFFERS 0

SGA_MAX_SIZE to whatever you want max size

DB_CACHE_SIZE to SGA_MAX_SIZE - SHARED_POOL_SIZE - LARGE_POOL_SIZE

LOG_BUFFERS - DB_xK_CACHE_SIZE (x is 2, 4, 8, 16, 32)

DB_xK_CACHE_SIZE to 1 less than a multiple of 4m or 16m (if SGA_MAX_SIZE >128m 16 m)

Shutdown and restart -- Note for a migrated instance there may be up to 3 ini files that need to be looked at to eliminate all setting for DB_BLOCK_BUFFERS, you cannot have DB_BLOCK_BUFFERS set to anything but null or zero.

Restart OEM if needed and log in as SYSTEM to your DB

Select Object - Create - Tablespace - Create from top menu bar

Fill in general parameters

Select storage tab and select db block size (if the parameters took should have down arrow to right of db block size button, if not, see 6 above. You can use see SQL text button to see actual SQL text used.

Select Create

Voila! an xK tablespace in an yK database.

okay, Thanks very much for the information

hi tom,
got the folowing error with the above solution from metalink.
ORA-01026:multiple buffers of size > 4000 in the bind list
Cause: More than one long buffer in the bind list.
Action: Change the buffer size to be less than 4000 for the bind variable bound to a normal column.

but on decreasing the size the error still persists.ca u please help.

thanx

Tom,

<quote from Tom, above>
-----------------------
to avoid 4031's -- use bind variables. I've

a) never monitored it
b) never hit a 4031 in my life
-----------------------
<quote from Tom, above>

Tom, I have run your query to detect 'not' using bind variables in the shared pool, and we appear to be using bind variables.... yet we are suddenly getting ORA-04031 errors.

Do you have any other suggestions on how to detect/avoid these ORA-04031 errors?

Thanks,

Robert.

Tom,

Here is current info about system.

SQL> show parameter shar

NAME                                 TYPE    VALUE
------------------------------------ ------- ---------------
cursor_sharing                       string  EXACT
hi_shared_memory_address             integer 0
shared_memory_address                integer 0
shared_pool_reserved_size            string  5000000
shared_pool_size                     string  100000000

SQL> select * from v$sgastat;

POOL        NAME                                      BYTES
----------- ------------------------------ ----------------
            fixed_sga                                73,888
            db_block_buffers                  1,515,520,000
            log_buffer                           10,240,000
shared pool free memory                           5,564,720
shared pool miscellaneous                        17,915,732
shared pool OUTLNCACHE                               18,236
shared pool PX msg pool                          61,777,992
shared pool KQLS heap                               601,356
shared pool KGFF heap                               418,000
shared pool KGK heap                                 46,612
shared pool db_block_hash_buckets                 6,444,376
shared pool trigger inform                              460
shared pool table columns                            18,356
shared pool db_block_buffers                     50,320,000
shared pool State objects                         6,561,104
shared pool table definiti                            1,600
shared pool PL/SQL DIANA                            366,396
shared pool PX subheap                              409,076
shared pool PLS non-lib hp                            2,096
shared pool dictionary cache                        612,068
shared pool library cache                         3,891,076
shared pool sql area                             16,672,400
shared pool joxs heap init                            4,248
shared pool fixed allocation callback                 1,280
shared pool PL/SQL MPCODE                           546,336
large pool  free memory                          10,000,000
java pool   free memory                          34,701,312
java pool   memory in use                           299,008

28 rows selected.

This is a test system so we don't have many online users, but developers write/submit jobs using dbms_jobs in parallel (say 15 at a time).

I have run a script you provided (and a simlar one I found on Metalink) that is supposed to show duplicates in the shared pool... but I found none.

(I noticed a lot of "row cache lock" waits for several sessions (I believe all on a particular sequence) could this have any bearing on an ora-04031)?

Any ideas?

Thanks,

Robert. 

Hi Tom,

Here is the info you requested (sorry about no version earlier!).
I tried to "X" out any 'personal' info. Thanks, Robert.

Starting up ORACLE RDBMS Version: 8.1.7.4.0.
System parameters with non-default values:
processes = 500
timed_statistics = TRUE
resource_limit = TRUE
event = 10262 trace name context forever,level 648, 1410 trace name errorstack level 3
shared_pool_size = 100000000
shared_pool_reserved_size= 5000000
large_pool_size = 10000000
java_pool_size = 35000000
_enqueue_locks = 100000
enqueue_resources = 10000
nls_date_format = DD-MON-RR
control_files = /u02/oradata/xxxx/xxxx_control01.ctl, /u03/oradata/xxxx/xxxxx_control02.ctl, /u04/oradata/xxxxx
/xxxx_control03.ctl
db_block_buffers = 370000
db_block_size = 4096
db_block_lru_latches = 8
db_writer_processes = 8
compatible = 8.1.7.0.0
log_buffer = 10240000
_log_io_size = 30000000
log_checkpoint_interval = 500000
_log_simultaneous_copies = 12
db_files = 2000
db_file_multiblock_read_count= 24
fast_start_io_target = 30000
_allow_resetlogs_corruption= TRUE
recovery_parallelism = 4
dml_locks = 500
max_rollback_segments = 100
rollback_segments = r01, r02, r03, r04, r05, r06, r07, r08, r09, r10, r11, r12, r13, r14, r15, r16, r17, r18, r19, r20, r21
, r22, r23, r24, r25, r26, r27, r28, r29, r30, r31, r32, r33, r34, r35, r36, r37, r38, r39
fast_start_parallel_rollback= low
max_enabled_roles = 25
remote_login_passwordfile= NONE
distributed_transactions = 200
audit_trail = TRUE
sort_area_size = 1048576
sort_area_retained_size = 1048576
db_name = tatest
open_cursors = 500
ifile = /u01/app/oracle/admin/xxxxx/pfile/configxxxxx.ora
os_authent_prefix = xxxxxx
optimizer_mode = choose
query_rewrite_enabled = TRUE
query_rewrite_integrity = trusted
remote_dependencies_mode = SIGNATURE
utl_file_dir = *
job_queue_processes = 15
_system_trig_enabled = TRUE
parallel_max_servers = 400
background_dump_dest = /u01/app/oracle/admin/xxxxx/bdump
user_dump_dest = /u01/app/oracle/admin/xxxxx/udump
max_dump_file_size = 10240
core_dump_dest = /u01/app/oracle/admin/xxxxx/cdump

It's given lot of help to me

Tom,

Thanks for your help and feedback.
I will follow up on your suggestions.

But please explain what the problem with "utl_file_dir=*"?

Thanks,

Robert.

The only underscore parameter that might help your particular situation is one you haven't set yet (the rest probably should be removed unless there was an extremely good reason they were set in the first place, and that reason was somehow still relevant today).

_PX_use_large_pool = true

I believe this will move the 61,777,992 bytes listed in v$sgastat under "PX msg pool" from the shared pool to the large pool where it won't have to worry about memory fragmentation that might be happening in the shared pool. You'll have to bump up the large pool from 10MB to 70MB or 80MB to safely accommodate this though (include whatever fudge factor you want). Then you can shrink the shared pool accordingly or leave it alone for other things or future growth.

Just my two cents.

Tom,

We have some issue in accessing the table from SAS.

I have 2 databases 1)DB2 and 2)Oracle 9i

and the same set of data is present in both the databases.

the users are using SAS for doing analysis.

Now the issue is.

DB2 database is having 12 CPUs
and Oracle is having 23 CPUS

so ideally Oracle should be faster then DB2. But

when I create the dataset with a table with "select * from xyz" (Note without any where conditions) in DB2 it will take 2 min to fetch 7,000,000 records

and the same in Oracle it will take 14 min to fetch the same no of records.

So now the question is this any thing to do with the oracle parameter sating or any thing to do with the SAS?

Please help me to solve this issue.

Thanks
Michael

Tom,

I keep to get the following error as I try to export the database. The Java_pool_size parameter is set to 0 and I can not change it without restarting the database.

What can I do to get a consisten export of the database schema ( I try to get a consistent export of schema in running 9.2.0.7 database)

Thanks in advance

EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4096 bytes of shared memory ("java pool","unknown
object","joxs heap init","ioc_allocate_pal")
EXP-00000: Export terminated unsuccessfully

Tom, sorry I am on 9.1.7 - I explicitly wrote this in my question...
The odd thing is that I have a prod and test databases both with java_pool_size=0 but I get the error only on prod, I can export test without having any problem.

And BTW, what JAVA_POOL has to do with export? We did not use java procedures in the database - so it is set to 0 for years and it is the first problem with java_pool.

Do you have any suggestion for working aroung with this problem? It is really difficult to find time to restart our prod database...

You may want to query dba_objects in the database where export works vs. fails (assuming both have java_pool_size=0). Maybe one system has objects with object_type like 'JAVA%' or maybe even XML and the other doesn't.
I guess you cannot alter java_pool_size in 9iR2.
SQL> select component from v$sga_dynamic_components;

COMPONENT
----------------------------------------------------------------
shared pool
large pool
buffer cache

The same query returns many more rows in 10gR2 including java pool.
.
Otherwise you could ask for help from Support to set an event in the server process for exp so it dumps an errorstack when the ORA-4031 error happens or get sql_trace output. That might give more info about what export is trying to do (or object it is trying to export) when it tries to allocate memory from the non-existent Java pool.
 

Tom,

Can you think of any way this can happen?

SELECT * FROM v$sgainfo

1 Fixed SGA Size                 791484         No
2 Redo Buffers                 1048576         No
3 Buffer Cache Size         503316480 Yes
4 Shared Pool Size         629145600 Yes
5 Large Pool Size                 8388608         Yes
6 Java Pool Size                 109051904 Yes
7 Streams Pool Size         0         Yes
8 Granule Size                 8388608         No
9 Maximum SGA Size         1577058304 No
10 Startup overhead in Shared Pool 33554432 No
11 Free SGA Memory Available 318767104

select * from v$sga_dynamic_free_memory;
318767104

select * from v$sysmetric where metric_name = 'Shared Pool Free %';
Shared Pool Free % 1% Free/Total

In terms of configuration, I have ASMM switched on with SGA_MAX_SIZE=1.5Gb, SGA_TARGET=1.2Gb and it has 300Mb of free space at the moment. However, the shared pool says it only has 6Mb free and I am getting regular 4031s.

The database is definitely using bind variables. The reason for the large shared pool is to try and mitigate this issue temporarily until we find what is causing it.

Surely if a process needs memory, ASMM should expand the relevant pool (shared pool or java pool) using the 300Mb of free memory it has and so there should be no way of getting 4031.

Any suggestions for avenues of investigation would be appreciated.

Version: 10.1.0.5
OS: Windows 2000
Concurrent Usage: 2~3 Apache Sessions
SGA_TARGET: 1.2Gb
SGA_MAX_SIZE: 1.5Gb
PGA_AGGREGATE_TARGET: 500Mb
JAVA_POOL_SIZE: 100Mb
SHARED_POOL_SIZE: 600Mb

ASMM - Automatic Shared Memory Management: I thought that was the current acronym, though the marketing department seem to come up with a new one every minute.

Basically, we have an sga target and sga max size and we want to let oracle do the rest for us.

The database doesn't have a hard parse or bind variable problem. Soft parse % is around 99% from a 15 minute statspack during busy times. The only reason to have the 600Mb shared pool is to try to cure the 4030s.

The errors all seem to be coming from some fairly hefty java stored procedures - the 4031 is coming when trying to allocate memory from the "joxp heap".

Also, when we do get these problems, flushing the shared pool does not solve them, nor does increasing the sga target - the only way I've found is to restart the windows service. Does this suggest a memory leak in the oracle JVM?

I agree with the acronyms - unfortunately oracle marketing tend to sprinkle them around liberally and they get in your head after a while. Without acronyms...

We are using automatic shared memory management with sga_target=1.2Gb and sga_max=1.5Gb. We also use automatic pga management with pga_aggregate_target=1Gb.

All the memory advisories tell us that this is "way overkill" for the work we are doing on the box (and past experience says the same). For example, at peak times we are normally using <100Mb of the 1Gb PGA and the advisor says we could drop it to under 100Mb without having any overallocations.

The stack traces always come from the java stored procedure and so are creating dumps in the UDUMP directory - these are enormous (12Mb - 25Mb) but I guess the important bit is the following

*** SERVICE NAME:(SYS$USERS) 2007-09-24 14:46:37.781
*** SESSION ID:(134.7030) 2007-09-24 14:46:37.781
ioc_allocate (size: 2097183, heap name: *** PGA ***, flags: 60008) caught 4030
*** 2007-09-24 14:46:37.781
ksedmp: internal or fatal error
ORA-04030: out of process memory when trying to allocate 2097196 bytes (joxp heap,f:OldSpace)

This is why I was thinking memory leak - none of the pools are stretched according to the advisors, but we are still getting errors. To prove the "we have bind variables" statement, a statspack says

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:   99.98
            Buffer  Hit   %:   99.97    In-memory Sort %:  100.00
            Library Hit   %:   99.85        Soft Parse %:   99.26
         Execute to Parse %:   71.12         Latch Hit %:   99.98
Parse CPU to Parse Elapsd %:   93.57     % Non-Parse CPU:   97.05

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   93.70   93.75
    % SQL with executions>1:   82.70   90.62
  % Memory for SQL w/exec>1:   90.46   94.05

Any suggestions for queries to run when we hit this issue would be appreciated.

declare
   type array is table of long;
   data array;
begin
   for i in 1 .. 2000000000
   loop
       data(i) := rpad('*',32765,'*');
   end loop;
end;
/

Now we're getting somewhere. Yes the process is doing something pretty heavyweight (xsl transformation of some pretty large documents and then running the transformed output through a processor), however...

1. The OS is telling me it has 1.2Gb of memory left
2. The process uses a reasonable amount (say 20Mb)

So if you have free memory on the box (lots of it) any thoughts as to why you would get processes running out of memory? My thought was the Windows 2Gb limit...

1. Is this the limit of the sga size or the sga size + the pga aggregate target?
2. If neither of these exceed 2Gb, any other suggestions (corrupt RAM?) to look into?

Thanks for your help on this one - it's proving a bit of a brain teaser.

Tom,

Thought I would publish the resolution to the above problem as a warning to others.

The issue did turn out to be the Windows 2Gb process limit.

Firstly, on windows, when you set an SGA_MAX_SIZE, this amount of memory is preallocated by the process but (and this was critical) doesn't show up in task manager. You can use the pslist utility from sysinternals to show you the actual memory allocated.

Secondly, the 2Gb process limit includes the shared libraries and dll's etc, so the actual usable memory is normally between 1.5Gb and 1.8Gb.

This means if you have SGA_MAX_SIZE=1.5Gb and then you get a couple of hundred meg of PGA then you hit the process limits.

Reducing SGA_MAX_SIZE to 800Mb and SGA_TARGET to 800Mb and all the errors have disappeared.

Tom,

ASMM - I read about ASMM in Windows server that SGA MAX seems better be set less than 1.2G. then set TARGET to 1G seems works well in my system.

I was reading this thread, where I came across following comment

<QUOTE>
Large shared pool without binds = eventual ora4031's and need to flush. Smaller shared pool without binds = it'll take care of itself
</QUOTE>

Can you please explain?

Thanks for the explanation

Hello Chris/Connor,

I should be really knowing this by now but somehow I have managed to confuse myself and hence asking this question.
For a 19c multitenant database having single PDB, what is the most reliable way to find out the current and historical size of shared pool?

I used below SQLs to get current size of shared pool and V$SGASTAT report different number than other sources so am wondering which one is correct and why the difference?

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
11-MAR-2025 15:18:06 C##RO_PERFORMANCE_DBA@LBGTH3PR_CDB > select component, oper_type, round(initial_size/1024/1024/1024, 1) init_GB, round(final_size/1024/1024/1024, 1) final_GB, status, start_time
  2  from gv$sga_resize_ops
  3  where component = 'shared pool'
  4  and inst_id = 1
  5  order by start_time desc fetch first 1 rows only ;

COMPONENT                                                        OPER_TYPE        INIT_GB   FINAL_GB STATUS    START_TIME
---------------------------------------------------------------- ------------- ---------- ---------- --------- --------------------
shared pool                                                      GROW                 3.3        3.4 COMPLETE  11-MAR-2025 00:07:20

SQL> select NVL(pool, name) ,round(sum(bytes)/1024/1024/1024, 1) GB
  2  from gv$sgastat
  3  where (pool = 'shared pool' OR name = 'buffer_cache')
  4  and inst_id = 1
  5  group by NVL(pool, name) ;

NVL(POOL,NAME)                     GB
-------------------------- ----------
shared pool                       2.5
buffer_cache                        6

SQL> select name, round(bytes/1024/1024/1024, 1) GB
  2  from gv$sgainfo
  3  where name = 'Shared Pool Size'
  4  and inst_id = 1 ;

NAME                                     GB
-------------------------------- ----------
Shared Pool Size                        3.4


SQL> select inst_id, shared_pool_size_factor, shared_pool_size_for_estimate
  2  from gv$shared_pool_advice
  3  where shared_pool_size_factor = 1
  4    and inst_id = 1 ;

   INST_ID SHARED_POOL_SIZE_FACTOR SHARED_POOL_SIZE_FOR_ESTIMATE
---------- ----------------------- -----------------------------
         1                       1                          3520

Thanks in advance

SQL> select NVL(pool, name) ,round(sum(bytes)/1024/1024, 1) MB
  2      from v$sgastat
  3      group by NVL(pool, name) ;

NVL(POOL,NAME)                     MB
-------------------------- ----------
fixed_sga                         4.7
buffer_cache                     2240
log_buffer                        4.2
shared_io_pool                    128
shared pool                       179
large pool                       15.5

6 rows selected.

Hello Connor,

Thank you for taking time to respond.
I am not sure I understand your response. In my case, v$sgastat is reporting 2.5 GB but other views are reporting 3.4 GB size.
My question is about this difference and which one is more accurate.

Thanks in advance

Hello connor,

Amazing metaphor...worked as a light bulb for me.
Thank you very much for your patience.