Hello,
In a 11.2.0.4 database, I am witnessing a slow performance for a set of similar sql statements that are using similar execution plan. I am struggling to interpret the statistics reported by SQL Monitor report and hence struggling to diagnose and come up with a possible fix.
SQL Monitor Report:
Global Stats
====================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
====================================================================================================
| 44 | 24 | 0.06 | 0.02 | 0.01 | 19 | 1 | 11527 | 35 | 29MB |
====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=12027518)
============================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
============================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | LOOKUPTAB | 1 | 2 | | | | | | | |
| 2 | INDEX UNIQUE SCAN | PK_LOOKUPTAB | 1 | 1 | | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | LOOKUPTAB | 1 | 2 | | | | | | | |
| 4 | INDEX UNIQUE SCAN | PK_LOOKUPTAB | 1 | 1 | | | | | | | |
| 5 | SORT ORDER BY | | 1 | 248K | 34 | +11 | 1 | 7 | 6144 | | |
| 6 | FILTER | | | | 34 | +11 | 1 | 7 | | | |
| 7 | MERGE JOIN CARTESIAN | | 1 | 248K | 34 | +11 | 1 | 7 | | | |
| 8 | HASH JOIN | | 1 | 248K | 44 | +1 | 1 | 7 | 1M | 100.00 | Cpu (44) |
| 9 | VIEW | | 58 | 18 | 1 | +11 | 1 | 5419 | | | |
| 10 | SORT UNIQUE | | 58 | 18 | 1 | +11 | 1 | 5419 | 950K | | |
| 11 | UNION-ALL | | | | 1 | +11 | 1 | 5419 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | REFDATATAB | 57 | 11 | 1 | +11 | 1 | 5418 | | | |
| 13 | INDEX RANGE SCAN | REFDATATABPK | 57 | 5 | 1 | +11 | 1 | 5418 | | | |
| 14 | COUNT STOPKEY | | | | 1 | +11 | 1 | 1 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | REFDATATAB | 1 | 5 | 1 | +11 | 1 | 1 | | | |
| 16 | INDEX RANGE SCAN | REFDATATABPK | 1 | 4 | 1 | +11 | 1 | 1 | | | |
| 17 | PARTITION HASH SINGLE | | 8417 | 248K | 34 | +11 | 1 | 5798 | | | |
| 18 | HASH JOIN OUTER | | 8417 | 248K | 34 | +11 | 1 | 5798 | 5M | | |
| 19 | HASH JOIN OUTER | | 8417 | 151K | 1 | +11 | 1 | 5798 | 5M | | |
| 20 | HASH JOIN | | 8417 | 86740 | 1 | +11 | 1 | 5798 | 3M | | |
| 21 | TABLE ACCESS FULL | PARENTTRANTBL | 8417 | 145 | 1 | +11 | 1 | 5798 | | | |
| 22 | TABLE ACCESS FULL | CHILDTRANTBL1 | 5M | 73318 | 1 | +11 | 1 | 7176 | | | |
| 23 | TABLE ACCESS FULL | CHILDTRANTBL2 | 1M | 64405 | 1 | +11 | 1 | 5134 | | | |
| 24 | TABLE ACCESS FULL | CHILDTRANTBL3 | 4M | 96173 | 1 | +11 | 1 | 2042 | | | |
| 25 | BUFFER SORT | | 1 | 248K | 34 | +11 | 7 | 7 | 2048 | | |
| 26 | FAST DUAL | | 1 | 2 | 1 | +11 | 1 | 1 | | | |
============================================================================================================================================================================
The "Global Stats" section is reporting 24 seconds of CPU time and 19 seconds of "Other Waits". But the execution plan statistics is reporting that the HASH JOIN on line 8 is spending all of 44 seconds of CPU time.
My questions are:
What exactly are the "Other Waits" where sql is spending significant amount of time?
Why is HASH JOIN on line 8 is having to spend 44 seconds (or 24 seconds if we go by Global Stats), especially when the 2 result sets are of less than 6000 records?
Happy to provide any more details if needed.
Thanks in advance