1 2 Previous Next 19 Replies Latest reply: May 28, 2011 3:11 AM by Martin Preiss Go to original post RSS
      • 15. Re: slow queries with inappropriate high_value for interval partition
        Martin Preiss
        Greg,

        thank you for filing the bug and for the additional details from sqlmonitor. So we have 100.000 cheap operations for the SORT GROUP BY and 100.000 more expensive operations with HASH GROUP BY (where the higher resource usage is related to the workarea operations and the aquisition of latches).

        The problem also occurs with date based partitions (I think that's not a big surprise):
        -- test_interval_date.sql
        create table test_interval_p1_&YYYYMM
          partition by range (startdate)
          interval (NUMTODSINTERVAL(1,'DAY'))
          (partition test_p1 values less than (to_date('&YYYYMM.01', 'yyyymmdd')))
        as
        select to_date('31.05.2011', 'dd.mm.yyyy') startdate
          from dual t;
        
        exec dbms_stats.gather_table_stats(user, 'TEST_INTERVAL_P1_&YYYYMM')
          
        commit;
        
        set serveroutput off  
        select /*+ gather_plan_statistics */ startdate, count(*)
          from test_interval_p1_&YYYYMM
         group by startdate;
        
        select plan_table_output
          from table( dbms_xplan.display_cursor ( NULL, NULL, 'allstats'));
         
        drop table test_interval_p1_&&YYYYMM;
        
        -- Result for 200001:
        STARTDATE    COUNT(*)
        ---------- ----------
        31.05.2011          1
        
        Abgelaufen: 00:00:00.09
        
        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------------------------------------------------------------------------------
        SQL_ID  4cn69s2rz1bzy, child number 0
        -------------------------------------
        select /*+ gather_plan_statistics */ startdate, count(*)   from
        test_interval_p1_200001  group by startdate
        
        Plan hash value: 3653245232
        
        ------------------------------------------------------------------------------------------------------------------------------------
        | Id  | Operation           | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
        ------------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |                         |      1 |        |      1 |00:00:00.08 |       6 |       |       |          |
        |   1 |  PARTITION RANGE ALL|                         |      1 |      1 |      1 |00:00:00.08 |       6 |       |       |          |
        |   2 |   HASH GROUP BY     |                         |   4170 |      1 |      1 |00:00:00.08 |       6 |   107M|  9724K|     1/0/0|
        |   3 |    TABLE ACCESS FULL| TEST_INTERVAL_P1_200001 |   4170 |      1 |      1 |00:00:00.01 |       6 |       |       |          |
        ------------------------------------------------------------------------------------------------------------------------------------
        
        -- Result for 190001:
        
        STARTDATE    COUNT(*)
        ---------- ----------
        31.05.2011          1
        
        Abgelaufen: 00:00:16.06
        
        PLAN_TABLE_OUTPUT
        ------------------------------------------------------------------------------------------------------------------------------------
        SQL_ID  9utxqnjsrjdd2, child number 0
        -------------------------------------
        select /*+ gather_plan_statistics */ startdate, count(*)   from
        test_interval_p1_190001  group by startdate
        
        Plan hash value: 1483556902
        
        ------------------------------------------------------------------------------------------------------------------------------------
        | Id  | Operation           | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
        ------------------------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT    |                         |      1 |        |      1 |00:00:16.03 |       6 |       |       |          |
        |   1 |  PARTITION RANGE ALL|                         |      1 |      1 |      1 |00:00:16.03 |       6 |       |       |          |
        |   2 |   HASH GROUP BY     |                         |  40694 |      1 |      1 |00:00:15.97 |       6 |   107M|  9724K|     1/0/0|
        |   3 |    TABLE ACCESS FULL| TEST_INTERVAL_P1_190001 |  40694 |      1 |      1 |00:00:00.04 |       6 |       |       |          |
        ------------------------------------------------------------------------------------------------------------------------------------
        But I guess that most people would not start their date ranges "ab urbe condita", so the problem is perhaps not so obvious.

        Regards

        Martin Preiss
        • 16. Re: slow queries with inappropriate high_value for interval partition
          Martin Preiss
          perhaps the columns Pstart and Pstop are not pointless for interval partitions:
          -- test_interval_pruning.sql
           
          set verify off
          undefine high_value
           
          create table test_interval_p1_&&high_value
            partition by range (id)
            interval (1)
            (partition test_p1 values less than (&high_value))
          as
          select 100000 id
            from dual t
          ;
           
          commit;
          
          exec dbms_stats.gather_table_stats(user, 'TEST_INTERVAL_P1_&high_value')
          
          set autot trace exp
          
          select id, count(*)
            from test_interval_p1_&high_value
           group by id;
           
          select id, count(*)
            from test_interval_p1_&high_value
           where id = 100000
           group by id;
          
          set autot off
          
          drop table test_interval_p1_&&high_value;
          
          ------------------------------------------------------------------
          -- all rows:
          --------------------------------------------------------------------------------------------------------------
          | Id  | Operation           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
          --------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT    |                        |     1 |     3 |     4  (25)| 00:00:01 |       |       |
          |   1 |  PARTITION RANGE ALL|                        |     1 |     3 |     4  (25)| 00:00:01 |     1 |1048575|
          |   2 |   HASH GROUP BY     |                        |     1 |     3 |     4  (25)| 00:00:01 |       |       |
          |   3 |    TABLE ACCESS FULL| TEST_INTERVAL_P1_80000 |     1 |     3 |     3   (0)| 00:00:01 |     1 |1048575|
          --------------------------------------------------------------------------------------------------------------
          
          -- only the partition with data:
          ------------------------------------------------------------------------------------------------------------------
          | Id  | Operation               | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
          ------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT        |                        |     1 |     3 |     3   (0)| 00:00:01 |       |       |
          |   1 |  SORT GROUP BY NOSORT   |                        |     1 |     3 |     3   (0)| 00:00:01 |       |       |
          |   2 |   PARTITION RANGE SINGLE|                        |     1 |     3 |     3   (0)| 00:00:01 | 20002 | 20002 |
          |*  3 |    TABLE ACCESS FULL    | TEST_INTERVAL_P1_80000 |     1 |     3 |     3   (0)| 00:00:01 | 20002 | 20002 |
          ------------------------------------------------------------------------------------------------------------------
          So the Partition with data is 20002 (and partition pruning is working) - and without a filtering condition we have a range from 1 - 1048575. The number 1048575 seems to be the upper bound for the interval partition count:
          create table t1
            partition by range (id)
            interval (1)
            (partition test_p1 values less than (1))
          as
          select 1 id
            from dual t
           where 1 = 0
          ;
          
          SQL> insert into t1 values (1000000);
          
          1 Zeile wurde erstellt.
          
          Abgelaufen: 00:00:00.04
          SQL> insert into t1 values (1048574);
          
          1 Zeile wurde erstellt.
          
          Abgelaufen: 00:00:00.04
          SQL> insert into t1 values (1048575);
          insert into t1 values (1048575)
                      *
          FEHLER in Zeile 1:
          ORA-14401: Eingefügter Partitionsschlüssel außerhalb der angegebenen Partition
          
          
          Abgelaufen: 00:00:00.01
          SQL> insert into t1 values (1048576);
          insert into t1 values (1048576)
                      *
          FEHLER in Zeile 1:
          ORA-14400: Eingefügter Partitionsschlüssel kann keiner Partition zugeordnet werden
          The two different error-codes are also interesting.

          Regards

          Martin Preiss
          • 17. Re: slow queries with inappropriate high_value for interval partition
            Hemant K Chitale
            11g(R1, R2) is limited to 1024K-1 (i.e. 1048575) partitions.

            With range partitioning and an interval of 1, the 1048575th partition can hold a value of 1048574.

            Seems to be a bug in interval partitioning that it cannot return the correct error message when it attempts to exceed the limit on the number of partitions.



            Hemant K Chitale

            Edited by: Hemant K Chitale on May 28, 2011 10:46 AM
            • 18. Re: slow queries with inappropriate high_value for interval partition
              864728
              As per Oracle concept guide "Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition." Does that imply first few ranges must be specified explicitly where range is constant?

              With partition interval 1 (partitions 1-100001-200001-300001), execs = 300k (hash group by slower)
              With partition interval 100000 (partitions 100001-200001-300001), execs = 3 (hash group by normal) - Fist two partitions were defined with create table and third one was created by interval as new data came in.

              Thanks,
              Vishal Desai
              • 19. Re: slow queries with inappropriate high_value for interval partition
                Martin Preiss
                in the VLDB guide: http://download.oracle.com/docs/cd/E11882_01/server.112/e16541/part_admin001.htm#VLDBG1088 you find the following definition: "The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition."

                I hope this is an answer to your question.

                Regards

                Martin Preiss

                Edited by: mpreiss on May 28, 2011 10:09 AM

                Edited by: mpreiss on May 28, 2011 10:10 AM
                1 2 Previous Next