So, are you saying that we need only to look at free memory in v$sgastat after a period of time, not monitor it constantly? i.e. free memory will only go down?

Many thanks.

sir,

In response you said shared_pool used size should stop increasing and keep monitoring v$sgastat..

What values should we monitor?

I got output like this..
POOL NAME BYTES
----------- -------------------------- ----------
fixed_sga 59020
db_block_buffers 176488448
log_buffer 20971520
shared pool free memory 3759272
shared pool miscellaneous 1055168
shared pool PLS non-lib hp 2136
shared pool transaction_branches 82176
shared pool table columns 20392
shared pool Checkpoint queue 164000
shared pool db_files 285768
shared pool PL/SQL MPCODE 947488

POOL NAME BYTES
----------- -------------------------- ----------
shared pool table definiti 14520
shared pool trigger defini 21240
shared pool KGK heap 26080
shared pool transactions 628056
shared pool SYSTEM PARAMETERS 80448
shared pool log_buffer 327680
shared pool state objects 172464
shared pool State objects 588136
shared pool db_block_buffers 13098752
shared pool KQLS heap 2090928
shared pool KGFF heap 112200

POOL NAME BYTES
----------- -------------------------- ----------
shared pool db_handles 270480
shared pool dictionary cache 3708536
shared pool DML locks 128000
shared pool messages 56000
shared pool PL/SQL DIANA 1420376
shared pool enqueue_resources 104000
shared pool library cache 25735448
shared pool ktlbk state objects 229944
shared pool sql area 39803808
shared pool processes 347200
shared pool sessions 973440

POOL NAME BYTES
----------- -------------------------- ----------
shared pool branches 123840
shared pool event statistics per sess 954720
shared pool fixed allocation callback 1120
shared pool db_block_hash_buckets 2069952


Thanks.
atul

I have the following values:

SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 25165824

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

SUM(BYTES)
----------
 268435456

SQL> select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool free memory                  65181796
***********                            ----------
sum                                      65181796

I'm glad I read your last comment on pg. 82 of your book that says there is no relationship between the outcome of the query from v$SGASTATS and the shared_pool_size init.ora parameter, because the values are so different in our case, 268MB to 25MB.  At first I thought the 25MB value of shared_pool_size seemed oftly small, but since there's 65MB(and let's say this is representative over a few day period) of free memory, then it's not too small??  Also, how is this free memory being calculated.  At first I thought it was part of the shared_pool_size init.ora parameter which is 25MB, but I guess not.
 

depends on the release.  Tell you what -- look for a time to submit a question and put this there.  would need to see the output of select * from v$sgastat, know the version of the database, the output of show sga

If this is 9i, there are these things called granules, sga_max_size settings and such that complicate the explanation.  I think what we are seeing here is you have a setting of SGA_MAX_SIZE that exceeds your real allocation -- consider:

ops$tkyte@ORA920> show sga;

Total System Global Area  537989600 bytes
Fixed Size                   451040 bytes
Variable Size             369098752 bytes
Database Buffers          167772160 bytes
Redo Buffers                 667648 bytes

ops$tkyte@ORA920> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 537989600

ops$tkyte@ORA920> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 67108864



See the disparity between the variable size and the shared pool size -- it is huge.  That is because the amount of memory I asked for in the sga_max_size is psuedo allocated to the variable size.  Depending in the OS it may or may not be allocated.  On shared memory systems (unixes), the SGA will dynammically grow and shrink by adding more shared memory chunks.  On poor old flat models, like windows, it is allocated -- but not paged in.  As it grows, we page in the memory we need, as it shrinks, the OS will eventually page it back out.

I believe you have a large sga_max_size and this accounts for the difference - check it out and check out the individual rows in v$sgastat.  


 

Tom,

I'm reading your expert one-on-one Oracle book and found an interesting remark on page 81. You talk about a system with a 1.1G SGA and a 1G Shared Pool, the DBAs had to shut down that instance every day because otherwise performance would drop due to the overhead of aging structures out of the pool.

Can you elaborate a little more on how objects are aged out of the shared pool? How would it help to have a smaller Shared Pool? Wouldn't objects be aged out the same way?

Many times i have to set up initial values for an SGA at the initial stages of a project, and up to now I believe i've been oversizing it.

My point is that i don't quite understand how an oversized Shared pool can hurt performance a lot.

Thanx

Tom,

" ...Then monitor its usage via the v$SGASTAT view over time to ensure you have right sized it."

Can you please give an example on how to monitor sga in oracle 9i releases

Thanks a lot

Yes tom, i did take the report. a part of it is here.

SGA Memory Summary for DB: GFWT3 Instance: gfwt3 Snaps: 19 -20

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 25,165,824
Fixed Size 453,492
Redo Buffers 667,648
Variable Size 109,051,904
----------------
sum 135,338,868
-------------------------------------------------------------


SGA breakdown difference for DB: GFWT3 Instance: gfwt3 Snaps: 19 -20

Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
java free memory 33,554,432 33,554,432 0.00
large free memory 8,388,608 8,388,608 0.00
shared 1M buffer 2,098,176 2,098,176 0.00
shared FileIdentificatonBlock 323,292 323,292 0.00
shared FileOpenBlock 695,504 695,504 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 2,073,736 2,442,648 17.79
shared KQR M PO 3,896,832 3,896,832 0.00
shared KQR M SO 103,996 103,996 0.00
shared KQR S PO 490,544 490,544 0.00
shared KQR S SO 5,632 5,632 0.00
shared KSXR pending messages que 841,036 841,036 0.00
shared KSXR receive buffers 1,033,000 1,033,000 0.00
shared MTTR advisory 11,776 11,776 0.00
shared PL/SQL DIANA 430,188 430,188 0.00
shared PL/SQL MPCODE 67,876 67,876 0.00
shared PLS non-lib hp 2,068 2,068 0.00
shared character set object 323,724 323,724 0.00
shared dictionary cache 1,610,880 1,610,880 0.00
shared event statistics per sess 1,718,360 1,718,360 0.00
shared fixed allocation callback 180 180 0.00
shared free memory 3,718,640 3,649,180 -1.87
shared joxs heap init 4,220 4,220 0.00
shared kgl simulator 1,677,364 1,706,704 1.75
shared library cache 8,724,680 8,690,280 -0.39
shared message pool freequeue 834,752 834,752 0.00
shared miscellaneous 6,261,336 6,303,480 0.67
shared parameters 1,852 1,852 0.00
shared sessions 410,720 410,720 0.00
shared sim memory hea 21,164 21,164 0.00
shared sql area 29,665,668 29,326,396 -1.14
shared subheap 46,580 46,580 0.00
shared table definiti 4,944 7,680 55.34
shared temporary tabl 4,220 4,220 0.00
shared trigger defini 476 476 0.00
shared trigger inform 1,240 1,240 0.00
shared trigger source 452 452 0.00
buffer_cache 25,165,824 25,165,824 0.00
fixed_sga 453,492 453,492 0.00
log_buffer 656,384 656,384 0.00
-------------------------------------------------------------

Please let me know, how interpret this - in the sense, which area is concern and which area is in trouble. what should % Diff indicate and what does negative values indicate. this snap was taken for 15 minutes


Hope i am not troubling you, thanks for your consideration

Can in anyway the SQL Area be configured for having more memory than allcated at present in the SGA. I mean that it has the memory allocated automatically from the SGA, but can we by any form allocate enough memory manually. If not, why? Also why is there free memory available in the SGA say around 50% which i feel is not being utilized and is a waste. But there are reloads in the Library Cache and i wonder why does it not allocate memory even though it has enough of it free. Or in case of other reloads also.

Maybe i wasn't that clear.
I wanted to know is it possible in any way to allocate more memory to SQL Area in the shared pool manually. Maybe by setting some parameters, i do not know of any such parameter. And as far i know Reloads in the SQL Area, Tables/Procedures or Trigger rows indicate that the shared pool may be too small. But most of the time there is plenty of free memory available to solve this problem. By querying the V$SGASTAT i get the information that the shared pool is around 40% free. Why does it not utilize this free memory or a part of it?
Could you suggest any more reasons for Reloads.
Thanks again. :)

POOL NAME BYTES
----------- -------------------------- ----------
.
.
shared pool sql area 100582372
shared pool free memory 167772160
.
.
.
.

Hi Tom,


