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
相关推荐
### OpenGL Fixed Function Pipeline #### 简介 OpenGL（Open Graphics Library）是一种用于渲染2D、3D矢量图形的跨语言、跨平台的应用程序编程接口（API）。它由Khronos Group维护，并且提供了丰富的命令参考文档...
Pipeline ADC设计指南 Pipeline ADC是一种常用的模数转换器架构，它通过将模拟信号分解成多个阶段来实现高精度的数字化。下面我们将详细介绍Pipeline ADC的设计指南。 一、Pipeline ADC的基本原理 Pipeline ADC...
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: ...
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 等步骤进行详细的介绍，对读者学习或工作具有一定的...
《gltf-pipeline：构建三维图形的利器》 gltf-pipeline 是一个强大的工具集，主要用于处理和优化基于 glTF（GL Transmission Format）的三维模型数据。glTF 是一种开放标准的三维模型格式，旨在高效、轻量地传输和...
Doris PipeLine设计文档 Doris PipeLine设计文档是关于Doris执行引擎的设计文档，旨在解决当前Doris执行引擎中存在的一些问题，如无法充分利用多核计算能力、提升查询性能、手动设置并行度等问题。该设计文档提出了...
高通QCOM camera Pipeline可视化工具 1.4版本
本篇重点讲解的是Jenkins Pipeline的高级技巧，旨在帮助初学者，即"小白"，快速搭建并掌握Pipeline项目的开发环境。 一、Jenkins Pipeline基础 Jenkins Pipeline是一种定义和执行CI/CD流程的声明式或脚本化方式。...
jenkins 构建项目之 pipeline基础教程 jenkins Pipeline 是一种基于工作流框架的自动化构建工具，它可以将原本独立运行于单个或者多个节点的任务连接起来，实现单个任务难以完成的复杂流程编排和可视化的工作。...
Avalon-MM Pipeline Bridge是Qsys系统中一种重要的互联组件，用于优化基于Avalon-MM接口的SoC（System-on-a-Chip）设计中的数据传输性能。Avalon-MM是Altera公司（现已被Intel收购）开发的一种内存映射协议，广泛...
这种变化对于计算机图形学领域来说意义重大，因为它标志着从固定功能管线(Fixed Function Pipeline, FFP)到可编程管线(Programmable Pipeline)的重要转变。 #### 顶点处理器的优势与挑战 顶点处理器相比于硬编码...
### Pipeline ADC概述与关键技术 #### 一、Pipeline ADC的基本概念 Pipeline ADC（流水线模数转换器）是一种高性能、高速度的模数转换技术，在现代通信系统、雷达系统及信号处理领域发挥着至关重要的作用。它能够...
在本文中，我们将深入探讨Netty中的关键概念，尤其是Pipeline（管道）的顺序及其工作原理。 首先，让我们理解Netty的核心组件：Server和Client。在Netty中，服务器端通常被称为BossGroup和WorkerGroup，BossGroup...
构建机器学习Pipeline，也就是构建机器学习流程线，是数据科学和软件工程领域中的一个重要议题。在现实世界中，数据科学家通常在一个为统计和机器学习量身定做的开发环境中工作，例如使用Python等工具，在一个“沙盒...
标题中的"PyPI 官网下载 | bamboo-pipeline-3.6.3.tar.gz"指出这是一个从Python Package Index（PyPI）官方站点获取的软件包，名为“bamboo-pipeline”。这个软件包的版本是3.6.3，并且是以tar.gz格式压缩的。在...
pipeline studio快速入门，确实容易上手
Jenkins CI Pipeline 是一个强大的自动化工具，用于构建、测试和部署软件。Pipeline 允许开发者定义持续集成（CI）和持续部署（CD）的工作流程，这些工作流程是声明式的，可编写为版本化的代码，提高了可重复性和可...
Redis Pipeline 是 Redis 数据库操作中的一个高效特性，它允许客户端一次性发送多个命令到服务器，而无需等待每个命令的响应。这种技术显著提高了批量处理和高并发环境下的性能，因为减少了网络通信的开销。 首先，...
在Jenkins自动化构建和部署的过程中，Pipeline作为一种强大的脚本化工具，可以让我们更灵活地管理持续集成和持续交付流程。本资料重点讲解了在Pipeline中如何通过代码串联多个job的执行，这对于构建复杂的CI/CD流程...