12 Replies Latest reply: Nov 22, 2012 4:29 AM by Dom Brooks RSS

    sql execution plan

    Vili Dialis
      Hello ,
      I have one sql that's always pickin the same bad plan, with the same hash value, no matter how many time I flush it from the shared pool with the exec DBMS_SHARED_POOL.PURGE
      or with the alter system flush shared pool.
      Also, that query, is presented in the OEM and v$sql with the different sql_id's ,but they all use that bad plan with the same plan hash value.
      That's visible throughOEM, but at the same time, when that query is explained in the sqlplus , plan is different, better, with the cost of 10 ,and for the same query plan is in the OEM shown with diferenet hash with
      the cost of 50000, having 99% of the cost related to full table scan, and therefore ,the dev team and users are saying we have perf regresion.
      What to do in this case ? There is no baseline or profile for that sql , but no matter what i do , plan is always the same.
      this is 11gr2.
      cursor_sharing=exact
      This was fine before few days ago.
        • 1. Re: sql execution plan
          JohnWatson
          What have you actually tried? You mentioned baselines and profiles, for instance. Or you could hint it. Or use a stored outline.
          • 2. Re: sql execution plan
            Dom Brooks
            I have one sql that's always pickin the same bad plan, with the same hash value
            Also, that query, is presented in the OEM and v$sql with the different sql_id's ,but they all use that bad plan with the same plan hash value.
            Please provide proper root cause analysis.
            SQL_ID is a hash.
            The same sql will not have different SQL_IDs across executions or even environments.

            See [url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to Post a SQL Tuning Request
            • 3. Re: sql execution plan
              Pavan Kumar
              Hi,

              Hello ,
              I have one sql that's always pickin the same bad plan, with the same hash value, no matter how many time I flush it from the shared pool with the exec DBMS_SHARED_POOL.PURGE
              or with the alter system flush shared pool.
              sql hash value is generated based on the query context - md5sum check and conversion or dbms_sqltune_util0 which was provided by oracle ( if is same - then it won't differ at all). Coming to flushing of share pool you are making your optimizer to hard parse ever time... nothing but hammering your optimizer. Coming to bad plan - tell me first what is the good plan for that ?

              If you have with you then, you must compare both plan and see what you find the difference.
              Next drill down to index stats, segment stats, Optimizer environment (any changes)
              Check the session stats fro v$sesstat during the start of query to end of query snap shot of time.
              Under the explan plain of it further we can go, but based on your inputs.
              Also, that query, is presented in the OEM and v$sql with the different sql_id's ,but they all use that bad plan with the same plan hash value.
              That's visible throughOEM, but at the same time, when that query is explained in the sqlplus , plan is different, better, with the cost of 10 ,and for the same query plan is in the OEM shown with diferenet hash with
              the cost of 50000, having 99% of the cost related to full table scan, and therefore ,the dev team and users are saying we have perf regresion.
              What to do in this case ? There is no baseline or profile for that sql , but no matter what i do , plan is always the same.
              this is 11gr2.
              cursor_sharing=exact
              This was fine before few days ago.
              post the environment and things across with formatted text, then we can check across.

              - Pavan Kumar N
              • 4. Re: sql execution plan
                Vili Dialis
                The thing is that
                I have sql which plan in the OEM is aprox with the cost of 50000, doing a full table scan,
                and when I take that query and do an explain plan from the sqlplus with the same user which is executing throuhg the application,
                plan is with the cost of 10, doing the TABLE ACCESS BY LOCAL INDEX ROWID where in the OEM that step is done with FTS.
                I don't know , could that be because application is sendind that sql with the binds to the database and then that query is displaied with the literals in the OEM,
                and for such sql plan from the sqlplus is better ?
                Thank you for your time and patience
                • 5. Re: sql execution plan
                  Vili Dialis
                  SQL> show parameter optimizer
                  
                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  optimizer_capture_sql_plan_baselines boolean     FALSE
                  optimizer_dynamic_sampling           integer     2
                  optimizer_features_enable            string      11.2.0.3
                  optimizer_index_caching              integer     95
                  optimizer_index_cost_adj             integer     5
                  optimizer_mode                       string      ALL_ROWS
                  optimizer_secure_view_merging        boolean     TRUE
                  optimizer_use_invisible_indexes      boolean     FALSE
                  optimizer_use_pending_statistics     boolean     FALSE
                  optimizer_use_sql_plan_baselines     boolean     TRUE
                  SQL>
                  I also found out that query is not always the same , only one 'AND' part have sometimes different search valueas, it's a string changed in the form
                  of -> like 'ABC%' , like 'ACB%' and so on.
                  But the cost in the OEM is always the same, around 50000, and always with the same plan doing the FTS.
                  And when some of those queris is explained from the sqlplus , then sometimes is doing TABLE ACCESS BY LOCAL INDEX ROWID and completes in second, cost is around 10.
                  All those queris in the OEM are registred executing between 10 and 15 minutes.
                  • 6. Re: sql execution plan
                    Dom Brooks
                    Post the execution plan from explain plan, e.g.
                    explain plan for
                    select...;
                    
                    select * from table(dbms_xplan.display);
                    Post the actual execution plan from an actual execution, e.g.
                    select * from table(dbms_xplan.display_cursor('<an executed sql id>','<child number>'));
                    If you're licensed for diagnostic pack, you can also pull back actual execution metrics using real time sql monitoring, e.g.
                    select dbms_sqltune.report_sql_monitor('<sql_id>');
                    This does not include the predicates section, so it's always nice to supplement this rtsm output with the predicates from dbms_xplan.display_cursor.

                    For both include the predicates section.
                    • 7. Re: sql execution plan
                      Vili Dialis
                      this is explain plan
                      SQL> select * from table(dbms_xplan.display);
                      
                      PLAN_TABLE_OUTPUT
                      ------------------------------------------------------------------------------------------------------------------------------------------------------
                      Plan hash value: 2231330766
                      
                      -------------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                                          | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      -------------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                                   |                            |     1 |   958 |    13   (8)| 00:00:01 |       |       |
                      |*  1 |  VIEW                                              |                            |     1 |   958 |    13   (8)| 00:00:01 |       |       |
                      |*  2 |   COUNT STOPKEY                                    |                            |       |    |       |          |       |       |
                      |   3 |    VIEW                                            |                            |     1 |   945 |    13   (8)| 00:00:01 |       |       |
                      |*  4 |     SORT ORDER BY STOPKEY                          |                            |     1 |   369 |    13   (8)| 00:00:01 |       |       |
                      |   5 |      NESTED LOOPS OUTER                            |                            |     1 |   369 |    12   (0)| 00:00:01 |       |       |
                      |   6 |       NESTED LOOPS OUTER                           |                            |     1 |   360 |    11   (0)| 00:00:01 |       |       |
                      |   7 |        NESTED LOOPS OUTER                          |                            |     1 |   350 |    10   (0)| 00:00:01 |       |       |
                      |   8 |         NESTED LOOPS OUTER                         |                            |     1 |   341 |     9   (0)| 00:00:01 |       |       |
                      |   9 |          NESTED LOOPS OUTER                        |                            |     1 |   334 |     8   (0)| 00:00:01 |       |       |
                      |* 10 |           FILTER                                   |                            |       |    |       |          |       |       |
                      |  11 |            NESTED LOOPS OUTER                      |                            |     1 |   327 |     7   (0)| 00:00:01 |       |       |
                      |  12 |             NESTED LOOPS OUTER                     |                            |     1 |   309 |     6   (0)| 00:00:01 |       |       |
                      |  13 |              NESTED LOOPS OUTER                    |                            |     1 |   289 |     5   (0)| 00:00:01 |       |       |
                      |  14 |               NESTED LOOPS OUTER                   |                            |     1 |   280 |     4   (0)| 00:00:01 |       |       |
                      |  15 |                NESTED LOOPS OUTER                  |                            |     1 |   257 |     3   (0)| 00:00:01 |       |       |
                      |  16 |                 NESTED LOOPS OUTER                 |                            |     1 |   162 |     2   (0)| 00:00:01 |       |       |
                      |  17 |                  PARTITION LIST ALL                |                            |     1 |   154 |     1   (0)| 00:00:01 |     1 |     4 |
                      |* 18 |                   TABLE ACCESS BY LOCAL INDEX ROWID| KLIJENTI                   |     1 |   154 |     1   (0)| 00:00:01 |     1 |     4 |
                      |* 19 |                    INDEX RANGE SCAN                | NAZIV_KL_I                 |     3 |    |     1   (0)| 00:00:01 |     1 |     4 |
                      |  20 |                  TABLE ACCESS BY INDEX ROWID       | EVIDENCIJE_TIMOVA          |     1 |     8 |     1   (0)| 00:00:01 |       |       |
                      |* 21 |                   INDEX UNIQUE SCAN                | ETM_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  22 |                 TABLE ACCESS BY INDEX ROWID        | EVIDENCIJE_ZIRORACUNA      |     1 |    95 |     1   (0)| 00:00:01 |       |       |
                      |* 23 |                  INDEX RANGE SCAN                  | ZRI_KLI_FK_I               |     2 |    |     1   (0)| 00:00:01 |  |       |
                      |  24 |                TABLE ACCESS BY INDEX ROWID         | ORGANIZACIJSKE_STRUKTURE   |     1 |    23 |     1   (0)| 00:00:01 |       |       |
                      |* 25 |                 INDEX UNIQUE SCAN                  | ORG_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  26 |               TABLE ACCESS BY INDEX ROWID          | PODTIPOVI_SEGMENTACIJE_KLI |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 27 |                INDEX UNIQUE SCAN                   | PDK_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  28 |              TABLE ACCESS BY INDEX ROWID           | MJESTA                     |     1 |    20 |     1   (0)| 00:00:01 |       |       |
                      |* 29 |               INDEX UNIQUE SCAN                    | MJE_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  30 |             TABLE ACCESS BY INDEX ROWID            | RIZICI                     |     8 |   144 |     1   (0)| 00:00:01 |       |       |
                      |* 31 |              INDEX RANGE SCAN                      | RIZICI_MB_KDSGRA_VAZIDO    |     8 |    |     1   (0)| 00:00:01 |  |       |
                      |  32 |           TABLE ACCESS BY INDEX ROWID              | SEKTORI                    |     1 |     7 |     1   (0)| 00:00:01 |       |       |
                      |* 33 |            INDEX UNIQUE SCAN                       | SEK_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  34 |          TABLE ACCESS BY INDEX ROWID               | DIREKCIJE                  |     1 |     7 |     1   (0)| 00:00:01 |       |       |
                      |* 35 |           INDEX UNIQUE SCAN                        | DIR_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  36 |         TABLE ACCESS BY INDEX ROWID                | LOKACIJE                   |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 37 |          INDEX UNIQUE SCAN                         | LKC_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  38 |        TABLE ACCESS BY INDEX ROWID                 | SLUZBE                     |     1 |    10 |     1   (0)| 00:00:01 |       |       |
                      |* 39 |         INDEX UNIQUE SCAN                          | SLU_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      |  40 |       TABLE ACCESS BY INDEX ROWID                  | POSTE                      |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 41 |        INDEX UNIQUE SCAN                           | PTT_PK                     |     1 |    |     1   (0)| 00:00:01 |  |       |
                      -------------------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - filter("RN">=1)
                         2 - filter(ROWNUM<21)
                         4 - filter(ROWNUM<21)
                        10 - filter(NVL("RZ"."RZC_VAZIDO",0)=0)
                        18 - filter("KL"."KLI_TYPE"='KDS' AND "KL"."PODTIP_KLIJENTA" IS NULL)
                        19 - access("KL"."NAZIV" LIKE 'STOMATOLO?KA%')
                             filter("KL"."NAZIV" LIKE 'STOMATOLO?KA%')
                        21 - access("KL"."ETM_ID"="ET"."ID"(+))
                        23 - access("KL"."ID"="EZR"."KLI_ID"(+))
                        25 - access("KL"."ORG_ID"="OS"."ID"(+))
                        27 - access("KL"."PDK_ID"="PDK"."ID"(+))
                        29 - access("KL"."MJE_ID"="MJ"."ID"(+))
                        31 - access("KL"."MATICNI_BROJ"="RZ"."RZC_MATBRO"(+))
                        33 - access("OS"."SEK_ID"="SE"."ID"(+))
                        35 - access("OS"."DIR_ID"="DI"."ID"(+))
                        37 - access("OS"."LKC_ID"="LO"."ID"(+))
                        39 - access("OS"."SLU_ID"="SL"."ID"(+))
                        41 - access("MJ"."PTT_ID"="PTT"."ID"(+))
                      
                      70 rows selected.
                      and this is the actual plan got by
                      select * from table(dbms_xplan.display_cursor('6aakksdrczbab'));
                      PLAN_TABLE_OUTPUT
                      ------------------------------------------------------------------------------------------------------------------------------------------------------
                      SQL_ID  6aakksdrczbab, child number 0
                       
                      Plan hash value: 3351316652
                      
                      ------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                      ------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                            |                            |       |       | 44539 (100)|          |       |       |
                      |*  1 |  VIEW                                       |                            |    17 | 16286 | 44539   (2)| 00:08:55 |       |       |
                      |*  2 |   COUNT STOPKEY                             |                            |       |       |         |     |       |       |
                      |   3 |    VIEW                                     |                            |    17 | 16065 | 44539   (2)| 00:08:55 |       |       |
                      |*  4 |     SORT ORDER BY STOPKEY                   |                            |    17 |  6273 | 44539   (2)| 00:08:55 |       |       |
                      |   5 |      NESTED LOOPS OUTER                     |                            |    17 |  6273 | 44538   (2)| 00:08:55 |       |       |
                      |*  6 |       FILTER                                |                            |       |       |         |     |       |       |
                      |   7 |        NESTED LOOPS OUTER                   |                            |    17 |  6154 | 44537   (2)| 00:08:55 |       |       |
                      |   8 |         NESTED LOOPS OUTER                  |                            |    16 |  5504 | 44529   (2)| 00:08:55 |       |       |
                      |   9 |          NESTED LOOPS OUTER                 |                            |    16 |  5392 | 44528   (2)| 00:08:55 |       |       |
                      |  10 |           NESTED LOOPS OUTER                |                            |    16 |  5248 | 44527   (2)| 00:08:55 |       |       |
                      |  11 |            NESTED LOOPS OUTER               |                            |    16 |  5088 | 44526   (2)| 00:08:55 |       |       |
                      |  12 |             NESTED LOOPS OUTER              |                            |    16 |  4944 | 44525   (2)| 00:08:55 |       |       |
                      |  13 |              NESTED LOOPS OUTER             |                            |    16 |  4624 | 44524   (2)| 00:08:55 |       |       |
                      |  14 |               NESTED LOOPS OUTER            |                            |    16 |  4480 | 44523   (2)| 00:08:55 |       |       |
                      |  15 |                NESTED LOOPS OUTER           |                            |    16 |  4112 | 44522   (2)| 00:08:55 |       |       |
                      |  16 |                 NESTED LOOPS OUTER          |                            |    16 |  2592 | 44520   (2)| 00:08:55 |       |       |
                      |  17 |                  PARTITION LIST ALL         |                            |    16 |  2464 | 44519   (2)| 00:08:55 |     1 |     4 |
                      |* 18 |                   TABLE ACCESS FULL         | KLIJENTI                   |    16 |  2464 | 44519   (2)| 00:08:55 |     1 |     4 |
                      |  19 |                  TABLE ACCESS BY INDEX ROWID| EVIDENCIJE_TIMOVA          |     1 |     8 |     1   (0)| 00:00:01 |       |       |
                      |* 20 |                   INDEX UNIQUE SCAN         | ETM_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  21 |                 TABLE ACCESS BY INDEX ROWID | EVIDENCIJE_ZIRORACUNA      |     1 |    95 |     1   (0)| 00:00:01 |       |       |
                      |* 22 |                  INDEX RANGE SCAN           | ZRI_KLI_FK_I               |     2 |       |     1   (0)| 00:00:01 |       |       |
                      |  23 |                TABLE ACCESS BY INDEX ROWID  | ORGANIZACIJSKE_STRUKTURE   |     1 |    23 |     1   (0)| 00:00:01 |       |       |
                      |* 24 |                 INDEX UNIQUE SCAN           | ORG_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  25 |               TABLE ACCESS BY INDEX ROWID   | PODTIPOVI_SEGMENTACIJE_KLI |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 26 |                INDEX UNIQUE SCAN            | PDK_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  27 |              TABLE ACCESS BY INDEX ROWID    | MJESTA                     |     1 |    20 |     1   (0)| 00:00:01 |       |       |
                      |* 28 |               INDEX UNIQUE SCAN             | MJE_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  29 |             TABLE ACCESS BY INDEX ROWID     | POSTE                      |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 30 |              INDEX UNIQUE SCAN              | PTT_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  31 |            TABLE ACCESS BY INDEX ROWID      | SLUZBE                     |     1 |    10 |     1   (0)| 00:00:01 |       |       |
                      |* 32 |             INDEX UNIQUE SCAN               | SLU_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  33 |           TABLE ACCESS BY INDEX ROWID       | LOKACIJE                   |     1 |     9 |     1   (0)| 00:00:01 |       |       |
                      |* 34 |            INDEX UNIQUE SCAN                | LKC_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  35 |          TABLE ACCESS BY INDEX ROWID        | DIREKCIJE                  |     1 |     7 |     1   (0)| 00:00:01 |       |       |
                      |* 36 |           INDEX UNIQUE SCAN                 | DIR_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      |  37 |         TABLE ACCESS BY INDEX ROWID         | RIZICI                     |     8 |   144 |     1   (0)| 00:00:01 |       |       |
                      |* 38 |          INDEX RANGE SCAN                   | RIZICI_MB_KDSGRA_VAZIDO    |     8 |       |     1   (0)| 00:00:01 |       |       |
                      |  39 |       TABLE ACCESS BY INDEX ROWID           | SEKTORI                    |     1 |     7 |     1   (0)| 00:00:01 |       |       |
                      |* 40 |        INDEX UNIQUE SCAN                    | SEK_PK                     |     1 |       |     1   (0)| 00:00:01 |       |       |
                      ------------------------------------------------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - filter("RN">=1)
                         2 - filter(ROWNUM<21)
                         4 - filter(ROWNUM<21)
                         6 - filter(NVL("RZ"."RZC_VAZIDO",0)=0)
                        18 - filter((INSTR("KL"."NAZIV",'STOMATOLOŐKA',1,1)=1 AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')>=HEXTORAW('696E5A50146E5
                                    A4B5A6B46140001010101010101010101010100')  AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')<HEXTORAW('696E5A50146E5A4B5A6B4615000
                                    1010101010101010101010100')  AND NLSSORT("KL"."KLI_TYPE",'nls_sort=''CROATIAN''')=HEXTORAW('4623690001010100')  AND
                                    "KL"."PODTIP_KLIJENTA" IS NULL))
                        20 - access("KL"."ETM_ID"="ET"."ID")
                        22 - access("KL"."ID"="EZR"."KLI_ID")
                        24 - access("KL"."ORG_ID"="OS"."ID")
                        26 - access("KL"."PDK_ID"="PDK"."ID")
                        28 - access("KL"."MJE_ID"="MJ"."ID")
                        30 - access("MJ"."PTT_ID"="PTT"."ID")
                        32 - access("OS"."SLU_ID"="SL"."ID")
                        34 - access("OS"."LKC_ID"="LO"."ID")
                        36 - access("OS"."DIR_ID"="DI"."ID")
                        38 - access("KL"."MATICNI_BROJ"="RZ"."RZC_MATBRO")
                        40 - access("OS"."SEK_ID"="SE"."ID")
                      
                      
                      89 rows selected.
                      • 8. Re: sql execution plan
                        Pavan Kumar
                        Hi Vili,
                        TABLE ACCESS FULL         | KLIJENTI                   
                        the second plan opt for FTS. As stated in previous couple of posts, I think it better to re-check code from application point of view, how the query is getting submitted. Instead of that I say call the block of code db level
                        (package/procedure etc - that makes your life job easy and application too).

                        - Pavan Kumar N
                        • 9. Re: sql execution plan
                          Dom Brooks
                          The difference in predicates might be giving a big hint towards the difference between your explain plan environment and the actual execution environment.

                          Specifically the difference in NLS_SORT applied to your table columns, resulting in a full table scan:
                            18 - filter((INSTR("KL"."NAZIV",'STOMATOLOŐKA',1,1)=1 AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')>=HEXTORAW('696E5A50146E5
                                        A4B5A6B46140001010101010101010101010100')  AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')<HEXTORAW('696E5A50146E5A4B5A6B4615000
                                        1010101010101010101010100')  AND NLSSORT("KL"."KLI_TYPE",'nls_sort=''CROATIAN''')=HEXTORAW('4623690001010100')  AND
                                        "KL"."PODTIP_KLIJENTA" IS NULL))
                          If this is representative of what you want to do then you could apply function-based indexes to those columns using NLS_SORT.

                          The queries are also different in that one uses LIKE and one INSTR - the two aren't comparable.
                          A trailing-only wildcard can use an index:
                          |* 18 |                   TABLE ACCESS BY LOCAL INDEX ROWID| KLIJENTI                   |     1 |   154 |     1   (0)| 00:00:01 |     1 |     4 |
                          |* 19 |                    INDEX RANGE SCAN                | NAZIV_KL_I                 |     3 |    |     1   (0)| 00:00:01 |     1 |     4 |
                          ...
                            19 - access("KL"."NAZIV" LIKE 'STOMATOLO?KA%')
                                 filter("KL"."NAZIV" LIKE 'STOMATOLO?KA%')
                          INSTR can't:
                          |* 18 |                   TABLE ACCESS FULL         | KLIJENTI                   |    16 |  2464 | 44519   (2)| 00:08:55 |     1 |     4 |
                          ...
                            18 - filter((INSTR("KL"."NAZIV",'STOMATOLOŐKA',1,1)=1 AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')>=HEXTORAW('696E5A50146E5
                                        A4B5A6B46140001010101010101010101010100')  AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')<HEXTORAW('696E5A50146E5A4B5A6B4615000
                                        1010101010101010101010100')  AND NLSSORT("KL"."KLI_TYPE",'nls_sort=''CROATIAN''')=HEXTORAW('4623690001010100')  AND
                                        "KL"."PODTIP_KLIJENTA" IS NULL))
                          Have you considered using Oracle Text?
                          http://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm
                          • 10. Re: sql execution plan
                            Iordan Iotzov
                            The CBO must come with a filtering coefficient for every filter and access predicate. How do you think Oracle CBO would come up with these numbers for a filter like this:
                            18 - filter((INSTR("KL"."NAZIV",'STOMATOLOŐKA',1,1)=1 AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')>=HEXTORAW('696E5A50146E5
                            A4B5A6B46140001010101010101010101010100') AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')<HEXTORAW('696E5A50146E5A4B5A6B4615000
                            1010101010101010101010100') AND NLSSORT("KL"."KLI_TYPE",'nls_sort=''CROATIAN''')=HEXTORAW('4623690001010100') AND
                            "KL"."PODTIP_KLIJENTA" IS NULL))
                            ?
                            Well, it will guess, and that is part of the reason why statements like that often run into problems.

                            One way to improve the situation (as Dom Brooks suggested) is create function based indexes and/or virtual columns, and maybe some histograms on those. That way the CBO would have better idea how many records would be eliminated from a clause like this
                            AND NLSSORT("KL"."NAZIV",'nls_sort=''CROATIAN''')<HEXTORAW('696E5A50146E5A4B5A6B4615000
                            1010101010101010101010100')
                            Using simple structures (LIKE) over complex (INSTR) might also help.

                            Another way is go force dynamic sampling on KLIJENTI. While you will pay a small penalty to get the correct cardinality, you would likely get an execution plan that better reflects the data.

                            You can get more info about confidence of cardinality estimates during my session at Hotsos 2013.

                            Iordan Iotzov
                            http://iiotzov.wordpress.com/
                            • 11. Re: sql execution plan
                              Vili Dialis
                              thank you all for the intereseting answers .
                              but to me it's strange that query is using that bad plan with 40k+ cost always - this is visible and confirmed through EM,
                              but at the same time , that high cost sql when executed in sqlplus completes in second and have differenct , better plan.
                              What am I missing here ?
                              Is this due to bind variables on the app side, as developer told me that they don't have fixed this query , but binds,
                              and this is also confirmed as I ussualy see different predicate values.
                              • 12. Re: sql execution plan
                                Dom Brooks
                                What am I missing here ?
                                How about all the stuff that we've mentioned previously?

                                Why's it strange?
                                We've pointed out the crucial differences.

                                To summarise:
                                1. LIKE with a trailing wildcard only can use an index
                                2. INSTR cannot so it has to do a full table scan
                                (2a. Using an Oracle Text index is probably a better option)
                                3. Your application is using different NLS settings to your SQL*Plus session so an NLS_SORT function is applied to a number of columns.