Solved

Limiting the number of parallel query processes per user

Posted on 2000-05-16
8
2,418 Views
Last Modified: 2011-09-20
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" ?
0
Comment
Question by:bobbruno
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2815857
I don't think there would be a way to limit the number of
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.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2815915
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.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2815944
For the complete procedure refer to Oracle Concepts Manual-> Chapter 9 Database Resource Management of Release 8.1.6

Regards,
Sudhi.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 3

Expert Comment

by:rkogelhe
ID: 2816858
Bob,

I have 8.1.5 and I set the parameter "parallel_threads_per_cpu" to 2 for my 2 CPU machine and it seems to always create 4 servers. Perhaps you could set this?

Also, I've never used them, but consider setting the "parallel_adaptive_multiuser" and "parallel_automatic_tuning" parameters to true.

Regards,

Ryan
0
 
LVL 4

Accepted Solution

by:
sudhi022299 earned 200 total points
ID: 2817018
I did a test try using the dbms_resource_manager and seems to be working fine. Check it out.

1. Clear any existing  pending area.
execute dbms_resource_manager.clear_pending_area;

2. Start a new pending area.
execute dbms_resource_manager.create_pending_area;

3. The following statement create a Test_Plan
execute dbms_resource_manager.create_plan(plan => 'Test_Plan', comment => 'Testing the resource manager');

4. This one would create a consumer group, which is used to group users.
execute dbms_resource_manager.create_consumer_group(consumer_group => 'Test_Group', comment => 'A consumer group');

5. Now comes the resource directive settings. The following statement set the parallelism degree to a max 2 server per operation of the Test_Group users.
EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'Test_Plan', Group_or_Subplan => 'Test_Group', -
 COMMENT => 'Test group', -
 CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 2);

6. This one is for the default group Other_Groups.
execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'test_plan', -
 GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'this one is required', -
 CPU_P1 => 0, CPU_P2 => 100);

7. Validate the actions so far.
Execute dbms_resource_manager.validate_pending_area();

8. Submit the pending area.
execute dbms_resource_manager.submit_pending_area();

9. Grant scott to switch to the consumer group 'Test_Group'
execute dbms_resource_manager.grant_switch_consumer_group('scott','Test_Group',TRUE);

10. Give scott the membership in the test_group.
execute dbms_resource_manager.set_initial_consumer_group('scott','Test_Group');


Regards,
Sudhi.
0
 

Author Comment

by:bobbruno
ID: 2914343
Comment accepted as answer
0
 

Author Comment

by:bobbruno
ID: 2914344
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).
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2917173
bobbruno, no problems of the grade as long as it helped you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question