Discussions
Categories
- 382.3K All Categories
- 2.1K Data
- 209 Big Data Appliance
- 1.9K Data Science
- 448K Databases
- 221K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 520 MySQL Community Space
- 467 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 492 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.2K SQL & PL/SQL
- 21K SQL Developer
- 293.3K Development
- 7 Developer Projects
- 128 Programming Languages
- 290K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 144 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 198 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 267 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 230 Portuguese
How to enforce index hint to a SQL !
Dear DBA Friends,
DB version - 11.2.0.4. Below are 2 identical SQLs with their execution plans. The only difference between 2 SQLs is I am using an index hint in the first SQL.
With hint, query completes in < 1 sec. Without the hint, Optimizer chooses a different index resulting in query time out .. (after 5 mins or so..).
The timing out SQL is coming from app, I cannot pass the hint directly. So, I created a profile on the good SQL and copied it to the bad SQL, using technique from below link:
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 can I enforce the good index - "XIE2FNM_VSBL_MSG" to the SQL ? Kindly help...
GOOD SQL
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 = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;Plan hash value: 1944127456---------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.08 | 12 | 7 | | | || 1 | SORT ORDER BY | | 1 | 1 | 2 |00:00:00.08 | 12 | 7 | 2048 | 2048 | 2048 (0)||* 2 | FILTER | | 1 | | 2 |00:00:00.08 | 12 | 7 | | | || 3 | NESTED LOOPS OUTER | | 1 | 1 | 2 |00:00:00.08 | 10 | 7 | | | || 4 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.06 | 6 | 5 | | | || 5 | VIEW | VW_NSO_1 | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | | | || 6 | HASH UNIQUE | | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | 1697K| 1697K| 487K (0)|| 7 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 1 | 1 |00:00:00.01 | 0 | 0 | | | ||* 8 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 1 | 2 |00:00:00.06 | 6 | 5 | | | ||* 9 | INDEX RANGE SCAN | XIE2FNM_VSBL_MSG | 1 | 4 | 4 |00:00:00.04 | 4 | 3 | | | ||* 10 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 2 | 1 | 2 |00:00:00.01 | 4 | 2 | | | || 11 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | || 12 | FIRST ROW | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | ||* 13 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | | | |---------------------------------------------------------------------------------------------------------------------------------------------------------------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 statement54 rows selected.BAD SQL
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 = '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 = TRK.INV_NUM))) QRSLT ORDER BY CRE_DTTM DESC;Plan hash value: 3354198206--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 2 |00:08:49.14 | 108K| 66422 | 1215 | | | | || 1 | SORT ORDER BY | | 1 | 1 | 2 |00:08:49.14 | 108K| 66422 | 1215 | 2048 | 2048 | 2048 (0)| ||* 2 | FILTER | | 1 | | 2 |00:08:49.14 | 108K| 66422 | 1215 | | | | ||* 3 | HASH JOIN SEMI | | 1 | 1 | 2 |00:08:49.14 | 108K| 66422 | 1215 | 13M| 2360K| 3658K (1)| 11264 || 4 | NESTED LOOPS OUTER | | 1 | 1 | 101K|00:08:48.48 | 108K| 66107 | 0 | | | | || 5 | TABLE ACCESS BY INDEX ROWID | FNM_VSBL_MSG | 1 | 1 | 101K|00:08:45.67 | 66038 | 66037 | 0 | | | | ||* 6 | INDEX RANGE SCAN | XIE11FNM_VSBL_MSG | 1 | 1 | 101K|00:00:01.17 | 671 | 670 | 0 | | | | ||* 7 | INDEX RANGE SCAN | XPKBCS_INV_RESEND_TRK | 101K| 1 | 7 |00:00:02.33 | 42087 | 70 | 0 | | | | || 8 | VIEW | VW_NSO_1 | 1 | 8168 | 1 |00:00:00.01 | 0 | 0 | 0 | | | | || 9 | COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST | 1 | 8168 | 1 |00:00:00.01 | 0 | 0 | 0 | | | | || 10 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | || 11 | FIRST ROW | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | ||* 12 | INDEX RANGE SCAN (MIN/MAX) | XPKBCS_INV_RESEND_TRK | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | | |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=)) 3 - access("MSG"."MSG_ID"="COLUMN_VALUE") 6 - access("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS') 7 - access("MSG"."MSG_ID"="TRK"."INV_NUM") 12 - access("TRK1"."INV_NUM"=:B1)48 rows selected.Thanks..
Answers
-
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 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.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;/ -
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 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 / [email protected]$2 5 - SEL$F5BB74E1 / [email protected]$2 6 - SEL$F5BB74E1 / [email protected]$2 7 - SEL$07BDC5B4 / [email protected]$4 8 - SEL$ABDE6DFF / [email protected]$6 9 - SEL$7 11 - SEL$7 / [email protected]$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'
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
-
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
