Limiting the number of parallel query processes per user
I have a DW environment, where I'm setting about 30 parallel query servers (PARALLEL_MAX_SERVER=30). My problem is that when I use the DEFAULT degree of parallelism, Oracle 8i (8.1.5, i think) tends to allocate about 20 query servers to a single SQL, i.e., the lucky guy who gets to send the first query. The following users get stuck with very few query processes, and their queries take forever to run. Is there a way of telling Oracle to allocate a maximum of X query processes per user, something like saying "each user can get at most 8 query processes on his queries" ?
I'm sorry of my previous comment. If you have 8.1.6 EE then you can use the Database Resource Manager.
Here is some info on it from the online docs.
The Database Resource Manager allows the database administrator to have more control over resource management than would normally be possible through
operating system resource management alone. Improved resource management enables better application performance and availability. By using the Database
Resource Manager, the database administrator can:
Guarantee groups of users a minimum amount of processing resources, regardless of the load or number of users in other groups on the system.
Distribute available processing resources by allocating percentages of CPU time to different users and applications. For example, in a data warehouse, a
higher priority may be given to ROLAP applications than to batch jobs.
Limit the degree of parallelism that a set of users can use.
Configure an instance to use a particular plan for allocating resources. A database administrator can dynamically change the plan, for example, from a daytime
setup to a nighttime setup, without having to shut down and restart the instance.
To use the Database Resource Manager, a database administrator defines the following items:
resource consumer groups
Groups of user sessions that have similar processing and resource use requirements.
resource plans
Means of allocating resources among the consumer groups.
resource allocation methods
Policies for allocating a particular resource. Resource allocation methods are used by both plans and consumer groups.
resource plan directives
Means of:
assigning consumer groups or subplans to resource plans
allocating resources among consumer groups in the plan by specifying parameters for each resource allocation method.
Here is some info on it from the online docs.
The Database Resource Manager allows the database administrator to have more control over resource management than would normally be possible through
operating system resource management alone. Improved resource management enables better application performance and availability. By using the Database
Resource Manager, the database administrator can:
Guarantee groups of users a minimum amount of processing resources, regardless of the load or number of users in other groups on the system.
Distribute available processing resources by allocating percentages of CPU time to different users and applications. For example, in a data warehouse, a
higher priority may be given to ROLAP applications than to batch jobs.
Limit the degree of parallelism that a set of users can use.
Configure an instance to use a particular plan for allocating resources. A database administrator can dynamically change the plan, for example, from a daytime
setup to a nighttime setup, without having to shut down and restart the instance.
To use the Database Resource Manager, a database administrator defines the following items:
resource consumer groups
Groups of user sessions that have similar processing and resource use requirements.
resource plans
Means of allocating resources among the consumer groups.
resource allocation methods
Policies for allocating a particular resource. Resource allocation methods are used by both plans and consumer groups.
resource plan directives
Means of:
assigning consumer groups or subplans to resource plans
allocating resources among consumer groups in the plan by specifying parameters for each resource allocation method.
For the complete procedure refer to Oracle Concepts Manual-> Chapter 9 Database Resource Management of Release 8.1.6
Regards,
Sudhi.
Regards,
Sudhi.
Bob,
I have 8.1.5 and I set the parameter "parallel_threads_per_cpu"
Also, I've never used them, but consider setting the "parallel_adaptive_multius
Regards,
Ryan
I have 8.1.5 and I set the parameter "parallel_threads_per_cpu"
Also, I've never used them, but consider setting the "parallel_adaptive_multius
Regards,
Ryan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Comment accepted as answer
ASKER
The answer was clear, quite complete and even tested. I'm not grading it as excellent only because it will take me some time to actually implement it (our site is not on 8.1.6 yet).
bobbruno, no problems of the grade as long as it helped you.
parallel server process per user basis. You would have to look into the way
the default degree of parallelism is determined for a SQL statement by the
following factors.
1. The number of CPUs in the system.
2. The number of Oracle Parallel Server instances.
3. The number of disks that the table or index is stored on.
4. For parallelism by partition, the number of partitions that will be
accessed, based upon partition pruning.
5. Also find out what is the value of PARALLEL_MIN_SERVERS which specifies the
number of parallel server processes that Oracle creates at instance startup.
6. you may consider setting the value of PARALLEL_MIN_PERCENT and PARALLEL_SERVER_IDLE_TIME.
Regards,
Sudhi.