PARALLEL Query,DML,Execution

- data dict Related to PARALLEL Operations:
V$PX_PROCESS,
V$PX_SYSSTAT,
V$PQ_SYSSTAT,
V$PQ_SLAVE,
DBA_TABLES.degree,
DBA_INDEXES.degree

- init parameters related to PARALLEL Opearions:

– parallel_min_servers = n ora_p0NN_SID servers created at startup
– parallel_max_servers = n
– parallel_server_idle_time = n servers terminated if idle this long
– parallel_threads_per_cpu default recommended
– parallel_min_percent = n if n servers requested not available, do
not run query and return an error
- parallel DML (PDML):
– must explicitly enable pdml: alter session enable parallel DML;
– direct path insert (direct load insert): use /*+ append */ hint
– inserted data is placed above the high water mark
– triggers must be disabled during parallel DML
– parallel statement MUST be committed before issuing another statement
– set transaction use rollback segment … not valid with pdml
– updates and deletes not parallelized on non-partitioned tables
- hints override parallel defaults set in create table statement
“delete /*+ parallel(, ) */ from ”

- create table emp1_dept parallel 5 as select * from scott.emp where deptno=10;

- ALTER TABLE emp PARALLEL 4; note, the 4 is optional, oracle can set degree

- space management implications: objects created in parallel will have at least as many extents as parallel execution servers used.

- parallelization of functions/packages: must be created with

create function … parallel_enable …

- PQ performs direct reads so blocks not written to buffer cache