1 2 Previous Next 24 Replies Latest reply on Sep 26, 2013 6:28 AM by davidp 2

    Weird issue about execution plan with IN clause

    NestaFeng

      OS: CentOS 5

      Oracle 10.2.0.1

       

      We are facing an weird issue.

      The table is like

      create table A (

      id varchar2(200) primary key,

      ...

      )

       

      ID's value is a little special, as it starts with "*" (Asterisk), and its pattern is '*' + 'onestring' + 'uuid'.

       

      We have a IN clause query with 20 arguments such as:

      select * from A where id in (

      '*line_item.name.uuid1.......',

      '*line_item.name.uuid2.......',

      '*line_item.name.uuid3.......',

      '*line_item.name.uuid4.......',

      '*line_item.name.uuid5.......',

      '*line_item.name.uuid6.......',

      '*line_item.name.uuid7.......',

      '*line_item.name.uuid8.......',

      '*line_item.name.uuid9.......',

      '*line_item.name.uuid10.......',

      '*line_item.name.uuid11.......',

      ....

      )

       

      The table's row count is 30M, and row count of the primary key index is also 30M.

       

      select index_name, blevel, num_rows from user_indexes where table_name = 'A';


      20 is much less than 30M, but Oracle's execution plan for this query is full table scan of A table.

      I try to remove the '*' from the id, and then the execution plan is range index scan, and then get the record by row id. I think that it should work this way.

       

      Is '*' special character in Oracle 10g?

        • 1. Re: Weird issue about execution plan with IN clause
          Priyasagi

          Hi Nesta,


          What you need actualy?

          Do you like to remove '*' from id (primary key)?

          What are all the dependent child tables?

          • 2. Re: Weird issue about execution plan with IN clause
            Paul  Horth

            Please read HOW TO: Post a SQL statement tuning request - template posting

            and follow the advice there.

             

            BTW: your choice of a primary key is suspect. You seem to have multiple pieces of information

            in the column - so it is not in 1st normal form. It also will have some meaning to the business,

            thus making it prone to change - which would be a problem if you have child tables.

             

            Have you considered a surrogate PK?

            • 3. Re: Weird issue about execution plan with IN clause
              Martin Preiss

              the asterisk should not be a problem. If it was not the PK index I could imagine problems with the large identical prefixes of the column that could irritate the CBO when working with histograms. But for a single column PK this is not a problem.

               

              So I would suggest that you create an execution plan for the query using the full table scan - and perhaps you could try to force the CBO to use the index with an index hint and get the plan for this access path too. The plans should show us why the CBO thinks that the FTS could be a good idea.

               

              Apart from this: 10.2.0.1 is quite old and perhaps not the most stable release.

              • 4. Re: Weird issue about execution plan with IN clause
                jihuyao

                The PK seems not case sensitive.  Can function index with lower() make it work?  Or function index with substr() excluding *?  Or in this case function index with substr on {uuidx}?

                • 5. Re: Weird issue about execution plan with IN clause
                  NestaFeng

                  Read 20 rows from 30M rows table with index, so I think that Oracle should not do full table scan.

                  • 6. Re: Weird issue about execution plan with IN clause
                    NestaFeng

                    Yes, the primary key is not from Oracle sequence, and has some meaning, and this is a legacy system. If we change the primary key, it needs more effort. If there is no work around, I think that we have to.

                     

                    The execution plan is:

                    Execution Plan

                    SELECT STATEMENT  ALL_ROWSCost: 133,889  Bytes: 4,038,806,648  Cardinality: 37,053,272 

                             1 TABLE ACCESS FULL TABLE nesta.A Cost: 133,889  Bytes: 4,038,806,648  Cardinality: 37,053,272

                    • 7. Re: Weird issue about execution plan with IN clause
                      NestaFeng

                      The identical prefix is almost 30 characters.

                      I do a simple test with Oracle 11g, and the table row count is 50K, and the index works.

                      The execution plan is:

                      OPERATION OBJECT_NAME COST LAST_CR_BUFFER_GETS
                          SELECT STATEMENT 34  

                              INLIST ITERATOR 25

                                  TABLE ACCESS A 34 25
                                      INDEX A_PK_IDX 18 25

                                          Access Predicates

                                              OR

                                                  T1.ID='*ABCDEFghijklmnOpqRstuvw.xyzabcdefgh.252f61e77c6f49c2b0f9b61a234ba770'

                                                  ...

                      So Asterisk should not be a problem as you mentioned, and I guess that 30M rows change the index behavior of Oracle.

                       

                       

                       


                      • 8. Re: Weird issue about execution plan with IN clause
                        davidp 2

                        "The identical prefix is almost 30 characters."

                        I think that is the problem. From memory, the 10.2 optimiser looks at the first 30 characters of the bind variable, and checks that against the histogram. If most of that is prefix, it may expect millions of rows to match. I agree with MartinPreiss that it should not do that if you have a simple unique index on ID, but 10.2.0.1 might.

                         

                        Try EXEC DBMS_STATS.DELETE_COLUMN_STATS(username, tablename, columnname, no_invalidate => False)

                        Then re-run the query and see what the new plan is. If it changes to use the index, you know that was the problem.

                         

                        If it is the problem, you can re-gather statistics with:

                        DBMS_STATS.GATHER_TABLE_STATS(username, tablename,  method_opt=>'for all columns size auto, for columns ID size 1')

                         

                        10.2.0.1 is a bad choice of version unless you are using Oracle XE. 10.2.0.1 is completely un-patched with plenty of bugs. 10.2.0.5 was the final patch release of 10.2 and anything short of 11.2 is obsolete now (and you should upgrade to 11.2.0.3 not an earlier version).

                        • 9. Re: Weird issue about execution plan with IN clause
                          Martin Preiss

                          does the query in 10g use the index if you use an index hint:

                          select /*+ index(A your_index_name) */ *

                            from A

                          where id IN (...)

                           

                          (with the table alias used in the query and the name of the index)

                          If the index is not used (and the index syntax is ok) then this is a sign for a basic problem with the index. If it is used then this could be a costing problem.

                          • 10. Re: Weird issue about execution plan with IN clause
                            NestaFeng

                            davidp2, Thanks very much.

                             

                            I am not very clear about the two procedues, and the questions are:

                            1. EXEC DBMS_STATS.DELETE_COLUMN_STATS

                            If statistics are deleted, how does Oracle get the execution plan? If the index is used, why? based on RBO?


                            2. DBMS_STATS.GATHER_TABLE_STATS(username, tablename,  method_opt=>'for all columns size auto, for columns ID size 1')

                            "ID size 1" is used to limit the histogram bucket for primary key "ID", and then Oracle can keep using same execution plan as the plan after delete statistics.

                            Is it?

                            • 11. Re: Weird issue about execution plan with IN clause
                              davidp 2

                              1. With column statistics deleted, Oracle will not have a histogram for the column. It will fall back on the table statistics that include the column cardinality and min and max values. The Cost Based Optimiser will still run - the table statistics are still there.

                               

                              2. Yes, "ID size 1" lets it gather statistics for the column but not produce a histogram (1 entry = no histogram). Yes that will let it keep using a similar plan after the delete statistics.

                               

                              I like MartinPreiss's suggestion of testing with an index hint. It will show the cardinality estimates and actual counts. I suggest you get plans and show them to us:

                              ALTER SESSION set statistics_level=ALL;

                              set autotrace traceonly statistics;

                              select *

                                from A where id IN (...);

                              set autotrace off;

                              select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

                              set autotrace traceonly statistics;

                              select /*+ index(A your_index_name) */ *

                                from A where id IN (...);

                              set autotrace off;

                              select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

                              I'm more interested in the plans than the statistics. I'm using set autotrace traceonly statistics; mainly as a way of suppressing output of the rows.

                              1 person found this helpful
                              • 12. Re: Weird issue about execution plan with IN clause
                                NestaFeng

                                MartinPreiss/davidp2,

                                 

                                I am not on customer site, and am waiting for the execution plan for index hint.

                                Thanks very much.

                                • 13. Re: Weird issue about execution plan with IN clause
                                  NestaFeng

                                  1. Execution plan with index hint before delete statistics

                                     SELECT STATEMENT ALL_ROWSCost: 86,501 Bytes: 2,630,173,527 Cardinality: 24,581,061
                                  1 TABLE ACCESS FULL TABLE test.A Cost: 86,501 Bytes: 2,630,173,527 Cardinality: 24,581,061

                                   

                                  Still full table scan

                                   

                                  2. Execution plan without index hint after delete statistics

                                   

                                  Still full table scan

                                   

                                  3. Execution plan with index hint after delete statistics

                                   

                                     SELECT STATEMENT ALL_ROWSCost: 136,524 Bytes: 42,949,052 Cardinality: 394,028
                                  3 INLIST ITERATOR
                                  2 TABLE ACCESS BY INDEX ROWID TABLE test.ACost: 136,524 Bytes: 42,949,052 Cardinality: 394,028
                                  1 INDEX UNIQUE SCAN INDEX (UNIQUE) test.PK_A Cost: 1,658 Cardinality: 157,611

                                   

                                  INLIST with unique scan index

                                  • 14. Re: Weird issue about execution plan with IN clause
                                    NestaFeng

                                    In Oracle 11.2.0.1.0, I create a table:

                                        CREATE TABLE A

                                          (

                                      id varchar2(200) not null,

                                         sub_id varchar2(2) not null,

                                         value varchar2(4000),

                                         creator_id number,

                                        creation_date date,

                                         last_modified_by number,

                                         last_modified_date date,  

                                            CONSTRAINT a_pk PRIMARY KEY (id, sub_id)

                                         )

                                     

                                    This time the primary key is composite id which is id and sub_id, and id has same pattern as mentioned above.

                                    The row size is 0.7M, with the same SQL query:

                                     

                                    Before delete column id statistics:

                                    OPERATION OBJECT_NAME COST LAST_CR_BUFFER_GETS
                                    Select Statement   S 4103   
                                      Table Access (FULL)  A 4103 14935
                                           
                                    Filter Predicates

                                     

                                    After delete column id statistics:

                                    OPERATION OBJECT_NAME COST LAST_CR_BUFFER_GETS
                                    SELECT STATEMENT    2745   
                                      INLIST ITERATOR      25
                                        TABLE ACCESS A 2745 25
                                               
                                    INDEX
                                    A_PK_IDX 34 25
                                                   
                                    Access Predicates

                                     

                                    So  my question is that Oracle 11g optimiser also looks at first 30 or other number characters when primary key has more than one column, it is right?

                                    1 2 Previous Next