I always emphasize on understanding data and business domain for query optimization. One must have technical skills in combination with the domain knowledge. Other than the blend, who could tune at this level!
We planned to tune some of the queries where we use literals and can't use bind variables as those are inside 'IN clause', and we don't exactly know how many values would be there (dynamically added by the application). We decided to replace the literals in 'IN clause' by Global Temporary Table (GTT). That change made every other queries perform better or at least same as before other than the SQL I am going to talk below. Changing literals in 'IN clause' caused to SQL to come up with very wrong execution plan and blundered our system's stability - the frequency of execution was very high for the SQL, we could not afford the bad plan.
After analyzing the plan, I found that the SQL with Global Temporary Table (GTT) doing unexpected Nested Loops at the very early state of execution order which was causing more data to process in other steps for the result. We know that, the join with GTT should be done at the later or last stage of execution order. And the best way (for the best selectivity) is to start execution with the underlying table
mv_caseload_priv (through the index MV_PGM_CASE_I) we have in the
client_access_role view. I drew the plan by hand and attached below for a clear understanding what was going on. The Nested Loop (NL, marked as red circle) was very unexpected, also the execution start point (marked as red cross) was not optimum.
The original SQL and the plan, only problem was not having bind variables, sometimes this
ended up with terrible execution plan for some bind values in the SQL, was uncontrollable.
SQL > SELECT DISTINCT login_id
FROM client_access_role car
WHERE car.pgm_id = :pgm
AND car.role IN (21,22,112,......);
Elapsed: 00:00:00.11
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11900 | 429K| 678 (1)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 11900 | 429K| 678 (1)| 00:00:01 | | |
|* 2 | HASH JOIN | | 11900 | 429K| 676 (1)| 00:00:01 | | |
| 3 | INLIST ITERATOR | | | | | | | |
|* 4 | INDEX RANGE SCAN | AR_ROLE_ACL_I | 16724 | 130K| 31 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 9589 | 271K| 643 (1)| 00:00:01 | | |
| 6 | MAT_VIEW ACCESS BY GLOBAL INDEX ROWID| MV_CASELOAD_PRIV | 279 | 3348 | 83 (0)| 00:00:01 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | MV_CASE_PRIV_PGM_I | 279 | | 3 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | CA_CASELOAD_LOGIN_ACL_I | 34 | 578 | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CA"."ACL_ID"="R"."ACL_ID")
4 - access("R"."ROLE"=21 OR "R"."ROLE"=22 OR "R"."ROLE"=112)
7 - access("CP"."PGM_ID"=TO_NUMBER(:PGM))
8 - access("CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
97 consistent gets
Replaced 'IN clause' with a Global Temporary Table for generating fixed sort of plan, which
backfired, caused severe performance problem for the unexpected terrible plan.
SQL> SELECT DISTINCT login_id
FROM client_access_role car
WHERE car.pgm_id = :pgm
AND car.role IN (SELECT value FROM gtt_in_list_2);
Elapsed: 00:00:18.98
Execution Plan
----------------------------------------------------------
Plan hash value: 1525097291
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 34 (3)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 50 | 34 (3)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 50 | 33 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 38 | 32 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 21 | 1 (0)| 00:00:01 | | |
| 5 | INDEX FULL SCAN | GTT2_VAL_I | 1 | 13 | 0 (0)| 00:00:01 | | |
| 6 | TABLE ACCESS BY INDEX ROWID| ACL_ROLE | 1 | 8 | 1 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | AR_ROLE | 3787 | | 1 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | CASELOAD_ACCESS | 137 | 2329 | 31 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | CASE_ACC_ACL | 137 | | 2 (0)| 00:00:01 | | |
| 10 | PARTITION HASH ITERATOR | | 1 | 12 | 1 (0)| 00:00:01 | KEY | KEY |
|* 11 | INDEX RANGE SCAN | MV_PGM_CASE_I | 1 | 12 | 1 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("R"."ROLE"="VALUE")
9 - access("CA"."ACL_ID"="R"."ACL_ID")
11 - access("CP"."PGM_ID"=TO_NUMBER(:PGM) AND "CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2817221 consistent gets
The tuned SQL by 'ORDERED' hint to guide the optimizer doing correct join order as we know best.
SQL> SELECT /*+ ORDERED */ DISTINCT login_id
FROM client_access_role c
JOIN gtt_in_list_2 x ON (c.role = x.value)
WHERE c.pgm_id = :pgm;
Elapsed: 00:00:00.08
Execution Plan
----------------------------------------------------------
Plan hash value: 3611759149
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 771 (7)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 50 | 771 (7)| 00:00:01 | | |
|* 2 | HASH JOIN | | 1 | 50 | 770 (7)| 00:00:01 | | |
| 3 | INDEX FULL SCAN | GTT2_VAL_I | 1 | 13 | 0 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 311K| 10M| 762 (6)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 9589 | 271K| 643 (1)| 00:00:01 | | |
| 6 | MAT_VIEW ACCESS BY GLOBAL INDEX ROWID| MV_CASELOAD_PRIV | 279 | 3348 | 83 (0)| 00:00:01 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | MV_CASE_PRIV_PGM_I | 279 | | 3 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | CA_CASELOAD_LOGIN_ACL_I | 34 | 578 | 2 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL | ACL_ROLE | 571K| 4467K| 105 (23)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R"."ROLE"="X"."VALUE")
4 - access("CA"."ACL_ID"="R"."ACL_ID")
7 - access("CP"."PGM_ID"=TO_NUMBER(:PGM))
8 - access("CP"."CASELOAD_ID"="CA"."CASELOAD_ID")
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
705 consistent gets
And we are back with the super performance!
***
