Re: Parallel Query Option on 7.3.4

Roman Gelfand wrote in message <71qf03$a93$1_at_news.monmouth.com>...
>select /*+ PARALLEL(TBL1, 5) */ fld1, fld2, fld3, fld4 from tbl1 order by
>fld5, fld6;
>
>The table tbl1 contains 5000 rows. Can anyone tell me why I am not seeing
>performance improvement using parallel query option.

Doing a parallel scan on 5,000 rows will probably not show any marked improvements. The table is a bit small. The little additional performance that you're getting by running the SQL in parallel for a simple FTS is probably negated by the initial parallelising Oracle needs to do to run that SQL in parallel.

You can however make the SQL much more complex - add a group by clause and use functions (SUMs, SUM DECODEs, AVGs etc) to the SELECT. The more functions, the better the results of the experiment. This will require additional horses for each row processed. If you now run the SQL in parallel, it should be many times faster than doing it in serial.

Oh yes, in order for Oracle to know that each of the 5 PQs must only process 1,000 rows (as your trying to do via the hint), you need to make sure that the table has been analysed. If not, Oracle may quite likely tell the each PQ to process 20,0000 rows - so the 1st PQ winds up doing all the work.

Hopes this help.

regards,
Billy Received on Thu Nov 05 1998 - 00:52:18 CST