Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Parallel query monitoring

Parallel query monitoring

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 10 Feb 2006 01:53:22 -0800
Message-ID: <1139565202.243895.301250@o13g2000cwo.googlegroups.com>


Hi ,

How can i monitor oracle parallel query consumer and producers? Parallel query wait events are called idle wait events . I am trying to optimize a parallel query .:

select /*+parallel(t,8) */id,count(*) from customer t group by id.

The table has 14.000.000 rows , 2.5 GB.

The os is sun solaris . Disk array is EMC . I have 1 hba which is capable of transferring 100MB. data per second. I have 4 cpus .

Simply counting the records takes 40 secs:

select /*+parallel(t,8) */count(*) from customer t

During the execution of this sql , v$session_Wait shows direct_path_read with 1mb. multiblock read count ( in p3 column) and iostat of teh opertaing system shows 80 MB. read per second.

But the group by sql follows a different pattern. After reading the data (again direct_path_read) , i see parallel query idle events in v$session_Wait. At the same time v$sql_workare_active shows group by command , iostat shows no io.

Oracle traces of one of the parallel sessions shows the following:

lots of reads.....

WAIT #1: nam='PX Deq: Execution Msg' ela= 106968 p1=268566527 p2=1 p3=0
WAIT #1: nam='direct path read' ela= 16 p1=15 p2=78986 p3=128
WAIT #1: nam='direct path read' ela= 8 p1=15 p2=79114 p3=128
WAIT #1: nam='direct path read' ela= 13 p1=15 p2=79242 p3=128
FETCH
#1:c=7350000,e=48331543,p=20013,cr=19952,cu=0,mis=0,r=0,dep=0,og=4,tim=4024584751112 WAIT #1: nam='PX Deq: Execution Msg' ela= 577722 p1=268566527 p2=1 p3=0 WAIT #1: nam='PX Deq Credit: need buffer' ela= 78715 p1=268501009 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 48933 p1=268501009 p2=1 p3=0
WAIT #1: nam='PX Deq Credit: send blkd' ela= 55213 p1=268501009 p2=2 p3=
same messages goes on....

How can i measure the performance of group by ? Can these parallel query waits be tuned?

Simple count(*) and group by or join operations are different . Simple count(*) sql can be tuned by increasing the io performance , such as increasing the multiblockreadcount , adding more HBA (host bus adapter) etc. But for the group by or join operations ( which are real sqls) , what resource is the main bottleneck ? The above group by sql statement takes 10min, while count(*) takes 40 secs. What can i do in order to increase the performance of group by ( decrease the time for instance below 2 min.) Do i need more hba , more cpu , more ram?

Kind Regards,
tolga Received on Fri Feb 10 2006 - 03:53:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US