Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Wolfgang.

Asked: February 01, 2016 - 3:23 pm UTC

Last updated: May 18, 2019 - 4:42 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi,

in a PLSQL package I generate and execute dynamic SQL Statements. I wrote a litte procedure, which returns the output of dbms_xplan.display into a log table:
FUNCTION logPlan(p_statament VARCHAR2) RETURN CLOB IS
  PRAGMA autonomous_transaction;
    TYPE vtable IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
    l_plan vtable;
    l_bigPlan CLOB;
  BEGIN    
    dbms_lob.CREATETEMPORARY(l_bigPlan,false);
    execute immediate'explain plan for '||p_statament;
    select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
    for i in l_plan.first .. l_plan.last loop
      l_plan(i):=l_plan(i)||CHR(10);
      dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
    END LOOP;
    commit;
    return l_bigPlan;
  END;


As I'm doing some performancetuning I noticed that the output of explain plan inside the PLSQL Package differs from the output from explain plan done in toad in the same session.
Also when I execute the generated SQL in Toad it runs within 350ms, the same statemente with the same binds exeuted in the packages runs over 20seconds.

OPTIMIZER_DYNAMIC_SAMPLING ist set to 11, OPTIMIZER_ADAPTIVE_FEATURES=true

"Bad" Plan in PLSQL:
Plan hash value: 3564500612
 
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |     1 |   101 |     7  (15)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                     |                               |     1 |   101 |     7  (15)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                   |                               |     1 |   101 |     6   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                  |                               |     1 |   101 |     6   (0)| 00:00:01 |       |       |
|   4 |     MERGE JOIN CARTESIAN         |                               |     1 |    70 |     5   (0)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE SINGLE      |                               |     1 |    48 |     4   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |       TABLE ACCESS BY INDEX ROWID| TBLPARTNERSUCHE               |     1 |    48 |     4   (0)| 00:00:01 |       |       |
|*  7 |        INDEX RANGE SCAN          | TBLPARTNERSUCHE_NAME1_IX      |    14 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|   8 |      BUFFER SORT                 |                               |     7 |   154 |     1   (0)| 00:00:01 |       |       |
|*  9 |       INDEX RANGE SCAN           | TBL3008PCBREFERENZ_IXU        |     7 |   154 |     1   (0)| 00:00:01 |       |       |
|* 10 |     INDEX RANGE SCAN             | TBLVERTRAGSSUCHE_ORGA_PERS_IX |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 11 |    TABLE ACCESS BY INDEX ROWID   | TBLVERTRAGSSUCHE              |     1 |    31 |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
-->Removed from author to make it more readable



"Good" Plan in Sql Context in Toad (same session):
------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                  | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                           |                               | 77167 |  7611K|       | 39523   (1)| 00:00:02 |       |       |
|     1 |  HASH UNIQUE                               |                               | 77167 |  7611K|  9512K| 39523   (1)| 00:00:02 |       |       |
|- *  2 |   HASH JOIN                                |                               | 77167 |  7611K|       | 37732   (1)| 00:00:02 |       |       |
|     3 |    NESTED LOOPS                            |                               | 77167 |  7611K|       | 37732   (1)| 00:00:02 |       |       |
|     4 |     NESTED LOOPS                           |                               | 77167 |  7611K|       | 37732   (1)| 00:00:02 |       |       |
|-    5 |      STATISTICS COLLECTOR                  |                               |       |       |       |            |          |       |       |
|- *  6 |       HASH JOIN                            |                               | 62413 |  3230K|       |   248   (1)| 00:00:01 |       |       |
|     7 |        NESTED LOOPS                        |                               | 62413 |  3230K|       |   248   (1)| 00:00:01 |       |       |
|-    8 |         STATISTICS COLLECTOR               |                               |       |       |       |            |          |       |       |
|  *  9 |          INDEX RANGE SCAN                  | TBL3008PCBREFERENZ_IXU        |     7 |   154 |       |     1   (0)| 00:00:01 |       |       |
|  * 10 |         TABLE ACCESS BY INDEX ROWID BATCHED| TBLVERTRAGSSUCHE              |  8636 |   261K|       |    35   (0)| 00:00:01 |       |       |
|  * 11 |          INDEX RANGE SCAN                  | TBLVERTRAGSSUCHE_ORGA_PERS_IX |   255 |       |       |     1   (0)| 00:00:01 |       |       |
|- * 12 |        TABLE ACCESS FULL                   | TBLVERTRAGSSUCHE              |  8636 |   261K|       |    35   (0)| 00:00:01 |       |       |
|    13 |      PARTITION RANGE SINGLE                |                               |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  * 14 |       INDEX RANGE SCAN                     | TBLPARTNERSUCHE_NAME1_IX      |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  * 15 |     TABLE ACCESS BY INDEX ROWID            | TBLPARTNERSUCHE               |     1 |    48 |       |     1   (0)| 00:00:01 |       |       |
|-   16 |    PARTITION RANGE SINGLE                  |                               |     1 |    48 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|- * 17 |     TABLE ACCESS BY INDEX ROWID BATCHED    | TBLPARTNERSUCHE               |     1 |    48 |       |     1   (0)| 00:00:01 |       |       |
|- * 18 |      INDEX RANGE SCAN                      | TBLPARTNERSUCHE_NAME1_IX      |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                  -->Removed from author to make it more readable                                                                                                                 

