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