During a briefing at the beginning of a tuning assignment the IT staff of a Swiss company describes their problem: “Our new application runs in Switzerland and also as a local installation of our subsidiary in the US. Both installations are served by our local database in Switzerland . The Step in question in this application is running with us 10 Seconds in Switzerland, in the US, it takes about 2 minutes. We have a trace of the application, but we can not find the problem. Granted, there are thousands of SQL statements that we work through, but each one is fast and their total time is about 10 seconds.”
What do you think is the cause of the problem? How would you verify your assumption? What could be a short-term solution? How should a long-term solution look like?
The buffer gets far right show us the strength of the activity on the corresponding segments.
The list is sorted by Buffergets, which means the segments with the highest activity are on top.
We can actually be pretty sure that theses segments are also the most central and most important of the whole application since they are showing the highest activity. (If a database corresponds to an application.)
This should be evident: the numbers in the report are important.
Normally we would expect that the physical reads behave proportional to the buffer gets. According to the rule of thumb that maximal 10% of buffer gets should be converted into physical reads. In reality, there is very often a concentration of physical reads on a few segments .
These segments, which have a disproportionate number of physical reads exhibit a bottleneck in physical design. Potential reasons are amongst others missing index or non-clustered data.
A exhaustive research of all execution plans which reference the bottleneck segments did reveal the root causes.
I will lay down in the log my experience in building a AWR warehouse. Experiences I believe being potenially useful for other Oracle users.
I will write a no general introduction into the ARW warehouse. To find out what it is and why you need it, I would, for example, refer to the blog of Kellyn Pot’Vin Gorman: http://dbakevlar.com/2014/06/awr-warehouse-in-em12c-rel-4/.
Day 1 (12.24.2014)
When the databases are linked with the AWR warehouse , PL / SQL code is installed in the user DBSNMP. In some databases we encountered compiling errors.
We found that these databases were hardened . That means all rights not currently needed are revoked from users . We had to assign the following rights to user DBSNMP:
grant execute on utl_file to dbsnmp;
grant execute on DBMS_RANDOM to dbsnmp;
grant execute on DBMS_SCHEDULER to dbsnmp;
The rights to the databases in the AWR warehouse we granted over SSH. For a single node database it was issue free, at a cluster database the option was not offered. The reason is an error in the GUI to be corrected in the next patch. For the time being you can use EMCLI as a workaround.
Speaking EMCLI. The adminstration of the AWR warehouse via EMCLI is only partialy possible. This should also change with the next patch due in December 2014.