1) I cannot increase the shared_pool_size. How I allocate more memory from the system.  My unix system currently has 8G of memory.  

SQL> alter system set SHARED_POOL_SIZE = 150M;
alter system set SHARED_POOL_SIZE = 150M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> show SGA;
Total System Global Area 320308312 bytes
Fixed Size 730200 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes


2)I need to increate the shared memory because I get the following errors during the replication processing in RAC.

(copy from the .trc file in udump)

Error while processing repcatlog request:
    -4031 - ORA-04031: unable to allocate 4200 bytes of shared memory ("shared p
ool","select cols,audit$,textlengt...","sga heap(1,0)","library cache")
*** 2004-07-14 10:16:00.721
Error while processing repcatlog request:
    -4031 - ORA-04031: unable to allocate 4200 bytes of shared memory ("shared p
ool","select order#,columns,types ...","sga heap(1,0)","library cache")
*** 2004-07-14 10:16:00.951
Error while processing repcatlog request:
    -4031 - ORA-04031: unable to allocate 4200 bytes of shared memory ("shared p
ool","select order#,columns,types ...","sga heap(1,0)","library cache")
*** 2004-07-14 10:16:01.188
Error while processing repcatlog request:
    -4031 - ORA-04031: unable to allocate 4200 bytes of shared memory ("shared p
ool","select order#,columns,types ...","sga heap(1,0)","library cache")
*** 2004-07-14 10:16:01.420

Thank you for your help.
 

Hi Tom,
You are saying that if your program is not using bind variable you would get ora-4031 error.

My question:
If you have many different sql statement in shared pool. Won't oracle flush shared pool(aged out based on LRU or some alogoritm) if program need memory in shared pool?

Please explain
Thanks

Hi Tom,
Thanks for the reply...
We are using siebel crm application and we've 200 concurrent user.Our shared pool size  is 130MB. Last week we got ora-4031 error. My first guess is we have not enough shared_pool size (130MB is too small for 200 users).. What do you think? 

below are stats from our prod. database..I just ran this  at 7:40 EST.. System usage is preety low as our peak hour usage is 9 to 5 pm.. Free shared pool is just 12MB
Please advice.
Thanks

shared_pool_reserved_size            string      10000000
shared_pool_size                     string      130000000

SQL> select * from v$sgastat where NAME = 'free memory';

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool free memory                  12040456
java pool   free memory                   9605120
 

Hi Tom,
Is there anyawy to reproduce ora-4031 ?

Thanks

Hi Tom I would appreciate your input regarding this topic. 

I have been asked to look at a systems configuration. 

sga_max_size         1008694368
shared_pool_size        268435456
large_pool_size         134217728
large_pool_size       134217728

SQL> show sga

Total System Global Area     1008694368 bytes
Fixed Size                  738400 bytes
Variable Size               637534208 bytes
Database Buffers             369098752 bytes
Redo Buffers                1323008 bytes    

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

301989888

SQL> select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';

109068784

Statspack Report

large  free memory                          77,411,408       75,660,912   -2.26
large  session heap                         56,806,320       58,556,816    3.08
shared 1M buffer                             2,099,200        2,099,200    0.00
shared Checkpoint queue                      2,053,120        2,053,120    0.00
shared DML lock                                285,920          285,920    0.00
shared FileIdentificatonBlock                  349,824          349,824    0.00
shared FileOpenBlock                         2,679,672        2,679,672    0.00
shared KGK heap                                  7,000            7,000    0.00
shared KGLS heap                             7,527,696        8,278,816    9.98
shared KQR L PO                              1,631,232        2,019,200   23.78
shared KQR M PO                              2,886,960        2,933,576    1.61
shared KQR S SO                                 52,512           52,512    0.00
shared KSXR pending messages que               853,952          853,952    0.00
shared KSXR receive buffers                  1,034,000        1,034,000    0.00
shared MTTR advisory                            72,496           72,496    0.00
shared PL/SQL DIANA                            771,848        1,268,312   64.32
shared PL/SQL MPCODE                           746,672        1,324,320   77.36
shared PL/SQL PPCODE                           161,960          161,960    0.00
shared PLS non-lib hp                            2,720            2,720    0.00
shared VIRTUAL CIRCUITS                        815,280          815,280    0.00
shared db_block_hash_buckets                 1,741,808        1,741,808    0.00
shared db_handles                              406,000          406,000    0.00
shared dictionary cache                      3,229,952        3,229,952    0.00
shared enqueue                                 667,408          667,408    0.00
shared errors                                    1,760            1,760    0.00
shared event statistics per sess             4,389,840        4,389,840    0.00
shared fixed allocation callback                   912              912    0.00
shared free memory                          97,626,328      134,183,648   37.45
shared joxs heap init                            4,240            4,240    0.00
shared krvxrr                                  253,056          253,056    0.00
shared ksm_file2sga region                     370,496          370,496    0.00
shared ktlbk state objects                     229,944          229,944    0.00
shared library cache                        40,239,200       47,399,504   17.79
shared message pool freequeue                  558,720          558,720    0.00
shared miscellaneous                        38,335,616       38,901,568    1.48
shared parameters                               48,560           53,384    9.93
shared processes                               450,800          450,800    0.00
shared sessions                              1,054,560        1,054,560    0.00
shared sim memory hea                          170,536          170,536    0.00
shared sql area                             87,306,176       40,734,608  -53.34
shared table definiti                           13,424           17,896   33.31
shared transaction                             724,592          724,592    0.00
shared trigger defini                          130,552          148,264   13.57
shared trigger inform                            3,344            4,360   30.38
       buffer_cache                        369,098,752      369,098,752    0.00
       fixed_sga                               738,400          738,400    0.00
       log_buffer                            1,312,768        1,312,768    0.00


Q1. You only really use sga_max_size if you wish to dynamically alter the SGA memory size.  If you make no dynamic adjustments then using the other parameters (shared_pool, large_pool, db_cache_size) should be fine.  

My reason for this is e.g. ] I have 6 production databases each with 1G set for sga_max_size. The current SGA total of the 5 database is 4G.  If I have 5G of ram, and someone starts adjusting the existing SGA memory parameters, SGA memory might start to be paged.

Q2. I need to free up memory on the system for other production systems. My Statspack report is average 100M free space within the shared pool/ and average of 70M for the large pool. The current shared_pool parameter is 268435456 and sum of V$SGASTAT shows 301989888.  Is the shared_pool using the memory available from the sga_max_size parameter to add to this free space? 

My plan of action is 

- Reduce the large pool by 40M
- Not to use sga_max_size and default to the other parameters for the SGA.  However Im wary that changing this may cause the shared_pool to go bang.

Thanks

Bob



 

Hi Tom,

Following on from the posts above determining suitable shared_pool size, we have a query with 3000+ bind values that uses over 100 Mb of shared memory before a ORA-04031 error occurs. You will probably laugh when you look at the query (below) but to achieve an understanding of what is happening under the hood can you shed some light on why the sql plan needs over 100 Mb of memory?

Thanks in advance,

Shane

SELECT dummy_id, dummy_column, another_dummy_column,yet_another_dummy_column FROM BEST_CLIENTS WHERE dummy_id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) OR (dummy_id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )) OR (dummy_id IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
....
....
....
....
)) ORDER BY 6

Tom,

I am looking at the output of v$sgastat what does "shared pool temporary tabl" show such a big number.

Thanks.


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

