20180417PLSQL中sql語句格式化與註解問題

[20180417]PLSQL中sql語句格式化與註解問題.txt

--//以前的測試:http://blog.itpub.net/267265/viewspace-748190/
--//今天發現一些註解也被過濾掉,還是透過例子來說明問題.

1.測試環境:
SCOTT@book> @ &r/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

SCOTT@book> select * from dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

2.建立測試例子:
create or replace procedure test_bind
as
v_deptno  number;
v_dname   varchar2(14);
v_loc     varchar2(13);
v_deptno1  number;
v_dname1   varchar2(14);
v_loc1     varchar2(13);
cursor c_dept is select deptno,dname from dept order by deptno;
begin
open c_dept;
loop
fetch c_dept into v_deptno,v_dname;
exit when c_dept%NOTFOUND;
select loc into v_loc from dept where deptno=v_deptno and dname=v_dname;

    Select loc into v_loc from dept
where deptno=v_deptno
and dname=v_dname;

    v_deptno1 := v_deptno;
    v_dname1  := v_dname;

    Select loc into v_loc from dept where deptno=v_deptno1
and dname=v_dname;

Select loc
into v_loc from dept where deptno=v_deptno
and dname=v_dname1;

Select
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select
loc
--this is laji
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  --this is a test!!
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  /* this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  --+rule
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  /*+ this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

end loop;
end;
/

3.測試:
SCOTT@book> @ &r/10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@book> exec test_bind
PL/SQL procedure successfully completed.

SCOTT@book> @ &r/10046off
Session altered.

4.使用tkprof:
$ tkprof /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_16419.trc
output = aa
TKPROF: Release 11.2.0.4.0 - Development on Tue Apr 17 10:41:12 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

--//分析輸出檔案aa.prf:
********************************************************************************

SQL ID: fq1jkwcmsx57d Plan Hash: 2852011669

SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        8      0.00       0.00          0          0          0           0
Execute     32      0.00       0.00          0          0          0           0
Fetch       32      0.00       0.00          0         64          0          32
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       72      0.00       0.00          0         64          0          32

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 8

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=15 us cost=1 size=20 card=1)
         1          1          1   INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=9 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************
--//你可以發現分析8次,而執行了32次(迴圈4次).也就是每次迴圈執行8次這條語句.
--//很明顯你可以發現plsql格式化了sql語句(變成了大寫),並且繫結變數被換成了:B1 :B2.
--//一些註解像
--this is a test!!
--this is laji
/* this is a test!! */
--//也被過濾掉.

SELECT --+rule
LOC FROM DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          8          0           4

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=27 us)
         1          1          1   INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=7 us)(object id 87107)

********************************************************************************

SQL ID: 88p9k1j3c3c71 Plan Hash: 2852011669

SELECT /*+ this is a test!! */ LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          8          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0          8          0           4

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=16 us cost=1 size=20 card=1)
         1          1          1   INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=7 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************

--//再看看給指令碼加註解的情況:
Select
loc
--this is laji
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  --this is a test!!
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

Select  /* this is a test!! */
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

--//這3條語句實際上註解被過濾了.變成了
SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

--//而2種特殊的註解被保留下來,就是開頭有加號的註解
SELECT --+rule
LOC FROM DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

SELECT /*+ this is a test!! */ LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

--//並且這個加號必須寫成這樣著/*+ 或者 --+ 才不會過濾.也就是這樣寫裡面的提示才有效.
--//注:我補充測試如下寫的情況,修改如下:
Select  -- +rule
loc
into v_loc1 from dept where deptno=v_deptno1
and dname=v_dname1;

********************************************************************************

SQL ID: fq1jkwcmsx57d Plan Hash: 2852011669

SELECT LOC
FROM
DEPT WHERE DEPTNO=:B2 AND DNAME=:B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        9      0.00       0.00          0          0          0           0
Execute     36      0.00       0.00          0          0          0           0
Fetch       36      0.00       0.00          0         72          0          36
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       81      0.00       0.00          0         72          0          36

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 83     (recursive depth: 1)
Number of plan statistics captured: 9

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=14 us cost=1 size=20 card=1)
         1          1          1   INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=8 us cost=0 size=0 card=1)(object id 87107)

********************************************************************************

--//這樣sql_id=fq1jkwcmsx57d的分析次數變成9次.大家可以自行測試.