query to find PARALELL or NOPARALLEL on a table

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 )

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

dplinnane,

so:
DEGREE=DEFAULT = PARALLEL ( DEGREE DEFAULT )

lwadwell

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?

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.

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

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

PARALLEL_MAX_SERVERS=240, thanks for the advice I'll keep that in mind.