At our sight, we're not using MTS or RMAN, nor are we using Oracle Parallel Server . We have several independent servers, each with varying numbers of CPUs (some have 1; some have 3; some have 6) and varying numbers of disks for storage.

Our objective is to enable and use parallel execution. We're trying to figure out whether Oracle will use the large pool, and how big we should make the pool.

In your book (pg 83), you state that the large pool is used by:

MTS;
Parallel Execution to coordinate parallel query servers;
RMAN Disk I/O buffers.

I interpret this to mean the Oracle will use the large pool for parallel execution query servers, even though we are not using MTS, RMAN, or parallel server (i.e. multiple machines).

Is my interpretation correct?

What views/tables should we look at to monitor Large Pool usage?

I've read as a quirk that setting PARALLEL_AUTOMATIC_TUNING=TRUE has a very useful side-effect of automatically and dynamically sizing the large pool, not only for parallel operations, but for the other cases as well (rman, mts, etc), preventing it from getting crowded -- so we don't have to worry about sizing it manually. What do you think of it ?

Tom,

I am curious about where the PQ messages are buffered when the parameters LARGE_POOL, DBWn_IO_SLAVES and PARALLEL_AUTOMATIC_TUNING are not set.

Would the shared pool be used under these circumstances?

Tom,
The production system we have is a busy OLTP, a few hundred concurrent users and we will have more users coming. Someone has been recommending to set:
PARALLEL-AUTOMATIC_TUNING = TRUE for the Database. Do you think it right?

Thanks so much

The answers were very concise (and made sense). Very nice.

Tom,

I have a table that is dynamically created at run time. This table is loaded with data via a JAVA app & speed is essential. The developers are considering multithreading the app. IN your opinion would it help to set parallel_automatic_tuning=TRUE & create the table with the parallel clause?

If so, do we need to do anything else in the INSERT or SELECT statements against this table to utilize the parallelizm?
THanks

Hi Tom,
When we use a parallel hint,what can be the maximium no.of
processes that can be started in parallel by the system and
also what is the max.number of instances that an Oracle database can have?
Thanks in advance.

enviroment 1

show parameter parallel


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 160
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

Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'GCA_BOOKING_DETAIL',ESTIMATE_PERCENT =>20,degree => 3);



STATUS PROGRAM MODULE LOGON_TIME LAST_CALL_ET PDM PDDL_STA CURRENT_QUEUE_DURATION
-------- ----------------------- -------- -------------- ----- --- ------ ----------------------
ACTIVE oracle@ned (P000) SQL*Plus 13-MAY-2004 05:24:01 2464 NO ENABLED 0
ACTIVE oracle@ned (P002) SQL*Plus 13-MAY-2004 05:24:01 2464 NO ENABLED 0
ACTIVE oracle@ned (P001) SQL*Plus 13-MAY-2004 05:24:01 2464 NO ENABLED 0
ACTIVE oracle@ned (P005) SQL*Plus 13-MAY-2004 05:24:01 2466 NO ENABLED 0
ACTIVE oracle@ned (P004) SQL*Plus 13-MAY-2004 05:24:01 2466 NO ENABLED 0
ACTIVE sqlplus@ned (TNS V1-V3) SQL*Plus 13-MAY-2004 05:23:10 2470 YES ENABLED 0
ACTIVE oracle@ned (P003) SQL*Plus 13-MAY-2004 05:24:01 2466 NO ENABLED 0


enviroment 2


fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 2152
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


Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'GCA_USER',TABNAME => 'GCA_BOOKING_DETAIL',ESTIMATE_PERCENT =>20,degree => 3);


STATUS PROGRAM MODULE LOGON_TIME LAST_CALL_ET PDM PDDL_STA CURRENT_QUEUE_DURATION
-------- ------------------------------------------------ ------------------------------------------------ -------- ------------ ------------
ACTIVE oracle@tosk (P001) SQL*Plus 13-MAY-2004 06:15:34 35 NO ENABLED 0
ACTIVE oracle@tosk (P000) SQL*Plus 13-MAY-2004 06:15:34 35 NO ENABLED 0
ACTIVE oracle@tosk (P003) SQL*Plus 13-MAY-2004 06:15:34 35 NO ENABLED 0
ACTIVE oracle@tosk (P004) SQL*Plus 13-MAY-2004 06:15:34 35 NO ENABLED 0
ACTIVE sqlplus@tosk (TNS V1-V3) SQL*Plus 13-MAY-2004 06:02:01 35 YES ENABLED 0

(1) Is memory for "Response Queue" and "Request Queue" for Dispatchers in Shared Server Configuration allocated in Large Pool or somewhere in the SGA?

