Example of How To Use a Pipelined SQL Function



ALTER SESSION ENABLE PARALLEL DML;
Session altered.


DROP TABLE metrics;
Table dropped.


DROP SEQUENCE metrics_seq;
Sequence dropped.


DROP TYPE tbl_metrics_type;
Type dropped.


DROP TYPE rec_metrics_type;
Type dropped.


DROP DIMENSION metrics_dim;
Dimension dropped.


CREATE SEQUENCE metrics_seq START WITH 1 INCREMENT BY 1;
Sequence created.


CREATE TABLE metrics
(metric_id NUMBER NOT NULL CONSTRAINT metrics_pk PRIMARY KEY NOVALIDATE,
class_id NUMBER NOT NULL,
class_name VARCHAR2(120) NOT NULL,
stat_id NUMBER NOT NULL,
stat_name VARCHAR2(120) NOT NULL);
Table created.


ALTER TABLE metrics ADD CONSTRAINT metrics_uk UNIQUE (class_id,class_name,stat_id,stat_name) VALIDATE;
Table altered.


CREATE OR REPLACE TYPE rec_metrics_type IS OBJECT (
metric_id NUMBER,
class_id NUMBER,
class_name VARCHAR2 (120),
stat_id NUMBER,
stat_name VARCHAR2 (120)
);
/
Type created.


CREATE OR REPLACE TYPE tbl_metrics_type IS TABLE OF rec_metrics_type;
/
Type created.


CREATE DIMENSION metrics_dim
LEVEL class_level IS (metrics.class_id)
LEVEL stat_level IS (metrics.stat_id)
HIERARCHY metric_hier (stat_level CHILD OF class_level)
ATTRIBUTE class_level DETERMINES (metrics.class_name)
ATTRIBUTE stat_level DETERMINES (metrics.stat_name);
Dimension created.


CREATE OR REPLACE VIEW metrics_vw
AS
SELECT /*+ PARALLEL (wss, 2) */ s.CLASS class_id,
DECODE (s.CLASS,
1, 'User',
2, 'Redo',
4, 'Enqueue',
8, 'Cache',
16, 'OS',
32, 'Parallelism',
40, 'Real Application Clusters',
64, 'SQL',
72, 'Internal Debugging',
128, 'Debug'
) class_name,
wss.stat_id, s.NAME
FROM SYS.wrh$_service_stat wss INNER JOIN v$sysstat s
ON (wss.stat_id = s.stat_id)
ORDER BY s.CLASS;
View created.


CREATE OR REPLACE PACKAGE load_dim_pkg
AS
TYPE metrics_cur IS REF CURSOR RETURN metrics_vw%ROWTYPE;

FUNCTION get_data (p_cur IN load_dim_pkg.metrics_cur) RETURN tbl_metrics_type
PARALLEL_ENABLE (PARTITION p_cur BY ANY)
PIPELINED;
END;
/
Package created.


SHOW ERRORS;
No errors.


CREATE OR REPLACE PACKAGE BODY load_dim_pkg
AS
FUNCTION get_data (p_cur IN load_dim_pkg.metrics_cur) RETURN tbl_metrics_type
PARALLEL_ENABLE (PARTITION p_cur BY ANY)
PIPELINED IS
rec_in rec_metrics_type := rec_metrics_type (NULL, NULL, NULL, NULL, NULL);
rec_out rec_metrics_type := rec_metrics_type (NULL, NULL, NULL, NULL, NULL);
BEGIN
LOOP
FETCH p_cur INTO rec_in.class_id, rec_in.class_name, rec_in.stat_id,rec_in.stat_name;

EXIT WHEN p_cur%NOTFOUND;
--Transformations may be performed here
rec_out.metric_id := NULL;
rec_out.class_id := rec_in.class_id;
rec_out.class_name := INITCAP (rec_in.class_name);
rec_out.stat_id := rec_in.stat_id;
rec_out.stat_name := INITCAP (rec_in.stat_name);
PIPE ROW (rec_out);
END LOOP;
RETURN;
END;
END;
/
Package body created.


SHOW ERRORS;
No errors.


SET autotrace ON;

MERGE INTO metrics m USING
(SELECT *
FROM TABLE (load_dim_pkg.get_data (CURSOR (SELECT DISTINCT *
FROM metrics_vw)))) s1
ON (m.stat_id = s1.stat_id)
WHEN MATCHED THEN
UPDATE SET m.stat_name = s1.stat_name, m.class_id = s1.class_id, m.class_name= s1.class_name
WHEN NOT MATCHED THEN
INSERT VALUES (metrics_seq.nextval, s1.class_id , s1.class_name , s1.stat_id, s1.stat_name);
26 rows merged.

Execution Plan
---------------------------------------------------------- 

Plan hash value: 3180087982                                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                        
| Id  | Operation                                 | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                        
|   0 | MERGE STATEMENT                           |                   |  8168 |  3589K|       |  2249   (1)| 00:00:27 |       |       |        |      |            |                                                                                                                                        
|   1 |  MERGE                                    | METRICS           |       |       |       |            |          |       |       |        |      |            |                                                                                                                                        
|   2 |   VIEW                                    |                   |       |       |       |            |          |       |       |        |      |            |                                                                                                                                        
|   3 |    SEQUENCE                               | METRICS_SEQ       |       |       |       |            |          |       |       |        |      |            |                                                                                                                                        
|   4 |     PX COORDINATOR                        |                   |       |       |       |            |          |       |       |        |      |            |                                                                                                                                        
|   5 |      PX SEND QC (ORDER)                   | :TQ10002          |  8168 |  2696K|       |  2249   (1)| 00:00:27 |       |       |  Q1,02 | P->S | QC (ORDER) |                                                                                                                                        
|   6 |       NESTED LOOPS OUTER                  |                   |  8168 |  2696K|       |  2249   (1)| 00:00:27 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|   7 |        VIEW                               |                   |  8168 |  1300K|       |    29   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|   8 |         VIEW                              |                   |  8168 |  6620K|       |    29   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|   9 |          COLLECTION ITERATOR PICKLER FETCH| GET_DATA          |       |       |       |            |          |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|  10 |           HASH UNIQUE                     |                   |    28 |  1904 |       |    17  (36)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|  11 |            VIEW                           | METRICS_VW        | 42000 |  2789K|       |    15  (27)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|  12 |             SORT ORDER BY                 |                   | 42000 |  3527K|  8312K|    15  (27)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|  13 |              PX RECEIVE                   |                   | 42000 |  3527K|       |    12   (9)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|  14 |               PX SEND RANGE               | :TQ10001          | 42000 |  3527K|       |    12   (9)| 00:00:01 |       |       |  Q1,01 | P->P | RANGE      |                                                                                                                                        
|  15 |                VIEW                       |                   | 42000 |  3527K|       |    12   (9)| 00:00:01 |       |       |  Q1,01 | PCWP |            |                                                                                                                                        
|* 16 |                 HASH JOIN                 |                   | 42000 |  2748K|       |    12   (9)| 00:00:01 |       |       |  Q1,01 | PCWP |            |                                                                                                                                        
|  17 |                  BUFFER SORT              |                   |       |       |       |            |          |       |       |  Q1,01 | PCWC |            |                                                                                                                                        
|  18 |                   PX RECEIVE              |                   |   100 |  6000 |       |     0   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |                                                                                                                                        
|  19 |                    PX SEND BROADCAST      | :TQ10000          |   100 |  6000 |       |     0   (0)| 00:00:01 |       |       |        | S->P | BROADCAST  |                                                                                                                                        
|  20 |                     VIEW                  | V$SYSSTAT         |   100 |  6000 |       |     0   (0)| 00:00:01 |       |       |        |      |            |                                                                                                                                        
|* 21 |                      VIEW                 | GV$SYSSTAT        |   100 |  7300 |       |     0   (0)| 00:00:01 |       |       |        |      |            |                                                                                                                                        
|  22 |                       FIXED TABLE FULL    | X$KSUSGSTA        |   100 |  7300 |       |     0   (0)| 00:00:01 |       |       |        |      |            |                                                                                                                                        
|  23 |                  PX BLOCK ITERATOR        |                   | 11760 | 82320 |       |    11   (0)| 00:00:01 |     1 |     4 |  Q1,01 | PCWC |            |                                                                                                                                        
|  24 |                   TABLE ACCESS FULL       | WRH$_SERVICE_STAT | 11760 | 82320 |       |    11   (0)| 00:00:01 |     1 |     4 |  Q1,01 | PCWP |            |                                                                                                                                        
|  25 |        VIEW                               |                   |     1 |   175 |       |     0   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
|* 26 |         TABLE ACCESS FULL                 | METRICS           |     1 |   175 |       |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                        
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
  16 - access("WSS"."STAT_ID"="S"."STAT_ID")                                                                                                                                                                                                                                                                
  21 - filter("INST_ID"=USERENV('INSTANCE'))                                                                                                                                                                                                                                                                
  26 - filter("M"."STAT_ID"="S1"."STAT_ID")                                                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - dynamic sampling used for this statement

Statistics
---------------------------------------------------------- 

        670  recursive calls                                                                                                                                                                                                                                                                                
        147  db block gets                                                                                                                                                                                                                                                                                  
       1709  consistent gets                                                                                                                                                                                                                                                                                
          0  physical reads                                                                                                                                                                                                                                                                                 
      23252  redo size                                                                                                                                                                                                                                                                                      
        552  bytes sent via SQL*Net to client                                                                                                                                                                                                                                                               
        958  bytes received via SQL*Net from client                                                                                                                                                                                                                                                         
          4  SQL*Net roundtrips to/from client                                                                                                                                                                                                                                                              
         24  sorts (memory)                                                                                                                                                                                                                                                                                 
          0  sorts (disk)
26 rows processed


SET autotrace OFF;

COMMIT;
Commit complete.


SELECT * FROM V$PQ_SESSTAT WHERE STATISTIC IN ('Queries Parallelized','Server Threads'); 


STATISTIC                        LAST_QUERY SESSION_TOTAL                                                                                                                                                                                                                                                   
------------------------------ ------------ -------------                                                                                                                                                                                                                                                   
Queries Parallelized                      0             1                                                                                                                                                                                                                                                   
Server Threads                            0             0

SELECT * FROM METRICS ORDER BY CLASS_NAME; 

   METRIC_ID     CLASS_ID CLASS_NAME                     STAT_ID STAT_NAME                                                                                                                                                                                                                                  
------------ ------------ ------------------------- ------------ ----------------------------------------                                                                                                                                                                                                   
          15            8 Cache                       1190468109 Physical Writes                                                                                                                                                                                                                            
          11            8 Cache                       2263124246 Physical Reads                                                                                                                                                                                                                             
           9            8 Cache                        916801489 Db Block Changes                                                                                                                                                                                                                           
          23           40 Real Application Clusters   2877738702 Gc Cr Blocks Received                                                                                                                                                                                                                      
          19           40 Real Application Clusters    326482564 Gc Current Blocks Received                                                                                                                                                                                                                 
          17           40 Real Application Clusters   1388758753 Gc Current Block Receive Time                                                                                                                                                                                                              
          16           40 Real Application Clusters   1759426133 Gc Cr Block Receive Time                                                                                                                                                                                                                   
          13            2 Redo                        1236385760 Redo Size                                                                                                                                                                                                                                  
          25           64 Sql                          798730793 Workarea Executions - Onepass                                                                                                                                                                                                              
          26           64 Sql                         3804491469 Workarea Executions - Multipass                                                                                                                                                                                                            
          24           64 Sql                           63887964 Parse Count (Total)                                                                                                                                                                                                                        
          22           64 Sql                         3211650785 Workarea Executions - Optimal                                                                                                                                                                                                              
          21           64 Sql                         1431595225 Parse Time Elapsed                                                                                                                                                                                                                         
          20           64 Sql                         2453370665 Execute Count                                                                                                                                                                                                                              
          18           64 Sql                         3678609077 Session Cursor Cache Hits                                                                                                                                                                                                                  
           4            1 User                        3649082374 Db Time                                                                                                                                                                                                                                    
           3            1 User                        2666645286 Logons Cumulative                                                                                                                                                                                                                          
           1            1 User                         582481098 User Commits                                                                                                                                                                                                                               
           2            1 User                        3671147913 User Rollbacks                                                                                                                                                                                                                             
           5            1 User                        2432034337 Cluster Wait Time                                                                                                                                                                                                                          
           8            1 User                        3332107451 User I/O Wait Time                                                                                                                                                                                                                         
           6            1 User                        3868577743 Concurrency Wait Time                                                                                                                                                                                                                      
          12            1 User                          85052502 Opened Cursors Cumulative                                                                                                                                                                                                                  
           7            1 User                        1099569955 Application Wait Time                                                                                                                                                                                                                      
          10            1 User                        3143187968 Session Logical Reads                                                                                                                                                                                                                      
          14            1 User                        2882015696 User Calls
26 rows selected.

SPOOL OFF;