POOL        NAME                                                BYTES
----------- -------------------------- ------------------------------
shared pool branch                                             394400
shared pool errors                                              27432
shared pool krvxrr                                             253056
shared pool enqueue                                           2051240
shared pool DML lock                                          1022032
shared pool KGK heap                                             7000
shared pool KQR L PO                                          3513568
shared pool KQR L SO                                           345168
shared pool KQR M PO                                          4721896
shared pool KQR M SO                                           338192
shared pool KQR S SO                                             8192
shared pool KQR X PO                                             5152
shared pool sessions                                          2987920
shared pool sql area                                        241177856
shared pool 1M buffer                                         2098176
shared pool KGLS heap                                         3925648
shared pool processes                                         1320000
shared pool PX subheap                                         166064
shared pool db_handles                                        1160000
shared pool parameters                                          22072
shared pool constraints                                        275152
shared pool free memory                                     295055272
shared pool kglsim heap                                       3128320
shared pool transaction                                       1562400
shared pool PL/SQL DIANA                                       856048
shared pool trace buffer                                      7241728
shared pool FileOpenBlock                                     7517528
shared pool PL/SQL MPCODE                                     1639288
shared pool library cache                                    49399552
shared pool miscellaneous                                    42902112
shared pool DG Broker heap                                      43456
shared pool MTTR advisory                                      524496
shared pool PLS non-lib hp                                       2088
shared pool joxlod: in ehe                                     283672
shared pool joxs heap init                                       4240
shared pool sim memory hea                                    6153096
shared pool table definiti                                      10768
shared pool temporary tabl                       36893488147419094912
shared pool trigger defini                                       7768
shared pool trigger inform                                       1768
shared pool trigger source                                       1096
shared pool Checkpoint queue                                  4106240
shared pool dictionary cache                                  4274432
shared pool qmps connections                                   486200
shared pool enqueue resources                                  662048
shared pool sim trace entries                                  393216
shared pool kglsim object batch                               5733504
shared pool ktlbk state objects                                651240
shared pool KSXR receive buffers                              1034000
shared pool session param values                             13658112
shared pool db_block_hash_buckets                            25991120
shared pool FileIdentificatonBlock                             349824
shared pool kglsim hash table bkts                            1048576
shared pool message pool freequeue                             234256
shared pool KSXR pending messages que                          853952
shared pool Temporary Tables State Ob                          262408
shared pool UNDO INFO SEGMENTED ARRAY                          216784
shared pool event statistics per sess                        12535120
shared pool fixed allocation callback                            1176
shared pool replication session stats                          335920

60 rows selected.



show sga

Total System Global Area                     7035923576 bytes
Fixed Size                                       740472 bytes
Variable Size                                 805306368 bytes
Database Buffers                             6224347136 bytes
Redo Buffers                                    5529600 bytes


show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 654311424 

Tom:

I would like to take your opinion on this code for determining the shared pool size based on number of concurrent users. Is this RELIABLE?

</code> http://www.ss64.com/orasyntax/sharedpool_sql.txt <code>

Tom:

So it is not good tool to determine shared pool size?

Hi Tom,
All of your materials are quite good - excellent.
If you have time,can you please give me suggession about my oracle 8i (8.1.7.4) database in sun solaris 5.8 with 8GB memory and 4 cpu. This database is using just to hold data from arround 1400 users and 400 concurrent users. There are 520 tables to hold data. There is no procedure, triggers etc.
What is the best sige of SGA (share pool, db block buffer, large pool, open cursors, processess, log buffer, max enable role so that it will run in maximum good performance.
jit

Hi Tom,
I am using oracle 8.1.7.4 on sun solaris 5.8. with 8GB of RAM. I tried to increase share pool from 810000000 to 1920000000 but when i re-start database it gave error message ORA-27102 out of memory.
My etc/system parameter is like this.

set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=1024
set shmsys:shminfo_shmseg=1024

set semsys:seminfo_semmap=1026
set semsys:seminfo_semmni=1024
set semsys:seminfo_semmsl=100
set semsys:seminfo_semmns=16384
set semsys:seminfo_semmnu=2048
set semsys:seminfo_semume=256
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767

How do i change my share pool size.
Thanks,
Jit

I want to use my 50% of Physical RAM for total SGA and out of which i want to allocate arround 48% to share pool and constantly monitor the database for tunning.

(I have 1400 users and 400 concurrent users. with lots and lots of data).

How to increase that ?

I am working on oracle and sun solaris 5.8 (32 bits).
Jit

Tom,
As i said i have 8GB of space i want to uses atleast 50% of it for SGA and in that server only oracle is running.

I have 1400 users and 400 concurrent users. with lots and lots of data

Are these parameters are o.k. for above requirements :
Current Proposed
DB_BLOCK_BUFFERS 118000 235000
LARGE_POOL_SIZE 614400 192000000
SHARED_POOL_SIZE 810000000 1920000000
LOG_BUFFER 3276800 160000000

With regards,
Jit

He answered the bit question, it's 32.

P L E A S E !

Methinks Jitendra has misapprehensions about what is in the shared pool. </code> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref97 <code>

Hi tom and all,
Basically i wanted to use my available RAM. As i know that my SGA quite high now itself but i think your ideas is to monitor closely all this stuff when there will be many users concurrently access the database and if there is any problem in tunning like hit ratio.....etc, then we have to tune these parameters accordingly.
Anyway thanks for your support.
Jit

Tom,

SQL> select FREE_SPACE,REQUEST_MISSES,REQUEST_FAILURES from  v$shared_pool_reserved;

FREE_SPACE REQUEST_MISSES REQUEST_FAILURES
---------- -------------- ----------------
   4958116            542                0

SQL> show parameter shared
shared_pool_reserved_size            big integer 65000000
shared_pool_size                     big integer 872415232

Also from v$sgastat the amount of free memory i am getting is approximately equal to below value most of the time.
shared pool free memory                  24105988

If i get this output which shows there are some number of request misses, does this mean that i am running low on shared pool and i need to increase it.If so then any reasonable percent like 10% or 20% of shared pool?

Thanks
Sushil

 

Tom,

You say "542 out of 4,958,116", but that 4 MB is the free space not the total number of requests.I assume you read that wrongly.There are 542 misses in the database and only 4Mb of shared memory is available.

Thanks
Sushil

Thanks Tom for your quick reply,just a quick question is the query that i am using a proper choice to judge the shared pool size(i am querying the v$shared_pool_reserved) view.

Sushil

Tom,
I am querying for shared pool allocations in order to judge proactively that shared pool is sized appropriately.I query v$sgastat and see most of the time my shared free memory is 24MB.Also i query stats$sgastat in the perfstat schema to see the differences in the values, that too gives me my shared memory free to 24MB.Now i have to proactively do the monitoring so that if needed i can increase the shared pool size.I guess v$shared_pool_reserved is also a good view to find the request misses and failures in the shared pool(after all the reserved memory comes from the shared pool itself).Correct me i am wrong.
Your valuable thoughts on this is appreciable.

Thanks
Sushil

Tom

Would you be kind enough to take a gander at the following and comment on it?

We have a two node RAC running 9.2.0.6.0 - 64bit Production on AIX 5.2 - with 32 CPUs and 70 Gb Memory per node.
We are running Oracle Applications and have approximately 900 concurrent users per node.

We are not using cursor_space_for_time.

We had a shared_pool_size of 2560M up until about a month ago when we increased from 450 to 900 concurrent users. Around this time we started getting loads of ORA-4031 errors swiftly followed by what appeared to be database hangs so the decision was made to increase the shared_pool_size first to 4000M and lately to 7680M.

My contention based on your comments above has been that this is way too large and that we should have reduced to 1024M.

The Oracle Applications sizing reference suggests 1000M for 501-1000 users.

However my views are not shared by others so I need some evidence to prove this. Could you a) comment on the current 7680M size of the shared pool and b) offer any suggestions on how to prove it is too big?

Sadly reducing it to suck it and see is not an option.

Also - we will be eventually be supporting 2000 concurrent users per node for which the sizing reference suggests a shared pool size of 2000M - and we would like to use cursor_space_for_time for which the suggestion is a shared pool of 3000M. Would a shared pool of this size ever be justified? And would it work? Do you know if there is a hard limit at which the database "stops short" trying to "clean" the share pool?

If I sum the shareable memory from v$db_object_cache grouped by namespace then cursors come out at 5209M - but if you add a where clause saying executions > 100 then that figure drops to 567M. This tells me that we are not using binds in a lot of cases - which is borne out by dumping out v$sqlarea and using your remove_constants method. Is the above method a valid starting point (given our starting point is a whopping shared pool)for figuring out how big the shared pool needs to be?

Sadly the shared pool advisory only goes down to 3856M so I can't use that.

Thanks and Regards

Ian

Very large shared pools are not unusual with Apps 11i on 64-bit Oracle.

There could be one subpool for every 4 CPUs seen at instance startup time with a maximum of 7 as Tom says. Each subpool will be protected by a separate shared pool child latch. v$latch_children may show how many shared pool child latches and subpools are actually in use (and may show if one subpool is much busier than the others). Statspack reports may show if you already have lots of sleeps for shared pool latches (relatively speaking). If so, then you may be better off with a larger shared pool than seeing if there's a way to reduce subpools to avoid ORA-4031 errors.

