Question about parallel processing. [message #422805] |
Sat, 19 September 2009 09:56 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vjeedigunta
Messages: 201 Registered: March 2005 Location: Hyderabad
|
Senior Member |
![vasu_jeedigunta](/forum/theme/orafaq/images/yahoo.png)
|
|
Hello all,
I have a question with respect to parallel processing in the query.
Oracle Version: 10.2.0.3
O/S: Solaris Sparc.
I have 2 databases both with the same version of oracle and both are on the same product. Both have the same data in it .. I have a query which is using the parallel processing in one database and is not in another database.
I have checked the parameters like parallel_min_servers, parallel_max_servers etc., along with the degree of parallelism in index scripts in both the db's and they are all same.
What could be the reason on it not using the parallel option in second database.
Review the explain plans below.
SQL> @utlxpls.sql
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 58375204
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 640 | 42664 (2)| 00:08:32 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10005 | 20 | 640 | 42664 (2)| 00:08:32 |
| 3 | HASH UNIQUE | | 20 | 640 | 42664 (2)| 00:08:32 |
| 4 | PX RECEIVE | | 1945K| 59M| 42439 (2)| 00:08:30 |
| 5 | PX SEND HASH | :TQ10004 | 1945K| 59M| 42439 (2)| 00:08:30 |
|* 6 | HASH JOIN BUFFERED | | 1945K| 59M| 42439 (2)| 00:08:30 |
| 7 | BUFFER SORT | | | | | |
| 8 | PX RECEIVE | | 1371 | 31533 | 26 (0)| 00:00:01 |
| 9 | PX SEND HASH | :TQ10000 | 1371 | 31533 | 26 (0)| 00:00:01 |
SQL> @utlxpls.sql
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 458983182
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31839 | 5783K| | 2490 (5)| 00:00:30 |
| 1 | HASH UNIQUE | | 31839 | 5783K| 12M| 2490 (5)| 00:00:30 |
| 2 | NESTED LOOPS | | 31839 | 5783K| | 1189 (10)| 00:00:15 |
| 3 | MERGE JOIN CARTESIAN | | 15 | 2655 | | 27 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TABLE_1234 | 1 | 45 | | 5 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 96 | 12672 | | 22 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TABLE_123456 | 96 | 12672 | | 22 (0)| 00:00:01 |
|
|
|
|
Re: Question about parallel processing. [message #422808 is a reply to message #422805] |
Sat, 19 September 2009 10:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68674 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If all things are equal there is no reason to have a different result.
If you have different result then not all things are equal.
Start with explaining (which seem not to be equal):
SELECT STATEMENT | | 20
SELECT STATEMENT | | 31839
Regards
Michel
[Updated on: Sat, 19 September 2009 10:43] Report message to a moderator
|
|
|