Q

Parallel processing causing performance problems

Our largest tables are defined with "parallel degree nn" in Oracle 9.1. As a consequence, almost all of our queries that run against these tables invoke parallel queries and spawn many, many parallel slaves.

I have a situation at work wherein we have most crucial (and the largest) tables defined with "parallel degree nn" in Oracle 9.1 on a Tru-64 server. As a consequence, almost all of our queries that run against these tables invoke parallel queries and spawn many, many parallel slaves. We have very serious performance problems and when I run a Statspack report or look at the top 10 SQL statements from OEM, eight to nine out of 10 of the...

queries are almost always parallel slave queries and our most resource-intensive processes, per Statspack ,are parallel query slave waits.

I know whoever the DBA was who defined these tables thought that parallel processing would solve all ills but the problem is, with the tables defined this way, all queries both analytical and batch and non-analytical are using parallel queries.

Short of convincing the powers-that-be to clone each table into an analytical one with "parallel degree" defined (for analytics) and another without, what would you recommend doing to help with our performance problem? Thanks much.

You have two options.

1) Try using the NOPARALLEL hint in your SQL as follows to try to force the optimizer to not access the table using parallel slaves.

   SELECT /*+ NOPARALLEL(Table1) */ col1, col2, col3
   From Table1, Table2 Where ....

2) Disable the Parallel Option on the table.

        ALTER TABLE Table_Name NOPARALLEL;

Reader feedback:

Greg Pike wrote:

This 9i user has several additional options besides using the noparallel hint or removing the degree from the table.

From the Oracle 9i documentation:

The adaptive multiuser algorithm, which reduces the degree of parallelism as the load on the system increases. You can turn this option with the PARALLEL_ADAPTIVE_MULTI_USER parameter of the ALTER SYSTEM statement or in your initialization parameter file.

User resource limits and profiles, which allow you to set limits on the amount of various system resources available to each user as part of a user's security domain.

The Database Resource Manager, which lets you allocate resources to different groups of users.

Here is the relevant link for PARALLEL_ADAPTIVE_MULTI_USER in Oracle 9.2.

This was first published in May 2007
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close