Does the same hold true when you are running in dedicated server mode? I was under the impression that large memory requests would try to allocate memory from the SHARED_POOL_RESERVE area when a chunk of memory was required above a certain threshold or some other algorithm. ( mimizing the number of ora-4031 errors )

Secondly I thought creating a large pool in dedicated server was a waste of memory as it is only used in MTS or when running RMAN in general.

Thanks in advance for clearing this up.

>>what large memory requests would be made?
That is a good question and one I have been asking myself recently. Our application has been running fine and now after 3 months we are receiving ora-4031 errors on the shared pool. (until we bounce the instance of course)

Free Memory is still high (20meg)therefore I believe this is related to a fragmentation problem. I queried the view V$SHARED_POOL_RESERVE and infact the failure count had been incremented but I cannot locate what is causing the problem.

Any ideas on how to resolve this issue? Our largest package is nowhere close to the amount of free memory available and as you have said before it wouldn't matter as the code is loaded in chunks anyway. My final guess as tp the cause of our problem would be the app created a rogue dynamic sql statement which could not be allocated to the pool.

Hi,
The response and the discussion thereafter has been very useful and added to my knowledge.

--prashant

are you saying we can pin packages in large pool?

>> Are you using bind variables?

Yes, in the majority of cases, but there are still some statements that could be modified to use bind variables. I am working with the developers on this.

>> what is the size of your shared pool?
120 megs (most code is in the app with a couple of stored packages) We are slowly starting to move the logic from the app to the database.

>>do you have an instance of the 4031 - it should have >>information in it pointing
>>to the failed statement. what are all of the last >>columns of v$shared pool
>>reserved?

The trace files always point to the same package but different statements.

We bounced the database so I don't have access to the v$shared_pool_reserved information anymore. I will keep it if/when we get it again.

We are planning an upgrade to 8.1.7.4 but if it is really due to fragmentation I don't think any upgrade will help.

Thanks.

Hi

I have some doubts because of Oracle 9.2 performance tuning documentation.

In Chapter 14 when it explains about shared pool it says this

"Allocation of memory from the shared pool is performed in chunks. This allows large objects (over 5k) to be loaded into the cache without requiring a single contiguous area, hence reducing the possibility of running out of enough
contiguous memory due to fragmentation."

So does this mean the possibilities of getting ORA-04031 have been dramatically reduced?

The following setence says

"Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5k. To allow these allocations to occur most efficiently, Oracle segregates a small amount of the shared pool. This memory is used if the shared pool does not have enough space. The segregated area of the shared pool is called the reserved pool."

Now this is for me the contradicting part. In the first place it says now memory is assigned in chunks so big objects does not requite contiguos space anymore, but secondly it says we still need the reserved pool if the shared pool hasnt got enough space, does LRU work here?

Does it mean if i needed a memory allocation say 6kb for my package, oracle would allocate from the shared pool reserved size based on shared_pool_reserved_size to avoid fragmentation. So, i would say it is important to configure shared_pool_reserved_size parameter even if i don't pin anything? am i right? Thanks.

Tom, If I configured shared_pool_reserved_size, will oracle use it? If so, when does oracle use this reserved space if I do not pin any package in the reserved pool?

Also, is it true that I can pin only packages not individual procedures if I chose to do?

Thanks.

I agree. Just for academic quriosity, if I used dbms_shared_pool.keep procedure to pin a package, for example, will that package remain in shared pool for ever until i bounce the instance whether or not I use that package? One of my application developers is asking me to pin sequence and STANDARD package using the above procedure everytime when we startup the instance? Can I do it or as you say, it is not necessary. Thanks.

In Orcale 9, setting SHARED_POOL_RESERVE_SIZE by some value the large statements uses this memory area. Like if I've a have big proc / package proc or func or plsql block then this is where the statements are stored. Is my understanding correct ?
If so, I set the value to some X and if my proc / func is bigger than this what happens ?

Hi Tom,
I have 8.1.7 on win2k.
If I hear you right than SQL's , packages and pinned packages irrespective of their sizes are not stored in shared reserved pool. As per you they get divided in 4K chunks and are stored in Shared pool and not in reserved shared pool.
1) Than why is that reserved shared pool ( in our case 10% of total shared pool ) required ? Should I remove it ?
2) You also mentioned that reserved pool is required for "big allocations".What are these "big allocations" who writes them, is this internal to oracle ?
3) Does the package size play role in 4031 error.
We have some packages around 5-10MB+ size, really huge.
Is it advisable to reduce package size.

Thanks,

Hi Tom,
Everybody say that we longer need to set shared_pool_reserved_size. Does information in all columns of v$shared_pool_reserved pertianing to shared_pool_reserved? For example, how many ORA-4031's occured, the last failure size, and so on.

If there are any ORA-4031's, did they occur because not having enough memory in shared_pool_reserved or rest of the shared pool?

Thanks.

Thanks Tom. Does it mean that if we see any ORA-4031's in v$shared_pool_reserved, we should concentrate on looking at reserved pool or change _reserved_pool_min_alloc?

Hi,
according to oracle documentation v$shared_pool_reserved holds valid information even if the reserved shared_pool is not defined.
also I know taht large pool is only for rman and shared servers , but you say that it replaces the reserved, if do, How can I control the object size that goes into each pool ,and How can I see it (which v$ view)

sorry to push this matter since we are expriancing ora-4031 in many of our 9i instances.
if I cannot control what goes into the large pool , why shouldnt I use the reserve pool which I have control on ?

a tar in the metalink (</code> http://www.metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=501761.995 <code> states that
The three main uses of the large pool in Oracle 8 are:

a. For the User Global Area (UGA) of sessions connected using MTS
(multi-threaded server)

b. Buffering for sequential file IO (Eg: as used by server managed
recovery/RMAN when there are multiple IO slaves)

Beginning in Oracle 8i, the large pool is also used for allocation of parallel
execution buffers if PARALLEL_AUTOMATIC_TUNING is TRUE.

It also states that LARGE_POOL_MIN_ALLOC can control the chunk size the gets into the large pool

so I'm getting confused,
a. should I be using large pool or reserved pool
b. can 9i shared pool become fragmented or not (since it's using 4k allocation unit a lot like LMT)
c. I ran the follwoing sql to try and determain the allocation size to get into the reserved
select round(SHARABLE_MEM/10000)*10000 "Mem_Range_n_bytes",
count(*) "Obj",sum(SHARABLE_MEM)/1024/1024 "Mem"
from v$db_object_cache
group by round(SHARABLE_MEM/10000)
order by 1 ;

Is that correct

regards,

sagi

Hi Tom,
Thnaks for the wonderful site..

I've question on SHARED_POOL_RESERVE_SIZE parameter..

We are using oracle 8.1.7.4 on solaris 2.8

When SHARED_POOL_RESERVE_SIZE is being used? Why we have this reserved memory.

Thanks

Hi Tom,
Thanks for the reply..
You mentioned
"it is generally "not relevant" in 8i and above (well, 8.0 really) " then why we have SHARED_POOL_RESERVE_SIZE in init.ora and currently value in our prod. database is

shared_pool_reserved_size string 10000000

We are using oracle 8.1.7.4 . Is this memory really reserved in shared pool above 8i??

Thanks

Hi Tom.
We are using oracle dedicated config. You told that it is resereved for "large allocation".. What do you mean by that? Is that used for pl/sql,sql statement etc ?

Thanks

Hi Tom,

I just want to know if SHARED_POOL_RESERVE_SIZE is not being used then why oracle is even reserving memory ? It's waste of memory..

Thanks

I made misake and made shared_pool_reserved_size too big.
I could not start database because of that. I get ORA-00093
OK. Istarted database with pfile, and tried to change shared_pool_reserved_size, but than get message ORA-02095
How can I change that parameter and start database with spfile?

My shared pool is set to 100M, shared pool reserved is 10M.

My environment has a lot of Cognos queries which are these gigantic SQLs, when I save one of these beasts to a text file, it is 255K (yes, one SELECT of 255K)

v$shared_pool_reserved.REQUEST_FAILURES is 96 within hours of starting the database. LAST_MISS_SIZE is 25360. MAX_MISS_SIZE is 266080.

Would this contribute to my shared pool getting trashed? This 255K wouldnt be subject to the 4K page/chunks you talk about, right?

Comments? Thanks

Um, yes? 96 failures would mean ORA-4031 was got by user sessions 96 times, right? Thats the definition of failure in this view?

Would that 255K SQL statement be considered a large allocation? What can be done to work around it? The default value for _shared_pool_reserved_min_alloc is 4K, isnt that too small?

Well, in my case most end users are shielded from direct Oracle errors since there is some app server or such in between which eats up the error and spits out its own error, but yes, 4031 is a big problem for us.

Large complex SQL (that one 255K SQL statement) will need 255K of contiguous memory? This would be considered a "large allocation" and would directly go to the reserved pool (bypassing the regular pool?). Also, are there 2 LRU lists, one for the reserved and one for the regular pool or is there only one?

On average, at any given time during the day, I have 300 sessions connected. Less than a dozen are active sessions.

"So, having a large reserved area could cause 4031's for small allocations under 4,000 bytes in your case"

Hm, not really. All the "failure" and "miss" sizes in v$shared_pool_reserved are all much larger than 4K, so that would mean these "large" requests are killing me, right?

In this case, would *increasing* the shared_pool_reserved_size help me? This would make all my complex large no-bind-variables-in-sight Cognos-generated SQL go in the reserved pool and all the "sane" SQL would go in the regular pool?

I just set it to 10% of the shared pool size because that is what most Metalink notes and books recommend?

[The max value for reserved is 50% of the regular pool, have you seen reason to actually set it this high?

Thanks

What's the best way to see what are the large objects currently in shared pool ? v$sql with order by one of memory column will do it ?

If I do this -

select * from v$sql
where parsing_user_id <> 0
order by sharable_mem;

The 1st object is 5k size (more than 4k min reserved default) that would mean pretty much ALL my requests are larger than 4k min default and qualify for reserved pool ? Would that mean I need larger reserved pool (say 40-50% instead of default 10%) or no reserved pool at all ?

Also, you said reserved pool is part of regular pool and not additional, correct ? If I have shared_pool_size=200M and shared_pool_reserved_size=40M, total shared pool is still 200M and not 240M ?

But

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

shows 268,435,600. Not sure why ?

Thanks. What about the 1st part about reserved pool ?

Repeated here from previous post -

What's the best way to see what are the large objects currently in shared pool ?
v$sql with order by one of memory column will do it ?

If I do this -

select * from v$sql
where parsing_user_id <> 0
order by sharable_mem;

The 1st object is 5k size (more than 4k min reserved default) that would mean
pretty much ALL my requests are larger than 4k min default and qualify for
reserved pool ? Would that mean I need larger reserved pool (say 40-50% instead
of default 10%) or no reserved pool at all ?

I got that, my question was more about

select * from v$sql
where parsing_user_id <> 0
order by sharable_mem;

The 1st object is 5k size (more than 4k min reserved default) that would mean ALL my requests are larger than 4k min default and qualify for reserved pool?

Would that mean I need larger reserved pool (say 40-50% instead of default 10%) or no reserved pool at all ?

Thanks

So where can I find a list of all the memory requests made by various SQLs? I thought v$sql.*_mem is it?

Note that I filtered out parsing_user_id=0 in my query, since I am not interested in the recursive sys-generated SQL, it is what it is.

1. If 'order by sharable_mem' shows me 5K as my lowest non-SYS SQL, why doesnt that mean that all my SQLs need at least 5K memory?

2. Also, max(sharable_mem) from v$sql shows me a number as high as 60M! What SQL could possibly need 60M of shared pool?

3. The memory doesnt seem to be related to the size of v$sql.sql_text itself?

Trivial statements like COMMIT and SELECT SYSDATE FROM DUAL have sharable_mem of 10K or so. Why is this?

Thanks

Are the columns LAST_MISS_SIZE and MAX_MISS_SIZE in v$SHARED_POOL_RESERVED additives too or actual allocation chunks of memory?

So,If I see a number > 4K in LAST_MISS_SIZE or MAX_MISS_SIZE of V$SHARED_POOL_RESERVED does it mean that there has been an attempt to allocate that much contiguous memory ?

I have the the following case.
Current shared_pool reserved size is 20 MB. and shared_pool_size is 350 MB.
select sum(sharable_mem) from v$sql where sharable_mem >500000
76040977
select sum(sharable_mem) from v$sql where sharable_mem <500000
184130908
select count(*) from v$sql where sharable_mem >500000;
77
select count(*) from v$sql where sharable_mem <500000;
3743
select * from v$SHARED_POOL_RESERVED;
4931128 6148.538653366583541147132169576059850374 219 133376 17003304 21201.127182044887780548628428927680798 583 489128 216442156 570222 21833648 29398112 0 0 1722281884894 0 0
I propose to raise the SHARED_POOL_RESERVED_SIZE from 20 MB to 100 MB without changing the SHARED_POOL_SIZE and
also set the _SHARED_POOL_RESERVED_MIN_ALLOC to 500000.
Would you approve this proposition? If not what will be your recomendation?

Because there are a number of sql statements as seen from statspack report and V$sql which have sharable mem > 10 MB. (I know you said earlier that sharable_mem in V$SQL is not the actual contuguous memory alloacations but later you answered me that LAST_MISS_SIZE from v$SHARED_POOL_RESERD_SIZE is actual contiguous allocation request). There is one statement which even uses 30 to 40 MB of sharable mem. Sometimes these statements show (in tools such as Precise)to be in shared pool wait. There is also a corresponding high CPU utilization when this happens.V$shared_pool_reserved shows high number(like 15 to 20 MB) for LAST_MISS_SIZE. My conclusion is that these high sharable_mem sqls are not often getting space in reserved pool. So my intention is to reduce the number of requests for reserved pool by raising _shared_pool_reserved_size_min_alloc and increase the size of shared_pool_reserved_size to make more memory avaliable for large allocations.
Let me specify that I am using 9.2.0.5.

No I do not get 4031's at all.
Sql's use bind variables well.
The 30 MB sharable_mem user SQL uses 60 union all.
Some Other large users of Sharable mem use IN list.
There is a dbms_utility.name_resolve sql I believe is caused by the way packages are called, although it is not implicitly used by any app.

The 30MB sharable memory could quite literally be composed of 10,000 chunks of 20 bytes to 4KB each if it's from a SQL statement with a 60-part UNION ALL. So increasing the reserved pool min alloc would not solve your problem. In fact, it might make things worse, as more 'not very large' chunk requests would be fired at the main pool rather than the reserved pool, causing more competition for the space and more pressure on the latches.

To get some idea of how many chunks of what size are going into that 30MB you could try a query like:

select
alloc_class, function, chunk_com, chunk_size, count(*)
from v$sql_shared_memory
where hash_value = {hash value of UNION ALL query}
group by
alloc_class, function, chunk_com, chunk_size
;

As Tom says, high CPU usage could come from the 60 separate optimizations that have to take place; but it could also come from the extremely heavy demand for lots of chunks from the shared pool. The 'shared pool waits' from Precise are (I am guessing) their way of reporting 'shared pool latch waits' - and if you are waiting a lot on the shared pool latch, you are most likely spending a lot of CPU time spinning on the latch as well, and doing a lot of work as chunks are claimed from the library cache LRU lists and made free.

As far as v$shared_pool_reserved is concerned, check Metalink for bugs in the definition. You seem to have a large value for your shared_pool_size, and if you have multiple CPUs (I believe Tanel Poder once said the boundary was 4) then you have may multiple shared pool latches. If so, v$shared_pool_reserved will be showing you rubbish, and there is an alternative definition on metalink to correct the problem.

Thanks Tom and Jonathan for all your replies.
The last comment from Jonathan cleared my doubts and made me aware of the existence of V$SQL_SHARED_MEMORY. With this info I do not think I need a SHARED_POOL_RESERVE_SIZE more than the default size.

Thanks

I have a shared_pool_size of 500M, shared_pool_reserved_size of 50M. Using columns in v$shared_pool_reserved, I have 140million "requests", 276,000 "misses", last/max miss size is 40M. ZERO failures.

Failures refer to ORA-4031's. So I guess I dont have any 4031's so far.

What does "misses" refer to? Should keeping that close to 0 be a goal as well? If so, how to do that?

Thanks

Request misses: Number of times the reserved list did not have a free piece of memory to satisfy the request, and started flushing objects from the LRU list

OK but should keeping this close to 0 be a desirable goal? If this number grows, doesnt that just mean that the reserved pool is doing its job?

As long as REQUEST_FAILURES=0, everything is good, right?

Thanks

Hi Tom, would configuring a large pool in dedicated server scenario potentially reduce the number of request misses in the reserved pool?

The application is not using binding.

Tom,
Thank you for a very valuable site..

We are on Oracle 9.2.0.4 on HPUnix 11(i).
We are using MTS -
DISPATCHERS = "(pro=tcp)(dispatchers=3)"
shared_servers=5
max_shared_servers=100
sessions=700

*.large_pool_size=40M

The application is a ASP application on Windows 2000 (IIS 5)
The Client on the web server (IIS 5) is Oracle 8.1.7.0.0.
We receive ORA-04031 Unable to allocate 9216 bytes of shared memory ( "large pool ", unknown object, "hash-join subh", "Kllcqas:Kllsltba" ).

The app uses bind variable (checked in the v$sqlar - may or may not be 100% ).

Once a month or so, we get to this stage and I bounce the DB to clear the issues.

What other DB parameters may need to be looked into. How to identify the specific statement that may be causing memeory issues?

Thanks in advance

when using shared server, the session UGA (user global area, session memory) comes from the large pool.  You have a pretty small large pool - unless you have a very small handful of concurrent sessions.

So, if you query v$license:

ops$tkyte@ORA10GR2> select sessions_highwater from v$license;

SESSIONS_HIGHWATER
------------------
                81


after you've been running for a while, what do you see?  Is 40m sufficient for the amount of work you are doing. 

I am reading your side every day , thanks for such a good response.

My quesion is

We had oracle 8.1.7.4 two years ago and migrated to 9.2.1.2 and start gating ora -4031 erros then add more space in shared pool and it was fixed.
On weekend i upgrade on 9.2.0.7 and shared pool usage 98% and may be anytime now i will gate 4031 and database crash (same as last time) today is very busy and applcaiton is 24/7. Since i upgrade i added twice from 176M to 206 then 224 but still its showing 96% used. I can bear database crush today please hlep me .
We are not using mts and not define large pool , I am using 10% shared pool resved size

If your application is closing and opening new connections very frequently, by not using connection pooling, you may want to set shared_pool_reserved_size to a minimum of (in Oracle 9i and in dedicated server mode):


Maximum of (shared_pool_size*(5/100), "sessions" * 27224)

Thanks,
Sai
</code> http://sai-oracle.blogspot.com/ <code>

Hi Tom.

We are on Windows and running on Oracle 9205.
I did see the ORA-04031: unable to locate message on my alert log and had couple of people saying the system is slow. I ran couple of statspack reports and I can get the each areas if you need, to help me. But I didn't want to paste the whole report here.

I can't make any changes to the sql statements pointed out by the report as this is a packaged solution and I don't have the authorization make any changes in the client code.
So, I am going to see If I can tune my memory components instead, or at least start from there.

The report did say my PGA_AGGREGATE_TARGET is very low. It is set to 200M while Shared pool = 432M and db cache = 480M.

These are some queries i ran, outside of the reprot.

1). PGA Observations

column name format a40;
column value format a15;
column unit format a15;
select name,
       to_char(decode( unit,
                       'bytes', value/1024/1024,
                                value ),'999,999,999.9') value,
                       decode( unit, 'bytes', 'mbytes', unit ) unit
from v$pgastat;

NAME                                     VALUE           UNIT
---------------------------------------- --------------- ---------------
aggregate PGA target parameter                    190.7  mbytes
aggregate PGA auto target                          11.9  mbytes
global memory bound                                 9.5  mbytes
total PGA inuse                                   308.4  mbytes
total PGA allocated                               424.9  mbytes
maximum PGA allocated                             445.0  mbytes
total freeable PGA memory                            .0  mbytes
PGA memory freed back to OS                          .0  mbytes
total PGA used for auto workareas                   5.6  mbytes
maximum PGA used for auto workareas                70.3  mbytes
total PGA used for manual workareas                  .0  mbytes
maximum PGA used for manual workareas                .3  mbytes
over allocation count                          49,746.0  
bytes processed                                85,942.7  mbytes
extra bytes read/written                       17,755.9  mbytes
cache hit percentage                               82.9  percent


column pga_target_for_estimate format a10
column pga_target_factor format a10
column estd_pga_cache_hit_percentage format a10
column estd_overalloc_count format a10
select trunc(pga_target_for_estimate / 1024 / 1024) pga_target_for_estimate,
       to_char(pga_target_factor * 100, '999.9') || '%' pga_target_factor,
       trunc(bytes_processed / 1024 / 1024) bytes_processed,
       trunc(estd_extra_bytes_rw / 1024 / 1024) estd_extra_bytes_rw,
       to_char(estd_pga_cache_hit_percentage, '999') || '%' estd_pga_cache_hit_percentage,
       estd_overalloc_count
  from v$pga_target_advice;

PGA_TARGET PGA_TARGET BYTES_PROCESSED ESTD_EXTRA_BYTES_RW ESTD_PGA_C ESTD_OVERA
---------- ---------- --------------- ------------------- ---------- ----------
        23   12.5%              85186               28298   75%            2163
        47   25.0%              85186               25863   77%             787
        95   50.0%              85186               21915   80%             723
       143   75.0%              85186               18220   82%             696
       190  100.0%              85186               13410   86%             615
       228  120.0%              85186               10917   89%             525
       267  140.0%              85186               10541   89%             466
       305  160.0%              85186               10328   89%             329
       343  180.0%              85186                9940   90%              15
       381  200.0%              85186                8817   91%               0
       572  300.0%              85186                3847   96%               0
       762  400.0%              85186                3750   96%               0
      1144  600.0%              85186                3419   96%               0
      1525  800.0%              85186                3353   96%               0

SELECT LOW_OPTIMAL_SIZE/1024 low_kb, (HIGH_OPTIMAL_SIZE+1)/1024 high_kb, 
       estd_optimal_executions estd_opt_cnt, 
       estd_onepass_executions estd_onepass_cnt, 
       estd_multipasses_executions estd_mpass_cnt 
  FROM v$pga_target_advice_histogram 
 WHERE pga_target_factor = 3
   AND estd_total_executions != 0 
 ORDER BY 1; 

    LOW_KB    HIGH_KB ESTD_OPT_CNT ESTD_ONEPASS_CNT ESTD_MPASS_CNT
---------- ---------- ------------ ---------------- --------------
        16         32      2101403                0              0
        32         64        16002                0              0
        64        128         7767                0              0
       128        256         1322                0              0
       256        512          745                0              0
       512       1024        58683                0              0
      1024       2048          317                0              0
      2048       4096          184                0              0
      4096       8192          183                0              0
      8192      16384           48                0              0
     16384      32768           79                2              0
     32768      65536            6                8              0
     65536     131072            2                3              0
    131072     262144            0                1              0
    262144     524288            4                4              0
   1048576    2097152            0                1              0

My Observation is, Yes the PGA target is very low. I should increase it least to 381M. Now I don't have enough memory to do that, I am going to look at Shared Pool and db cache and pull some memory from those two or at least from shared pool.

2). Shared Pool Observation

select SHARED_POOL_SIZE_FOR_ESTIMATE, 
       SHARED_POOL_SIZE_FACTOR, 
       ESTD_LC_TIME_SAVED 
from v$shared_pool_advice;

SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_TIME_SAVED
----------------------------- ----------------------- ------------------
                          240                  0.5556             147596
                          288                  0.6667             147616
                          336                  0.7778             147622
                          384                  0.8889             147623
                          432                       1             147624
                          480                  1.1111             147624
                          528                  1.2222             147624
                          576                  1.3333             147624
                          624                  1.4444             147624
                          672                  1.5556             147624
                          720                  1.6667             147624
                          768                  1.7778             147624
                          816                  1.8889             147624
                          864                       2             147624

select * from (select name, bytes/(1024*1024) MB from v$sgastat where pool = 'shared pool' order by bytes desc) where rownum < 11;

NAME                               MB
-------------------------- ----------
sql area                   236.484539
library cache              71.0234107
PL/SQL MPCODE              34.1812667
free memory                27.2027702
miscellaneous              21.5663032
PL/SQL DIANA               13.0983505
KGLS heap                  12.1298675
KQR M PO                   10.7081832
errors                     5.84624862
event statistics per sess  4.75311279

My Observation, it looks like It shouldn't make any difference in ESTD_LC_TIME_SAVED even if I go back to 240M from 432M. But the second query result shows me that only 27M is free. Here, my gut feeling tells me go ahead and reduce the shared pool at least to 240M. But would it be a good solution?? what else should I check?? the SQL Area is full of sql statements that I can't change or rearrange.

3). db cache observation

