does<\/span> prevent the query from running in parallel.<\/p>\n[oracle@ISP4400 ~]$ sqlplus testuserSQL*Plus: Release 10.2.0.1.0 - Production on Mon May 8 20:56:37 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Enter password:Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> select table_name, degree from user_tables;TABLE_NAME DEGREE------------------------------ ------------------------------TEST_TAB1 8TEST_TAB2 1DUDE 1TEMP 1SQL> set autotrace onSQL> select count(*) from test_tab1;COUNT(*)----------65536000Execution Plan----------------------------------------------------------Plan hash value: 1614812727---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |TQ |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 84269 (1)| 00:29:51 || | || 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 | | 65M| 84269 (1)| 00:29:51 | Q1,00 | PCWC | || 6 | TABLE ACCESS FULL| TEST_TAB1 | 65M| 84269 (1)| 00:29:51 | Q1,00 | PCWP | |---------------------------------------------------------------------------------------------------------Statistics----------------------------------------------------------821 recursive calls3 db block gets1119199 consistent gets1118211 physical reads680 redo size413 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to\/from client7 sorts (memory)0 sorts (disk)1 rows processedSQL> alter session disable parallel query;Session altered.SQL> select count(*) from test_tab12 \/COUNT(*)----------65536000Execution Plan----------------------------------------------------------Plan hash value: 1745491793------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 607K (1)| 03:35:14 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| TEST_TAB1 | 65M| 607K (1)| 03:35:14 |------------------------------------------------------------------------Statistics----------------------------------------------------------1 recursive calls0 db block gets1118229 consistent gets1118207 physical reads0 redo size413 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to\/from client0 sorts (memory)0 sorts (disk)1 rows processed<\/pre>\nBut, when I hint the code, it over-rides the session setting.<\/p>\n
SQL> select \/*+ parallel(test_tab1) *\/ count(*)2 from test_tab1;COUNT(*)----------65536000Execution Plan----------------------------------------------------------Plan hash value: 1614812727---------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |TQ |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 606K (1)| 03:34:54 || | || 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 | | 65M| 606K (1)| 03:34:54 | Q1,00 | PCWC | || 6 | TABLE ACCESS FULL| TEST_TAB1 | 65M| 606K (1)| 03:34:54 | Q1,00 | PCWP | |---------------------------------------------------------------------------------------------------------Statistics----------------------------------------------------------61 recursive calls3 db block gets1119092 consistent gets1118207 physical reads632 redo size413 bytes sent via SQL*Net to client385 bytes received via SQL*Net from client2 SQL*Net roundtrips to\/from client1 sorts (memory)0 sorts (disk)1 rows processed<\/pre>\nI suppose it’s not that<\/span> big a deal, but I think it would be an easy mistake to think that using a login trigger could completely disable PX at the session level, but it appears that it won’t work if you have hinted code.<\/p>\n","protected":false},"excerpt":{"rendered":"One of the problems in working with Parallel Execution is that you tend to work on it in bursts, usually when a data warehouse is running into problems, and then don’t work on it for months. I’m in the middle of one of those bursts and there are a couple of related issues that I… Continue reading PX Issues Continued<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-788","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":881,"url":"http:\/\/orcldoug.com\/blog\/2006\/01\/15\/peter-robson-on-the-temporal-database-seminar\/","url_meta":{"origin":788,"position":0},"title":"Peter Robson on the Temporal Database seminar","date":"January 15, 2006","format":false,"excerpt":"It's a bit of a blog frenzy today!On the 3rd November last year, immediately after the UKOUG conference, there was a seminar in Edinburgh to discuss temporal databases. (Mogens blogged about it here and I'd mentioned some of Rob Squire's work here.) I had planned to go initially but was\u2026","rel":"","context":"With 7 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1011,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/04\/whats-a-data-warehouse-dba\/","url_meta":{"origin":788,"position":1},"title":"What’s a ‘Data Warehouse DBA’?","date":"July 4, 2006","format":false,"excerpt":"I've seen that question asked often in forums and mail groups. It's easy to understand why there's so much confusion because, for most DBAs, if you've seen one database, you've seen them all. Or rather, you haven't seen any of them. What I mean is that every database that lands\u2026","rel":"","context":"With 14 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1159,"url":"http:\/\/orcldoug.com\/blog\/2006\/12\/11\/a-more-complex-statspack-example-summary\/","url_meta":{"origin":788,"position":2},"title":"A More Complex Statspack Example – Summary","date":"December 11, 2006","format":false,"excerpt":"Looking back at the three blogs (and hopefully the comments, where others have made some very useful contributions), it's all quite unsatisfactory, isn't it? We haven't solved the problem. All we've proved is that the tests aren't equivalent, although I think there's value in that negative result because I was\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1050,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/10\/historical-perspective-part-2\/","url_meta":{"origin":788,"position":3},"title":"Historical Perspective (Part 2)","date":"August 10, 2006","format":false,"excerpt":"Let's look at items 2 and 3 from the list in my previous blog1) You know you have a performance problem and can re-create it by running a specific part of the application, be it a user interaction or batch job.2) You have an intermittent but recurring performance problem which\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1371,"url":"http:\/\/orcldoug.com\/blog\/2007\/12\/24\/the-reality-gap-4-its-never-the-san\/","url_meta":{"origin":788,"position":4},"title":"The Reality Gap (4) – It’s never the SAN","date":"December 24, 2007","format":false,"excerpt":"I've left one of my favourite topics for the penultimate episode of this mini-series. The more sites you work at and the more performance problems you work on, the more you begin to learn the one essential truth of modern system architecture. It's never the SAN. Here's a true story.\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1231,"url":"http:\/\/orcldoug.com\/blog\/2007\/03\/10\/how-can-i-tell-the-actual-dop-used-for-my-parallel-query\/","url_meta":{"origin":788,"position":5},"title":"How can I tell the actual DOP used for my Parallel Query?","date":"March 10, 2007","format":false,"excerpt":"At the end of my presentation this week, when I asked for questions, a lady from the middle of the hall piped up to ask something along the lines of.\"How can I tell the actual DOP used for my Parallel Query?\" (or words to that effect)It was in the context\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/788","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"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=788"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/788\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=788"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=788"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=788"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}