Re: Monitoring parallelism

Rick Denoire wrote:

> I switched on parallel query ..

<snipped>
> I haven't noticed any significant change in performance.

PQ (Parallel Query) is not a magic wand that suddenly improves performance. PQ only happens under certain circumstances - in essence, PQ allows you to process a huge data set in chunks in parallel.

For example, you need to peform a SELECT count(*) on a VLT to see how many rows it has. You can use several PQ processes to do the row counting. e.g.

SELECT /*+ FULL(x) PARALLEL(x,10) */ count(*) FROM foobar x

> How can I somehow see that parallelism is active at all?

Two basic methods.

In Unix you will have a number of PQ slave processes active (equal to the PARALLEL_MIN_SERVERS setting in your INIT.ORA on startup). Do a : # ps -ef | grep <insert your Oracle SID here>

You should see processes called "ora_p000_orcl, ora_p001_orcl" and so on on (assuming your SID is orcl).

In Oracle you can look at the various V$PQ_* views.

> 8.1.7/Solaris 2.7 on a Sun E3500 with 4 CPUs). If any long running job
> starts, it sticks to one CPU so the overall CPU usage never goes over
> 25%.

Parallelism works on the principle of breaking a job up into various parts that can be done in parallel. This in itself is a very complex undertaking - the job's nature must enable this type of thing (i.e. process a large chunk of data). Oracle need to have a way to break up the work between parallel processes (it relies heavily on analyzer stats). Consideration must be given to sync issues between the various parallel processes. Etc.

Actually, very few jobs are of this nature in your standard RDBMS OLTP environment. PQ only comes into its own when you are dealing with large volumes of data - e.g. when an index range scan of a 4GB index for a 10 million row table is slower than throwing 10 PQ's directly at the massive table where each PQ (in theory) process 1 million rows diretcly from the table.

> Any sugestions?

Run the above SELECT count(*) on the largest table in your database (need a few million rows to illustrate the performance that results in using PQ).

Run it without the hint. Run it with the hint.

--
Billy