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.
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;
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.
Dig Deeper on Oracle database design and architecture
Related Q&A from Phillip Bracken
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have a critical performance issue due to the large volume of data for a specific customer. Whenever customer XYZ's data is being fetched the query ... Continue Reading
I increased the RAM from 2GB to 3GB, but when I try to increase the sga_max_size I get the following error on startup: "ORA-27102: out of memory." Continue Reading