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

[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] [<variable> [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 <sql_id> <conv_sys> @ 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