[20260109]优化sql语句采用execute immediate执行遇到的问题.txt

  • lfree

    2026-01-11 20:16:49
  • Oracle
  • 原创

[20260109]优化sql语句采用execute immediate执行遇到的问题.txt

--//前几天在优化使用自己写的b5.sql脚本生成sql语句执行脚本，发现一些问题，做了一些修改更新，我个人很少使用该脚本，主要这
--//次优化的语句里面绑定变量是timestamp类型，采用以前生成的执行脚本,以字符串变量带入如果字段是date类型会报错，每次生成的
--//执行脚本必须做一些修改，想节省时间采用b5.sql脚本，但是在优化时遇到问题，特别做一个例子说明：

1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.问题提出：
SCOTT@book01p> create table t1 as select * from all_objects ;
Table created.

SCOTT@book01p> create index i_t1_created on t1(created)
Index created.
--//分析表略。

SCOTT@book01p> variable N1 varchar2(32)
SCOTT@book01p> exec :N1 := '2026-01-01 15:29:56.000000000'
PL/SQL procedure successfully completed.

SCOTT@book01p> select count(*) from t1 where created> :N1;
select count(*) from t1 where created> :N1
                                        *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

--//必须把生成的脚本做一些修改：
SCOTT@book01p> exec :N1 := '2026-01-01 15:29:56'

PL/SQL procedure successfully completed.

SCOTT@book01p> select count(*) from t1 where created> :N1;

  COUNT(*)
----------
       122

--//而采用b5.sql生成的脚本就没有这个问题。

3.先构造执行脚本：
--//主要原因sqlplus的variable不支持date，timestamp，麻烦的是toad情况类似，toad仅仅支持date类型的绑定变量，顺便发现oracle
--//sqlplus的help输出的一个小错误：

COTT@book01p> help variable
 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

 VAR[IABLE] [ [type][=value]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE
--//oracle不支持date，timestamp类型。感觉oracle应该更改支持这类类型，日期类型以字符串输入受nls_*_format参数影响，
--//比如在字符串前面加一个d表示date类型，t表示timestamp类型。

 One can assign value to a variable for input with the new syntax
 Example:
   VARIABLE tmp_var VAHRCHAR2(10)=Smith
                    ~~~~~~~~~~~~~~
   or
   VARIABLE tmp_var VAHRCHAR2(10)
                    ~~~~~~~~~~~~~
   VARIABLE tmp_var=Smith
   EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)

--//注意看下划线，写成VAHRCHAR2，顺便测试后面的字符串，确实不使用单引号也是可以的，补充测试如下：
SCOTT@book01p> VARIABLE tmp_var VARCHAR2(10)=Sm'i'th
SCOTT@book01p> print :tmp_var
TMP_VAR
--------------------------------
Sm'i'th

--//回到问题，建立执行脚本：
$ cat g1.txt
declare
  b2 timestamp:=to_timestamp('2026/01/01 15:19:56.000001', 'yyyy/mm/dd hh24:mi:ss.ff6');
begin
execute immediate q'[select owner,object_name from t1 where created>:2 ]' using b2;
end;
/

SCOTT@book01p> @ g1.txt
PL/SQL procedure successfully completed.

--//查询共享池找到sql_id=bup5map0711px.

SCOTT@book01p> @ sql_id bup5map0711px
-- SQL_ID = bup5map0711px come from shared pool
select owner,object_name from t1 where created>:2  ;

SCOTT@book01p> @ bc bup5map0711px ''
SQL_ID        CHILD_NUMBER WAS NAME   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING                     INST_ID
------------- ------------ --- ---- ---------- ---------- ------------------- --------------- ----------------------------- ----------
bup5map0711px            0 YES :2            1         11 2026-01-09 09:52:46 TIMESTAMP       2026/01/01 15:19:56.000001000          1

SCOTT@book01p> @ b9 bup5map0711px 0
conv_sys=1 convert SYS_B_N,conv_sys=0 not convert SYS_B_N,conv_sys=2 convert constant
@ b9
@ b9 bup5map0711px 0

variable N2 VARCHAR2(32)
begin
:N2 := '2026/01/01 15:19:56.000001000';
null;
end;
/

set termout off
set sqlblanklines on
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
select owner,object_name from t1 where created>:N2  ;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' '' ''
@dpc '' outline ''
rollback;
alter session set current_schema=SCOTT ;