Having a higher user load on 9i RAC may mean more global enqueues are being created. They use shared pool memory, so it might not be unusual to have to go above what is recommended by the Apps sizing note.

Things are more efficient in this area in 10gR2. Still might be worth talking with Support about what best to do.

Thanks to both of you for the feedback - most useful.

Regards

Ian

Tom,
i know we can find the sharedpool utilization for v$sgastat. but i would know how do we know the oversized sharedpool had been costing on the performance if found it had been on the low percantage in v$sgastat? any indications from the statspack reports?

TIA

Jimmy

My perfstat is like this:
shared 1M buffer 2,098,176 2,098,176 0.00
shared Checkpoint queue 564,608 564,608 0.00
shared FileIdentificatonBlock 323,292 323,292 0.00
shared FileOpenBlock 1,349,112 1,349,112 0.00
shared KGK heap 3,756 3,756 0.00
shared KGLS heap 2,257,512 5,423,508 140.24
shared KQR M PO 1,459,792 1,738,156 19.07
shared KQR S PO 240,216 252,812 5.24
shared KQR S SO 18,704 21,520 15.06
shared KSXR pending messages que 841,036 841,036 0.00
shared KSXR receive buffers 1,033,000 1,033,000 0.00
shared PL/SQL DIANA 559,484 1,451,756 159.48
shared PL/SQL MPCODE 1,320,348 3,122,608 136.50
shared PLS non-lib hp 3,360 3,360 0.00
shared VIRTUAL CIRCUITS 521,260 521,260 0.00
shared character set object 380,060 380,060 0.00
shared dictionary cache 1,614,976 1,614,976 0.00
shared enqueue 321,472 321,472 0.00
shared errors 231,916 268,732 15.87
shared event statistics per sess 3,770,760 3,770,760 0.00
shared fixed allocation callback 272 272 0.00
shared free memory 40,921,460 44,836,832 9.57
shared joxlod: in ehe 239,276 239,276 0.00
shared joxlod: in phe 15,676 15,676 0.00
shared joxs heap init 4,220 4,220 0.00
shared ksm_file2sga region 370,496 370,496 0.00
shared library cache 118,213,744 170,743,240 44.44
shared message pool freequeue 665,792 665,792 0.00
shared miscellaneous 34,760,996 35,094,424 0.96
shared parameters 103,872 361,404 247.93
shared sessions 809,360 809,360 0.00
shared sim memory hea 300,220 300,220 0.00
shared sql area 438,585,700 375,350,860 -14.42
shared table definiti 3,704 6,012 62.31
shared transaction 395,208 395,208 0.00
shared trigger defini 6,604 8,124 23.02
shared trigger inform 1,884 2,840 50.74
shared trigger source 100 3,208 #######

Large portion of shared pool goes to "SQL area", does that mean our vendor did not use bind variable? This is always my suspicision, from v$sql, I saw they use bind and not bind variable, and they claim that part should not affect performance that much.
How do I get hard evidence to push them to use bind variable?
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.75 93.15
% SQL with executions>1: 33.13 72.24
% Memory for SQL w/exec>1: 32.73 42.72
Is that sufficient enough?

Tom,

Thanks very much for your help.
count per sec per tran
parse count (hard) 45,609 12.7 3.1
parse count (total) 295,816 82.1 20.3

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction --------------- ---------------
Redo size: 129,953.54 32,061.26
Logical reads: 19,073.95 4,705.80
Block changes: 224.60 55.41
Physical reads: 191.52 47.25
Physical writes: 38.28 9.45
User calls: 169.14 41.73
Parses: 82.10 20.26
Hard parses: 12.66 3.12
Sorts: 25.69 6.34
Logons: 0.64 0.16
Executes: 896.85 221.27
Transactions: 4.05

In the afternoon, things seems get worse
Begin Snap: 94 15-Dec-06 15:00:05 117 25.7
End Snap: 95 15-Dec-06 16:00:04 130 24.3
Elapsed: 59.98 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 600M Std Block Size: 8K
Shared Pool Size: 600M Log Buffer: 1,024K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 51,842.24 4,284.67
Logical reads: 5,571.96 460.51
Block changes: 382.26 31.59
Physical reads: 589.36 48.71
Physical writes: 12.76 1.05
User calls: 264.55 21.86
Parses: 118.75 9.81
Hard parses: 33.91 2.80
Sorts: 34.58 2.86
Logons: 0.37 0.03
Executes: 291.19 24.07
Transactions: 12.10

% Blocks changed per Read: 6.86 Recursive Call %: 57.88
Rollback per transaction %: 4.03 Rows per Sort: 606.54

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 89.54 In-memory Sort %: 100.00
Library Hit %: 92.37 Soft Parse %: 71.45
Execute to Parse %: 59.22 Latch Hit %: 99.79
Parse CPU to Parse Elapsd %: 97.41 % Non-Parse CPU: 58.15

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 92.77 92.93
% SQL with executions>1: 24.78 27.50
% Memory for SQL w/exec>1: 25.46 26.79
=========================================
But our vendor is pushing to get big shared_pool_size, insisting this hard parse is not a problem. What is your opinion?

Why do vendors never use binds or understand how to write good code? None of ours do either. Then we are stuck with a crap application that we can't change, it's SO frustrating.

To Charlie:

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

Increase the shared pool, make it worse.

Dear Tom,
How can I solve this error?
ORA-04031: unable to allocate 19204 bytes of shared memory ("shared pool", "unknown object", "sga heap(1,0)", "session param values")

Hi Tom

There is a fundamental thing I dont' understand.

SHARED POOL LATCHING.

Why is it bad? Shared pool is where the SQL code and the data dictionary lives. Isn't this structure mostly READ_ONLY? Why latch? Why do you care to latch? What will be corrupted in the shared pool? To stop latching/parsing we must use Bind Variables yes, but why latch in the first place?

Shared Pool is not like the Data Buffer where data lives . In the Data Buffer I understand the reason latching is bad and why it inhibits concurrency, 'Consistent Gets' ACID theory of transactions, locks, transactions trying to concurrently modify same data, phantom reads and dirty reads etc. Latching (Locking) in the database buffer I understand it can be chore.

But why in SHARED POOL, it is just SQL text, what is going to be corrupt? Why does it have to be concurrent and use latches (serialization devices) ? If it wasn't for these latches we wouldn't have to use bind variables, is that correct?

Many thanks

Kubilay

Hi Tom

I have now figured out the necessity of latches in the SHARED POOL. Jonathan Lewis in his blog answered my question as follows, I thought I shared it in this blog as well.

http://jonathanlewis.wordpress.com/2007/01/21/shared-sql/


The following question appeared in response to my comments in a posting on Bind Variables:

-- Shared pool is where the SQL code and the data dictionary lives. Isn¿t this structure "mostly" READ_ONLY? Why latch/lock? Why do you care to latch? What will be corrupted in the library cache? Why does Oracle have to "protect" it, as you say above? --

It's a good question, so I've decided to write a note about it, rather than replying directly to the comment.

A lot of the work done by Oracle uses linked lists. For example, each cache buffers chain is a linked list, and the reason you grab the cache buffers chains latch is to make sure that the chain (linked list) that you want to traverse does not get damaged (by someone else adding or removing an entry) as you walk along it.

So the latching for data blocks has very little to do with updating data, it's essentially about finding out whether the block you want is in memory, or getting it safely into memory if it isn't already there.

The latches for the library cache and shared pool are the same. You acquire a library cache latch when you want to walk along a linked list of objects in the library cache to find, or insert, an object. (In the case of the library cache, you tend to talk about library cache hash buckets, rather than library cache chains).

The shared pool latch covers the lists of chunks of free memory; if you need a chunk of free memory you acquire the latch so that you can take a piece of memory off a list and use it. If you free up some memory (by kicking something out of the library cache) you grab the shared pool latch so that you can safely add the free chunk to the correct chain in the shared pool. (Of course, you will previously have grabbed the library cache latch to knock the object out of a library cache hash bucket to free the memory it was using)

