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

[20170703]SQL語句分析執行過程.txt

相關文章