[20211026]奇怪注解不起作用.txt

来源：这里教程网时间：2026-03-03 17:04:58 作者：

[20211026]奇怪注解不起作用.txt --//昨天在测试row cache 相关等待时，我发现我脚本里面的注解不起作用为什么，今天仔细探究看看。 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试环境建立: $ cat m14.txt set verify off host sleep $(echo &&3/50 | bc -l ) variable vmethod varchar2(20); exec :vmethod := '&&2'; insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ; commit ; declare v_id number; v_d date; v_val VARCHAR2(1000); l_count PLS_INTEGER; begin for i in 1 .. &&1 loop --//select value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; --//select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; --//select sql_text into v_val from v$sql where rownum=1; SELECT /* &&3 */ seq1.NEXTVAL into v_id FROM DUAL; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod; commit; quit --//说明：循环体内脚本SELECT /* &&3 */ seq1.NEXTVAL into v_id FROM DUAL;. 3.测试： SCOTT@book> @ tpt/sql_id 9cp836a3k67w2 % Show SQL text, child cursors and execution stats for SQLID 9cp836a3k67w2 child % no rows selected no rows selected --//可以发现测试前该语句不再共享池中。 SCOTT@book> @m14.txt 100 P=1 1 PL/SQL procedure successfully completed. 1 row created. Commit complete. PL/SQL procedure successfully completed. 1 row updated. Commit complete. SCOTT@book> @ tpt/sql_id 9cp836a3k67w2 % Show SQL text, child cursors and execution stats for SQLID 9cp836a3k67w2 child % HASH_VALUE CH# SQL_TEXT ---------- ---- ------------------------------ 2267225986 0 SELECT SEQ1.NEXTVAL FROM DUAL CH# PARENT_HANDLE OBJECT_HANDLE PLAN_HASH PARSES H_PARSES EXECUTIONS FETCHES ROWS_PROCESSED CPU_MS ELA_MS LIOS PIOS SORTS USERS_EXECUTING ---- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- ---------- --------------- 0 000000007C3DCAB8 000000007CAB3C68 51561390 1 1 100 100 100 20.997 19.969 404 0 0 0 --//执行的是SELECT SEQ1.NEXTVAL FROM DUAL，oracle的PLSQL脚本把我写的注解给丢失了。 SCOTT@book> select sql_id,executions,sql_fulltext c200 from v$sqlarea where lower(sql_text) like '%seq1.nextval%'; SQL_ID EXECUTIONS C200 ------------- ---------- ------------------------------------------------------------------------------------------------------- gspuskkd102p6 1 select sql_text from v$sqlarea where lower(sql_text) like '%seq1.nextval%' d6dgq30k5jb64 1 declare v_id number; v_d date; v_val VARCHAR2(1000); l_count PLS_INTEGER; begin for i in 1 .. 100 loop --//select value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; --//select value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; --//select sql_text into v_val from v$sql where rownum=1; SELECT /* 1 */ seq1.NEXTVAL into v_id FROM DUAL; end loop; end ; f7tupa89262a0 1 select sql_id,sql_fulltext from v$sqlarea where lower(sql_text) like '%seq1.nextval%' 9cp836a3k67w2 100 SELECT SEQ1.NEXTVAL FROM DUAL --//仔细看sql_id=d6dgq30k5jb64,里面的语句带注解的，而sql_id=9cp836a3k67w2时，注解不见了。 --//修改为注解里面多了一个加号。 SELECT /*+ &&3 */ seq1.NEXTVAL into v_id FROM DUAL; SCOTT@book> @ m14.txt 100 test 2 PL/SQL procedure successfully completed. 1 row created. Commit complete. PL/SQL procedure successfully completed. 1 row updated. Commit complete. SCOTT@book> select sql_id,executions,sql_fulltext c200 from v$sqlarea where lower(sql_text) like '%seq1.nextval%' and executions>=99; SQL_ID EXECUTIONS C200 ------------- ---------- -------------------------------------- bd62h0wujsfms 100 SELECT /*+ 2 */ SEQ1.NEXTVAL FROM DUAL 9cp836a3k67w2 100 SELECT SEQ1.NEXTVAL FROM DUAL --//注意看这回注解起作用了，大家可以注意一个细节PLSQL会全部转化为大写。oracle有时候真搞不懂。 --//看看其它语句的情况： set verify off host sleep $(echo &&3/50 | bc -l ) variable vmethod varchar2(20); exec :vmethod := '&&2'; insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ; commit ; declare v_id number; v_d date; v_val VARCHAR2(1000); l_count PLS_INTEGER; begin for i in 1 .. &&1 loop --//select value into v_val from v$nls_parameters where parameter = 'NLS_CHARACTERSET'; select /* &&3 */ value into v_val from nls_database_parameters where parameter = 'NLS_CHARACTERSET'; --//select sql_text into v_val from v$sql where rownum=1; --//SELECT /*+ &&3 */ seq1.NEXTVAL into v_id FROM DUAL; end loop; end ; / update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod; commit; --//quit SCOTT@book> @ m14.txt 300 PPP 3 PL/SQL procedure successfully completed. 1 row created. Commit complete. PL/SQL procedure successfully completed. 2 rows updated. Commit complete. SCOTT@book> select sql_id,executions,sql_fulltext c200 from v$sqlarea where lower(sql_text) like '%nls%' and executions>=299; SQL_ID EXECUTIONS C200 ------------- ---------- ------------------------------------------------------------------------------ 7mgsfc44trnr8 300 SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET' --//注解丢失。循环体内改为如下呢？ select /* &&3 */deptno into v_id from dept where deptno=10; SCOTT@book> @ m14.txt 400 OOO 4 SCOTT@book> select sql_id,executions,sql_fulltext c200 from v$sqlarea where lower(sql_text) like '%deptno%' and executions>=299; SQL_ID EXECUTIONS C200 ------------- ---------- --------------------------------------- a31kd5tkdvvmm 400 SELECT DEPTNO FROM DEPT WHERE DEPTNO=10 --//确实如此，以后测试中注意这个细节问题，注解里面加入+，PLSQL才不会取消注解内容。 4.补充测试： --//如果语句在sqlplus直接执行这样没有加号的注解是有效的。 SCOTT@book> select /* 1 */ * from dept where deptno=20; DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 41uk0b87mb1zq, child number 0 ------------------------------------- select /* 1 */ * from dept where deptno=20 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Plan hash value: 2852011669 ---------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | ---------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("DEPTNO"=20) --//注意看下划线注解并没有消失。 --//总之以后测试工作注意这个细节问题。