There are lots more details and, for a little more information, you might want to look at some of the notes on the Dizwell Knowledge Base ( http://www.dizwell.com/prod/node/325 ). The Concepts menu includes a couple of notes that I wrote on this and related topics.


Now I understand better bind variables and latching and why latching is bad to scalability and concurrency.

Latches are memory structures thus they are finite hardware resources, using them sparingly is wise.

As overusing the latches by writing SQL which doesn't use Bind Variables, will starve the system of this resource and will ground it to a halt.



Kubilay

5 * because it relates to a problem I'm dealing with. We have a 3rd party app that ( sigh !) doesn't use bind variables. With a large shared pool ( 500m ) we are now getting ORA-04031. Clearly the answer is to reduce the shared pool size, not increase it. Are there any other ways to mitigate the problem of using literals rathe rthan bind variables ?

5 * because it relates to a problem I'm dealing with. We have a 3rd party app that ( sigh !) doesn't use bind variables. With a large shared pool ( 500m ) we are now getting ORA-04031. Clearly the answer is to reduce the shared pool size, not increase it. Are there any other ways to mitigate the problem of using literals rathe rthan bind variables ?

Hello!

From my understanding after reading the tuning and performance guide the reloads and invalidation columns of v$librarycache should be close to zero in an application that reuses SQL effectivly. In my system I see that i have lots of reloads and invalidations on SQL AREA and a SQL AREA hit ratio below 90%.

select namespace, gets, pins, reloads,invalidations, substr(gethitratio*100, 1,6) as "ratio%" from v$librarycache;
NAMESPACE GETS PINS RELOADS INVALIDATIONS ratio%
--------------- ---------- ---------- ---------- ------------- ------
SQL AREA 423170327 2075427196 8937337 1646 89,792

I have free memory in the shared pool, so it cannot be that the shared pool is sized too small. From my understanding the low hit ratio for the SQL AREA must then be a result of not using binds? Please let me know if my interprentation is somewhat correct.

I have been watching the shared pool free memory more closely and i have spotted that free mb goes up and down constantly. In one minute i have 50MB free and 30 sec later i have 3MB free in the shared pool. More so, the developers have said that collection optimizer statistics have a negative effect on the performace of the application so they have adviced me not to collect stats. Cant see how that can be the case though.. Since a reload occurs when the sql statement has been aged out and the shared pool free memory is decreasing and then suddenly increasing, then i'm more and more certain this is because of not using binds. Somewhat correct ?
Thanks!

Hi Tom
I'm a developer and yes, I adhere to using binds as much as I can! We've got a slow DB which predominately is down to the disk raid which we can't improve much for writes & our DBA is telling us he can improve the situation by moving lots of the main tables into RAM. I'd like to evaluate the numbers - ie work out the size of the tables and indexes to see if this is possible but surely this will not improve any insert/update dml action, or will it?

I believe that if you cache table data you will speed up reads, not the writes.

Tom,
you are right.
When I read "slow DB which predominately is down to the disk raid which we can't improve much for writes" I imagined they are using a raid 5 that is slow in writes.
In this case the bottleneck would manifest when the lgw and dbw are writing to disk, if it's true I believe caching would shift the problem, not solve it. It would be nice if "A reader from UK" could add infos on this matter.

Hi Tom

I understand the table/memory thing but for ease of maintaience, and ability to switch on, should we use a small config table with switches for debug or store these settings in a package spec?

Is there a particularly big trade off in storing the value in a table which is then queried each time a debug message is written to determine if debug is on?

Thanks

Phil

Tom,

I read above , but one thing can't find. What kinds of errors can we find in the following query:

select name, bytes/1024/1024 "MB"
from v$sgastat
where pool = 'shared pool'
order by bytes desc

Results of first row is unbleiveable
session param values = 4095.37768173218 MB
where i am using total 4 GB ram. If some thing wrong with the query of system because when i got the sga values

select * from v$sga
NAME VALUE
-------------------- ----------
Fixed Size 454856
Variable Size 167772160
Database Buffers 671088640
Redo Buffers 667648
4 rows selected

Please guide me what is happening

What version of Oracle are you on? Older versions of Oracle can report abnormally large values for some entries in v$sgastat if that type of memory allocation used to be bunched together under the 'miscellaneous' category since the time the instance was started.

The large value is actually the result of a 32-bit integer underflow. It's hard to do accurate bookkeeping of every memory allocation type in versions of Oracle where v$sgastat was limited in number of rows.

I have heard a new concept in shared pool called MUTEX. What is this? How different is it from a latch? Does both MUTEX and latch exist in shared pool? Which one takes precedence? Does DBA have any control over mutexes?

How does Oracle decides to use latches or mutex? If mutex is faster then can we set or Oracle knows always to use mutex?

Thanks,

Tom,
For sizing an shared pool, why dont we use the size of the sql/plsql statements that is issued when application is loaded.Assume i cant use ad-hoc in my application. If i know that 100 similar statements will be at max loaded each 1M, then 100M should be good?right
I agree that we cannot do this in development where we dont freeze sql (which may be frequently modified for enhancement)
If my approach is right then
1,what are the other attributes should i look in for excluding size of the statement/procedure?
2,How to calculate the size of sql statement in shared pool?

Tom, help to point me documentations explain about mutexes and the way it could be better than latches.

Thanks.

Hello Tom

We are receiving the following error while running a snapshot refresh on our data warehouse environment (9207 oracle ee):

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 189
ORA-06512: at line 1
Wed Dec 16 14:30:42 2009
Errors in file /ora_dwhti/dump/dwhti/dwhti_j001_22073.trc:
ORA-12012: error on auto execute of job 3429856
ORA-04031: unable to allocate 27512 bytes of shared memory ("shared pool","unknown object","sga heap
(1,0)","session param values")

My SGA is 1 GGB and shared pool is sized at 208 MB. A snap from the statspack report of 1 hour window (while this error occured) shows:

Shared Pool    SP       Estd         Estd     Estd Lib LC Time
   Size for  Size  Lib Cache    Lib Cache   Cache Time   Saved  Estd Lib Cache
  Estim (M) Factr   Size (M)      Mem Obj    Saved (s)   Factr    Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
        112    .5         96       31,171       99,119     1.0      22,184,745
        144    .7        127       39,367       99,224     1.0      22,200,756
        176    .8        158       43,433       99,238     1.0      22,202,149
        208   1.0        189       48,716       99,275     1.0      22,206,212
        240   1.2        220       56,486       99,284     1.0      22,208,458
        272   1.3        251       60,867       99,287     1.0      22,208,695
        304   1.5        282       64,793       99,304     1.0      22,210,122
        336   1.6        313       69,066       99,315     1.0      22,211,334
        368   1.8        344       74,554       99,370     1.0      22,216,795
        400   1.9        375       80,247       99,426     1.0      22,222,857
        432   2.1        403       87,078       99,530     1.0      22,229,952
          -------------------------------------------------------------
^LSGA Memory Summary for DB: DWHTI  Instance: dwhti  Snaps: 11185 -11186

SGA regions                       Size in Bytes
------------------------------ ----------------
Database Buffers                    553,648,128
Fixed Size                              737,696
Redo Buffers                         10,760,192
Variable Size                       520,093,696
                               ----------------
sum                               1,085,239,712
          -------------------------------------------------------------

My question is that while I cannot go for shared SQL (binds) in warehouse, how should I proceed to fix this error? Moreover, on metalink's request I ran an analysis on shared pool fragmentation which shows me that there are enough large chunks available to allocate that ~ 26K shared memory:

BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize      Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
6+ (4108+)     free              1      10000          4      10968      10498      41992
6+ (4108+)     free              1      11000          4      11616      11508      46032
6+ (4108+)     free              1      12000          1      12472      12472      12472
6+ (4108+)     free              1      13000          3      13672      13504      40512

Additionally when I try running 'alter system flush shared_pool' in this case, I could see the above o/p changed to show large chunks of free memory in shared pool, but it did not prevent recurrence of ORA 4031.

Can you please suggest as I really do not want to increase shared_pool at this moment.

Best Regards
Jatin Pal Singh

Ok, while we agree to increase shared pool in this case, we donot find much help from the advisory (posted above) as to how much to increase (am going in iteration now from 200 to 300 and so on for 2-3 times). Am I looking at the right section in statspack - can you please comment as am not sure what it is indicating.

Shared Pool    SP       Estd         Estd     Estd Lib LC Time
   Size for  Size  Lib Cache    Lib Cache   Cache Time   Saved  Estd Lib Cache
  Estim (M) Factr   Size (M)      Mem Obj    Saved (s)   Factr    Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
        112    .5         96       31,171       99,119     1.0      22,184,745
        144    .7        127       39,367       99,224     1.0      22,200,756
        176    .8        158       43,433       99,238     1.0      22,202,149
        208   1.0        189       48,716       99,275     1.0      22,206,212
        240   1.2        220       56,486       99,284     1.0      22,208,458
        272   1.3        251       60,867       99,287     1.0      22,208,695
        304   1.5        282       64,793       99,304     1.0      22,210,122
        336   1.6        313       69,066       99,315     1.0      22,211,334
        368   1.8        344       74,554       99,370     1.0      22,216,795
        400   1.9        375       80,247       99,426     1.0      22,222,857
        432   2.1        403       87,078       99,530     1.0      22,229,952
          -------------------------------------------------------------

Another thing I want to know is regarding the shared pool reserved size. As 26 KB allocation looks well above _shared_pool_reserved_min_alloc, shouldn't it look into reserved pool to give it some space?


I see that this view is consistently showing average free size of over 50 KB, max free size of over 600 KB and so on.. Am I looking at incorrect stats?

SQL> show parameter reserved

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 10905190
SQL> set lines 300
SQL> select * from v$shared_pool_reserved
  2  ;

FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE USED_COUNT MAX_USED_SIZE   REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS LAST_ABORTED_SIZE
---------- ------------- ---------- ------------- ---------- ------------- ---------- ------------- ---------- -------------- -------------- ------------- ---------------- ----------------- ------------------------- ---------------- -----------------
   9854136     58308.497         63        671600     891464    5274.93491        106         73632 43305               0              0             0                0                 0                2147483647            0                 0

thanks for all the mentoring.

My Best Regards ~ Jatin

Here is the top section of the report pertaining to:

Thu Dec 17 14:30:42 2009
Errors in file /ora_dwhti/dump/dwhti/dwhti_j001_1682.trc:
ORA-12012: error on auto execute of job 3430051
ORA-04031: unable to allocate 27512 bytes of shared memory ("shared pool","unknown object","sga heap
(1,0)","session param values")
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 189
ORA-06512: at line 1

DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DWHTI         1606462732 dwhti               1 9.2.0.7.0   NO      eux981

              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:     11253 18-Dec-09 14:18:09       20   1,673.5
  End Snap:     11254 18-Dec-09 15:18:13       38     891.6
   Elapsed:               60.07 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:       528M      Std Block Size:          4K
           Shared Pool Size:       208M          Log Buffer:     10,240K

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:          1,821,555.11          5,185,532.87
              Logical reads:              9,340.14             26,589.14
              Block changes:              4,531.71             12,900.71
             Physical reads:              5,021.23             14,294.26
            Physical writes:                798.35              2,272.71
                 User calls:                  3.62                 10.30
                     Parses:                 15.19                 43.26
                Hard parses:                  0.50                  1.42
                      Sorts:                  3.22                  9.17
                     Logons:                  0.19                  0.54
                   Executes:                159.39                453.76
               Transactions:                  0.35

  % Blocks changed per Read:   48.52    Recursive Call %:     99.10
 Rollback per transaction %:    0.32       Rows per Sort:  ########

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.91       Redo NoWait %:    100.00
            Buffer  Hit   %:   67.38    In-memory Sort %:     99.59
            Library Hit   %:   99.38        Soft Parse %:     96.71
         Execute to Parse %:   90.47         Latch Hit %:     99.93
Parse CPU to Parse Elapsd %:   25.80     % Non-Parse CPU:     99.34

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   92.07   82.19
    % SQL with executions>1:   72.79   76.46
  % Memory for SQL w/exec>1:   76.16   66.98

And the advisory part is:

^LShared Pool Advisory for DB: DWHTI  Instance: dwhti  End Snap: 11254
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                                          Estd
Shared Pool    SP       Estd         Estd     Estd Lib LC Time
   Size for  Size  Lib Cache    Lib Cache   Cache Time   Saved  Estd Lib Cache
  Estim (M) Factr   Size (M)      Mem Obj    Saved (s)   Factr    Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
        112    .5         96       30,484      108,868     1.0      24,462,016
        144    .7        128       39,522      108,975     1.0      24,478,193
        176    .8        159       43,560      108,989     1.0      24,479,612
        208   1.0        191       48,926      109,028     1.0      24,483,748
        240   1.2        222       56,693      109,037     1.0      24,485,997
        272   1.3        253       61,069      109,040     1.0      24,486,239
        304   1.5        284       64,991      109,057     1.0      24,487,670
        336   1.6        315       69,204      109,069     1.0      24,488,942
        368   1.8        346       74,657      109,125     1.0      24,494,436
        400   1.9        377       80,345      109,181     1.0      24,500,503
        432   2.1        405       87,091      109,285     1.0      24,507,682
          -------------------------------------------------------------

"..if you are doing 15-16 parses a SECOND for 60 minutes - are you sure you are a warehouse?.." this is small testing & integration setup (small in terms of RAM or SGA - not the data volume)

"..You seem to be using binds pretty much..".. this is an old warehouse design where transformations donot happen outside in etl's and staging file loaded (instead extracted data is first loaded, cleansing done and reloaded etc.. that's why much redo generated and binds used); moreover, we are not doing lots of reporting as this is "test" - i beieve this provide enough info on characteristic of this warehouse.

"..do you happen to be using lots of global temporary table accesses?.." I am not sure of this but how does it contribute to the 4031 issue we are facing?

Best Regards
Jatin Pal Singh

Sir

The issue is that it a 2 tier sort of so called warehouse system; the way you told me to characterize this database implies that I cannot technically call it a pure warehouse (as lots of transformations/loading/unloading (dmls) are going on as well in addition to reporting). But it serves a warehouse purpose in terms of the contents it has for our enterpsise (lots of referential data) and the db size ~ 1 TB (cloned as such from LIVE) with a limited RAM (1 GB SGA) for testing.

So, it a hybrid system I should 've said (DSS+OLTP) and I know is not the most optimally designed systems.

However, I checked and to my understanding confirmed that global temporary table accesses are not there; Can you please elaborate what direction should I take from here?

Thanks, Jatin

Hi Tom,

We have a poorly performing production system (as most posters do). All evidence from AWRRPT output indicates it's due to lack of bind variables among other things. The strange thing I am finding however is the tiny buffer cache size that the ASMM has created.

The DBA has set the system correctly in my opinion:
dayneo@RMSP> show parameters sga_target

NAME TYPE VALUE
------------------------------------ ----------- -----
sga_target big integer 528M

with:

dayneo@RMSP> show parameters cache

NAME TYPE VALUE
------------------------------------ ----------- ------
__db_cache_size big integer 28M
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 0
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 0

And yet, the buffer cache is tiny at 28mb and a whopping 468mb shared pool. Here it is from the AWRRPT:
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 28M Std Block Size: 8K
Shared Pool Size: 468M Log Buffer: 512K

I tried to setup a test environment to see if I could get ASMM to adjust the cache sizes in a similar way. I used 4 different sessions to generate 3500 unique SQL statements each at the same time. After testing, it barely made a difference to the shared pool and buffer cache sizes (75mb and 200mb respectively on test env). They still maintained roughly the same size. I then ran the test again but monitored the count in v$sqlarea. The SQL count remained in a range of around 590 to 630 SQL's.

So, what is going on here? If the SQL is aged fairly quickly out of the SGA, how on earth did we ever land up with a 400+mb shared pool and a super tiny buffer cache?

More info from AWRRPT (10:00-11:00):
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 3,340.95 1,894.37
Logical reads: 3,028.16 1,717.01
Block changes: 25.76 14.61
Physical reads: 591.68 335.49
Physical writes: 1.48 0.84
User calls: 8.32 4.72
Parses: 9.27 5.26
Hard parses: 0.12 0.07
Sorts: 15.32 8.69
Logons: 0.10 0.05
Executes: 35.82 20.31
Transactions: 1.76

% Blocks changed per Read: 0.85 Recursive Call %: 92.83
Rollback per transaction %: 2.24 Rows per Sort: 19.58

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.55 Redo NoWait %: 100.00
Buffer Hit %: 80.47 In-memory Sort %: 100.00
Library Hit %: 99.72 Soft Parse %: 98.72
Execute to Parse %: 74.11 Latch Hit %: 99.82
Parse CPU to Parse Elapsd %: 1.59 % Non-Parse CPU: 97.68

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.71 93.84
% SQL with executions>1: 78.14 83.33
% Memory for SQL w/exec>1: 71.43 81.73

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) DB Time Wait Class
------------------------------ ------------ ----------- --------- --------------
latch: library cache 9,787 966 29.13 Concurrency
db file sequential read 990,863 790 23.82 User I/O
CPU time 317 9.55
db file scattered read 75,483 236 7.13 User I/O
read by other session 49,332 173 5.22 User I/O

Tom, thanks a million for your assisance. It's really appreciated!

Hi Tom,

Thanks for the reply above! Your answer is spot on. The DBA reset the memory a few days ago. The users have reported that the system is running much better than before.

Oracle started off with a 360MB buffer cache and an 80MB shared pool. I checked the pool sizes each day and noticed that the shared pool was increasing every day (20MB first day, 30MB the second day, 40MB the third, etc.). This of course is forcing the buffer cache downward by the same amount. I am expecting the shared pool to stop at around 400MB.

Also worth noting is that the wait events that we used to have on latch shared pool don't exist at the moment. The top wait events are for CPU time and IO, and even the IO wait time is tiny. I expect that the latch shared pool will come to life again when the shared pool tops the 400MB mark and can't grow anymore.

Two questions this time:
1) The DBA has set the minimum buffer cache to 36MB (the plan is to slowly increase this value). Considering that the system has this nasty bind variable issue, what minimum would you set for buffer cache? (Note that SGA_TARGET is around 528MB. and we seem to get +-80% buffer hit using 36MB buffer cache, 99% buffer hit using 360MB buffer cache)

