5 Replies Latest reply: May 16, 2013 7:00 AM by 980965 RSS

    Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice

    980965
      Hai sir,

      How do i Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice results please help me

      SQL> select * from v$sga_target_advice order by sga_size;

      SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
      ---------- --------------- ------------ ------------------- -------------------
      256 .25 1052 5.7796 145982
      512 .5 187 1.0269 46732
      768 .75 182 1 46653
      1024 1 182 1 46653
      1280 1.25 182 1 46653
      1536 1.5 182 1 46653
      1792 1.75 182 1 46653
      2048 2 182 1 46653

      8 rows selected.

      -------------------------------------------------------------------------------------------------------------------------------
      SQL> select * from v$pga_target_advice;

      PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
      ----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
      71303168 .125 ON 435643392 1081 321155072 58 7
      142606336 .25 ON 435643392 666 30100480 94 0
      285212672 .5 ON 435643392 622 0 100 0
      427819008 .75 ON 435643392 622 0 100 0
      570425344 1 ON 435643392 622 0 100 0
      684510208 1.2 ON 435643392 622 0 100 0
      798595072 1.4 ON 435643392 622 0 100 0
      912679936 1.6 ON 435643392 622 0 100 0
      1026764800 1.8 ON 435643392 622 0 100 0
      1140850688 2 ON 435643392 622 0 100 0
      1711276032 3 ON 435643392 622 0 100 0

      PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
      ----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
      2281701376 4 ON 435643392 622 0 100 0
      3422552064 6 ON 435643392 622 0 100 0
      4563402752 8 ON 435643392 622 0 100 0

      14 rows selected.


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

      SQL> select * from v$memory_target_advice;

      MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
      ----------- ------------------ ------------ ------------------- ----------
      784 .5 187 1.0062 0
      1176 .75 186 1 0
      1568 1 186 1 0
      1960 1.25 186 1 0
      2352 1.5 186 1 0
      2744 1.75 186 1 0
      3136 2 186 1 0

      7 rows selected.

      Thanks to all
        • 1. Re: Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice
          swapnil kambli
          first of all read the fine manual for these views for explanation about the columns such as

          [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2104.htm] v$sga_target_advice

          Whenever you post query out put use \
             tags that makes readable for other                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
          • 2. Re: Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice
            John Stegeman
            Did you expect us to read the documentation for you and repeat what it says?

            All of this is documented.

            If you have specific questions, such as "I read this in the documentation but I cannot understand this point. Does it mean <A> or <B> or something else?" then we'd be happy to help.

            But as far as reading the documentation to you, not so happy to do that.
            • 3. Re: Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice
              980965
              hello sir,

              sorry!!

              i read Database Documentation chapter managing memory in this i get memory size factor 1 is current memory size but i don't know how to verify this result, from this result how
              can i understand what is the needs memory_target value

              MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
              ----------- ------------------ ------------ ------------------- ----------
              784 .5 187 1.0062 0
              1176 .75 186 1 0
              1568 1 186 1 0
              1960 1.25 186 1 0
              2352 1.5 186 1 0
              2744 1.75 186 1 0
              3136 2 186 1 0

              Thanks
              • 4. Re: Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice
                swapnil kambli
                from [url http://docs.oracle.com/cd/B28359_01/server.111/b28310/memory003.htm ] here
                The row with the MEMORY_SIZE_FACTOR of 1 shows the current size of memory, as set by the MEMORY_TARGET initialization parameter, and the amount of DB 
                time required to complete the current workload. In previous and subsequent rows, the results show a number of alternative MEMORY_TARGET sizes. For each 
                alternative size, the database shows the size factor (the multiple of the current size), and the estimated DB time to complete the current workload if the MEMORY_TARGET parameter were changed to the alternative size. *Notice that for a total memory size smaller than the current MEMORY_TARGET size, estimated DB time increases. Notice also that in this example, there is nothing to be gained by increasing total memory size beyond 450MB. However, this situation might change if a complete workload has not yet been run*
                Did you read this?Do you still have any doubts?
                • 5. Re: Analyse v$sga_taget_advice, v$pga_target_advice, v$memory_target_advice
                  980965
                  Hello sir,

                  Thanks for your reply, i understand
                  Thanks