Oracle

October 26, 2010

PARALLEL_ENABLE & Deterministic

Filed under: Uncategorized — srivenu @ 8:30 pm

I was recently asked to analyze and improve the performance of a report in a DWH environment.
The sql was something like this

create table X nologging parallel 3 as
select a.*, b.operator, b.circle,
substr(calling_no,length(std_code_fixed.stdcodegen(calling_no))+1,1) as fixed_code
from a,
b
where b.point(+)=substr(calling_no,1,4)
…………..
…………..

Despite hinting by the developers, the sql was not getting parallelized.

Having faced the issue in the past, i could make out the issue and parallel_enable ‘d the function stdcodegen.

For optimization of plsql function calls in sql, this note from Jonathan provides more details.

Blog at WordPress.com.