2) Does CPU time wait event mean the db was constrained by CPU?

Thanks Tom

We have a system that uses literals (fixed in a later release which we are migrating to now). It caused endless problems with the shared pool for a database which was quite large but nothing like some tp type services in size and traffic. We had to switch to 64 bit Oracle to cope with the memory issues.

Trouble was that all the literal sql was fragmenting the shared pool and connections were failing as they couldn't acquire space for their sql. Wouldn't reducing the shared pool have exacerbated this ? I should add that Oracle Support suggested switching to 64 bit.

Lateral thinking - don't raise the bridge, lower the river. I like it !

Talk about memory problems ! This was 3 years ago but I retrieved the SR from MyOracle Support and I did indeed do as you suggested - make the pool smaller and easier to manage. Other issues pushed up to 64 bit Oracle, though.

Thanks Tom,
We were running MOD_PLSQL on 64 bit AIX and Oracle 10.1.0.5, and constantly had performance issues with the database reporting latch library cache waits between 40% and 80% of db time.

We simply reduced the shared pool from 450MB down to 140MB (and switched the db back to manually managed memory). Performance is excellent! And our transaction throughput has almost doubled and we are still not using the max CPU or memory.

For anyone getting latch shared pool waits, trust in what Tom is saying! 1) Bind variables, 2) shared pool size, 3) session cached cursors, and parse once execute many

