Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Parallel query monitoring
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=128FETCH
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