Note                                                                                                                                                  
-----                                                                                                                                                 
   - dynamic statistics used: dynamic sampling (level=AUTO)                                                                                           
   - this is an adaptive plan (rows marked '-' are inactive)                                                                                          

49 rows selected.


Also strange, that the plsql Plan does not show that dynamic statistics are used. It seems as if the OPTIMIZER_DYNAMIC_SAMPLING=11 ist ignored.

In short terms: In the same session, the same Statement has different results when using explain plan within a plsql Package (execute immediate 'explain plan for||variable_that_hols_the_statement;) and within "pure" sql.
Also it seems as if the CBO ignores the OPTIMIZER_DYNAMIC_SAMPLING=11 setting within plsql (because there there are no notes in the output of dbmx_xplan.

Testcase where I can reproduce it:
create or replace function explain_plan(p_statament varchar2) return clob is 
PRAGMA autonomous_transaction;
TYPE vtable IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
    l_plan vtable;
    l_bigPlan CLOB;
  BEGIN    
    dbms_lob.CREATETEMPORARY(l_bigPlan,false);
    execute immediate'explain plan for '||p_statament;
    select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
    for i in l_plan.first .. l_plan.last loop
      l_plan(i):=l_plan(i)||CHR(10);
      dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
    END LOOP;
    commit;
    return l_bigPlan;
  END;
 /

alter session set OPTIMIZER_DYNAMIC_SAMPLING=11;

explain plan for <i>write a statement in which the CBO uses dynamic statistics</i>
select * from table(dbms_xplan.display(format=>'ADAPTIVE'));

Now the same with the plsql function:
select explain_plan('same statement from above') from dual


The output differs. First uses dynamic statistics, second doesn't


PS: Hope the plan is readable after submitting...




and Connor said...

It is not a PL/SQL thing per se. It appears to be an artifact of running the PL/SQL function from within the context of SQL. Consider the following example


SQL> set timing off
SQL> drop table T purge;

Table dropped.

SQL> drop table T1 purge;

Table dropped.

SQL>
SQL> create table T as select * from dba_objects;

Table created.

SQL> create table T1 as select * from dba_objects;

Table created.

SQL>
SQL> exec dbms_stats.delete_table_stats('','T');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> create or replace function explain_plan(p_statament varchar2) return clob is
  2  PRAGMA autonomous_transaction;
  3  TYPE vtable IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
  4      l_plan vtable;
  5      l_bigPlan CLOB;
  6    BEGIN
  7      dbms_lob.CREATETEMPORARY(l_bigPlan,false);
  8      execute immediate'explain plan for '||p_statament;
  9      select plan_table_output bulk collect into l_plan from table(dbms_xplan.display(format=>'ADAPTIVE'));
 10      for i in l_plan.first .. l_plan.last loop
 11        l_plan(i):=l_plan(i)||CHR(10);
 12        dbms_lob.writeappend(l_bigPlan,length(l_plan(i)),l_plan(i));
 13      END LOOP;
 14      commit;
 15      return l_bigPlan;
 16    END;
 17   /

Function created.

SQL>
SQL> set long 500000
SQL> set longchunksize 500000
SQL> set lines 200
SQL>
SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING=11;

Session altered.

SQL>
SQL> alter system flush shared_pool;

System altered.

--
-- explain plan direct
-- take note: hash join estimated rows = 89361 
--

SQL> explain plan for select count(*) from t1, t where t.object_id = t1.object_id;

Explained.

SQL> select * from table(dbms_xplan.display(format=>'ADAPTIVE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |       |  1221   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN         |      | 89361 |  2268K|  3296K|  1221   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   134K|  1713K|       |   450   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T    |   134K|  1713K|       |   450   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)

20 rows selected.

SQL>
SQL> alter system flush shared_pool;

System altered.

--
-- explain plan from within PLSQL *executed from SQL*
-- take note: hash join estimated rows = 134K
-- no reference to dynamic sampling
--


SQL> select explain_plan('select count(*) from t1, t where t.object_id = t1.object_id') from dual;

EXPLAIN_PLAN('SELECTCOUNT(*)FROMT1,TWHERET.OBJECT_ID=T1.OBJECT_ID')
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |       |  1221   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN         |      |   134K|  3426K|  3296K|  1221   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   134K|  1713K|       |   450   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T    |   134K|  1713K|       |   450   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")


SQL>
SQL> alter system flush shared_pool;

System altered.

--
-- explain plan from within PLSQL *NOT* executed from SQL
-- take note: hash join estimated rows = 89361 
--

SQL> variable c clob
SQL> exec :c := explain_plan('select count(*) from t1, t where t.object_id = t1.object_id')

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
Plan hash value: 949044725

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |       |  1221   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    26 |       |            |          |
|*  2 |   HASH JOIN         |      | 89361 |  2268K|  3296K|  1221   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |   134K|  1713K|       |   450   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T    |   134K|  1713K|       |   450   (1)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)


SQL>
SQL>


I will ask around internally, but it would appear some things are different/locked down within a PL/SQL function when its being executed from SQL (which would sort of make sense, because by the time you are executing the plsql function, you would expect all of the dynamic sampling etc to be already done).

Moral of the story: "select plsql_function from dual", probably isnt a good idea if that function is doing dynamic SQL etc.

Hope this helps.

Rating

  (8 ratings)

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

Comments

Wolfgang Bauer, February 05, 2016 - 11:25 am UTC

Hi Conner,

thanks for your investigations :-)
Moral of the story: "select plsql_function from dual", probably isnt a good idea if that function is doing dynamic SQL etc.

Well it's a pipelined function which is not only executing a query...
Perhaps you can find out some more details about this behavior.
Connor McDonald
February 05, 2016 - 10:27 pm UTC

I have been doing exactly that. I'll be sharing some information via a blog post when I get a moment

just a thought on pl/sql difference

ojock, March 02, 2019 - 9:23 am UTC

Does PL/SQL have any differences in optimizer environment settings which could cause different plans ? e.g. optimizer_mode or anything else ?

From SQL Developer, I run a SELECT on its own, then cut & paste the same SELECT inside a PL/SQL cursor for loop - resulted in different execution plans.

We're on 11.2.0.4

How do I check what settings may be different ?
Connor McDonald
March 06, 2019 - 6:50 am UTC

From your other reply you can see the PLSQL normalizes a query before running it (case etc). But if you take that same normalized SQL and run it in SQL Developer or equivalent and you STILL get a different plan, you can compare the two cursors via V$SQL_SHARED_CURSOR.

Example here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=what-is-a-child-cursor

found at least 1 difference between SQL and PL/SQL

ojock, March 02, 2019 - 10:37 am UTC

...apart from maybe OPTIMIZER_MODE, I've found that PL/SQL does this to the SQL statement cut & pasted in;

1. converts all the text from lower to upper case
2. strips out comments /* */, but not if it's a hint /*+ */
3. convert literals to bind variables for certain filters, like this;

where some_ts_col < to_timestamp('01-FEB-2019','DD-MON-YYYY')


to

where some_ts_col < to_timestamp(to_char(:b1),to_char(:b2))


Whereas something like this has NOT been converted;

where some_str_col = 'ABC'


Do you know what's causing that ?

CURSOR_SHARING is set to EXACT on the system, and I'm running the SQL from SQL Developer. I'm a bit baffled...

Connor McDonald
March 06, 2019 - 6:49 am UTC

1) yes
2) yes
3) Never ever seen that.....ever. Can you give us a test case?

