Portuguese

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:

• option 1:  create a column group (extended stats) on the pair (MSG_TYP_CD, MSG_CAPTR_STG_CD) which may have a fairly small number of distinct values and some reasonable degree of correlation.  If that is the case then this may result in Oracle estimating a larger number of rows, and higher cost, of the bad driving range scan and therefore changing indexes.
• option 2: check what histograms you have on the "%_CD" columns that appear in the two indexes - perhaps you need to create histograms to let the optimizer know that you're selecting on very popular values from skewed distributions.  Again a change in estimated rows may be sufficient to modify the plans.

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 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 -

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.

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..)

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

        (

        select

                /*+ no_eliminate_oby */

                *

        from

                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.

Regards

Jonathan Lewis

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  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      *  FROM
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
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 = TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESC
Plan 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$7
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$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'

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

Regards

Jonathan Lewis

Regards

Jonathan Lewis

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.

Regards

Jonathan Lewis

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 that
until 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.

Regards

Jonathan Lewis

Jon,

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_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
*
FROM
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 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 = 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 !

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 ?

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.

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 ?

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.

Regards

Jonathan Lewis

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  FROM
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
 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: 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 ---------------------------------------------------------------------------------------------------------
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 ---------------------------------------------------------------------------------------------------------
0
0 Predicate Information (identified by operation id):
0 ---------------------------------------------------
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
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
0
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.

Regards

Jonathan Lewis

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.

Regards

Jonathan Lewis

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):

select

        t1.v1

from

        t1

where

        (

            :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)

        )

;

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------

|   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 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  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).

select

        t1.v1

from

        t1

where

        t1.id in (

                select /*+ unnest */ id

                from    t2

                where  n1 = 0

                and    :v1 = 'INVOICE'

                union all

                select /*+ unnest */ id

                from    t3

                where  n1 = 0

                and    :v1 = 'ORDERED'

        )

;

-----------------------------------------------------------------------------------

| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   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 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  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.

Regards

Jonathan Lewis

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..