3 Replies Latest reply on Feb 5, 2014 5:23 PM by Jan-Marten Spit

    Oracle 11G & SPM

    user2522172

      Hi,

       

      We've implemented SPM (Sql Plan Management) on a 11g (11.2.0.3.0) database to achieve plan stability. Plan stability has been achieved so we are satisfied. From time-to-time we evolve new plans to see if they better than de baseline.

      Our only concern is the size of the base-line table. In +/- 1 month the base-line table has reached +/- 540000 records!

      SPM is using approx. 4 Gbyte (awrinfo.sql):

       

      **********************************

      (3a) Space usage by AWR components (per database)

      **********************************

       

      COMPONENT        MB  % AWR  KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%

      --------- --------- ------ ------------ ---------- ----------- ----------------

      SQLPLAN     4,105.0   58.9       15,121      354.4     2,480.7    70% : 30%

      FIXED         320.1    4.6        1,179       27.6       193.5    47% : 53%

      ASH           114.8    1.6          423        9.9        69.3    87% : 13%

      SQLBIND        81.0    1.2          298        7.0        48.9    54% : 46%

      EVENTS         67.4    1.0          248        5.8        40.8    49% : 51%

      SQLTEXT        65.0    0.9          239        5.6        39.3    95% : 5%

      SPACE          39.6    0.6          146        3.4        23.9    55% : 45%

      SQL            31.9    0.5          118        2.8        19.3    63% : 37%

      RAC             0.8    0.0            3        0.1         0.5    50% : 50%


      We are not concerned about the size of the sysaux tblspc. We have plenty of space. But could such big  spm cause performance problems by itself because of the big number of records?

      Any has experience with this?


      regards,


      Ivan