SYS.TADG>select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
I have created two tables P (Master table) and C (Child table).
create table p (id number,name varchar2(10));
alter table p add constraint pk1 primary key (id);
declare
begin
for i in 1..100000
loop
insert into p values(i,'xxx');
end loop;
end;
/
--
create table c (id number ,address varchar2(10));
alter table c add constraint fk1 foreign key(id) references p(id);
(i have un-indexed fk on c)
--
declare
begin
for i in 1..180000
loop
insert into c values(1,'yyy');
end loop;
end;
/
alter table p parallel 8;
alter table c parallel 8;
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS','P',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>8,NO_INVALIDATE=>FALSE);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS','C',METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE AUTO',CASCADE=>TRUE,DEGREE=>8,NO_INVALIDATE=>FALSE);
select count(*) from sys.c where id=1;
COUNT(*)
----------
180000
--
alter session enable parallel dml;
explain plan for
DELETE /*+PARALLEL (C,8) */ FROM C WHERE ID=1;
Plan hash value: 2474876329
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1800 | 12600 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | DELETE | C | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| C | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("ID"=1)
--
set timing on
DELETE /*+PARALLEL (C,8) */ FROM C WHERE ID=1;
180000 rows deleted.
Elapsed: 00:00:07.42
break on dfo_number on tq_id
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES
---------- ---------- ------------------------------ ------------------------------ ---------- ----------
1 0 Consumer QC 8 952
1 row selected.
It seems, PX have not allocated and DELETE went serialized.
(i have px_max_servers=128) and no one else is using any px.
ROLLBACK
Now i am creating index on fk on C.
create index c_idx on c(id);
select count(*) from sys.c where id=1;
COUNT(*)
----------
180000
explain plan for
DELETE /*+PARALLEL (C,8) */ FROM C WHERE ID=1;
Plan hash value: 1697380224
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1800 | 12600 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | C | | | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | P->P | RANGE |
| 6 | DELETE | C | | | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| C | 1800 | 12600 | 9 (12)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("ID"=1)
Why Index maintenance is happening after entire deletion ? Does that mean, parallel DELETE has occurred ? I believe, without PARALLEL DML, index would be maintained for every single DELETE. Correct me if i am wrong...
DELETE /*+PARALLEL (C,8) */ FROM C WHERE ID=1;
180000 rows deleted.
Elapsed: 00:00:13.14
break on dfo_number on tq_id
SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
DFO_NUMBER TQ_ID SERVER_TYPE PROCESS NUM_ROWS BYTES
---------- ---------- ------------------------------ ------------------------------ ---------- ----------
1 0 Ranger QC 0 384
1 Consumer QC 8 944
Here, consumer slave is allocated which confirms that DELETE have used px. (was that for DELETE Or Scanning rows from table where ID=1 ?)
Can anyone confirm which test (Without FK INDEX / WITH FK INDEX) is PARALLEL DML (DELETE) ?
One of my large non-partitioned table (10 GB-128M rows) is having 1 Pk and 5 un-indexed FKs.
Delete on that table (deleting 39M) is taking 5 hrs and i have seen that even though 8 slaves are allocated, only QC is doing all deletion.
Execution plan is not showing any step like : INDEX MAINTANANCE..It is just FTS with PX. This is the reason why i am performing this parallel Delete test !