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

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

--//注意看下劃線註解並沒有消失。
--//總之以後測試工作注意這個細節問題。