column size format a10;
select size_for_estimate as "Cache Size", 
       buffers_for_estimate "Buffers", 
       estd_physical_read_factor "Read Factor", 
       estd_physical_reads "Phys Reads"
from v$db_cache_advice;

Cache Size    Buffers Read Factor Phys Reads
---------- ---------- ----------- ----------
        48       3036      5.8795  148528674
        96       6072      5.0803  128339569
       144       9108      4.3032  108708257
       192      12144      1.4481   36581299
       240      15180      1.2317   31116310
       288      18216      1.1532   29131451
       336      21252      1.1012   27819875
       384      24288      1.0618   26822138
       432      27324      1.0274   25954580
       480      30360           1   25262122
       528      33396      0.9756   24646360
       576      36432      0.9531   24078499
       624      39468      0.9331   23572406
       672      42504      0.9173   23172490
       720      45540      0.8977   22678654
       768      48576      0.8795   22218887
       816      51612      0.8613   21758059
       864      54648      0.8433   21302914
       912      57684      0.8268   20885878
       960      60720      0.8098   20457235

My Observation,
It looks like my optimal point is 240M. So, I could reduce this area also to free up some memory for PGA. I at least need 180M extra for the PGA and I think I should first get that much from the shared pool and then run for a day or two, look at the statspack and these statistics again and decide the future actions.

