Disclosure: I did read some documentation and articles available in Internet (including Ask Tom) !
My database version:
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
All the points discussed here is about Query (No DML, No DDL, Only SELECT) with /*+ PARALLEL */ hint.
What I conclude from those articles and documents are:
1. If the TABLE is not PARALLEL enabled then, even if you are using /*+ PARALLEL */ hint, Oracle will not use that feature.
Is that understanding correct ? Here is one test case I have tested.
SQL> /* Table is NOT parallel enabled */
SQL> ALTER TABLE emp NOPARALLEL;
Table altered.
SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL */ * FROM emp;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
"EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
21 rows selected.
SQL>
SQL> /* Table is PARALLEL enabled */
SQL> ALTER TABLE emp PARALLEL;
Table altered.
SQL> EXPLAIN PLAN FOR SELECT /*+ PARALLEL */ * FROM emp;
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display(null,null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 2 (0)| 00:00:01
| | | |
| 1 | PX COORDINATOR | | | | |
| | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 518 | 2 (0)| 00:00:01
| Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 518 | 2 (0)| 00:00:01
| Q1,00 | PCWC | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | TABLE ACCESS FULL| EMP | 14 | 518 | 2 (0)| 00:00:01
| Q1,00 | PCWP | |
--------------------------------------------------------------------------------
------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / EMP@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR
2,9],
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMP"."DEPTNO"[NUMBER,22]
2 - (#keys=0) "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JO
B"[VARCHAR2,9],
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22],
"EMP"."DEPTNO"[NUMBER,22]
3 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR
2,9],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22],
"EMP"."DEPTNO"[NUMBER,22]
4 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR
2,9],
"EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
"EMP"."COMM"[NUMBER,22],
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"EMP"."DEPTNO"[NUMBER,22]
33 rows selected.
SQL>
SQL>
2.
Is there any significance of the parameter parallel_automatic_tuning ? In my case it is:
SQL> SHOW PARAMETER automatic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_automatic_tuning boolean FALSE
SQL>
I have seen in the documentation that it is deprecated. But, in practice, I am finding that If it is TRUE then even if table is NOT parallel enabled then also it is using PARALLEL with
/*+ PARALLEL */ hint.
3.
How to find that the table is PARALLEL enabled or NOT. I mean I have used ALTER TABLE emp NOPARALLEL, So where in the data dictionary I can find this. Currently, I am using tools lke SQL Developer and TOAD to find the DDLs. It is not available in DBMS_METADATA.GET_DDL and dba_tables.degree is inconclusive to me.