• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

query to find PARALELL or NOPARALLEL on a table

I want to run a query to see what tables have parallel and noparallel enabled.

database oracle  10g






0
dplinnane
Asked:
dplinnane
  • 5
  • 4
1 Solution
 
lwadwellCommented:
Hi dplinnane,

you can start with
SELECT owner, table_name, DEGREE
  FROM all_tables;

the DEGREE column is for the parallel degree.  From what I understand, a value of:
"DEFAULT" indicates that parallel is enabled to the system default,
"         1" (yes, space filled number) more or less indicates no parallel,
anything else should be a (space filled) number indicating the parallel degree specified.


lwadwell
0
 
dplinnaneAuthor Commented:
I was thinking that the degree column might have been the column to look at but I could only find an example
where degree=1.

I looked on another schema and found 2 examples where degree=2 and the tables were set to parallel.
select distinct degree from all_tables  returned values 1,2 I did not see any DEFAULT value.

In conclusion
DEGREE=1 = NOPARALLEL
DEGREE=2 = PARALLEL ( DEGREE 2 INSTANCES 1 )
DEGREE=n= PARALLEL ( DEGREE n INSTANCES 1 )
0
 
lwadwellCommented:
dplinnane,

I have DEFAULT in my database.
From the Oracle documentation for ALL_TABLES (http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/statviews_2105.htm):
DEGREE            VARCHAR2(10)       Number of threads per instance for scanning the table, or DEFAULT
INSTANCES      VARCHAR2(10)       Number of instances across which the table is to be scanned, or DEFAULT

lwadwell
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lwadwellCommented:
dplinnane,

so:
DEGREE=DEFAULT = PARALLEL ( DEGREE DEFAULT )

lwadwell
0
 
dplinnaneAuthor Commented:
A quick question on parallel
select * from sys.V_$INSTANCE;

The value in the column PARALLEL returns NO does that mean that you cannot run any queries in parallel?
0
 
dplinnaneAuthor Commented:
I asked about  the NOPARALLEL because I am doing some tuning in a data warehouse where they have some very large partitioned tables all the tables are set to NOPARALLEL, they have 12 cpu's and 128 Gigs of ram.  reports are taking forever to run, I guess its time to make some of these tables PARALLEL.
0
 
lwadwellCommented:
dplinnane,

no I don't think so, again from the manual
PARALLEL      VARCHAR2(3)      Indicates whether the instance is mounted in cluster database mode (YES) or not (NO)

for parallel SQL you need to check your init parameter PARALLEL_MAX_SERVERS from memory.  Look at
"show parameter parallel"



lwadwell
0
 
lwadwellCommented:
dplinnane,

This is just my personal opinion - so take it as you wish, I am sure you can get many different views on this.

Be careful setting the parallel degree at the table level - at this may globally affect every SQL touching the table.  If all of them are doing full table scans - then parallel can help.  Parallel can be applied at the SQL level as a HINT.

lwadwell
0
 
dplinnaneAuthor Commented:
PARALLEL_MAX_SERVERS=240, thanks for the advice I'll keep that in mind.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now