When a query contains a regular or inline view, there are 3 basic strategies for the optimizer to choose from:
1) merge the view (no “VIEW” operation in the plan)
2) instantiate the view as the whole and join it to the rest of the query (the plan shows a VIEW “operation”)
3) push join predicates inside the view (the plan shows “VIEW PUSHED PREDICATE”).
In my previous post I showed an example of how a query’s performance can be improved using the waste minimization technique. My focus was primarily on identifying and enforcing the correct plan, but I received some questions regarding the root cause of the problem: why the optimizer came up with a wrong join order? It’s a very interesting question, and it deserves a separate post so that it could be explored in detail.
Today I’d like to share another tuning example from a recent case at work, which in my opinion is good for illustrating typical steps involved in SQL optimization process.
I was handed a poorly performing query with a relatively verbose text, so I will only give the general structure here (it will also prevent me from accidentally disclosing some sensitive information from that application):
Continue reading “Query tuning by waste minimization: a real-life example”
A few weeks ago, I received a request to review an AWR report for a database suffering from instance-level performance issues. Here are the the key parts of that report (with some masking):
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
XXXX XXXXX XXXXX 1 10.2.0.5.0 NO XXXX
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 65115 03-May-16 11:00:09 152 17.8
End Snap: 65116 03-May-16 12:00:18 152 17.7
Elapsed: 60.16 (mins)
DB Time: 2,712.41 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 5,856M 5,856M Std Block Size: 8K
Shared Pool Size: 2,048M 2,048M Log Buffer: 14,340K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,455,599.10 14,087.84
Logical reads: 613,415.60 3,519.18
Block changes: 12,238.64 70.21
Physical reads: 12,233.70 70.19
Physical writes: 1,517.54 8.71
User calls: 1,159.19 6.65
Parses: 39,080.15 224.20
Hard parses: 32.45 0.19
Sorts: 708.22 4.06
Logons: 0.31 0.00
Executes: 39,393.06 226.00
Transactions: 174.31
...
Oracle Diagnostician 