I really appreciate your inputs, comments on my observations and point me to where I think wrong or what else I should be looking at to make a decision.

Thanks Tom,

Kandy Train

Hi Tom,

But I don't think I am thinking the same line. Yes, the error is related to shared pool. The other fact is for some people's it is running slow. Again, I have the statspacks if you need specific areas.

My Shared Pool is bigger and I don't have more memory to assigned to it, because I get 4031. That's why I started looking at all the memory components to see what should be decreased and what should be increased.

I appreciate your inputs...

Thanks,

Kandy Train

Hi Tom,

I think I am more inclined to get the system running faster for those people who complain during the peak hours.

4031, I want to solve it too, but not the first one to touch. There are days I don't get any of these issues. There are some days I get both. But 4031 is rare compared to the other.

How do I find out if I bind or not??

Thanks again for your inputs.

Kandy train

Hi Tom,

What should I exactly look for in v$sql??

There are some sqls with zero executions and some with thousands of executions. Even if I see low(Let me interprit that as 0~5 executions) executions, what is it telling me??

And I could only look at v$sql when the system is slow for those people right??
Otherwise those sqls may not be there right??

I am clueless how to find the root cause, and appreciate your help. I have both your books and if you want to point me to specific pages, I can read them and come back.

Thanks,

Kandy Train

