Home » RDBMS Server » Performance Tuning » Question about parallel processing. (10.2.0.3 Sun Sparc.)
Question about parallel processing. [message #422805] Sat, 19 September 2009 09:56 Go to next message
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 #422807 is a reply to message #422805] Sat, 19 September 2009 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
You posted a nice mystery & no useful clues.

using sqlplus along with CUT & PASTE do the following against both DBs

SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> -- HERE INVOKE PROBLEM SQL

PASTE whole session back here using <code tags> as explained in Posting Guidelines below.
http://www.orafaq.com/forum/t/88153/0/

Re: Question about parallel processing. [message #422808 is a reply to message #422805] Sat, 19 September 2009 10:42 Go to previous message
Michel Cadot
Messages: 63802
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

Previous Topic: alternate for Table Partitioning
Next Topic: Performance Tuning
Goto Forum:
  


Current Time: Sun Sep 25 16:17:54 CDT 2016

Total time taken to generate the page: 0.12305 seconds