This discussion is archived
1 2 Previous Next 19 Replies Latest reply: May 28, 2011 1:11 AM by Martin Preiss Go to original post RSS
  • 15. Re: slow queries with inappropriate high_value for interval partition
    Martin Preiss Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points