When using to bypass optimizer bug....
Dion Cho, June 03, 2008 - 12:25 am UTC
I think that it can be classified as "good hint" only when being used to bypass optimizer bug.
Altering optimizer parameter in session or system level to bypass optimizer bug?
It has been considered quite dangerous.
But with opt_param hint, we can finally narrow down the effect of parameter change to specific SQL level.
From this point of view, i believe that this hint should(but no always) be used with hidden parameters to control optimizer behavior.
thx
Matthias Rogel, June 03, 2008 - 1:44 am UTC
use_nl
Matthias Rogel, June 03, 2008 - 3:42 am UTC
interesting thing (for me) was (in the example above), that I knew, nested loop was the "correct" plan.
I tried use_nl but I did not succeed to force the optimizer to use the plan I wanted, don't know why - and don't want to know why either (I would never use use_nl in production - it is not our job to tell the optimizer how to do a query)
But opt_param looks promising in cases where default-parameters seem not adequate for a query, so I think before using outlines it might be an option to keep in mind also for production
June 03, 2008 - 11:22 am UTC
that is the #1 problem with hints that tell the optimizer "how to do something"
You have to be so very precise and detailed - Jonathan Lewis has some really good examples in his book "Cost Based Oracle" - hinting to tell the optimizer how to do something correctly is really not that easy.
OPT_PARAM with PARALLEL_MIN_PERCENT
Yuri Gorelik, April 30, 2009 - 8:55 am UTC
Hello Tom and thank you very much for the explanation,
I'm trying to use the opt_param hint with parallel_min_percent, to make a report execute only if it gets enough parallel slaves, but it doesn't seem to have any affect, the report still often executes serially, so I'm assuming that the hint is unsupported with this parameter.
Am I right in my assumption, and if so do I have any alternative to achieve what I'm trying to do without an "alter session"?
April 30, 2009 - 11:06 am UTC
parallel min percent isn't an optimizer thing - it is a runtime execution thing.
The optimizer has no clue if the statement will be able to get the number of resources it needs - not until we go to execute it do we know that. The optimizer would not cause this to 'fail' at compile time - it could not.
think about it - suppose it "could" do this - then, say at noon the optimizer parsed a query and generated a plan (did the optimization). At noon, we were the only ones on the machine. At noon, we had parallel min percent easily. At 1pm we rerun the same query. The optimizer is NOT executed because we found the sql in the shared pool - at 1pm, we have been joined by 1,000 other concurrent users - we are going to go serial - and the optimizer cannot 'stop us' because we didn't run it this time.
Naresh, October 30, 2012 - 5:43 am UTC
Hi Tom,
Can you please list the parameters which we can set by using opt_hint.
October 30, 2012 - 7:08 pm UTC
run a 10053 trace, it will dump out the optimizer parameters, most all of those can be set by this.
but, again, i'll reiterate that you shouldn't be using this. just alter your session to test with and since you wouldn't use this in real code - you don't need it.
opt_param use in prod
Ragesh, September 01, 2017 - 4:09 am UTC
We have a similar situation as explained in the original question. Our application has a search functionality where it uses dynamic SQLs and generate thousands of different SQL IDs in the system based on the search criteria. Most of these SQL executes pretty fine , but some of them takes significant time to execute. We tried to create SQL profiles with force matching and it has helped little bit, but since the variation in the SQL is more, the SQL profiles are not getting used always. The database was upgraded to 12c recently and we had to turn off optmizer_adatpive_feature to false as these queries have fallen off the track and caused major performance issue on the system. Also while working on this we tried OPT_PARM hint with optmizer_index_cost_adj set to value 1 and it has shown significant improvement. Since many years passed after oracle introduced this hint to public, just wanted to get your opinion on using this on production system now.
September 01, 2017 - 10:40 am UTC
Like most hints, you should avoid this in production SQL as much as possible.
As Tom said earlier, you need to be precise when using hints. Just using opt_param may not be enough to give you the plan you want.
And even if it does, you're handicapping the optimizer's choices in the future. As the data changes or you upgrade to newer versions, using this may prevent it using better plans.
Bug or expected behaviour?
Narendra, February 16, 2024 - 4:01 pm UTC
Hello Chris/Connor,
I am trying to use OPT_PARAM hint to influence specific SQL to use non-default value for CONTAINER_DATA parameter. While the explicit hint works fine, when I create a SQL Patch with the same hint then the hint does not appear to work, even though hint report claims hint was valid and notes claim that patch was used.
Would you be able to shed any light on whether this is expected behaviour or a bug? Why would this be considered as expected behaviour?
Below is my test case:
SQL> select version_full from v$instance ;
VERSION_FULL
-----------------
19.20.0.0.0
SQL> show parameter container_data
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
container_data string ALL
SQL> REM Original SQL
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from all_synonyms ;
COUNT(*)
----------
12437
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +HINT_REPORT -PREDICATE')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2t0jpay94qf4y, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(*) from all_synonyms
Plan hash value: 1158282781
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.17 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.17 | | | |
| 2 | VIEW | ALL_SYNONYMS | 1 | 21200 | 12437 |00:00:00.17 | | | |
| 3 | SORT UNIQUE | | 1 | 21200 | 12437 |00:00:00.17 | 1683K| 1078K| 1495K (0)|
| 4 | UNION-ALL | | 1 | | 12437 |00:00:00.02 | | | |
| 5 | FILTER | | 1 | | 12437 |00:00:00.02 | | | |
| 6 | PX COORDINATOR | | 1 | | 12437 |00:00:00.01 | 73728 | 73728 | |
| 7 | PX SEND QC (RANDOM) | :TQ20000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 8 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 9 | EXTENDED DATA LINK FULL | INT$DBA_SYNONYMS | 0 | 20000 | 0 |00:00:00.01 | | | |
| 10 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 11 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 12 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 13 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 14 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 16 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 18 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 19 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 20 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 21 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 23 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 25 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 26 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | | | |
| 27 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 28 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 29 | VIEW | _ALL_SYNONYMS_TREE | 1 | 20001 | 0 |00:00:00.11 | | | |
| 30 | CONNECT BY WITHOUT FILTERING | | 1 | | 0 |00:00:00.11 | 1024 | 1024 | |
| 31 | PX COORDINATOR | | 1 | | 0 |00:00:00.11 | 73728 | 73728 | |
| 32 | PX SEND QC (RANDOM) | :TQ50002 | 0 | 1 | 0 |00:00:00.01 | | | |
| 33 | HASH JOIN RIGHT SEMI BUFFERED | | 0 | 1 | 0 |00:00:00.01 | 5435K| 4181K| 9343K (0)|
| 34 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 956K| 956K| 424K (0)|
| 35 | PX RECEIVE | | 0 | 1190 | 0 |00:00:00.01 | | | |
| 36 | PX SEND HYBRID HASH | :TQ50000 | 0 | 1190 | 0 |00:00:00.01 | | | |
| 37 | STATISTICS COLLECTOR | | 1 | | 13349 |00:00:00.04 | | | |
| 38 | VIEW | VW_SQ_1 | 1 | 1190 | 13349 |00:00:00.01 | | | |
| 39 | FILTER | | 1 | | 13349 |00:00:00.01 | | | |
| 40 | PX COORDINATOR | | 1 | | 13349 |00:00:00.01 | 73728 | 73728 | |
| 41 | PX SEND QC (RANDOM) | :TQ30000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 42 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 43 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_AO | 0 | 20000 | 0 |00:00:00.01 | | | |
| 44 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 45 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 46 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 47 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 48 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 49 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 50 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 51 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 52 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 53 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 54 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 56 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 57 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 58 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 59 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | | | |
| 60 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 61 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 62 | PX RECEIVE | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 63 | PX SEND HYBRID HASH | :TQ50001 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 64 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 65 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 0 | 20000 | 0 |00:00:00.01 | | | |
| 66 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 73728 | 73728 | |
| 67 | PX SEND QC (RANDOM) | :TQ40000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 68 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 69 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 0 | 20000 | 0 |00:00:00.01 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
0 - SEL$DB9294E1
- NO_PUSH_SUBQ
39 - SEL$F2590D3C
- NO_PUSH_SUBQ
91 rows selected.
SQL> REM With OPT_PARAM hint for CONTAINER_DATA8 and it works
SQL> select /*+ GATHER_PLAN_STATISTICS OPT_PARAM('CONTAINER_DATA','CURRENT_DICTIONARY') */ count(*) from all_synonyms ;
COUNT(*)
----------
687
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +HINT_REPORT -PREDICATE')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g2j0jbd94tx9h, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS OPT_PARAM('CONTAINER_DATA','CURRENT_DI
CTIONARY') */ count(*) from all_synonyms
Plan hash value: 1892637848
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 2857 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 2857 | | | |
| 2 | VIEW | ALL_SYNONYMS | 1 | 49 | 687 |00:00:00.02 | 2857 | | | |
| 3 | SORT UNIQUE | | 1 | 49 | 687 |00:00:00.02 | 2857 | 95232 | 95232 |83968 (0)|
| 4 | UNION-ALL | | 1 | | 687 |00:00:00.01 | 2857 | | | |
| 5 | FILTER | | 1 | | 687 |00:00:00.01 | 1429 | | | |
| 6 | HASH JOIN | | 1 | 695 | 687 |00:00:00.01 | 1429 | 3530K| 3530K| 5259K (0)|
| 7 | INDEX FULL SCAN | I_USER2 | 1 | 217 | 217 |00:00:00.01 | 1 | 1028K| 1028K| |
| 8 | HASH JOIN | | 1 | 695 | 687 |00:00:00.01 | 1428 | 3098K| 3098K| 5270K (0)|
| 9 | TABLE ACCESS STORAGE FULL | USER$ | 1 | 217 | 217 |00:00:00.01 | 15 | 1028K| 1028K| |
| 10 | HASH JOIN | | 1 | 695 | 687 |00:00:00.01 | 1413 | 2204K| 2204K| 4786K (0)|
| 11 | TABLE ACCESS STORAGE FULL | SYN$ | 1 | 695 | 687 |00:00:00.01 | 7 | 1028K| 1028K| |
| 12 | TABLE ACCESS STORAGE FULL | OBJ$ | 1 | 12514 | 12514 |00:00:00.01 | 1406 | 1028K| 1028K| |
| 13 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 14 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 15 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 16 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 17 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 19 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 21 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 22 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 23 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 24 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 25 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 26 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 27 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 28 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 29 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 30 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 31 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 32 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 33 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 35 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 36 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 37 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 38 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 39 | VIEW | _ALL_SYNONYMS_TREE | 1 | 2 | 0 |00:00:00.01 | 1428 | | | |
| 40 | CONNECT BY NO FILTERING WITH START-WITH | | 1 | | 0 |00:00:00.01 | 1428 | 1024 | 1024 | |
| 41 | FILTER | | 1 | | 0 |00:00:00.01 | 1428 | | | |
| 42 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.01 | 1428 | | | |
| 43 | HASH JOIN | | 1 | 2 | 0 |00:00:00.01 | 1428 | 1617K| 1617K| 576K (0)|
| 44 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.01 | 1428 | | | |
| 45 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.01 | 1428 | | | |
| 46 | NESTED LOOPS | | 1 | 2 | 0 |00:00:00.01 | 1428 | | | |
| 47 | HASH JOIN | | 1 | 2 | 0 |00:00:00.01 | 1428 | 3098K| 3098K| 5316K (0)|
| 48 | TABLE ACCESS STORAGE FULL | USER$ | 1 | 217 | 217 |00:00:00.01 | 15 | 1028K| 1028K| |
| 49 | HASH JOIN | | 1 | 372 | 669 |00:00:00.01 | 1413 | 2384K| 2384K| 4284K (0)|
| 50 | JOIN FILTER CREATE | :BF0000 | 1 | 695 | 687 |00:00:00.01 | 7 | | | |
| 51 | TABLE ACCESS STORAGE FULL | SYN$ | 1 | 695 | 687 |00:00:00.01 | 7 | 1028K| 1028K| |
| 52 | JOIN FILTER USE | :BF0000 | 1 | 12514 | 714 |00:00:00.01 | 1406 | | | |
| 53 | TABLE ACCESS STORAGE FULL | OBJ$ | 1 | 12514 | 714 |00:00:00.01 | 1406 | 1028K| 1028K| |
| 54 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 55 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 56 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 57 | INDEX FULL SCAN | I_USER2 | 0 | 217 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 58 | TABLE ACCESS CLUSTER | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 59 | INDEX UNIQUE SCAN | I_USER# | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 60 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 61 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 62 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 63 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 64 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 65 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 66 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 67 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 68 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 69 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 70 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 71 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 72 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 73 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 74 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 75 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 76 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 77 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 78 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 79 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 80 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 81 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 82 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 83 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 84 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 85 | TABLE ACCESS BY INDEX ROWID | SYN$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 86 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 87 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 88 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 89 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 90 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 91 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 92 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 93 | FILTER | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 94 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 95 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 96 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 97 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 98 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 99 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0
Re: Bug or Expected Behaviour?
Narendra, February 16, 2024 - 4:05 pm UTC
For some reason unknown to me, could not see all the test case in my previous post....here is the remaining part
| 100 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 101 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 102 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 103 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 104 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 105 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 106 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 107 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 108 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 109 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 110 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 111 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 112 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 113 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 114 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 115 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 116 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 117 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 118 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 119 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 120 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 121 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 122 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 123 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
| 124 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 | 1028K| 1028K| |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('CONTAINER_DATA','CURRENT_DICTIONARY')
0 - SEL$5174B98E
- NO_PUSH_SUBQ
74 - SEL$7DCA4A15
- NO_PUSH_SUBQ
Note
-----
- this is an adaptive plan
154 rows selected.
SQL> REM Created a sql patch for original sql
SQL> DECLARE
2 l_patch VARCHAR2(100) := 'CONDATA_PATCH' ;
3 BEGIN
4 l_patch := dbms_sqldiag.create_sql_patch(sql_id=>'2t0jpay94qf4y', hint_text=>'OPT_PARAM(''CONTAINER_DATA'',''CURRENT_DICTIONARY'')', name => 'CONDATA_PATCH');
5 -- dbms_sqldiag.drop_sql_patch(/*min sig:*/name=>l_patch/*varchar2*/);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> REM Reran Original SQL (Hint Report and Outline claims the OPT_PARAM was honoured but output and execution plan confirms the hint had no effect)
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*) from all_synonyms ;
COUNT(*)
----------
12437
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +HINT_REPORT -PREDICATE')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2t0jpay94qf4y, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ count(*) from all_synonyms
Plan hash value: 1158282781
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.14 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.14 | | | |
| 2 | VIEW | ALL_SYNONYMS | 1 | 21200 | 12437 |00:00:00.14 | | | |
| 3 | SORT UNIQUE | | 1 | 21200 | 12437 |00:00:00.14 | 1683K| 1078K| 1495K (0)|
| 4 | UNION-ALL | | 1 | | 12437 |00:00:00.01 | | | |
| 5 | FILTER | | 1 | | 12437 |00:00:00.01 | | | |
| 6 | PX COORDINATOR | | 1 | | 12437 |00:00:00.01 | 73728 | 73728 | |
| 7 | PX SEND QC (RANDOM) | :TQ20000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 8 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 9 | EXTENDED DATA LINK FULL | INT$DBA_SYNONYMS | 0 | 20000 | 0 |00:00:00.01 | | | |
| 10 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 11 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 12 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 13 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 14 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 16 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 17 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 18 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 19 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 20 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 21 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 23 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 25 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 26 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | | | |
| 27 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 28 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 29 | VIEW | _ALL_SYNONYMS_TREE | 1 | 20001 | 0 |00:00:00.09 | | | |
| 30 | CONNECT BY WITHOUT FILTERING | | 1 | | 0 |00:00:00.09 | 1024 | 1024 | |
| 31 | PX COORDINATOR | | 1 | | 0 |00:00:00.09 | 73728 | 73728 | |
| 32 | PX SEND QC (RANDOM) | :TQ50002 | 0 | 1 | 0 |00:00:00.01 | | | |
| 33 | HASH JOIN RIGHT SEMI BUFFERED | | 0 | 1 | 0 |00:00:00.01 | 5435K| 4181K| 9343K (0)|
| 34 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 956K| 956K| 424K (0)|
| 35 | PX RECEIVE | | 0 | 1190 | 0 |00:00:00.01 | | | |
| 36 | PX SEND HYBRID HASH | :TQ50000 | 0 | 1190 | 0 |00:00:00.01 | | | |
| 37 | STATISTICS COLLECTOR | | 1 | | 13349 |00:00:00.01 | | | |
| 38 | VIEW | VW_SQ_1 | 1 | 1190 | 13349 |00:00:00.01 | | | |
| 39 | FILTER | | 1 | | 13349 |00:00:00.01 | | | |
| 40 | PX COORDINATOR | | 1 | | 13349 |00:00:00.01 | 73728 | 73728 | |
| 41 | PX SEND QC (RANDOM) | :TQ30000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 42 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 43 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_AO | 0 | 20000 | 0 |00:00:00.01 | | | |
| 44 | FILTER | | 0 | | 0 |00:00:00.01 | | | |
| 45 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 46 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 47 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | | | |
| 48 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 49 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 50 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 51 | INDEX RANGE SCAN | I_OBJ5 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 52 | INDEX RANGE SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 53 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 54 | FIXED TABLE FULL | X$KZSRO | 0 | 1 | 0 |00:00:00.01 | | | |
| 55 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 56 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 57 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | | | |
| 58 | INDEX RANGE SCAN | I_USER_EDITIONING | 0 | 2 | 0 |00:00:00.01 | 1028K| 1028K| |
| 59 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | | | |
| 60 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 61 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 1028K| 1028K| |
| 62 | PX RECEIVE | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 63 | PX SEND HYBRID HASH | :TQ50001 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 64 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 65 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 0 | 20000 | 0 |00:00:00.01 | | | |
| 66 | PX COORDINATOR | | 0 | | 0 |00:00:00.01 | 73728 | 73728 | |
| 67 | PX SEND QC (RANDOM) | :TQ40000 | 0 | 20000 | 0 |00:00:00.01 | | | |
| 68 | PX PARTITION LIST ALL | | 0 | 20000 | 0 |00:00:00.01 | | | |
| 69 | EXTENDED DATA LINK FULL | _INT$_ALL_SYNONYMS_FOR_SYN | 0 | 20000 | 0 |00:00:00.01 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('CONTAINER_DATA','CURRENT_DICTIONARY')
0 - SEL$DB9294E1
- NO_PUSH_SUBQ
39 - SEL$F2590D3C
- NO_PUSH_SUBQ
Note
-----
- SQL patch "CONDATA_PATCH" used for this statement
February 20, 2024 - 2:58 pm UTC
I'm not sure what's going on here; take this up with support.