Have you tried the remove_constants process documented in other AskTom pages? Search on remove_constants.
I have a similar database running 9.2.0.7, high transaction, high volume, "real time" OLTP.
Over the years, the basic tools I've used are the remove_constants process, trace/tkprof, and statspack.
I find statspack extremely helpful in the "gee, the app is slow right now" types of problems.
Additionally, statspack will show you SQL wildly skewed from normal processing assuming you can respond while the problem is still ongoing. Then you can trace/tkprof to tune appropriately.
For ongoing reports, I use statspack daily over a long period (24 hours) to get a baseline for overall load profile, with a sampling of high usage, low usage times for comparison. With all of the information statpack provides, you can usually see the anomalies of problem performance jump out in the first page of the standard report.

Hi,

I would appreciate if some one can point me to remove_constants function. I searched for it, and looked at
all the links came up, in all those it is mentioned that they have used it, but no way to locate itself.

Thanks in advance,

Kandy Train

Hi,

I found the function.
Most of the SQLs I find are inside the packaged solution.
I can't do much about them. They are not in user_source, meaning they are not part of PL/SQL where I could make changes.

It seems like Other than going back to the provider and ask them to use binds in their client code, I guess I shouldn't do anything from Database side of it??

Not a good feeling when you can't do something when there is a problem...