Test case as requested

ojock, March 06, 2019 - 5:26 pm UTC

Connor, test case;

create table test_tbl (ts_col timestamp(0));
 

declare
  l_cnt pls_integer := 0;
begin 
  for c_rec in (
            select /*+ findme_plsql */
                   *
            from   test_tbl
            where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')
            and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1
             )
  loop
    l_cnt := l_cnt +1;
  end loop;

  dbms_output.put_line('how many='||l_cnt);
end;
/    

-- same SQL as above, just changed the commented text to find it easier

select /*+ findme_sql */
                   *
from   test_tbl
where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')
and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1;

-- find the 2 SQLs

select sql_fulltext,sql_id
from gv$sql
where upper(sql_fulltext) like 'SELECT /*+ FINDME%';

-- what I found ! 
SELECT /*+ findme_plsql */ * FROM TEST_TBL WHERE TS_COL >= TO_TIMESTAMP(TO_CHAR( :B4 ), TO_CHAR( :B3 )) AND TS_COL < TO_TIMESTAMP(TO_CHAR( :B2 ), TO_CHAR( :B1 )) + 1

select /*+ findme_sql */ * from   test_tbl where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')  and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1


I'm using SQL Developer 4.2.0.17, Oracle 11.2.0.4

Looked some more, still can't explain it. I thought maybe PL/SQL optimization level set to 2 might affect it, changed it, made no difference
Chris Saxon
March 13, 2019 - 3:07 pm UTC

