| Question about parallel processing. [message #422805] |
Sat, 19 September 2009 09:56  |
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
|
Senior Member |
|
|
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  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
