Showing posts with label Parallel query. Show all posts
Showing posts with label Parallel query. Show all posts

Thursday, September 2, 2010

BUFFER SORT and Parallel query

One of our developer complained that he executed a query that failed because because of "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP".
He also explained that there was no "sorting" or "group by" operation in that query.
Then I looked at the execution plan and found that there are some buffer sort in the execution plan which consumed space in temp.

After some googling I found that it the parallel query who in responsible for this.
http://www.orafaq.com/forum/t/125684/0/

If joining two tables, one has parallel degree more than 1 and other has parallel 1 or noparallel
then buffer sort will take place.€
To check this I performed below tests:

Buffer Sort:

alter table bs1 parallel 4;
alter table bs2 parallel 1;

explain plan for
select * from bs1 a,bs2 b
where a.id=b.id;

select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,50 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;

Operation Object
----------------------------------------------------------
SELECT STATEMENT ()
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10002
HASH JOIN ()
PX RECEIVE ()
PX SEND (HASH) :TQ10001
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS1
BUFFER (SORT)
PX RECEIVE ()
PX SEND (HASH) :TQ10000
TABLE ACCESS (STORAGE FULL) BS2



No Buffer Sort:


alter table bs1 parallel 4;
alter table bs2 parallel 2;

explain plan for
select * from bs1 a,bs2 b
where a.id=b.id;

select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,50 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;

Operation Object
--------------------------------------------------------
SELECT STATEMENT ()
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10002
HASH JOIN (BUFFERED)
PX RECEIVE ()
PX SEND (HASH) :TQ10000
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS1
PX RECEIVE ()
PX SEND (HASH) :TQ10001
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS2