Why ALL Tools in at Your Disposal ARE IMPORTANT
After my AWR Warehouse session at DOAG in Nuremberg, Germany last week, an attendee asked me if he really needed to use trace files anymore with the value provided by AWR, (Automatic Workload Repository) and ASH, (Active Session History.) I responded that trace files were incredibly valuable and should never be discounted.
To use an analogy, when I was 22 years old, and entered a doctor’s office missing a small percentage of my visual field after an apparent migraine, doctors used a number of different “tools” at their disposal in an attempt to diagnose what had happened. They first started with an Ophthalmoscope to determine the health of my retinas. They assumed that I was simply experiencing visual disturbances due to a migraine and sent me home with a prescription strength case of Tylenol after verifying that my eyes were healthy.
After no change with another two days passed, the doctors then proceeded with a Computed Tomography Scan, aka CAT/CT scan. This is a standard first line inspection of the brain and again, it resulted with no information as to what was causing the visual disturbance. If it hadn’t been for a neurologist that was on duty at the hospital, I may very well have been sent home again. He asked me about the severe migraine, the actual specifics of the experience and suddenly realized he was hearing someone describe to him the symptoms of a stroke. No one had considered to ask what had happened and since I was in my early twenties, hadn’t considered this. At this point, the doctor asked for me to have an MRI, (Magnetic Resonance Imaging) with contract. An injection of gadolinium contrast resulted in certain tissues and abnormalities more clearly visible and for me, it showed that I had experienced a break in the blood vessels in the back right of my brain, issuing a small aneurysm, but suffering only a little blood loss. The main damage was to the brain tissue in the back area of my brain which “translates” the information being sent from my optic nerve and this was the cause of my left side visual field loss. Due to this new information, he was able to start the research that in the coming years saved my life.
This doctor, like a database administrator or developer, knew to use the right tool for the job once he knew what he was up against and this is how we succeed when others fail in the technical industry. To be told that you no longer need a tool such as tracing because valuable data is provided from AWR or ASH limits the ability to see real culprits that the secondary tools discussed weren’t really designed to capture.
To know when to use one tool over the other is often the biggest challenge. A number of years back, a customer was experiencing terrible, overall performance issues in an ETL process that no one had been able to pinpoint. I always love a good challenge and began to look at it via AWR reports. I noticed that there were waits that were unaccounted for in the SQL processing.
Execution Plan
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 688 | | |
| 1 | DELETE | _ITEM_MONTH_ROLLUP | | | | | |
| 2 | NESTED LOOPS | | 1490 | 86420 | 688 | | |<--This one doesn't add up to time/value consumed.
| 3 | VIEW | VW_NSO_1 | 1 | 26 | 20 | | |
| 4 | SORT UNIQUE | | 1 | 40 | 20 | | |
| 5 | TABLE ACCESS BY INDEX ROWID| PROCESS_LOG_MASTER | 1 | 40 | 4 | | |
| 6 | INDEX RANGE SCAN | PROCESS_LOG_MASTER_I7 | 132 | | 1 | | |
| 7 | PARTITION RANGE ITERATOR | | 1490 | 47680 | 386 | KEY | KEY |
| 8 | INDEX RANGE SCAN | ITEM_MONTH_ROLLUP_I2 | 1490 | 47680 | 386 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------
Note
—–
– dynamic sampling used for this statement (level=8)
– SQL profile “SYS_SQLPROF_013dad8465770005” used for this statement
CPU Costing is off and there is a profile on this statement. Stats are up to date, why am I seeing dynamic sampling level 8??
I proceeded to capture a trace and that’s when I was able to demonstrate that due to auto-DOP in 11.2, a choice was made to perform dynamic sampling that was adding over 80% to the execution time, impacting performance significantly.
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring blah, blah, blah, blah... :)
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 28.60 48.52 322895 43776 489123 189 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 28.60 48.52 322895 43776 489123 189
Limiting your tools, no matter if you limit yourself to just using tracing or AWR/ASH, SLOB, Snapper or any other tool is a poor choice. Use the right tool for the situation that provides answer to the questions the business relies on you for. You wouldn’t trust a doctor that used a CAT scan to check a broken bone vs. an X-ray, so why would you trust an optimization specialist that limits themselves to one set of tools?
