Home » RDBMS Server » Performance Tuning » Shared Pool Advisory for Shared Pool Size
Shared Pool Advisory for Shared Pool Size [message #209445] Thu, 14 December 2006 20:55 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,
i am using Orcle 9i R2 on Linux
i want to configure Shared Pool size

following are the results for Shared pool advisory
please suggest me the appropraite size and advice for selecting the size

Also please suggest is it necessary to have multiple concurrent users connected while executing this Query?

  set lines  100
 set pages  999

 column        c1     heading 'Pool |Size(M)'
 column        c2     heading 'Size|Factor'
 column        c3     heading 'Est|LC(M)  '
 column        c4     heading 'Est LC|Mem. Obj.'
 column        c5     heading 'Est|Time|Saved|(sec)'
 column        c6     heading 'Est|Parse|Saved|Factor'
 column c7     heading 'Est|Object Hits'   format 999,999,999


 SELECT
  2     shared_pool_size_for_estimate  c1,
  3     shared_pool_size_factor        c2,
  4     estd_lc_size                   c3,
  5     estd_lc_memory_objects         c4,
  6     estd_lc_time_saved                    c5,
  7     estd_lc_time_saved_factor             c6,
  8     estd_lc_memory_object_hits            c7
  9  FROM
 10     v$shared_pool_advice;


                                                   Est        Est
                                                  Time      Parse
     Pool        Size        Est     Est LC      Saved      Saved          Est
   Size(M)     Factor    LC(M)    Mem. Obj.      (sec)     Factor  Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------
       128         .5        127      14639    1683656          1  155,576,580
       160       .625        158      19799    1683687          1  155,578,954
       192        .75        189      22020    1683703          1  155,580,261
       224       .875        220      26715    1683711          1  155,581,090
       256          1        251      29576    1683718          1  155,581,597
       288      1.125        282      34115    1683721          1  155,582,059
       320       1.25        313      36667    1683726          1  155,582,471
       352      1.375        344      38462    1683729          1  155,582,635
       384        1.5        377      40901    1683733          1  155,583,195
       416      1.625        408      44563    1683735          1  155,583,427
       448       1.75        439      47012    1683737          1  155,583,577
       480      1.875        470      52207    1683737          1  155,583,650
       512          2        502      55654    1683737          1  155,583,783

13 rows selected.

SQL>




Thanks and Regards
OraSaket
Re: Shared Pool Advisory for Shared Pool Size [message #209514 is a reply to message #209445] Fri, 15 December 2006 05:15 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
By reading your output, it would appear that increasing your shared pool to 448MB would be the most efficient. You'd obviously want to have the advisory enabled during a time of a representative load on the system.
Re: Shared Pool Advisory for Shared Pool Size [message #209660 is a reply to message #209445] Sat, 16 December 2006 07:39 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Brian,
Thanks for your advice and reply

however one query regarding this...

for PGA_Aggregate_Target i used to refer v$pgastat and v$sysstat with following parameters so as to know which should be my PGA_AGGREGATE_TARGET and how it is affecting each time i change it

for pga_aggregate_target
cache hit % in v$pgastat should be 100%
over allocation count in v$pgastat should be 0
extra bytes read/written  in v$pgastat should be less

following values in v$sysstat should be 0
workarea executions - onepass           
workarea executions - multipass


are there such reference parameters for shared pool too?
please suggest


Thanks in advance
OraSaket
Re: Shared Pool Advisory for Shared Pool Size [message #209664 is a reply to message #209660] Sat, 16 December 2006 08:44 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on Oracle 9i or less and are NOT using shared server (MTS) configuration, then you don't have to worry about tuning the shard pool yourself if you are using PGA_AGGREGATE_TARGET. You can refer to How to Tune PGA_AGGREGATE_TARGET and Tuning PGA_AGGREGATE_TARGET in Oracle 9i for more information on setting this parameter.

Previous Topic: Trace Oracle Job Session using tkprof
Next Topic: how to increase the speed of a MView Refresh
Goto Forum:
  


Current Time: Sat Dec 03 06:16:37 CST 2016

Total time taken to generate the page: 0.05484 seconds