AWR and statspack should show approximate tuning potential of sql Statements

Lothar Flatz

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 Flatz

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

Franck Pachot

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

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

top.gun

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.

Lothar Flatz

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

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

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.