How can I prove the validation of a constraint went parallel?
11.1.0.6 (Win32)
Hi, I'm working on a test case to see if I can prove a constraint is validated in parallel. I am a database developer for a data warehouse and am experimenting with disabling and enabling constraints. In my test database I could not ever seem to get constraints to validate in parallel so I figured I needed to create a test case to help me understand what is going on and ultimately to diagnose why I'm not getting parallel.
The oracle documentation (11.1 performance tuning quide) says:
>
Integrity Constraints and Parallelism
All constraints can be validated in parallel. When validating constraints on very large tables, parallelism is often necessary to meet performance goals. The degree of parallelism for a given constraint operation is determined by the default degree of parallelism of the underlying table.
>
I created this test case but cannot confirm if the validate went parallel or not. By observation it appears
not to. However, there must be a way to measure this.
You'll see in this script that I cannot seem to find anything in the MYSTAT view as the parallel stats in there do not budge but they also may not budge for recursive queries like validations...
/******************************************************************************
How do I know if a constraint was validated in parallel based on this excerpt
from the Oracle 11.1 Data Warehousing Guide
Integrity Constraints and Parallelism
All constraints can be validated in parallel. When validating constraints on
very large tables, parallelism is often necessary to meet performance goals.
The degree of parallelism for a given constraint operation is determined by
the default degree of parallelism of the underlying table.
*******/
SQL> create table big_table parallel 2 as
(
select
rownum as id
, lpad(' ',4000,' ') as filler_txt
from
dual connect by level < 100000
)
Table created.
SQL> create unique index big_table$pk on big_table (id) parallel 2
Index created.
SQL> alter table big_table add
constraint big_table$pk primary key (id)
enable
validate
Table altered.
SQL> select name, value, class from sys.v_$mystat inner join v$statname using (statistic#)
where name in ('queries parallelized','DML statements parallelized','DDL statements parallelized')
order by name, value
NAME VALUE
---------------------------------------------------------------- ----------
DDL statements parallelized 6
DML statements parallelized 0
queries parallelized 0
3 rows selected.
SQL> alter table big_table disable primary key
Table altered.
SQL> alter table big_table enable primary key
Table altered.
SQL> select name, value, class from sys.v_$mystat inner join v$statname using (statistic#)
where name in ('queries parallelized','DML statements parallelized','DDL statements parallelized')
order by name, value
NAME VALUE
---------------------------------------------------------------- ----------
DDL statements parallelized 6
DML statements parallelized 0
queries parallelized 0
3 rows selected.