Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
AWR and statspack should show approximate tuning potential of sql Statements

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.
Comments
-
strangely the per row stats are present in the AWR sql detail report, just side by side with the per execution stats-
-
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 !
-
part of ADDM calculation must be included in AWR to recommend tunable candidates.
-
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.
-
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.
-
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.
-
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.