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, 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!
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.
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.
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:
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):
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):
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):
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.
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.
Apologies, my laptop Oracle version is 11.2.0.1 not 11.2.0.3 as stated above.
Hi Tony,
as OP, I can show what is in RESOURCE_IO_CALIBRATE$:
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.
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
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$.
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:
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:
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:
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:
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:
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?
Another test using a much smaller dataset of 10 rows:
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
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:
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.)
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.
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.”
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.
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