This discussion is archived
5 Replies Latest reply: May 16, 2013 5:00 AM by 980965 RSS

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

980965 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hello sir,

    Thanks for your reply, i understand
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points