Hi Tom,

I have a doubt that when we shutdown our database,then SGA is de-allocated then what happen to all sql's in shared_pool.I mean all parsing and explain plans were also flused.

If yes then how cme all explain plans will again appear on startup of the database.

Please clear

thx
Vishu

TOM, i m monitoring my shared pool size which is 1024MB, only 300MB is used and 700MB is free always..then should i decrease shared pool size to 300MB ? i have 500 concurrent users, oracle 9ir2 32bit

Hi Tom,

Once i my database ( running on Solaris 10),Oracle is 9.2.0.8 enterprise.The bind variables are used in the app.
I tested to increase the shared_pool_size from 200000000 to 600000000 bytes. The oracle starts its instance with ora-600 error. when i change back to 200000000 ,everything is back to normal.

Could you share some insights?
Thanks .

Tom,
Could you please help me in uderstaning the below scneario better.

In AWR we have :

Tx/sec : 20
Session : 1200

32 cpu server:
Top 5 wait events:

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
latch: library cache 74,568 90,045 1,208 31.4 Concurrency
latch: library cache lock 121,538 52,678 433 18.4 Concurrency
latch: cache buffers chains 379,618 21,113 56 7.4 Concurrency
CPU time  20,971  7.3 
buffer busy waits 190,817 18,780 98 6.5 Concurrency

b)


Parse CPU to Parse Elapsed ratio:

Parse CPU to Parse Elapsd %: 2.29

Statistic Name Time (s) % of DB Time
parse time elapsed 11,307.52 3.94

Statistic Total per Second p er Trans
parse time cpu 25,758 7.25 0.38
parse time elapsed 1,126,064 316.92 16.4

Latch Sleep Breakdown

       
Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
library cache lock 35,225,611 12,679,354 121,538 12,564,845 0 0 0
cache buffers chains 626,493,690 6,916,251 379, 626 6,561,191 0 0 0
library cache 79,765,862 2,975,588 74,568 2,903,565 0 0 0

Library Cache Activity

Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations

SQL AREA 571,097 89.3 41,030,406 -1.44 3 1

Prase call Vs execution

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
275,746 275,664 7.86 ex1dtgtpxy9ny 252E00060002 sql1
234,921 234,926 6.69 1u5x7457kfjhk 264B00020002 sql2
167,507 167,685 4.77 8ap22vqajnwa3 252E00060002 sql3

Other settings:
shared pool size = 8 gb !
session_cached_cursors = 12000
cursor_sharing = force :(

question

a) is above system scalable?
b) concurrent related waits ( libary cache) is because of number of parses vs execution
or it is because of
large shared pool size , large number of child cursors
?
c) can library cache concurrency issues can bring a system to halt - never seen/experienced it - but looks this system would show halt soon ?
d) side effects of - session_cached_cursors here. I think 12000 setting is too high?
e) one of the fix for above is
( seeing the number of parses vs execution section)
parse
loop
bind
execute
end loop

rather than doing it other way which looks it is being done currently?

f) how the goal "parse one and execute many times" is achived at application level... is it by doing connection pooling? - i mean keeping the connection alive and closing it only when all job is done?

g) 8 gb shared pool looks to big. do you think if the norms of bind variables are correctly followed we really dont need such a big shared pool size? -- to avoid ORA-4031 shared pool was increased in past on this host.


regards

Sorry missed to mentioned it earlier.
above AWR stats are from 1 hr window.

regards

Hi Tom,

Why would we see the following?

> show parameter shared_pool_size

NAME_COL_PLUS_SHOW_PARAM                |TYPE       |VALUE_COL_PLUS_SHOW_
----------------------------------------|-----------|--------------------
shared_pool_size                        |big integer|400M

> select * from v$sgainfo where name = 'Shared Pool Size';

NAME                            |     BYTES|RES
--------------------------------|----------|---
Shared Pool Size                |2097152000|Yes

One says 400M and one say 2Gig.  

shared pool = 400mb
db cache    = 400mb
sga target  = 3gb

Tom,

Refering the section just one post above
at http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1513005503967#5884324800346396705


you said:
.....a) doesn't look like it, lots of unnecessary latching going on. Your programmers have decided to

1) not use bind variables - introducing HUGE security issues (big time, beyond belief time) as well as impossible to conquer scalability issues. Look at the amount of time you spend PARSING per second - every second you spend 7.25 seconds of your 32 seconds of cpu time PARSING sql!!!! amazing.

...

e) that is one approach, another would be to move all SQL into plsql (which caches statements open automagically). And if they are not doing things in a loop - then they would need a bit of different code - but the concept is the same.

.....




questions
1)
how did you calculated 32 seconds as mentioned above?

2) Case of moving all SQL to PL/SQL ..
I did a small test of below but it did not helped.

CREATE OR REPLACE procedure user.p
as
b number;
begin
select 1 into b from t,t2,t3,t4 where x = t2.t2_x and t2.t2_x = t3.T3_X and t.x = t3.T3_X and t4.t4_x = t.x;
end;
/

and called the same from java code.
( only snip of the code below)

...
try
{
c = DriverManager.getConnection("jdbc:oracle:thin:@<IP>:1522:db10g", "X", "Y");
try
{
s = c.createStatement();
s.execute("call p()");
c.close();
} catch (SQLException e)
{
System.out.println("Error execution sql");
e.printStackTrace();
}

} catch (SQLException e)
{
System.out.println("Error establishing connection");
e.printStackTrace();
}
...

