Please have a look on the statement below and it’s result. It represent a real application. I changed the table names to protect the privacy of my customers, but kept the meaning of the tables intact. The statement below is very usefull to sketch the characteristics of an application very quickly. It picks the statements showing the highest activity. This tables are likely to be the central elements of the schema you research. It also shows how these tables are supported by the phisical design and the application code. That way it is a kind of a I/O map of an application.
I created the statement more or less by accident and was surprised, how usefull it is. The result below is typical. Inituition would suggest that the I/O is distributed evenly across all tables. In real life this is rarely the case. The use of the application leaves it’s footprints in the activity pattern. Adding up percentages we reveal , that 97% of physical I/Os results from a few tables. It is beneficial to investigate in more depth. How would you proceed?
1 select OBJECT_NAME, "logical reads","physical reads",
2 round(ratio_to_report("physical reads") over ()* 100,2) "% physical reads",
3 round(ratio_to_report("logical reads") over ()* 100,2) "% buffergets"
4 from
5 (select OBJECT_NAME, sum(decode(STATISTIC_NAME,'logical reads', value ,null)) "logical reads",
6 sum(decode(STATISTIC_NAME,'physical reads', value ,null)) "physical reads"
7 from v$segment_statistics
8 where owner='&owner'
9 and STATISTIC_NAME in ('logical reads','physical reads')
10 and OBJECT_TYPE ='TABLE'
11 group by OBJECT_NAME
12 order by "logical reads" desc)
13* where rownum < 11
Oracle Riddle Blog 
First of all I would ask an operations manager what/if there is a problem.
Without a defined goal, I’m in high trouble for CTD.
Let’s assume there _is_ a problem of the general type “Application is slow”. – This would lead to the next question if the whole application is slow for everyone or if we can partition the response time by specific business-tasks, people, days/time, departments, geographical buildings, anything?
Equipped with this information I’d grab one guy knowing the busines process(es) involved, and another who knows it’s specific implementation (both application and database). Lucky me if the full knowledge is condensed in one person. In addition one affected “customer” is required which can reproduce the slowless and measure the effect of any changes done.
Now with all the knowledge available, the next step is to identify where/why all those physical reads are done.
There are numerous reasons, so I’ll pick 3 examples:
the application is asking the same question again and again (without new information in the DB) => use the result in the application until it’s invalid.
There are SQL statements with strong FILTER predicates => make them ACCESS predicates (create Index).
Really (nearly) all tables rows are needed every time => transfer “physical reads” to “physical reads direct”.
With any reason and given suggestion the first round is with the application knowing guy(s) to check if the suggestion can bring any advantage – and if yes, do a reasonable guess how much faster the business process will run (and check, if this might be within acceptable boundaries).
If we agree on a method and expected gains, we implement the suggestion and test with our gunea pig if we are right.
When it’s documented we meet the application requirements, we can stop. Otherwise we need to iterate.
LikeLike
Thanks you for commenting. It is certainly worth while remembering the old wisdom that you should not tune what is no problem on a business level. You crorrectly assume there was a perceived performance problem. We had to chnage indexing and cluster one table making it an IOT.
LikeLike