5 Replies Latest reply on Aug 27, 2014 1:43 PM by BM_dba-Oracle

    how to evalutate sqlarea "retention time"?

    1445625
    1445625 Aug 26, 2014 3:18 PM

      Hi all,

       

      I am trying to find out how to calculate the sqlarea "retention time".

       

      I know that the sqlarea is used in a cyclic fashion: when an object is not used by any session it will fall down in a LRU list and eventually it will be purged out of the sqlarea. This can happen after some seconds or after some hours, depending on many different circumstances (sga size, db load type, use of bind variables, ...).

       

      I would like to periodically calculate my sqlarea "retention time" to see how long I am caching unused corsors, just to check from a different perspective how my db is behaving.

       

      According to the v$sqlarea reference ( http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3064.htm#REFRN30259 ), V$SQLAREA.LAST_ACTIVE_TIME is the "Time at which the query plan was last active".

      That seems to meet my requirement, because I would expect " SYSDATE - MIN ( V$SQLAREA.LAST_ACTIVE_TIME  ) " to be my sqlarea actual "retention time".

      Unfortunatly, if I look for the oldest values of LAST_ACTIVE_TIME I found a few rows with LAST_ACTIVE_TIME="01/01/1970 01:00:00" AND a few rows with LAST_ACTIVE_TIME ~ one months ago (checked on different 11gR2 instances).

      That's weird and that seems to break the LRU rule.

       

      Do you know how can we approximate the sqlarea retention time?

       

      Thanks

       

      Andrea

        • 1. Re: how to evalutate sqlarea "retention time"?
          sol.beach Aug 26, 2014 4:29 PM (in response to 1445625)

          >Do you know how can we approximate the sqlarea retention time?

           

          what difference does it make?

           

          how exactly would you actually use this value after you obtain it?

          • 2. Re: how to evalutate sqlarea "retention time"?
            sybrand_b Aug 26, 2014 4:55 PM (in response to 1445625)

            As usually the SGA reaches a certain optimum after a while AND you can PIN objects in the shared pool (so they don't age out), why bother about an imaginary 'sqlarea retention time'

            It is just another Meaningless Indicator of the Performance of the System. Just like hit ratios.

             

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: Re: how to evalutate sqlarea "retention time"?
              Green Dust
              Green Dust Aug 26, 2014 8:00 PM (in response to sybrand_b)

              Agree with sybrand_b

               

              If your aim is to keep any particular object in the shared pool go for the pinning of that object.

               

              use the below pink for the same

              DBMS SHARED POOL - Oracle FAQ

              • 4. Re: how to evalutate sqlarea "retention time"?
                1445625
                1445625 Aug 27, 2014 7:46 AM (in response to Green Dust)

                Hi all,

                my objectives are many:

                • curiosity (trying to understand and see how Oracle works keeps me trained and helps me learning new stuff)
                • performance tuning. I agree with the fact that hit ratios are often meaningless, but I think that using many different performance indicators can help understand how the database and application are behaving. My guess is that high sqlarea hit ratio together with "high sqlarea retention time" could suggest that the shared pool is over-sized, low sqlarea hit ratio together with "high sqlarea retention time" could suggest that the application has some problems, and low sqlarea hit ratio together with "low sqlarea retention time" could be a signal of a under-sized shared pool
                • index usage monitoring. I know that Oracle provides a really basic index usage monitoring and I would like to build a slightly more sofisticated one. I think that it would be helpful to see if, when, and how many times an index has been used in order to understand if an index has become useless. Before starting to monitor the v$sql_plan% views, however, I want to check whether the shared pool contains (at least) all the plan which have been generated in the last X hours and that have not been invalidated.

                 

                Regards,

                Andrea

                • 5. Re: how to evalutate sqlarea "retention time"?
                  BM_dba-Oracle
                  BM_dba-Oracle Aug 27, 2014 1:43 PM (in response to 1445625)

                  OP,

                   

                  About Sizing of shared pool:

                   

                    In a running system, first observe the allocated memory for sql area in shared pool you can use v$sgastat view for that:

                   

                  SQL> select * from v$sgastat where name='SQLA';  ## allocated memory for Sql area

                  POOL         NAME                            BYTES

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

                  shared pool  SQLA                          5854400

                   

                   

                  From library cache stats, you can see the number of reloads in the area: v$librarycache

                  NAMESPACE                                      GETS       PINS    RELOADS

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

                  SQL AREA                                      68682     304693        925   ## Number of reloads, if the number of  reloads is high comparing to gets that means the objects are aging out quickly. Memory constraint.

                  TABLE/PROCEDURE                               25355      84375       1413

                   

                  Increasing shared pool might help if the number of reloads are high -- as a basic configuration check.

                   

                  Regards,

                  Bhanu