CODEWITHSUNDEEP
oracle-databasetoad
access_granted
access_granted

Reputation: 1927

how to disable TOAD parallel slave processes in Oracle?

I have a 12.5 TOAD going against multiple Oracle SIDs. On one of them the first connection opens a session with 4 instant parallel slave processes (which show up as additional sessions in v$session, freaking out the local DBA). And then each next SQL editor adds another 5.

On other SIDs this is not happening.

Is there a known way to disable this in TOAD? (so far nothing worked)

EDIT #1: Okay, this turned out not to be related to TOAD. Every session opened against that instance (just a blank conn too) automatically creates 4 additional slave processes right away, which are only seen within gv$session for your own connection (which is why it looked like other TOAD connections were not having it). I will keep this thread open for some time until I find out what the deal is with the worker processes.

FINAL EDIT: Finally found out that they force multiple threads for each statement on the instance level, so this has nothing to do with TOAD or clients.

Upvotes: 1

Views: 2322

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

This isn't "real" parallelism. Oracle uses small parallel queries for GV$ dynamic performance views on a Real Application Cluster (RAC). Oracle is currently a shared-everything architecture where all the data resides on every node. Except for dynamic performance views, since most activity only happens on a specific node and is tracked on that node.

This behavior will only occur on some SIDs because GV$ only uses parallel queries if the database is clustered. The queries may consume parallel processes, but only one per node and these queries usually do not use up much resources.

These queries should not normally be a problem. I can think of a few scenarios where they would look like a problem, but would not be the root problem:

  1. PARALLEL_MAX_SERVERS too low. Parallel sessions should not be a scarce resource. A DBA is right to worry about run-away parallelism. But when organizations worry about such a small number of parallel sessions it's usually because they've created an artificial scarcity by shrinking PARALLEL_MAX_SERVERS. The default value for parallel_max_servers is usually "PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5". If your server was purchased in this century there's no need to worry about a few extra sessions.

  2. Bad interconnect RAC needs a really good network connection between nodes. 100Mbps Ethernet is not going to cut it and the nodes will spend a lot of time communicating.

  3. Bad dictionary or fixed object statistics. Data dictionary queries may be slow if dictionary or fixed object stats have never been gathered. If these queries are running for a long time, try gathering stats with: exec dbms_stats.gather_dictionary_stats; and exec dbms_stats.gather_fixed_object_stats;.

Below is a demonstration of GV$ using parallel queries. This will only work on a RAC database.

> explain plan for select * from v$process;

Explained.

> select * from table(dbms_xplan.display(format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
Plan hash value: 4113158240

------------------------------------
| Id  | Operation        | Name    |
------------------------------------
|   0 | SELECT STATEMENT |         |
|   1 |  FIXED TABLE FULL| X$KSUPR |
------------------------------------

8 rows selected.

> explain plan for select * from gv$process;

Explained.

> select * from table(dbms_xplan.display(format => 'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
Plan hash value: 3900509504

-------------------------------------------
| Id  | Operation            | Name       |
-------------------------------------------
|   0 | SELECT STATEMENT     |            |
|   1 |  PX COORDINATOR      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000   |
|   3 |    VIEW              | GV$PROCESS |
|   4 |     FIXED TABLE FULL | X$KSUPR    |
-------------------------------------------

11 rows selected.

>

Upvotes: 1

Michael S.
Michael S.

Reputation: 1791

To minimize the number of sessions set the following options on the Oracle|Transaction page in Options.

  1. Disable "Execute queries in threads"
  2. Enable "Execute scripts in Toad session"
  3. Set "Session for Explain Plan" to "Main Toad Session"

Upvotes: 0

Related Questions