> 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

I suspect those parallel processes are due to parallel rollback. If you alter system set 
fast_start_parallel_rollback = false, you shouldn't 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%'

----------------------------------------------------------------------------------------------------

Bookmarks:

Fairlie Rego:
http://el-caro.blogspot.com/2008_11_01_archive.html