Re: Newbie: How to ensure parallel query execution

DSmith wrote in message <36b199ae.0_at_208.206.112.5>...
>I have an Oracle 8.0.4 instance on an NT box w/4 CPUs and 1 GB of RAM.
Do I
>simply add the parameters:
>parallel_max_servers, parallel_min_servers, sort_area_size,
hash_area_size,
>and optimizer_percent_parallel to the initxxxx.ora file to ensure
parallel
>query execution?
>
>The manual is pretty clear on how to determine the appropriate values
but
>how does one test the functionality? Any help would be greatly
appreciated.

I assume you have RTFM the above stuff and configured your instance accordingly. To test parallel execution you need to do some full table scans (FTS) or some index range scans in parallel.

The easiest is FTS. You will have "parallel_min_servers" PQ (parallel query slaves) idling. You can check this with a 'SELECT * FROM v$pqslave'.

Now do a FTS in parallel. Use the largest table in your database. SELECT /*+parallel(largetable, 10) full(largetable) */   count(*)
FROM largetable

Hope I got everything right above - I'm typing from memory here. The PARALLEL hint specifies that "largetable" must be processed in parallel using 10 PQs. There's also a 3rd optional parameter that you can use with Oracle Parallel Server - it specifies the number of nodes on which to run 10 PQs each, e.g. parallel(largetable,10,10). In your case, you have one node so this parameter is not applicable. The FULL hint specifies that a full table scan must be done (unnecessary in this case as Oracle will always do a FTS on a SELECT COUNT(*) unless instructed otherwise - but this is just so that you can get an idea of how things works).

OK, now while this SELECT COUNT(*) is running, do a select from PQ$SLAVE. It will now show that there are 10 PQ's busy. Each PQ has an entry in the V$PROCESS table. You can thus also use V$PROCESS to get some data on PQs. When a PQ goes from idle to busy, it creates an entry in the V$SESSION table. Now this even has more data you can use to see what a PQ is doing. You can for example join V$SESSION to V$SQLAREA to get the SQL statement that the PQ is executing. In the above case, it will be something like this:
SELECT /*+ GIV_GB(something or the other.. */   count(*)
FROM largetable
WHERE rowid BETWEEN :x and :y

As you can see, Oracle made some changes. It added its own hints to the SQL and it has added a WHERE clause. It now gives each of the PQ's a rowid range to scan and process. Now this is where ANALYZE comes in. Oracle needs to know the size of largetable to give each PQ a fair size chunk of rows to process. If Oracle does not have this data, it "guesses". And in such a case you may find that the rowid ranges for the 1st 2 PQs covers the entire table and the other 8 PQs got nothing to do.

You can also join V$SESSION to V$SESSTAT. This is where things get interesting. You can most times determine the number of rows processed by a PQ thus far by looking at stats for that session like table rows gotten.

OK, now you have gotten you hands dirty with PQ and there is a lot of frustration and joy in the future! :-) PQ is a great feature if used correctly. It can also be a major pain in the butt. The bottomline though is that it's fun to use - as all technical challenges should be. :-)

regards,
Billy Received on Fri Jan 29 1999 - 03:12:56 CST