Forum Stats

  • 3,825,204 Users
  • 2,260,480 Discussions
  • 7,896,440 Comments

Discussions

AWR and statspack should show approximate tuning potential of sql Statements

Lothar Flatz
Lothar Flatz Member Posts: 687 Silver Badge
edited Jan 11, 2016 6:18PM in Database Ideas - Ideas

There is a very easy way to implent a rough tuning potential indicator. Right now we see number per execution. E.g. buffer gets / execution. That gives us no idea on efficiency. What we need to get a rough idea of tuning potential is buffer gets / row or CPU / row, etc. This measure is referenced as a rule of thumb in tuning literature. ( No link given for compliance reasons).

Of course the number must be used with care (e.g. account for GROUP BY clause), but it does make a good practicaal indicator.

Lothar FlatzrohanwaliaborneselFranck Pachot2807298Gugs-OracleUser259623 -OracleRainer StenzelHemant K ChitaleUser2121 - -Oraclevinaykumar2ctriebKayKcaadecarvalhoPravin TakpireJagadekarabhagatsinghabhinivesh.jainZlatko SiroticAish13Andreas BuckenhoferSven W.3324453Knut Härtel
26 votes

Active · Last Updated

Comments

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    strangely the per row stats are present in the AWR sql detail report, just side by side with the per execution stats-

  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    I often check the rows processed in the 'SQL ordered by execution' in order to see the logical reads per row.

    You can't imagine how often I've seen that the most expensive query is returning on average 0 rows !

  • Gugs-Oracle
    Gugs-Oracle Member Posts: 74

    part of  ADDM  calculation must be included in AWR to recommend tunable candidates.

    anjuls
  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    The AWR and statspack are really just top level reporting on workloads and overall capacity utilisation. eg average I/O per exec, average elapsed time per exec, etc.

    So for the general overviews the AWR and statspack reports provide that for the novice DBA.

    The buffer gets / row, and CPU / row, and others, are already available to calculate when you query v$sql, etc.

    So for the per row calculation, it's better to get it off the dynamic views directly rather than from the AWR and statspack.

    As you say, there are some calcs (eg count(*)) that will always indicate poor efficiency, so this information needs to be correctly interpreted by a senior DBA. If this information is in an AWR and statspack report then the novice DBAs are going to be misled.

    Franck Pachot
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    The AWR and statspack are really just top level reporting on workloads and overall capacity utilisation. eg average I/O per exec, average elapsed time per exec, etc.

    So for the general overviews the AWR and statspack reports provide that for the novice DBA.

    The buffer gets / row, and CPU / row, and others, are already available to calculate when you query v$sql, etc.

    So for the per row calculation, it's better to get it off the dynamic views directly rather than from the AWR and statspack.

    As you say, there are some calcs (eg count(*)) that will always indicate poor efficiency, so this information needs to be correctly interpreted by a senior DBA. If this information is in an AWR and statspack report then the novice DBAs are going to be misled.

    Well, quite often the first thing I get from a customer is an AWR. That is long before I will get access to his database. It is very helpfull to see tuning potential right from the AWR. Although it sometimes will be missleading, there is no better information possible at the high summary level.

    Once you have access to the database it self you can get better information. But at a first source of information the AWR has it's value.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Well, quite often the first thing I get from a customer is an AWR. That is long before I will get access to his database. It is very helpfull to see tuning potential right from the AWR. Although it sometimes will be missleading, there is no better information possible at the high summary level.

    Once you have access to the database it self you can get better information. But at a first source of information the AWR has it's value.

    I'm all for the AWR as a summary.

    For the detail - it's just better for a senior DBA to get the information and interpret it correctly from the dynamic views.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    I'm all for the AWR as a summary.

    For the detail - it's just better for a senior DBA to get the information and interpret it correctly from the dynamic views.

    Buffer gets / row is on the exact same detail level as buffer gets / per execution. Which is present in the AWR right now.

    Franck Pachot