Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Danilo.

Asked: February 24, 2001 - 12:48 pm UTC

Last updated: November 30, 2006 - 8:36 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Tom,

I had a database with problems of buffer free and block size was 4k, we did an upgrade in this Unix Box (Solaris 2.6), so I recreated the database with a block size of 8k, increase the db_block_buffers, the DB is working OK, I don´t have buffer free
problems, but I started to have problems with ORA-4031 in shared_memory.

Our application is based on Java/JDBC with lots of dynamic SQL, I increased the size of shared_pool size, but the problem is still going on (shared_pool - 120M), in about 3 to 4 minutes all the shared pool is fragments in blocks of 76 ou 254 bytes, the free SGA is about 100Mb, but the greatest block is 3k size.

I put the alter system flush shared_pool in cron with a interval of 5 minutes. I don´t know what is going on, it seems that Oracle is taking to long to defragment the shared-pool, with the 4k block size I didn't have the problem.

Thanks

Danilo Bonamini
danilo_bonamini@yahoo.com


I am at Home Know, so I don´t have the message, but I will get it, I have hundreds of queries like:
select * from emp where empno = 1;
select * from emp where empno = 2;
select * from emp where empno = 3;
select * from emp where empno = 4;

The message had something of sga heap, table_name, and I think library cache.

and Tom said...

Ok, this is an easy one to diagnose.

You are not using bind variables. This is terrible. Flushing the shared pool is a bad solution -- you are literally killing the performance on your box by not using them.

If I was to write a book on how to build “non scalable applications in Oracle”, this would be the first and last chapter. This is a major cause of performance issues and a major inhibitor of scalability in Oracle. The way the Oracle shared pool (a very important shared memory data structure) operates is predicated on developers using bind variables. If you want to make Oracle run slowly, even grind to a total halt – just refuse to use them.

For those that do not know, a bind variable is a placeholder in a query. For example, to retrieve the record for employee 1234, I can either query:

SELECT * FROM EMP WHERE EMPNO = 1234;

Or I can query:

SELECT * FROM EMP WHERE EMPNO = :empno;

And supply the value for :empno at query execution time. The difference between the two is huge, dramatic even. In a typical system, you would query up employee 1234 maybe once and then never again. Later, you would query up employee 456, then 789 and so on. If you use literal (constants) in the query – each and every query is a brand new query, never before seen by the database. It will have to be parsed, qualified (names resolved), security checked, optimized and so on. In short, it will be compiled. Every unique statement you execute will have to be compiled every time. This would be like shipping your customers Java source code and before calling a method in a class – you would invoke the Java compiler, compile the class, run the method and then throw away the byte code. The next time you wanted to execute the same exact method, you would do the same thing – compile it, run it and throw it away. Executing SQL statements without bind variables is very much the same thing as compiling a subroutine before each and every call. You would never consider doing that in your application – you should never consider doing that to your database either.

Not only will parsing a statement like that (also called a HARD parse) consume many more resources and time then reusing an already parsed query plan found in the shared pool (called a SOFT parse), it will limit your scalability. We can see it will obviously take longer, what is not obvious is that it will reduce the amount of users your system can support. This is due in part to the increased resource consumption but mainly to the latching mechanisms for the library cache where these plans are stored after they are compiled. When you hard parse a query, we will spend more time holding certain low level serialization devices called latches. These latches protect the data structures in the shared memory of Oracle from concurrent modifications by two sessions (else Oracle would end up with corrupt data structures) and from someone reading a data structure while it is being modified. The longer and more often we have to latch these data structures, the longer the queue to get these latches will become. Similar to the MTS architecture issue described above, we will start to monopolize scarce resources. Your machine may appear to be underutilized at times – but yet everyone in the database is running very slowly. This will be because someone is holding one of these serialization mechanisms and a line is forming. You are not able to run at top speed.

The second query above on the other hand, the one with :empno, is compiled once and stored in the shared pool (the library cache). Everyone who submits the same exact query that references the same object will use that compiled plan (the SOFT parse). You will compile your subroutine once and use it over and over again. This is very efficient and the way the database is intending you will do your work. Not only will you use less resources (a SOFT parse is much less resource intensive), but you will hold latches for less time and need them less frequently. This increases your performance and greatly increases your scalability.

Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test:

tkyte@TKYTE816> alter system flush shared_pool;
System altered.

tkyte@TKYTE816> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = ' || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
18 ' seconds...' );
19 end;
20 /
14.86 seconds...

PL/SQL procedure successfully completed.

tkyte@TKYTE816> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 begin
7 for i in 1 .. 1000
8 loop
9 open l_rc for
10 'select object_name
11 from all_objects
12 where object_id = :x'
13 using i;
14 fetch l_rc into l_dummy;
15 close l_rc;
16 end loop;
17 dbms_output.put_line
18 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
19 ' seconds...' );
20 end;
21 /
1.27 seconds...

PL/SQL procedure successfully completed.

That is pretty dramatic. The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:992630543630 <code>
for a feature that might help you in 8.1.6 (not 8.1.5, new feature) although it has the side effect of binding everything -- some queries need a mixture of binds and constants and with cursor_sharing -- you only get binds.




Rating

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-04031 in snp background process....

Mohan Naik, August 29, 2001 - 7:19 am UTC

Tom,

I'm getting a same kind of error but for image: ora_snp2_WW8 (where WW8 is my instance name) in Oracle 8.0.6.0.0

Errors in file /oravl01/oracle/adm/WW8/bdump/snp2_1575.trc:
ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4184 bytes of shared memory ("shared pool","PROCEDURE$","sga heap","state objects")

Please let me know what has to be done in such a case ?


shared pool

yazid, April 18, 2003 - 6:27 am UTC

Hi
I tested your example, but no big diffrences in the time of execution. I dont't understand what happen :
First execution : 27,5
Second execution : 26,25 (diffrences : 1,25s : not much)

I work with a database V81700 on Win2000.