Kandy Train

Hi,

I already have cursor_sharing= similar database wide.
This App is 85% and there are very minor web apps too.
So, I don't think it has any bad effects on other apps.

Thanks for the tip.

Kandy Train,

Hi,

I think in order for me to find the root cause, I am going to use v$session_wait and v$session_event togather with 10046 trace.

First, let me know if I am going in the right direction.
This is to look at those sessions where the system is slow. Because not every one is complaining out of 160 users(250 sessions). It's only few(3) users that complain and again that's when they do a specific function.

How can I rename the trace file generated for another session??

First I am not tracing my session. I am going to use

sys.dbms_support.start_trace_in_session(vSid, vSerialNo, TRUE, TRUE);

to trace those sessions. I know I can look for the latest file in udump directory.

I can use TRACEFILE_IDENTIFIER for my session. Is there a way to alter the name of the trace file that get generated while tracing from another session??

Logon & Logoff trigger would mislead me as they never log off for days and there will be a huge SQL*Net message from client value for these sessions.

I am trying trace when they kick off the specific functionality and stop tracing as soon as it is completed.

Thanks,

Kandy Train

Hello,
What queries consistitute the Shared Mem in STATSPACK? There are whole lot of queries in Shared Mem.

We are running 9.2.0.6 RAC, Win2k3, two nodes, load balancing, shared spfile, shared server set-up. We use bind variables for 80% of code and working with developers to make it 100% :-)

