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

1 comment:

  1. Thanks for your post. It is helpful. I am the mentioned developer. :)

    ReplyDelete