[20170703]SQL语句分析执行过程.txt-白红宇

[20170703]SQL语句分析执行过程.txt

--//正常sql select语句执行需要这些过程,create cursor,parse,execute and fetch.

--//dml估计缺少fetch步骤.参考vage的书写的例子,原书的例子存在问题,理解如下脚本对于sql语句如何执行很有益处.

--//当然正常的编程很少有人这样写代码的.

DECLARE

mcur NUMBER;

mstat NUMBER;

v_name VARCHAR2 (14);

BEGIN

mcur := DBMS_SQL.open_cursor;

DBMS_SQL.parse ( mcur ,'select dname from dept where deptno = :deptno' ,DBMS_SQL.native);

DBMS_SQL.bind_variable (mcur, ':deptno', 20);

DBMS_SQL.define_column ( mcur ,1 ,v_name ,14);

mstat := DBMS_SQL.execute (mcur);

mstat := DBMS_SQL.fetch_rows (mcur);

DBMS_SQL.COLUMN_VALUE (mcur, 1, v_name);

DBMS_OUTPUT.put_line ('查询结果:' || v_name);

DBMS_SQL.close_cursor (mcur);

END;

--//vage书有错,难查,理解上面对oracle如何执行sql语句有很好的理解.

--//首先

--//打开游标.

--//分析语句

--//确定绑定变量

--//定义字段的输出

--//执行

--//提取

--//传给变量

--//输出结果

--//关闭游标.

--//自己通过一些例子跟踪学习看看.另外看看ddl的语句的情况.估计仅仅存在建立cursor以及分析.

0.环境:

SCOTT@test01p> @ ver1

PORT_STRING VERSION BANNER CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

1.测试1:

--//drop table test1;

@ 10046on 12

create table test1(col1 number);

@ 10046off

--//检查跟踪文件:

=====================

PARSING IN CURSOR #390604240 len=31 dep=0 uid=109 oct=1 lid=109 tim=5919603195 hv=3361099048 ad='7ff14e084e8' sqlid='9ukzrsg45cm98'