*.compatible='9.2.0.0.0'
*.db_cache_size=419430400
*.large_pool_size=314572800
*.pga_aggregate_target=419430400
*.shared_pool_reserved_size=41943040
*.shared_pool_size=419430400

We run an internet application that experiences ~700 concurrent users at peak usage. Statspack shows ~6 transactions per second at peak times.

Node 1 has SQL AREA GetHitRatio of 91% and PinHitRatio of 90%.
Node 2 has SQL AREA GetHitRatio of 69% and PinHitRatio of 94%.

Node 1 has zero Request Failures.
Node 2 has 227066 Request Failures (~2 weeks since last recycle of database). Last Failure Size of 2892176.

On node 2 only, we are starting to experiencing periodic ora-4031 errors such as:

ORA-00603: ORACLE server session terminated by fatal error ORA-04031: unable to allocate 4176 bytes of shared memory ("shared pool","REPORTS_SPECIAL_CLOB","sga heap(1,0)","library cache") ORA-04031: unable to allocate 4176 bytes of shared memory ("shared pool","REPORTS_SPECIAL_CLOB","sga heap(1,0)","library cache")

The only change in the environment was the addition of an additional Weblogic Server in the middle tier. The connection pools on the WLS are 25 minimum and 100 maximum, so will have added more sessions to the database.

1) Why would one node experience such problems when the other performs adequately?
2) Would increasing shared pool help?

Thank you in advance for your wisdom in this matter.

Joe

Hi Tom,
Very interesting thread this. I have problem with 04031 error.

SQL> select sum(bytes) from v$sgastat where pool='shared pool';

SUM(BYTES)
----------
167772160

SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 6710886
shared_pool_size big integer 134217728


SQL> show parameter large

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0


large_pool_size big integer 0
SQL> col free_space for 999,999,999,999 head "TOTAL FREE"
SQL> col avg_free_size for 999,999,999,999 head "AVERAGE|CHUNK SIZE"
SQL> col free_count for 999,999,999,999 head "COUNT"
SQL> col request_misses for 999,999,999,999 head "REQUEST|MISSES"
SQL> col request_failures for 999,999,999,999 head "REQUEST|FAILURES"
SQL> col max_free_size for 999,999,999,999 head "LARGEST CHUNK"
SQL>
SQL> select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures
2 from v$shared_pool_reserved;

