Parallel Parking – What is causing the Query to be Executed in Parallel?

31 07 2012

July 31, 2012

I encountered an interesting OTN thread that caused me to stop and think for a couple of minutes.  The OP (original poster) in the OTN thread wondered why parallel execution was used for a query when the parallel degree for the table accessed by the query and its indexes were specified to have a parallel degree of 1.  For some reason, while the OP provided DBMS_XPLAN generated execution plan output, the Note section from the output was not included in the OTN post.  With parallel execution enabled in the session (ALTER SESSION ENABLE PARALLEL QUERY;) the execution plan showed a cost of 2,025.  With parallel execution disabled in the session (ALTER SESSION DISABLE PARALLEL QUERY;) the execution plan showed a cost of 36,504.  From this, we can determine that the parallel degree for the query is 20 (36504 / 2025 / 0.9 = 20.03).  The table is partitioned on a date column, apparently partitioned by month.

Interesting?

Could the parallel execution be caused by the PARALLEL_AUTOMATIC_TUNING parameter’s value?  The DBMS_XPLAN output indicates that the OP is using Oracle Database 11.2.0.2, where that parameter is deprecated.  What about the PARALLEL_DEGREE_POLICY parameter?  When that parameter is set to AUTO, the query optimizer is free to plan a parallel execution for a query, even when the tables (and their indexes) accessed by the query are set to a parallel degree of 1.

A table creation script for a couple of tests (note that the table created in the test case is using INTERVAL partitioning using an approach very similar to that found on page 576 in the book Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition except that I have a DATE column in the table rather than a TIMESTAMP column):

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  C1 NUMBER,
  C2 VARCHAR2(10),
  C3 DATE,
  C4 VARCHAR2(20))
  PARTITION BY RANGE(C3)
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')));

INSERT INTO
  T1
SELECT
  ROWNUM,
  RPAD(ROWNUM,10,'A'),
  TRUNC(SYSDATE)+MOD(ROWNUM-1,3000),
  RPAD('A',20,'A')
FROM
  (SELECT
    1 C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<100000),
  (SELECT
    1 C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<1000);

COMMIT; 

Let’s see, a Cartesian join between a rowsource with 1,000 rows and a rowsource with 100,000 rows produces (on 11.2.0.1, 11.2.0.2, and 11.2.0.3):

99899001 rows created. 

Interesting, I might have to investigate that oddity at a later time.  I was expecting 1,000 * 100,000 = 100,000,000 rows.

Let’s try a simple test:

select count(*) from t1;

SET LINESIZE 140
SET PAGESIZE 1000

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1

Plan hash value: 2705263620

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       | 59271 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |   115M| 59271   (2)| 00:07:55 |     1 |1048575|
|   3 |    TABLE ACCESS FULL | T1   |   115M| 59271   (2)| 00:07:55 |     1 |1048575|
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

1,048,575 partitions… I was expecting about 100 partitions because there should be 3,000 distinct dates in column C3.  Maybe I should find some time to investigate?  Moving on…

To test the theory that the unexpected parallel execution might be caused by a non-default value for the PARALLEL_DEGREE_POLICY parameter, we need to first use the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure to indicate performance metrics for the server.  To save time, I found a blog article (by another OakTable Network member) that shows a back-door approach that does not require the CALIBRATE_IO procedure (the usual warnings apply here – we are technically modifying objects in the SYS schema).  The back-door approach, when connected as the SYS user (note that the script first selects from RESOURCE_IO_CALIBRATE$ so that if values are currently present, those values may be restored after testing completes):

SELECT
  *
FROM
  RESOURCE_IO_CALIBRATE$;

DELETE FROM
  RESOURCE_IO_CALIBRATE$;

INSERT INTO
  RESOURCE_IO_CALIBRATE$
VALUES(
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP,
  0,
  0,
  200,
  0,
  0);

COMMIT; 

I will start by posting results that I obtained from Oracle Database 11.2.0.3.

Let’s try the previous select from T1 again (note that I am first setting the PARALLEL_DEGREE_POLICY parameter at the session level to its default value):

ALTER SESSION SET PARALLEL_DEGREE_POLICY=MANUAL;

select count(*) from t1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5bc0v4my7dvr5',NULL,'TYPICAL'));

SQL_ID  5bc0v4my7dvr5, child number 0
-------------------------------------
select count(*) from t1

Plan hash value: 2705263620

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       | 59271 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |   115M| 59271   (2)| 00:00:03 |     1 |1048575|
|   3 |    TABLE ACCESS FULL | T1   |   115M| 59271   (2)| 00:00:03 |     1 |1048575|
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Let’s try again with a modified value for that parameter:

ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

select count(*) from t1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5bc0v4my7dvr5',NULL,'TYPICAL'));

SQL_ID  5bc0v4my7dvr5, child number 2
-------------------------------------
select count(*) from t1

