Hi ALL
Please have a look into this,
this may help us to think on PARALLEL option
WITHOUT PARALLEL Option
SQL> explain plan for select * from hr.emp ;
Explained.
PLAN
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7444K| 944M| 16077 (4)| 00:03:13 |
| 1 | TABLE ACCESS FULL| EMP | 7444K| 944M| 16077 (4)| 00:03:13 |
--------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7444K| 944M| 16077 (4)| 00:03:13 |
| 1 | TABLE ACCESS FULL| EMP | 7444K| 944M| 16077 (4)| 00:03:13 |
--------------------------------------------------------------------------
WITH PARALLEL Option
SQL> explain plan for select /*+parallel(emp,4)*/ * from hr.emp ;
Explained.
PLAN
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7444K| 944M| 4442 (3)| 00:00:54 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 7444K| 944M| 4442 (3)| 00:00:54 |
| 3 | PX BLOCK ITERATOR | | 7444K| 944M| 4442 (3)| 00:00:54 |
| 4 | TABLE ACCESS FULL| EMP | 7444K| 944M| 4442 (3)| 00:00:54 |
---------------------------------------------------------------------------------
In the above plan ( WITH PARALLEL Option )
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7444K| 944M| 4442 (3)| 00:00:54 |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 7444K| 944M| 4442 (3)| 00:00:54 |
| 3 | PX BLOCK ITERATOR | | 7444K| 944M| 4442 (3)| 00:00:54 |
| 4 | TABLE ACCESS FULL| EMP | 7444K| 944M| 4442 (3)| 00:00:54 |
---------------------------------------------------------------------------------
In the above plan ( WITH PARALLEL Option )
1. "Cost" has been nearly reduced to 1/4th
2. "CPU" has been reduced
3. "Time" has been nearly reduced to 1/3rd
On Thu, Jan 26, 2012 at 2:24 AM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
If you mean this[0] thread, it doesn't show anything conclusiveOn Wed, Jan 25, 2012 at 5:16 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
> On Wed, Jan 25, 2012 at 7:43 AM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
>> I know squat about how to implement this, but I've been considering
>> picking the low hanging fruit on that tree and patching up PG to try
>> the concept. Many of the items above would require a thread-safe
>> execution engine, which may be quite hard to get and have a
>> significant performance hit. Some don't, like parallel sort.
>
> This was just discussed on -hackers yesterday -- see thread
> 'multithreaded query planner'. In short, judging by the comments of
> some of the smartest people working on this project, it sounds like
> using threads to attack this is not going to happen, ever. Note you
> can probably still get parallel execution in other ways, using
> processes, shared memory, etc, so I'd consider researching in that
> direction.
against, say, parallel sort or pipelining.
But I agree, checking the code, it would be really tough to get any
more than parallel sorting by primitive types with threads.
Processes, however, show promise.
[0] http://archives.postgresql.org/pgsql-hackers/2012-01/msg00734.php