AVERAGE REQUEST REQUEST
TOTAL FREE CHUNK SIZE COUNT LARGEST CHUNK MISSES FAILURES
--------- --------- -------- ------------- --------- ---------
4,258,696 28,582 33 471,576 73 68


From the alert log we are getting:-

ORA-04031: unable to allocate 270424 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KSFQ Buffers")
Wed Feb 13 16:03:45 2008
ksfqxcre: failure to allocate shared memory means sync I/O will be used whenever async I/O to file not supported natively
Wed Feb 13 16:04:07 2008
Errors in file /orasftware/app/orabuffs/admin/BUFP1/bdump/bufp1_j000_597.trc:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [], [], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [pvm.c:BREAK], [0], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [pvm.c:BREAK]


What we see from the reserved_size is that 471,576 is the largest chunk. So why it cannot allocate 270424 bytes of free memory?
Please can you derive something I cannot see or please can you explain the working of the shared_pool and shared_pool_reserved_size as per the above scenerio?
Please let me know if I need to post anything else missing from above.

Thanks lots as always Tom,
Regards,
Aru.


Also if it helps some outputs for queries I found on oracle metalink regarding ora-04031 but do not understand myself.

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS; 2 3

CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 33 4337296 128.35k
R-freea 120 943608 7.68k
R-perm 13 1435896 107.86k
free 2192 590984 .26k
freeabl 46386 114040192 2.40k
perm 448 37646672 82.06k
recr 10764 8776872 .80k

7 rows selected.

SQL> col sga_heap format a15
SQL> col size format a10
SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
2 decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
3 4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
4 '8-9k', 9,'9-10k','> 10K') "size",
5 count(*),ksmchcls Status, sum(ksmchsiz) Bytes
6 from x$ksmsp
7 where KSMCHCOM = 'free memory'
8 group by ksmchidx, ksmchcls,
9 'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
10 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
1 sga heap(1,0) free memory 0-1K 2076 free 218920
1 sga heap(1,0) free memory 1-2K 8 free 10576
1 sga heap(1,0) free memory 2-3K 46 free 90184
1 sga heap(1,0) free memory 3-4K 16 free 49752
1 sga heap(1,0) free memory 4-5K 39 free 158256
1 sga heap(1,0) free memory 8-9k 1 free 8368
1 sga heap(1,0) free memory 9-10k 1 free 9000
1 sga heap(1,0) free memory > 10K 1 free 21464
1 sga heap(1,0) free memory 1-2K 1 R-free 1264
1 sga heap(1,0) free memory 3-4K 1 R-free 3456
1 sga heap(1,0) free memory > 10K 31 R-free 4332576

11 rows selected.

Also I thought the chunks were 4KB each, but the above query does not really indicate that. Please help.

Hi Tom,
Above in this thread you wrote :-
'Yes, that is what it means -- plsql and such is loaded in about 4k pages it the shared pool and the 4k chunks need not be contigous. It is the same concept as locally managed tablespaces and why they cannot get fragmented. if everyone is using about 4k chuncks -- any chunk of free memory is as good as any other chunk.'

I understand that. But when I query

SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
2 decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
3 4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
4 '8-9k', 9,'9-10k','> 10K') "size",
5 count(*),ksmchcls Status, sum(ksmchsiz) Bytes
6 from x$ksmsp
7 where KSMCHCOM = 'free memory'
8 group by ksmchidx, ksmchcls,
9 'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
10 1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
11 '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) STATUS BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
1 sga heap(1,0) free memory 0-1K 2076 free 218920
1 sga heap(1,0) free memory 1-2K 8 free 10576
1 sga heap(1,0) free memory 2-3K 46 free 90184
1 sga heap(1,0) free memory 3-4K 16 free 49752
1 sga heap(1,0) free memory 4-5K 39 free 158256
1 sga heap(1,0) free memory 8-9k 1 free 8368
1 sga heap(1,0) free memory 9-10k 1 free 9000
1 sga heap(1,0) free memory > 10K 1 free 21464
1 sga heap(1,0) free memory 1-2K 1 R-free 1264
1 sga heap(1,0) free memory 3-4K 1 R-free 3456
1 sga heap(1,0) free memory > 10K 31 R-free 4332576

11 rows selected.

Here it shows that the chunks are of various sizes ranging from 0-1 K to more than 10K's.
Please can you help me clear this boubt in order to get a clearer understanding of how shared_pool allocation is done.
Thanks,
Aru.