create table test1(col1 n

END OF STMT

PARSE #390604240:c=15600,e=35082,p=0,cr=95,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=5919603194

=====================

--//你可以发现ddl语句仅仅存在PARSE,继续测试.

2.测试2:

--//我采用单步执行,这样好分析问题.看了一些文档要单步执行,需要设置如下参数才行.

SYS@test> alter system set "_dbms_sql_security_level" = 384 scope=spfile;

System altered.

--//注测试好几次报错,看了一些介绍.要打开看看.重启数据库.

SCOTT@test01p> variable x1 number

SCOTT@test01p> exec dbms_sql.parse ( :x1 ,'create table test2(col1 number)',dbms_sql.native);

BEGIN dbms_sql.parse ( :x1 ,'create table test2(col1 number)',dbms_sql.native); END;

ERROR at line 1:

ORA-01001: invalid cursor

ORA-06512: at "SYS.DBMS_SQL", line 1120

ORA-06512: at line 1

--//可以发现报错.提示ORA-01001: invalid cursor.

3.测试3:

@ 10046on 12

variable x1 number

exec : x1:=dbms_sql.open_cursor;

exec dbms_sql.parse ( :x1 ,'create table test2(col1 number)',dbms_sql.native);

desc test2;

SCOTT@test01p> desc test2

Name Null? Type

----- -------- -------------

COL1 NUMBER

--//你可以发现分析执行完成,表就建立好了.

@ 10046off

--//检查跟踪文件:

=====================

PARSING IN CURSOR #184955136 len=86 dep=0 uid=109 oct=47 lid=109 tim=8626211826 hv=3290462973 ad='7ff116d9900' sqlid='3vxq07v220yrx'

BEGIN dbms_sql.parse ( :x1 ,'create table test2(col1 number)',dbms_sql.native); END;

END OF STMT

PARSE #184955136:c=0,e=566,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=8626211825

BINDS #184955136:

Bind#0

oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0

kxsbbbfp=0b3f8198 bln=22 avl=06 flg=05

value=564855601

XCTEND rlbk=0, rd_only=1, tim=8626214121

=====================

PARSING IN CURSOR #184944496 len=31 dep=1 uid=109 oct=1 lid=109 tim=8626214476 hv=2512661138 ad='7ff12b52f40' sqlid='9114rc6aw8ank'

create table test2(col1 n

END OF STMT

PARSE #184944496:c=0,e=766,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=8626214475

=====================

4.测试4:

@ 10046on 12

Select * from dept where deptno=10;

@ 10046on off

--//查看跟踪文件:

=====================

PARSING IN CURSOR #188283872 len=34 dep=0 uid=109 oct=3 lid=109 tim=9009592446 hv=3496103236 ad='7ff118e1940' sqlid='3vf78dv864ma4'

Select * from dept where deptno=10

END OF STMT

PARSE #188283872:c=15600,e=74701,p=4,cr=93,cu=0,mis=1,r=0,dep=0,og=1,plh=2852011669,tim=9009592445

EXEC #188283872:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2852011669,tim=9009592677

WAIT #188283872: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=9009592766

WAIT #188283872: nam='Disk file operations I/O' ela= 387 FileOperation=2 fileno=9 filetype=2 obj#=92286 tim=9009593260

WAIT #188283872: nam='db file sequential read' ela= 16007 file#=9 block#=155 blocks=1 obj#=92286 tim=9009609339

WAIT #188283872: nam='db file sequential read' ela= 9259 file#=9 block#=133 blocks=1 obj#=92285 tim=9009618776

FETCH #188283872:c=0,e=26217,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2852011669,tim=9009619030

STAT #188283872 id=1 cnt=1 pid=0 pos=1 obj=0 op='RESULT CACHE f91jjq15gk0g4bf7sn0b4r8bvj (cr=2 pr=2 pw=0 time=26208 us)'

STAT #188283872 id=2 cnt=1 pid=1 pos=1 obj=92285 op='TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=2 pw=0 time=26164 us cost=1 size=20 card=1)'

STAT #188283872 id=3 cnt=1 pid=2 pos=1 obj=92286 op='INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=1 pw=0 time=16650 us cost=0 size=0 card=1)'

WAIT #188283872: nam='SQL*Net message from client' ela= 735 driver id=1413697536 #bytes=1 p3=0 obj#=92285 tim=9009619966

FETCH #188283872:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2852011669,tim=9009620048

WAIT #188283872: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=92285 tim=9009620094

*** 2017-07-02 22:03:14.586

WAIT #188283872: nam='SQL*Net message from client' ela= 3618583 driver id=1413697536 #bytes=1 p3=0 obj#=92285 tim=9013238715

CLOSE #188283872:c=0,e=18,dep=0,type=0,tim=9013239293

=====================

--//经历PARSE,EXEC,FETCH,CLOSE等步骤.

5.测试5:

variable y1 number

variable z number

variable v_name varchar2(14);

exec :y1 := dbms_sql.open_cursor;

exec dbms_sql.parse ( :y1 ,'select dname from dept where deptno=:deptno',dbms_sql.native);

exec DBMS_SQL.bind_variable ( :y1, ':deptno', 30);

exec DBMS_SQL.define_column ( :y1 ,1 ,:v_name ,14);

exec :z := dbms_sql.execute (:y1);

exec :z := DBMS_SQL.fetch_rows (:y1);

exec DBMS_SQL.COLUMN_VALUE (:y1,1, :v_name);

exec DBMS_OUTPUT.put_line ('查询结果:' || :v_name);

exec DBMS_SQL.close_cursor (:y1);

--//我这里仅仅涉及如下:

--//打开游标.

--//分析语句

--//确定绑定变量

--//定义字段的输出

--//执行

--//提取

--//传给变量

--//输出结果

--//关闭游标.

--//总结:

1.仅仅了解一些细节.

2.另外ddl执行仅仅需要2个步骤,open cursor,parse.

--//补充在11g下测试,单步跟踪分析看看:

--//首先确定执行语句的sql_id='9nv50pp4yjghd'.要退出在刷新共享池.

--//注单步执行也需要设置 alter system set "_dbms_sql_security_level" = 384 scope=spfile;.

variable y1 number;

variable z number;

variable v_name varchar2(14);

exec :y1 := dbms_sql.open_cursor;

exec dbms_sql.parse ( :y1 ,'select dname from dept where deptno=:deptno',dbms_sql.native);

exec DBMS_SQL.bind_variable ( :y1, ':deptno', 30);

exec DBMS_SQL.define_column ( :y1 ,1 ,:v_name ,14);

exec :z := dbms_sql.execute (:y1);

exec :z := DBMS_SQL.fetch_rows (:y1);

exec DBMS_SQL.COLUMN_VALUE (:y1,1, :v_name);

exec DBMS_OUTPUT.put_line ('查询结果:' || :v_name);

exec DBMS_SQL.close_cursor (:y1);

SYS@book> @ &r/sharepool/shp4 9nv50pp4yjghd 0

no rows selected

--//当执行exec dbms_sql.parse ( :y1 ,'select dname from dept where deptno=:deptno',dbms_sql.native);时,再执行:

SYS@book> @ &r/sharepool/shp4 9nv50pp4yjghd 0

TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09

-------------- ---------------- ---------------- ---------------------------------------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------

子游标句柄地址 000000007DE2E290 000000007D395C40 select dname from dept where deptno=:dep 0 000000007DE2E1D8 000000007DE2EB40 4528 0 3100 7628 7628 1239989773 9nv50pp4yjghd 0

父游标句柄地址 000000007D395C40 000000007D395C40 select dname from dept where deptno=:dep 0 000000007D395B88 00 4736 0 0 4736 4736 1239989773 9nv50pp4yjghd 65535

--//你可以发现在分析后,父子光标句柄已经建立.

SYS@book> select sql_id,sql_text,executions,parse_calls from v$sql where sql_id='9nv50pp4yjghd';

SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS

------------- ------------------------------------------------------------ ---------- -----------

9nv50pp4yjghd select dname from dept where deptno=:deptno 0 1

--//执行次数是0.

--//当执行exec :z := dbms_sql.execute (:y1);时,再执行:

SYS@book> select sql_id,sql_text,executions,parse_calls from v$sql where sql_id='9nv50pp4yjghd';

SQL_ID SQL_TEXT EXECUTIONS PARSE_CALLS

------------- ------------------------------------------------------------ ---------- -----------

9nv50pp4yjghd select dname from dept where deptno=:deptno 1 1

--//执行次数是1.说明已经执行.

--//在执行exec DBMS_SQL.close_cursor (:y1);前,清除共享池看看.

SYS@book> alter system flush shared_pool ;

System altered.

SYS@book> @ &r/sharepool/shp4 9nv50pp4yjghd 0

old 18: WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2

new 18: WHERE kglobt03 = '9nv50pp4yjghd' or kglhdpar='9nv50pp4yjghd' or kglhdadr='9nv50pp4yjghd' or KGLNAHSH= 0

TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09

子游标句柄地址 000000007DE2E290 000000007D395C40 select dname from dept where deptno=:dep 1 00 00 0 0 3100 3100 3100 1239989773 9nv50pp4yjghd 0

父游标句柄地址 000000007D395C40 000000007D395C40 select dname from dept where deptno=:dep 1 000000007D395B88 00 4736 0 0 4736 4736 1239989773 9nv50pp4yjghd 65535

SYS@book> select sql_id,sql_text,executions,parse_calls from v$sql where sql_id='9nv50pp4yjghd';

no rows selected

--//说明子光标已经清除,而父光标还在.继续执行exec DBMS_SQL.close_cursor (:y1);.

SCOTT@book> BEGIN DBMS_SQL.close_cursor (:y1); END;

ERROR at line 1:

ORA-01001: invalid cursor

ORA-06512: at "SYS.DBMS_SQL", line 1192

ORA-06512: at line 1

--//报错,因为这个时候子光标已经清除了.父光标还在.

--//在执行exec DBMS_SQL.close_cursor (:y1);后,清除共享池看看.

SYS@book> @ &r/sharepool/shp4 9nv50pp4yjghd 0

TEXT KGLHDADR KGLHDPAR C40 KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09

子游标句柄地址 000000007DE2E290 000000007D395C40 select dname from dept where deptno=:dep 1 00 00 0 0 3100 3100 3100 1239989773 9nv50pp4yjghd 0

父游标句柄地址 000000007D395C40 000000007D395C40 select dname from dept where deptno=:dep 1 000000007D395B88 00 4736 0 0 4736 4736 1239989773 9nv50pp4yjghd 65535

--//刷新前父游标还在.

SYS@book> alter system flush shared_pool ;

System altered.

SYS@book> @ &r/sharepool/shp4 9nv50pp4yjghd 0

no rows selected

--//父游标在刷新共享池后清除.

--//附上shp4.sql脚本:

column N0_6_16 format 99999999

SELECT DECODE (kglhdadr,

kglhdpar, '父游标句柄地址',

'子游标句柄地址')

text,

kglhdadr,

kglhdpar,

substr(kglnaobj,1,40) c40,

kglhdivc,

kglobhd0,

kglobhd6,

kglobhs0,kglobhs6,kglobt16,

kglobhs0+kglobhs6+kglobt16 N0_6_16,

kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,

kglnahsh,

kglobt03 ,

kglobt09

FROM x$kglob

WHERE kglobt03 = '&1' or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;