version : oracle 10g
hi all,
i am trying to enable parallel dml in the program but the updates are not executed in parallel mode.
i tried so many ways but i could'nt make the update to execute in parallel.
But if i alter session in sqlplus and trace explain just for the update in sqlplus then i can see that the update is in parallel mode.
can some one please explain where i am going wrong.
/***IS it because that i am updating the table based on partition --- Is it the Restriction ****************/
/* Formatted on 1/26/2011 10:31:48 AM (QP5 v5.115.810.9015) */
PROCEDURE pr_remap_id (p_person_id IN ACXIOM_LOAD.person_id%TYPE,
p_partition_name IN VARCHAR2)
IS
/* Execute this Third */
-- Ref Cursor to process Acxiom_load
-----------------------------------
TYPE Cref_Acxiom_load IS REF CURSOR;
c_Acxiom_load Cref_Acxiom_load;
TYPE r_rec IS RECORD (
axiom_load_person_id DBMS_SQL.number_table,
We_id DBMS_SQL.number_table,
cleanse_code DBMS_SQL.number_table
);
l_rec r_rec;
l_rec1 r_rec;
l_rec9 r_rec;
v_index1 NUMBER := 0;
v_index9 NUMBER := 0;
v_sql VARCHAR2 (4000);
v_person_id NUMBER := 0;
v_context VARCHAR2 (1000);
v_procedure_name VARCHAR2 (32);
v_partition_name VARCHAR2 (10) := 'PT_' || p_partition_name;
BEGIN
v_person_id := p_person_id;
IF v_person_id IS NULL
THEN
SELECT MAX (company_id) INTO v_person_id FROM acxiom;
END IF;
v_context := 'Construct Sql for partition ' || p_partition_name;
v_sql :=
' SELECT axiom_load_person_id, '
|| ' CASE '
|| ' WHEN load_cnt = axiom_cnt THEN axiom_person_id '
|| ' ELSE parent_id '
|| ' END '
|| ' We_id, '
|| ' CASE WHEN load_cnt = axiom_cnt THEN 1 ELSE 9 END '
|| ' cleanse_code '
|| ' FROM (SELECT /*+ USE_HASH (axiom_load acx) parallel (acx,4)*/ '
|| ' acx.person_id axiom_person_id, '
|| ' axiom_load.person_id axiom_load_person_id, '
|| ' axiom_load.first_name, '
|| ' axiom_load.last_name, '
|| ' axiom_load.Birth_date, '
|| ' axiom_load.middle_name, '
|| ' axiom_load.load_cnt, '
|| ' COUNT( * ) '
|| ' OVER (PARTITION BY TRUNC (acx.person_id, 5)) '
|| ' axiom_cnt, '
|| ' MIN(acx.person_id) '
|| ' OVER (PARTITION BY TRUNC (acx.person_id, 5)) '
|| ' parent_id '
|| ' FROM (SELECT /*+ parallel (a,4)*/ '
|| ' person_id, '
|| ' last_name, '
|| ' first_name, '
|| ' Birth_date, '
|| ' middle_name, '
|| ' COUNT( * ) '
|| ' OVER (PARTITION BY TRUNC (person_id, 5)) '
|| ' load_cnt '
|| ' FROM acxiom_load PARTITION ('
|| v_partition_name
|| ') a '
|| ' WHERE person_id > NVL (:p_person_id, 0)) '
|| ' axiom_load, '
|| ' Acxiom acx '
|| ' WHERE axiom_load.last_name = acx.last_name '
|| ' AND axiom_load.first_name = acx.first_name '
|| ' AND axiom_load.Birth_date = acx.Birth_date '
|| q'[ AND NVL (axiom_load.middle_name, '1') = ]'
|| q'[ NVL (acx.middle_name, '1')) ]';
EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML PARALLEL 4';
OPEN c_Acxiom_load FOR v_sql USING v_person_id;
LOOP
FETCH c_Acxiom_load BULK COLLECT INTO l_rec LIMIT g_commit_limit;
FOR i IN 1 .. l_rec.axiom_load_person_id.COUNT
LOOP
IF l_rec.cleanse_code (i) = 1
THEN
v_index1 := l_rec1.axiom_load_person_id.COUNT + 1;
l_rec1.axiom_load_person_id (v_index1) :=
l_rec.axiom_load_person_id (i);
l_rec1.We_id (v_index1) := l_rec.We_id (i);
l_rec1.cleanse_code (v_index1) := l_rec.cleanse_code (i);
ELSE
v_index9 := l_rec9.axiom_load_person_id.COUNT + 1;
l_rec9.axiom_load_person_id (v_index9) :=
l_rec.axiom_load_person_id (i);
l_rec9.We_id (v_index9) := l_rec.We_id (i);
l_rec9.cleanse_code (v_index9) := l_rec.cleanse_code (i);
END IF;
END LOOP;
FORALL i IN 1 .. l_rec1.axiom_load_person_id.COUNT
SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'UPDATE /*+ parallel (a) INDEX(A,ACXIOM_LOAD_I2)*/ '
|| ' ACXIOM_LOAD PARTITION('
|| v_partition_name
|| ') a'
|| ' SET we_pid = :we_id , '
|| ' we_Cleanse_code = :Cleanse_code '
|| ' WHERE person_id = :l_person_id '
USING l_rec1.We_id (i),
l_rec1.Cleanse_code (i),
l_rec1.axiom_load_person_id (i);
COMMIT;
FORALL i IN 1 .. l_rec9.axiom_load_person_id.COUNT
SAVE EXCEPTIONS
EXECUTE IMMEDIATE 'UPDATE /*+ parallel (a) INDEX(A,ACXIOM_LOAD_IP6) */ '
|| ' ACXIOM_LOAD PARTITION('
|| v_partition_name
|| ') a '
|| ' SET we_pid = :weid,'
|| ' we_Cleanse_code = :cleanse_code '
|| ' WHERE TRUNC (person_id, 5) = TRUNC (:l_pid, 5)'
USING l_rec9.We_id (i),
l_rec9.Cleanse_code (i),
l_rec9.axiom_load_person_id (i);
COMMIT;
l_rec1.axiom_load_person_id.DELETE;
l_rec1.We_id.DELETE;
l_rec1.cleanse_code.DELETE;
l_rec9.axiom_load_person_id.DELETE;
l_rec9.We_id.DELETE;
l_rec9.cleanse_code.DELETE;
EXIT WHEN c_Acxiom_load%NOTFOUND;
END LOOP;
CLOSE c_Acxiom_load;
EXECUTE IMMEDIATE ' ALTER SESSION DISABLE PARALLEL DML';
END pr_remap_id;
Thanks
Edited by: new learner on Jan 26, 2011 7:39 AM