This discussion is archived
4 Replies Latest reply: Sep 7, 2013 6:50 AM by sb92075 RSS

AWR - Advisory Statistics looks really strange

7c811860-a348-4277-a62a-e5818e9c1aae Newbie
Currently Being Moderated

Hello,

 

 

After years of using AWR reports I noticed that reported numbers are really strange.
Please compare following tables (this is just an example from a small test system):

 

Time Model Statistics

  • Total time in database user-calls (DB Time): 707s
Statistic NameTime (s)% of DB Time
sql execute elapsed time458.6564.87
DB CPU347.2349.11
parse time elapsed45.546.44
hard parse elapsed time37.185.26
hard parse (sharing criteria) elapsed time8.521.20
PL/SQL execution elapsed time5.030.71
PL/SQL compilation elapsed time1.870.27
connection management call elapsed time1.720.24
hard parse (bind mismatch) elapsed time0.710.10
repeated bind elapsed time0.690.10
sequence load elapsed time0.000.00
DB time707.01
background elapsed time396.96
background cpu time15.83

Segments by Physical Reads

  • Total Physical Reads: 23,942
  • Captured Segments account for 71.6% of Total
OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
JSK_FI_MARTINJSK_FI_MARTINSYS_LOB0002249122C00005$$LOB5,36022.39
SYSSYSTEMIND$TABLE5,10721.33
SYSSYSTEMTAB$TABLE2,87512.01
SYSSYSTEMCDEF$TABLE1,8077.55
JSK_PRMJSK_PRMSYS_LOB0002249929C00007$$LOB8293.46

 

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
9000.75467,267125,992,859
1,2001.00457,880119,594,551
1,5001.25450,737117,812,592
1,8001.50446,754116,796,039
2,1001.75444,189116,796,039
2,4002.00444,189116,796,039

 

Using these numbers I can calculate

 

SGA SizeEst DB TimeTime Saved
Est Physical ReadsReads Saved
900467267-9387125992859-6398308
120045788001195945510
150045073771431178125921781959
1800446754111261167960392798512
2100444189136911167960392798512
2400444189136911167960392798512

 

NOW:
- Why "Est DB Time" for the current SGA size is 457,880 sec, when total "DB time" was 707 sec only?

- Why "Est Physical Reads" for the current SGA size are 119,594,551 when "Total Physical Reads" was 23,942 only?

- Thus I hardly can save 13,691 sec and 2,798,512 physical reads by doubling SGA !

 

It looks like "Est DB Time" is cumulative value, because the difference with previous snapshot for "Size Factor" = 1 gives exactly real "DB time".

But this method applied to other factors as well to "Est Physical Reads" gives nonsense results.

 

Comparing just percent of "Est DB Time", like 444,189 / 457,880 => 97%  (for increase 1200 to 2400) also does not make much sense,

as these numbers are not comparable to the real snapshot "DB time".

 

You can find the complete report here but I am sure you will find similar numbers in your reports too.

 

Can you explain these numbers?

How do you calculate possible benefit of SGA increase for large productive systems?

 

Best Regards,

Frantisek Bouda

  • 1. Re: AWR - Advisory Statistics looks really strange
    sb92075 Guru
    Currently Being Moderated

    what problem are you trying to solve?

    How will you, I or anyone recognize that any subsequent post contains the correct solution?

  • 2. Re: AWR - Advisory Statistics looks really strange
    7c811860-a348-4277-a62a-e5818e9c1aae Newbie
    Currently Being Moderated

    I am working on a customer system analysis to give them a recommendation if the current database configuration is able to serve additional load or they have to invest into a new HW.

    I was going to use Advisory statistics - If SGA decrease estimation (factor 0.75) gives almost the same results as the current state, it means there is some reserve.

    if SGA increase (factor 1.25) estimates significant improvement, it means the system is already undersized.

     

    So far I thought that I can understand AWR reports, including Advisory statistics, quite well - until I came across these unusable numbers.

     

    It means - either there is a mistake in AWR report - then may be I would be able to interpret the numbers in a different way.

    Or they can not be compared like I am trying to - then I would like to understand why... and how can I get some more realistic estimation.

    Note: Of course SGA is only part of the analysis....

  • 3. Re: AWR - Advisory Statistics looks really strange
    Alvaro Pro
    Currently Being Moderated

    NOW:
    - Why "Est DB Time" for the current SGA size is 457,880 sec, when total "DB time" was 707 sec only?

    Because the AWR report hold statistics regarding only the snapshots you provided, while the SGA TARGET advisor hold cumulative numbers since the instance startup.

     

    - Why "Est Physical Reads" for the current SGA size are 119,594,551 when "Total Physical Reads" was 23,942 only?

    Same as above


    - Thus I hardly can save 13,691 sec and 2,798,512 physical reads by doubling SGA !

     

    Looking at your number it seems you don't have much to gain by giving more memory to SGA_TARGET. Check your parsing activity as well.

  • 4. Re: AWR - Advisory Statistics looks really strange
    sb92075 Guru
    Currently Being Moderated

    7c811860-a348-4277-a62a-e5818e9c1aae wrote:

     

    I am working on a customer system analysis to give them a recommendation if the current database configuration is able to serve additional load or they have to invest into a new HW.

    I was going to use Advisory statistics - If SGA decrease estimation (factor 0.75) gives almost the same results as the current state, it means there is some reserve.

    if SGA increase (factor 1.25) estimates significant improvement, it means the system is already undersized.

     

    So far I thought that I can understand AWR reports, including Advisory statistics, quite well - until I came across these unusable numbers.

     

    It means - either there is a mistake in AWR report - then may be I would be able to interpret the numbers in a different way.

    Or they can not be compared like I am trying to - then I would like to understand why... and how can I get some more realistic estimation.

    Note: Of course SGA is only part of the analysis....

     

     

    What is OS name & version?

     

    Does the system at the OS level have sufficient resources (CPU, RAM, I/O) to accommodate another instance?

Legend

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