Home » SQL & PL/SQL » SQL & PL/SQL » Issue with Parallel Pipelined (Oracle 11g)
Issue with Parallel Pipelined [message #581566] Tue, 09 April 2013 00:27 Go to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I'm doing an exercise of transferring data from one table to another table. Both have same structure the only difference is in P1 table Gender Field have values like (Male/Female) but in P2 table there will be code against them eg. (M/F). Same is with Designation field of both the tables. For this purpose I've created two master tables M_GENDER and M_DESIGNATION respectively. Whatever code will be present in these master table against that description will be inserted in P2 table.

To speed up the process I've decided to use parallel pipeline. There colud be other ways but as I said earlier this is an exercise. In P2 table there is an added field SESSION_ID it is just for tracking purpose to find out how many sessions were present and how many rows were inserted by each session.

To find out performance I've created Millions of records in P1. It's not possible to put insert script for all of them here so I'm putting only few.

Issue is in FUNCTION PARALLEL_PIPELINED that I'm creating. I'm trying to use BULK COLLECT to fetch multiple rows at once and then use a table type collection to hold them and return a table type collection.

Is it possible for a pipelined function to pipe a table type collection or it can only pipe a row type?

Please help me to understand and resolve this issue.

CREATE TABLE P1
  (
    ID          	NUMBER(10,0),
    E_NAME      	VARCHAR2(25),
    GENDER      	VARCHAR2(25),
    DESIGNATION 	VARCHAR2(25),
    CONSTRAINT PK_P1 PRIMARY KEY (ID) 
  ) ;


Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (546,'rajni','Female','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (547,'MANOJ','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (548,'sushila','FEMALe','Sr. Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (549,'Raj kumar','MAle','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (550,'vaibhav','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (551,'sohan','male','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (552,'rajni','Female','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (553,'MANOJ','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (554,'sushila','FEMALe','Sr. Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (555,'Raj kumar','MAle','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (556,'vaibhav','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (557,'sohan','male','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (558,'rajni','Female','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (559,'MANOJ','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (560,'sushila','FEMALe','Sr. Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (561,'Raj kumar','MAle','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (562,'vaibhav','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (563,'sohan','male','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (564,'rajni','Female','Systems Analyst');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (565,'MANOJ','MAle','Software Engineer');
Insert into P1 (ID,E_NAME,GENDER,DESIGNATION) values (566,'sushila','FEMALe','Sr. Software Engineer');
COMMIT ;

CREATE TABLE M_GENDER
(
  G_CODE 	VARCHAR2(1),
  G_DESC 	VARCHAR2(25) NOT NULL ENABLE,
  CONSTRAINT PK_M_GENDER PRIMARY KEY (G_CODE) 
)


CREATE TABLE M_DESIGNATION
(
  D_CODE 	VARCHAR2(5),
  D_DESC 	VARCHAR2(25) NOT NULL ENABLE,
  CONSTRAINT PK_M_DESIGNATION PRIMARY KEY (D_CODE) 
) ;

Insert into M_GENDER (G_CODE,G_DESC) values ('M','Male');
Insert into M_GENDER (G_CODE,G_DESC) values ('F','Female');

Insert into M_DESIGNATION (D_CODE,D_DESC) values ('SE','Software Engineer');
Insert into M_DESIGNATION (D_CODE,D_DESC) values ('SSE','Sr. Software Engineer');
Insert into M_DESIGNATION (D_CODE,D_DESC) values ('SA','Systems Analyst');
COMMIT ;


CREATE TABLE P2
  (
    ID          	NUMBER(10,0),
    E_NAME      	VARCHAR2(25),
    GENDER      	VARCHAR2(25),
    DESIGNATION 	VARCHAR2(25),
    SESSION_ID      NUMBER(10) ,
    CONSTRAINT PK_P2 PRIMARY KEY (ID) 
  ) ;

CREATE OR REPLACE
TYPE TYP_P2 AS OBJECT
(
    ID                  NUMBER(10),
    E_NAME              VARCHAR2(25),
    GENDER              VARCHAR2(1),
    DESIGNATION         VARCHAR2(5),
    SESSION_ID          NUMBER(10)
) ;

CREATE OR REPLACE TYPE TYP_TBL_P2 AS TABLE OF TYP_P2 ;


CREATE OR REPLACE FUNCTION PARALLEL_PIPELINED
(
    PIN_RCURSOR     IN      SYS_REFCURSOR
)
RETURN TYP_TBL_P2
PIPELINED
PARALLEL_ENABLE( PARTITION PIN_RCURSOR BY ANY )
IS
      LVN_SESSION_ID        NUMBER ;
      LVT_TBL               TYP_TBL_P2 ;
BEGIN
      SELECT SYS_CONTEXT('USERENV', 'SID')
      INTO LVN_SESSION_ID
      FROM DUAL ;
      
      LOOP
          FETCH PIN_RCURSOR 
          BULK COLLECT INTO LVT_TBL LIMIT 5000 ;
          
          EXIT WHEN PIN_RCURSOR%NOTFOUND ;

          --PIPE ROW( TYP_P2( LVT_TBL.ID, LVT_TBL.E_NAME, LVT_TBL.GENDER, LVT_TBL.DESIGNATION, LVT_TBL.SESSION_ID  ) ) ;
          PIPE ROW( TYP_P2( LVT_TBL ) ) ;
      END LOOP ;
      
      CLOSE PIN_RCURSOR ;
      
      RETURN ;

END ;



INSERT /*+ APPEND */
INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
    SELECT *
    FROM TABLE( 
            PARALLEL_PIPELINED
                ( 
                      CURSOR
                      (
                          SELECT
                            /*+ PARALLEL(P1) */
                            E1.ID,
                            E1.E_NAME,
                            ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(E1.GENDER) ) G_CODE,
                            ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(E1.DESIGNATION) ) D_CODE
                          FROM E1
                          WHERE NOT EXISTS
                            ( SELECT 1 FROM E2 WHERE E2.ID = E1.ID )
                      ) 
                 )
              ) ;


Thanks & Regards
Manoj
Re: Issue with Parallel Pipelined [message #581774 is a reply to message #581566] Wed, 10 April 2013 09:33 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

Any idea for above issue?

Thanks & Regards
Manoj
Re: Issue with Parallel Pipelined [message #581808 is a reply to message #581774] Wed, 10 April 2013 17:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2005
Registered: January 2010
Senior Member
Did you read what pipelined function is? PIPE ROW pipes back a single row, not a nested table. And that's the advantage of pipelined fucntion. Caller doesn't wait till you fetch all/bulk limit rows. Row is piped back to caller as soon as you issue PIPE ROW, so caller can process that row while pipelined function is busy fetching next row. And your refcursor doesn't select TYP_P2 object - just individual columns. And SYS_CONTEXT is available in PL/SQL - no need to select from dual. Anyway:

CREATE OR REPLACE
  FUNCTION PARALLEL_PIPELINED(
                              PIN_RCURSOR SYS_REFCURSOR
                             )
    RETURN TYP_TBL_P2
    PIPELINED
    PARALLEL_ENABLE(PARTITION PIN_RCURSOR BY ANY)
    IS
        V_TYP_P2 TYP_P2 := TYP_P2(NULL,NULL,NULL,NULL,SYS_CONTEXT('USERENV','SID'));
    BEGIN
        LOOP
          FETCH PIN_RCURSOR 
            INTO V_TYP_P2.ID,
                 V_TYP_P2.E_NAME,
                 V_TYP_P2.GENDER,
                 V_TYP_P2.DESIGNATION;
          EXIT WHEN PIN_RCURSOR%NOTFOUND;
          PIPE ROW(V_TYP_P2);
        END LOOP;
        CLOSE PIN_RCURSOR;
        RETURN;
END;
/


SY.

[Updated on: Wed, 10 April 2013 17:56]

Report message to a moderator

Re: Issue with Parallel Pipelined [message #581831 is a reply to message #581566] Thu, 11 April 2013 04:07 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi SY,

Thanks for your help and support. I got that. But now I'm facing below issue.

Error starting at line 1 in command:
INSERT /*+ APPEND */
INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
    SELECT *
    FROM TABLE( 
            PARALLEL_PIPELINED
                ( 
                      CURSOR
                      (
                          SELECT
                            /*+ PARALLEL(P1) */
                            P1.ID,
                            P1.E_NAME,
                            ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
                            ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE
                          FROM P1
                          WHERE NOT EXISTS
                            ( SELECT 1 FROM P2 WHERE P2.ID = P1.ID )
                      ) 
                 )
              ) 
Error report:
SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at "SCOTT.PARALLEL_PIPELINED", line 11
12838. 00000 -  "cannot read/modify an object after modifying it in parallel"
*Cause:    Within the same transaction, an attempt was made to add read or
           modification statements on a table after it had been modified in parallel
           or with direct load. This is not permitted.
*Action:   Rewrite the transaction, or break it up into two transactions
           one containing the initial modification and the second containing the
           parallel modification operation.


Thanks & Regards
Manoj

[Updated on: Thu, 11 April 2013 04:14]

Report message to a moderator

Re: Issue with Parallel Pipelined [message #581840 is a reply to message #581831] Thu, 11 April 2013 04:34 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just use a straight insert/select statement?
Re: Issue with Parallel Pipelined [message #581850 is a reply to message #581840] Thu, 11 April 2013 04:50 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

(1.) I want to learn this concept and issues related to this. Although there are so many ways to do the same thing. This is the best reason I can give you.
(2.) For better performance as I explained earlier. I've millions of records. Using INSERT/SELECT will not be good idea.

Thanks & Regards
Manoj
Re: Issue with Parallel Pipelined [message #581860 is a reply to message #581850] Thu, 11 April 2013 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
What makes you think insert/select will be less performant than what you are doing?
Re: Issue with Parallel Pipelined [message #581941 is a reply to message #581566] Fri, 12 April 2013 00:55 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi,

Best answer I can give you is.

I want to learn this and want to compare performance for same set of records using different methods. Eg. BULK COLLECT, INSERT/SELECT (PARALLEL), COPY, EXP/IMP etc.

Thanks & Regards
Manoj
Re: Issue with Parallel Pipelined [message #581952 is a reply to message #581941] Fri, 12 April 2013 02:36 Go to previous messageGo to next message
cookiemonster
Messages: 10929
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well you can't use parallel and append with the approach you are using. You should be able to use both with a straight insert/select.
Re: Issue with Parallel Pipelined [message #582112 is a reply to message #581952] Sat, 13 April 2013 08:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2005
Registered: January 2010
Senior Member
Actually, issue is pipelined function is reading from table P2 - same table code is inserting into. If we remove APPEND we will get mutating table error:

SQL> INSERT /*+ APPEND */
  2  INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
  3      SELECT *
  4      FROM TABLE(
  5              PARALLEL_PIPELINED
  6                  (
  7                        CURSOR
  8                        (
  9                            SELECT
 10                              /*+ PARALLEL(P1) */
 11                              P1.ID,
 12                              P1.E_NAME,
 13                              ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
 14                              ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE
 15                            FROM P1
 16                            WHERE NOT EXISTS
 17                              ( SELECT 1 FROM P2 WHERE P2.ID = P1.ID )
 18                        )
 19                   )
 20                )
 21  /
            PARALLEL_PIPELINED
            *
ERROR at line 5:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at "SCOTT.PARALLEL_PIPELINED", line 11


SQL> INSERT
  2  INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
  3      SELECT *
  4      FROM TABLE(
  5              PARALLEL_PIPELINED
  6                  (
  7                        CURSOR
  8                        (
  9                            SELECT
 10                              /*+ PARALLEL(P1) */
 11                              P1.ID,
 12                              P1.E_NAME,
 13                              ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
 14                              ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE
 15                            FROM P1
 16                            WHERE NOT EXISTS
 17                              ( SELECT 1 FROM P2 WHERE P2.ID = P1.ID )
 18                        )
 19                   )
 20                )
 21  /
            PARALLEL_PIPELINED
            *
ERROR at line 5:
ORA-04091: table SCOTT.P2 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.PARALLEL_PIPELINED", line 11


SQL>


Why? Read Controlling Side Effects of PL/SQL Subprograms. It clearly states:

If a function either queries or modifies a table, and a DML statement on that table invokes the function, then ORA-04091 (mutating-table error) occurs. There is one exception: ORA-04091 does not occur if a single-row INSERT statement that is not in a FORALL statement invokes the function in a VALUES clause.

I must say, I hate when Oracle docs are using term DML so loosely (and it is used in such way not just in the above sentence). By DML the above means data modification DML and not SELECT which is a DML too. Also, there is no detail explanation of the above. The above is clear when we use function like:

INSERT ... SELECT ...,some_function,... FROM some_table
INSERT ... SELECT ... FROM some_table WHERE some_function ...
INSERT ... SELECT ... FROM some_table ORDER BY some_function ...


When we issue plain (no functions)

INSERT ... SELECT FROM some_table


oracle fetches first row from some_table, evaluates select list expressions, inserts row into target table and repeats the process until we done fetching rows. Now assume one of select list expressions is a function and it queries target table (one we are inserting into). Obviously we have mutating issue - we are trying to query same table we are inserting into. But what if we have:

INSERT ... SELECT ... FROM TABLE(some_function)


Assume some_function is not a pipelined function but a plain table function. Then sequence of events is:

1. Execute some_function
2. Apply TABLE operator to resultset
3. Fetch a row
4. Exit if not found
5. Evaluate select list expressions
6. Insert a row
7. Go back to 3

As you can see, no mutating situation can occur even if table function queries target table (table we insert into). And we can test it:

CREATE OR REPLACE
  FUNCTION PARALLEL_TABLE(
                          PIN_RCURSOR SYS_REFCURSOR
                         )
    RETURN TYP_TBL_P2
    PARALLEL_ENABLE(PARTITION PIN_RCURSOR BY ANY)
    IS
        V_TYP_P2 TYP_P2 := TYP_P2(NULL,NULL,NULL,NULL,SYS_CONTEXT('USERENV','SID'));
        V_TYP_TBL_P2 TYP_TBL_P2 := TYP_TBL_P2();
    BEGIN
        LOOP
          FETCH PIN_RCURSOR 
            INTO V_TYP_P2.ID,
                 V_TYP_P2.E_NAME,
                 V_TYP_P2.GENDER,
                 V_TYP_P2.DESIGNATION;
          EXIT WHEN PIN_RCURSOR%NOTFOUND;
          V_TYP_TBL_P2.EXTEND;
          V_TYP_TBL_P2(V_TYP_TBL_P2.COUNT) := V_TYP_P2;
        END LOOP;
        CLOSE PIN_RCURSOR;
        RETURN V_TYP_TBL_P2;
END;
/

Function created.

SQL> INSERT /*+ APPEND */
  2  INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
  3      SELECT *
  4      FROM TABLE(
  5              PARALLEL_TABLE
  6                  (
  7                        CURSOR
  8                        (
  9                            SELECT
 10                              /*+ PARALLEL(P1) */
 11                              P1.ID,
 12                              P1.E_NAME,
 13                              ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
 14                              ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE
 15                            FROM P1
 16                            WHERE NOT EXISTS
 17                              ( SELECT 1 FROM P2 WHERE P2.ID = P1.ID )
 18                        )
 19                   )
 20                )
 21  /

21 rows created.

SQL>


As you can see, no mutation occured. Now I'll use pipelined without parallelism and use isert without append:

CREATE OR REPLACE
  FUNCTION NOPARALLEL_PIPELINED(
                                PIN_RCURSOR SYS_REFCURSOR
                               )
    RETURN TYP_TBL_P2
    PIPELINED
    IS
        V_TYP_P2 TYP_P2 := TYP_P2(NULL,NULL,NULL,NULL,SYS_CONTEXT('USERENV','SID'));
    BEGIN
        LOOP
          FETCH PIN_RCURSOR 
            INTO V_TYP_P2.ID,
                 V_TYP_P2.E_NAME,
                 V_TYP_P2.GENDER,
                 V_TYP_P2.DESIGNATION;
          EXIT WHEN PIN_RCURSOR%NOTFOUND;
          PIPE ROW(V_TYP_P2);
        END LOOP;
        CLOSE PIN_RCURSOR;
        RETURN;
END;
/

Function created.

SQL> INSERT
  2  INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
  3      SELECT *
  4      FROM TABLE(
  5              NOPARALLEL_PIPELINED
  6                  (
  7                        CURSOR
  8                        (
  9                            SELECT
 10                              P1.ID,
 11                              P1.E_NAME,
 12                              ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
 13                              ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE
 14                            FROM P1
 15                            WHERE NOT EXISTS
 16                              ( SELECT 1 FROM P2 WHERE P2.ID = P1.ID )
 17                        )
 18                   )
 19                )
 20  /
            NOPARALLEL_PIPELINED
            *
ERROR at line 5:
ORA-04091: table SCOTT.P2 is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.NOPARALLEL_PIPELINED", line 10


SQL>


We got mutating table error. But why? I believe it is a bug. What has changed comparing to plain table function? We pipe row as soon as we fetch it and insert starts right away. So what? In both cases cursor was open BEFORE insert started, so there is no mutating table situation. But we have ... what we have (OP is welcome to open a SR with Oracle for an explanation) and we can't use pipelined function is this case. I hope it is a bit more clear now.

SY.

[Updated on: Sat, 13 April 2013 08:22]

Report message to a moderator

Re: Issue with Parallel Pipelined [message #582113 is a reply to message #581952] Sat, 13 April 2013 08:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2005
Registered: January 2010
Senior Member
cookiemonster wrote on Fri, 12 April 2013 03:36
Well you can't use parallel and append with the approach you are using. You should be able to use both with a straight insert/select.


It is append or parallel not parallel and append. Both will cause same error:

SQL> rollback;

Rollback complete.

SQL> Insert /*+ APPEND */ into P2 (ID,E_NAME,GENDER,DESIGNATION) values (888,'rajni','Female','Systems Analyst');

1 row created.

SQL> Insert into P2 (ID,E_NAME,GENDER,DESIGNATION) values (999,'rajni','Female','Systems Analyst');

1 row created.

SQL> rollback;

Rollback complete.

SQL> Insert /*+ APPEND */ into P2 (ID,E_NAME,GENDER,DESIGNATION) select 888,'rajni','Female','Systems Analyst' from dual;

1 row created.

SQL> Insert into P2 (ID,E_NAME,GENDER,DESIGNATION) values (999,'rajni','Female','Systems Analyst');
Insert into P2 (ID,E_NAME,GENDER,DESIGNATION) values (999,'rajni','Female','Systems Analyst')
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>


Error message is misleading. It lists just one cause - parallel:

ORA-12838: cannot read/modify an object after modifying it in parallel

Cause: Within the same transaction, an attempt was made to add read or modification statements on a table after it had been modified in parallel or with direct load. This is not permitted.

SY.

[Updated on: Sat, 13 April 2013 08:47]

Report message to a moderator

Re: Issue with Parallel Pipelined [message #582140 is a reply to message #582113] Sun, 14 April 2013 03:50 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi SY,

Thankyou very much for your detailed explanation.

I strongly agree with your comments. I've also tried the same where I'm not using Parallel Pipelined and APPEND both. But still I face mutating error. Ideally this should not happen because cursor should have been opened before starting Insert or Before piping first row. This looks like cursor is simply opened but it is fetching data continuously.

Eg.
(1.) If we have cursor with two million rows. If oracle opens the cursor and fetches all rows before piping it's first row then this situation will not occur. But it will have performance issue.
(2.) It looks like cursor opens and fetches first few rows faster and starts piping and then it does fetching and piping in parallel so we face this mutating error.

Am I clear?

Thanks & Regards
Manoj
Re: Issue with Parallel Pipelined [message #582147 is a reply to message #582140] Sun, 14 April 2013 05:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2005
Registered: January 2010
Senior Member
Manoj.Gupta.91 wrote on Sun, 14 April 2013 04:50
Am I clear?


Yes, you correctly described what is happening. Hiowever, I assume your example is just for learning purposes. You code is nothing but:

INSERT /*+ APPEND */
INTO P2 ( ID, E_NAME, GENDER, DESIGNATION, SESSION_ID )
    SELECT  /*+ PARALLEL(P1) */
            P1.ID,
            P1.E_NAME,
            ( SELECT MG.G_CODE FROM M_GENDER MG WHERE UPPER(MG.G_DESC) = UPPER(P1.GENDER) ) G_CODE,
            ( SELECT MD.D_CODE FROM M_DESIGNATION MD WHERE UPPER(MD.D_DESC) = UPPER(P1.DESIGNATION) ) D_CODE,
            SYS_CONTEXT('USERENV', 'SID')
      FROM P1
      WHERE NOT EXISTS(
                       SELECT 1 FROM P2 WHERE P2.ID = P1.ID
                      ) 
/


And doing it via cursor, table function (pipeliined or not) will degrade (not by much, but nevertheless) performance.

SY.
Re: Issue with Parallel Pipelined [message #582162 is a reply to message #581566] Mon, 15 April 2013 01:35 Go to previous message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi SY,

Thankyou very much.

This example was only for learning purpose.

Regards
Manoj
Previous Topic: EXECUTE IMMEDIATE HELP
Next Topic: duplicates issue - UNIQUE constraint
Goto Forum:
  


Current Time: Fri Aug 29 09:24:52 CDT 2014

Total time taken to generate the page: 0.10708 seconds