Skip to Main Content
  • Questions

  • Bind Variables on Extensibile optimizer

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: September 02, 2015 - 12:10 am UTC

Last updated: September 18, 2015 - 3:31 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Tom,

Is the Extensible Optimizer doesnot support bind variables?
please see below when using bind variables, estimated_rows=1 but when not-binded estimated_rows=4 why so?
I am on 11.2.0.4 on 64 bit linux.

rajesh@ORA11G> variable x varchar2(20)
rajesh@ORA11G> exec :x :='1,2,3,4';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> select * from table( myfnc(:x,','));
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3443857157

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |       |     1 |     2 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| MYFNC |     1 |     2 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

rajesh@ORA11G> select * from table( myfnc('1,2,3,4',','));
Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3443857157

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |       |     4 |     8 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| MYFNC |     4 |     8 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

rajesh@ORA11G> set autotrace off

Here is the complete script i used for this demo.

rajesh@ORA11G> create or replace type myarraytype is table of varchar2(20);
  2  /

Type created.

Elapsed: 00:00:00.06
rajesh@ORA11G>
rajesh@ORA11G> create or replace function myfnc(p_in varchar2,
  2     p_delim varchar2 default ',')
  3  return     myarraytype
  4  pipelined is
  5  begin
  6     for x in (
  7             select substr(txt,
  8             instr(txt,p_delim,1,level)+1,
  9             instr(txt,p_delim,1,level+1) -
 10             instr(txt,p_delim,1,level) -1 ) val
 11             from ( select p_delim||p_in||p_delim txt from dual )
 12             connect by level <= length(p_in) -
 13                     length(replace(p_in,p_delim))+1
 14             )
 15     loop
 16             pipe row( x.val );
 17     end loop;
 18  end;
 19  /

Function created.

Elapsed: 00:00:00.01
rajesh@ORA11G> create or replace type myfnc_stats
  2  as object
  3  (
  4     x number ,
  5     static function ODCIGetInterfaces
  6             ( p_ifclist OUT sys.ODCIObjectList )
  7     return number ,
  8
  9     static function odcistatstablefunction(
 10             p_func in sys.odcifuncinfo,
 11             p_outstats out sys.odcitabfuncstats,
 12             p_argdesc in sys.odciargdesclist,
 13             p_str in varchar2 default null ,
 14             p_delim in varchar2 default ',' )
 15     return number
 16  );
 17  /

Type created.

Elapsed: 00:00:00.58
rajesh@ORA11G>
rajesh@ORA11G> create or replace type body myfnc_stats
  2  as
  3
  4     static function ODCIGetInterfaces
  5             ( p_ifclist OUT sys.ODCIObjectList )
  6     return number
  7     as
  8     begin
  9             p_ifclist := sys.ODCIObjectList( sys.ODCIObject('SYS','ODCISTATS2')) ;
 10             return ODCIConst.Success;
 11     end;
 12
 13     static function odcistatstablefunction(
 14             p_func in sys.odcifuncinfo,
 15             p_outstats out sys.odcitabfuncstats,
 16             p_argdesc in sys.odciargdesclist,
 17             p_str in varchar2 default null ,
 18             p_delim in varchar2 default ',' )
 19     return number
 20     as
 21     begin
 22             p_outstats := sys.ODCITabFuncStats(nvl(length(p_str) -
 23                                     length( replace(p_str,p_delim))+1,10));
 24             return ODCIConst.Success;
 25     end;
 26  end;
 27  /

Type body created.

Elapsed: 00:00:00.02
rajesh@ORA11G> associate statistics with functions myfnc using myfnc_stats;

Statistics associated.

Elapsed: 00:00:00.06
rajesh@ORA11G>

and Connor said...


EXPLAIN PLAN (whether executed explicitly, or via "set autotrace") does not peek at bind variables, so you are not going to see the real picture here.

Here's a simple example

SQL> create table T as select * from all_objects;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T',method_opt=>'for columns owner size 255');

PL/SQL procedure successfully completed.

SQL>
SQL> variable x varchar2(30)
SQL> exec :x := 'SYS';

PL/SQL procedure successfully completed.

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly explain
SQL> select count(*) from T where owner = :x;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |   423   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  2732 | 16392 |   423   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"=:X)

SQL>
SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace off
SQL> select count(*) from T where owner = :x;

  COUNT(*)
----------
     41844

1 row selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  808u9z1g11nuy, child number 0
-------------------------------------
select count(*) from T where owner = :x

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   423 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 41844 |   245K|   423   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("OWNER"=:X)


Notice how explain plan didnt peek, and assumed the default, whereas when we actually *ran* the statement, we can use DBMS_XPLAN to see what plan/costs were truly adopted.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

on reality plans

Rajeshwaran, Jeyabal, September 03, 2015 - 1:01 pm UTC

Even with bind variables in place and peeking into reality plans, the estimated card is not matching up.

look like extensible optimizer doesn't work with bind variables. 

rajesh@ORA11G>
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> variable x varchar2(20)
rajesh@ORA11G> exec :x :='1,2,3,4';

PL/SQL procedure successfully completed.

rajesh@ORA11G> select * from table( myfnc(:x,','));

COLUMN_VALUE
--------------------
1
2
3
4

4 rows selected.

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  grc21f4h6awwf, child number 0
-------------------------------------
select * from table( myfnc(:x,','))

Plan hash value: 3443857157

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |       |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| MYFNC |     1 |     2 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


13 rows selected.

rajesh@ORA11G> select * from table( myfnc('1,2,3,4',','));

COLUMN_VALUE
--------------------
1
2
3
4

4 rows selected.

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  6j24c49sg195c, child number 0
-------------------------------------
select * from table( myfnc('1,2,3,4',','))

Plan hash value: 3443857157

-------------------------------------------------------------------------------------------
| Id  | Operation                         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |       |       |       |    29 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| MYFNC |     4 |     8 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


13 rows selected.

rajesh@ORA11G>

Connor McDonald
September 05, 2015 - 4:40 am UTC

I did some tracing. In both cases, the trace file shows the following call:

PARSING IN CURSOR #197688992 len=347 dep=1 uid=110 oct=47 lid=110 tim=1199243599539 hv=2498191275 ad='7ff4a7f2558' sqlid='10xwr2yaffrxb'
  declare 
       
    begin 
      :1 := "ASKTOM"."MYFNC_STATS".ODCIStatsTableFunction(sys.ODCIFuncInfo('ASKTOM', 'MYFNC', NULL, 1), :2, sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)) , :3, :4); 
    exception 
      when others then 
        raise; 
    end;
END OF STMT


However if we combine that with a 10053 optimizer trace for both scenarios, we see an interesting difference

When using literals

SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for KOKBF$0[KOKBF$0] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Calling user-defined function card function...
Bind :3 Value '1,2,3,4'
Bind :4 Value ','
  "ASKTOM"."MYFNC_STATS".ODCIStatsTableFunction returned:
    num_rows      : 4


When using binds

SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for KOKBF$0[KOKBF$0] 
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Calling user-defined function card function...
Bind :3 Value :1
Bind :4 Value ','
  ORA-1008 received when calling ASKTOM.MYFNC_STATS.ODCIStatsTableFunction -- method ignored


So looks like an issue with *calling* the statistics function, and hence your user defined statistics dont make it through.

Take to support ?

Rajeshwaran Jeyabal, September 05, 2015 - 11:01 am UTC

I dont have access to support, would you mind in taking this to support ?
Connor McDonald
September 18, 2015 - 3:31 am UTC

I will do so.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library