{"id":1319,"date":"2007-09-23T12:00:00","date_gmt":"2007-09-23T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1319"},"modified":"2007-09-23T12:00:00","modified_gmt":"2007-09-23T12:00:00","slug":"parallel-query-and-11g","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g\/","title":{"rendered":"Parallel Query and 11g"},"content":{"rendered":"

I’ve been playing around with 11g on and off this week, re-running some of the tests in this document<\/a> (PDF file).<\/p>\n

The main edits required to the scripts were simple ones to correct the locations of trace files, for example (where $ORACLE_BASE is \/ora on the 10g system)<\/p>\n

rm \/ora\/admin\/TEST1020\/udump\/*.trc<\/strong><\/pre>\n
and $ORACLE_BASE is \/u01\/app\/oracle on the 11g system<\/p>\n
rm \/u01\/app\/oracle\/diag\/rdbms\/test11\/TEST11\/trace\/*.trc<\/strong><\/pre>\n
\nThey were always hard-coded because I never planned to re-run the tests much but, even if I had used $ORACLE_BASE\/admin\/$ORACLE_SID\/udump (and bdump), which would have been the correct approach, the scripts still wouldn’t have worked because of Oracle’s decision to play around with OFA directory structures yet again! I can’t help feeling this is going to cause more problems than expected because there are a lot of scripts on systems out there that depend on the existing locations.<\/p>\n
Anyway, on to the tests themselves. I was keen to try out the PARALLEL_IO_CAP_ENABLED parameter<\/a>. The first step was to use the new I\/O calibration tool. I simply lifted the example straight out of the documentation and amended the number of available disks.<\/p>\n
SQL> @io\nSQL> SET SERVEROUTPUT ON\nSQL> DECLARE\n  2    lat  INTEGER;\n  3    iops INTEGER;\n  4    mbps INTEGER;\n  5  BEGIN\n  6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);\n  7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (5, 10, iops, mbps, lat);\n  8\n  9    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);\n 10    DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);\n 11    dbms_output.put_line('max_mbps = ' || mbps);\n 12  end;\n 13  \/\n\nmax_iops = 112\nlatency  = 8\nmax_mbps = 32\n\nPL\/SQL procedure successfully completed.<\/pre>\n
Well, it’s certainly recognised how pathetic my i\/o infrastructure is, as I’ve mentioned in the past. Oh, and I should mention that this is a 5-disk stripe set with a stripe width of 256KB. That’s too small, but was the one the Linux installation decided on and matched the one I used in the original paper, so I’ve left it as it is for now.<\/p>\n
Now to see how this impacts the decision to use PX for a query. The problem is that the existing scripts use hints to request a specific degree of parallelism (DOP), e.g.<\/p>\n
SELECT \/*+ parallel(tt1,$DOP) parallel(tt2, $DOP) *\/ MOD(tt1.pk_id + tt2.pk_id, 113), COUNT(*)\nFROM test_tab1 tt1, test_tab2 tt2\nWHERE tt1.pk_id = tt2.pk_id\nGROUP BY MOD(tt1.pk_id + tt2.pk_id ,113)\nORDER BY MOD(tt1.pk_id + tt2.pk_id ,113);<\/o:p><\/o:p><\/o:p><\/o:p><\/o:p><\/span><\/pre>\n
So I threw together a slightly modified script that requests PX but doesn’t specify the DOP in the hint, leaving it to Oracle to decide :-<\/p>\n
SELECT \/*+ parallel(tt1) parallel(tt2) *\/ MOD(tt1.pk_id + tt2.pk_id, 113), COUNT(*)<\/o:p><\/span><\/pre>\n

Without parallel_io_cap_enabled<\/strong><\/span><\/p>\n
First I’ll check the behaviour without enabling i\/o capping, which should be similar to 10g.<\/p>\n
SQL> show parameter parallel_io\n\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nparallel_io_cap_enabled              boolean     FALSE<\/pre>\n
Next I’ll run the test and then check the contents of v$pq_tqstat to see if parallelism was used (note – this is only enough of the output to illustrate my point).<\/p>\n
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes\n  2  FROM v$pq_tqstat\n  3  ORDER BY dfo_number DESC, tq_id, server_type DESC , process;\n\nDFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES\n---------- ---------- ---------- ---------- ---------- ----------\n         1          0 Producer   P000          8220476   58056397\n                      Producer   P001          8104871   57233905\n                      Producer   P002          8075353   57029517\n                      Producer   P003          7816421   55186002\n                      Producer   P004          8088829   57061516\n                      Producer   P005          8008482   56470221\n                      Producer   P006          8491092   59649083\n                      Producer   P007          8730476   61174367\n                      Consumer   P008          8189988   57718569\n                      Consumer   P009          8196864   57766896\n                      Consumer   P010          8192339   57734621\n                      Consumer   P011          8189512   57715446\n                      Consumer   P012          8192294   57734372\n                      Consumer   P013          8192216   57734131\n                      Consumer   P014          8193654   57745093\n                      Consumer   P015          8189133   57711880<\/pre>\n
So the DOP is cpu_count (4) * parallel_threads_per_cpu (2) = 8 px slaves per slave set, with two slave sets used. That’s what I expected.<\/p>\n
With parallel_io_cap_enabled<\/strong><\/p>\n
Now I’ll look at the effect of the new parameter.<\/p>\n
SQL> alter system set PARALLEL_IO_CAP_ENABLED=true;\n\nSystem altered.<\/pre>\n
This time, the contents of v$pq_tqstat were<\/p>\n
SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes\n  2  FROM v$pq_tqstat\n  3  ORDER BY dfo_number DESC, tq_id, server_type DESC , process;\n\nno rows selected<\/pre>\n
So, because of the limited i\/o resources, Oracle’s decided not to use PX for the same query, with the same hints. What I found interesting was that the execution plan still looks like PX is used. (Just a snippet of output again.)<\/p>\n
| Id  | Operation                  | Name      | Rows  | Bytes |TempSpc| Cost (%\nCPU)| Time     |    TQ  |IN-OUT| PQ Distrib |\n--------------------------------------------------------------------------------\n---------------------------------------------\n|   0 | SELECT STATEMENT           |           |    65M|   751M|       |   585K\n(13)| 03:22:10 |        |      |            |\n|   1 |  PX COORDINATOR            |           |       |       |       |\n    |          |        |      |            |\n|   2 |   PX SEND QC (ORDER)       | :TQ10002  |    65M|   751M|       |   585K\n(13)| 03:22:10 |  Q1,02 | P->S | QC (ORDER) |\n|   3 |    SORT GROUP BY           |           |    65M|   751M|  2513M|   585K\n(13)| 03:22:10 |  Q1,02 | PCWP |            |\n|   4 |     PX RECEIVE             |           |    65M|   751M|       |   285K\n(11)| 01:38:33 |  Q1,02 | PCWP |            |\n|   5 |      PX SEND RANGE         | :TQ10001  |    65M|   751M|       |   285K\n(11)| 01:38:33 |  Q1,01 | P->P | RANGE      |\n|*  6 |       HASH JOIN            |           |    65M|   751M|  1126M|   285K\n(11)| 01:38:33 |  Q1,01 | PCWP |            |\n|   7 |        PX RECEIVE          |           |    65M|   375M|       |   112K\n (9)| 00:38:52 |  Q1,01 | PCWP |            |\n|   8 |         PX SEND BROADCAST  | :TQ10000  |    65M|   375M|       |   112K\n (9)| 00:38:52 |  Q1,00 | P->P | BROADCAST  |\n|   9 |          PX BLOCK ITERATOR |           |    65M|   375M|       |   112K\n (9)| 00:38:52 |  Q1,00 | PCWC |            |\n|  10 |           TABLE ACCESS FULL| TEST_TAB2 |    65M|   375M|       |   112K\n (9)| 00:38:52 |  Q1,00 | PCWP |            |\n|  11 |        PX BLOCK ITERATOR   |           |    65M|   375M|       |   112K\n (9)| 00:39:00 |  Q1,01 | PCWC |            |\n|  12 |         TABLE ACCESS FULL  | TEST_TAB1 |    65M|   375M|       |   112K\n (9)| 00:39:00 |  Q1,01 | PCWP |            |<\/pre>\n
There’ll be more to say later, no doubt, but that’s all for now.<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve been playing around with 11g on and off this week, re-running some of the tests in this document (PDF file). The main edits required to the scripts were simple ones to correct the locations of trace files, for example (where $ORACLE_BASE is \/ora on the 10g system) rm \/ora\/admin\/TEST1020\/udump\/*.trc and $ORACLE_BASE is \/u01\/app\/oracle on… Continue reading Parallel Query and 11g<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1319","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":1319,"position":0},"title":"Parallel Query and 11g – Part 2","date":"September 23, 2007","format":false,"excerpt":"Now, that's weird. A little surprising might be more accurate and maybe I'm missing something.During the various tests with and without parallel hints and different parallel_io_cap_enabled settings, I expected the runs that didn't use parallelism to show up \"db file scattered read\" events in the trace files. For example, here's\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1697,"url":"http:\/\/orcldoug.com\/blog\/2013\/03\/11\/not-all-deadlocks-are-created-the-same\/","url_meta":{"origin":1319,"position":1},"title":"Not all Deadlocks are created the same","date":"March 11, 2013","format":false,"excerpt":"I've blogged about deadlocks in Oracle at least once before. I said then that although the following message in deadlock trace files is usually true, it isn't always. The following deadlock is not an Oracle error. Deadlocks of\u00a0 this type can be expected if certain SQL statements are\u00a0\u00a0\u00a0\u00a0\u00a0 issued. The\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1294,"url":"http:\/\/orcldoug.com\/blog\/2007\/07\/12\/oracle-11g-total-recall\/","url_meta":{"origin":1319,"position":2},"title":"Oracle 11g – Total Recall","date":"July 12, 2007","format":false,"excerpt":"I suppose most readers will be aware that Oracle held a big launch for 11g yesterday, including the release of various technical docs at OTN. I've only managed a quick scan because I'm up to my eyeballs in some internal ASH\/AWR training I'm giving later today, but what particularly interested\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1030,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/21\/monitoring-index-usage\/","url_meta":{"origin":1319,"position":3},"title":"Monitoring Index Usage","date":"July 21, 2006","format":false,"excerpt":"A common requirement cropped up this week. A new Data Warehouse has just gone live and is still in the 'do we have the right indexes here' phase. In this case, the suspicion is that there are too many indexes in a specific schema and that a number of them\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1374,"url":"http:\/\/orcldoug.com\/blog\/2008\/01\/08\/book-review-oracle-11g-new-features\/","url_meta":{"origin":1319,"position":4},"title":"Book Review – Oracle 11g New Features","date":"January 8, 2008","format":false,"excerpt":"I remember I said I wouldn't do this any more, but last October, someone from Oracle Press contacted me by email because they'd noticed the following words on my website. \"However, those aspects are well dealt with in Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning by\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1310,"url":"http:\/\/orcldoug.com\/blog\/2007\/08\/14\/thats-more-like-it\/","url_meta":{"origin":1319,"position":5},"title":"That’s More Like It!","date":"August 14, 2007","format":false,"excerpt":"(and I'm writing this from a positive perspective)Now that the community is over the initial 'Look over, there! It's 11g!!!!', there are some genuinely interesting blogs kicking around. (Maybe we need to give these things a little time?) I'd better only talk about a couple, though, or Dave Edwards will\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1319","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1319"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1319\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1319"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1319"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1319"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}