> 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