sql profiler

[Roshan]

Hello Erman,

can you please advise how to force sql profiler to use index?

DECLARE
  l_sql_text   CLOB;
  l_sql_patch  VARCHAR2(30) := 'PATCH_AA_ARR_CUST_PK';
BEGIN
  SELECT sql_fulltext
  INTO   l_sql_text
  FROM   v$sql
  WHERE  sql_id = '0yyb0jzbwn'
  AND    rownum = 1;

  DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(
    sql_text    => l_sql_text,
    hint_text   => 'INDEX(@"SEL$1" "XAX" "AXA_PK")',
    name        => l_sql_patch,
    category    => 'DEFAULT',
    validate    => TRUE
  );
END;
/

[ErmanArslansOracleBlog]

check the outline data using select * from table(dbms_xplan.display_awr....
or use SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR....

And check the correct INDEX... part..

Use the information gathered from there in the hint_text argument of  "DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(..........."

[Roshan]

is that ok?

SQL_ID  2j1q3va13daya, child number 0
-------------------------------------
SELECT t.RECID, t.XMLRECORD.getClobVal() FROM FBNK_AA_ARR_CHARGE t  
WHERE ( RECID = :P0  OR  RECID = :P1  OR  RECID = :P2  OR  RECID = :P3  
OR  RECID = :P4  OR  RECID = :P5  OR  RECID = :P6  OR  RECID = :P7  OR  
RECID = :P8  OR  RECID = :P9  OR  RECID = :P10  OR  RECID = :P11  OR  
RECID = :P12  OR  RECID = :P13  OR  RECID = :P14  OR  RECID = :P15  OR  
...
 
Plan hash value: 415917010
 
-----------------------------------------------------------------
| Id  | Operation                 | Name               | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |        |
|*  1 |  TABLE ACCESS STORAGE FULL| FBNK_AA_ARR_CHARGE |      1 |
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage(("RECID"=:P0 OR "RECID"=:P1 OR "RECID"=:P2 OR
              "RECID"=:P3 OR "RECID"=:P4 OR "RECID"=:P5 OR "RECID"=:P6 OR "RECID"=:P7
              OR "RECID"=:P8 OR "RECID"=:P9 OR "RECID"=:P10 OR "RECID"=:P11 OR
              "RECID"=:P12 OR "RECID"=:P13 OR "RECID"=:P14 OR "RECID"=:P15 OR
              "RECID"=:P16 OR "RECID"=:P17 OR "RECID"=:P18 OR "RECID"=:P19 OR
              "RECID"=:P20 OR "RECID"=:P21 OR "RECID"=:P22 OR "RECID"=:P23 OR
              "RECID"=:P24 OR "RECID"=:P25 OR "RECID"=:P26 OR "RECID"=:P27 OR
              "RECID"=:P28 OR "RECID"=:P29 OR "RECID"=:P30 OR "RECID"=:P31 OR
              "RECID"=:P32 OR "RECID"=:P33 OR "RECID"=:P34 OR "RECID"=:P35 OR
              "RECID"=:P36 OR "RECID"=:P37 OR "RECID"=:P38 OR "RECID"=:P39 OR
              "RECID"=:P40 OR "RECID"=:P41 OR "RECID"=:P42 OR "RECID"=:P43 OR

              "RECID"=:P240 OR "RECID"=:P2)
       filter(("RECID"=:P0 OR "RECID"=:P1 OR "RECID"=:P2 OR "RECID"=:P3
              OR "RECID"=:P4 OR "RECID"=:P5 OR "RECID"=:P6 OR "RECID"=:P7 OR
              "RECID"=:P8 OR "RECID"=:P9 OR "RECID"=:P10 OR "RECID"=:P11 OR
)
 
Note
-----
   - SQL patch "FORCE_INDEX_PATCH2" used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


DECLARE
  l_patch_name VARCHAR2(30);
BEGIN
  l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id    => '2j1q3va13daya', -- Replace with your SQL_ID
    hint_text => 'INDEX(@SEL$1 T FBNK_AA_ARR_CHARGE_PK)', <<use alias or table name?
    name      => 'FORCE_INDEX_PATCH2',
    description => 'Forcing index on FBNK_AA_ARR_CHARGE T'
  );
END;
/

 

[ErmanArslansOracleBlog]

Your hint: 'INDEX(@"SEL$1" "XAX" "AXA_PK")'

Check: Is XAX the actual table alias or the table name?
 If no alias exists, use table name: 'INDEX(@"SEL$1" "TABLE_NAME" "AXA_PK")'
Remove quotes if names aren't case-sensitive: 'INDEX(@SEL$1 XAX AXA_PK)'

[ErmanArslansOracleBlog]

Check with hint report and repair the hint if necessary.. Note that in 19c , you have hint report feature :
https://blogs.oracle.com/optimizer/what-is-hint-usage-reporting

Also see -> https://forums.oracle.com/ords/apexds/post/sql-patch-is-installed-but-have-no-effect-6915

[ErmanArslansOracleBlog]

Rewrite your query with in clause. You have 240+ or statements there. Due to that optimizer thinks FTS is cheaper eventough you have an index hint.
Use - in- clause. Or tune it with an alternative / better way. Also review the use_concat hint in addition to the indez hinr.. ( you may try i mean)

[Roshan]

It improved for this one. changed from full to index

RECID = :P24  OR  RECID = :P25  OR  RECID = :P26  OR  RECID = :P27  OR  
RECID = :P28  OR  RECID = :P29  OR  RECID = :P30  OR  RECID = :P31  OR  
RECID = :P32  OR  RECID = :P33  OR  RECID = :P34  OR  RECID = :P35  OR  
RECID = :P36  OR  RECID = :P37  OR  RECID = :P38  OR  RECID = :P39  OR  
RECID = :P40  OR  RECID = :P41  OR  RECID = :P42  OR  RECID = :P43  OR  
RECID = :P44  OR  RECID = :P45  OR  RECID = :P46  OR  RECID = :P47  OR  
RECID = :P48  OR  RECID = :P49  OR  RECID = :P50  OR  RECID = :P51  OR
 
Plan hash value: 94014108
 
-----------------------------------------------------------------------
| Id  | Operation                    | Name                  | E-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |        |
|   1 |  INLIST ITERATOR             |                       |        |
|   2 |   TABLE ACCESS BY INDEX ROWID| FBNK_AA_ARR_CHARGE    |    400 |
|*  3 |    INDEX UNIQUE SCAN         | FBNK_AA_ARR_CHARGE_PK |    400 |
-----------------------------------------------------------------------
 

DECLARE
  l_patch_name VARCHAR2(30);
BEGIN
  l_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
    sql_id    => 'asdsd', -- Replace with your SQL_ID
    hint_text => 'INDEX(@SEL$1 T FBNK_AA_ARR_CHARGE_PK)', -- Replace with hint(T)
    name      => 'FORCE_INDEX_PATCH2',
    description => 'Forcing index on FBNK_AA_ARR_CHARGE T'
  );
END;
/