Scalar subquery and Result Cache on 11g

Scalar subqueries are used to minimize the number of times a PL/SQL function is called from SQL. This is important because the overhead of going from SQL to PL/SQL over and over again can be quite expensive, and the scalar subquery caching database feature can dramatically decrease this overhead.

What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value.

rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select * from all_objects;

Table created.

Elapsed: 00:00:06.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  as
  4  begin
  5     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  6     return (length(x));
  7  end;
  8  /

Function created.

Elapsed: 00:00:00.67
rajesh@ORA11GR2>
rajesh@ORA11GR2> select count(distinct owner) from t;

COUNT(DISTINCTOWNER)
--------------------
                  30

Elapsed: 00:00:00.01

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable cpu number;
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.73
Statistics
----------------------------------------------------------
         55  recursive calls
          0  db block gets
       1527  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')      DBMS_UTILITY.GET_CPU_TIME-:CPU
--------------------------- ------------------------------
71598                       67

Elapsed: 00:00:00.09

rajesh@ORA11GR2>

you can see that the function was called once per row even though the inputs to the function were repeated over and over again. If you use a scalar subquery you will notice a massive reduction in calls to the function

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,(select f(owner) from dual) from t;

71598 rows selected.

Elapsed: 00:00:00.18

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1495  consistent gets
          0  physical reads
          0  redo size
     808592  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')     DBMS_UTILITY.GET_CPU_TIME-:CPU
-------------------------- ------------------------------
64                         8

Elapsed: 00:00:00.01

rajesh@ORA11GR2>

The function call went down from 71598  to 64 and CPU time got dropped. How about making the function DETERMINISTIC

rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  deterministic
  4  as
  5  begin
  6     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  7     return (length(x));
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.21

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       1519  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')         DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
954                            16

Elapsed: 00:00:00.01

rajesh@ORA11GR2>

DETERMINISTIC function reduced the function calls, but not as smart as scalar subquery caching. so going one step futher how about making the function as result_cache in Oracle 11g?

rajesh@ORA11GR2> create or replace function f(x varchar2)
  2  return number
  3  result_cache
  4  as
  5  begin
  6     dbms_application_info.set_client_info( userenv('client_info') + 1 );
  7     return (length(x));
  8  end;
  9  /

Function created.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.54

Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
       1519  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')       DBMS_UTILITY.GET_CPU_TIME-:CPU
---------------------------- ------------------------------
30                           44

Elapsed: 00:00:00.03

rajesh@ORA11GR2>

The function called is equal to the number of distinct values present in the column OWNER. If you run the query again then function call becomes zero but still the cpu utilization time is longer than scalar subquery.

rajesh@ORA11GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> exec :cpu := dbms_utility.get_cpu_time;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> select object_id,f(owner) from t;

71598 rows selected.

Elapsed: 00:00:00.50

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1495  consistent gets
          0  physical reads
          0  redo size
     808576  bytes sent via SQL*Net to client
       5663  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71598  rows processed

rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select userenv('client_info') ,
  2        dbms_utility.get_cpu_time - :cpu
  3  from dual;

USERENV('CLIENT_INFO')         DBMS_UTILITY.GET_CPU_TIME-:CPU
------------------------------ ------------------------------
0                              42

Elapsed: 00:00:00.01

rajesh@ORA11GR2> 

This shows that even if the function is defined as DETERMINISTIC or RESULT_CACHE its better to have the function called as scalar subquery for efficient CPU utilization.

Good Bye to Cardinality Hint and Welcome to Cardinality Feedback

rajesh@ORA10GR2> create table t
  2  as
  3  select * from all_objects;

Table created.

Elapsed: 00:00:04.21

rajesh@ORA10GR2>
rajesh@ORA10GR2> create index t_ind on t(object_id) nologging;

Index created.

Elapsed: 00:00:00.32
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
  2  from t
  3  where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1584247153
----------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |     1 |   187   (4)|
|*  1 |  HASH JOIN RIGHT SEMI                  |      |     1 |   187   (4)|
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |            |
|   3 |   TABLE ACCESS FULL                    | T    | 50096 |   161   (3)|
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=VALUE(KOKBF$))

Statistics
----------------------------------------------------------
        274  recursive calls
          0  db block gets
        797  consistent gets
          1  physical reads
          0  redo size
       1394  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
          5  rows processed

rajesh@ORA10GR2>

Optimizer is not using the index, since cardinality is not attached to plan at id=2, So how can we instruct the optimizer about cardinality?  using undocumented hint cardinality

rajesh@ORA10GR2> select *
  2  from t
  3  where object_id in (select /*+ cardinality(t,5) */ * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) t  where rownum > 0);

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 561482654
-------------------------------------------------------------------------------
| Id  | Operation                                   | Name     | Rows  | Cost  |
-------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                            |          |     1 |    27 |
|   1 |  TABLE ACCESS BY INDEX ROWID                | T        |     1 |     2 |
|   2 |   NESTED LOOPS                              |          |     1 |    27 |
|   3 |    VIEW                                     | VW_NSO_1 |     5 |    24 |
|   4 |     HASH UNIQUE                             |          |     1 |       |
|   5 |      COUNT                                  |          |       |       |
|*  6 |       FILTER                                |          |       |       |
|   7 |        COLLECTION ITERATOR CONSTRUCTOR FETCH|          |       |       |
|*  8 |    INDEX RANGE SCAN                         | T_IND    |     1 |     1 |
--------------------------------------------------------------------------------

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

   6 - filter(ROWNUM>0)
   8 - access("OBJECT_ID"="COLUMN_VALUE")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         27  consistent gets
          0  physical reads
          0  redo size
       1388  bytes sent via SQL*Net to client
        396  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

rajesh@ORA10GR2>
rajesh@ORA10GR2>

The predicate rownum > 0 is added to have the subquery "materialized".  Now we have the correct cardinality in plan & the optimizer picked up the index to answer our queries.

Even the MEMBER OF is not looking index at this point -- even when hinted to use index, it does Fast Full Scan and not Range scan.

rajesh@ORA10GR2> create or replace type numarray is table of number;
  2  /

Type created.

Elapsed: 00:00:00.06
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable x refcursor;
rajesh@ORA10GR2>

rajesh@ORA10GR2> declare
  2     arr numarray := numarray(1,2,3,4,5,6);
  3  begin
  4     open :x for
  5     select object_id,data_object_id,owner,object_type,object_name
  6     from t
  7     where object_id member of ( arr );
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
 T WHERE OBJECT_ID MEMBER OF ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.02          0          0          0           0
Fetch        1      0.04       0.03          0        694          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.06       0.05          0        694          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS FULL T (cr=694 pr=0 pw=0 time=140 us)

rajesh@ORA10GR2>

rajesh@ORA10GR2> declare
  2     arr numarray := numarray(1,2,3,4,5,6);
  3  begin
  4     open :x for
  5     select /*+ index(t,t_ind) */ object_id,data_object_id,owner,object_type,object_name
  6     from t
  7     where object_id member of ( arr );
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>

SELECT /*+ index(t,t_ind) */ OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,
  OBJECT_NAME
FROM
 T WHERE OBJECT_ID MEMBER OF ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.01          0          0          0           0
Fetch        1      0.03       0.03          0        113          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.04          0        113          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS BY INDEX ROWID T (cr=113 pr=0 pw=0 time=97 us)
      5   INDEX FULL SCAN T_IND (cr=112 pr=0 pw=0 time=60 us)(object id 55213)

rajesh@ORA10GR2> declare
  2     arr numarray := numarray(1,2,3,4,5,6);
  3  begin
  4     open :x for
  5     select object_id,data_object_id,owner,object_type,object_name
  6     from t
  7     where object_id in ( select /*+ cardinality(t1,5) */ * from table ( arr ) t1 ) ;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@ORA10GR2>

SELECT OBJECT_ID,DATA_OBJECT_ID,OWNER,OBJECT_TYPE,OBJECT_NAME
FROM
 T WHERE OBJECT_ID IN ( SELECT /*+ cardinality(t1,5) */ * FROM TABLE ( :B1 )
  T1 )

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          9          0           0
Fetch        1      0.00       0.00          0          9          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         18          0           5

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  TABLE ACCESS BY INDEX ROWID T (cr=9 pr=0 pw=0 time=278 us)
     12   NESTED LOOPS  (cr=8 pr=0 pw=0 time=889 us)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=92 us)
      6     COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=28 us)
      5    INDEX RANGE SCAN T_IND (cr=8 pr=0 pw=0 time=93 us)(object id 55213)

When working with Oracle 10g database, we need to Instruct cardinality to optimizer using Cardinality Hint. But when repeating the same test in Oracle 11g database

rajesh@ORA11GR2>
rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
  2  from t
  3  where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

 OBJECT_ID DATA_OBJECT_ID OWNER      OBJECT_TYPE     OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
         3              3 SYS        INDEX           I_OBJ#
         6              6 SYS        CLUSTER         C_TS#
         5              2 SYS        TABLE           CLU$
         2              2 SYS        CLUSTER         C_OBJ#
         4              2 SYS        TABLE           TAB$

Elapsed: 00:00:00.07
rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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        2      0.03       0.02          0       1026          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.02          0       1026          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  HASH JOIN RIGHT SEMI (cr=1026 pr=0 pw=0 time=0 us cost=316 size=49 card=1)
      6   COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=16336 card=8168)
  71598   TABLE ACCESS FULL T (cr=1026 pr=0 pw=0 time=99596 us cost=286 size=3365106 card=71598)

rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
  2  from t
  3  where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

OBJECT_ID DATA_OBJECT_ID OWNER      OBJECT_TYPE     OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
         2              2 SYS        CLUSTER         C_OBJ#
         3              3 SYS        INDEX           I_OBJ#
         4              2 SYS        TABLE           TAB$
         5              2 SYS        TABLE           CLU$
         6              6 SYS        CLUSTER         C_TS#

Elapsed: 00:00:00.04

rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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        2      0.00       0.00          0         12          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation

-------  ---------------------------------------------------
      5  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)
      5   NESTED LOOPS  (cr=10 pr=0 pw=0 time=136 us cost=36 size=294 card=6)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      6     COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      5    INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
      5   TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)

You see the optimizer hard parsed the query again & came up with right explain plan to answer this query. And further execution optimizer has reused the plan again.

rajesh@ORA11GR2> select object_id,data_object_id,owner,object_type,object_name
  2  from t
  3  where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) );

 OBJECT_ID DATA_OBJECT_ID OWNER      OBJECT_TYPE     OBJECT_NAME
---------- -------------- ---------- --------------- --------------------
         2              2 SYS        CLUSTER         C_OBJ#
         3              3 SYS        INDEX           I_OBJ#
         4              2 SYS        TABLE           TAB$
         5              2 SYS        TABLE           CLU$
         6              6 SYS        CLUSTER         C_TS#

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

select object_id,data_object_id,owner,object_type,object_name
from t
where object_id in (select * from table ( sys.odcinumberlist(1,2,3,4,5,6) ) )

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        2      0.00       0.00          0         12          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         12          0           5

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      5  NESTED LOOPS  (cr=12 pr=0 pw=0 time=0 us)
      5   NESTED LOOPS  (cr=10 pr=0 pw=0 time=120 us cost=36 size=294 card=6)
      6    SORT UNIQUE (cr=0 pr=0 pw=0 time=5 us cost=29 size=12 card=6)
      6     COLLECTION ITERATOR CONSTRUCTOR FETCH (cr=0 pr=0 pw=0 time=10 us cost=29 size=12 card=6)
      5    INDEX RANGE SCAN T_IND (cr=10 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 79427)
      5   TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=0 us cost=2 size=47 card=1)

This proper cardinality is supplied to 11g optimizer by a new feature "cardinality feedback"