Hi ,
I have sql which is not performing well as per the Performance team and upon analysing it , sometimes it is adaptive plan and the cost is less and some times it is non adaptive (using hash join) which is expensive.
Would any one please tell me how to force optimizer to use adaptive plan so that the optimizer will use the less expensive plan
I m using 12c
Below is the explain plan for both
Plan hash value: 1995525407
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41042 | 13M| 16728 (1)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 41042 | 13M| 16728 (1)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 41042 | 12M| 16727 (1)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 41042 | 10M| 8515 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL | USERS | 14329 | 615K| 170 (1)| 00:00:01 |
|* 5 | HASH JOIN RIGHT OUTER | | 41042 | 9258K| 8345 (1)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | SALES_LOG | 1 | 10 | 1 (0)| 00:00:01 |
|* 7 | INDEX FULL SCAN | I02_SALES_LOG | 1 | | 1 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 41042 | 8857K| 8344 (1)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | LOCATION_CODES_ML | 1062 | 37170 | 5 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED| REFERRAL | 42560 | 7730K| 8338 (1)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | I11_REFERRAL | 42909 | | 20 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PROSPECT | 1 | 33 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_PROSPECT | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | ACTION_LIST | 1 | 39 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_ACTION_LIST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("USERS"."USER_ID"(+)="REFERRAL"."ASSIGNED_TO")
5 - access("SALES_LOG"."REFERRAL_ID"(+)="REFERRAL"."REFERRAL_ID")
7 - filter("SALES_LOG"."REFERRAL_ID"(+) IS NOT NULL)
8 - access("LOCATION_CODES_ML"."LOCATION_CODE_ID"(+)="REFERRAL"."LOCATION_LIMIT")
9 - filter("LOCATION_CODES_ML"."LANGUAGE_ID"(+)=U'en')
10 - filter("REFERRAL"."REFERRAL_STATUS"=U'0' OR "REFERRAL"."REFERRAL_STATUS"=U'1' OR
"REFERRAL"."REFERRAL_STATUS"=U'11' OR "REFERRAL"."REFERRAL_STATUS"=U'2')
11 - access("REFERRING_CAMPAIGN"=1000772)
13 - access("PROSPECT"."PROSPECT_ID"(+)="REFERRAL"."PROSPECT_ID")
15 - access("ACTION_LIST"."PROSPECT_ID"(+)="PROSPECT"."PROSPECT_ID" AND
"ACTION_LIST"."REFERRAL_ID"(+)="REFERRAL"."REFERRAL_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 3 Sql Plan Directives used for this statement
--When changing the input to without quote
Plan hash value: 1986258508
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 | 8344 (1)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 347 | 8344 (1)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 1 | 303 | 8343 (1)| 00:00:01 |
| 3 | NESTED LOOPS OUTER | | 1 | 293 | 8342 (1)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 1 | 258 | 8341 (1)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 1 | 219 | 8340 (1)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| REFERRAL | 1 | 186 | 8339 (1)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | I11_REFERRAL | 42909 | | 20 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | PROSPECT | 1 | 33 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_PROSPECT | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | ACTION_LIST | 1 | 39 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_ACTION_LIST | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | LOCATION_CODES_ML | 1 | 35 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_LOCATION_CODES_ML | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | SALES_LOG | 1 | 10 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | I02_SALES_LOG | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | USERS | 1 | 44 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_USERS | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(TO_NUMBER("REFERRAL"."REFERRAL_STATUS")=0 OR TO_NUMBER("REFERRAL"."REFERRAL_STATUS")=1 OR
TO_NUMBER("REFERRAL"."REFERRAL_STATUS")=2 OR TO_NUMBER("REFERRAL"."REFERRAL_STATUS")=11)
7 - access("REFERRING_CAMPAIGN"=1000772)
9 - access("PROSPECT"."PROSPECT_ID"(+)="REFERRAL"."PROSPECT_ID")
11 - access("ACTION_LIST"."PROSPECT_ID"(+)="PROSPECT"."PROSPECT_ID" AND
"ACTION_LIST"."REFERRAL_ID"(+)="REFERRAL"."REFERRAL_ID")
13 - access("LOCATION_CODES_ML"."LOCATION_CODE_ID"(+)="REFERRAL"."LOCATION_LIMIT" AND
"LOCATION_CODES_ML"."LANGUAGE_ID"(+)=U'en')
15 - access("SALES_LOG"."REFERRAL_ID"(+)="REFERRAL"."REFERRAL_ID")
filter("SALES_LOG"."REFERRAL_ID"(+) IS NOT NULL)
17 - access("USERS"."USER_ID"(+)="REFERRAL"."ASSIGNED_TO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
- 3 Sql Plan Directives used for this statement