(2) Is there a view that would tell me this much memory was used for Response and Request Queue in the SGA?Thanks.

Dear Tom,

Chapter 7 of Oracle 9i Documentation states about the Shared Pool as
"The shared pool portion of the SGA contains three major areas: library cache, dictionary cache, buffers for parallel execution messages, and control structures."

Then it gives a note as
"If the initialization parameter PARALLEL_AUTOMATIC_TUNING is set to true, these buffers are allocated from the large pool."

This is a bit confusing. I recollect, once an Oracle Support Executive visited our organization for RAC Implementation and wanted this parameter to be set to true. In RAC, I can understand that the locking details are maintained in Shared Pool thus to reduce the load on the shared pool, this parameter needs to be set so that Large Pool can be used.

My Question is :
1. If this parameter is set to TRUE, irrespective of RAC and Non RAC, will Large Pool behave like Shared Pool. i.e.will parsing of SQL Statements is done in Large Pool.

2. Can this be set on Non -RAC Databases ? If yes, then what is the advantage ?

3. What are other benefits of this parameter ?

I apologize for the amount of question I have asked but will appreciate this knowledge sharing from you.

Thanks and Regards
Vivek

Hi Tom,
I currently have a discussion about Large Pool and Direct path inserts at the Oracle Database Forum (Nick=dimitri).
Perhaps you could clarify the questions which came up:

</code> http://forums.oracle.com/forums/thread.jspa?threadID=351967&tstart=0 <code>

Regards
Wolfgang

Thanks a lot Tom :-)

Hi Tom,
Trying to find an answer to this within Metalink and asktom, but not able to do so (yet!), so stealing your CPU cycles as a last resort.
We have just implemented 2 node 10g RAC. Now, if I execute a parallel operation while logged on to instance 1, the parallel slaves start up on instance 2 (nothing else is running on the boxes, I mean apart from essential OS services). I understand it tries to balance stuff and therefore the parallel slaves start up on whichever node it thinks best. My question is:

Is there a way to control this behaviour? Reason is - I
want to perform loading on a huge tables, which are
range-list partitioned by a certain code, and I "know"
that certain partitions are bigger than the rest and
hence want to divide the work up between the instances
manually. My "logic" being that since the instances
will be working on different partitions of the same
table - there should be less pressure on the
interconnect. However, if I cannot control the parallel
slaves then I cannot guarantee exclusive work on the
partitions within the instances.

I could be thinking in a completely wrong direction! any suggestions appreciated.

Thanks,
karmit

Hi,

Did some more digging around and it seems that there's a 
parameter called "PARALLEL_INSTANCE_GROUP" which works in 
conjunction with "INSTANCE_GROUPS". This seems to address
my issue(?!).

So, will be testing this out:

a) Setting up instance 1 as Group 1


b) Setting up instance 2 as Group 2


c) SQL> alter session parallel_instance_group='GROUP1';
   SQL> <<run load 1 - meant to run only on instance 1>>
   

d) SQL> alter session parallel_instance_group='GROUP2';
   SQL> <<run load 2 - meant to run only on instance 2>>

(will be setting all tables/indexes to default degree+instance)

Would this work - as intended?

Regards,
Karmit
 

Tested this today and it does seem to work!!!

Basically, could control the exection of pq slaves per
instance using the parallel_instance_group parameter
in conjunction with instance_groups.

Tom

Is there a reason that parallel_max_servers cannot be changed on the fly? We have a situation whereby we want parallel_max_servers=72 at night but equal to zero during the day. Reason being that during the day we are heavily OLTP so we don't want PX - but at night we have huge snapshots to refresh so we need PX.

Is there anyway we can make it impossible to run PX during the day?

Obvioulsy we can just not run anything with a PX hint in it during the day - but that is not foolproof. The DOP on all our tables and Indexes is zero so PX should not kick in unless hinted - but statspack indicates that PX slaves are being used during the day.

Any advise gratefully received.

Regards

Ian

ops$tkyte%ORA10GR2> alter system set parallel_max_servers = 10;

System altered.

ops$tkyte%ORA10GR2> alter system set parallel_max_servers = 20
  2  ;

System altered.

ops$tkyte%ORA10GR2> alter system set parallel_max_servers = 0;

System altered.

Sadly we are on 9.2.0.7

That's basically the route we are probably going to go - but it does leave us open to someone running a (homegrown) snapshot refresh during the day. I was just wondering if there was anyway of disabling it during te day. Guess not.

Cheers

Ian

Thanks Tom - that's exactly what I was looking for.

Thanks and Regards

Ian