Slow HASH JOIN operation and unaccounted "Other Waits" in SQL Monitor Report
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
0