The PL/SQL compiler converts function arguments to binds as part of its canonicalization process. Whereas regular literals remain as-is:

create table test_tbl (ts_col timestamp(0), n_col number);

create or replace function f ( p int ) 
  return int as
begin
  return p;
end f;
/

declare
  l_cnt pls_integer := 0;
begin 
  for c_rec in (
            select /*+ findme_plsql */
                   *
            from   test_tbl
            where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')
            and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1
            and    ts_col < date'2019-03-31'
            and    n_col = 1
            and    f ( 1 ) = 1 
             )
  loop
    l_cnt := l_cnt +1;
  end loop;

  dbms_output.put_line('how many='||l_cnt);
end;
/    

set long 10000
select sql_fulltext,sql_id
from gv$sql
where upper(sql_fulltext) like 'SELECT /*+ FINDME%';

SQL_FULLTEXT                                                                                                                                                                                                                                        SQL_ID          
SELECT /*+ findme_plsql */ * FROM TEST_TBL WHERE TS_COL >= TO_TIMESTAMP(TO_CHAR( :B5 ), TO_CHAR( :B4 )) AND TS_COL < TO_TIMESTAMP(TO_CHAR( :B3 ), TO_CHAR( :B2 )) + 1 AND TS_COL < DATE'2019-03-31' AND N_COL = 1 AND F ( TO_NUMBER( :B1 ) ) = 1    051dfh38kwfzk

who dunnit?

Racer I., March 07, 2019 - 11:02 am UTC

Hi,

I think CURSOR-SHARING binds look like this :
select count(*) from test where id1=:”SYS_B_0″
So this doesn't seem to be that.
Doesn't happen with our 12.1. Neither in TOAD nor in sqlplus.
May be an SQLDeveloper/deature/plugin? Or maybe some
https://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA131

who dunnit?

ojock, March 08, 2019 - 2:02 pm UTC

CURSOR_SHARING=EXACT
On 11.2.0.4, so no SQL Translation Framework
Just installed SQL Developer as standard, not added any plug-ins

gaming the system

Racer I., March 14, 2019 - 7:31 am UTC

