Re: Unexpected Parallel Query Execution

mccmx_at_hotmail.com (Matt) wrote in message news:<cfee5bcf.0407290056.4f3ac360_at_posting.google.com>...
> Oracle 8.1.7.4.1 EE on W2K (SP3)
>
> Is there anyway that parallel query slaves would be used for a query
> when every table in the dayabase has a degree of 1 and no parallel
> hints have been used in the SQL.
>
> SQL> select distinct degree from dba_tables;
>
> DEGREE
> -------------------------------
> 1
>
> SQL> sho parameter para
>
> NAME TYPE VALUE
> ------------------------------------ -------
> -------fast_start_parallel_rollback string LOW
> optimizer_percent_parallel integer 0
> parallel_adaptive_multi_user boolean FALSE
> parallel_automatic_tuning boolean FALSE
> parallel_broadcast_enabled boolean FALSE
> parallel_execution_message_size integer 2148
> parallel_instance_group string
> parallel_max_servers integer 5
> parallel_min_percent integer 0
> parallel_min_servers integer 0
> parallel_server boolean FALSE
> parallel_server_instances integer 1
> parallel_threads_per_cpu integer 2
> recovery_parallelism integer 0
>
> The following output from v$session shows that parallel query was
> being used which ended up consuming the total server CPU capcacity:
>
> 3472 SYSTEM 20 26544 ORACLE.EXE (P000)
> 3492 SYSTEM 12 440 ORACLE.EXE (P001)
> 3476 SYSTEM 24 17751 ORACLE.EXE (P002)
> 3420 SYSTEM 22 37652 ORACLE.EXE (P003)
> 2396 SYSTEM 26 15038 ORACLE.EXE (P004)
> 3452 SYSADM COMMON\B89263 25 13709 SQLPLUSW.EXE
Hi, Matt,

I suspect those parallel processes are due to parallel rollback. If you alter system set fast_start_parallel_rollback = false, can you still see them?

I did a test on my 9.0.1.3.1 database running on XP, with exactly the same parameter setting as yours (I don't have optimizer_percent_parallel). I kill a session doing a big update. I look at v$lock before my alter system kill command comes back (i.e. use another session, unless I kill the update by killing the sqlplus process). It shows SMON holding a shared PS and an exclusive TX as well as an exclusive TS lock, one parallel server process holding a shared PS lock (as Metalink Note:144332.1 describes). This parallel slave has an entry in v$px_process and v$process, and is a real Windows thread in the oracle.exe process, but it has no corresponding session in v$session. It hangs around for about 5 minutes and exits. But the entries in v$fast_start_servers and v$fast_start_transactions only show up until my alter system kill session command finishes. Since my update normally would take less than 1 minute to finish, the parallel rollback also takes less than 1 minute.

I have 1 CPU. But I always see only 1 process in v$px_process. v$sysstat shows all 0's for name like '%arallel%' (Christian's query; I changed '%parallelized' a little).

Yong Huang Received on Mon Aug 02 2004 - 14:40:48 CDT