`

pipelined function issues(原创)

 
阅读更多

Pipelined functions have been available in Oracle for several versions (and years) since 9i Release 1 and there are several related articles on oracle-developer.net. This latest article summarises some of the issues we might encounter when using pipelined functions in our applications.

issue 1: parallel pipelined functions and cursor variables

One of the major benefits of pipelined functions is that they can be parallelised. This means that Oracle's parallel query mechanism can be exploited to execute PL/SQL code in parallel, providing excellent performance gains. For a pipelined function to be parallel-enabled, however, it must take its source dataset from a ref cursor parameter (rather than have a static cursor defined inside the PL/SQL). Such a function would be used as follows (in pseudo-code):

SELECT *

FROM   TABLE(

          pipelined_function(

             CURSOR(SELECT * FROM staging_table))) --<-- input rowsource

The cursor parameter in a parallel-enabled pipelined function can be defined as either a weak or strong refcursor. The first issue we will see is that when we use a cursor variable to pass to the pipelined function (instead of the direct CURSOR function call as above), Oracle's parallel DML fails.

It should be stated at the outset that this problem only occurs when we are using parallel DML to load a table from the resultset of a pipelined function (i.e. not when simply selecting from the function). This is important, however, because parallel DML from pipelined functions is how Oracle most commonly demonstrates the technology in its articles and documentation.

We will setup a small example of a parallel insert from a parallel pipelined function. We will begin by creating a source and target table of the same structure (for simplicity) as follows.

SQL> CREATE TABLE source_table ( x PRIMARY KEY, y )

  2  PARALLEL

  3  NOLOGGING

  4  AS

  5     SELECT ROWNUM, CAST(NULL AS INTEGER)

  6     FROM   dual

  7     CONNECT BY ROWNUM <= 10000;

Table created.

SQL> CREATE TABLE target_table

  2  PARALLEL

  3  NOLOGGING

  4  AS

  5     SELECT *

  6     FROM   source_table;

Table created.

We will now create the types required for our pipelined function (an object type to define a record and a collection type for buffering arrays of this record), as follows.

SQL> CREATE TYPE target_table_row AS OBJECT

  2  ( x INT, y INT );

  3  /

Type created.

SQL> CREATE TYPE target_table_rows

  2     AS TABLE OF target_table_row;

  3  /

Type created.

To complete our setup, we will create a parallel-enabled pipelined function (in a package) as follows. The cursor parameter in this example is defined as a SYS_REFCURSOR (built-in weak refcursor type).

SQL> CREATE OR REPLACE PACKAGE etl_pkg AS

  2  

  3     FUNCTION pipelined_fx (p_cursor IN SYS_REFCURSOR)

  4        RETURN target_table_rows PIPELINED

  5        PARALLEL_ENABLE (PARTITION p_cursor BY ANY);

  6  

  7  END etl_pkg;

  8  /

Package created.

The function, implemented in the package body below, simply pipes out the input dataset. Obviously, this is not a true representation of what pipelined functions are needed for, but it keeps the example short and simple.

SQL>CREATE OR REPLACE PACKAGE BODY etl_pkg AS                      
    FUNCTION pipelined_fx (p_cursor IN SYS_REFCURSOR)           
       RETURN target_table_rows PIPELINED                       
       PARALLEL_ENABLE (PARTITION p_cursor BY ANY) IS           
             TYPE cursor_ntt IS TABLE OF source_table%ROWTYPE;  
      nt_src_data cursor_ntt;                                   
 BEGIN                                                          
 LOOP                                                           
    FETCH p_cursor BULK COLLECT INTO nt_src_data LIMIT 100;     
    FOR i IN 1 .. nt_src_data.COUNT LOOP                        
       PIPE ROW (target_table_row(                              
                    nt_src_data(i).x, nt_src_data(i).y          
                    ));                                         
    END LOOP;                                                   
     EXIT WHEN p_cursor%NOTFOUND;                               
  END LOOP;                                                     
  CLOSE p_cursor;                                               
  RETURN;                                                       
     END pipelined_fx;                                          
 END etl_pkg;                                                   
Package body created.

We will now test the function by inserting its output into the target table we created earlier. We enable both parallel query and parallel DML to avoid having to use any hints (we already parallel-enabled both the source and target tables). Note how we use the CURSOR function to supply the refcursor parameter to our pipelined function.

SQL> ALTER SESSION ENABLE PARALLEL QUERY;

Session altered.

SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

SQL> BEGIN

  2

  3     INSERT INTO target_table

  4     SELECT *

  5     FROM   TABLE(

  6               etl_pkg.pipelined_fx(

  7                  CURSOR(SELECT * FROM source_table) ));

  8

  9     DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows inserted.' );

 10

 11     ROLLBACK;

 12

 13  END;

 14  /

10000 rows inserted.

PL/SQL procedure successfully completed.

As we are using a refcursor parameter in our pipelined function, we might wish to use a cursor variable instead of a direct CURSOR function call. For example, our source data cursor might be large and complex. Therefore, we might wish to avoid having to embed a large SQL statement in a packaged function call. The following example demonstrates, however, that this will not work with parallel DML statements. This is true of all versions up to and including 11.1.0.6 (this is the latest version tested).

SQL> DECLARE
  2     rc SYS_REFCURSOR;
  3   BEGIN
  4         OPEN RC FOR SELECT * FROM source_table;
  5         INSERT INTO target_table
  6        SELECT *
  7        FROM   TABLE(etl_pkg.pipelined_fx(rc));
  8          DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows inserted.' );
  9        ROLLBACK;
 10       END;
 11       /
DECLARE
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P007
ORA-01008: not all variables bound
ORA-06512: at line 9
The cursor variable method works in serial mode. If we disable parallel DML, the statement succeeds as follows.
SQL> ALTER SESSION DISABLE PARALLEL DML;
Session altered.
SQL> DECLARE
  2      rc SYS_REFCURSOR;
  3   BEGIN
  4     OPEN RC FOR SELECT * FROM source_table;
  5      INSERT INTO target_table
  6       SELECT *
  7       FROM   TABLE(etl_pkg.pipelined_fx(rc));
  8      DBMS_OUTPUT.PUT_LINE( SQL%ROWCOUNT || ' rows inserted.' );
  9      ROLLBACK;
 10   END;
 11     /
10000 rows inserted.
PL/SQL procedure successfully completed.

This issue has been recorded as bug 5349930 and still exists in 11.1.0.6.

issue 2: performance with wide records

One of the uses for pipelined functions is to replace row-by-row inserts/updates with a piped rowsource that is bulk loaded. For example, the following "traditional" PL/SQL-based ETL technique is slow and inefficient.

FOR rec IN (SELECT * FROM source_data) LOOP

   ...prepare table_A variables...

   INSERT INTO table_A VALUES (...);

END LOOP;

Assuming the "prepare table_A variables" stage in the above pseudo-code is sufficiently complex to warrant the use of PL/SQL, pipelined functions can be used to exploit bulk SQL techniques as follows.

INSERT INTO table_A (...)

SELECT ...

FROM   TABLE( pipelined_fx( CURSOR(SELECT * FROM source_table) ) );

We can achieve some good performance gains by adopting this technique, especially if we use parallel-enabled pipelined functions. There is, however, an issue with "wide" records where the efficiency of pipelined functions degrades quite dramatically to the point where the row-by-row alternative is faster.

To demonstrate this issue, we are going to manufacture some variable-width records and simply compare the time it takes to load one table using row-by-row inserts and bulk insert from a pipelined function. We will begin by creating a source and target table with just three columns to demonstrate the type of comparison we will be making.

SQL> CREATE TABLE src

  2  CACHE

  3  AS

  4     SELECT 'xxxxxxxxxx' AS c1

  5     ,      'xxxxxxxxxx' AS c2

  6     ,      'xxxxxxxxxx' AS c3

  7     FROM   dual

  8     CONNECT BY ROWNUM <= 50000;

Table created.

SQL> CREATE TABLE tgt

  2  AS

  3     SELECT *

  4     FROM   src

  5     WHERE  ROWNUM < 1;

Table created.

We will now create an ETL package containing the two methods of loading that we wish to compare. The specification is as follows.

SQL> CREATE PACKAGE etl_pkg AS
  2       PROCEDURE row_by_row;
  3       PROCEDURE bulk_from_pipeline;
  4       TYPE piped_rows IS TABLE OF tgt%ROWTYPE;
  5       FUNCTION pipelined_fx(p_cursor IN SYS_REFCURSOR)
  6          RETURN piped_rows PIPELINED;
  7    END etl_pkg;
  8    /
Package created.

Note that we have defined a PL/SQL-based collection type for our pipelined function. This method can raise issues in itself (as we will see later in this article), but for the purposes of this demonstration, it aligns the collection to however many columns the target table happens to have at runtime (if the table changes, Oracle will recompile the package when we next execute it and create a new underlying collection type to support the pipelined function).

The package body is as follows. We can see that the ROW_BY_ROW and BULK_FROM_PIPELINE procedures implement the two types of loading we saw in pseudo-code earlier. For simplicity, the pipelined function pipes out the input dataset without any modifications.

SQL> CREATE PACKAGE BODY etl_pkg AS

  2

  3     PROCEDURE row_by_row IS

  4     BEGIN

  5        FOR r IN (SELECT * FROM src) LOOP

  6           INSERT INTO tgt VALUES r;

  7        END LOOP;

  8        COMMIT;

  9     END row_by_row;

 10

 11     PROCEDURE bulk_from_pipeline IS

 12     BEGIN

 13        INSERT INTO tgt

 14        SELECT *

 15        FROM   TABLE(

 16                  etl_pkg.pipelined_fx(

 17                     CURSOR( SELECT * FROM src )));

 18        COMMIT;

 19     END bulk_from_pipeline;

 20

 21     FUNCTION pipelined_fx(p_cursor IN SYS_REFCURSOR)

 22        RETURN piped_rows PIPELINED IS

 23        nt piped_rows;

 24     BEGIN

 25        LOOP

 26           FETCH p_cursor BULK COLLECT INTO nt LIMIT 100;

 27           FOR i IN 1 .. nt.COUNT LOOP

 28              PIPE ROW (nt(i));

 29           END LOOP;

 30           EXIT WHEN p_cursor%NOTFOUND;

 31        END LOOP;

 32        RETURN;

 33     END pipelined_fx;

 34

 35  END etl_pkg;

 36  /

Package body created.

With just three columns to begin, we will compare the two methods using the wall-clock as follows. Note that we have deliberately turned off the PL/SQL compiler optimisation introduced in 10g (this optimisation turns cursor-for-loops into implicit bulk fetches).

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Session altered.

SQL> set timing on

SQL> exec etl_pkg.row_by_row;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.82

SQL> exec etl_pkg.bulk_from_pipeline;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.59

At such low data volume, the timings are reasonably meaningless (note that the source table data was already queried before the test, so the effects of PIOs vs LIOs should be mitigated). Nevertheless, the pipelined function method is noticeably faster.

We will now create a small procedure that will rebuild our tables with the number of columns we supply. We will use this to compare ROW_BY_ROW and BULK_FROM_PIPELINE at various record-widths.

SQL> CREATE PROCEDURE rebuild_tables( p_cols IN NUMBER ) IS
  2
  3     v_ddl VARCHAR2(32767) := 'CREATE TABLE src CACHE AS SELECT ';
  4
  5     PROCEDURE drop_table( p_table IN VARCHAR2 ) IS
  6        x_no_such_table EXCEPTION;
  7        PRAGMA EXCEPTION_INIT(x_no_such_table, -942);
  8     BEGIN
  9        EXECUTE IMMEDIATE 'DROP TABLE ' || p_table;
 10     EXCEPTION
 11        WHEN x_no_such_table THEN NULL;
 12     END drop_table;
 13
 14  BEGIN
 15        drop_table('TGT');
 16     FOR i IN 1 .. p_cols LOOP
 17        v_ddl := v_ddl || '''xxxxxxxxxx'' AS c' || i || ',';
 18    END LOOP;
 19     v_ddl := RTRIM(v_ddl,',') ||
 20              ' FROM dual CONNECT BY ROWNUM <= 50000';
 21     EXECUTE IMMEDIATE v_ddl;
 22    v_ddl := 'CREATE TABLE tgt
 23               AS
 24                  SELECT *
 25                 FROM src
 26                  WHERE ROWNUM < 1';
 27    EXECUTE IMMEDIATE v_ddl;
 28     DBMS_STATS.GATHER_TABLE_STATS(user,'SRC');
 29     v_ddl := 'ALTER PACKAGE etl_pkg COMPILE';
 30
 31  END;
 32  /
Procedure created.

The following table shows the timings for both methods at 50, 100 and 150 columns on 10.2, 10.1 and 9.2 databases. All timings are from a second run of each procedure and, where relevant, 10g PL/SQL optimisation is disabled.

Columns 10.2 Row (s) 10.2 Bulk (s) 10.1 Row (s) 10.1 Bulk (s) 9.2 Row (s) 9.2 Bulk (s)

50

9.07

4.37

9.90

8.65

7.07

8.00

100

14.23

12.98

15.79

18.42

13.05

17.01

150 14.90 19.04 24.82 30.86 18.06 29.01

The point at which the bulk method becomes slower than row-by-row processing is highlighted for each database version and we can see that it has improved with each release (the patterns are the same with repeated runs of the test). In 9i, the pipelined function performance degrades with records of around 50 attributes or fewer, while in 10g Release 2 (and since confirmed in 11g Release 1) it is with records of somewhere around 150 attributes.

To dig deeper than the "wall-clock" allows, we will compare the row-by-row and bulk pipelined function inserts using a variation on Tom Kyte's RUNSTATS utility (available here). In the following example, we compare the loads at 150 columns on a 10.2 database to see if we can determine the cause of the performance degradation.

SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

SQL> exec etl_pkg.row_by_row;

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

SQL> exec etl_pkg.bulk_from_pipeline;

PL/SQL procedure successfully completed.

SQL> exec runstats_pkg.rs_stop(5000);

Run1 ran in 2111 hsecs

Run2 ran in 2243 hsecs

Run1 ran in 94.12% of the time

Name                                  Run1        Run2        Diff

LATCH.row cache objects             17,417       9,661      -7,756

STAT..IMU Redo allocation size           0       8,672       8,672

LATCH.object queue header oper      74,739      65,960      -8,779

LATCH.session allocation            26,480      17,555      -8,925

LATCH.simulator hash latch          32,361      21,450     -10,911

LATCH.simulator lru latch           29,913      18,400     -11,513

LATCH.cache buffers lru chain       63,670      51,941     -11,729

STAT..table scan blocks gotten      50,176      12,729     -37,447

STAT..consistent gets               66,133      28,152     -37,981

STAT..consistent gets from cac      66,133      28,152     -37,981

STAT..no work - consistent rea      52,058      14,005     -38,053

STAT..execute count                 50,870         505     -50,365

LATCH.shared pool                   57,480       5,370     -52,110

STAT..redo entries                 114,550      52,821     -61,729

STAT..db block gets                143,282      81,419     -61,863

STAT..db block gets from cache     143,282      81,419     -61,863

STAT..db block changes             165,904      66,971     -98,933

STAT..session logical reads        209,415     109,571     -99,844

LATCH.library cache pin            104,611       3,265    -101,346

LATCH.library cache                108,966       6,403    -102,563

STAT..recursive calls              111,548       7,402    -104,146

STAT..table scan rows gotten       204,973      55,321    -149,652

STAT..physical read bytes          319,488     548,864     229,376

STAT..physical read total byte     319,488     548,864     229,376

STAT..session uga memory max             0     261,856     261,856

STAT..session pga memory          -327,680      65,536     393,216

LATCH.cache buffers chains         755,709     360,644    -395,065

STAT..undo change vector size    3,181,600   1,004,352  -2,177,248

STAT..redo size                122,201,564  88,791,160 -33,410,404

Run1 latches total versus run2 -- difference and pct

Run1        Run2        Diff        Pct

1,350,719     635,670    -715,049    212.49%

PL/SQL procedure successfully completed.

We can see that the row-by-row method uses far more resources than the bulk pipelined function method and yet it is still quicker. Many of the resource differences we see above are directly attributable to row-by-row insert inefficiencies (such as the volume of redo and the additional latching). Note that the "table scan rows gotten" statistic is misleading: we haven't accidentally loaded TGT with 204,000 rows in the row-based version (these additional rows must be related to recursive SQL).

Reversing the running order of the test makes no difference to the overall patterns we've seen in timings and resource statistics. As a final investigation, we will run the load under SQL trace as follows.

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE;

PL/SQL procedure successfully completed.

SQL> exec etl_pkg.row_by_row;

PL/SQL procedure successfully completed.

SQL> exec etl_pkg.bulk_from_pipeline;

PL/SQL procedure successfully completed.

SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE;

PL/SQL procedure successfully completed.

If we run the trace file through TKProf, we see the following statements for the row-by-row load.

********************************************************************************

SELECT * 

FROM

 SRC

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch    50001      1.79       1.67          0      50011          0       50000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    50002      1.79       1.68          0      50011          0       50000

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 54     (recursive depth: 1)

********************************************************************************

INSERT INTO TGT 

VALUES

 (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,

  :B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,

  :B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,

  :B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 ,:B48 ,:B49 ,:B50 ,

  :B51 ,:B52 ,:B53 ,:B54 ,:B55 ,:B56 ,:B57 ,:B58 ,:B59 ,:B60 ,:B61 ,:B62 ,

  :B63 ,:B64 ,:B65 ,:B66 ,:B67 ,:B68 ,:B69 ,:B70 ,:B71 ,:B72 ,:B73 ,:B74 ,

  :B75 ,:B76 ,:B77 ,:B78 ,:B79 ,:B80 ,:B81 ,:B82 ,:B83 ,:B84 ,:B85 ,:B86 ,

  :B87 ,:B88 ,:B89 ,:B90 ,:B91 ,:B92 ,:B93 ,:B94 ,:B95 ,:B96 ,:B97 ,:B98 ,

  :B99 ,:B100 ,:B101 ,:B102 ,:B103 ,:B104 ,:B105 ,:B106 ,:B107 ,:B108 ,:B109 ,

  :B110 ,:B111 ,:B112 ,:B113 ,:B114 ,:B115 ,:B116 ,:B117 ,:B118 ,:B119 ,:B120 

  ,:B121 ,:B122 ,:B123 ,:B124 ,:B125 ,:B126 ,:B127 ,:B128 ,:B129 ,:B130 ,

  :B131 ,:B132 ,:B133 ,:B134 ,:B135 ,:B136 ,:B137 ,:B138 ,:B139 ,:B140 ,:B141 

  ,:B142 ,:B143 ,:B144 ,:B145 ,:B146 ,:B147 ,:B148 ,:B149 ,:B150 ) 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute  50000      3.39       7.45          2      11217     145770       50000

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    50000      3.39       7.45          2      11217     145770       50000

Misses in library cache during parse: 0

Misses in library cache during execute: 2

Optimizer mode: ALL_ROWS

Parsing user id: 54     (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                         2        0.02          0.02

  log buffer space                               18        0.45          2.85

  log file switch completion                      7        0.49          1.24

  latch: cache buffers chains                     1        0.00          0.00

  latch: shared pool                              3        0.00          0.00

********************************************************************************

We can see that the individual user-SQL components of the row-by-row load have not really accounted for much time, nor did they spend much time waiting for resources. If we look at the bulk pipelined load SQL, we see the following statistics.

********************************************************************************

SELECT "A3"."C1" "C1","A3"."C2" "C2","A3"."C3" "C3","A3"."C4" "C4","A3"."C5" 

  "C5","A3"."C6" "C6","A3"."C7" "C7","A3"."C8" "C8","A3"."C9" "C9","A3"."C10" 

  "C10","A3"."C11" "C11","A3"."C12" "C12","A3"."C13" "C13","A3"."C14" "C14",

  "A3"."C15" "C15","A3"."C16" "C16","A3"."C17" "C17","A3"."C18" "C18",

  

  ...<snip>...

  

  "A3"."C143" "C143","A3"."C144" "C144","A3"."C145" "C145","A3"."C146" "C146",

  "A3"."C147" "C147","A3"."C148" "C148","A3"."C149" "C149","A3"."C150" "C150" 

FROM

 "SRC" "A3"

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      501      1.71       1.80          0      12511          0       50000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total      503      1.71       1.80          0      12511          0       50000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54     (recursive depth: 2)

********************************************************************************

INSERT INTO TGT SELECT * FROM TABLE( ETL_PKG.PIPELINED_FX( CURSOR( SELECT * 

  FROM SRC )))

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        0      0.00       0.00          0          0          0           0

Execute      1     16.98      17.50          1      12024      78652       50000

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        1     16.98      17.50          1      12024      78652       50000

Misses in library cache during parse: 0

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 54     (recursive depth: 1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                         1        0.00          0.00

  log file switch completion                      5        0.21          0.29

  log buffer space                                2        0.13          0.18

********************************************************************************

The source cursor for the bulk load is faster still, but the bulk insert statement has accounted for the majority of the runtime. Again the waits are trivial, but we'd need to be able to investigate lower still to find out what, if anything, is causing Oracle's reduced performance at this level. It has been suggested that it is Oracle's handling of object types (that underpins pipelined functions) that might contribute to this performance issue but we cannot prove this from the above data.

issue 3: versioned objects: ora-04043

The implementation of all pipelined functions is supported by object and collection types. Oracle provides three ways of defining these types, but most developers will either create their own object and collection types explicitly or rely on "versioned types" that Oracle generates from PL/SQL packaged record and collection declarations. There is an interesting bug with versioned types that is worthy of note in this article.

To demonstrate the issue, we will create a pipelined function that relies on versioned types for its implementation. To do this, we simply create a package with a global record type, a collection type based on this record type and the pipelined function itself, as follows.

SQL> CREATE PACKAGE etl_pkg AS

  2

  3     TYPE plsql_record_type IS RECORD

  4     ( a1 VARCHAR2(30)

  5     , a2 VARCHAR2(30)

  6     , a3 VARCHAR2(30)

  7     );

  8

  9     TYPE plsql_nested_table_type

 10        IS TABLE OF plsql_record_type;

 11

 12     FUNCTION parallel_fx

 13        RETURN plsql_nested_table_type

 14        PIPELINED;

 15

 16  END etl_pkg;

 17  /

Package created.

Remember that pipelined functions require SQL types to be able to pipe collections of data to the consumer. Because we have only declared PL/SQL types to support our pipelined function, Oracle creates the SQL types on our behalf. We can see these in the dictionary using the following type of query.

SQL> WITH t AS (

  2     SELECT object_id AS o

  3     FROM   user_objects

  4     WHERE  object_name = 'ETL_PKG'

  5     AND    object_type = 'PACKAGE'

  6     )

  7  SELECT type_name, typecode

  8  FROM   user_types

  9  WHERE  type_name LIKE 'SYS%'

 10  AND    type_name LIKE '%' || (SELECT o FROM t) || '%';

 

TYPE_NAME                      TYPECODE

------------------------------ ------------------------------

SYS_PLSQL_53640_33_1           COLLECTION

SYS_PLSQL_53640_9_1            OBJECT

SYS_PLSQL_53640_DUMMY_1        COLLECTION

3 rows selected.

The type names are system-generated but contain the object ID of the package that the types were created for (as an aside, if we base our PL/SQL types on a table%ROWTYPE, the corresponding object ID will be that of the table).

The issue we will see arises when we create synonyms for the versioned types. We are unlikely to do this knowingly (we have no need to access these types; access to the package is all that is required to execute the pipelined function). Nevertheless, some environments automatically generate synonyms for all objects created for their applications, so we will replicate something similar. In the following example, we will imagine that the supplied SH schema is our application user, with SCOTT being the application owner. SCOTT creates synonyms in the SH schema for all of its objects, as follows.

SQL> DECLARE

  2     v_ddl VARCHAR2(1024) := 'CREATE SYNONYM sh."%s" FOR "%s"';

  3  BEGIN

  4     FOR r IN (SELECT object_name FROM user_objects) LOOP

  5        EXECUTE IMMEDIATE

  6           REPLACE(v_ddl, '%s', r.object_name);

  7     END LOOP;

  8  END;

  9  /

PL/SQL procedure successfully completed.

Versioned types are so-named for a reason. If we recompile the package specification and repeat our query over USER_TYPES, we see that the trailing integer in the type name increases, as follows.

SQL> ALTER PACKAGE etl_pkg COMPILE;

Package altered.

SQL> WITH t AS (

  2     SELECT object_id AS o

  3     FROM   user_objects

  4     WHERE  object_name = 'ETL_PKG'

  5     AND    object_type = 'PACKAGE'

  6     )

  7  SELECT type_name, typecode

  8  FROM   user_types

  9  WHERE  type_name LIKE 'SYS%'

 10  AND    type_name LIKE '%' || (SELECT o FROM t) || '%';

TYPE_NAME

------------------------------

SYS_PLSQL_53640_33_2

SYS_PLSQL_53640_9_2

SYS_PLSQL_53640_DUMMY_2

3 rows selected.

Our supporting types have been re-generated by Oracle and a new "version" has been created. If we use CREATE OR REPLACE, the "version numbers" are reset to 1. However, because we have already recompiled this package, it appears as though we have no more chances to change its state, as follows.

SQL> ALTER PACKAGE etl_pkg COMPILE;

ALTER PACKAGE etl_pkg COMPILE

*

ERROR at line 1:

ORA-04043: object SYS_PLSQL_53640_33_1 does not exist

We have hit bug number 3744836. We receive the same message if we try to drop the package, as follows.

SQL> DROP PACKAGE etl_pkg;

DROP PACKAGE etl_pkg

*

ERROR at line 1:

ORA-04043: object SYS_PLSQL_53640_33_1 does not exist

Finally, if we try to replace the package specification, we receive the following compilation errors.

SQL> CREATE OR REPLACE PACKAGE etl_pkg AS

  2     c INTEGER;

  3  END etl_pkg;

  4  /

Warning: Package created with compilation errors.

SQL> sho err

Errors for PACKAGE ETL_PKG:

LINE/COL ERROR

-------- -----------------------------------------------------------------

0/0      ORA-04043: object SYS_PLSQL_53640_33_1 does not exist

Note the version number of the type that we are being told does not exist. We know from above that our system-generated types have been incremented to version 2. We can also verify that we have no version 1 types as follows.

SQL> WITH t AS (

  2     SELECT object_id AS o

  3     FROM   user_objects

  4     WHERE  object_name = 'ETL_PKG'

  5     AND    object_type = 'PACKAGE'

  6     )

  7  SELECT type_name

  8  FROM   user_types

  9  WHERE  type_name LIKE 'SYS%'

 10  AND    type_name LIKE 'SYS_PLSQL_' || (SELECT o FROM t) || '%_1';

no rows selected

Of course, we created some synonyms earlier in the application user's schema (SH) and these are at version 1. But these are in another schema and we are trying to drop our own objects. Fortunately, we are able to see the root of this problem quite easily in a SQL trace file, so we will enable SQL trace and attempt to drop the ETL_PKG package, as follows.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

SQL> DROP PACKAGE etl_pkg;

DROP PACKAGE etl_pkg

*

ERROR at line 1:

ORA-04043: object SYS_PLSQL_53640_33_1 does not exist

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

The trace file leads us to the cause of this problem. Before Oracle can drop, recompile or replace a package with versioned types, it needs to identify the associated types and drop these first. In 10.2, Oracle uses the following SQL statement to identify the types. Each type is then dropped in turn.

=====================

PARSING IN CURSOR #25 len=109 dep=1 uid=0 oct=3 lid=0 tim=78367384109 hv=2962406971 ad='1d176f3c'

select UNIQUE name from obj$ where name like 'SYS_PLSQL@_53640@_%' escape '@'   and type# != 10 order by name

END OF STMT

Note that there is no schema reference in this query. We can run this query directly, as follows.

SQL> SELECT UNIQUE name

  2  FROM   obj$

  3  WHERE  name LIKE 'SYS_PLSQL@_53640@_%' ESCAPE '@'

  4  AND    type# != 10

  5  ORDER  BY

  6         name;

NAME

------------------------------

SYS_PLSQL_53640_33_1

SYS_PLSQL_53640_33_2

SYS_PLSQL_53640_9_1

SYS_PLSQL_53640_9_2

SYS_PLSQL_53640_DUMMY_1

SYS_PLSQL_53640_DUMMY_2

6 rows selected.

The query returns the synonyms before our own schema's types! This means that Oracle will try to drop the version 1 synonym first, which of course doesn't exist as a type in our schema. To resolve this issue on 10.2, we can simply drop the redundant synonyms, as follows.

SQL> BEGIN

  2     FOR r IN (SELECT synonym_name

  3               FROM   dba_synonyms

  4               WHERE  owner = 'SH'

  5               AND    synonym_name LIKE 'SYS_PLSQL%')

  6     LOOP

  7        EXECUTE IMMEDIATE

  8           'DROP SYNONYM sh.' || r.synonym_name;

  9     END LOOP;

 10  END;

 11  /

PL/SQL procedure successfully completed.

We can now test that Oracle's recursive SQL statement returns the correct versioned type details, as follows.

SQL> SELECT UNIQUE name

  2  FROM   obj$

  3  WHERE  name LIKE 'SYS_PLSQL@_53640@_%' ESCAPE '@'

  4  AND    type# != 10

  5  ORDER  BY

  6         name;

NAME

------------------------------

SYS_PLSQL_53640_33_2

SYS_PLSQL_53640_9_2

SYS_PLSQL_53640_DUMMY_2

3 rows selected.

We should now be able to drop the package.

SQL> DROP PACKAGE etl_pkg;

Package dropped.

a special note for 11g release 1

Note that this bug is fixed in 11g. Oracle has changed its versioned type naming rules to resolve the issue altogether. In 11g, regardless of whether we recompile or replace a package, Oracle retains the corresponding versioned type names (in other words, their number suffix doesn't increment). Hence, Oracle's recursive query against OBJ$ to determine the corresponding type names will always return the correct values, enabling the versioned types to be dropped.

a special note for 9i and 10g release 1

Prior to 10g Release 2, Oracle used a slightly different recursive statement to identify versioned types belonging to a package. The following is taken from a 10.1 trace file.

=====================

PARSING IN CURSOR #29 len=78 dep=1 uid=0 oct=3 lid=0 tim=79199728262 hv=1397641108 ad='21e1cf10'

select UNIQUE name from obj$ where name like 'SYS_PLSQL_53766_%' order by name

END OF STMT

The main difference with this earlier version of the recursive SQL is that there is no restriction on the OBJ$.TYPE# column. On a 10.1 database with the same example code as above, this query returns the following values both before and after the synonyms are dropped.

SQL> SELECT UNIQUE name

  2  FROM   obj$

  3  WHERE  name LIKE 'SYS_PLSQL_53766_%'

  4  ORDER  BY

  5         name;

NAME

------------------------------

SYS_PLSQL_53766_33_1

SYS_PLSQL_53766_33_2

SYS_PLSQL_53766_9_1

SYS_PLSQL_53766_9_2

SYS_PLSQL_53766_DUMMY_1

SYS_PLSQL_53766_DUMMY_2

6 rows selected.

This is critical because when we drop synonyms, Oracle updates the corresponding OBJ$ record to TYPE#=10, rather than delete the record from the dictionary. We can see this below by querying OBJ$ after the synonyms are dropped.

SQL> SELECT name, type#

  2  FROM   obj$

  3  WHERE  name LIKE 'SYS_PLSQL_53766_%'

  4  ORDER  BY

  5         name;

NAME                                TYPE#

------------------------------ ----------

SYS_PLSQL_53766_33_1                   10

SYS_PLSQL_53766_33_2                   13

SYS_PLSQL_53766_9_1                    10

SYS_PLSQL_53766_9_2                    13

SYS_PLSQL_53766_DUMMY_1                10

SYS_PLSQL_53766_DUMMY_2                13

6 rows selected.

This means, of course, that we cannot drop, recompile or recreate the package until the obsolete synonym entries (TYPE#=10) are removed from OBJ$. This cleanup will be performed by SMON after a database bounce (or sometimes after an indeterminate period of time), after which point the package and associated versioned types can be dropped.

 

参考至:http://www.oracle-developer.net/display.php?id=423

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

0
2
分享到:
评论

相关推荐

    OpenGL Fixed Function Pipeline

    ### OpenGL Fixed Function Pipeline #### 简介 OpenGL(Open Graphics Library)是一种用于渲染2D、3D矢量图形的跨语言、跨平台的应用程序编程接口(API)。它由Khronos Group维护,并且提供了丰富的命令参考文档...

    pipeline ADC的设计指南

    Pipeline ADC设计指南 Pipeline ADC是一种常用的模数转换器架构,它通过将模拟信号分解成多个阶段来实现高精度的数字化。下面我们将详细介绍Pipeline ADC的设计指南。 一、Pipeline ADC的基本原理 Pipeline ADC...

    Android代码-pipeline

    Having Issues? Contact Us Anytime... We're Always Awake. Slack: https://joinslack.pipeline.ai Email: help@pipeline.ai Web: https://support.pipeline.ai YouTube: https://youtube.pipeline.ai Slideshare: ...

    D3D render pipeline

    function pixel processing provided by the stock pipeline. Finally, the pixels generated from the scan conversion process are incorporated into the render target surface by the frame bu®er. Chapter 10...

    Jenkins Pipeline 部署 SpringBoot 应用的教程详解

    Jenkins Pipeline 部署 SpringBoot 应用详解 本篇文章主要介绍了使用 Jenkins Pipeline 部署 SpringBoot 应用的详细教程,从安装依赖包到环境准备、安装 Jenkins 等步骤进行详细的介绍,对读者学习或工作具有一定的...

    gltf-pipeline-3.0.2.zip

    《gltf-pipeline:构建三维图形的利器》 gltf-pipeline 是一个强大的工具集,主要用于处理和优化基于 glTF(GL Transmission Format)的三维模型数据。glTF 是一种开放标准的三维模型格式,旨在高效、轻量地传输和...

    Doris PipeLine的设计文档.pdf

    Doris PipeLine设计文档 Doris PipeLine设计文档是关于Doris执行引擎的设计文档,旨在解决当前Doris执行引擎中存在的一些问题,如无法充分利用多核计算能力、提升查询性能、手动设置并行度等问题。该设计文档提出了...

    高通QCOM camera Pipeline可视化工具 1.4版本

    高通QCOM camera Pipeline可视化工具 1.4版本

    Jenkins高级篇之Pipeline技巧篇-1-小白搭建Pipeline项目开发环境.rar

    本篇重点讲解的是Jenkins Pipeline的高级技巧,旨在帮助初学者,即"小白",快速搭建并掌握Pipeline项目的开发环境。 一、Jenkins Pipeline基础 Jenkins Pipeline是一种定义和执行CI/CD流程的声明式或脚本化方式。...

    jenkins 构建项目之 pipeline基础教程

    jenkins 构建项目之 pipeline基础教程 jenkins Pipeline 是一种基于工作流框架的自动化构建工具,它可以将原本独立运行于单个或者多个节点的任务连接起来,实现单个任务难以完成的复杂流程编排和可视化的工作。...

    Avalon-MM Pipeline Bridge

    Avalon-MM Pipeline Bridge是Qsys系统中一种重要的互联组件,用于优化基于Avalon-MM接口的SoC(System-on-a-Chip)设计中的数据传输性能。Avalon-MM是Altera公司(现已被Intel收购)开发的一种内存映射协议,广泛...

    Fixed function pipeline using vertex programs

    这种变化对于计算机图形学领域来说意义重大,因为它标志着从固定功能管线(Fixed Function Pipeline, FFP)到可编程管线(Programmable Pipeline)的重要转变。 #### 顶点处理器的优势与挑战 顶点处理器相比于硬编码...

    pipeline ADC

    ### Pipeline ADC概述与关键技术 #### 一、Pipeline ADC的基本概念 Pipeline ADC(流水线模数转换器)是一种高性能、高速度的模数转换技术,在现代通信系统、雷达系统及信号处理领域发挥着至关重要的作用。它能够...

    Netty测试代码,尤其对于Pipeline顺序

    在本文中,我们将深入探讨Netty中的关键概念,尤其是Pipeline(管道)的顺序及其工作原理。 首先,让我们理解Netty的核心组件:Server和Client。在Netty中,服务器端通常被称为BossGroup和WorkerGroup,BossGroup...

    构建机器学习Pipeline

    构建机器学习Pipeline,也就是构建机器学习流程线,是数据科学和软件工程领域中的一个重要议题。在现实世界中,数据科学家通常在一个为统计和机器学习量身定做的开发环境中工作,例如使用Python等工具,在一个“沙盒...

    jenkins流水线任务pipeline使用JaCoCo

    https://stackoverflow.com/questions/41893846/jacoco-coverage-in-jenkins-pipeline jenkins官网介绍:https://jenkins.io/doc/pipeline/steps/jacoco/ 流水线语法的片段生成器可以选择jacoco,设置jacoco jacoco...

    PyPI 官网下载 | bamboo-pipeline-3.6.3.tar.gz

    标题中的"PyPI 官网下载 | bamboo-pipeline-3.6.3.tar.gz"指出这是一个从Python Package Index(PyPI)官方站点获取的软件包,名为“bamboo-pipeline”。这个软件包的版本是3.6.3,并且是以tar.gz格式压缩的。在...

    pipeline studio快速入门

    pipeline studio快速入门,确实容易上手

    jenkins-ci-pipeline-源码.rar

    Jenkins CI Pipeline 是一个强大的自动化工具,用于构建、测试和部署软件。Pipeline 允许开发者定义持续集成(CI)和持续部署(CD)的工作流程,这些工作流程是声明式的,可编写为版本化的代码,提高了可重复性和可...

    redispipeLine

    Redis Pipeline 是 Redis 数据库操作中的一个高效特性,它允许客户端一次性发送多个命令到服务器,而无需等待每个命令的响应。这种技术显著提高了批量处理和高并发环境下的性能,因为减少了网络通信的开销。 首先,...

Global site tag (gtag.js) - Google Analytics