Plan hash value: 974985148

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       | 32790 (100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   115M| 32790   (2)| 00:00:02 |     1 |1048575|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |   115M| 32790   (2)| 00:00:02 |     1 |1048575|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 2 

In the above, the Note section indicates that the automatic degree of parallelism was set to 2, and that dynamic sampling was performed at level 2.  I have yet to collect statistics on table T1, so that explains why dynamic sampling was used when the query was executed with and without parallel execution.  If you take a close look at the OTN thread that is linked to at the start of this blog article, you will see that the dynamic sampling level was set to 6, but only for the parallel execution.  The simple answer for this change in behavior is that it is expected, as described in this article by Maria Colgan (another OakTable Network member).  We can confirm that the cost calculation is working as expected: 59271 / 32790 / 0.9 = 2.01.

Let’s try executing the query of table T1 using a WHERE clause that is similar to what is found in the OTN thread:

ALTER SESSION SET PARALLEL_DEGREE_POLICY=MANUAL;

select count(*) from t1
WHERE C3 BETWEEN to_date('01/08/2012', 'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  1ggk1hy0r22kt, child number 0
-------------------------------------
select count(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2744578615

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |   235K(100)|          |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     9 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  2286K|    19M|   235K(100)| 00:00:10 |    69 |    71 |
|*  3 |    TABLE ACCESS FULL      | T1   |  2286K|    19M|   235K(100)| 00:00:10 |    69 |    71 |
--------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C3"<=TO_DATE(' 2012-10-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement (level=2) 

Let’s try again, this time with the non-default value for the PARALLEL_DEGREE_POLICY:

ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

select count(*) from t1
WHERE C3 BETWEEN to_date('01/08/2012', 'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  1ggk1hy0r22kt, child number 1
-------------------------------------
select count(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2946076746

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       |   835K(100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     9 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     9 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |     9 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   112M|   968M|   835K(100)| 00:00:33 |    69 |    71 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |   112M|   968M|   835K(100)| 00:00:33 |    69 |    71 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      SHARED(16)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C3"<=TO_DATE(' 2012-10-14
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 16 because of degree limit 

Notice anything strange… really strange about the above output?  The new version of the query is limited to just 3 partitions, yet the calculated cost of the non-parallel execution is approximately 235,000 (much higher than the calculated cost of selecting from the full table without the WHERE clause) – that is just one reason why relying on cost for tuning performance purposes (as suggested by a couple of books) is potentially misleading.  But wait, the plan using parallel execution with an automatic degree of parallelism of 16 has a plan cost of approximately 835,000 and the optimizer still selected that plan!  I strongly suspect that a query optimizer bug caused a miscalculation of the plan cost; I could not reproduce the odd cost calculation on Oracle Database 11.2.0.1 or 11.2.0.2.  Still suspicious of the cost in the execution plan, at the end of testing I dropped and recreated the table.  After recreating the test table, the following execution plan was generated with a less crazy cost calculation:

SQL_ID  1ggk1hy0r22kt, child number 1
-------------------------------------
select count(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2946076746

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |       | 23822 (100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     9 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     9 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |     9 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  2286K|    19M| 23822 (100)| 00:00:01 |    69 |    71 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  2286K|    19M| 23822 (100)| 00:00:01 |    69 |    71 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      SHARED(11)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter(("C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "C3"<=TO_DATE(' 2012-10-14
              00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 11 

Let’s double-check the calculated cost in the parallel execution plan: 235,000 / 23822 / 0.9 = 10.96 – that calculated cost is consistent with the Computed Degree of Parallelism.

I am curious about whether or not dynamic sampling might affect just the parallel execution.  Let’s collect statistics on table T1 with 100% sampling (note that it could take a long time for this statistics collection to complete):

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',ESTIMATE_PERCENT=>NULL)

Let’s try our query again (note that I have forced a hard parse of the SQL statement by changing the capitalization of a couple parts of the SQL statement):

ALTER SESSION SET PARALLEL_DEGREE_POLICY=MANUAL;

SELECT COUNT(*) from t1
WHERE C3 BETWEEN to_date('01/08/2012', 'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  2q2uz15vyzqsy, child number 1
-------------------------------------
SELECT COUNT(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2744578615

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  1816 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  2531K|    19M|  1816   (3)| 00:00:01 |    69 |    71 |
|*  3 |    TABLE ACCESS FULL      | T1   |  2531K|    19M|  1816   (3)| 00:00:01 |    69 |    71 |
--------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C3"<=TO_DATE(' 2012-10-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Notice in the above that the execution plan for the non-parallel execution has a calculated cost that decreased from 235,000 to just 1,816, even though the same Pstart (69) and Pstop (71) columns are listed, and approximately the same estimated number of rows (~2,286,000 vs ~2,531,000) are expected to be returned.  So, if you are attempting to performance tune by attempting to simply reduce the calculated cost of a query (not a valid approach), you might be lead to believe that collecting statistics improved the performance of this query by a factor of 129.4 (the performance did improve a little due to dynamic sampling no longer being required, but the performance did not improve nearly that much).

Let’s try the query again with a modified value for the PARALLEL_DEGREE_POLICY parameter:

ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

select COUNT(*) from t1
WHERE C3 BETWEEN to_date('01/08/2012', 'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  2q2uz15vyzqsy, child number 0
-------------------------------------
SELECT COUNT(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2744578615

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  1816 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  2531K|    19M|  1816   (3)| 00:00:01 |    69 |    71 |
|*  3 |    TABLE ACCESS FULL      | T1   |  2531K|    19M|  1816   (3)| 00:00:01 |    69 |    71 |
--------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C3"<=TO_DATE(' 2012-10-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

The Note section indicates that parallel execution was considered, but not used.  So, after collecting statistics, the parallel degree for this query decreased from 16 (or 11 in the non-bug affected execution plan) to 1 – a non-parallel execution.

The above output was generated with Oracle Database 11.2.0.3 using the following system (CPU) statistics:

COLUMN PNAME FORMAT A14
COLUMN PVAL2 FORMAT A20

SELECT
  PNAME,
  PVAL1,
  PVAL2
FROM
  SYS.AUX_STATS$;

PNAME               PVAL1 PVAL2
-------------- ---------- ----------------
STATUS                    COMPLETED
DSTART                    07-11-2012 15:11
DSTOP                     07-11-2012 15:11
FLAGS                   1
CPUSPEEDNW     1720.20725
IOSEEKTIM              10
IOTFRSPEED           4096
SREADTIM                8
MREADTIM               10
CPUSPEED             2664
MBRC                   16
MAXTHR           19181568
SLAVETHR

While experimenting with Oracle Database 11.2.0.2, I used the following system (CPU) statistics:

COLUMN PNAME FORMAT A14
COLUMN PVAL2 FORMAT A20

SELECT
  PNAME,
  PVAL1,
  PVAL2
FROM
  SYS.AUX_STATS$;

PNAME               PVAL1 PVAL2
-------------- ---------- ----------------
STATUS                    COMPLETED
DSTART                    02-29-2012 19:07
DSTOP                     02-29-2012 19:07
FLAGS                   1
CPUSPEEDNW     2116.57559
IOSEEKTIM              10
IOTFRSPEED           4096
SREADTIM               .4
MREADTIM               .8
CPUSPEED             1922
MBRC                   16
MAXTHR          155910144
SLAVETHR

11.2.0.2 showed the following execution plan after collecting statistics for table T1 and setting the PARALLEL_DEGREE_POLICY parameter to AUTO:

SQL_ID  81dw7f2yz0jxp, child number 1
-------------------------------------
select COUNT(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2744578615

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  4082 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  2531K|    19M|  4082  (29)| 00:00:01 |    69 |    71 |
|*  3 |    TABLE ACCESS FULL      | T1   |  2531K|    19M|  4082  (29)| 00:00:01 |    69 |    71 |
--------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C3"<=TO_DATE(' 2012-10-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

In the above execution plan, notice that the calculated cost is 4,082 (this is higher than the calculated cost returned by Oracle Database 11.2.0.3).  The SREADTIM and MREADTIM system statistics were manually set to very small numbers – due to a bug in Oracle Database 11.2.0.1 and 11.2.0.2 these system statistics could be set to much large values, such as 2,491 and 9,163, respectively.  What might happen to the calculated cost if we were to increase the SREADTIM and MREADTIM system statistics by a factor of 10?  Such a change would indicate to the query optimizer that each disk access will required 10 times longer to complete.  Should the calculated cost of this query increase or decrease after changing the system statistics?

First, let’s manually alter the value of the system statistics:

EXEC DBMS_STATS.SET_SYSTEM_STATS('SREADTIM',4)
EXEC DBMS_STATS.SET_SYSTEM_STATS('MREADTIM',8)

Next, execute the query and display the execution plan (notice that capitalization of the word SELECT was changed to force a hard parse of the query):

ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;

SELECT COUNT(*) from t1
WHERE C3 BETWEEN to_date('01/08/2012', 'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));

SQL_ID  2q2uz15vyzqsy, child number 1
-------------------------------------
SELECT COUNT(*) from t1 WHERE C3 BETWEEN to_date('01/08/2012',
'dd/mm/yyyy') and to_date('14/10/2012', 'dd/mm/yyyy')

Plan hash value: 2744578615

--------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |  3020 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |      |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|      |  2531K|    19M|  3020   (4)| 00:00:01 |    69 |    71 |
|*  3 |    TABLE ACCESS FULL      | T1   |  2531K|    19M|  3020   (4)| 00:00:01 |    69 |    71 |
--------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("C3"<=TO_DATE(' 2012-10-14 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C3">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Notice the changes in the execution plan?  The calculated cost shown in the execution plan decreased from 4,082 to 3,020, and the %CPU for the third line in the execution plan decreased from 29 to 4.  Why did the calculated cost decrease when the optimizer is informed that disk accesses require 10 times as long to complete?  For the answer, take a look at this blog article written by Randolf Geist (another OakTable Network member).

OK, so I drifted a bit from the focus of the OTN message thread that provoked this article.  What do you think caused parallel execution to be used when the OP executed the query found in the OTN thread?


Actions

Information

15 responses

31 07 2012
Tony Sleight

Charles, the reason your code generated 998999001 rows was due to the connect by level being < 1000 and < 1000000 resulting in the product 999 * 999999 = 99899901.

I shall now try and digest the rest of your post!

31 07 2012
Charles Hooper

Tony,

Thank you for pointing that out. I completely missed the missing = sign (should have been <= 1000 and <= 100000).

One oddity down, a couple more to go. It might be interesting to see if anyone else is able to trigger/duplicate the 835K cost bug for the parallel query, where the parallel execution calculated cost was higher than the calculated cost of the serial execution, yet parallel execution was still used.

1 08 2012
Mustafa KALAYCI

Charles,

This is Mustafa KALAYCI who is the owner of OTN thread. this really surprised me! I was just looking for some oracle blogs and incidentally saw your article. Thanks for your tests, in my system I will also try to get some trace and also another dba team is talking with oracle about that.

by the way, in my db PARALLEL_DEGREE_POLICY is also set to MANUAL.

2 08 2012
Charles Hooper

Mustafa,

Great that you found this blog article. The problem that you descriibed in the OTN thread is an interesting one, and I think that Tony Sleight has provided a couple of interesting possibilities to investigate.

First, how up to date are the statistics for the HAREKET_TABLE table (keep in mind that unless NO_INVALIDATE=>FALSE is specified when collecting the statistics, there could be a roughly 5 hour delay before the new statistics will result in a hard parse of a previously hard parsed SQL statement).

Let’s investigate Tony’s suggestion that PARALLEL_DEGREE_POLICY may have been adjusted at the session level, possibly by the tool that executed the SQL statement.

There are three views that might prove to be very useful in determining what is happening. As most readers of this blog likely know, the DESC command will list the columns and datatypes found in views and tables:

DESC V$SES_OPTIMIZER_ENV
 
DESC V$SQL_OPTIMIZER_ENV
 
DESC V$SYS_OPTIMIZER_ENV

A Google search may be used to find the description of any of these views:
V$SES_OPTIMIZER_ENV 112 site:docs.oracle.com

The first result is this page:

If we go back to my example, I still have a test session connected to the database. I need the SID of that session (as you very likely know, you can use V$SESSION to determine the SID of another session, but I will use V$MYSTAT since this is my SQL*Plus session):

SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;
 
       SID
----------
        96

Let’s take a look at the (majority of the) optimizer parameters for the session that affect the decisions made by the Oracle query optimizer (note that I have an arrow pointing at one of the rows):

SELECT
  NAME,
  ISDEFAULT,
  VALUE
FROM
  V$SES_OPTIMIZER_ENV
WHERE
  SID=96
ORDER BY
  NAME;
 
NAME                                     ISD VALUE
---------------------------------------- --- -------------
_parallel_cluster_cache_policy           NO  cached
_pga_max_size                            NO  409600 KB
active_instance_count                    YES 1
bitmap_merge_area_size                   YES 1048576
cell_offload_compaction                  YES ADAPTIVE
cell_offload_plan_display                YES AUTO
cell_offload_processing                  YES true
cpu_count                                YES 8
cursor_sharing                           YES exact
db_file_multiblock_read_count            YES 128
deferred_segment_creation                YES true
dst_upgrade_insert_conv                  YES true
hash_area_size                           YES 131072
is_recur_flags                           YES 0
optimizer_capture_sql_plan_baselines     YES false
optimizer_dynamic_sampling               YES 2
optimizer_features_enable                YES 11.2.0.3
optimizer_index_caching                  YES 0
optimizer_index_cost_adj                 YES 100
optimizer_mode                           YES all_rows
optimizer_secure_view_merging            YES true
optimizer_use_invisible_indexes          YES false
optimizer_use_pending_statistics         YES false
optimizer_use_sql_plan_baselines         YES true
parallel_autodop                         YES 0
parallel_ddl_mode                        YES enabled
parallel_ddldml                          YES 0
parallel_degree                          YES 0
parallel_degree_limit                    YES 65535
parallel_degree_policy                   NO  auto           {---- here
parallel_dml_mode                        YES disabled
parallel_execution_enabled               YES true
parallel_force_local                     YES false
parallel_max_degree                      YES 16
parallel_min_time_threshold              YES 10
parallel_query_default_dop               YES 0
parallel_query_mode                      YES enabled
parallel_threads_per_cpu                 YES 2
pga_aggregate_target                     YES 2048000 KB
query_rewrite_enabled                    YES true
query_rewrite_integrity                  YES enforced
result_cache_mode                        YES MANUAL
skip_unusable_indexes                    YES true
sort_area_retained_size                  YES 0
sort_area_size                           YES 65536
star_transformation_enabled              YES false
statistics_level                         YES typical
total_cpu_count                          YES 8
total_processor_group_count              YES 1
transaction_isolation_level              YES read_commited
workarea_size_policy                     YES auto

In the above, we are able to see that the PARALLEL_DEGREE_POLICY for the session was adjusted to AUTO from the default parameter value that was in place when the database was brought online.

We are also able to see the parameters that were in effect when a SQL statement was optimized. In the above test case examples, there were two child cursors for SQL_ID 5bc0v4my7dvr5, one with the default value for the PARALLEL_DEGREE_POLICY parameter and one with the value of AUTO for that parameter. Let’s examine the optimizer parameters for this SQL_ID (again with an arrow pointing to the two rows that are of interest):

SELECT
  CHILD_NUMBER,
  NAME,
  ISDEFAULT,
  VALUE
FROM
  V$SQL_OPTIMIZER_ENV
WHERE
  SQL_ID='5bc0v4my7dvr5'
ORDER BY
  CHILD_NUMBER,
  NAME;
  
CHILD_NUMBER NAME                                     ISD VALUE
------------ ---------------------------------------- --- -------------
           0 _pga_max_size                            NO  409600 KB
           0 active_instance_count                    YES 1
           0 bitmap_merge_area_size                   YES 1048576
           0 cell_offload_compaction                  YES ADAPTIVE
           0 cell_offload_plan_display                YES AUTO
           0 cell_offload_processing                  YES true
           0 cpu_count                                YES 8
           0 cursor_sharing                           YES exact
           0 db_file_multiblock_read_count            YES 128
           0 deferred_segment_creation                YES true
           0 dst_upgrade_insert_conv                  YES true
           0 hash_area_size                           YES 131072
           0 is_recur_flags                           YES 0
           0 optimizer_capture_sql_plan_baselines     YES false
           0 optimizer_dynamic_sampling               YES 2
           0 optimizer_features_enable                YES 11.2.0.3
           0 optimizer_index_caching                  YES 0
           0 optimizer_index_cost_adj                 YES 100
           0 optimizer_mode                           YES all_rows
           0 optimizer_secure_view_merging            YES true
           0 optimizer_use_invisible_indexes          YES false
           0 optimizer_use_pending_statistics         YES false
           0 optimizer_use_sql_plan_baselines         YES true
           0 parallel_autodop                         YES 0
           0 parallel_ddl_mode                        YES enabled
           0 parallel_ddldml                          YES 0
           0 parallel_degree                          YES 0
           0 parallel_degree_limit                    YES 65535
           0 parallel_degree_policy                   YES manual           {---- here
           0 parallel_dml_mode                        YES disabled
           0 parallel_execution_enabled               YES true
           0 parallel_force_local                     YES false
           0 parallel_max_degree                      YES 16
           0 parallel_min_time_threshold              YES 10
           0 parallel_query_default_dop               YES 0
           0 parallel_query_mode                      YES enabled
           0 parallel_threads_per_cpu                 YES 2
           0 pga_aggregate_target                     YES 2048000 KB
           0 query_rewrite_enabled                    YES true
           0 query_rewrite_integrity                  YES enforced
           0 result_cache_mode                        YES MANUAL
           0 skip_unusable_indexes                    YES true
           0 sort_area_retained_size                  YES 0
           0 sort_area_size                           YES 65536
           0 star_transformation_enabled              YES false
           0 statistics_level                         YES typical
           0 total_cpu_count                          YES 8
           0 total_processor_group_count              YES 1
           0 transaction_isolation_level              YES read_commited
           0 workarea_size_policy                     YES auto
           2 _parallel_cluster_cache_policy           NO  cached
           2 _pga_max_size                            NO  409600 KB
           2 active_instance_count                    YES 1
           2 bitmap_merge_area_size                   YES 1048576
           2 cell_offload_compaction                  YES ADAPTIVE
           2 cell_offload_plan_display                YES AUTO
           2 cell_offload_processing                  YES true
           2 cpu_count                                YES 8
           2 cursor_sharing                           YES exact
           2 db_file_multiblock_read_count            YES 128
           2 deferred_segment_creation                YES true
           2 dst_upgrade_insert_conv                  YES true
           2 hash_area_size                           YES 131072
           2 is_recur_flags                           YES 0
           2 optimizer_capture_sql_plan_baselines     YES false
           2 optimizer_dynamic_sampling               YES 2
           2 optimizer_features_enable                YES 11.2.0.3
           2 optimizer_index_caching                  YES 0
           2 optimizer_index_cost_adj                 YES 100
           2 optimizer_mode                           YES all_rows
           2 optimizer_secure_view_merging            YES true
           2 optimizer_use_invisible_indexes          YES false
           2 optimizer_use_pending_statistics         YES false
           2 optimizer_use_sql_plan_baselines         YES true
           2 parallel_autodop                         NO  1
           2 parallel_ddl_mode                        YES enabled
           2 parallel_ddldml                          YES 0
           2 parallel_degree                          NO  2
           2 parallel_degree_limit                    YES 65535
           2 parallel_degree_policy                   NO  auto           {---- here
           2 parallel_dml_mode                        YES disabled
           2 parallel_execution_enabled               YES true
           2 parallel_force_local                     YES false
           2 parallel_max_degree                      YES 16
           2 parallel_min_time_threshold              YES 10
           2 parallel_query_default_dop               NO  16
           2 parallel_query_mode                      YES enabled
           2 parallel_threads_per_cpu                 YES 2
           2 pga_aggregate_target                     YES 2048000 KB
           2 query_rewrite_enabled                    YES true
           2 query_rewrite_integrity                  YES enforced
           2 result_cache_mode                        YES MANUAL
           2 skip_unusable_indexes                    YES true
           2 sort_area_retained_size                  YES 0
           2 sort_area_size                           YES 65536
           2 star_transformation_enabled              YES false
           2 statistics_level                         YES typical
           2 total_cpu_count                          YES 8
           2 total_processor_group_count              YES 1
           2 transaction_isolation_level              YES read_commited
           2 workarea_size_policy                     YES auto

In the above, notice that the PARALLEL_DEGREE_POLICY parameter value is MANUAL for child cursor 0, and the ISDEFAULT value is YES. For child cursor 2, the values are AUTO and NO, respectively.

You will need to determine the SQL_ID and possibly the CHILD_NUMBER for the SQL statement that is executed in TOAD (or PLSQL Developer) to try the query of V$SQL_OPTIMIZER_ENV.

2 08 2012
Tony Sleight

Having gone through the same tests as yourself on 11.2.0.3 I did not suffer from the 835K cost bug. In all my tests parallel execution was performed only when PARALLEL_DEGREE_POLICY was set to AUTO. The OP indicated that the parallel execution happened when TOAD or SQL Developer was being used. I can only assume that these applications set PARALLEL_DEGREE_POLICY to AUTO on a SESSION level. Perhaps it is for performance enhancement? If that is the case, then RESOURCE_IO_CALIBRATE$ must have been set. It would be interesting to see what the contents of RESOURCE_IO_CALIBRATE$ are on the OP system.

Incidentally, Charles, the second issue regarding PSTOP = 1048575 indicating 1,048,575 partitions. My execution plans showed the same, however, USER_TAB_PARTITIONS showed only 100 partitions as expected. As this is my first foray into partitions, I find it difficult to explain. I shall endeavour to investigate over the next few days.

2 08 2012
Tony Sleight

Apologies, my laptop Oracle version is 11.2.0.1 not 11.2.0.3 as stated above.

2 08 2012
Mustafa Kalayci

Hi Tony,

as OP, I can show what is in RESOURCE_IO_CALIBRATE$:

START_TIME                       END_TIME                         MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_DISKS
28/03/2012 07.31.30.582060000 PM 28/03/2012 07.44.03.792624000 PM     1099      290       182       9       130
2 08 2012
Tony Sleight

Thanks Mustafa for a swift answer. As there is a record in RESOURCE_IO_CALIBRATE$ (created 28 March 2012), setting PARALLEL_DEGREE_POLICY on a session level to AUTO will enable parallel execution. Which is what Charles’ web post has shown.

I guess the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO must have been executed for the table entry to be created as it is not the manual value suggested by Charles’ article.

2 08 2012
Mustafa Kalayci

Tony,

PARALLEL_DEGREE_POLICY has been set to MANUAL for all sessions and db but query still can run parallel. I will get 10053 and 10046 traces and look for the results

2 08 2012
Tony Sleight

I see, that leaves me clean out of new suggestions! Certainly the 10053 would show how the execution plan was reached.

From your post, I gather you are wishing to ensure parallel queries are not executed on your system? If so, the drastic way to ensure that would be to delete the existing record in RESOURCE_IO_CALIBRATE$.

2 08 2012
Charles Hooper

Tony,

Good to see that you took an interest in the test case.

I was also curious about the Pstop value displayed in the execution plan. A quick check suggests that I have 101 partitions, one without a segment:

COLUMN TABLE_NAME FORMAT A10
COLUMN PARTITION_NAME FORMAT A10
 
SELECT
  TABLE_NAME,
  PARTITION_NAME,
  HIGH_VALUE,
  INTERVAL,
  SEGMENT_CREATED
FROM
  USER_TAB_PARTITIONS
WHERE
  TABLE_NAME='T1'
ORDER BY
  PARTITION_NAME;
 
TABLE_NAME PARTITION_ HIGH_VALUE                                                                       INT SEG
---------- ---------- -------------------------------------------------------------------------------- --- ---
T1         P0         TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO  NO
T1         SYS_P341   TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P342   TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P343   TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P344   TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P345   TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P346   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P347   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P348   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P349   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P350   TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P351   TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P352   TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P353   TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P354   TO_DATE(' 2013-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P355   TO_DATE(' 2013-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P356   TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P357   TO_DATE(' 2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P358   TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P359   TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P360   TO_DATE(' 2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P361   TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P362   TO_DATE(' 2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P363   TO_DATE(' 2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P364   TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P365   TO_DATE(' 2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P366   TO_DATE(' 2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P367   TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P368   TO_DATE(' 2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P369   TO_DATE(' 2014-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P370   TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P371   TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P372   TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P373   TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P374   TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P375   TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P376   TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P377   TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P378   TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P379   TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P380   TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P381   TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P382   TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P383   TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P384   TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P385   TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P386   TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P387   TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P388   TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P389   TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P390   TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P391   TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P392   TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P393   TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P394   TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P395   TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P396   TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P397   TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P398   TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P399   TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P400   TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P401   TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P402   TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P403   TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P404   TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P405   TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P406   TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P407   TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P408   TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P409   TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P410   TO_DATE(' 2018-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P411   TO_DATE(' 2018-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P412   TO_DATE(' 2018-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P413   TO_DATE(' 2018-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P414   TO_DATE(' 2018-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P415   TO_DATE(' 2018-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P416   TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P417   TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P418   TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P419   TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P420   TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P421   TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P422   TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P423   TO_DATE(' 2019-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P424   TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P425   TO_DATE(' 2019-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P426   TO_DATE(' 2019-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P427   TO_DATE(' 2019-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P428   TO_DATE(' 2019-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P429   TO_DATE(' 2019-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P430   TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P431   TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P432   TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P433   TO_DATE(' 2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P434   TO_DATE(' 2020-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P435   TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P436   TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P437   TO_DATE(' 2020-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P438   TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P439   TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
T1         SYS_P440   TO_DATE(' 2020-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES YES
  
101 rows selected.

Where is the Pstop number 1048575 coming from? Let’s try flushing the shared pool and executing a test script with a 10053 trace enabled:

ALTER SYSTEM FLUSH SHARED_POOL;
 
ALTER SESSION SET TRACEFILE_IDENTIFIER='TESTING10053'; 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
 
ALTER SESSION SET PARALLEL_DEGREE_POLICY=AUTO;
 
SELECT
  COUNT(*)
FROM
  T1;
 
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

I did not see in the 10053 trace where the optimizer determined the number of partitions in the table T1. The plan from the 10053 trace file:

============
Plan Table
============
------------------------------------------+-----------------------------------+-------------------------+----------------+
| Id  | Operation               | Name    | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop  |
------------------------------------------+-----------------------------------+-------------------------+----------------+
| 0   | SELECT STATEMENT        |         |       |       |   32K |           |      |      |           |       |        |
| 1   |  SORT AGGREGATE         |         |     1 |       |       |           |      |      |           |       |        |
| 2   |   PX COORDINATOR        |         |       |       |       |           |      |      |           |       |        |
| 3   |    PX SEND QC (RANDOM)  | :TQ10000|     1 |       |       |           |:Q1000| P->S |QC (RANDOM)|       |        |
| 4   |     SORT AGGREGATE      |         |     1 |       |       |           |:Q1000| PCWP |           |       |        |
| 5   |      PX BLOCK ITERATOR  |         |   95M |       |   32K |  00:01:07 |:Q1000| PCWC |           | 1     | 1048575|
| 6   |       TABLE ACCESS FULL | T1      |   95M |       |   32K |  00:01:07 |:Q1000| PCWP |           | 1     | 1048575|
------------------------------------------+-----------------------------------+-------------------------+----------------+
Predicate Information:
----------------------
6 - access(:Z>=:Z AND :Z<=:Z)
 
Content of other_xml column
===========================
nodeid/pflags: 6 17nodeid/pflags: 5 17  dop_op_reason  : scan of object TESTUSER.T1
  dop            : 2
  px_in_memory   : no
  db_version     : 11.2.0.3
  parse_schema   : TESTUSER
  plan_hash      : 974985148
  plan_hash_2    : 876471417
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 5)
      ALL_ROWS
      SHARED(2)
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
    END_OUTLINE_DATA
  */

Notice in the above that the Pstop value is 1048575, which is 2^20 – 1, that number might be important (see additional test below).

Here is the section where the optimizer decided to re-cost for a parallel degree of 2:

kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=13 mb_io_size=104858 (bytes) 
 tot_io_size=5980(MB) time=29899(ms)

AUTO DOP PLANS EVALUATION
***************************************
Compilation completed with Dop: 1.
  Cost_io:  58844.00  Cost_cpu: 20348604814.96
  Card:     1.00  Bytes:    0.00
  Cost:     59798.80  Est_time:  29899ms
kkopqCombineDop: Dop:2 Hint:no 
Query: compute:yes forced:no  computedDop:2 forceDop:0
Serial plan is expensive enough to be a candidate for parallelism (59799)
Signal reparse with DOP 2.
*****************************
Number of Compilations tried: 1
*****************************

Later in the trace file, we find the following block of text. The cost calculations appears to be consistent for a degree of parallel of 2:

Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 33081.7322  Degree: 2  Card: 99899001.0000  Bytes: 0
  Resc: 59547.1180  Resc_io: 58844.0000  Resc_cpu: 14984850150
  Resp: 33081.7322  Resp_io: 32691.1111  Resc_cpu: 8324916750

A little further into the 10053 trace file we find the following block of text. Notice the calculated CPU cost of 20348604815 for the parallel degree (dop) of 1 – that number appears to be inconsistent with the Resc_cpu value (14984850150) above – I wonder if this is expected?

kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=13 mb_io_size=104858 (bytes) 
 tot_io_size=3308(MB) time=16541(ms)

AUTO DOP PLANS EVALUATION
***************************************
Compilation completed with Dop: 2.
  Cost_io:  32691.11  Cost_cpu: 8324916750.00
  Card:     1.00  Bytes:    0.00
  Cost:     33081.73  Est_time:  16541ms
Comparing plan with dop=2 and plan with dop=1:
  dop=2 io=32691.11 cpu=8324916750 cost=33081.73 card=1 bytes=0 -> est=16541ms, scaled cost=33081.73
  dop=1 io=58844.00 cpu=20348604815 cost=59798.80 card=1 bytes=0 -> est=29899ms, scaled cost=59798.80
Plan with dop 2 is better. Scalability: 50
Current plan with dop=2 is better than best plan with dop=1
Costing completed. DOP chosen: 2.
AutoDop: kkopqSetMaxDopInCursorEnv:In the Cursor Session Env, max DOP is set to 16
*****************************
Number of Compilations tried: 2
*****************************
 
kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=258(MB) time=1292(ms)
kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=258(MB) time=1292(ms)
kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=258(MB) time=1292(ms)
kkeCostToTime: using io calibrate stats 
 maxmbps=0(MB/s) maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=258(MB) time=1292(ms)

Another test using a much smaller dataset of 10 rows:

DROP TABLE T2 PURGE;
 
CREATE TABLE T2 (
  C1 NUMBER,
  C2 VARCHAR2(10),
  C3 DATE,
  C4 VARCHAR2(20))
  PARTITION BY RANGE(C3)
  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')));
 
INSERT INTO
  T2
SELECT
  ROWNUM,
  RPAD(ROWNUM,10,'A'),
  TRUNC(SYSDATE)+MOD(ROWNUM-1,3000),
  RPAD('A',20,'A')
FROM
  (SELECT
    1 C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10);
 
COMMIT;
 
SELECT
  COUNT(*)
FROM
  T2;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));
 
SQL_ID  dp4dnf1mug84c, child number 0
-------------------------------------
SELECT   COUNT(*) FROM   T2

Plan hash value: 611317447
 
-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |     5 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |    10 |     5   (0)| 00:00:01 |     1 |1048575|
|   3 |    TABLE ACCESS FULL | T2   |    10 |     5   (0)| 00:00:01 |     1 |1048575|
-------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

The above plan, with just 10 rows, also shows a Pstop value of 1048575.

Let’s dig through the 11.2 documentation to find the page with the database limits listed:

http://docs.oracle.com/cd/E11882_01/server.112/e24448/limits003.htm

“Maximum number of partitions allowed per table or index 1024K – 1″

Pstop value is 1048575, which is 2^20 – 1, which is also 1024 * 1024 – 1 (the documentation’s “1024K – 1″)

Interesting?

Let’s try another test without interval partitioning:

DROP TABLE T3 PURGE;
 
CREATE TABLE T3 (
  C1 NUMBER,
  C2 VARCHAR2(10),
  C3 DATE,
  C4 VARCHAR2(20))
  PARTITION BY RANGE(C3)
  (
   PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
   PARTITION SYS_P341 VALUES LESS THAN (TO_DATE('1-8-2012', 'DD-MM-YYYY')),
   PARTITION SYS_P342 VALUES LESS THAN (TO_DATE('1-9-2012', 'DD-MM-YYYY')),
   PARTITION SYS_P343 VALUES LESS THAN (TO_DATE('1-10-2012', 'DD-MM-YYYY')),
   PARTITION SYS_P344 VALUES LESS THAN (TO_DATE('1-11-2012', 'DD-MM-YYYY')),
   PARTITION SYS_P345 VALUES LESS THAN (TO_DATE('1-12-2012', 'DD-MM-YYYY')),
   PARTITION SYS_P346 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MM-YYYY'))
);
 
INSERT INTO
  T3
SELECT
  ROWNUM,
  RPAD(ROWNUM,10,'A'),
  TRUNC(SYSDATE)+MOD(ROWNUM-1,3000),
  RPAD('A',20,'A')
FROM
  (SELECT
    1 C1
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10);
 
COMMIT;
 
SELECT
  COUNT(*)
FROM
  T3;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL +OUTLINE'));
 
SQL_ID  c4tq02u8za731, child number 0
-------------------------------------
SELECT   COUNT(*) FROM   T3
 
Plan hash value: 2024532151
 
-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |     5 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |      |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|      |    10 |     5   (0)| 00:00:01 |     1 |     7 |
|   3 |    TABLE ACCESS FULL | T3   |    10 |     5   (0)| 00:00:01 |     1 |     7 |
------------------------------------------------------------------------------------- 
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      NO_PARALLEL
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T3"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Notice in the above that the Pstop value is 7, not 1048575 as we saw with interval partitioning. Perhaps this is expected behavior?

(Edit: Aug 2, 2012, roughly 5 minutes after the comment was posted: the initial insert into table T3 failed – the insert statement had the HTML code for the less than (<) sign rather than the actual less than sign and I did not catch that error before posting the execution plan.)

2 08 2012
Tony Sleight

Thanks Charles, that explains quite a lot. I think that with interval partitioning, Oracle does not know how many partitions will be created as it is a dynamic value dependent upon the inserts to the table and so the maximum allowed value is chosen. However, in your second example with fixed partitions, 7 were generated and that was fixed in stone for the pstop max value.

10 08 2012
Dom Brooks

Coming to this thread a bit late – the 1048575 is the max number of interval partitions as you guys have stated so yes, it’s expected behaviour.

To back this up with the documentation:

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BABEGHBB

“An execution plan with a full table scan on an interval-partitioned table shows 1 for PSTART, and 1048575 for PSTOP, regardless of how many interval partitions were created.”

10 08 2012
Charles Hooper

Dominic,

Thank you for the documentation reference. The behavior is spelled out quite clearly in the documentation… I was not expecting to see that clarification on the topic in the documentation.

16 08 2012
Mustafa KALAYCI

Hi Charles,

I finally got 10053 trace. here it is: http://yadi.sk/d/bwlTsyMpEYpz you can download it.

I did not worked much with 10053 trace so it is a bit hard to understand to me. I would be appreciate if you look at it too. also added to OTN forum:

https://forums.oracle.com/forums/thread.jspa?threadID=2420587&tstart=15&start=30

and as I mentioned in forum, I realized something that may be related to that subject. when I query V$DB_OBJECT_CACHE, I saw that, HAREKET_TABLE and all of it’s indexes are seems as “MULTI-VERSIONED OBJECT”

select * from V$DB_OBJECT_CACHE where type = ‘MULTI-VERSIONED OBJECT’ and owner = ‘SG';

NAME NAMESPACE TYPE STATUS
I_NEW_HAREKET_1 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_HAREKET81_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_HAREKET_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_TARIK_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_HAREKET_MAHEKOL_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_HAREKET6_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
NEW_HAREKET_POLICE_NDX MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
I_NEW_HAREKET_2 MULTI-VERSION OBJECT FOR INDEX MULTI-VERSIONED OBJECT VALID
HAREKET_TABLE MULTI-VERSION OBJECT FOR TABLE MULTI-VERSIONED OBJECT VALID

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: