[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)

--//注意看下划线注解并没有消失。
--//总之以后测试工作注意这个细节问题。