1 2 Previous Next 16 Replies Latest reply: Mar 18, 2014 5:02 AM by M.Suradech RSS

    High physical reads when use substr function

    M.Suradech

      hi everyone,

       

      I found a problem when use substr function in where condition trace is high physical read & bad response time[more than 5 mins]

       

      case 1

      COL_TRN_AGREEMENT.SZORGCODE = substr('dd.MM.yyyy001xxxxx-zz.aa.201415:53:271530',11,3) AND

      COL_CUR_AGREEMENTALLOC.SZORGCODE = substr('dd.MM.yyyy001xxxxx-zz.aa.201415:53:271530',11,3) AND

       

       

      Statistics

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

              201  recursive calls

                0  db block gets

         19491908  consistent gets

           178087  physical reads

                0  redo size

           199910  bytes sent via SQL*Net to client

             8058  bytes received via SQL*Net from client

              687  SQL*Net roundtrips to/from client

                5  sorts (memory)

                0  sorts (disk)

            10283  rows processed

       

       

      but, if i use string in where condition, it return result immediately

       

      case 2

      COL_TRN_AGREEMENT.SZORGCODE = '001' AND

      COL_CUR_AGREEMENTALLOC.SZORGCODE = '001' AND

       

                   Statistics

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

              345  recursive calls

                0  db block gets

            32049  consistent gets

              216  physical reads

             6384  redo size

           162119  bytes sent via SQL*Net to client

             6629  bytes received via SQL*Net from client

              557  SQL*Net roundtrips to/from client

               45  sorts (memory)

                0  sorts (disk)

            10283  rows processed

       

      remark**

      COL_TRN_AGREEMENT.SZORGCODE is not null and datatype is CHAR(3)

      COL_CUR_AGREEMENTALLOC.SZORGCODE is not null and datatype is CHAR(3)

      These two statements generate same plan[from autotrace]


      Environment : oracle 11g on AIX 7100-02-02

       

      Please advise me.

      Thanks

        • 1. Re: High physical reads when use substr function
          SKP

          Please share the execution Plan for both the cases.

          • 2. Re: High physical reads when use substr function
            Martin Preiss

            autotrace is not always a source of truth: I would take a look at the plans for the actual executions shown by dbms_xplan.display_cursor.

            • 3. Re: High physical reads when use substr function
              K.S.I.

              Hi.

              also  look at ASH report. ...

               

              or

              implement the request plan with collection of statistics of runtime :  use hint    gather_plan_statistics  and

              look at the plans for the actual executions shown by dbms_xplan.display_cursor.

               

               

              Whether you use special buffers (as recycle,keep and etc) ?

              • 4. Re: High physical reads when use substr function
                Mohamed Houri

                Are you always going to use substr(xxxx, 11,3) ?

                 

                if yes then you could create a virtual column as shown below

                 

                alter table t1 add ( virt_SZORGCODE generated always as (substr(SZORGCODE ,11,3)) virtual;


                and create an index on that column


                create index ind_virt on t1(virt_SZORGCODE);


                Best regards

                Mohamed Houri

                • 5. Re: High physical reads when use substr function
                  M.Suradech

                  when use autotrace or explain plan, i got same plan.

                  but i get plan from dbms_xplan.display_cursor, i got different plan.

                   

                  plan from condition '001'

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

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

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

                  |   0 | SELECT STATEMENT                    |                           |       |       |  8917 (100)|          |

                  |   1 |  SORT ORDER BY                      |                           |  3609 |   348K|  8917   (1)| 00:01:48 |

                  |   2 |   NESTED LOOPS                      |                           |       |       |            |          |

                  |   3 |    NESTED LOOPS                     |                           |  3609 |   348K|  8916   (1)| 00:01:47 |

                  |   4 |     NESTED LOOPS OUTER              |                           |  3609 |   239K|  1692   (1)| 00:00:21 |

                  |   5 |      NESTED LOOPS                   |                           |  3609 |   207K|  1691   (1)| 00:00:21 |

                  |   6 |       VIEW                          | VW_SQ_1                   |     1 |    14 |    62   (2)| 00:00:01 |

                  |   7 |        HASH UNIQUE                  |                           |     1 |    63 |            |          |

                  |   8 |         NESTED LOOPS                |                           |       |       |            |          |

                  |   9 |          NESTED LOOPS               |                           |     1 |    63 |    62   (2)| 00:00:01 |

                  |* 10 |           TABLE ACCESS FULL         | COL_MST_GRPCOLLALLOCATION |     1 |    28 |    58   (2)| 00:00:01 |

                  |* 11 |           INDEX RANGE SCAN          | GRPHRCHY_IDX1             |     7 |       |     1   (0)| 00:00:01 |

                  |* 12 |          TABLE ACCESS BY INDEX ROWID| COL_HRCHY_COLLECTORGROUP  |     1 |    35 |     4   (0)| 00:00:01 |

                  |  13 |       TABLE ACCESS BY INDEX ROWID   | COL_CUR_AGREEMENTALLOC    |  3609 |   158K|  1629   (1)| 00:00:20 |

                  |* 14 |        INDEX RANGE SCAN             | AGRALLOC_IDX2             |  5456 |       |    17   (0)| 00:00:01 |

                  |* 15 |      INDEX UNIQUE SCAN              | WFHED_PK                  |     1 |     9 |     0   (0)|          |

                  |* 16 |     INDEX UNIQUE SCAN               | AGR_PK                    |     1 |       |     1   (0)| 00:00:01 |

                  |  17 |    TABLE ACCESS BY INDEX ROWID      | COL_TRN_AGREEMENT         |     1 |    31 |     2   (0)| 00:00:01 |

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

                   

                   

                   

                   

                   

                   

                  plan from condition substr function

                   

                   

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

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

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

                  |   0 | SELECT STATEMENT                 |                           |       |       |    10 (100)|          |

                  |   1 |  SORT ORDER BY                   |                           |     1 |    89 |    10  (10)| 00:00:01 |

                  |   2 |   NESTED LOOPS SEMI              |                           |     1 |    89 |     9   (0)| 00:00:01 |

                  |   3 |    NESTED LOOPS                  |                           |     1 |    85 |     6   (0)| 00:00:01 |

                  |   4 |     NESTED LOOPS OUTER           |                           |     1 |    54 |     4   (0)| 00:00:01 |

                  |   5 |      TABLE ACCESS BY INDEX ROWID | COL_CUR_AGREEMENTALLOC    |     1 |    45 |     4   (0)| 00:00:01 |

                  |*  6 |       INDEX RANGE SCAN           | AGRALC_AGR_FK_I           |     1 |       |     3   (0)| 00:00:01 |

                  |*  7 |      INDEX UNIQUE SCAN           | WFHED_PK                  |     1 |     9 |     0   (0)|          |

                  |   8 |     TABLE ACCESS BY INDEX ROWID  | COL_TRN_AGREEMENT         |     1 |    31 |     2   (0)| 00:00:01 |

                  |*  9 |      INDEX UNIQUE SCAN           | AGR_PK                    |     1 |       |     1   (0)| 00:00:01 |

                  |  10 |    VIEW PUSHED PREDICATE         | VW_SQ_1                   |     1 |     4 |     3   (0)| 00:00:01 |

                  |  11 |     NESTED LOOPS                 |                           |       |       |            |          |

                  |  12 |      NESTED LOOPS                |                           |     1 |    63 |     3   (0)| 00:00:01 |

                  |* 13 |       TABLE ACCESS BY INDEX ROWID| COL_HRCHY_COLLECTORGROUP  |     1 |    35 |     2   (0)| 00:00:01 |

                  |* 14 |        INDEX RANGE SCAN          | GRPHRCHY_IDX2             |     7 |       |     1   (0)| 00:00:01 |

                  |* 15 |       INDEX UNIQUE SCAN          | GRPCOLLALC_PK             |     1 |       |     0   (0)|          |

                  |* 16 |      TABLE ACCESS BY INDEX ROWID | COL_MST_GRPCOLLALLOCATION |     1 |    28 |     1   (0)| 00:00:01 |

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

                  • 6. Re: High physical reads when use substr function
                    Moazzam

                    Please verify that optimizer statistics for tables, being used in the query are up-to-date.

                     

                    exec DBMS_STATS.GATHER_TABLE_STATS('ownername','tablename');

                    • 7. Re: High physical reads when use substr function
                      K.S.I.

                      Hi.


                      DBMS_XPLAN.DISPLAY_CURSOR : Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views

                       

                      Can you show sql query  and  full output dbms_xplan ?

                      • 8. Re: High physical reads when use substr function
                        Martin Preiss

                        the optimizer seems to underestimate the cardinalities for the substr-access. Could you add the prediate sections of the plans? I guess that the access on COL_CUR_AGREEMENTALLOC in step 5 (and 6) returns much more than just one row - and this initial error leads to a lot of iterations in the following operations.

                        • 9. Re: High physical reads when use substr function
                          M.Suradech

                          This below plan for full query with Predicate section.




                          condition '001'

                          Plan hash value: 774531493

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

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

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

                          |   0 | SELECT STATEMENT                             |                           |       |       |       | 30838 (100)|          |

                          |*  1 |  COUNT STOPKEY                               |                           |       |       |       |            |          |

                          |   2 |   TABLE ACCESS BY INDEX ROWID                | COL_MST_COLLECTOR         |     1 |    12 |       |     3   (0)| 00:00:01 |

                          |*  3 |    INDEX RANGE SCAN                          | COLLMST_PK                |     1 |       |       |     2   (0)| 00:00:01 |

                          |   4 |  SORT ORDER BY                               |                           |  3609 |  1124K|  1216K| 30838   (1)| 00:06:11 |

                          |*  5 |   FILTER                                     |                           |       |       |       |            |          |

                          |   6 |    NESTED LOOPS                              |                           |       |       |       |            |          |

                          |   7 |     NESTED LOOPS                             |                           |  3609 |  1124K|       | 30589   (1)| 00:06:08 |

                          |   8 |      NESTED LOOPS                            |                           |  3609 |   743K|       | 23365   (1)| 00:04:41 |

                          |   9 |       NESTED LOOPS                           |                           |  3609 |   401K|       | 16141   (1)| 00:03:14 |

                          |* 10 |        VIEW                                  |                           |  3609 | 68571 |       |  8917   (1)| 00:01:48 |

                          |  11 |         COUNT                                |                           |       |       |       |            |          |

                          |  12 |          VIEW                                |                           |  3609 | 21654 |       |  8917   (1)| 00:01:48 |

                          |  13 |           SORT ORDER BY                      |                           |  3609 |   348K|       |  8917   (1)| 00:01:48 |

                          |  14 |            NESTED LOOPS                      |                           |       |       |       |            |          |

                          |  15 |             NESTED LOOPS                     |                           |  3609 |   348K|       |  8916   (1)| 00:01:47 |

                          |  16 |              NESTED LOOPS OUTER              |                           |  3609 |   239K|       |  1692   (1)| 00:00:21 |

                          |  17 |               NESTED LOOPS                   |                           |  3609 |   207K|       |  1691   (1)| 00:00:21 |

                          |  18 |                VIEW                          | VW_SQ_1                   |     1 |    14 |       |    62   (2)| 00:00:01 |

                          |  19 |                 HASH UNIQUE                  |                           |     1 |    63 |       |            |          |

                          |  20 |                  NESTED LOOPS                |                           |       |       |       |            |          |

                          |  21 |                   NESTED LOOPS               |                           |     1 |    63 |       |    62   (2)| 00:00:01 |

                          |* 22 |                    TABLE ACCESS FULL         | COL_MST_GRPCOLLALLOCATION |     1 |    28 |       |    58   (2)| 00:00:01 |

                          |* 23 |                    INDEX RANGE SCAN          | GRPHRCHY_IDX1             |     7 |       |       |     1   (0)| 00:00:01 |

                          |* 24 |                   TABLE ACCESS BY INDEX ROWID| COL_HRCHY_COLLECTORGROUP  |     1 |    35 |       |     4   (0)| 00:00:01 |

                          |  25 |                TABLE ACCESS BY INDEX ROWID   | COL_CUR_AGREEMENTALLOC    |  3609 |   158K|       |  1629   (1)| 00:00:20 |

                          |* 26 |                 INDEX RANGE SCAN             | AGRALLOC_IDX2             |  5456 |       |       |    17   (0)| 00:00:01 |

                          |* 27 |               INDEX UNIQUE SCAN              | WFHED_PK                  |     1 |     9 |       |     0   (0)|          |

                          |* 28 |              INDEX UNIQUE SCAN               | AGR_PK                    |     1 |       |       |     1   (0)| 00:00:01 |

                          |  29 |             TABLE ACCESS BY INDEX ROWID      | COL_TRN_AGREEMENT         |     1 |    31 |       |     2   (0)| 00:00:01 |

                          |  30 |        TABLE ACCESS BY INDEX ROWID           | COL_CUR_AGREEMENTALLOC    |     1 |    95 |       |     2   (0)| 00:00:01 |

                          |* 31 |         INDEX UNIQUE SCAN                    | AGRALC_PK                 |     1 |       |       |     1   (0)| 00:00:01 |

                          |  32 |       TABLE ACCESS BY INDEX ROWID            | COL_TRN_AGREEMENT         |     1 |    97 |       |     2   (0)| 00:00:01 |

                          |* 33 |        INDEX UNIQUE SCAN                     | AGR_PK                    |     1 |       |       |     1   (0)| 00:00:01 |

                          |* 34 |      INDEX UNIQUE SCAN                       | CUSTMST_PK                |     1 |       |       |     1   (0)| 00:00:01 |

                          |  35 |     TABLE ACCESS BY INDEX ROWID              | COL_MST_CUSTOMER          |     1 |   108 |       |     2   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             1 - filter(ROWNUM<=:SYS_B_39)

                             3 - access("CMC"."SZCOLLECTORCODE"=:B1)

                             5 - filter(:SYS_B_44<:SYS_B_45)

                            10 - filter(("P"."RNUM">:SYS_B_44 AND "P"."RNUM"<=:SYS_B_45))

                            22 - filter(("M"."SZCOLLECTORCODE"=:SYS_B_43 AND ("M"."CSUBGROUPDATAACCESSYN"='Y' OR "M"."CGROUPDATAACCESSYN"='Y') AND

                                        "M"."SZORGCODE"=:SYS_B_41))

                            23 - access("H"."SZORGCODE"=:SYS_B_41 AND "M"."SZCOLLECTORGRPCODE"="H"."SZPARENTGRPCODE")

                            24 - filter((("H"."ILEVELDIFF">0 OR "H"."ILEVELDIFF"=0) AND (("M"."CGROUPDATAACCESSYN"='Y' AND "H"."ILEVELDIFF"=0) OR

                                        ("M"."CSUBGROUPDATAACCESSYN"='Y' AND "H"."ILEVELDIFF">0))))

                            26 - access("ITEM_1"="COL_CUR_AGREEMENTALLOC"."SZORGCODE" AND "ITEM_2"="COL_CUR_AGREEMENTALLOC"."SZCOLLECTORGRPCODE")

                                 filter("COL_CUR_AGREEMENTALLOC"."SZORGCODE"=:SYS_B_41)

                            27 - access("WFL_MST_WORKFLOW"."SZORGCODE"=:SYS_B_41 AND "COL_CUR_AGREEMENTALLOC"."SZWFCODE"="WFL_MST_WORKFLOW"."SZWFCOD

                                        E")

                            28 - access("COL_TRN_AGREEMENT"."SZORGCODE"=:SYS_B_40 AND "COL_CUR_AGREEMENTALLOC"."SZAGREEMENTNO"="COL_TRN_AGREEMENT"."

                                        SZAGREEMENTNO")

                            31 - access("B"."IALLOCSEQNO"="P"."IALLOCSEQNO")

                            33 - access("A"."SZORGCODE"="B"."SZORGCODE" AND "A"."SZAGREEMENTNO"="B"."SZAGREEMENTNO")

                            34 - access("A"."SZORGCODE"="C"."SZORGCODE" AND "A"."SZCUSTOMERNO"="C"."SZCUSTOMERNO")

                           

                           

                           

                          condition substr

                          Plan hash value: 4031114393

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

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

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

                          |   0 | SELECT STATEMENT                          |                           |       |       |    17 (100)|          |

                          |*  1 |  COUNT STOPKEY                            |                           |       |       |            |          |

                          |   2 |   TABLE ACCESS BY INDEX ROWID             | COL_MST_COLLECTOR         |     1 |    12 |     3   (0)| 00:00:01 |

                          |*  3 |    INDEX RANGE SCAN                       | COLLMST_PK                |     1 |       |     2   (0)| 00:00:01 |

                          |   4 |  SORT ORDER BY                            |                           |     1 |   319 |    17  (12)| 00:00:01 |

                          |*  5 |   FILTER                                  |                           |       |       |            |          |

                          |   6 |    NESTED LOOPS                           |                           |       |       |            |          |

                          |   7 |     NESTED LOOPS                          |                           |     1 |   319 |    16   (7)| 00:00:01 |

                          |   8 |      NESTED LOOPS                         |                           |     1 |   211 |    14   (8)| 00:00:01 |

                          |   9 |       NESTED LOOPS                        |                           |     1 |   114 |    12   (9)| 00:00:01 |

                          |* 10 |        VIEW                               |                           |     1 |    19 |    10  (10)| 00:00:01 |

                          |  11 |         COUNT                             |                           |       |       |            |          |

                          |  12 |          VIEW                             |                           |     1 |     6 |    10  (10)| 00:00:01 |

                          |  13 |           SORT ORDER BY                   |                           |     1 |    89 |    10  (10)| 00:00:01 |

                          |  14 |            NESTED LOOPS SEMI              |                           |     1 |    89 |     9   (0)| 00:00:01 |

                          |  15 |             NESTED LOOPS                  |                           |     1 |    85 |     6   (0)| 00:00:01 |

                          |  16 |              NESTED LOOPS OUTER           |                           |     1 |    54 |     4   (0)| 00:00:01 |

                          |  17 |               TABLE ACCESS BY INDEX ROWID | COL_CUR_AGREEMENTALLOC    |     1 |    45 |     4   (0)| 00:00:01 |

                          |* 18 |                INDEX RANGE SCAN           | AGRALC_AGR_FK_I           |     1 |       |     3   (0)| 00:00:01 |

                          |* 19 |               INDEX UNIQUE SCAN           | WFHED_PK                  |     1 |     9 |     0   (0)|          |

                          |  20 |              TABLE ACCESS BY INDEX ROWID  | COL_TRN_AGREEMENT         |     1 |    31 |     2   (0)| 00:00:01 |

                          |* 21 |               INDEX UNIQUE SCAN           | AGR_PK                    |     1 |       |     1   (0)| 00:00:01 |

                          |  22 |             VIEW PUSHED PREDICATE         | VW_SQ_1                   |     1 |     4 |     3   (0)| 00:00:01 |

                          |  23 |              NESTED LOOPS                 |                           |       |       |            |          |

                          |  24 |               NESTED LOOPS                |                           |     1 |    63 |     3   (0)| 00:00:01 |

                          |* 25 |                TABLE ACCESS BY INDEX ROWID| COL_HRCHY_COLLECTORGROUP  |     1 |    35 |     2   (0)| 00:00:01 |

                          |* 26 |                 INDEX RANGE SCAN          | GRPHRCHY_IDX2             |     7 |       |     1   (0)| 00:00:01 |

                          |* 27 |                INDEX UNIQUE SCAN          | GRPCOLLALC_PK             |     1 |       |     0   (0)|          |

                          |* 28 |               TABLE ACCESS BY INDEX ROWID | COL_MST_GRPCOLLALLOCATION |     1 |    28 |     1   (0)| 00:00:01 |

                          |  29 |        TABLE ACCESS BY INDEX ROWID        | COL_CUR_AGREEMENTALLOC    |     1 |    95 |     2   (0)| 00:00:01 |

                          |* 30 |         INDEX UNIQUE SCAN                 | AGRALC_PK                 |     1 |       |     1   (0)| 00:00:01 |

                          |  31 |       TABLE ACCESS BY INDEX ROWID         | COL_TRN_AGREEMENT         |     1 |    97 |     2   (0)| 00:00:01 |

                          |* 32 |        INDEX UNIQUE SCAN                  | AGR_PK                    |     1 |       |     1   (0)| 00:00:01 |

                          |* 33 |      INDEX UNIQUE SCAN                    | CUSTMST_PK                |     1 |       |     1   (0)| 00:00:01 |

                          |  34 |     TABLE ACCESS BY INDEX ROWID           | COL_MST_CUSTOMER          |     1 |   108 |     2   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             1 - filter(ROWNUM<=:SYS_B_39)

                             3 - access("CMC"."SZCOLLECTORCODE"=:B1)

                             5 - filter(:SYS_B_48<:SYS_B_49)

                            10 - filter(("P"."RNUM">:SYS_B_48 AND "P"."RNUM"<=:SYS_B_49))

                            18 - access("COL_CUR_AGREEMENTALLOC"."SZORGCODE"=SUBSTR(:SYS_B_43,:SYS_B_44,:SYS_B_45))

                            19 - access("WFL_MST_WORKFLOW"."SZORGCODE"=SUBSTR(:SYS_B_43,:SYS_B_44,:SYS_B_45) AND

                                        "COL_CUR_AGREEMENTALLOC"."SZWFCODE"="WFL_MST_WORKFLOW"."SZWFCODE")

                            21 - access("COL_TRN_AGREEMENT"."SZORGCODE"=SUBSTR(:SYS_B_40,:SYS_B_41,:SYS_B_42) AND

                                        "COL_CUR_AGREEMENTALLOC"."SZAGREEMENTNO"="COL_TRN_AGREEMENT"."SZAGREEMENTNO")

                            25 - filter(("H"."ILEVELDIFF">0 OR "H"."ILEVELDIFF"=0))

                            26 - access("H"."SZORGCODE"="COL_CUR_AGREEMENTALLOC"."SZORGCODE" AND

                                        "H"."SZCOLLECTORGRPCODE"="COL_CUR_AGREEMENTALLOC"."SZCOLLECTORGRPCODE")

                            27 - access("M"."SZCOLLECTORGRPCODE"="H"."SZPARENTGRPCODE" AND

                                        "M"."SZORGCODE"="COL_CUR_AGREEMENTALLOC"."SZORGCODE" AND "M"."SZCOLLECTORCODE"=:SYS_B_47)

                                 filter("M"."SZORGCODE"="H"."SZORGCODE")

                            28 - filter((("M"."CSUBGROUPDATAACCESSYN"='Y' OR "M"."CGROUPDATAACCESSYN"='Y') AND

                                        (("M"."CGROUPDATAACCESSYN"='Y' AND "H"."ILEVELDIFF"=0) OR ("M"."CSUBGROUPDATAACCESSYN"='Y' AND

                                        "H"."ILEVELDIFF">0))))

                            30 - access("B"."IALLOCSEQNO"="P"."IALLOCSEQNO")

                            32 - access("A"."SZORGCODE"="B"."SZORGCODE" AND "A"."SZAGREEMENTNO"="B"."SZAGREEMENTNO")

                            33 - access("A"."SZORGCODE"="C"."SZORGCODE" AND "A"."SZCUSTOMERNO"="C"."SZCUSTOMERNO")

                          • 10. Re: High physical reads when use substr function
                            M.Suradech

                            @Mohamed Houri

                            Thank you, I'll try it. now, waiting to prepare test environment.

                            • 11. Re: High physical reads when use substr function
                              K.S.I.

                              Structures of plans differ and it is well visible since a step 13...

                               

                              Can you show sql-query ?

                               

                              Can you   for query "condition substr"   used  hint  /*dynamic_sampling(4) gather_plan_statistics */  and show  output dbms_xplan  with ALLSTATS LAST ?

                              note :

                              alter session set statistics_level =  all or typical

                              • 12. Re: High physical reads when use substr function
                                Jonathan Lewis

                                You're running with cursor_sharing enabled, so all literals have been turned into bind variables.

                                 

                                This means that the optimizer has to optimize for:  "starting at some unknown position, for an unknown number of characters" when you supply the substr() option, so Oracle optimizes for "unknown value".  When extract the 3 characters oracle optimises for the actual peeked value.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: High physical reads when use substr function
                                  Mohamed Houri

                                  Jonathan

                                   

                                  This is why I suppose that a virtual column will help in contrast to a function based index in this particular case of cursor_sharing value

                                   

                                  SQL> create table t1( vc varchar2(10));

                                   

                                  Table created.

                                   

                                  SQL> create index ind1 on t1 (substr(vc, 1,3));

                                   

                                  Index created.

                                   

                                  SQL> alter session set cursor_sharing=force;

                                   

                                  Session altered.

                                   

                                  SQL> select * from t1 where substr(vc,1,3) = 'xxx';

                                   

                                  no rows selected

                                   

                                  SQL_ID  a0q86qz61cypr, child number 0

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

                                  select * from t1 where substr(vc,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2"

                                   

                                  Plan hash value: 3617692013

                                   

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

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

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

                                  |   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |

                                  |*  1 |  TABLE ACCESS FULL| T1   | 1     |    22 |     2 (0)  | 00:00:01 |

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

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                     1 - filter(SUBSTR("VC",:SYS_B_0,:SYS_B_1)=:SYS_B_2)

                                   

                                  Note

                                  -----

                                     - dynamic sampling used for this statement (level=2)

                                   

                                   

                                  SQL> drop index ind1;

                                   

                                  Index dropped.

                                   

                                  SQL> alter table t1 add virt_vc generated always as (substr(vc,1,3)) virtual;

                                   

                                  Table altered.

                                   

                                  SQL> create index ind2 on t1(virt_vc);

                                   

                                  Index created.

                                   

                                  SQL> select * from t1 where virt_vc = 'xxx';

                                   

                                  no rows selected

                                   

                                  SQL_ID  f7yur8r20buak, child number 0

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

                                  select * from t1 where virt_vc = :"SYS_B_0"

                                   

                                  Plan hash value: 3688005278

                                   

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

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

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

                                  |   0 | SELECT STATEMENT            |      |       |       |     1 (100)|          |

                                  |   1 |  TABLE ACCESS BY INDEX ROWID| T1   | 1     |    30 |     1 (0)  | 00:00:01 |

                                  |*  2 |   INDEX RANGE SCAN          | IND2 |     1 |       |     1   (0)| 00:00:01 |

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

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                     2 - access("VIRT_VC"=:SYS_B_0)

                                   

                                  Note

                                  -----

                                     - dynamic sampling used for this statement (level=2)


                                  Best regards

                                  Mohamed Houri

                                  • 14. Re: High physical reads when use substr function
                                    K.S.I.

                                    Hi.

                                    IND2  INDEX_TYPE = 'FUNCTION-BASED NORMAL'

                                    select

                                            i.INDEX_NAME,i.FUNCIDX_STATUS,i.index_type

                                    from

                                            user_indexes i

                                    where

                                            i.index_name = 'IND2';

                                     

                                    SQL>

                                     

                                    INDEX_NAME                     FUNCIDX_STATUS    INDEX_TYPE

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

                                    IND2                                   ENABLED                FUNCTION-BASED NORMAL

                                     

                                     

                                    1 2 Previous Next