[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;
