Re: Monitoring parallelism

"Anurag Varma" <avdbi_at_hotmail.com> wrote:

>If were expecting your queries to automatically start running in parallel then I don't think that is
>true.
>Though things change from version to version. The parameters that you are talking about
>"parallel_max_servers"
>are there to "enable" parallelism. That statement however does not mean that things will
>automatically
>start running in parallel. Thus if you set parallel_max_servers = 0, you would probably be not able
>to use the PQO.

I did some experiments. On one DB, I changed the degree of parallelism of some large tables to two but didn't change anything in the init.ora file. Now, things run in parallel indeed, using two CPUs. The only problem: It takes longer to execute, because full table scans are taking place!!

On the 2nd DB, parallelism was switched on the way I mentioned, changing parameters only in the init.ora file, no changes of the degree of parallelism of tables and no hints. Here, nothing runs in parallel.

Now I am wondering why should one use the init.ora settings for parallelism, if they are not needed at all and have no effect if set alone. One can switch on parallelism at different levels: For the whole DB in the init.ora file, for some particular tables by changing their degree of parallelism, or by using hints. It makes no sense to try to force parallelism in more than one of these levels for the same operation at the same time. That is why I asumed that if the corresponding parameters would be set in the init.ora file, nothing more should be needed.  

>you would be able to use parallelism. I however would not recommend this. Be aware that if you set
>this
>clause at the table level, Oracle will start doing parallel full table scans much more than using
>indexes, which you might or *might not* want.

Yes, you are right. I suppose that there is some parameter to influence Oracle's decissions in favor of using index inspite of parallelism, i.e., it should do full table scans only when it would do them without parallelism anyway. By the way, indexes can be set to be accessed in parallel too... Not sure about the advantage of this.

>You should according to me use this feature by providing hints (for parallel) in your queries, if
>you think that
>the query will benefit from it. Which is the reason why I recommended reading up a perf tuning
>manual.

Again, you are right, but I am only the administrator of the DB and can't afford to spend my time looking at what developers do. Of course, everyone expects the *administrator* to accelerate things in the DB...

Bye
Rick Received on Mon Aug 19 2002 - 17:54:55 CDT