Hi,

Weird. I don't see that in our (12.1) db with neither TOAD nor sqlplus. Neither does it show in LiveSQL ( https://livesql.oracle.com/apex/livesql/s/h3o28dexh3xl2dfxury9sryiv )

So I can't test if this helps :

WITH
Params AS (
SELECT '01-MAR-19' fd, '31-MAR-19' td, 'dd-MON-yy' df, 1 fp FROM DUAL)
select /*+ findme2_plsql */ *
from test_tbl
CROSS JOIN Params p
where ts_col >= to_timestamp(p.fd, p.df)
and ts_col < to_timestamp(p.td, p.df) + 1
and n_col = 1
and f ( p.fp ) = 1

regards,
Chris Saxon
March 15, 2019 - 8:44 am UTC

Ahh, my mistake. This appears to be something happening in SQL Developer/SQLcl, rather than PL/SQL itself. 

C:\Users\csaxon>sql.exe chris/chris@db

SQLcl: Release 18.4 Production on Fri Mar 15 08:43:10 2019

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Fri Mar 15 2019 08:43:13 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> create or replace procedure p as
  2    l_cnt pls_integer := 0;
  3  begin
  4    for c_rec in (
  5              select /*+ findme_plsql */
  6                     *
  7              from   test_tbl
  8              where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')
  9              and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1
 10              and    ts_col < date'2019-03-31'
 11              and    n_col = 1
 12              and    f ( 1 ) = 1
 13               )
 14    loop
 15      l_cnt := l_cnt +1;
 16    end loop;
 17
 18    dbms_output.put_line('how many='||l_cnt);
 19  end;
 20  /

Procedure P compiled

SQL>
SQL> exec p();
how many=0


PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    l_cnt pls_integer := 0;
  3  begin
  4    for c_rec in (
  5              select /*+ findme_plsql */
  6                     *
  7              from   test_tbl
  8              where  ts_col >= to_timestamp('01-MAR-19', 'dd-MON-yy')
  9              and    ts_col <  to_timestamp('31-MAR-19', 'dd-MON-yy') + 1
 10              and    ts_col < date'2019-03-31'
 11              and    n_col = 1
 12              and    f ( 1 ) = 1
 13               )
 14    loop
 15      l_cnt := l_cnt +1;
 16    end loop;
 17
 18    dbms_output.put_line('how many='||l_cnt);
 19  end;
 20  /
how many=0


PL/SQL procedure successfully completed.

SQL>
SQL> set long 100000
SQL> select sql_fulltext,sql_id
  2  from gv$sql
  3  where upper(sql_fulltext) like 'SELECT /*+ FINDME%';
SQL_FULLTEXT                                                                                                                                                                                                                                        SQL_ID
SELECT /*+ findme_plsql */ * FROM TEST_TBL WHERE TS_COL >= TO_TIMESTAMP(TO_CHAR( :B5 ), TO_CHAR( :B4 )) AND TS_COL < TO_TIMESTAMP(TO_CHAR( :B3 ), TO_CHAR( :B2 )) + 1 AND TS_COL < DATE'2019-03-31' AND N_COL = 1 AND F ( TO_NUMBER( :B1 ) ) = 1    051dfh38kwfzk
SELECT /*+ findme_plsql */ * FROM TEST_TBL WHERE TS_COL >= TO_TIMESTAMP('01-MAR-19', 'dd-MON-yy') AND TS_COL < TO_TIMESTAMP('31-MAR-19', 'dd-MON-yy') + 1 AND TS_COL < DATE'2019-03-31' AND N_COL = 1 AND F ( 1 ) = 1                               2umm803usx9xy

who dunnit?

ojock, March 17, 2019 - 8:58 am UTC

". This appears to be something happening in SQL Developer/SQLcl, rather than PL/SQL itself. "

Is that documented behaviour ?

The main problem I'm having with this is the execution plan being different when literal is swapped for bind
Connor McDonald
May 18, 2019 - 4:42 am UTC

That is a feature of SQLcl

http://krisrice.io/2015-09-11-sqlcl-more-secure-now-with-rest/

You can control it with: set secureliterals
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

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here