标签搜索

20180417PLSQL中sql语句格式化与注解问题

冰封一夏
2021-06-18 14:30:28 / 1 阅读 / 正在检测是否收录...

[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次.大家可以自行测试.

0

评论

博主关闭了所有页面的评论