system@declt> declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 i number := 0;
7 date_jour date;
8 begin
9 -- tache 1
10 for i in 1 .. 1000
11 loop
12 open l_rc for
13 'select object_name
14 from all_objects
15 where object_id = ' || i;
16 fetch l_rc into l_dummy;
17 close l_rc;
18 end loop;
19 dbms_output.put_line('Tache1 -utilise all_objects- : '|| round( (dbms_utility.get_time-l
_start)/100, 2 ) || ' seconds...' );
20 end;
21 /
Tache1 -utilise all_objects- : 27,5 seconds...

Procédure PL/SQL terminée avec succès.

system@declt> r
1 declare
2 type rc is ref cursor;
3 l_rc rc;
4 l_dummy all_objects.object_name%type;
5 l_start number default dbms_utility.get_time;
6 i number := 0;
7 date_jour date;
8 begin
9 -- tache 1
10 for i in 1 .. 1000
11 loop
12 open l_rc for
13 'select object_name
14 from all_objects
15 where object_id = ' || i;
16 fetch l_rc into l_dummy;
17 close l_rc;
18 end loop;
19 dbms_output.put_line('Tache1 -utilise all_objects- : '|| round( (dbms_utility.get_time-l_sta
20* end;
Tache1 -utilise all_objects- : 26,25 seconds...

Procédure PL/SQL terminée avec succès.

I checked the v$sqlarea and the result was 1Load an 2 executions (the code was ot unloaded):
system@declt> r
1 select sql_text, executions, loads from v$sqlarea
2 where sql_text like '%begin%'
3* order by first_load_time desc

SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS LOADS
---------- ----------
select sql_text, executions, loads from v$sqlarea where sql_text like '%begin%'
order by first_load_time desc
3 2

declare type rc is ref cursor; l_rc rc; l_dummy all_objects.ob
ject_name%type; l_start number default dbms_utility.get_time; i number
:= 0; date_jour date; begin -- tache 1 for i in 1 .. 1000 loop
open l_rc for 'select object_name from all_
objects where object_id = ' || i; fetch l_rc into l_dumm
y; close l_rc; end loop; dbms_output.put_line('Tache1 -
utilise all_objects- : '|| round( (dbms_utility.get_time-l_start)/100, 2 ) || '
seconds...' ); end;
2 1

Any adea please
thank you


Tom Kyte
April 18, 2003 - 12:02 pm UTC

ALTER SYSTEM FLUSH SHARED_POOL;


you ran it more then once, you soft parsed the second time around.


tkyte@TKYTE816> alter system flush shared_pool;
System altered.


that simulates REALITY. when you have an application that doesn't use binds -- it is generating UNIQUE sql that never existed before and will not be reused after.

To find sql that is not using bind vars

Sachin, May 20, 2003 - 11:25 am UTC

Tom,
I remember seeing a script on your site that finds sql that is not using bind variables.
Could you please post it here...

Thanks.

Tom Kyte
May 20, 2003 - 1:36 pm UTC

search for

remove_constants

batch job procedure parsing and shared pool

Bogy, February 05, 2004 - 6:40 am UTC

Hi Tom,

Could you please tell me whether I am correct or wrong.
If I submit let say 100000 dbms_jobs executing procedure proc_a with different parameters, I will have in job_queue something like:

proc_a('param_1', 'param_2');
proc_a('param_3', 'param_4');
proc_a('param_5', 'param_6');
proc_a('param_7', 'param_8');
proc_a('param_9', 'param_10');

... and so on up to 100000....

Oracle will parse procedure "proc_a" once and execute it 100000 times, right?

So, can this process somehow make any bad influence on a shared memory pool?

I think no, since "proc_a" is parsed once and executed many times. Only what for me is unknown is relation between job queue and shared pool if there is any.


Thanks,


Bogy




Tom Kyte
February 05, 2004 - 7:51 am UTC

but the statement being parsed is:

begin proc_a( 'x', 'y' ); end;
begin proc_a( 'a', 'b' ); end;


they are each unique -- you've slammed the shared pool BIG TIME. My approach (as demonstrated on this site and in the book "Expert one on one Oracle") is to use the job queues like this:


create table parameters_for_proc_a( job_id number primary key, p1 varchar2, p2 varchar2 );

create or replace procedure proc_a( P_JOB in number )
is
l_rec parameters_for_proc_a%rowtype;
begin
select * into l_rec from parameters_for_proc_a where job_id = P_JOB;

... process....
delete from parameters_for_proc_a where job_id = p_job;
commit;
end;
/


and to submit the job, you:

declare
l_job number;
begin
dbms_job.submit( l_job, 'proc_a(JOB);' );
insert into parameters_for_proc_a values ( l_job, param1, param2 );
commit;
end;
/





Rewiew

Bogy, February 05, 2004 - 5:32 pm UTC

Right, I have read about this method in your book, but what is not clear to me is how things work with pl/sql code. With sql query we have parsing, optimization and row-source generation. But how things work with pl/sql. So, for block of code:

begin proc_a( 'x', 'y' ); end;

we will have hard parse but what kind of optimization and row-source generation (for this pl/sql code) which are also the most CPU intensive operations?
There is a obvious overhead of searching SGA in phase of hard pars but can this approach cause problems with ORA-4031 in shared_memory? If so, could you please explain me why?
Also, if we are repeatedly calling "proc_a", this procedure should already been in cash memory and reused? Or because we are calling this procedure repeatedly with static parameters database have to again and again hard parse this proc "from the beginning"?
Could you please put a little light on pl/sql procedure/package parsing process?
Thanks a lot!!!


Tom Kyte
February 06, 2004 - 8:26 am UTC

don't call proc_a that way -- use binds -- period. no if and or buts about it.


begin proc_a( ?, ? ); end;

in java/vb


begin proc_a( :x, :y ); end;

in C/pro*c and others......



shared pool problem with bind variables

Mehdi Torki, July 12, 2005 - 12:46 am UTC

we have a ImportImage stored procedure that its body described below:

(dest_nid number, filename varchar2, img_date varchar2) is

img_blob BLOB;

ctx raw(64) := null;

file_string varchar2(4) := 'file';
dir_string varchar2(8) := 'IMAGEDIR';

begin

delete from ImageTable where NID = dest_nid;


insert into ImageTable (NID, Image, ImageDate)
values (dest_nid, empty_blob(), img_date)

returning Image into img_blob;

ORDSYS.ORDImage.importFrom(ctx, img_blob,
file_string, dir_string, filename);

update ImageTable set image=img_blob where
NID=dest_nid;

end;


this stored procedure inserts an image into database. Also we have a VB application that includes this code:

Set prm1 = cmd.CreateParameter("dest_nid", adNumeric, adParamInput)
Set prm2 = cmd.CreateParameter("filename", adVarChar, adParamInput, 14)
Set prm3 = cmd.CreateParameter("img_date", adVarChar, adParamInput, 8)

'-- Append parameters to command object
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2
cmd.Parameters.Append prm3

...

For Each tFil In fld.Files
temp = Mid(fld.Path + "\", 7)
temp = RemoveCharacters(temp, "\")
nid = temp
...
crDate = Format(tFil.DateLastModified, "yyyy/MM/DD")
crDate = RemoveCharacters(crDate, "/")
...

cmd.CommandText = " call sys.ImportImage(?, ?, ?) "

'-- Assign Parameter Values
cmd(0).Value = Val(nid)
cmd(1).Value = nid + ".jpg"
cmd(2).Value = crDate

cmd.Execute
Next


and SHARED_POOL size is 256M. But after insertion of almost 600000 record we receive this error:

ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select pos#,intcol#,col#,spa...","Typecheck heap","kgghteInit")

please help us!


Tom Kyte
July 12, 2005 - 4:39 pm UTC

well, I surely wouldn't blame binds for that. It is something else.

are you shared server or dedicated server?
large pool configured or set to zero

the update of the blob to itself it sort of redundant isn't it?

shared pool problem with bind variables (2)

Mehdi Torki, August 06, 2005 - 6:59 am UTC

We have Dedicated server and Large pool configured!

Tom Kyte
August 06, 2005 - 9:35 am UTC

if you are dedicated server, what are you using the large pool for? parallel query?

does your system as a whole employ bind variables or do you have LOTS of literal SQL in your shared pool (sql that would be the same if they bound)

shared pool problem with bind variables (3)

Mehdi Torki, August 07, 2005 - 7:36 am UTC

we set large pool for no any specific purpose. when we wanted configure SGA, we configured large pool also. we do not have parallel query. we run only a stored procedure call (that imports images to database) repeatedly over a dedicated server connection.

when I run this query:

select count(HASH_VALUE) copies, SUBSTR(SQL_TEXT,1,64) sqltext from v$sqlarea GROUP BY SUBSTR(SQL_TEXT,1,64)

After 550,000 record insertion - after server startup -,
it return 900 records that most of them have copies = 1


Tom Kyte
August 07, 2005 - 9:39 am UTC

use this script
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580 <code>

shared pool problem with bind variables (4)

Mehdi Torki, August 08, 2005 - 2:16 am UTC 

I run your script and saw below result:

SQL> update t1 set sql_text_wo_constants = remove_constants(sql_text);

805 rows updated.

SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constan ts having count(*) > 100 order by 2 /

no rows selected
 
After this I changed 100 to 1 and run again:

SQL> select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constan ts having count(*) > 1 order by 2;
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
ALTER SESSION SET TIME_ZONE='#'
         2
 
MERGE INTO CACHE_STATS_@$ D USING (SELECT * FROM TABLE(DBMS_STATS_INTERNAL.FORMA
T_CACHE_ROWS(CURSOR((SELECT DATAOBJ# O, STATISTIC# STAT, NVL(VALUE, @) VAL FROM
GV$SEGSTAT WHERE STATISTIC# IN (@, @, @) AND OBJ# > @ AND INST_ID = @) UNION ALL
 (SELECT OBJ# O, @ STAT,NVL(SUM(NUM_BUF), @) VAL FROM X$KCBOQH X WHERE INST_ID =
 @ GROUP BY OBJ#) ORDER BY O))) WHERE CACHEDBLK>@) S ON (D.DATAOBJ# = S.OBJ# AND
 D.INST_ID = S.INST_ID) WHEN MATCHED THEN UPDATE SET CACHED_AVG = CASE WHEN (S.C
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
ACHEDBLK>@ AND CACHED_SQR_AVG <= CACHED_AVG*CACHED_AVG* @) THEN @/(CACHED_NO+@)*
((CACHED_NO-@)/@*CACHED_AVG+S.CACHEDBLK) WHEN (S.CACHEDBLK>@ AND CACHED_SQR_AVG
> CACHED_AVG*CACHED_AVG* @) THEN S.CACHEDBLK ELSE CACHED_AVG END, CACHED_SQR_AVG
 = CASE WHEN (S.CACHEDBLK>@ AND CACHED_SQR_AVG <= CACHED_AVG*CACHED_AVG* @) THEN
 @/(CACHED_NO+@)*((CACHED_NO-@)/@*CACHED_SQR_AVG+ S.CACHEDBLK*S.CACHEDBLK) WHEN
(S.CACHEDBLK>@ AND CACHED_SQR_AVG > CACHED_AVG*CACHED_AVG* @) THEN S.CACHEDBLK*S
.CACHEDBLK ELSE CACHED_SQR_AVG END, CAC
         2
 
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
SELECT /*+ INDEX(IDL_UB@$ I_IDL_UB@) +*/ PIECE#,LENGTH,PIECE FROM IDL_UB@$ WHERE
 OBJ#=:@ AND PART=:@ AND VERSION=:@ ORDER BY PIECE#
         2
 
SELECT NVL(VALUE,'#') FROM MGMT_OMS_PARAMETERS WHERE NAME='#' AND HOST_URL = :B@
 
         2
 
SELECT OWNER FROM DBA_PROCEDURES WHERE OBJECT_NAME = '#' AND PROCEDURE_NAME = '#
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
'
         2
 
SELECT SCALAR_VALUE FROM MGMT_JOB_STEP_PARAMS WHERE JOB_TYPE=:B@ AND STEP_NAME=:
B@ AND IS_SCALAR=@ AND PARAM_NAME LIKE '#'
         2
 
SELECT SUBSTR(VERSION, @+INSTR(VERSION,'#',@,@), INSTR(VERSION,'#',@,@)-INSTR(VE
RSION,'#',@,@)-@) FROM V$INSTANCE
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
         2
 
SELECT TEXT.SQL_TEXT, TEXT.COMMAND_TYPE, STAT.* FROM WRH$_SQLTEXT TEXT , (SELECT
 * FROM (SELECT S.SQL_ID, MAX(S.PLAN_HASH_VALUE) AS PLANHASH, SUM(S.CPU_TIME_DEL
TA) AS CPU_TIME, SUM(S.ELAPSED_TIME_DELTA) AS ELAPSED_TIME, SUM(S.ELAPSED_TIME_D
ELTA - S.PLSEXEC_TIME_DELTA - S.JAVEXEC_TIME_DELTA) AS SQLX_TIME, SUM(S.PLSEXEC_
TIME_DELTA) AS PLSQLX_TIME, SUM(S.JAVEXEC_TIME_DELTA) AS JAVAX_TIME, SUM(S.DISK_
READS_DELTA + S.DIRECT_WRITES_DELTA) AS IO_BLOCKS, SUM(S.IOWAIT_DELTA) AS IO_WAI
T, SUM(S.CLWAIT_DELTA) AS CL_WAIT, SUM(S.ROWS_PROCESSED_DELTA) AS ROWS_PROCESSED
 
SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
, SUM(S.FETCHES_DELTA) AS FETCHES, SUM(S.EXECUTIONS_DELTA) AS EXECS FROM (SELECT
 T@.* FROM WRH$_SQLSTAT T@, WRM$_SNAPSHOT S@ WHERE T@.DBID = S@.DBID AND T@.INST
ANCE_NUMBER = S@.INSTANCE_NUMBER AND T@.SNAP_ID = S@.SNAP_ID AND S@.BL_MOVED = @
 UNION ALL SELECT T@.* FROM WRH$_SQLSTAT_BL T@, WRM$_SNAPSHOT S@
         3

8 rows selected.

Also then run this script:

SQL> select * from v$sgastat;
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
             fixed_sga                      781168
             buffer_cache                352321536
             log_buffer                     262144
shared pool  KQR L SO                        56320
shared pool  KQR M PO                       525976
shared pool  KQR M SO                        95860
shared pool  KQR S PO                        46120
shared pool  KQR S SO                         4352
shared pool  KTI-UNDO                      2058840
shared pool  sessions                      1284644
shared pool  sql area                      1068540
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  KGLS heap                      368568
shared pool  joxs heap                        4220
shared pool  row cache                     3707272
shared pool  parameters                       2192
shared pool  repository                      30812
shared pool  ASH buffers                   2097152
shared pool  free memory                 115151972
shared pool  PL/SQL DIANA                   855988
shared pool  FileOpenBlock                 1213284
shared pool  KSPD key heap                    1800
shared pool  PL/SQL MPCODE                  651712
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  library cache               112301804
shared pool  miscellaneous                18154780
shared pool  pl/sql source                    7364
shared pool  PLS non-lib hp                  11232
shared pool  alert threshol                   1456
shared pool  joxlod exec hp                 320052
shared pool  partitioning d                   9496
shared pool  table definiti                    548
shared pool  temporary tabl                    500
shared pool  type object de                  70792
shared pool  private strands               1996800
 
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  event statistics per sess     6334720
shared pool  fixed allocation callback         288
large pool   free memory                   4194304
java pool    joxs heap                      229824
java pool    free memory                  99398272
java pool    joxlod exec hp                5229504

Tom Kyte
August 08, 2005 - 7:51 am UTC

is this from your real database after it has been running for a while

shared pool problem with bind variables (5)

Mehdi Torki, August 09, 2005 - 12:25 am UTC

yes! it is for our real database while our application inserted 615,000 records after oracle server startup and continue that work.

shared pool problem with bind variables (6)

Mehdi Torki, August 09, 2005 - 1:06 am UTC

I run "select * from v$sgastat;" after insertion of 250,000 more records and I saw:

shared pool library cache 168,855,656

and library cache size is increasing very fastly yet!

Tom Kyte
August 09, 2005 - 9:53 am UTC

I removed that really big, huge, report you had below here that simply showed "the stuff is executing just fine, no reloads to speak of"

monitor the growth of the library cache and the decrease of the free memory in the shared pool during your load, we would not expect the library cache to keep increasing.

shared pool problem with bind variables (6)

Mehdi Torki, August 13, 2005 - 3:06 am UTC 

Now, after insertion of 800,000 more records (during 96 hours) and I saw:

shared pool    library cache     316,988,096
shared pool    free memory        18,215,928

Automatic Shared Memory Management is Enabled and Shared pool size is 388M now. Insertion rate decreased from 14000 (records per hour) to 7600. Also:

QL> select namespace, trunc(gethitratio * 100) "Hit Ratio", trunc(pinhitratio * 100) "Pin Hit Ratio", reloads "reloads" from v$librarycache;
 
NAMESPACE        Hit Ratio Pin Hit Ratio    reloads
--------------- ---------- ------------- ----------
SQL AREA                89            98     152249
TABLE/PROCEDURE         77            98     148261
BODY                    97            99       8644
...

SQL> select owner || '.' || name AS "NAME", type, loads, sharable_mem from v$db_object_cache where type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER') and loads > 10 ORDER BY loads DESC;
 
NAME
----------------------------------------------------
TYPE                              LOADS SHARABLE_MEM
---------------------------- ---------- ------------
SYS.DBMS_STANDARD
PACKAGE                            1420        25493
 
SYS.DBMS_APPLICATION_INFO
PACKAGE                            1244        12949
 
SYSMAN.EM_PING
PACKAGE                             993        35770
 
SYS.STANDARD
PACKAGE                             914       451000

...

SQL>  select owner || '.' || name AS "NAME", type, loads, sharable_mem from v$db_object_cache where loads > 2000 order by loads desc;

NAME
----------------------------------------------------
TYPE                              LOADS SHARABLE_MEM
---------------------------- ---------- ------------
SYS.TRIGGER$
TABLE                              2375         1448
 
SYSMAN.MGMT_TARGETS
TABLE                              2158         2179
 
SYS.COLTYPE$
TABLE                              2124         1448

...

And In Top SQL section of EM, I found below details:

SQL Text: BEGIN ORDPLUGINS.ORDX_file_SOURCE.importFrom(:1, :2, :3, :4, :5, 
    :6, :7); END;   
---------------------------------
               Executions: 5867 
              Parse Calls: 5868 
       Rows Per Execution: 3.0
Executions Per Parse Call: 1.0 

Shareable Memory (bytes):  21322 
                   Loads:  286 

Elapsed Time Per Execution (seconds):  0.56 
    CPU Time per Execution (seconds):  0.48 
                          Wait Ratio:  0.14

Tom Kyte
August 13, 2005 - 9:58 am UTC

monitoring growth requires at least 3 data points and preferably more. I don't know why you keep showing the other stuff, we just want to see "does memory keep increasing"


a) measure memory usage
b) load 10000 things
c) goto a, do this say 100 times

now, is the amount of memory allocated in the shared pool growing, if so, how.

And then, contact support, you'll have the supporting information you need to show them "we have a problem"

shared pool problem with bind variables (7)

Mehdi Torki, August 14, 2005 - 2:33 am UTC

Tom,
thanks for your guides.

How can I clear library cache without restarting Oracle server?
Additionally I want to see contents of library cache with them's associated memory size. Please guide me!

Tom Kyte
August 14, 2005 - 9:32 am UTC

it depends on what kind of memory this is, it may well not be "flushable"

there is the alter system flush shared_pool command.

You'll want to work with support to go further if you've done what I've asked and measure a constant increase in library cache usage over time with this single thing going.

shared pool

anand, November 21, 2005 - 3:14 am UTC

Its great solution to solve my problem ...
i want to know how to add ALTER SYSTEM FLUSH SHARED_POOL in CRON for flush every 5 min

Tom Kyte
November 21, 2005 - 8:41 am UTC

no you don't!

man, that would be killer.

ratio

A reader, November 21, 2005 - 5:49 am UTC


Tom Kyte
November 21, 2005 - 8:50 am UTC

proportion?

determining the source of shared pool growth

Jeff, May 18, 2006 - 9:10 am UTC 

Tom,

We have been experiencing a steady growth in shared pool usage of our production 9.2 database.  It grows to about 95%, then an OEM event kicks off, flushes the shared pool down to about 20%, then the growth starts all over again.  (another dba has set this up and swears that with out the flush, we end up with tons of memory errors and everything screeches to a halt)
It takes about 2-3 hours to hit the 95% threshold again.

Anyway, querying v$db_object_cache led me to believe that lack of binds was the culprit.

SQL> select * from (
  2  select owner,type,substr(name,1,40),count(*) from v$db_object_cache
  3  group by owner,type,substr(name,1,40)
  4  order by 4 desc)
  5  where rownum<11;

OWNER                                                            TYPE
---------------------------------------------------------------- ----------------------------
SUBSTR(NAME,1,40)
-------------------------------------------------------------------------------------------------------------
  COUNT(*)
----------
                                                                 CURSOR
select personcstmdatatxt from personcstm
     25110

                                                                 NOT LOADED
SELECT DATASOURCEID, CLIENTCONTEXTID, FU
      2901

                                                                 CURSOR
SELECT   /*+ORDERED USE_NL(sh PK_SHIFT)
      2544

                                                                 CURSOR
SELECT PERSONID  FROM PERSONCSTMDATA
      1721

                                                                 CURSOR
SELECT CUSTOMDATA  FROM VP_PERSONCUSTDAT
      1720

                                                                 CURSOR
SELECT FULLNM  FROM PERSON  WHERE PERSON
      1720

                                                                 CURSOR
SELECT WFCEXCEPTION.WFCEXCEPTIONID, WFCE
      1479

                                                                 NOT LOADED
SELECT CUSTOMDATA  FROM VP_PERSONCUSTDAT
       826

                                                                 NOT LOADED
SELECT FULLNM  FROM PERSON  WHERE PERSON
       826

                                                                 NOT LOADED
SELECT PERSONID  FROM PERSONCSTMDATA
       826


10 rows selected.


SQL> select name,count(*) from v$db_object_cache
  2  where name like 'select personcstmdatatxt from personcstmdata   where personid = :"SYS_B_0" and customdatadefid = :"SYS_B_1"%'
  3  group by name;

NAME
-------------------------------------------------------------------------------------------------------------
  COUNT(*)
----------
select personcstmdatatxt from personcstmdata   where personid = :"SYS_B_0" and customdatadefid = :"SYS_B_1"
     25151

Why are there over 25000 copies of the same exact statement in the shared pool?
I thought it would be because lack of binds, but as you can see, the statement is using the system generated binds (we have cursor_sharing=similar set)
What am I missing?
Should I be looking elsewhere to find the source of the shared pool growth?

Thanks.

Tom Kyte
May 19, 2006 - 9:42 am UTC 

you likely have histograms on personid or customdatadefid and are using cursor_sharing similar.

The optimizer is looking at one of those fields and saying "we might generate different plans, you are using similar, so we shall create a child cursor for each unique bind inputs".  That is exactly what similar is supposed to do.

That you have to use cursor sharing = similar tells me one fact - you have a serious bug - both performance and SECURITY (google sql injection) in your applicaiton, a serious one.  The developers should be working on nothing but fixing this.


If you run this script:

drop table t;

create table t
as
select rownum id1, case when mod(rownum,100) <= 80 then 0 else mod(rownum,100) end id2,
       all_objects.*
  from all_objects
/

select id2, count(*) from t group by id2 order by id2;

exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for columns id2 size 254' );

alter system flush shared_pool;
alter session set cursor_sharing=similar;
set termout off
select * from t /* look for me */ where id1 = 1;
select * from t /* look for me */ where id1 = 2;
select * from t /* look for me */ where id1 = 3;
select * from t /* look for me */ where id2 = 1;
select * from t /* look for me */ where id2 = 2;
select * from t /* look for me */ where id2 = 3;
select * from t /* look for me */ where id1 = 1 and id2 = 1;
select * from t /* look for me */ where id1 = 2 and id2 = 1;
select * from t /* look for me */ where id1 = 3 and id2 = 1;
set termout on
select sql_text from v$sql where sql_text like 'select%/* look for me */%';
set termout off
select * from t /* look for me */ where id1 = 1 and id2 = 2;
set termout on
select sql_text from v$sql where sql_text like 'select%/* look for me */%';


You'll see this sort of output:

ops$tkyte@ORA10GR2> drop table t;

Table dropped.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t
  2  as
  3  select rownum id1, case when mod(rownum,100) <= 80 then 0 else mod(rownum,100) end id2,
  4         all_objects.*
  5    from all_objects
  6  /

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select id2, count(*) from t group by id2 order by id2;

       ID2   COUNT(*)
---------- ----------
         0      40538
        81        500
        82        500
        83        500
        84        500
        85        500
        86        500
        87        500
        88        500
        89        500
        90        500
        91        500
        92        500
        93        500
        94        500
        95        500
        96        500
        97        500
        98        500
        99        500

20 rows selected.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for columns id2 size 254' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte@ORA10GR2> alter session set cursor_sharing=similar;

Session altered.

ops$tkyte@ORA10GR2> set termout off
ops$tkyte@ORA10GR2> select sql_text from v$sql where sql_text like 'select%/* look for me */%';

SQL_TEXT
-------------------------------------------------------------------------------
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id1 = :"SYS_B_0" and id2 = :"SYS_B_1"
select * from t /* look for me */ where id1 = :"SYS_B_0"

<b>note how the queries where id2 vary created separate child cursors - whereas id1 did not - That is because the optimizer, having histograms on id2, says "we might be doing different plans, so we'll only share this cursor when id2 has the same bind input.

That is why there is only one copy where id1 and id2 were used, we kept id2 constant and varied id1.. However, after running that one additional id1/id2 query:</b>

ops$tkyte@ORA10GR2> set termout off
ops$tkyte@ORA10GR2> select sql_text from v$sql where sql_text like 'select%/* look for me */%';

SQL_TEXT
-------------------------------------------------------------------------------
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id2 = :"SYS_B_0"
select * from t /* look for me */ where id1 = :"SYS_B_0" and id2 = :"SYS_B_1"
select * from t /* look for me */ where id1 = :"SYS_B_0" and id2 = :"SYS_B_1"
select * from t /* look for me */ where id1 = :"SYS_B_0"

6 rows selected.

<b>we get two of them</b>

So, this is what you want to do in order:

a) get the schedule laid out to FIX THE BIND PROBLEM AT THE SOURCE
b) seriously, concentrate on a)
c) do a)

d) remove the histograms or use cursor sharing = force, which doesn't do what similar does.

Shared Pool filled fast with Oracle 11i

keyur, July 11, 2006 - 11:46 am UTC

Hello Tom:

Thanks for all your help to everybody.

We have just upgrade Oracle Applicaiton to Oracle 11i Applications. After that we are running into problem with Shared pool.

My Shared pool is as bellow

shared pool - 912MB ( I would like to say it used to be around 700 MB , then we increase it to 800 and now it is 912 mb, still it fills up)

We are flushing it every 30 minutes, becuase withing 30 minutes,It drops to around 60 MB free Memory.

Our Cursor_sharing parameter is set to EXACT

I would like to give you my statspack report as follow :

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 191 11-Jul-06 11:02:08 678 64.5
End Snap: 201 11-Jul-06 11:31:44 622 62.1
Elapsed: 29.60 (mins)


Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,008M Std Block Size: 8K
Shared Pool Size: 800M Log Buffer: 10,240K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 121,997.06 10,815.49
Logical reads: 484,261.11 42,931.55
Block changes: 784.82 69.58
Physical reads: 2,408.13 213.49
Physical writes: 88.90 7.88
User calls: 597.23 52.95
Parses: 176.09 15.61
Hard parses: 10.31 0.91
Sorts: 98.82 8.76
Logons: 0.88 0.08
Executes: 849.23 75.29
Transactions: 11.28
% Blocks changed per Read: 0.16 Recursive Call %: 81.41
Rollback per transaction %: 5.26 Rows per Sort: 17.72


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.51 In-memory Sort %: 100.00
Library Hit %: 98.75 Soft Parse %: 94.15
Execute to Parse %: 79.27 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 42.84 % Non-Parse CPU: 99.36


Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.67 92.83
% SQL with executions>1: 78.82 68.97
% Memory for SQL w/exec>1: 77.76 72.61
----------------------------------------------------------
LSQL ordered by Parse Calls for DB: FA1DB Instance: FA1DB Snaps: 191 -201
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
11,845 11,845 3.79 1307778841
select condition from cdef$ where rowid=:1

5,480 5,482 1.75 3468666020
select text from view$ where rowid=:1

3,623 3,623 1.16 2266133025
Module: RGRARG
SELECT child_flex_value_low, child_flex_value_high,
nvl(structured_hierarchy_level,'-1') from fnd_flex_valu
e_hierarchies fvh, fnd_flex_values fv where fv.flex_value_set_
id = :v_set_id and fv.flex_value_set_id = fvh.flex_value_set
_id and fv.flex_value = :p_value and fvh.parent_flex_val

2,380 2,380 0.76 803659160
Module: POXRQERQ
begin FND_FLEX_WORKFLOW_APIS.START_GENERATION (:v1, :v2, :v3, :v
4, :v5); end;

2,314 2,314 0.74 453101415
Module: POXRQERQ
begin FND_FLEX_WORKFLOW_APIS.END_GENERATION (:v1, :v2, :v3, :v4,
:v5); end;

2,292 2,292 0.73 1867339551
Module: RGRARG
SELECT nvl(sum(rows_this_value),0) FROM GL_SEGMENT_FREQUENCIES
WHERE SEGMENT_NAME = 'SEGMENT4' AND ( (segment_value >= '000' A
ND segment_value <= '999') )

2,290 2,291 0.73 21081923
Module: RGRARG
SELECT nvl(sum(rows_this_value),0) FROM GL_SEGMENT_FREQUENCIES

-> End Sharable Memory Threshold: 1048576

Sharable Mem (b) Executions % Total Hash Value
---------------- ------------ ------- ------------
4,584,547 1 0.5 1748159938
Module: RCVDLPDT
SELECT rrp . item_id , rrp . organization_id rrp_organization_id
, MSI.SEGMENT1 C_FLEX_ITEM , MCA.SEGMENT1||' '||MCA.SEGMENT2
C_FLEX_CAT , rrp . revision_num , rrp . category_id , decode ( m
si . segment1 , null , rrp . item_description , msi . segment1 |
| '-' || rrp . item_description ) item_description , rrp . un_nu

2,183,700 3 0.2 2981817383
Module: RCVRCVRC
select * from RCV_VRC_TXS_VENDINT_V where shipment_header_id = :
1 order by transaction_id

1,534,766 9 0.2 391693727
Module: ARXTWMAI
SELECT ROW_ID,APBA_INACTIVE_DATE,DEFAULT_TAX_EXEMPT_FLAG,BS_BATC
H_SOURCE_NAME,TRX_NUMBER,RA_BILLING_NUMBER,SOA_AGREEMENT_NAME,RA
B_BATCH_NAME,RAC_BILL_TO_CUSTOMER_NAME,RAC_BILL_TO_CUSTOMER_NUM,
RAA_BILL_TO_CONCAT_ADDRESS,RAA_BILL_TO_ADDRESS1,RAA_BILL_TO_ADDR
ESS2,RAA_BILL_TO_ADDRESS3,RAA_BILL_TO_ADDRESS3_DB,RAA_BILL_TO_CI

1,426,880 25 0.1 3214151870
Module: FNDRSRUN
INSERT INTO FND_CONCURRENT_REQUESTS ( REQUEST_ID,SECURITY_GROUP_
ID,PHASE_CODE,STATUS_CODE,PRIORITY,PARENT_REQUEST_ID,PRIORITY_RE
QUEST_ID,DESCRIPTION,REQ_INFORMATION,IS_SUB_REQUEST,HAS_SUB_REQU
EST,UPDATE_PROTECTED,HOLD_FLAG,ENFORCE_SERIALITY_FLAG,SINGLE_THR
EAD_FLAG,ARGUMENT_INPUT_METHOD_CODE,IMPLICIT_CODE,REQUEST_DATE,R

1,416,967 3 0.1 2363270862
SELECT decode(psp.manual_req_num_type,'NUMERIC',null,prh.segment
1) , decode(psp.manual_req_num_type,'NUMERIC',to_number(p
rh.segment1), null) , prh.segment1 Requisition
, psp.manual_req_num_type req_num_type , ppf.f
ull_name Preparer , prh.creation_date C
=======================================================

I have also created one of your script to populate the table with similiar kind of statements and replacing from following site.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580 <code>

Output from this query is as below:

SELECT /*+ ORDERED INDEX(GL_CODE_COMBINATIONS GL_CODE_COMBINATIONS_N@) INDEX(GL_BALANCES GL_BALANCES_N@) */ NVL(BAL.PERIOD_TYPE, '#'), NVL(BAL.PERIOD_YEAR, -@), BAL.PERIOD_NAME, NVL(BAL.PERIOD_NUM, -@), NVL(BAL.PERIOD_NUM, -@), BAL.ACTUAL_FLAG, DECODE(CC.TEMPLATE_ID, NULL, '#', '#'), -@, NVL(BAL.BUDGET_VERSION_ID, -@), -@, NVL(BAL.ENCUMBRANCE_TYPE_ID, -@), BAL.CURRENCY_CODE, BAL.SET_OF_BOOKS_ID, NVL(BAL.TRANSLATED_FLAG, '#'), NVL(BAL.PERIOD_NET_DR, @) - NVL(BAL.PERIOD_NET_CR, @), NVL(BAL.PERIOD_NET_DR, @), NVL(BAL.PERIOD_NET_CR, @), NVL(BAL.QUARTER_TO_DATE_DR,@)-NVL(BAL.QUARTER_TO_DATE_CR, @), NVL(BAL.QUARTER_TO_DATE_DR, @), NVL(BAL.QUARTER_TO_DATE_CR, @), NVL(BAL.BEGIN_BALANCE_DR, @) - NVL(BAL.BEGIN_BALANCE_CR, @), NVL(BAL.BEGIN_BALANCE_DR, @), NVL(BAL.BEGIN_BALANCE_CR, @), NVL(BAL.PROJECT_TO_DATE_DR, @) - NVL(BAL.PROJECT_TO_DATE_CR, @), NVL(BAL.PROJECT_TO_DATE_DR, @), NVL(BAL.PROJECT_TO_DATE_CR, @) , NVL(SEGMENT@,'#'), NVL(SEGMENT@,'#'), NVL(SEGMENT@,'#'), |113
SAVEPOINT ORACLE_SVPT_@|119
SELECT NVL(SUM(ROWS_THIS_VALUE),@) FROM GL_SEGMENT_FREQUENCIES WHERE SEGMENT_NAME = '#' AND ( (SEGMENT_VALUE = '#') ) |250
INSERT INTO SYCLOSMARTREMOTELABTRANS (LABTRANSID, LABORCODE, LASTUPDATE, ACTION, SERVERID) VALUES ('#', '#', TO_DATE('#', '#'), NULL, '#')|307
BEGIN FND_PROFILE.PUT('#','#'); END;|342
SELECT /*+ ORDERED INDEX(GL_BALANCES GL_BALANCES_N@) INDEX(GL_CODE_COMBINATIONS GL_CODE_COMBINATIONS_U@) */ NVL(BAL.PERIOD_TYPE, '#'), NVL(BAL.PERIOD_YEAR, -@), BAL.PERIOD_NAME, NVL(BAL.PERIOD_NUM, -@), NVL(BAL.PERIOD_NUM, -@), BAL.ACTUAL_FLAG, DECODE(CC.TEMPLATE_ID, NULL, '#', '#'), -@, NVL(BAL.BUDGET_VERSION_ID, -@), -@, NVL(BAL.ENCUMBRANCE_TYPE_ID, -@), BAL.CURRENCY_CODE, BAL.SET_OF_BOOKS_ID, NVL(BAL.TRANSLATED_FLAG, '#'), NVL(BAL.PERIOD_NET_DR, @) - NVL(BAL.PERIOD_NET_CR, @), NVL(BAL.PERIOD_NET_DR, @), NVL(BAL.PERIOD_NET_CR, @), NVL(BAL.QUARTER_TO_DATE_DR,@)-NVL(BAL.QUARTER_TO_DATE_CR, @), NVL(BAL.QUARTER_TO_DATE_DR, @), NVL(BAL.QUARTER_TO_DATE_CR, @), NVL(BAL.BEGIN_BALANCE_DR, @) - NVL(BAL.BEGIN_BALANCE_CR, @), NVL(BAL.BEGIN_BALANCE_DR, @), NVL(BAL.BEGIN_BALANCE_CR, @), NVL(BAL.PROJECT_TO_DATE_DR, @) - NVL(BAL.PROJECT_TO_DATE_CR, @), NVL(BAL.PROJECT_TO_DATE_DR, @), NVL(BAL.PROJECT_TO_DATE_CR, @) , NVL(SEGMENT@,'#'), NVL(SEGMENT@,'#'), NVL(SEGMENT@,'#'), |367


Could you please give some hints what else I need to look at and why shared pool is filled up so fast?

I have also one question, If we dont flush and we have only let's say 30 MB and new statement come with the size of 50 mb how Oracle Handles it.

Thank you
~Keyur


Tom Kyte
July 12, 2006 - 3:06 pm UTC

don't flush it, it is a cache, it is meant to be full and when full will age out to make room for other stuff.

the shared pool is a cache like the block buffer cache is. What if the block buffer cache has 30mb free but you run a query that needs 100mb? Cache empties out stuff to make room.

Just stop flushing it, no reason to do that.

v$sgastat and v$librarycache

Anurag, November 03, 2006 - 11:36 am UTC

Sorry if this is off-topic... its confusing that in 9i, v$sgastat has two "pools" - "library cache" and "sql area"; and v$librarycache, which I 'assumed' was a drill-down of the "library cache" pool, also has a "sql area" namespace. Does v$librarycache correspond to the entire shared pool?

I've also seen the "data dictionary cache" being referred to as one of the components of the shared pool. Is this in fact the "library cache" or something else?

Reason I'm asking is because I need a way (v$ table etc) to find out why v$sgastat's "library cache" pool memory keeps on increasing. We have opened a tar on this, but so far their advice has been to "increase the large pool size" which did not help one bit. We use dedicated servers only.

Tom Kyte
November 03, 2006 - 12:16 pm UTC

please utilize support for this - you've started there, it'll only be really confusing to attack it from multiple points. They've got the tools to measure who is using which bit of what.

BI tools (Siebel) and shared pool problems

Jarek, November 04, 2006 - 6:29 am UTC

Hi Tom,

Your advice is always welcome. Thanks for helping all of us solve our problems.

I would like to know what do you think about BI tools e.g. Siebel. You always encourage us to use bind variables, but you admit that they are not always appropriate.

We are using Siebel as a reporting tool for our uses and it doesn't use bind variables at all! Therefore from time to time (~every two weeks) we are facing ora-4031 error. I have changed cursor_sharing=FORCE to alleviate the situation, but the problem still exists. My shared pool is ~1.8 GB already. Any idea what else I can do to solve the issue?

Regards,
jarek

Tom Kyte
November 04, 2006 - 12:27 pm UTC

perhaps it is because your shared pool is 1.8 gig - but you don't have very much reusable SQL (eg: bigger is not always better)



BI tools (Siebel) and shared pool problems 2

Jarek, November 06, 2006 - 7:48 am UTC

Hi,

Thanks for your answer, but I wrote in my question that Siebel doesn't use bind variables therefore there is almost no reusable SQL. To alleviate the situation we have cursor_sharing=FORCE. What else can we do to solve the problem?

Regards,
jarek

Tom Kyte
November 06, 2006 - 10:18 am UTC

IF setting cursor_sharing to force does not reduce the number of sql statements in your shared pool, THEN you do not have lots of reusable sql even if binds were used.

that is, you have lots of queries like:

select * from t where x = 5 and y = 10;
select * from t where x = 5 and z = 10;
select * from t where x = 5;
select * from t where y = 10;
select * from t where .......


that is, you don't have lots of reusable sql and in that case BIGGER IS NOT BETTER.

shared pool table definiti

Amao, November 28, 2006 - 8:52 pm UTC 

I want to know why shared pool table definiti of v$sgastat is so big?! I want to open iTAR for this problem. However,I appreciate your ideas before doing that.
By the way, my Oracle version is 9.2.0.6 on HPUX 11.23. We used the Siebel BI tools.
SQL> select * from v$sgastat;

POOL        NAME                            BYTES
----------- -------------------------- ----------
            fixed_sga                      741320
            buffer_cache                788529152
            log_buffer                    1312768
shared pool errors                          79920
shared pool subheap                        100000
shared pool KGK heap                        33304
shared pool KQR L PO                     25211544
shared pool KQR L SO                       760440
shared pool KQR M PO                     15099880
shared pool KQR M SO                       365304
shared pool KQR S SO                        23376
shared pool KQR X PO                       657208
shared pool sql area                   1377736960
shared pool KGLS heap                     4797384
shared pool OUTLNCACHE                      18504
shared pool PX subheap                     941096
shared pool parameters                      47976
shared pool free memory                 159135816
shared pool PL/SQL DIANA                  1271608
shared pool PL/SQL MPCODE                 2479528
shared pool library cache                34875048
shared pool miscellaneous               162267592
shared pool pl/sql source                   17208
shared pool MOLAP chunk he                 144752
shared pool MTTR advisory                  165832
shared pool XDB Schema Cac                5842328
shared pool joxlod: in ehe                 385336
shared pool joxs heap init                   4240
shared pool partitioning d               15317240
shared pool partitioning i                 179960
shared pool sim memory hea                 481568
shared pool table definiti             1.8447E+19
shared pool temporary tabl                   8048
shared pool trigger inform                   1552
shared pool trigger source                   7344
shared pool type object de                 279640
shared pool dictionary cache              3229952
shared pool fixed allocation callback         912
large pool  PX msg pool                  30670848
large pool  free memory                 103546880
java pool   free memory                  44490752
java pool   memory in use                 5840896

Tom Kyte
November 30, 2006 - 8:36 am UTC

open an itar please.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library