July 8, 2012
As I was reading a book, I saw a comment essentially stating that with multiple CPUs/cores, parallel execution will be faster than serial execution of the same query. The book provided a test case – I decided to change around the test case a bit so that the results were a bit more fair, and then I performed a bit of testing using the sample database data that Oracle Corporation provides.
My testing was performed in Oracle Database 11.2.0.1 on 64 bit Windows with a quad core CPU that supports hyperthreading, and two SSD drives in a RAID 0 array (with all scripts executed on the server). So, is parallel query always faster than serial execution when the server has multiple CPUs/cores? But wait, that is not the interesting question… not the point of this parallel execution challenge, but still an interesting question. Here is the test script, which will be executed three times, with the results of the first execution thrown out so that we may eliminate the side effects of the hard parse:
ALTER SESSION SET CURRENT_SCHEMA=SH; SET AUTOTRACE TRACEONLY STATISTICS EXPLAIN SET TIMING ON SET LINESIZE 145 SET PAGESIZE 1000 ALTER SYSTEM FLUSH BUFFER_CACHE; SELECT /*+ PARALLEL(2) */ S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC; ALTER SYSTEM FLUSH BUFFER_CACHE; SELECT S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC;
Notice in the above that the test case performs the parallel execution first, with a degree of parallelism of 2, and then executes the same query without using parallel execution.
Let’s look at the output for the parallel execution:
918843 rows selected. Elapsed: 00:00:11.97 Execution Plan ---------------------------------------------------------- Plan hash value: 2055439529 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 918K| 19M| | 308 (14)| 00:00:04 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 918K| 19M| | 308 (14)| 00:00:04 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 918K| 19M| 31M| 308 (14)| 00:00:04 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 918K| 19M| | 277 (4)| 00:00:04 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 918K| 19M| | 277 (4)| 00:00:04 | | | Q1,00 | P->P | RANGE | | 6 | PX BLOCK ITERATOR | | 918K| 19M| | 277 (4)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| SALES | 918K| 19M| | 277 (4)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------------- Note ----- - Degree of Parallelism is 2 because of hint Statistics ---------------------------------------------------------- 12 recursive calls 0 db block gets 1802 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 918843 rows processed
918,843 rows selected (not displayed in SQL*Plus due to the AUTOTRACE setting), 1,647 blocks read from disk, 1,802 consistent gets, an estimated cost of 308, and the execution completed in 11.97 seconds.
Let’s take a look at the serial execution output:
918843 rows selected. Elapsed: 00:00:08.84 Execution Plan ---------------------------------------------------------- Plan hash value: 3803407550 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 918K| 19M| | 6637 (2)| 00:01:20 | | | | 1 | SORT ORDER BY | | 918K| 19M| 31M| 6637 (2)| 00:01:20 | | | | 2 | PARTITION RANGE ALL| | 918K| 19M| | 499 (4)| 00:00:06 | 1 | 28 | | 3 | TABLE ACCESS FULL | SALES | 918K| 19M| | 499 (4)| 00:00:06 | 1 | 28 | ------------------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1718 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 918843 rows processed
918,843 rows selected (not displayed in SQL*Plus due to the AUTOTRACE setting), 1,647 blocks read from disk, 1,718 consistent gets, an estimated cost of 6,637, and the execution completed in 8.84 seconds.
* The first part of the challenge is to describe how the cost is derived in the above execution plan for the parallel execution. Here is the easy part of the cost calculation:
cost of serial full table scan / degree of parallel / 0.90 499 / 2 / 0.90 = 277.22
The above calculation is easy to understand, and is explained in this article. The SORT ORDER BY operation in the parallel query execution plan added 31 to the calculated cost (308 – 277), while the same operation in the serial execution added 6,138 to the calculated cost (6637 – 499).
The first challenge is to describe why the calculated cost of the parallel execution plan is so small compared to the serial execution.
—
The timing output indicated that the serial execution required 3.13 fewer seconds than the parallel execution. Let’s look at the results of the third execution of this test case script:
918843 rows selected. Elapsed: 00:00:10.66 Execution Plan ---------------------------------------------------------- Plan hash value: 2055439529 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 918K| 19M| | 308 (14)| 00:00:04 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 918K| 19M| | 308 (14)| 00:00:04 | | | Q1,01 | P->S | QC (ORDER) | | 3 | SORT ORDER BY | | 918K| 19M| 31M| 308 (14)| 00:00:04 | | | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 918K| 19M| | 277 (4)| 00:00:04 | | | Q1,01 | PCWP | | | 5 | PX SEND RANGE | :TQ10000 | 918K| 19M| | 277 (4)| 00:00:04 | | | Q1,00 | P->P | RANGE | | 6 | PX BLOCK ITERATOR | | 918K| 19M| | 277 (4)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | | | 7 | TABLE ACCESS FULL| SALES | 918K| 19M| | 277 (4)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------------- Note ----- - Degree of Parallelism is 2 because of hint Statistics ---------------------------------------------------------- 12 recursive calls 0 db block gets 1802 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 918843 rows processed
The above shows the results of the parallel execution. 918,843 rows selected (not displayed in SQL*Plus due to the AUTOTRACE setting), 1,647 blocks read from disk, 1,802 consistent gets, an estimated cost of 308, and completed in 10.66 seconds. Notice that with the exception of the elapsed time, the statistics are identical to what we saw during the previous execution of the test script.
918843 rows selected. Elapsed: 00:00:08.48 Execution Plan ---------------------------------------------------------- Plan hash value: 3803407550 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 918K| 19M| | 6637 (2)| 00:01:20 | | | | 1 | SORT ORDER BY | | 918K| 19M| 31M| 6637 (2)| 00:01:20 | | | | 2 | PARTITION RANGE ALL| | 918K| 19M| | 499 (4)| 00:00:06 | 1 | 28 | | 3 | TABLE ACCESS FULL | SALES | 918K| 19M| | 499 (4)| 00:00:06 | 1 | 28 | ------------------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1718 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 918843 rows processed
The above shows the results of the serial execution. 918,843 rows selected (not displayed in SQL*Plus due to the AUTOTRACE setting), 1,647 blocks read from disk, 1,718 consistent gets, an estimated cost of 6,637, and completed in 8.48 seconds. Notice that with the exception of the elapsed time, the statistics are identical to what we saw during the previous execution of the test script.
The second challenge is to describe why the serial execution completed at least 25% faster than the parallel execution of the same query – this result is counter to what the calculated cost of the execution plans suggest, and counter to what the book claimed.
—
Maybe we need to look at the result from a different angle. The book did not use SET TIMING ON as my test case above did, so let’s change the test case again. This time, we will use the GATHER_PLAN_STATISTICS hint so that the runtime engine will collect the timing and various other statistics, and then use DBMS_XPLAN to display the actual execution plan. To eliminate the effects of hard parsing, the script will be executed three times with the first execution thrown out. The new script:
ALTER SESSION SET CURRENT_SCHEMA=SH; SET AUTOTRACE TRACEONLY STATISTICS SET TIMING OFF SET LINESIZE 145 SET PAGESIZE 1000 ALTER SYSTEM FLUSH BUFFER_CACHE; SELECT /*+ Q1 PARALLEL(2) GATHER_PLAN_STATISTICS */ S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC; ALTER SYSTEM FLUSH BUFFER_CACHE; SELECT /*+ Q2 GATHER_PLAN_STATISTICS */ S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC; SET AUTOTRACE OFF SELECT SQL_ID, CHILD_NUMBER, SUBSTR(SQL_TEXT,1,30) SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT /*+ Q%' ORDER BY 3, 1, 2;
The last of the above SQL statements simply retrieves the SQL_ID and CHILD_NUMBER for the two previous queries. For my results, I received the following output from the last of the above SQL statements:
SQL_ID CHILD_NUMBER SQL_TEXT ------------- ------------ ------------------------------ 5p47nbddfn0k7 0 SELECT /*+ Q1 PARALLEL(2) GATH 8k6bdqr09xc8u 0 SELECT /*+ Q2 GATHER_PLAN_STAT
To display the execution plans with the runtime statistics, we are able to use the following two statements:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5p47nbddfn0k7',0,'ALLSTATS LAST +COST')); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8k6bdqr09xc8u',0,'ALLSTATS LAST +COST'));
Here are the autotrace statistics for the parallel execution:
Statistics ---------------------------------------------------------- 12 recursive calls 0 db block gets 1802 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 918843 rows processed
The DBMS_XPLAN generated execution plan for the parallel execution follows:
SQL_ID 5p47nbddfn0k7, child number 0 ------------------------------------- SELECT /*+ Q1 PARALLEL(2) GATHER_PLAN_STATISTICS */ S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC Plan hash value: 2055439529 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 308 (100)| 918K|00:00:00.70 | 116 | 28 | | | | | 1 | PX COORDINATOR | | 1 | | | 918K|00:00:00.70 | 116 | 28 | | | | | 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 918K| 308 (14)| 0 |00:00:00.01 | 0 | 0 | | | | | 3 | SORT ORDER BY | | 0 | 918K| 308 (14)| 0 |00:00:00.01 | 0 | 0 | 83M| 3142K| 37M (0)| | 4 | PX RECEIVE | | 0 | 918K| 277 (4)| 0 |00:00:00.01 | 0 | 0 | | | | | 5 | PX SEND RANGE | :TQ10000 | 0 | 918K| 277 (4)| 0 |00:00:00.01 | 0 | 0 | | | | | 6 | PX BLOCK ITERATOR | | 0 | 918K| 277 (4)| 0 |00:00:00.01 | 0 | 0 | | | | |* 7 | TABLE ACCESS FULL| SALES | 0 | 918K| 277 (4)| 0 |00:00:00.01 | 0 | 0 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 7 - access(:Z>=:Z AND :Z<=:Z) Note ----- - Degree of Parallelism is 2 because of hint
Notice that the execution plan claims that the query completed in 0.70 seconds, that the calculated cost of the execution plan is (still) 308, that 28 blocks were read from disk, and 116 consistent gets were performed.
Here are the autotrace statistics for the serial execution:
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1718 consistent gets 1647 physical reads 0 redo size 22116448 bytes sent via SQL*Net to client 1102982 bytes received via SQL*Net from client 61258 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 918843 rows processed
The DBMS_XPLAN generated execution plan for the serial execution plan follows:
SQL_ID 8k6bdqr09xc8u, child number 0 ------------------------------------- SELECT /*+ Q2 GATHER_PLAN_STATISTICS */ S.PROD_ID, S.CUST_ID, S.TIME_ID FROM SH.SALES S ORDER BY S.AMOUNT_SOLD DESC Plan hash value: 3803407550 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 6637 (100)| 918K|00:00:00.93 | 1718 | 1647 | | | | | 1 | SORT ORDER BY | | 1 | 918K| 6637 (2)| 918K|00:00:00.93 | 1718 | 1647 | 42M| 2308K| 37M (0)| | 2 | PARTITION RANGE ALL| | 1 | 918K| 499 (4)| 918K|00:00:00.31 | 1718 | 1647 | | | | | 3 | TABLE ACCESS FULL | SALES | 28 | 918K| 499 (4)| 918K|00:00:00.22 | 1718 | 1647 | | | | -----------------------------------------------------------------------------------------------------------------------------------------
Notice that the execution plan claims that the query completed in 0.93 seconds, that the calculated cost of the execution plan is (still) 6,637, that 1,647 blocks were read from disk, and 1,718 consistent gets were performed.
The third challenge is to describe why DBMS_XPLAN claims that the parallel execution completed roughly 32.9% faster than the serial execution, when the timing output in SQL*Plus indicated that the reverse was true. Bonus: Why do the Buffers and Reads statistics in the top row of the execution plan for the serial execution plan exactly match the autotrace generated consistent gets and physical reads statistics, yet the same is not true for the execution plan for the parallel execution? Double Bonus: the execution plan claims that the query completed in 0.93 seconds, yet the timing information in SQL*Plus indicated that the query required an additional 7.55 seconds, why is there so much difference between the reported times (keep in mind that all testing was performed on the server, and the output was discarded without formatting and without displaying on screen)?
—
If cost is time, and time is money, what is the exchange rate for parallel execution? 😉
—
I have not fully worked the answers to the above challenge questions, and I will wait a couple of days before providing any feedback regarding proposed answers supplied by readers. I strongly suspect that some readers know without a doubt the answers to all of the above questions.
What book are you referring? I’m interested in PX.
Romeo,
I suggest taking a look at some of the articles that Doug Burns wrote on his blog. I believe that his articles on the topic are well regarded (and free as a bonus). Clicking the following link should help you locate those articles:
http://www.google.com/#hl=en&q=site:oracledoug.com+parallel
If you are looking for a *vry* light-weight article on parallel execution, you can take a look at one of my articles:
https://hoopercharles.wordpress.com/2010/02/17/parallel-reasoning/
I have read several books on the topic of Oracle Database performance tuning (some much better than others). The only general performance tuning book for which I did not have page after page of review notes pointing out errors is the “Troubleshooting Oracle Performance” book (yes, I made a considerable effort to find logic and technical errors in the book). You can read my review of that book here:
https://hoopercharles.wordpress.com/2011/11/07/book-review-troubleshooting-oracle-performance-part-2/
Some of my review notes from that book related to parallel execution follow:
You should be able to find the “Troubleshooting Oracle Performance” book in the online Google books site, and read a couple of pages to see if the book it is worth your money to buy the book.
The parallel execution hints (and some of the parallel execution behavior) changed a bit in Oracle Database 11.2, so you might want to seek out articles that are specific to 11.2.0.1 and above, if that is the release version of Oracle Database that you are using.
The book that I am in the process of finalizing the second half of the review for only has a couple of pages on the topic of parallel execution – that book used a table level parallel hint, while my test case above used a statement level parallel hint.
Thanks for all these links Charles, much appreciated. I also enjoy very much your chalenges because they are simply very helpfull to learn again and again. regarding above challenges, I think i got the answers, but i prefer to think about it twice before posting something that would simply be stupide 🙂
I thought for sure that I would have received several theories for the challenge questions by now.
I wonder if changing the script slightly to enable a couple types of traces might provide a clue:
Taking a look inside the large trace file containing “parallel” in its name, among other items, the file contains:
Taking a look inside the large trace file containing “serial” in its name, among other items, the file contains:
Interesting? Remember that the PARALLEL hint does not force parallel execution, it only changes plan costing. Silly thought – could simply adding an ORDER BY clause cause a query to switch from serial execution to parallel execution? 😉
—
Let’s process the trace files with TKPROF. First, the large trace file containing “serial” in its name, just the section related to the test SQL statement:
Now, the large trace file containing “parallel” in its name:
The above “query” value of 116 matches the “Buffers” value found in the DBMS_XPLAN output for the parallel execution, and the “disk” value matches the “Reads” value found in the DBMS_XPLAN output. If you take another look at the DBMS_XPLAN output, you will see that the “Starts” column value for plan line IDs 2 through 7 is 0 (indicating that line in the plan was never executed), and the “A-Rows” column for those same plan line IDs is 0, just as the above execution plan shows. The “A-Time” value in the DBMS_XPLAN output does not exactly match the “time=” value shown above – of course enabling a trace is sufficient to increase the execution time, but the above indicates that the plan required 0.54 seconds, while the DBMS_XPLAN output suggests 0.70 seconds. I wonder if there is a clue here?
You might have noticed that I stated “the large trace file containing ‘parallel’ in its name” above. Let’s process the other trace files containing “parallel” in their names.
p003:
p002:
p001:
p000:
—
I do not know if these MOS (Metalink) documents apply, but I thought that the documents appeared to be interesting:
Bug 13040301: “CBO CHOOSE PARALLEL EXECUTION DUE CHEAPER COST BUT SERIAL IS RUNNING FASTER” – costing problem, related to views and partitioned tables
Bug 12339245: “OPTIMIZER IMPROPERLY USING PARALLELISM FOR QUERIES” – probably does not apply, makes no mention of cost
A couple of possibly helpful documentation links:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm
—-
OK – anyone want to try answering the parallel execution challenge questions?
First problem I see with this query is fact that data distribution between parallel servers is totally incorrect. p001 receives almost whole dataset from both parallel servers performing table scans (900K rows), and almost whole sorting is performed by this server, while second sorting PX server (p000) sorts only 17K rows. This parallel (not-so-parallel?) sort operation, which is the most time-consuming operation of this query, won’t be performed faster than sort in serial execution. I suspect that PX coordination overhead is not so significant to make whole parallel query slower, so probably there are more issues here.
Something about the sorting looks fishy to me 🙂
Colin,
I was thinking something similar. I thought that people running other Oracle Database release versions might determine if they see the same behavior (a table level PARALLEL hint will be required if using 11.1.0.7 or below). The fish have not had any bites yet, so I tested Oracle Database 11.2.0.3 with patch 7 (released less than a month ago) with a fresh copy of the sample database schema.
In the sample database, I hardcoded the system statistics and left all parameters at the default (SGA_TARGET at 6000M, PGA_AGGREGATE_TARGET at 2000M):
Parallel run timing:
Serial run timing:
Notice that the same odd sort costing is still in effect for the parallel execution.
From the 10053 parallel run:
From the 10053 serial run:
—
Lukasz:
p000:
p001:
The same type of skewed division of rows for the sort operation is still present for the parallel slaves.
[…] Charles Hooper is dealing with a parallel execution challenge. […]
the trace files claim not to use the same sorting mechanism v1 vs v2. but as I have forgoten most
of what I read about this on Jonthan Lewis blog I cannot jude if this makes a difference.
pure speculation, not tested anywhere, two theories about optimizer costing
1) There is something I never really understood in these and similar plans (maybe I should start reading some serious books)
If parallel slaves are (pre)sorting the data, is there not another sort or merge needed once they different sources are combined?
Is maybe only the merge/additional sort costed in the parallel case?
or
2) could it be that the optimizer thinks, the lower number of rows to sort per slave fits into memory,
whereas serial execution needs to spill to temp table space? hence more IO higher cost
besides, does gather plan statistics gather all info or only the query coordinator?
Stefan,
I also wondered the same thing about the GATHER_PLAN_STATISTICS hint – maybe that is why the actual runtime statistics are not updated in the execution plan. I then recalled that the GATHER_PLAN_STATISTICS hint is roughly equivalent to setting STATISTICS_LEVEL=ALL in regard to execution actual runtime statistics, but the hint has a much lower performance impacting overhead than does changing STATISTICS_LEVEL from the default of TYPICAL to ALL.
Let’s try the test again:
The SQL_IDs:
Retrieving the actual statistics:
The execution plan for the parallel execution still does not include the runtime statistics for the parallel slaves:
Good that you caught the version 1 and version 2 sort difference. After reviewing the trace files generated by the above script, it appears that only the parallel query coordinator performs a version 1 sort, while it appears that parallel slave p001 performs two version 2 sorts. What is a version 1 sort, and what is a version 2 sort? Jonathan Lewis has a couple of articles that describe version 1 sorts well, and touch on version 2 sorts:
http://jonathanlewis.wordpress.com/2009/10/01/_smm_isort_cap/
http://jonathanlewis.wordpress.com/2009/12/28/short-sorts/
One of Jonathan’s articles mentions the _SMM_ISORT_CAP parameter, so I thought that I would check the value of that parameter (connected as the SYS user):
Just 102400? If the scale of the parameter is in bytes, that is just 100KB.
As shown in the above script, it is possible to disable the version 2 sort by setting the _NEWSORT_ENABLED hidden parameter to false.
The sort statistics from the trace file:
Query coordinator for the parallel execution:
p001 for the parallel execution:
With version 2 sort enabled, the execution plan and sort statistics:
With version 2 sort disabled, the execution plan and sort statistics:
Still to be determined is why the serial execution was costed with a sort area size of 1MB, while the parallel execution was costed with a sort area size of 40MB (the value of the _PGA_MAX_SIZE parameter as it appeared in the trace files) – that would be the likely explanation for the costing difference of the sort operation.
ALLSTATS LAST is not suitable for parallel executions because it only reports statistics from the QC.
Similarly, ALLSTATS ALL can be better but not if there have been multiple executions.
This is why Real Time SQL Monitoring is the better tool for reporting parallel execution statistics, but it requires Diagnostic+Tuning license.
Maria Colgan commented the above on a post of mine here:
http://orastory.wordpress.com/2011/03/10/real-time-sql-monitoring/
Dominic,
The comments in the article that you linked to above cover the third challenge of this blog article very well; your summarization of the comments from the article is well stated.
I did not have a good explanation for the behavior – I assumed that the query was executed once, not once per parallel server process. Thinking out loud, by what value will the EXECUTIONS and PX_SERVERS_EXECUTIONS columns of V$SQL (http://docs.oracle.com/cd/E14072_01/server.112/e10820/dynviews_3042.htm ) and V$SQLAREA (http://docs.oracle.com/cd/E11882_01/server.112/e17110/dynviews_3064.htm ) be increased when a query is executed in parallel: 1) The values in those columns will be increased by a value of 1 for each execution by the query coordinator; 2) The values in those columns will be increased by a value of 1 per parallel server process (and quey coordinator) involved in the query. Time for another test case script…
> By what value will the EXECUTIONS and PX_SERVER_EXECUTIONS be increased…
Questions that ring a bell with a recent OTN forum thread wondering how to tell what the real DOP was for a query:
https://forums.oracle.com/forums/thread.jspa?messageID=10447813�
PX_SERVER_EXECUTIONS is described in that OTN forum thread – I had previously read that thread, but missed the mention of the PX_SERVER_EXECUTIONS statistic.
A little test script that uses the sample database supplied by Oracle Corp.:
I executed the script 5 times, the results are posted below:
With Jonathan’s explanation from the OTN thread, would this be a DOP (degree of parallel) of 4 with 2 sets of slaves to run it?
Let’s take a look at the execution plan:
As is probably known by several readers, the degree of parallel in the note section may differ from the actual degree of parallel (this was mentioned in the thread).
Let’s try the test script again, this time with a GATHER_PLAN_STATISTICS hint in the query:
Will the ALLSTATS ALL format parameter of the DBMS_XPLAN call help? The ALLSTATS LAST format parameter call’s output:
Now, for comparison the ALLSTATS ALL format parameter call’s output:
The ALLSTATS ALL format parameter of the DBMS_XPLAN.DISPLAY_CURSOR function worked as described by Maria Colgan (and Doug Burns).