For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hi DBA,
maybe the optimizer can't realize that the list of msg_id coming from inlist-select will be short, don't know how selective this part really is.
Without the hint, the optimizer choose index XIE11FNM_VSBL_MSG using 3 columns with single literals. Not a bad idea at all.
What happens if combine these two indexes ?
Try an index like this
new_FNM_VSBL_MSG ( MSG_TYP_CD, MSG_CAPTR_STG_CD, SRCH_4_FLD_VAL, MSG_ID )
This will get you the columns from XIE11 plus the msg_id which you need to restrict the result set.
regards
Kay
The output from your "Good Plan" reports "Cardinality feedback used" - this may explain why the optimizer CHOSE to use the index you think you had ordered: the optimizer had acquired information that the collection consisted of just one row. The "Bad Plan" shows the default (for 8KB blocks) 8168 rows which is why it doesn't use the collection as the build table in the hash join.
Execute the code to produce the good plan again but use the 'outline' format option to generate the full set of hints for the plan - there's likely to be at least 15 or them - and show them to us. You will need to create a profile holding most of them for Oracle to reproduce the plan you want.
Alternatively include the 'alias' format option in the call to dbms_xplan so that we can work out the fully qualified aliases that Oracle gives to the various query blocks and tables and we may be able to work out a "legal" minimum set. This will include an UNNEST for the "in subquery", a NO_UNNEST, NO_PUSH_SUBQ for the "= max" subquery, a LEADING, 2 USE_NL, and an INDEX() hint for the main query block.
Another possibility - which may work and would be a better strategic option - is to take action on the statistics:
A final thought - does your production query run with bind variables or with the literals you've supplied ? You may be asking us to solve a problem that isn't the same as the one that needs to be solved.
Regards
Jonathan Lewis
[Edited to correct several typos]
Now, when the bad SQL is executed from the app, I can see it picked up the profile I created but not the underlying index from the profile. It still uses bad index and timing out..
How did you determine this?
It's not usual for a profile to be matched but only partially applied - that would normally, but not always, indicate that the profile hints were incomplete/wrong somehow.
Cardinality feedback seems to be adjusting is the estimates in your collection, FNM_GN_IN_STRING_LIST , which default to 8168.
If you had control of the SQL I would suggest adding a CARDINALITY hint to adjust the number of rows lower - depending on how many elements the application can put into the collection and assuming that a static lower estimated cardinality delivered a plan which worked for all circumstances.
Or, you'd need to create a function as a wrapper around your type and then use extensible optimizer to associate statistics with that function - that means changing the SQL which means you could just add a hardcoded cardinality hint..
Otherwise you could potentially use a SQL profile to adjust the cardinality estimate using OPT_ESTIMATE (undocumented) which would be similar to using a profile to force the index... in which case back to my first question.
Jon,
Thank you. Below is the 'outline' portion of the good execution plan.
This time I created profile with full set of hints from the outline, also attaching the syntax I used to create the profile. Still, no luck enforcing the cheaper index.
I created extended stats, re-gathered stats after creating ext. stats, but SQL from the application is still going for the bad index.
I see we have "frequency" histograms on the _CD col's. (NUM_DISTINCT for MSG_TYP_CD and MSG_CAPTR_STG_CD are 20 and 5 respectively, and NUM_BUCKETS 17 and 5).
The business folks are choosing the Invoice # (literal values) from drop down and submitting in the UI. This means query is using bind variables, correct ? (Also, I can see bind variables in the SQL execution..)
For some Invoice #'s, SQL seems to run fine.. But for the one in question.. it's timing out.
I truly admire your approach in going after the actual root cause and try to understand Optimizer's behavior, rather than applying band-aid hints.
In this case, if feasible, I would like to understand what I am dong incorrectly with the profile ?
select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ MSG.MSG_ID, MSG.VSBL_MSG_ID, MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL, MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL,MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROMFNM.FNM_VSBL_MSG MSG, FNM.BCS_INV_RESEND_TRK TRK WHEREMSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD ='PRE_BCS' AND MSG.SRCH_4_FLD_VAL = '123456' AND (('INVOICENUMBER' ='INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROMTABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))) OR('INVOICENUMBER' = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT * FROM TABLE(CAST(FNM_GN_IN_STRING_LIST('') ASTABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND(TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECTMAX(TRK1.RESEND_DT) FROM FNM.BCS_INV_RESEND_TRK TRK1 WHERETRK1.INV_NUM =Plan hash value: 1944127456-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 46 (100)| || 1 | SORT ORDER BY | | 1 | 2158 | 46 (5)| 00:00:01 ||* 2 | FILTER | | | | | || 3 | NESTED LOOPS OUTER | | 1 | 2158 | 45 (3)| 00:00:01 || 4 | NESTED LOOPS | | 1 | 2141 | 44 (3)| 00:00:01 || 5 | VIEW | VW_NSO_1 | 1 | 2002 | 36 (0)| 00:00:01 || 6 | HASH UNIQUE | | 1 | 2 | | || 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 36 (0)| 00:00:01 ||* 8 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 139 | 7 (0)| 00:00:01 ||* 9 | INDEX RANGE SCAN | XIE2FNM_VSBL_MSG | 4 | | 3 (0)| 00:00:01 ||* 10 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | 17 | 1 (0)| 00:00:01 || 11 | SORT AGGREGATE | | 1 | 17 | | || 12 | FIRST ROW | | 1 | 17 | 2 (0)| 00:00:01 ||* 13 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 17 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------------------Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS FORCE_XML_QUERY_REWRITE XML_DML_RWT_STMT XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT NO_COST_XML_QUERY_REWRITE OUTLINE_LEAF(@"SEL$7") OUTLINE_LEAF(@"SEL$A8541665") OUTLINE_LEAF(@"SEL$B7274CD5") UNNEST(@"SEL$07BDC5B4" UNNEST_SEMIJ_VIEW) OUTLINE(@"SEL$07BDC5B4") MERGE(@"SEL$4") OUTLINE(@"SEL$F5BB74E1") MERGE(@"SEL$2") OUTLINE(@"SEL$3") OUTLINE(@"SEL$4") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") NO_ACCESS(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5") INDEX_RS_ASC(@"SEL$B7274CD5" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM" "FNM_VSBL_MSG"."MSG_TYP_CD")) INDEX(@"SEL$B7274CD5" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT")) LEADING(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5" "MSG"@"SEL$2" "TRK"@"SEL$2") USE_NL(@"SEL$B7274CD5" "MSG"@"SEL$2") USE_NL(@"SEL$B7274CD5" "TRK"@"SEL$2") SEMI_TO_INNER(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5") FULL(@"SEL$A8541665" "KOKBF$0"@"SEL$4") USE_HASH_AGGREGATION(@"SEL$A8541665") INDEX(@"SEL$7" "TRK1"@"SEL$7" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT")) END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=)) 8 - filter(("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS')) 9 - access("MSG"."MSG_ID"="COLUMN_VALUE" AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE') filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE') 10 - access("MSG"."MSG_ID"="TRK"."INV_NUM") 13 - access("TRK1"."INV_NUM"=:B1)Note----- - cardinality feedback used for this statement94 rows selected.
select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ MSG.MSG_ID,
MSG.VSBL_MSG_ID, MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL,
MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL,
MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,
MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROM
FNM.FNM_VSBL_MSG MSG, FNM.BCS_INV_RESEND_TRK TRK WHERE
MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD =
'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = '123456' AND (('INVOICENUMBER' =
'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROM
TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))) OR
('INVOICENUMBER' = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT
* FROM TABLE(CAST(FNM_GN_IN_STRING_LIST('') AS
TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND
(TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT
MAX(TRK1.RESEND_DT) FROM FNM.BCS_INV_RESEND_TRK TRK1 WHERE
TRK1.INV_NUM =
Plan hash value: 1944127456
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 46 (100)| |
| 1 | SORT ORDER BY | | 1 | 2158 | 46 (5)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 2158 | 45 (3)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2141 | 44 (3)| 00:00:01 |
| 5 | VIEW | VW_NSO_1 | 1 | 2002 | 36 (0)| 00:00:01 |
| 6 | HASH UNIQUE | | 1 | 2 | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 36 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 139 | 7 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | XIE2FNM_VSBL_MSG | 4 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | 17 | 1 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | | 1 | 17 | | |
| 12 | FIRST ROW | | 1 | 17 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 17 | 2 (0)| 00:00:01 |
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
FORCE_XML_QUERY_REWRITE
XML_DML_RWT_STMT
XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT
NO_COST_XML_QUERY_REWRITE
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$A8541665")
OUTLINE_LEAF(@"SEL$B7274CD5")
UNNEST(@"SEL$07BDC5B4" UNNEST_SEMIJ_VIEW)
OUTLINE(@"SEL$07BDC5B4")
MERGE(@"SEL$4")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")
INDEX_RS_ASC(@"SEL$B7274CD5" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM"
"FNM_VSBL_MSG"."MSG_TYP_CD"))
INDEX(@"SEL$B7274CD5" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))
LEADING(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5" "MSG"@"SEL$2" "TRK"@"SEL$2")
USE_NL(@"SEL$B7274CD5" "MSG"@"SEL$2")
USE_NL(@"SEL$B7274CD5" "TRK"@"SEL$2")
SEMI_TO_INNER(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")
FULL(@"SEL$A8541665" "KOKBF$0"@"SEL$4")
USE_HASH_AGGREGATION(@"SEL$A8541665")
INDEX(@"SEL$7" "TRK1"@"SEL$7" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=))
8 - filter(("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))
9 - access("MSG"."MSG_ID"="COLUMN_VALUE" AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')
filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')
10 - access("MSG"."MSG_ID"="TRK"."INV_NUM")
13 - access("TRK1"."INV_NUM"=:B1)
Note
-----
- cardinality feedback used for this statement
94 rows selected.
Profile creation -
DECLARESQL_FTEXT CLOB;BEGINSELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '5w8mvk838bas2';DBMS_SQLTUNE.IMPORT_SQL_PROFILE( SQL_TEXT => SQL_FTEXT, PROFILE => SQLPROF_ATTR( 'IGNORE_OPTIM_EMBEDDED_HINTS', 'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')', 'DB_VERSION(''11.2.0.4'')', 'ALL_ROWS', 'FORCE_XML_QUERY_REWRITE', 'XML_DML_RWT_STMT', 'XMLINDEX_REWRITE', 'XMLINDEX_REWRITE_IN_SELECT', 'NO_COST_XML_QUERY_REWRITE', 'OUTLINE_LEAF(@"SEL$7")', 'OUTLINE_LEAF(@"SEL$A8541665")', 'OUTLINE_LEAF(@"SEL$B7274CD5")', 'UNNEST(@"SEL$07BDC5B4" UNNEST_SEMIJ_VIEW)', 'OUTLINE(@"SEL$07BDC5B4")', 'MERGE(@"SEL$4")', 'OUTLINE(@"SEL$F5BB74E1")', 'MERGE(@"SEL$2")', 'OUTLINE(@"SEL$3")', 'OUTLINE(@"SEL$4")', 'OUTLINE(@"SEL$1")', 'OUTLINE(@"SEL$2")', 'NO_ACCESS(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")', 'INDEX_RS_ASC(@"SEL$B7274CD5" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM" "FNM_VSBL_MSG"."MSG_TYP_CD"))', 'INDEX(@"SEL$B7274CD5" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))', 'LEADING(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5" "MSG"@"SEL$2" "TRK"@"SEL$2")', 'USE_NL(@"SEL$B7274CD5" "MSG"@"SEL$2")', 'USE_NL(@"SEL$B7274CD5" "TRK"@"SEL$2")', 'SEMI_TO_INNER(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")', 'FULL(@"SEL$A8541665" "KOKBF$0"@"SEL$4")', 'USE_HASH_AGGREGATION(@"SEL$A8541665")', 'INDEX(@"SEL$7" "TRK1"@"SEL$7" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))'), NAME => 'coe_2fcyrt9373kyr_1944127456', REPLACE => TRUE, FORCE_MATCH => TRUE);END;/
DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '5w8mvk838bas2';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR(
'IGNORE_OPTIM_EMBEDDED_HINTS',
'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',
'DB_VERSION(''11.2.0.4'')',
'ALL_ROWS',
'FORCE_XML_QUERY_REWRITE',
'XML_DML_RWT_STMT',
'XMLINDEX_REWRITE',
'XMLINDEX_REWRITE_IN_SELECT',
'NO_COST_XML_QUERY_REWRITE',
'OUTLINE_LEAF(@"SEL$7")',
'OUTLINE_LEAF(@"SEL$A8541665")',
'OUTLINE_LEAF(@"SEL$B7274CD5")',
'UNNEST(@"SEL$07BDC5B4" UNNEST_SEMIJ_VIEW)',
'OUTLINE(@"SEL$07BDC5B4")',
'MERGE(@"SEL$4")',
'OUTLINE(@"SEL$F5BB74E1")',
'MERGE(@"SEL$2")',
'OUTLINE(@"SEL$3")',
'OUTLINE(@"SEL$4")',
'OUTLINE(@"SEL$1")',
'OUTLINE(@"SEL$2")',
'NO_ACCESS(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")',
'INDEX_RS_ASC(@"SEL$B7274CD5" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM" "FNM_VSBL_MSG"."MSG_TYP_CD"))',
'INDEX(@"SEL$B7274CD5" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))',
'LEADING(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5" "MSG"@"SEL$2" "TRK"@"SEL$2")',
'USE_NL(@"SEL$B7274CD5" "MSG"@"SEL$2")',
'USE_NL(@"SEL$B7274CD5" "TRK"@"SEL$2")',
'SEMI_TO_INNER(@"SEL$B7274CD5" "VW_NSO_1"@"SEL$B7274CD5")',
'FULL(@"SEL$A8541665" "KOKBF$0"@"SEL$4")',
'USE_HASH_AGGREGATION(@"SEL$A8541665")',
'INDEX(@"SEL$7" "TRK1"@"SEL$7" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))'),
NAME => 'coe_2fcyrt9373kyr_1944127456',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/
Dom,
Thank you for the inputs. I may have created profile incorrectly the first time as I did not provide all hints while creating profile.
Second time, I did create with all hints. (attached the syntax...). I will try the hints you suggested, but regardless, trying to find the flawless way of creating the profile.. or not sure if this profile approach even works in all situations..
So, you are getting the text of sql id 5w8mvk838bas2 but creating a sql profile with the naming pattern of sql id 2fcyrt9373kyr?
Seems weird from a naming perspective.
If you want to apply the profile to sql 2fcyrt9373kyr then you need to get the text of that sql id.
Reason is that the text is used to get a hash - the signature you see in dba_sql_profiles - hash exposed via DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE.
If it's not working then you need to have a close look at that signature in DBA_SQL_PROFILES vs the EXACT_MATCHING_SIGNATURE/FORCE_MATCHING_SIGNATURE of the sql in V$SQL.
From what you've posted above, your SQL is using literals so every statement with a different values will be a different sql id.
So, you'd need to use FORCE - as you have done - so check the FORCE_MATCHING_SIGNATURE.
Note that FORCE will not work with a mixture of binds and literals... but the literal you see in the execution plan predicates is related to subquery optimisation not to value supplied in the SQL statements (at least not one I could see in the actual SQL statements you've posted so far).
The other challenge is that using a TABLE operator and a TYPE suggests that you can have a variable length of inputs.
In which case, FORCE matching is no help unless you profile every unique combination of lengths.
If this is the case, you have a fundamental issue which sticking plasters will struggle to cope with.
Ideally, you want a single sql statement (and sql id) for every combination of values (provided that can perform acceptably for all combinations of course) - that has to involve bind variables to give you any degree of control and from a variable length list perspective - it's not really going to work brilliantly unless you use a different approach like a global temporary table or bind in a single collection of values.
All that aside, as an initial experiment- rather than final solution - you could try setting a profile with just this hint:
opt_estimate(@"SEL$A8541665" table "KOKBF$0"@"SEL$4" rows=1 )
But from what you've said already, I suspect the issue is to do with what I've mentioned above.
There's no way to determine from the UI behaviour whether or not the generated query will be using bind variables. If you've managed to see that "the" query run by the users is picking up the profile then you should be able to show use the sql_fulltext of the query. The example you've shown has two components that look as if the invoice number is appearing as a literal (and also suggesting that the predicate could change to an IN list.
MSG.SRCH_4_FLD_VAL = '123456'
TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR))
The ":B1" bind variable we can see in the execution plan looks like it's a correlating value for the "select max()" subquery passing in the value from a column - but it's possible it's a real, declared bind variable with a name that happens to match a name that Oracle tends to use internally.
So -
Can you pull a query, with sql_id, and full text from memory, with it's execution plan, with outline and notes so that we can see a query that you think is using the profile but failing to obey it. (People have reported that problem before occasionally, I'd have to do a search to find out why it was happening.)
You could also query dba_sql_profiles for the profile_name you've created with suitable SQL*Plus format commands to show the sql text that Oracle thinks it belongs to, and if you have access to the data dictionary object sqlobj$data you can run the following query for the signature that showed up in dba_sql_profiles to see what the hints that arrived look like.
set pagesize 60
set linesize 132
set trimspool on
column hint format a70 wrap word
column signature format 999,999,999,999,999,999,999
break on signature skip 1 on opt_type skip 1 on plan_id skip 1
spool sql_profile_baseline_11g
select
prf.signature,
decode(
obj_type,
1,'Profile',
2,'Baseline',
3,'Patch',
'Other'
) opt_type,
prf.plan_id,
extractvalue(value(tab),'.') hint
from
(
/*+ no_eliminate_oby */
*
sqlobj$data
where
comp_data is not null
order by
signature, obj_type, plan_id
) prf,
table(
xmlsequence(
extract(xmltype(prf.comp_data),'/outline_data/hint')
)
) tab
;
UPDATE: I wrote the above for 11g, but I think it should still work for 12c.
Hello,
I'd like to understand why the optimizer use index XIE11FNM_VSBL_MSG instead of the XIE2FNM_VSBL_MSG which would avoid reading 101K rows on the table FNM_VSBL_MSG.
It seems clear to me from the runtime statistics of the two execution plans that the selectivity of the index XIE2FNM_VSBL_MSG in the conditions/filters of your query is clearly more advantageous, then I wonder, why accessing in RANGE SCAN the optimizer decides to use XIE11FNM_VSBL_MSG?
When the optimizer has to choose how to access the table FNM_VSBL_MSG it finds the cost of the index XIE11FNM_VSBL_MSG more convenient than that of the index XIE2FNM_VSBL_MSG and probably the optimizer does not choose XIE2FNM_VSBL_MSG because for some statistical value of this index the cost increased (eg clustering factor).Have you tried to see what the statistics of the two indexes are? have been calculated on all objects (table FNM_VSBL_MSG and indexes)?
Regards,
DS
I want to apply profile to bad SQL ID - "5w8mvk838bas2" generated from good SQL - "2fcyrt9373kyr".
2fc* is the SQL ID of the SQL from my SQL PLUS run with the index hint. Then using the outline section of the execution plan, I am trying to copy hints to bad sql id using the link in my first thread..
Query is using bind variables. I was with the business user this morning while she submitted different invoice numbers, every time the SQL is running with same SQL ID - 5w8*.
Here's an example I pulled from AWR report that shows Query has bind variables:
(Note* - Currently, the profile I copied from good sql is dropped and I applied a profile recommended by Tuning Advisor.. which is using a different index.. XIE1*.. at-least this way query is running long but not timing out...)
SQL_ID 5w8mvk838bas2--------------------SELECT * FROM (SELECT MSG.MSG_ID, MSG.VSBL_MSG_ID,MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL, MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL, MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROMFNM.FNM_VSBL_MSG MSG, FNM.BCS_INV_RESEND_TRK TRK WHEREMSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD ='PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy ='INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) ASTABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' ANDMSG.SRCH_3_FLD_VAL IN (SELECT * FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) ASTABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND(TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECTMAX(TRK1.RESEND_DT) FROM FNM.BCS_INV_RESEND_TRK TRK1 WHERETRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESCPlan hash value: 1071232985-------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 4 (100)| || 1 | SORT ORDER BY | | 1 | 156 | 4 (25)| 00:00:01 || 2 | FILTER | | | | | || 3 | NESTED LOOPS OUTER | | 1 | 156 | 3 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 139 | 2 (0)| 00:00:01 || 5 | INDEX RANGE SCAN | XIE11FNM_VSBL_MSG | 1 | | 1 (0)| 00:00:01 || 6 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | 17 | 1 (0)| 00:00:01 || 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 3 (0)| 00:00:01 || 8 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 3 (0)| 00:00:01 || 9 | SORT AGGREGATE | | 1 | 17 | | || 10 | FIRST ROW | | 1 | 17 | 2 (0)| 00:00:01 || 11 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 17 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$F5BB74E1 4 - SEL$F5BB74E1 / MSG@SEL$2 5 - SEL$F5BB74E1 / MSG@SEL$2 6 - SEL$F5BB74E1 / TRK@SEL$2 7 - SEL$07BDC5B4 / KOKBF$0@SEL$4 8 - SEL$ABDE6DFF / KOKBF$1@SEL$6 9 - SEL$7 11 - SEL$7 / TRK1@SEL$7Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS FORCE_XML_QUERY_REWRITE XML_DML_RWT_STMT XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT NO_COST_XML_QUERY_REWRITE OUTLINE_LEAF(@"SEL$07BDC5B4") MERGE(@"SEL$4") OUTLINE_LEAF(@"SEL$ABDE6DFF") MERGE(@"SEL$6") OUTLINE_LEAF(@"SEL$7") OUTLINE_LEAF(@"SEL$F5BB74E1") MERGE(@"SEL$2") OUTLINE(@"SEL$3") OUTLINE(@"SEL$4") OUTLINE(@"SEL$5") OUTLINE(@"SEL$6") OUTLINE(@"SEL$1") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$F5BB74E1" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."SRCH_4_FLD_VAL" "FNM_VSBL_MSG"."MSG_TYP_CD" "FNM_VSBL_MSG"."MSG_CAPTR_STG_CD")) INDEX(@"SEL$F5BB74E1" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT")) LEADING(@"SEL$F5BB74E1" "MSG"@"SEL$2" "TRK"@"SEL$2") USE_NL(@"SEL$F5BB74E1" "TRK"@"SEL$2") INDEX(@"SEL$7" "TRK1"@"SEL$7" ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT")) FULL(@"SEL$ABDE6DFF" "KOKBF$1"@"SEL$6") FULL(@"SEL$07BDC5B4" "KOKBF$0"@"SEL$4") END_OUTLINE_DATA */Peeked Binds (identified by position):-------------------------------------- 1 - :BINDINVOICETO (VARCHAR2(30), CSID=873): '123456'
SQL_ID 5w8mvk838bas2
--------------------
SELECT * FROM (SELECT MSG.MSG_ID, MSG.VSBL_MSG_ID,
MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL, MSG.SRCH_5_FLD_VAL,
MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL, MSG.DISP_3_FLD_VAL,
MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,
'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy =
TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS
TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND
MSG.SRCH_3_FLD_VAL IN (SELECT * FROM
TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS
TRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC
Plan hash value: 1071232985
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 1 | 156 | 4 (25)| 00:00:01 |
| 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 156 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 139 | 2 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | XIE11FNM_VSBL_MSG | 1 | | 1 (0)| 00:00:01 |
| 6 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | 17 | 1 (0)| 00:00:01 |
| 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 3 (0)| 00:00:01 |
| 8 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 2 | 3 (0)| 00:00:01 |
| 9 | SORT AGGREGATE | | 1 | 17 | | |
| 10 | FIRST ROW | | 1 | 17 | 2 (0)| 00:00:01 |
| 11 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 17 | 2 (0)| 00:00:01 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / MSG@SEL$2
5 - SEL$F5BB74E1 / MSG@SEL$2
6 - SEL$F5BB74E1 / TRK@SEL$2
7 - SEL$07BDC5B4 / KOKBF$0@SEL$4
8 - SEL$ABDE6DFF / KOKBF$1@SEL$6
9 - SEL$7
11 - SEL$7 / TRK1@SEL$7
OUTLINE_LEAF(@"SEL$07BDC5B4")
OUTLINE_LEAF(@"SEL$ABDE6DFF")
MERGE(@"SEL$6")
OUTLINE_LEAF(@"SEL$F5BB74E1")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
INDEX_RS_ASC(@"SEL$F5BB74E1" "MSG"@"SEL$2" ("FNM_VSBL_MSG"."SRCH_4_FLD_VAL"
"FNM_VSBL_MSG"."MSG_TYP_CD" "FNM_VSBL_MSG"."MSG_CAPTR_STG_CD"))
INDEX(@"SEL$F5BB74E1" "TRK"@"SEL$2" ("BCS_INV_RESEND_TRK"."INV_NUM"
"BCS_INV_RESEND_TRK"."RESEND_DT"))
LEADING(@"SEL$F5BB74E1" "MSG"@"SEL$2" "TRK"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "TRK"@"SEL$2")
FULL(@"SEL$ABDE6DFF" "KOKBF$1"@"SEL$6")
FULL(@"SEL$07BDC5B4" "KOKBF$0"@"SEL$4")
Peeked Binds (identified by position):
--------------------------------------
1 - :BINDINVOICETO (VARCHAR2(30), CSID=873): '123456'
Enter value for SQLID: 5w8mvk838bas2old 31: where sql_id='&sql_id'new 31: where sql_id='5w8mvk838bas2' elapsd rows time elapsed in plan_hash snap exec processed delta per SQL ID SQL_ID value id SNAP_BEG delta delta sec CPU_EX execution Profile--- ------------- ---------- ------ --------------- ------- ---------- ------------ ---------- ------------ -------------------- 1 5w8mvk838bas2 1071232985 10096 29-05-19 14:00 4 0 242.02 1.3345235 60.51 null 1 5w8mvk838bas2 3990772682 10116 30-05-19 10:00 2 0 120.42 .74443 60.21 null 1 5w8mvk838bas2 1071232985 10117 30-05-19 11:00 1 0 120.21 2.405869 120.21 null 1 5w8mvk838bas2 3990772682 10117 30-05-19 11:00 0 0 .00 null null null 1 5w8mvk838bas2 1071232985 10118 30-05-19 12:00 4 2 121.47 .713648 30.37 null 1 5w8mvk838bas2 3990772682 10118 30-05-19 12:00 0 0 .00 null null null 1 5w8mvk838bas2 1071232985 10119 30-05-19 13:00 3 4 1.94 .011043667 .65 null 1 5w8mvk838bas2 1071232985 10120 30-05-19 14:00 8 0 120.73 .391422625 15.09 null 1 5w8mvk838bas2 1071232985 10121 30-05-19 15:00 5 0 242.15 .5827904 48.43 null 1 5w8mvk838bas2 1071232985 10141 31-05-19 11:01 2 0 120.98 1.4070945 60.49 null 1 5w8mvk838bas2 1071232985 10142 31-05-19 12:00 4 0 242.48 1.477149 60.62 null 1 5w8mvk838bas2 3990772682 10142 31-05-19 12:00 2 0 120.63 1.2266175 60.32 coe_5w8mvk838bas2_39 1 5w8mvk838bas2 4200011164 10144 31-05-19 14:00 15 6 315.43 .8964386 21.03 SYS_SQLPROF_016b0f62 1 5w8mvk838bas2 3990772682 10145 31-05-19 15:00 2 0 240.73 2.3402655 120.37 null 1 5w8mvk838bas2 3462797742 10146 31-05-19 16:00 2 0 120.20 1.7872955 60.10 PROFILE_5w8mvk838bas 1 5w8mvk838bas2 1071232985 10149 31-05-19 19:00 4 0 85.26 .349116 21.31 null 1 5w8mvk838bas2 1071232985 10214 03-06-19 12:00 2 0 67.82 .651554 33.91 coe_2fcyrt9373kyr_19 1 5w8mvk838bas2 1071232985 10215 03-06-19 13:00 5 0 121.48 .6201476 24.30 coe_2fcyrt9373kyr_19 1 5w8mvk838bas2 1071232985 10236 04-06-19 10:00 3 0 120.81 .570824333 40.27 null 1 5w8mvk838bas2 1071232985 10237 04-06-19 11:00 3 0 241.92 1.42221667 80.64 coe_2fcyrt9373kyr_19 1 5w8mvk838bas2 4200011164 10242 04-06-19 16:00 2 2 105.11 2.1878195 52.56 SYS_SQLPROF_016b23ae 1 5w8mvk838bas2 4200011164 10260 05-06-19 10:00 3 1 122.01 1.08735633 40.67 SYS_SQLPROF_016b23ae
Enter value for SQLID: 5w8mvk838bas2
old 31: where sql_id='&sql_id'
new 31: where sql_id='5w8mvk838bas2'
elapsd
rows time elapsed
in plan_hash snap exec processed delta per SQL
ID SQL_ID value id SNAP_BEG delta delta sec CPU_EX execution Profile
--- ------------- ---------- ------ --------------- ------- ---------- ------------ ---------- ------------ --------------------
1 5w8mvk838bas2 1071232985 10096 29-05-19 14:00 4 0 242.02 1.3345235 60.51 null
1 5w8mvk838bas2 3990772682 10116 30-05-19 10:00 2 0 120.42 .74443 60.21 null
1 5w8mvk838bas2 1071232985 10117 30-05-19 11:00 1 0 120.21 2.405869 120.21 null
1 5w8mvk838bas2 3990772682 10117 30-05-19 11:00 0 0 .00 null null null
1 5w8mvk838bas2 1071232985 10118 30-05-19 12:00 4 2 121.47 .713648 30.37 null
1 5w8mvk838bas2 3990772682 10118 30-05-19 12:00 0 0 .00 null null null
1 5w8mvk838bas2 1071232985 10119 30-05-19 13:00 3 4 1.94 .011043667 .65 null
1 5w8mvk838bas2 1071232985 10120 30-05-19 14:00 8 0 120.73 .391422625 15.09 null
1 5w8mvk838bas2 1071232985 10121 30-05-19 15:00 5 0 242.15 .5827904 48.43 null
1 5w8mvk838bas2 1071232985 10141 31-05-19 11:01 2 0 120.98 1.4070945 60.49 null
1 5w8mvk838bas2 1071232985 10142 31-05-19 12:00 4 0 242.48 1.477149 60.62 null
1 5w8mvk838bas2 3990772682 10142 31-05-19 12:00 2 0 120.63 1.2266175 60.32 coe_5w8mvk838bas2_39
1 5w8mvk838bas2 4200011164 10144 31-05-19 14:00 15 6 315.43 .8964386 21.03 SYS_SQLPROF_016b0f62
1 5w8mvk838bas2 3990772682 10145 31-05-19 15:00 2 0 240.73 2.3402655 120.37 null
1 5w8mvk838bas2 3462797742 10146 31-05-19 16:00 2 0 120.20 1.7872955 60.10 PROFILE_5w8mvk838bas
1 5w8mvk838bas2 1071232985 10149 31-05-19 19:00 4 0 85.26 .349116 21.31 null
1 5w8mvk838bas2 1071232985 10214 03-06-19 12:00 2 0 67.82 .651554 33.91 coe_2fcyrt9373kyr_19
1 5w8mvk838bas2 1071232985 10215 03-06-19 13:00 5 0 121.48 .6201476 24.30 coe_2fcyrt9373kyr_19
1 5w8mvk838bas2 1071232985 10236 04-06-19 10:00 3 0 120.81 .570824333 40.27 null
1 5w8mvk838bas2 1071232985 10237 04-06-19 11:00 3 0 241.92 1.42221667 80.64 coe_2fcyrt9373kyr_19
1 5w8mvk838bas2 4200011164 10242 04-06-19 16:00 2 2 105.11 2.1878195 52.56 SYS_SQLPROF_016b23ae
1 5w8mvk838bas2 4200011164 10260 05-06-19 10:00 3 1 122.01 1.08735633 40.67 SYS_SQLPROF_016b23ae
The text that is actually executed may explain why the profile has to be ignored.
If you supply an actual value when testing code that runs with literals you may produce a plan that is dependent on the optimizer "KNOWING" that it is doing a comparison with something that is not null. When the query runs with bind variables the optimizer has to produce a plan which caters for the possibility that the bind variable may hold NULL - this means you may end up with a faked profile that dictates a plan that would give the wrong results if an incoming bind variable were NULL. If that happens you can be in a position where Oracle reports "I applied the profile - but then I ignored it because it would produce an illegal plan", unfortunately there's no code to produce the second half of the message.
For testing purposes (from SQL*Plus, for example) you have to declare variables and assign values to them, then use the variables in the SQL, e.g.
SQL> variable BindInvoiceTo varchar2(10)
SQL> exec :BindInvoiceTo := '12345'
select ... where MSG.SRCH_4_FLD_VAL = :BindInvoiceTo
Compare the plan you generated at the start of this thread with the plan from the live system.
Note particularly that the operation "COLLECTION ITERATOR PICKLER FETCH" appears twice in the live plan, but only once in your plan.
This is because you've generated a plan with a predicate: "... OR ('INVOICENUMBER' = 'SIEBELORDERID' AND ... ) and the optimizer at PARSE time can see that that bit of the where clause will always be false - so it's been able to drop the subquery referencing the second collection construct.
In production the optimizer has to produce a plan that caters for the possibility that the bind variable will hold the value 'SIEBELORDERID' at runtime, and that means your profile is illegal.
Jonathan
This is exactly what I've got at one client site where the execution plan Note was saying that the SQL Profile has been used but the desired execution plan has not been generated. I spent a couple of hours searching the root cause for thatuntil I spotted out, in the peeked bind variables of the application query execution plan, that one of the bind variables sent from the front-end application was null. The original poster can check whether this is the case for him or not using the peeked bind variable of the bad plan.
This is one of the important difference between a SQL Profile and a SPM. Oracle will say (via the Note) that the SQL Profile has been used whenever the signature of the Profile matches that of the SQL query regardless of the difference that might exist between the plan forced by the Profile
and the one that would have normally been produced by applying the set of hints contained into the Profile.
Whereas SPM will never say that the SQL Plan baseline has been used if the plan_id stored into the baseline doesn’t match the phv2 of the CBO execution plan
Best regards
Mohamed Houri
Here's an old blog note of mine that demonstrates the problem of testing a query with bind variables by substituting for a particular set of actual values: https://jonathanlewis.wordpress.com/2010/10/31/conditional-sql-3/ The principle is the same even though it doesn't go into the topic of SQL Profiles.
Thank you for teaching me a new point. This is getting intriguing and would like to clarify a couple of things:
1. This means I should "never" supply actual literal values while testing the code, when I know the code from application uses bind variables ? because the plan I produce by using literals in the SQL is no good (illegal for the optimizer..) for the SQL coming from the application, correct ?
So, for this you are suggesting the right way to test is to declare the bind variables first and run the SQL with binds..
For instance, is this the right way to test ? With this, I am not getting the <1 sec response time anymore even though I am using the Index hint I "thought" would help.....
variable BINDINVOICETO VARCHAR2(32);variable BINDSEARCHBY VARCHAR2(128);variable BINDINVOICELIST VARCHAR2(32);variable BINDSEARCHBY VARCHAR2(128);variable BINDSEIBELIDLIST VARCHAR2(32);begin:BINDINVOICETO := '123456';:BINDSEARCHBY := 'INVOICENUMBER';:BINDINVOICELIST := '456789';:BINDSEARCHBY := 'INVOICENUMBER';:BINDSEIBELIDLIST := '';end;/select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/MSG.MSG_ID,MSG.VSBL_MSG_ID,MSG.SRCH_4_FLD_VAL,MSG.SRCH_3_FLD_VAL,MSG.SRCH_5_FLD_VAL,MSG.MSG_TRSM_DTTM,MSG.DISP_4_FLD_VAL,MSG.DISP_3_FLD_VAL,MSG.DISP_1_FLD_VAL,MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL,TRK.RESEND_DT,MSG.CRE_DTTMFROMFNM.FNM_VSBL_MSG MSG,FNM.BCS_INV_RESEND_TRK TRKWHEREMSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT*FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT*FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL ORTRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)FROM FNM.BCS_INV_RESEND_TRK TRK1WHERE TRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;
variable BINDINVOICETO VARCHAR2(32);
variable BINDSEARCHBY VARCHAR2(128);
variable BINDINVOICELIST VARCHAR2(32);
variable BINDSEIBELIDLIST VARCHAR2(32);
begin
:BINDINVOICETO := '123456';
:BINDSEARCHBY := 'INVOICENUMBER';
:BINDINVOICELIST := '456789';
:BINDSEIBELIDLIST := '';
end;
select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/
MSG.MSG_ID,
MSG.VSBL_MSG_ID,
MSG.SRCH_4_FLD_VAL,
MSG.SRCH_3_FLD_VAL,
MSG.SRCH_5_FLD_VAL,
MSG.MSG_TRSM_DTTM,
MSG.DISP_4_FLD_VAL,
MSG.DISP_3_FLD_VAL,
MSG.DISP_1_FLD_VAL,
MSG.DISP_2_FLD_VAL,
MSG.SRCH_1_FLD_VAL,
TRK.RESEND_DT,
MSG.CRE_DTTM
FROM
FNM.FNM_VSBL_MSG MSG,
FNM.BCS_INV_RESEND_TRK TRK
WHERE
MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT
TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT
TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL OR
TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)
FNM.BCS_INV_RESEND_TRK TRK1
TRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;
2. Now that I am testing it the right way (?) (by declaring the bind variables before SQL execution) and the index hint is not helping... any suggestions on how else I improve the run-time of this query ?
Ideally, if the Index hint is still helping when I am testing it the right way, then copying the profile (with outline hints) makes sense ?
Thank you very much for the guidance and your technical acumen !
Sometimes a test which uses literals that match the end-user's inputs will be sufficient - but you have to be aware that plans may different between bind variable versions and literal value versions - so doing a test that is as close as possible to the user's activity is desirable. In this case the approach you've taken is adequate, but there may be some cases where you have to wrap the SQL inside a PL/SQL block and use PL/SQL variables to pass in the values as SQL*Plus variables are all considered to be character types, so there's a risk of implicit conversion doing the wrong thing and changing plans (though you could edit the SQL to use to_number(), to_date() etc. on the SQL*Plus variables - but even that may mean Oracle doesn't have exactly the same information for the arithmetic.
You already know how to start - do what you did for the original posting: execute the hinted query with row source statistics enabled and pull the plan from memory after it has executed, reporting the execution statistics. At present you haven't even told use whether or not Oracle used the index you hinted, so how can we tell you what to do next. (Include the 'alias' formatting option so we get a little more information about what Oracle is doing with the query.
Jon, Sorry for the delayed response.
Here's query execution stats from cursor cache. The optimizer is picking up the index hint, but it is doing a "index full scan" (instead of range scan). Still not returning results after 30 min.
Next, I enforced index range scan hint "INDEX_RS_ASC (MSG XIE2SNI_VSBL_MSG)*/ ", now optimizer doesn't even pick this index.
How do I check for data distribution or see if collecting histograms is of any use in this scenario ? Apparently, the best run-time we got so far is through a Tuning advisor profile which is using a different index..
SQL> alter session set statistics_level='ALL';Session altered.SQL> variable BINDINVOICETO VARCHAR2(32);SQL> variable BINDSEARCHBY VARCHAR2(128);SQL> variable BINDINVOICELIST VARCHAR2(32);SQL> variable BINDSEARCHBY VARCHAR2(128);SQL> variable BINDSEIBELIDLIST VARCHAR2(32);SQL>SQL> begin 2 3 :BINDINVOICETO := '197639'; 4 :BINDSEARCHBY := 'INVOICENUMBER'; 5 :BINDINVOICELIST := '34609590'; 6 :BINDSEARCHBY := 'INVOICENUMBER'; 7 :BINDSEIBELIDLIST := ''; 8 9 end; 10 11 /PL/SQL procedure successfully completed.SQL> select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ 2 MSG.MSG_ID, 3 MSG.VSBL_MSG_ID, 4 MSG.SRCH_4_FLD_VAL, 5 MSG.SRCH_3_FLD_VAL, 6 MSG.SRCH_5_FLD_VAL, 7 MSG.MSG_TRSM_DTTM, 8 MSG.DISP_4_FLD_VAL, 9 MSG.DISP_3_FLD_VAL, 10 MSG.DISP_1_FLD_VAL, 11 MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL, 12 13 TRK.RESEND_DT, 14 MSG.CRE_DTTM 15 FROM 16 FNM.FNM_VSBL_MSG MSG, 17 FNM.BCS_INV_RESEND_TRK TRK 18 WHERE 19 MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT 20 * 21 FROM 22 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT 23 * 24 FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL OR 25 26 TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT) 27 FROM 28 FNM.BCS_INV_RESEND_TRK TRK1 29 WHERE 30 TRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;
SQL> alter session set statistics_level='ALL';
Session altered.
SQL> variable BINDINVOICETO VARCHAR2(32);
SQL> variable BINDSEARCHBY VARCHAR2(128);
SQL> variable BINDINVOICELIST VARCHAR2(32);
SQL> variable BINDSEIBELIDLIST VARCHAR2(32);
SQL>
SQL> begin
2
3 :BINDINVOICETO := '197639';
4 :BINDSEARCHBY := 'INVOICENUMBER';
5 :BINDINVOICELIST := '34609590';
6 :BINDSEARCHBY := 'INVOICENUMBER';
7 :BINDSEIBELIDLIST := '';
8
9 end;
10
11 /
PL/SQL procedure successfully completed.
SQL> select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/
2 MSG.MSG_ID,
3 MSG.VSBL_MSG_ID,
4 MSG.SRCH_4_FLD_VAL,
5 MSG.SRCH_3_FLD_VAL,
6 MSG.SRCH_5_FLD_VAL,
7 MSG.MSG_TRSM_DTTM,
8 MSG.DISP_4_FLD_VAL,
9 MSG.DISP_3_FLD_VAL,
10 MSG.DISP_1_FLD_VAL,
11 MSG.DISP_2_FLD_VAL,
12 13 TRK.RESEND_DT,
14 MSG.CRE_DTTM
15 FROM
16 FNM.FNM_VSBL_MSG MSG,
17 FNM.BCS_INV_RESEND_TRK TRK
18 WHERE
19 MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT
20 *
21 FROM
22 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT
23 *
24 FROM
25 26 TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)
27 FROM
28 FNM.BCS_INV_RESEND_TRK TRK1
29 WHERE
30 TRK1.INV_NUM = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;
Enter value for sql_id: 7pc3shgs6gy29old 3: where s.sql_id='&sql_id'new 3: where s.sql_id='7pc3shgs6gy29'EXECUTION_PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 SQL_ID 7pc3shgs6gy29, child number 00 -------------------------------------0 select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ MSG.MSG_ID,0 MSG.VSBL_MSG_ID, MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL,0 MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL,0 MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,0 MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROM FNM.FNM_VSBL_MSG0 MSG, FNM.BCS_INV_RESEND_TRK TRK WHERE MSG.MSG_TYP_CD =0 '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND0 MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy =0 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROM0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS0 TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND0 MSG.SRCH_3_FLD_VAL IN (SELECT * FROM0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS0 TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND0 (TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)0 FROM FNM.BCS_INV_RESEND_TRK TRK1 WHERE TRK1.INV_NUM =0 TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC00 Plan hash value: 158745648600 ---------------------------------------------------------------------------------------------------------0 | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |0 ---------------------------------------------------------------------------------------------------------0 | 0 | SELECT STATEMENT | | | | | |0 | 1 | SORT ORDER BY | | 1 | 73728 | 73728 | |0 |* 2 | FILTER | | | | | |0 | 3 | NESTED LOOPS OUTER | | 1 | | | |0 |* 4 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | | | |0 |* 5 | INDEX FULL SCAN | XIE2FNM_VSBL_MSG | 4975K| | | |0 |* 6 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | | | |0 |* 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | | | |0 |* 8 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | | | |0 | 9 | SORT AGGREGATE | | 1 | | | |0 | 10 | FIRST ROW | | 1 | | | |0 |* 11 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | | | |0 ---------------------------------------------------------------------------------------------------------00 Predicate Information (identified by operation id):0 ---------------------------------------------------00 2 - filter((((:BINDSEARCHBY='INVOICENUMBER' AND IS NOT NULL) OR0 (:BINDSEARCHBY='SIEBELORDERID' AND IS NOT NULL)) AND ("TRK"."RESEND_DT" IS NULL OR0 "TRK"."RESEND_DT"=)))0 4 - filter(("MSG"."SRCH_4_FLD_VAL"=:BINDINVOICETO AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))0 5 - access("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')0 filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')0 6 - access("MSG"."MSG_ID"="TRK"."INV_NUM")0 7 - filter(VALUE(KOKBF$)=:B1)0 8 - filter(VALUE(KOKBF$)=:B1)0 11 - access("TRK1"."INV_NUM"=:B1)00 Note0 -----0 - Warning: basic plan statistics not available. These are only collected when:0 * hint 'gather_plan_statistics' is used for the statement or0 * parameter 'statistics_level' is set to 'ALL', at session or system level059 rows selected.
Enter value for sql_id: 7pc3shgs6gy29
old 3: where s.sql_id='&sql_id'
new 3: where s.sql_id='7pc3shgs6gy29'
EXECUTION_PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 SQL_ID 7pc3shgs6gy29, child number 0
0 -------------------------------------
0 select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ MSG.MSG_ID,
0 MSG.VSBL_MSG_ID, MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL,
0 MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL,
0 MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,
0 MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROM FNM.FNM_VSBL_MSG
0 MSG, FNM.BCS_INV_RESEND_TRK TRK WHERE MSG.MSG_TYP_CD =
0 '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND
0 MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy =
0 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROM
0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS
0 TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND
0 MSG.SRCH_3_FLD_VAL IN (SELECT * FROM
0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS
0 TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND
0 (TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)
0 FROM FNM.BCS_INV_RESEND_TRK TRK1 WHERE TRK1.INV_NUM =
0 TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC
0
0 Plan hash value: 1587456486
0 ---------------------------------------------------------------------------------------------------------
0 | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
0 | 0 | SELECT STATEMENT | | | | | |
0 | 1 | SORT ORDER BY | | 1 | 73728 | 73728 | |
0 |* 2 | FILTER | | | | | |
0 | 3 | NESTED LOOPS OUTER | | 1 | | | |
0 |* 4 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | | | |
0 |* 5 | INDEX FULL SCAN | XIE2FNM_VSBL_MSG | 4975K| | | |
0 |* 6 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 1 | | | |
0 |* 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | | | |
0 |* 8 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | | | |
0 | 9 | SORT AGGREGATE | | 1 | | | |
0 | 10 | FIRST ROW | | 1 | | | |
0 |* 11 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | | | |
0 Predicate Information (identified by operation id):
0 ---------------------------------------------------
0 2 - filter((((:BINDSEARCHBY='INVOICENUMBER' AND IS NOT NULL) OR
0 (:BINDSEARCHBY='SIEBELORDERID' AND IS NOT NULL)) AND ("TRK"."RESEND_DT" IS NULL OR
0 "TRK"."RESEND_DT"=)))
0 4 - filter(("MSG"."SRCH_4_FLD_VAL"=:BINDINVOICETO AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))
0 5 - access("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')
0 filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')
0 6 - access("MSG"."MSG_ID"="TRK"."INV_NUM")
0 7 - filter(VALUE(KOKBF$)=:B1)
0 8 - filter(VALUE(KOKBF$)=:B1)
0 11 - access("TRK1"."INV_NUM"=:B1)
0 Note
0 -----
0 - Warning: basic plan statistics not available. These are only collected when:
0 * hint 'gather_plan_statistics' is used for the statement or
0 * parameter 'statistics_level' is set to 'ALL', at session or system level
59 rows selected.
The first step would be to get my name right.
Then take a close look at the current plan which does the INDEX FULL SCAN, and compare it with the "good" plan that you started this thread with. Ask yourself this question - where is Oracle coming FROM when it can make good use of that index, and is it coming from the same place when you hint it now that you've got the bind variables in the code.
Jonathan, Sorry about the shorter name I used earlier. Thank you for all the inputs, I will continue the analysis.
The problem is the query - I don't think the optimizer can do anything efficient with it to make it efficient; you may have to rewrite it as a UNION ALL.
In your original Oracle could detect that one of your IN subqueries could be discarded because you had 'INVOICE' = 'SIEBELORDERID' which is alway false.
With bind variables Oracle has to check at run time which subquery needs to be run - which means it has to create a single plan with two branches (hence the "union all" requirement) so that it can pick the right one. This means with the current code you HAVE to drive from the FNM_VSBL_MSG table and run one of two possible filter subqueries.
I may be able to find time to give you a sample solution later on today.
I'm stuck on a train for a couple more hours, so I've prepared a model (which I'll probably blog about) as a demo - here's something that looks like a simplified version of your query (the fact that I don't use collections is irrelevant):
t1.v1
t1
:v1 = 'INVOICE'
and t1.id in (select id from t2 where n1 = 0)
or (
:v1 = 'ORDERID'
and t1.id in (select id from t3 where n1 = 0)
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 25 (4)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 10000 | 146K| 25 (4)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 1 | 8 | 25 (4)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T3 | 1 | 8 | 25 (4)| 00:00:01 |
1 - filter(:V1='INVOICE' AND EXISTS (SELECT 0 FROM "T2" "T2" WHERE
"ID"=:B1 AND "N1"=0) OR :V1='ORDERID' AND EXISTS (SELECT 0 FROM "T3"
"T3" WHERE "ID"=:B2 AND "N1"=0))
3 - filter("ID"=:B1 AND "N1"=0)
4 - filter("ID"=:B1 AND "N1"=0)
Oracle HAS to use the two IN subqueries as (existence ) filter predicates. But your programmer (and my sample) "know" that the IDs in t2 are about INVOICES and the ids in t3 are about ORDERIDs, and I only ever want to run one of these subqueries. If I make the subquery a UNION ALL (which isn't going to have an overlap) the optimizer can unnest and drive from the suqbuery back into t1 - in your case using the index you want used (even if you have to hint it).
t1.id in (
select /*+ unnest */ id
from t2
where n1 = 0
and :v1 = 'INVOICE'
union all
from t3
and :v1 = 'ORDERED'
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 1512 | 74 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 54 | 1512 | 74 (3)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 54 | 702 | 49 (3)| 00:00:01 |
| 3 | HASH UNIQUE | | 54 | 432 | 49 (3)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS FULL| T2 | 27 | 216 | 25 (4)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | TABLE ACCESS FULL| T3 | 27 | 216 | 25 (4)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 10000 | 146K| 25 (4)| 00:00:01 |
1 - access("T1"."ID"="ID")
5 - filter(:V1='INVOICE')
6 - filter("N1"=0)
7 - filter(:V1='ORDERED')
8 - filter("N1"=0)
Note that in my case the union all HAS unnested and is the build table in a hash join - in your case it would (probably) drive a nested loop rather than a hash join, using the desired index into the main table.
You can generate desired sql profile with that hint. Example can be found here: https://dbaclass.com/article/change-the-execution-plan-without-changing-the-sql-query/
Thank you, but in this case, that method did not work due to bind variable coming from the application.
Jonathan suggested query rewrite that I am trying..