6 Replies Latest reply: Dec 12, 2011 6:13 AM by 637538 RSS

    iot columns that should be in top segment  are in overflow segment

    637538
      Hi ,
      i created an iot with about 45 columns
      i used the including syntax to keep only 15 columns in the top segments while all others in the overfolow segment.

      this reduced the size of the top segment dramatically so i thought if i query the table and use only the 15 columns
      it will greatly improve the Elapsed time since I/O was my bottleneck.

      running the query had the same results as all columns are in the top segment.

      checking which files oracle is accessing with 10046 trace i discovered that it's going to the overflow segment
      ( which is reside in another tablespace and another datafile)

      the primary key consist of 5 columns
      trying another query the query with only the 5 primary key columns discovered that it's going only to the top segment.

      Am i missing something ?

      here is my test case

      block size is 32k
      oracle 11.2.0.2
      --create IOT
      
      CREATE
        TABLE "ZVIKAG"."Z1_FAC_OPERATIONS_OF"
        (
          "ACCOUNT_FK" NUMBER,
          "FROM_DATE_TRUNC" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ROW_TYPE_FK"            NUMBER,
        "AGENT_FK"               NUMBER,
        "VS_HOT_LEAD_IND"        NUMBER,
        "CHANNEL_FK"             NUMBER,
        "ENG_INTERACTIVE_IND"    NUMBER,
        "IMD_CHANNEL_FK"         NUMBER,
        "ENG_SERVICE_QUEUE_FK"   NUMBER,
        "EVENT_ROOM_FK"          NUMBER,
        "ENG_SKILL_FK"           NUMBER,
        "STATUS_DURATION_MILLIS" NUMBER,
        "AGENT_STATUS_FK"        NUMBER,
        "FROM_DATE" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "FIRST_INTERVAL_IND" NUMBER,
        "ENG_AGENT_SURVEY_SUBMISSION_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_COMMUNICATION_END_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_COMMUNICATION_START_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "LP_AGENT_SESSION_ID"   VARCHAR2(400 BYTE),
        "TOTAL_DURATION_MILLIS" NUMBER,
        "EVENT_SEQ"             NUMBER,
        "ROW_GK"                NUMBER,
        "INSERT_DATE" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "UPDATE_DATE" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "DI_RU_ID"          VARCHAR2(4000 BYTE),
        "DI_ETL_VERSION_FK" NUMBER,
        "DI_APP_VERSION_FK" NUMBER,
        "DI_VS_CLOSE_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "DI_VS_CLOSE_REASON_FK" NUMBER,
        "DI_VS_OPEN_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_QUEUE_ENTER_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_QUEUE_EXIT_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_GK" NUMBER,
        "TO_DATE_TRUNC" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "ENG_INCLUDE_INTERACTION_IND" NUMBER,
        "TO_DATE" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "LAST_INTERVAL_IND"        NUMBER,
        "INSERT_REASON_FK"         NUMBER,
        "VS_ROOM_IND"              NUMBER,
        "IMD_APPROACH_TYPE_FK"     NUMBER,
        "VS_VISITOR_SESSION_GK"    NUMBER,
        "VS_LP_VISITOR_ID"         VARCHAR2(400 BYTE),
        "VS_LP_VISITOR_SESSION_ID" VARCHAR2(400 BYTE),
        "VS_START_DT" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        "VS_START_DT_TRUNC" TIMESTAMP (3)
      WITH
        LOCAL TIME ZONE,
        CONSTRAINT "FAC_OPERATIONS_IOT_OF1" PRIMARY KEY ("ACCOUNT_FK",
        "FROM_DATE_TRUNC", "ROW_TYPE_FK", "AGENT_FK", "ROW_GK") ENABLE
        )
        ORGANIZATION INDEX COMPRESS 3 PCTFREE 1 INITRANS 2 MAXTRANS 255 TABLESPACE
        "TS_OPERATIONS_OTHER" STORAGE
        (
          BUFFER_POOL DEFAULT
        )
        PCTTHRESHOLD 50 INCLUDING "ROW_GK" OVERFLOW PCTFREE 10 INITRANS 1 MAXTRANS
        255 TABLESPACE "TS_Z_ACCOUNT_100_OLD" STORAGE
        (
          BUFFER_POOL DEFAULT
        )
        PARTITION BY RANGE
        (
          "FROM_DATE_TRUNC"
        )
        (
        PARTITION "P111017" VALUES LESS THAN (TIMESTAMP    ' 2011-10-18 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111018" VALUES LESS THAN (TIMESTAMP    ' 2011-10-19 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111019" VALUES LESS THAN (TIMESTAMP    ' 2011-10-20 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111020" VALUES LESS THAN (TIMESTAMP    ' 2011-10-21 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111021" VALUES LESS THAN (TIMESTAMP    ' 2011-10-22 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111022" VALUES LESS THAN (TIMESTAMP    ' 2011-10-23 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111023" VALUES LESS THAN (TIMESTAMP    ' 2011-10-24 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111024" VALUES LESS THAN (TIMESTAMP    ' 2011-10-25 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111025" VALUES LESS THAN (TIMESTAMP    ' 2011-10-26 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111026" VALUES LESS THAN (TIMESTAMP    ' 2011-10-27 00:00:00.000000000 GMT GMT') PCTFREE 1 
        ,PARTITION "P111027" VALUES LESS THAN (TIMESTAMP    ' 2011-10-28 00:00:00.000000000 GMT GMT') PCTFREE 1 
        );
      
      
      
      
      create table zvikag.z1_t ( id number);
      
      
      
      
      
      --NEEDS TO LOAD SOME DATA
      ...
      
      
      
      
      -- start trace  check  file dwh_ora_731_zvika_check_iot_overflow11.trc
      
      
      -- run as sysdba
       alter system flush shared_pool ; 
       alter system flush buffer_cache ; 
      
      set echo on timing on
      
      
      --set autot traceonly
      
      
      alter session set tracefile_identifier = 'zvika_check_iot_overflow11';
      
      
      alter session set events = '10046 trace name context forever, level 12';
      
      insert into zvikag.z1_t
      select 1
                      FROM    zvikag.Z1_FAC_OPERATIONS_OF   fac_operations 
        WHERE  fac_operations.account_fk = 382 and  fac_operations.row_type_fk IN (1,3)
                              AND
                              (
                               fac_operations.from_date_trunc BETWEEN CAST(to_timestamp('17/10/2011 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP   WITH LOCAL TIME ZONE) 
                      AND CAST(to_timestamp('26/10/2011 23:59:59','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP        WITH LOCAL TIME ZONE))
      AND fac_operations.agent_fk IN(395729,235639,243679,290076,396272,240522,232646,396279,240717,232675,240681,235505,673653,396278,236890,396274,396275,239291,290088,232685,396271,396273,242625,396270,396268,243827,249498,396282,396269,232814,229163,396283,235541,251284,277992,250162,237528,248920,237525,277660,396253,274388,400255,400251,244950,666500,400246,282202,231101,544323,249492,242897,237118,409067,246810,240879,237513,242663,238928,246878,242423,396641,396644,396643,396642,242376,396632,240308,243035,395718,238884,277982,290495,395715,395717,238889,239375,395716,235600,277219,240917,237050,237049,237521,234451,235604,240507,240505,235605,235606,235607,228497,235608,234449,229159,235609,232644,240503,235610,235611,235612,235613,240500,229256,235614,228504,231404,237487,284384,237486,228714,284385,231075,228715,235616,235617,239332,235618,232508,235619,284386,235620,235621,234507,235622,231395,240501,240508,240499,240502,284387,231398,
                                              231397,235623,237187,231399,240498,231761,240504,231838,235624,235625,231400,284389,235626,239353,284388,235627,284391,235628,235629)
       AND
                              (
                                      (
                                              1                              = 1
                                              AND fac_operations.channel_fk IN(1,4)
                                      )
                                      OR fac_operations.row_type_fk = 3
                              )
      ;
      
      
      --set autot off
      
      
      rollback;
      
      
      alter session set events '10046 trace name context off';
      
      
      
      
      
      -- run without filter the channel_fk which not in the PK index see trace dwh_ora_11319_zvika_check_iot_overflow12.trc
      
      
      
      -- run as sysdba
       alter system flush shared_pool ; 
       alter system flush buffer_cache ; 
      
      set echo on timing on
      
      
      --set autot traceonly
      
      
      alter session set tracefile_identifier = 'zvika_check_iot_overflow12';
      
      
      alter session set events = '10046 trace name context forever, level 12';
      
      insert into zvikag.z1_t
      select 1
                      FROM    zvikag.Z1_FAC_OPERATIONS_OF   fac_operations 
        WHERE  fac_operations.account_fk = 382 and  fac_operations.row_type_fk IN (1,3)
                              AND
                              (
                               fac_operations.from_date_trunc BETWEEN CAST(to_timestamp('17/10/2011 00:00:00','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP   WITH LOCAL TIME ZONE) 
                      AND CAST(to_timestamp('26/10/2011 23:59:59','dd/mm/yyyy hh24:mi:ss') AS TIMESTAMP        WITH LOCAL TIME ZONE))
      AND fac_operations.agent_fk IN(395729,235639,243679,290076,396272,240522,232646,396279,240717,232675,240681,235505,673653,396278,236890,396274,396275,239291,290088,232685,396271,396273,242625,396270,396268,243827,249498,396282,396269,232814,229163,396283,235541,251284,277992,250162,237528,248920,237525,277660,396253,274388,400255,400251,244950,666500,400246,282202,231101,544323,249492,242897,237118,409067,246810,240879,237513,242663,238928,246878,242423,396641,396644,396643,396642,242376,396632,240308,243035,395718,238884,277982,290495,395715,395717,238889,239375,395716,235600,277219,240917,237050,237049,237521,234451,235604,240507,240505,235605,235606,235607,228497,235608,234449,229159,235609,232644,240503,235610,235611,235612,235613,240500,229256,235614,228504,231404,237487,284384,237486,228714,284385,231075,228715,235616,235617,239332,235618,232508,235619,284386,235620,235621,234507,235622,231395,240501,240508,240499,240502,284387,231398,
                                              231397,235623,237187,231399,240498,231761,240504,231838,235624,235625,231400,284389,235626,239353,284388,235627,284391,235628,235629)
       AND
                              (
                                      (
                                              1                              = 1
      --                                        AND fac_operations.channel_fk IN(1,4)
                                      )
                                      OR fac_operations.row_type_fk = 3
                              )
      ;
      
      
      --set autot off
      
      
      rollback;
      
      
      alter session set events '10046 trace name context off';
      if you need i can upload the 10046 trace file to somewhere .


      Thanks in advanced .