Pipeline Functions - PLS-00653

Breaking large chunks into smaller pieces, can run in parallel has got one word in PLSQL/SQL it is Pipeline. 

     The pipeline functions heavily reduces the strain on memory and developers can leverage this functions to convert PLSQL functions into row source operations.

Lets see one simple example with Pipeline Function, 


SQL> 
SQL> create table tbl_pipeline as
  2  select rownum as ACCOUNT_NBR,
  3         'A' as GROUP_IND,
  4         'B' as INCONSISTENT_PRICE
  5  from dual connect by level < 10
  6  /

Table created.

SQL> CREATE or REPLACE PACKAGE pkg_pipeline is
  2  
  3  TYPE t_objects_row IS record(ACCOUNT_NBR  NUMBER ,GROUP_IND CHAR(1),INCONSISTENT_PRICE VARCHAR2(1));
  4  TYPE t_objects_tab IS TABLE OF t_objects_row;
  5  FUNCTION  F1 return t_objects_tab PIPELINED;
  6  end pkg_pipeline;
  7  /

Package created.

SQL> CREATE or REPLACE PACKAGE BODY pkg_pipeline is
  2  FUNCTION  F1 RETURN t_objects_tab  PIPELINED
  3    IS
  4      l_row      t_objects_row;
  5  
  6  
  7  Cursor C1 is
  8  SELECT
  9   ACCOUNT_NBR,
 10   GROUP_IND,
 11   INCONSISTENT_PRICE
 12  FROM tbl_pipeline
 13  WHERE ROWNUM < 2;
 14  
 15  BEGIN
 16  FOR c1_rec  in 1..10
 17  LOOP
 18     Open C1;
 19     LOOP
 20      FETCH C1 INTO
 21          l_row.ACCOUNT_NBR,
 22          l_row.GROUP_IND,
 23          l_row.INCONSISTENT_PRICE;
 24      EXIT WHEN C1%NOTFOUND;
 25      PIPE ROW (l_row);
 26     END LOOP;
 27     Close C1;
 28  END LOOP;
 29  RETURN;
 30  EXCEPTION
 31    WHEN OTHERS
 32      THEN
 33  
 34          raise_application_error(-20001,sqlerrm);
 35  END F1;
 36  
 37  END pkg_pipeline;
 38  /

Package body created.

SQL> REM - INVOKING THE PIPELINE FUCNTION
SQL> 
SQL> DECLARE
  2    X_TAB pkg_pipeline.t_objects_tab;
  3    Begin
  4    X_TAB  := F1;
  5    end;
  6    /
  X_TAB pkg_pipeline.t_objects_tab;
  *
ERROR at line 2:
ORA-06550: line 1, column 11: 
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope 

Ops whats the issue here, every seems to be right.  Lets try invoking the pipeline function from SQL.

SQL> REM - INVOKING PIPELINE FROM SQL
SQL> 
SQL> SELECT * FROM TABLE(PKG_PIPELINE.F1);

ACCOUNT_NBR G I                                                                 
----------- - -                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 
          1 A B                                                                 

10 rows selected.

    
Bingo, Pipeline functions are meant for SQL. 

            The error PLS-00653 is due to overloading of pipeline function, which is a PLSQL restriction . Therefore do not overload an pipeline function. 

MEMBER OF Vs TABLE OF CAST in Oracle

This articles is to create a Dynamic IN-List and to compare the performance between the options MEMBER OF and TABLE OF CAST.

             The member we say dynamic IN-List, the data to be compared using IN clause is going to be dynamic and would require a temporary storage as a place holder. This temporary storage can be a Global Temporary Table or Nested Array. Let's proceed with the later option, 

Dynamic IN - List with TABLE OF CAST - Creating the required tables.

 

Gathering Stats on the table, with estimate as 100 leading to a stats of actual data set

Creating the SQL Type Array, that is a place holder to hold the run time IN-List data

SQL> select count(*) from tbl_member;

  COUNT(*)                                                                      

----------                                                                      

     33513                           

Array "l_object_tbl" would hold the entire IN-List values and which can be passed as parameters also.

The time taken by TABLE OF CAST is is 58 milli seconds. Lets repeat the same steps using MEMBER OF condition

Dynamic IN - List with MEMBER OF Condition

The time taken by TABLE OF CAST is relatively faster than MEMBER OF condition.  So as to identify the root cause of the issue, have re-executed above set of code with trace enabled. The   execution plan provides all the necessary info we need, 

Execution Plan of the Query with Table Of Cast - From Trace

FETCH #9:c=0,e=169163,p=101,cr=31,cu=0,mis=0,r=9,dep=1,og=1,plh=2623979658,tim=33047507231

STAT #9 id=1 cnt=9 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=31 pr=101 pw=0 time=78573 us)'

STAT #9 id=2 cnt=9 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=22 pr=61 pw=0 time=42121 us cost=49 size=640 card=20)'

STAT #9 id=3 cnt=20 pid=2 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=18 us cost=29 size=40 card=20)'

STAT #9 id=4 cnt=9 pid=2 pos=2 obj=75755 op='INDEX UNIQUE SCAN PK_TBL_MEMBER (cr=22 pr=61 pw=0 time=0 us cost=0 size=0 card=1)'

STAT #9 id=5 cnt=9 pid=1 pos=2 obj=75754 op='TABLE ACCESS BY INDEX ROWID TBL_MEMBER (cr=9 pr=40 pw=0 time=0 us cost=1 size=30 card=1)'

CLOSE #9:c=0,e=2,dep=1,type=3,tim=33047507701

Execution Plan of the Query with MEMBER OF - From Trace

FETCH #8:c=62400,e=136205,p=196,cr=199,cu=0,mis=0,r=9,dep=1,og=1,plh=1345468690,tim=33201283248

STAT #8 id=1 cnt=9 pid=0 pos=1 obj=75754 op='TABLE ACCESS FULL TBL_MEMBER (cr=199 pr=196 pw=0 time=115332 us cost=59 size=50280 card=1676)'

CLOSE #8:c=0,e=1,dep=1,type=3,tim=33201283479

Now this makes things very clear that Query with MEMBER OF is not using the index. Does this mean that MEMBER OF function is hiding the real info from the Optimizer, we see in the coming post.

Oracle Child Cursor – Part 2

What is a Child Cursor ?

            Child Cursor is nothing but the cursor itself. The first one has the number Child_Number = 0 i.,e the Parent and there on the Child_Number gets incremented based on the difference in their metadata.

Lets start with ONE to ONE

Version – Oracle 11.2.0.1.0

 SQL> CREATE TABLE TBL_CC_TEST AS
2  SELECT ROWNUM AS ID,
3   decode(trunc(dbms_random.value(1,4)),
4    1,’A',
5    2,’B',
6    3,’C',
7    ’D’
8   ) AS STATUS,
9   TRUNC(dbms_random.value (1,1000000)) AS ID_VAL
10  from all_objects;

 Table created.

 SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘DTCC’,'TBL_CC_TEST’);

 PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing;
NAME                  TYPE          VALUE
————–       ———      ——–
cursor_sharing       string         EXACT

Data is ready and now a simple query. Remember to use binds, which is the only method to make the query reusable when cursor_sharing is EXACT.

SQL> Variable X number;
SQL> exec :X:=54321;
PL/SQL procedure successfully completed.
SQL> select * from tbl_cc_test where id = :X;
ID            S    ID_VAL
———- -      ———-
54321     C      638375

SQL> col sql_id format A20;
SQL> col sql_text format A40;
SQL> col executions format 99;
SQL> col child_number format 99;
SQL> col version_count format 99;
SQL> col plan_hash_value format 999999999999;
SQL> col hash_value format 99999999999999;
SQL>
SQL> select sql_id, sql_text, executions, child_number, plan_hash_value, hash_value
2  from v$sql where sql_text like ‘select * from tbl_cc_test%’;

SQL_ID                           SQL_TEXT                  EXECUTIONS CHILD_NUMBERPLAN_HASH_VALUE HASH_VALUE
————–        ————————-              —————- ————————– ————————— ——————
2nzy95ddbu6ss  select * from tbl_cc_test              1                                  0                          2643996215                1522342680
where id = :X

Thus for a given SQL Text we have got the parent cursor- with Child_Number = 0. Will check how this child number gets changed for the same SQL Text.

ONE to MANY

Iam going to repeat the same steps as above with a small change.

SQL> Variable X varchar2(10);
SQL> exec :X:=’54320′;
PL/SQL procedure successfully completed.

SQL> select * from tbl_cc_test where id = :X;
ID            S     ID_VAL
———- – ———-
54320    A     341189

SQL> Select Sql_Id, Sql_Text, Executions, Child_Number From V$sql Where Sql_Id =’2nzy95ddbu6ss’

SQL_ID   SQL_TEXT       EXECUTIONS CHILD_NUMBER
————-  —————————————–   ———-   ————
2nzy95ddbu6ss select * from tbl_cc_test where id = :X  1  0
2nzy95ddbu6ss select * from tbl_cc_test where id = :X  1  1

Now for the same SQL Text we have got one more child, indicated as Child_Number =  1. You may think,

• Why a new child cursor is created?
• Why not just reuse the same parent cursor(Child_Number = 0)

To answer these lets take a look at the v$sql_shared_cursor which would let us know the reason for creating a new child cursor.

SQL>select SQL_ID,CHILD_ADDRESS,CHILD_NUMBER, from v$sql_shared_cursor Where Sql_Id =’2nzy95ddbu6ss’;

SQL_ID              ADDRESS  CHILD_ADDRESS CHILD_NUMBER BIND_MISMATCH
————-               ——– ————- ———— ————-
2nzy95ddbu6ss      2D730F78 2D4836FC         0             N
2nzy95ddbu6ss      2D730F78 326F78F8          1             Y

The reason for oracle creating a new child cursor is Bind Mismatch, So

• What is Bind Mismatch?

• What are the reason that could lead to Bind Mismatch ?

When there is a change in Bind Variable definition associated with the SQL it leads to a Bind Mismatch, example like change in the data type of the bind variable(like the above test case) or change in the size of the bind variable(oracle has a range for this bind size)  leads to creation of new child cursor for the same SQL Text.

Lets confirm the Bind Mismatch as the cause of new child cursor by validating the actual data,

SQL> Select * From V$sql_Bind_Capture Where Sql_Id =’2nzy95ddbu6ss’;

SQL_ID    CHILD_ADDRESS   CHILD_NUMBER  NAME   DATATYPE      VALUE_STRING
———— ————-   ————  —–  ——-       ————
2nzy95ddbu6ss   326F78F8       1     :X     VARCHAR2(32)  54320
2nzy95ddbu6ss   2D4836FC      0     :X     NUMBER                54320

The above info clearly indicates that during the second run the bind variable’s data type is changed from Number to Varchar2 thus leading to Bind Mismatch and creation of new Child Cursor for the identical SQL Text.

Oracle Child Cursors – Part1

What does Child_Number=0 mean. Does it mean that there are no child cursors for the given SQL ID?

There has been an often misconception towards the value “0” associated with Child_Number in V$SQL for a cursor.  Before we deep dive into child cursors, let’s get into cursors.

In a cursor / Query‘s life cycle the major components are

1. Query – FULL Text – Case sensitive
2. Literals / Bind Variables
3. Statistics
4. Execution Plan

There is a One-To-Many relationship between Query Text and other components within the SQL Life Cycle i.,e For a given Query (SQL) there can be

1. Range of bind values
2. Fluctuating statistics
3. Changing Environments
4. N number of execution plans

Based on the changing and non changing components of a cursor they are splitted into parent and child cursors, which looks like below

          Thus for every cursor, Oracle internally treats them in a parent child format. It is just the value given as “0” for the initial child cursor.  The parent cursor is a representation of the Hash Value and the child cursor represents the metadata for the SQL. When the metadata associated with the SQL starts changing it leads to a creation of a  new child cursor. Every cursor has one parent and one or more child cursors. Therefore under these circumstances the 1:1 relation b/w parent and child cursor becomes 1:n i,.e as below

Find bind variable values in oracle

How to get bind variable values in oracle ?

To retrieve the value of the bind variables V$SQL_BIND_CAPTURE view has to be queried, But does oracle really provides us the bind variable's value? lets see in detail

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME like ‘%cursor_sharing%’;

NAME VALUE
————– ———-
cursor_sharing EXACT

SQL> VARIABLE X NUMBER;
SQL> EXEC :X:=10;

PL/SQL procedure successfully completed.
SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;

ID ID_VAL
———- ———
10 10ORACLE

SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 1 0
D = :X

Lets query V$SQL_BIND_CAPTURE to retrieve the bind variable data

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()

Lets Repeat the same and check what we get,

SQL> EXEC :X:=100;

PL/SQL procedure successfully completed.

SQL> SELECT ID,ID_VAL FROM BV_CAPTURE WHERE ID = :X;

ID ID_VAL
———- ———-
100 100ORACLE

SQL> SELECT SQL_ID,SQL_TEXT,EXECUTIONS AS EXEC,CHILD_NUMBER CHD_NUM FROM V$SQL WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID SQL_TEXT EXEC CHD_NUM
——————– —————————————- —- ——-
4pfw91tshj4yp SELECT ID,ID_VAL FROM BV_CAPTURE WHERE I 2 0
D = :X

and now what does V$SQL_BIND_CAPTURE provide us

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA()
————– —————- —– ————- —————-
4pfw91tshj4yp NUMBER :X 10 ANYDATA()

oh! still sticking to value 10, it has to be 100. The support link Note-444551.1 provides us an work around to get the timestamp values using “anydata.accesstimestamp(value_anydata)” but does anydata.accessnumber help in our case, lets check that too

SQL> SELECT SQL_ID,DATATYPE_STRING,NAME,VALUE_STRING,ANYDATA.ACCESSNUMBER(VALUE_ANYDATA) AS VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = ’4pfw91tshj4yp’;

SQL_ID DATATYPE_STRING NAME VALUE_STRING VALUE_ANYDATA
————- —————– —– ———— ————-
4pfw91tshj4yp NUMBER :X 10 10

Ops! the issue is not with timestamp alone, it is also with Numbers. The run time bind variables are not updated for executions greater than one. Varchar2 also have the same issue. On checking with oracle, Oracle states as below,

"Bind values are not always captured for this view and have referenced to Not Every Bind Values Captured in v$sql_bind_capture (Doc ID 1370816.1) "