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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: