Effects of PARALLEL hint in different parts of SQL script
I have a fairly large data warehouse with most of the Child tables having more than 5 billion rows. They are partitioned by a DATE column.
I have several local B-tree and bitmap indexes on the appropriate columns.
The DEGREE on most of the large tables is currently set to 4.
AUTO_DOP is not currently being used.
The DW runs on a 64 processor server with 64GB memory and 16k block size.
Database is Oracle 11.2.0.2 EE on Solaris
I have several queries that extract data into separate reference tables that are totally refreshed on typically a monthly basis although a few are daily.
The queries use an INSERT /*+ APPEND */ and usually are a JOIN of 3-4 tables and/or in-line views with a GROUP BY on 1-2 columns.
I'm trying to find the best place to specify a PARALLEL hint or optionally force parallel DML in the session and set the degree via the 'ALTER SESSION ...' statement.
Here are the options I'm looking at: (others are greatly appreciated!)
1) use the /*+ PARALLEL x */ hint only on the topmost SELECT
2) specify the /*+ PARALLEL x */ hint on each separate SELECT in all the sub-queries and in-line views where it's deemed useful (i.e. not on very small tables).
3) use an 'ALTER SESSION FORCE PARALLEL DML PARALLEL x;'
Questions:
a) by using option (1) will the DOP in that topmost SELECT be used for all subsequent SELECT statements below it?
b) same question for using option (3)?
c) how can I monitor the activity and verify what DOP is used in the different query sections? I have tried to follow the script execution in TOAD but haven't had much luck.
Thanks very much for your help and please let me know if you need any more information.
-gary