--//采用b9生成的脚本如上，执行会报错。
SCOTT@book01p> @ bup5map0711px.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Last statement has a PREV_SQL_ID of ZERO
2 rows selected.
argment : basic typical serial all adaptive advanced peeked_binds outline
argment : rows bytes cost partition predicate projection alias remote note iostats memstats allstats last
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.

--//必须修改N2的值。:N2 := '2026/01/01 15:19:56';

SCOTT@book01p> @ b5 bup5map0711px
set sqlblanklines on
alter session set current_schema=SCOTT;
alter session set statistics_level=all;
--@10046on 12
begin
execute immediate q'select owner,object_name from t1 where created>:2  ' using
TO_TIMESTAMP('2026/01/01 15:19:56.000001000','yyyy/mm/dd hh24:mi:ss.ff9')  --  :2
;
end;
/

@dpc bup5map0711px outline ''

set sqlblanklines off
rollback;
--@10046off
alter session set current_schema=SCOTT ;

--//采用b5生成的脚本如上，执行ok。
SCOTT@book01p> @bup5map0711px.sql5
Session altered.
Session altered.
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bup5map0711px, child number 0
-------------------------------------
select owner,object_name from t1 where created>:2
Plan hash value: 352500366
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |       |     6 (100)|          |      0 |00:00:00.01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |    162 |  7938 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN                  | I_T1_CREATED |      1 |    162 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T1"@"SEL$1"
   2 - SEL$1 / "T1"@"SEL$1"
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."CREATED"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - (TIMESTAMP): [Not Printable]
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CREATED">:2)

SQL_ID  bup5map0711px, child number 1
-------------------------------------
select owner,object_name from t1 where created>:2
Plan hash value: 352500366
------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |       |     6 (100)|          |      0 |00:00:00.01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1           |      1 |    162 |  7938 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |
|*  2 |   INDEX RANGE SCAN                  | I_T1_CREATED |      1 |    162 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / "T1"@"SEL$1"
   2 - SEL$1 / "T1"@"SEL$1"
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('21.1.0')
      DB_VERSION('21.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."CREATED"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - (TIMESTAMP): [Not Printable]
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CREATED">:2)
90 rows selected.

argment : basic typical serial all adaptive advanced peeked_binds outline
argment : rows bytes cost partition predicate projection alias remote note iostats memstats allstats last
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.

--//生成新的子光标1，因为alter session set statistics_level=all;。
--//注意看执行子光标1的执行计划，从12c开始，oracle这类情况可以使用建立的日期索引，不存在隐式转换问题。
--//但是执行计划执行的 A-Rows=0，换一句话讲执行语句根本没有执行，仅仅做了分析生成了执行计划。也就是按照这样的生成的sql脚
--//本来做优化，会存在根本无法定位问题的情况。

--//也就是采用这样的方式仅仅拿来看看执行计划，有时候很难分析问题在哪里。

--//再做一个简单的例子：
SCOTT@book01p> set timing on
SCOTT@book01p> select count(*) from t1,emp,emp,emp ;
  COUNT(*)
----------
 191816576
Elapsed: 00:00:06.36

SCOTT@book01p> exec execute immediate 'Select count(*) from t1,emp,emp,emp';
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
--//几乎马上返回，对比前面可以知道根本没有执行。

SCOTT@book01p> variable a number ;
SCOTT@book01p> exec execute immediate 'Select count(*) from t1,emp,emp,emp' into :a;
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.42

SCOTT@book01p> print a
A
----------
 191816576

--//采用into接收到1个变量时才真正执行。而且采用into接收这类语句仅仅返回1行才行，多行报错。看如下测试：

SCOTT@book01p> variable v_deptno number ;
SCOTT@book01p> variable v_dname varchar2(14) ;
SCOTT@book01p> variable v_loc varchar2(14) ;
SCOTT@book01p> exec  execute immediate  'select * from dept ' into :v_deptno,:v_dname,:v_loc;
BEGIN execute immediate  'select * from dept ' into :v_deptno,:v_dname,:v_loc; END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 1

SCOTT@book01p> exec  execute immediate  'select * from dept where deptno=:1' into :v_deptno,:v_dname,:v_loc using 20;
PL/SQL procedure successfully completed.

SCOTT@book01p> select :v_deptno,:v_dname,:v_loc from dual ;
:V_DEPTNO :V_DNAME                         :V_LOC
---------- -------------------------------- --------------------------------
        20 RESEARCH                         DALLAS


请使用浏览器的分享功能分享到微信等