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?
Charles Hooper's Oracle Notes 
Recent Comments