20180417PLSQL中sql语句格式化与注解问题-白红宇

[20180417]PLSQL中sql语句格式化与注解问题.txt

--//以前的测试:

--//今天发现一些注解也被过滤掉,还是通过例子来说明问题.

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

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

--//分析输出文件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次.大家可以自行测试.