Insert As Select Is Slow When Running In Parallel (Doc ID 1265719.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 22-Apr-2013***

Symptoms


  Database was upgraded from 9i to 10.2.0.4. Following the upgrade the following INAS was slow:

INSERT /*+ PARALLEL(BOA_VXSI_Cell_Est, 2) */ INTO BOA_VXSI_Cell_Est(
campaign_id, offer_cell_id, resp_grp_id, behavior_id, behavior_name, est_rate, est_count, est_fulfill,
est_return, offercelltrkcd, valueproptrkcd, treattypetrkcd
)
SELECT a.campaign_id, a.offer_cell_id, a.resp_grp_id, b.behavior_id, b.name, a.est_rate, a.est_count,
c.est_fulfill, c.est_return, d.tracking_code, d.vp_tracking_code, e.tt_tracking_code
FROM VXSI_SPLIT e, VXSI_SPLIT_CELL d, VXSI_CELL_EST a, VXSI_VP_RESP_GRPS c,VXSI_BEHAVIORS b
WHERE TO_NUMBER(a.campaign_version||a.fact_version) = (SELECT MAX (TO_NUMBER(campaign_version||fact_version)) FROM vxsi_cell_est z
WHERE a.campaign_id = z.campaign_id) AND b.campaign_version = (SELECT MAX (campaign_version) FROM vxsi_behaviors x WHERE x.campaign_id = b.campaign_id) AND
c.campaign_version = (SELECT MAX (campaign_version) FROM vxsi_vp_resp_grps w WHERE w.campaign_id = c.campaign_id AND w.resp_grp_id = c.resp_grp_id) AND
d.campaign_version = (SELECT MAX (campaign_version) FROM vxsi_split_cell v WHERE v.campaign_id = d.campaign_id AND v.split_cell_id = d.split_cell_id AND
v.tracking_code = d.tracking_code) AND e.campaign_version = (SELECT MAX (campaign_version) FROM vxsi_split u WHERE u.campaign_id = e.campaign_id AND
u.split_id = e.split_id) AND a.campaign_id = b.campaign_id (+) AND a.behavior_id = b.behavior_id (+) AND a.campaign_id = c.campaign_id (+) AND
a.resp_grp_id = c.resp_grp_id (+) AND a.offer_cell_id = d.split_cell_id (+) AND d.split_id = e.split_id AND a.campaign_id = e.campaign_id
/

The issue was investigate from the serial point of view and in serial mode and obtained a good plan by adding a LEADING hint to the SELECT. Still parallel plan is slow.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms