Oracle Explain Plan for Query with Collection using Table function

Question

How to generate Plan in Oracle for the query using some Collection in exists clause using table function

e.g.

CREATE TYPE TYP_EMP AS OBJECT(
EMPNO     NUMBER(4),
ENAME     VARCHAR2(10),
JOB       VARCHAR2(9),
MGR       NUMBER(4),
HIREDATE  DATE,
SAL       NUMBER(7,2),
COMM      NUMBER(7,2),
DEPTNO    NUMBER(2));
/

Type created.

CREATE TYPE TYP_EMP_COLL AS TABLE OF TYP_EMP;
/

Type created.

CREATE OR REPLACE VIEW VW_EMP 
OF TYP_EMP
WITH OBJECT IDENTIFIER(EMPNO) AS
SELECT
EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
/

View Created

CREATE TYPE TYP_DEPT AS OBJECT(
DEPTNO NUMBER(2),
DNAME  VARCHAR2(14),
LOC    VARCHAR2(13));
/

Type Created

CREATE TYPE TYP_DEPT_COLL AS TABLE OF TYP_DEPT;
/

Type Created

CREATE OR REPLACE VIEW VW_DEPT
OF TYP_DEPT
WITH OBJECT IDENTIFIER(DEPTNO) AS
SELECT
DEPTNO, DNAME, LOC
FROM DEPT
/

CREATE OR REPLACE PROCEDURE SP_EMPTEST AS
V_EMP TYP_EMP_COLL;
V_DEPT TYP_DEPT_COLL;
BEGIN
SELECT VALUE(V) BULK COLLECT INTO V_EMP FROM VW_EMP V;
SELECT VALUE(VD)
BULK COLLECT INTO V_DEPT FROM VW_DEPT VD
WHERE EXISTS
    (SELECT 1 FROM TABLE(V_EMP) VV WHERE VD.DEPTNO = VV.DEPTNO);
END;
/

---

TKPROF OUTPUT OF TRACE FILE

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

SQL ID: 7c02yjs9q5kqr
Plan Hash: 2616009478
SELECT VALUE(VD) 
FROM
VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(:B1 ) VV WHERE VD.DEPTNO = VV.DEPTNO)


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         19          0           0
Fetch        1      0.00       0.00          0          7          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         26          0           3

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  (mac)   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
  3  HASH JOIN SEMI (cr=7 pr=0 pw=0 time=6 us cost=33 size=32 card=1)
  4   TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=0 us cost=3 size=120 card=4)
 14   COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=0 us cost=29 size=28 card=14)

error during execute of EXPLAIN PLAN statement
ORA-22905: cannot access rows from a non-nested table item

parse error offset: 129

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
asynch descriptor resize                        1        0.00          0.00
********************************************************************************

Answer 1

There are are many different ways to get the explain plan. This isn't intended as merely an exhaustive list. I think there are important differences between the methods, and it's worth having each of them in your tool kit.

1 - Trace the call to the procedure.

As your error demonstrates, it can be tricky to pull SQL out of a PL/SQL block. Even though PL/SQL and SQL are meant to work together, the interaction between them can be weird sometimes.

Tracing the procedure will definitely get the context right, and may also show you other important things that are going on. Although I generally avoid tracing because it creates so much useless information, and it's usually (inexplicably) difficult to get access to the server.

2 - Instantiate the types in SQL

Create an empty collection, then your regular explain plan methods should work:

SELECT VALUE(VD) 
FROM
VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(typ_emp_coll()  ) VV WHERE VD.DEPTNO = VV.DEPTNO);

Or populate actual values:

SELECT VALUE(VD) 
FROM
VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(typ_emp_coll(typ_emp(null,null,null,null,null,null,null,null))  ) VV WHERE VD.DEPTNO = VV.DEPTNO);

Populating this with meaningful values could require some massive SQL statements. But sometimes it's helpful to have everything in one place. Also, as I'll explain later, it doesn't actually matter if you use an empty collection. Oracle can't tell the difference between an empty collection and a huge collection.

3 - Use the SQL_ID of the executed query.

Find the SQL_ID in V$SQL. This may return more than 1 row, you may need to manually pick the right one.

select * from v$sql where upper(sql_text) like '%SELECT 1 FROM TABLE(%';

The sql_id can be used in a lot of ways to get the plan, such as:

select * from table(dbms_xplan.display_cursor(sql_id => '7c02yjs9q5kqr'));

SQL_ID  7c02yjs9q5kqr, child number 0
-------------------------------------
SELECT VALUE(VD) FROM VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(:B1 
) VV WHERE VD.DEPTNO = VV.DEPTNO)

Plan hash value: 2616009478

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |       |    32 (100)|          |
|*  1 |  HASH JOIN SEMI                    |      |     1 |    32 |    32   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | DEPT |     1 |    30 |     2   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=SYS_OP_ATG(VALUE(KOKBF$),8,9,2))

There are many related ways to get similar information: using the /*+ gather_plan_statistics */ hint, v$sql_monitor, v$sql_plan, dbms_sqltune.report_sql_monitor(sql_id => '7c02yjs9q5kqr', type => 'active'), etc. Each one has different pros and cons. Personally, I try to use report_sql_monitor whenever possible. It's a great graphical representation of the query, although it requires some additional licensing.

4 - Use a function instead of the variables.

create or replace package test_pkg is
    v_emp typ_emp_coll;
    function get_v_emp return typ_emp_coll;
end;
/

create or replace package body test_pkg is
    function get_v_emp return typ_emp_coll is
    begin
        return v_emp;
    end;
end;
/

Now you can run the query as a standalone SQL statement, and use the real collection data. Your normal explain plan methods should work fine:

select * from table(test_pkg.get_v_emp);

---

Why is the plan always so bad?

Whichever method you use, you'll probably see almost the same explain plan that I generated. This is because functions and collections are invisible to the optimizer, and it will just guess 8168 rows each time.

To tune these queries, you'll probably want to look at this article, setting cardinality for pipelined and table functions.

Here's a quick example of a solution, using dynamic sampling:

select /*+ dynamic_sampling(5) */ * from table(test_pkg.get_v_emp)