I called the above in a loop and ran 8 simultaneous session executing this peice of code.



Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 23 87.4
SQL*Net more data from client 2,000 1 0 3.0 Network
latch: library cache 35 0 7 .9 Concurrency
latch: library cache lock 6 0 37 .8 Concurrency
cursor: pin S wait on X 12 0 17 .8 Concurrency

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
2,000 2,000 9.34 4r8wgzxtazhar JDBC Thin Client SELECT 1 FROM T, T2, T3, T4 WH...
2,000 2,000 9.34 5dmt772ctv3sz JDBC Thin Client call p()

In this scneario does PLSQL should cache the statements for me.?

3) Seeing the test case as in (2) above , what is way out in such a scneario.

regards

Dear Tom,

After going through the video
http://www.youtube.com/watch?v=xNDnVOCdvQ0

How to rule out large number_of_connections as one of the issue of concurrency in the issue posted above ....( I understand that around 20% time of the cpu seconds available is spent on parsing.. but just thinking if reducing the number of connections would help here?)

to be precise @

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1513005503967#5884324800346396705

as I mentioned it is 32 cpu system.
and we have seen 1200 sessions during 1 hr AWR

Hi Tom:
Could you please help me to explain the following question :
1)Why those two shared pool size are difference?

>select pool, sum(bytes)/1024/1024
from v$sgastat
where pool = 'shared pool'
group by pool

pool sum(bytes)/1024/1024
Shared Pool Size 1216


>select name, bytes/1024/1024
from v$sgainfo
where name='Shared Pool Size'

name bytes/1024/1024
shared pool 4835.72691345215

2)the items values of v$sgastat seems incorrect? ex:KGH: NO ACCESS and ktcmvcb(too large)
how can I get the correct value of all shared pool items by other way?
- db version 10.2.0.3
- my sga_target=4G sga_max_size=4G, shared pool size=0 , db_cache_size=0
- currently ,Buffer Cache Size=2919235584 , Shared Pool Size=1275068416 from v$sgainfo


pool name bytes
shared pool KGH: NO ACCESS 2186484864
shared pool ktcmvcb 1492642480
shared pool free memory 915836568
shared pool sql area 66895744
shared pool ASH buffers 52428800
shared pool KQR X PO 35985272
shared pool PCursor 33747968
shared pool library cache 29871688


thx
Lon

Hi Tom,

Good day.

I need to understand the significance of Free SGA Memory Available in v$sgainfo.

I tried to find oracle documentation in metalink but couldn't found a definitive result.
NAME BYTES RESIZEABLE
Granule Size 16777216 No
Maximum SGA Size 2137886720 No
Free SGA Memory Available 1090519040

Is the Free SGA Memory Available part of the SGA which is not used uptill now as the database doesnt have sufficient load.

Thanking in advance.
Snehasish Das.

Last week one of our users got the following message:
ORA-04031: unable to allocate string bytes of shared memory

As a result of that, our DBA increaseed the amount of shared memory from 5GB to 15GB. I'm not a DBA but I'm worried that this could cure the symptoms instead of looking at the cause of the problem, and could actually create even bigger problems.

Tom,
On my machine, shared pool size is shown as zero
SQL> Select name,value from v$parameter where name like 'shared_pool_size%';

NAME               VALUE
----------         ------------------

shared_pool_size   0


What exactly does it mean? Is it possible? Do I need to ask my DBA to set it accordingly?

Hi Tom,

I used the below query to get the free memory available in shared pool over past sometime i get some negative values what does it mean ? .

    SELECT time, instance_number,
MAX(DECODE(name, 'free memory',shared_pool_bytes,NULL)) free_memory,
MAX(DECODE(name,'library cache',shared_pool_bytes,NULL)) library_cache,
MAX(DECODE(name,'sql area',shared_pool_bytes,NULL)) sql_area
FROM (
 SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') time,
 dhs.instance_number, name, bytes - LAG(bytes, 1, NULL)
 OVER (ORDER BY dhss.instance_number,name,dhss.snap_id) AS
 shared_pool_bytes
 FROM dba_hist_sgastat dhss, dba_hist_snapshot dhs
 WHERE name IN ('free memory', 'library cache', 'sql area')
 AND pool = 'shared pool'
 AND dhss.snap_id = dhs.snap_id
 AND dhss.instance_number = dhs.instance_number
 ORDER BY dhs.snap_id,name)
GROUP BY time, instance_number
ORDER BY 1,2 desc

Sameple output.

Snap_id Inst_id free_mem lib_cas sql_area
----------------------------------------------------
2013_08_28 23:00 1 -345936 58480 -309848
2013_08_29 00:00 2 -428592 62488 226920
2013_08_29 00:00 1 -745696 78400 -415600
2013_08_29 01:00 2 299280 101536 -534968
2013_08_29 01:00 1 581632 -64568 337912
2013_08_29 02:00 2 371368 -249616 568832
2013_08_29 02:00 1 -515880 -41864 -854360

Regards
Bala

ops$tkyte%ORA11GR2> SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') time,
  2         dhs.instance_number,
  3         name,
  4             lag(name) over (ORDER BY dhss.instance_number,name,dhss.snap_id),
  5         bytes - LAG(bytes, 1, NULL) OVER (ORDER BY dhss.instance_number,name,dhss.snap_id) AS shared_pool_bytes
  6    FROM dba_hist_sgastat dhss, dba_hist_snapshot dhs
  7   WHERE name IN ('free memory', 'ASH buffers', 'library cache', 'sql area')
  8     AND pool = 'shared pool'
  9     AND dhss.snap_id = dhs.snap_id
 10     AND dhss.instance_number = dhs.instance_number
 11  ORDER BY dhs.snap_id,name
 12  /

TIME                           INSTANCE_NUMBER NAME                           LAG(NAME)OVER(ORDERBYDHSS.INSTANCE_NUMBER,NAME,DHSS.SNAP_ID)     SHARED_POOL_BYTES
------------------------------ --------------- ------------------------------ ---------------------------------------------------------------- -----------------
2013_08_27 13:00                             1 ASH buffers
2013_08_27 13:00                             1 free memory                    ASH buffers                                                               91278552
2013_08_27 14:00                             1 ASH buffers                    ASH buffers                                                                      0
2013_08_27 14:00                             1 free memory                    free memory                                                                  -7160

ops$tkyte%ORA11GR2> select to_char( dhs.begin_interval_time,'YYYY_MM_DD HH24:MI') time,
  2         dhs.instance_number,
  3             dhss.name,
  4             dhss.bytes
  5    from dba_hist_sgastat dhss, dba_hist_snapshot dhs
  6   where dhss.pool = 'shared pool'
  7     and dhss.name = 'free memory'
  8     AND dhss.snap_id = dhs.snap_id
  9     AND dhss.instance_number = dhs.instance_number
 10   order by dhs.begin_interval_time, dhs.instance_number
 11  /

TIME                           INSTANCE_NUMBER NAME                                BYTES
------------------------------ --------------- ------------------------------ ----------
2013_08_27 13:00                             1 free memory                     108055768
2013_08_27 14:00                             1 free memory                     108048608
2013_08_27 15:00                             1 free memory                     108144880
2013_08_27 16:00                             1 free memory                     108184520
2013_08_27 17:00                             1 free memory                     108153632
2013_08_27 18:00                             1 free memory                     108122696
2013_08_27 19:00                             1 free memory                     108093944
2013_08_27 20:00                             1 free memory                     104899064
2013_08_27 21:00                             1 free memory                     103804056
2013_08_27 22:00                             1 free memory                      99703336
2013_08_27 23:00                             1 free memory                      99395552
2013_08_28 00:00                             1 free memory                      98856480
2013_08_28 01:00                             1 free memory                      98204896
2013_08_28 02:00                             1 free memory                      97693136

Hi Tom,

I'm managing a large OLTP database with a 50 GB SGA. ASMM was recently enabled, but prior to that the pool sizes were all set manually. Since the database was started with ASMM, I've observed a steady growth in the size of the shared pool. It started at about 9 GB and over the course of a month or so, has grown to 14 GB. I have a suspicion that the developers aren't using binds in their queries, but I'm not sure how to confirm that. What resources can I use to find queries that should be be using binds?

Thanks in advance.