1 2 Previous Next 15 Replies Latest reply: Aug 29, 2014 7:53 AM by user13080027 RSS

    Full Scan

    user13080027

      Oracle Version: 11gr2

      OS: Red Hat Linux

       

      There are two tables in relationship parent and child:

       

      CREATE TABLE par_tab

      (

      list_id NUMBER (10) NOT NULL,

      process_id NUMBER(6) NOT NULL,

      file_name VARCHAR2(255) NOT NULL,

      status INTEGER NOT NULL,

      inserted_date TIMESTAMP NOT NULL,

      last_updated TIMETAMP NOT NULL,

      session_id VARCHAR2(32)

      ) PARTITION BY RANGE (process_id)

      (

        PARTITION PTMAX VALUES LESS THAN (MAXVALUE)

      );

       

      CREATE UNIQUE INDEX par_Tab_pk ON par_tab (list_id);

      ALTER TABLE par_tab ADD CONSTRAINT par_tab_pk PRIMARY KEY (LIST_ID) USING INDEX;

       

      CREATE INDEX par_tab_idx1 ON par_tab (process_id, process_Date) LOCAL;

      CREATE INDEX par_tab_idx2 ON par_tab (session_id) LOCAL;

       

      CREATE TABLE ch_tab

      (

      id NUMBER(10) NOT NULL,

      list_id NUMBER(10) NOT NULL,

      code VARCHAR2(30) NOT NULL,

      code_type VARCHAR2(8) NOT NULL,

      symbol VARCHAR2(20) NOT NULL,

      description VARCHAR2(80)

      thr_id NUMBER(1) DEFAULT 0 NOT NULL,

      CONSTRAINT fk_ch_par_tab FOREIGN KEY (list_id) REFERENCES par_tab (list_id)

      ) PARTITION BY REFERENCE (fk_ch_par_tab);

       

      CREATE UNIQUE INDEX ch_tab_pk ON ch_tab (id);

      ALTER TABLE ch_tab ADD CONSTRAINT ch_tab_pk PRIMARY KEY (id) USING INDEX;

       

      CREATE INDEX ch_tab_idx1 ON ch_tab (list_id, symbol) LOCAL;

      CREATE INDEX ch_tab_idx2 ON ch_tab (list_id, code, code_type) LOCAL;

       

      There partitions for 20 years such as one partition holds data for one month. The values in column PAR_TAB.PROCESS_DATE represents dates, for example 20140827, which is 2014-Aug-27.

       

      Only four partitions are full of data. These are the first four partitions in the tables. Extents are allocated only for them.

      Both tables and their indexes are analyzed, statistics are up-to-date on tables and indexes (statistics are not stale).

       

      Data volumes in Parent table PAR_TAB are:

      * 1st partition - 601 rows

      * 2nd partition - 503 rows

      * 3rd partition - 689 rows

      * 4th partition - 601 rows

       

      Data volumes in Child table CH_TAB are:

      * 1st partition - 95,915,105 rows

      * 2nd partition - 95,915,203 rows

      * 3rd partition - 97,887,531 rows

      * 4th partition, 95,915,105 rows

       

      All constraints are validated and enabled on the two tables and all indexes are in valid state.

       

      When query like this one is run, oracle optimizer goes for full scan on the child:

       

      SELECT *

      FROM ch_tab

      WHERE list_id = 123456789;

       

      Query like above returns apporx 956,347 rows. The query plan is TABLE ACCESS FULL - oracle scans the whole partition where values LIST_ID = 123456789 are stored, even though the total count of rows for LIST_ID = 123456789 is much less than 10% of the total number of rows in the same partition. Oracle behaves the same, goes for full scan, with every search by LIST_ID. ORacle goes for FULL SCAN even if the final result set is just 14,000 rows (against 95 million in the whole partition).

      If I hint the query with /*+ INDEX (ch_tab)*/, then it uses index and finishes significantly faster.

      The estimation for using index is for higher cost compared with the plan using full scan, but at the same time the estimated time with usage of index is much much faster and the final result set is much smaller than 10 percent of total rows in partition.

       

      What could be influential in this scenario, so oracle always decides to go for tabel access full?

       

      Please share your knowledge.

       

      Thanks.

        • 1. Re: Full Scan
          JohnWatson2

          You are hiding the truth: your first CREATE TABLE statement is impossible. I havent looked at the rest. What code did you really run?

          • 2. Re: Full Scan
            user13080027

            Sorry,

            I corrected the code. Just I wrote wrongly the name of one column, but the problem is not in this, so no hiding of anything here.

            • 3. Re: Full Scan
              jgarry

              How are you gathering statistics?

              Please show the plan (although use the new formatting here).

              Maybe the partioning on process_id is confusing something.  There may be a clue in Re: Local index:prefixed or non-prefixed though I don't have time to look closely just now.

              • 4. Re: Full Scan
                sol.beach

                user13080027 wrote:

                 

                Oracle Version: 11gr2

                OS: Red Hat Linux

                 

                There are two tables in relationship parent and child:

                 

                CREATE TABLE par_tab

                (

                list_id NUMBER (10) NOT NULL,

                process_id NUMBER(6) NOT NULL,

                file_name VARCHAR2(255) NOT NULL,

                status INTEGER NOT NULL,

                inserted_date TIMESTAMP NOT NULL,

                last_updated TIMETAMP NOT NULL,

                session_id VARCHAR2(32)

                ) PARTITION BY RANGE (process_id)

                (

                  PARTITION PTMAX VALUES LESS THAN (MAXVALUE)

                );

                 

                There partitions for 20 years such as one partition holds data for one month. The values in column PAR_TAB.PROCESS_DATE represents dates, for example 20140827, which is 2014-Aug-27.

                 

                One of us needs a reality check.

                What PROCESS_DATE column in PAR_TAB table?

                 

                Stop obfuscating reality, because you can NOT keep the facts straight.

                • 5. Re: Full Scan
                  Maran Viswarayar

                  >The estimation for using index is for higher cost compared with the plan using full scan

                   

                  This could be driving the FULL scan but why is the cost high.

                   

                  Besides the others recommendations...add this as well as question to answer

                   

                  1. Has the plan changed or it has always been the same, How often you gather stats?

                  2. OPTIMIZER_INDEX_COST_ADJ - Value ?

                  • 6. Re: Full Scan
                    Harmandeep Singh

                    It surely seems to be issue with gathering stats as Maran has pointed out.

                     

                    Try to gather stats on both tables at higher percentage say , 20% and check any change in plan.

                     

                    Another thought, since you are using the "partition by reference", so partitions on the child table are as per the msater table. In this case partitions are as per process_id.

                     

                    If functionally possible(which seems possible as user must be interested in data related to particular process id, also what user will do with 14000 records ...?),

                     

                    Add the master table to the query along with process_id. It gives optimizer better options to think and in turn better access path.

                     

                    Ex

                    SELECT *

                    FROM ch_tab, par_tab

                    WHERE list_id = 123456789

                    and ch_tab.id=par_tab.id

                    and par_tab.process_id ='<>';

                     

                    Regards,

                    Harman

                    • 7. Re: Full Scan
                      Girish Sharma

                      Optimizer will choose full table scan if ;

                      1.Leading column of index have null values

                      2.Select query do not have primary key column in the select columns

                       

                      Regards

                      Girish Sharma

                      • 8. Re: Full Scan
                        user13080027

                        Hello everyone and thanks for the replies.

                         

                        Here are some answers:

                         

                        The value of parameter optimizer_index_cost_adj is 100:

                         

                        SELECT name, type, value, display_value FROM v$parameter WHERE name LIKE '%optimizer_index_cost_adj%';

                        NAME                                TYPE VALUE                          DISPLAY_VALUE           

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

                        optimizer_index_cost_adj                3 100                            100                     

                        1 row selected.

                        Here is the oputput from the query plan for this SELECT:

                         

                        SELECT * FROM ch_tab b WHERE list_id = 877784;

                         

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

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

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

                        |  0 | SELECT STATEMENT          |                              |      |      |  1661 (100)|          |      |      |

                        |  1 |  PARTITION REFERENCE SINGLE|                              |  155K|  5921K|  1661  (2)| 00:00:20 |  KEY |  KEY |

                        |*  2 |  TABLE ACCESS FULL        | CH_TAB |  155K|  5921K|  1661  (2)| 00:00:20 |  KEY |  KEY |

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

                         

                        Predicate Information (identified by operation id):

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

                         

                          2 - filter("LIST_ID"=877784)

                         

                        The query plan for the same if I use index:

                         

                        SELECT /*+ INDEX (b)*/* FROM ch_tab b WHERE list_id = 877784;

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

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

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

                        |   0 | SELECT STATEMENT                                      |                                            |       |       | 23974 (100)|          |       |       |

                        |   1 |  PARTITION REFERENCE SINGLE                 |                                            |   155K|  5921K| 23974   (1)| 00:04:48 |   KEY |   KEY |

                        |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| CH_TAB                              |   155K|  5921K| 23974   (1)| 00:04:48 |   KEY |   KEY |

                        |*  3 |    INDEX RANGE SCAN                                    | CH_TAB_IDX1                    |   656 |       |   815   (1)| 00:00:10 |   KEY |   KEY |

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

                         

                        Predicate Information (identified by operation id):

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

                         

                           3 - access("LIST_ID"=877784)

                         

                        The statistics (I'm listing only the partitions with data and the next one - ILP201411 - without data; the rest are also empty like ILP201411):

                         

                        TABLE_NAME  PARTITION_NAME                   NUM_ROWS     BLOCKS LAST_ANALYZED              

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

                        PAR_TAB    ILP201411                                                                       

                        PAR_TAB    ILP201410                                                                       

                        PAR_TAB    ILP201409                             601         64 27-AUG-14                  

                        PAR_TAB    ILP201408                             503         64 27-AUG-14                  

                        PAR_TAB    ILP201407                             689         64 27-AUG-14                  

                        PAR_TAB    ILP201406                             601         64 27-AUG-14      

                         

                        TABLE_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS LAST_ANALYZED              

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

                        CH_TAB                         ILP201411                                                                       

                        CH_TAB                         ILP201410                                                                       

                        CH_TAB                         ILP201409                        50669619     312951 27-AUG-14                  

                        CH_TAB                         ILP201408                        95519105     593901 27-AUG-14                  

                        CH_TAB                         ILP201407                        85370605     523327 27-AUG-14                  

                        CH_TAB                         ILP201406                        95519105     593901 27-AUG-14

                         

                        Column "LIST_ID" do not have empty values in CH_TAB:

                         

                        TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE  COLUMN_ID  NUM_NULLS

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

                        CH_TAB                         ID                             NUMBER                        22             10          0 N                 1          0

                        CH_TAB                         LIST_ID                        NUMBER                        22             10          0 N                 2          0

                        CH_TAB                         CODE                           VARCHAR2                      30                           N                 3          0

                        CH_TAB                         CODE_TYPE                      VARCHAR2                       8                           N                 4          0

                        CH_TAB                         SYMBOL                         VARCHAR2                      20                           Y                 5    2429676

                        CH_TAB                         DESCRIPTION                    VARCHAR2                      80                           Y                 6  225591466

                        CH_TAB                         THR_ID                         NUMBER                        22              1          0 N                 7          0

                         

                         

                        7 rows selected.

                        • 9. Re: Full Scan
                          user13080027

                          Alos, I forget to answer to these questions:

                           

                          Yes, the plan changed at some point in time and starting doing full scan. Initially it was going for index as it is on other environments with the same tables.

                           

                          In the SELECT list there is column which is primary key and this is ID as I select *.

                           

                          The index it used to use before - CH_TAB_IDX2, which is on columns LIST_ID, SYMBOL - is NORMAL, but column LIST_ID is NOT NULL.

                           

                          I will try to regather stats with high estimate percent as suggested and will check if anything changes.

                           

                          Thanks again.

                          • 10. Re: Full Scan
                            jgarry

                            Please show us the exact command used to gather statistics.  Please see https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

                            • 11. Re: Full Scan
                              user13080027

                              Hi,

                               

                              Now I just used this one:

                               

                              EXEC dbms_stats.gather_table_stats(USER, 'CH_TAB', ESTIMATE_PERCENT => 20, CASCADE => TRUE, NO_INVALIDATE => FALSE, FORCE => TRUE);

                              The same for parent:

                               

                              EXEC dbms_stats.gather_table_stats(USER, 'PAR_TAB', ESTIMATE_PERCENT => 20, CASCADE => TRUE, NO_INVALIDATE => FALSE, FORCE => TRUE);

                               

                              Now I checked and it still goes for the query plan with the full scan.

                              • 12. Re: Full Scan
                                jgarry

                                What is your method_op?  Do you have histograms?

                                • 13. Re: Full Scan
                                  user13080027

                                  I don't specify METHOD_OPT, I use what is by default. I checked with dbms_stats.get_prefs and for the two tables the default is:

                                  FOR ALL COLUMNS SIZE AUTO

                                   

                                  Please, tell me how can I check for histograms?

                                   

                                  I'm pasting this:

                                   

                                   

                                   

                                  TABLE_NAME                     COLUMN_NAME                    DATA_TYPE       NUM_DISTINCT LOW_VALUE                      HIGH_VALUE                        DENSITY  NUM_NULLS NUM_BUCKETS GLOBAL_STATS HISTOGRAM            

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

                                  CH_TAB                         ID                                  NUMBER                      235011915                 C51437423A5D                  C5172D52525A                              4.2551E-09          0                            1 YES          NONE                 

                                  CH_TAB                         LIST_ID                        NUMBER                                 1395                 C356063A                          C3584F09                                     .002645503           0                         254 YES          HEIGHT BALANCED      

                                  CH_TAB                         CODE                           VARCHAR2                      2137070                  2D31303031                     787331303238393534343431       4.6793E-07           0                             1 YES          NONE                 

                                  CH_TAB                         CODE_TYPE               VARCHAR2                                   4                 414450                                49534E                                         2.1276E-09          0                              4 YES          FREQUENCY            

                                  CH_TAB                         SYMBOL                      VARCHAR2                        1837176                2D31303031                       585330393630353033313333       5.4431E-07         2471970                    1 YES          NONE                 

                                  CH_TAB                         DESCRIPTION            VARCHAR2                                    2                 4455504C49434154455F53594D424F 4455504C49434154455F53594D424F                                 .5  228907035           1 YES          NONE                 

                                                                                                                                                                                        4C7C                                                      4C7C4455504C49434154455F53594D                                                                     

                                                                                                                                                                                         4C7C                                                      424F                                                                                               

                                  CH_TAB                         THR_ID                      NUMBER                                           6                80                                    C106                                                2.1276E-09           0                              6 YES          FREQUENCY            

                                   

                                   

                                  7 rows selected.

                                  1 2 Previous Next