绑定变量固定解析

SQL> set linesize 300
SQL> set pagesize 10000
SQL> VAR v1 NUMBER;
SQL> exec :v1:=1; 

PL/SQL procedure successfully completed.

SQL> select to_char(sample_time, 'yyyymmdd hh24:mi:ss') qa,
  2         a.event,
  3         a.sql_id,
  4         a.blocking_session_status
  5    from ASH191614 a
  6   where a.sql_id ='8n108mrykkbzw'  and user_id=:v1;

no rows selected

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dmcyjqsjaf6r7, child number 0
-------------------------------------
select to_char(sample_time, 'yyyymmdd hh24:mi:ss') qa,        a.event,
      a.sql_id,        a.blocking_session_status   from ASH191614 a
where a.sql_id ='8n108mrykkbzw'  and user_id=:v1

Plan hash value: 2944716488

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |    32 (100)|          |
|*  1 |  TABLE ACCESS FULL| ASH191614 |     1 |   120 |    32   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter(("A"."SQL_ID"='8n108mrykkbzw' AND "USER_ID"=:V1))

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


24 rows selected.

SQL> 

 

declare
  stmt_str01                varchar2(200);
  cur_hdl01                 int;
  rows_processed01          int;
  vname                     varchar2(10);
  vsqltext                  varchar2(1000);
begin
for cnt in 1..100 loop
  cur_hdl01 := dbms_sql.open_cursor; -- open cursor 01
  select sql_fulltext   into vsqltext from v$sql where sql_id='dmcyjqsjaf6r7' and rownum<2;
  dbms_sql.parse(cur_hdl01, stmt_str01, dbms_sql.native);
  --dbms_sql.bind_variable(cur_hdl01, ':v1', 1);
  rows_processed01 := dbms_sql.execute(cur_hdl01); -- execute
  dbms_sql.close_cursor(cur_hdl01);
 end loop;
end;


declare
  stmt_str01                varchar2(200);
  cur_hdl01                 int;
  rows_processed01          int;
  vname                     varchar2(10);
  vsqltext                  varchar2(1000);
begin
for cnt in 1..10000 loop
  --cur_hdl01 := dbms_sql.open_cursor; -- open cursor 01
  select sql_fulltext   into vsqltext from v$sql where sql_id='dmcyjqsjaf6r7' and rownum<2;
  --dbms_output.put_line(vsqltext);
  EXECUTE IMMEDIATE vsqltext USING 1;
 end loop;
end;