2 Replies Latest reply: Jul 3, 2014 10:03 AM by Iordan Iotzov RSS

    Index range scan query changes to full table scan

    user13080027

      Hello experts,

       

      Database version: 11.2.0.3.0 Oracle Database 11g Enterprise Edition 64 bit

      Red Hat Linux

       

      There is a table T1 that is partitioned by reference. Partitions data might differ, for example in first partition to have 400 mln, second 90 lmn, third and forth 100 mln, etc.

      There is an index IDX1 on two columns C1 and C2, normal, local. Column C1 (leading column in the index) is FK to the parent table. Based on this FK the table T1 is partitioned.

      Each partition holds data for one calendar month.

      Statistics are gathered and they show:

       

      Partition 1 - January - 200 mln records

      Partition 2 - Februaty - 400 mln of records

      Partition 3 - March - 90 lmn of records

      Partition 4 - April - 100 mln of records

      Partition 5 - May - 100 mln of records

      Partition 6 - June - 26 mln of records

      Partition 7 - 0 records

       

      There is a procedure in one package that is run many times per day. It opens a cursor in this way:

       

      OPEN curs1 FOR 'SELECT * FROM t1 WHERE c1 IN (' || list_of_values_separated_by_comma || ') AND c2 = ' || c2_value;

       

      The execution of the cursor always returns about 2,3, up to 5 records on average.

       

      No bind variables are used.

       

      When the procedure with this cursor was run before July 1st it ran for INDEX RANGE SCAN on IDX1 (based on C1 and C2).

      When the procedure was run on July 1st and subsequently it started to go for FULL TABLE SCAN on T1, scanning all partitions.

       

      cursor_sharing is EXACT.

       

      Gathering of statistics on partition 7 was not enough to make the optimizer choose IDX1 again. It chose it when the SELECT is run outside the scope of the package manually. But run as part of the cursor within the package it would continue for the full table scan until the execution plans for T1 were invalidated.

       

      How the optimizer is influenced by the statistics and the lack of bind variables and usage of explicit cursor?

       

      I ran a test with separate "test" partitioned table but I couldn't reproduce the case. Even with statistics for 0 records for the next partition, lack of bind variables and excplicit cursor, the optimizer continued to use index with data from the next partition (where statistics show 0 records).

       

      What else the optimizer might have been influenced by?

       

      Thank you very much.

        • 1. Re: Index range scan query changes to full table scan
          Dom Brooks

          Explicit cursor is irrelevant.

           

           

          The lack of binds means that you get a sql statement optimised every time for the unique combination of values supplied.

           

           

          The main influences for optimizer are statistics and parameters.

           

           

          Gathering statistics by default is done with no_invalidate of TRUE so that related existing execution plans are not all invalidate at the same time potentially causing a rush of hard parsing.

           

           

          For further comment, more information is required.

           

          See:

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

           

           

          You can extract past plans from AWR using DBMS_XPLAN.DISPLAY_AWR (subject to diagnostic license).

          • 2. Re: Index range scan query changes to full table scan
            Iordan Iotzov

            If the SQL you issued outside of the package is identical to the one issued from inside the package, then both of them should have the same SQL ID.

             

            When you run the SQL from outside the package, you should see one entry in V$SQL_SHARED_CURSOR for the SQL ID.

             

            When you run the SQL from inside the package, you should see another entry in V$SQL_SHARED_CURSOR for the same SQL ID.

             

            Reviewing those two records in V$SQL_SHARED_CURSOR might give you a clue.

             

              

            HTH,

             

            Iordan Iotzov