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

时间: 2024-10-22 10:50:06

[20170703]SQL语句分析执行过程.txt的相关文章

ORACLE数据库SQL语句的执行过程

SQL语句在 数据库中处理过程是怎样的呢?执行顺序呢?在回答这个问题前,我们先来回顾一下:在ORACLE数据库系统架构下,SQL语句由用户进程产生,然后传到相 对应的服务端进程,之后由服务器进程执行该SQL语句,如果是SELECT语句,服务器进程还需要将执行结果回传给用户进程. SQL语句的执行过程一般如下: 解析(PARSE)-- 绑定(BIND)--执行(EXECUTE)--提取(FETCH 只有SELECT才需要这步) 解析   服务器进程接收到一个SQL语句时,首先要将其转换成执行这个S

[20120104]稳定一条sql语句的执行计划.txt

[20120104]稳定一条sql语句的执行计划.txt http://www.itpub.net/thread-1495845-1-1.htmlhttp://space.itpub.net/267265/viewspace-723066 ORACLE8I升级11G R2后,查询系统视图特别慢 我的测试版本:SQL> select * from v$version where rownumBANNER------------------------------------------------

通过分析SQL语句的执行计划优化SQL(二)

优化|语句|执行 第5章 ORACLE的执行计划 背景知识:        为了更好的进行下面的内容我们必须了解一些概念性的术语: 共享sql语句    为了不重复解析相同的SQL语句(因为解析操作比较费资源,会导致性能下降),在第一次解析之后,ORACLE将SQL语句及解析后得到的执行计划存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个

xml-存储过程执行SQL与Print SQL语句单独执行结果不同

问题描述 存储过程执行SQL与Print SQL语句单独执行结果不同 代码如下: DECLARE @SQLEX1 nVARCHAR(MAX) DECLARE @TempEX1 VARCHAR(MAX) SELECT @SQLEX1 = '' SELECT @TempEX1 = '' SET @SQLEX1 = 'SELECT @Temp3 = A.T FROM (SELECT T = REPLACE((SELECT DISTINCT ''20'' AS ''machine/@id1'',1 AS

《Oracle数据库管理与维护实战》—— 2.10 SQL语句的处理过程

2.10 SQL语句的处理过程 Oracle数据库管理与维护实战Oracle数据库是关系数据库,Oracle用SQL语言对数据库进行操作.了解SQL语句的处理过程,能更深一步地了解Oracle的内部运行机制. 2.10.1 SQL 语句的处理过程 Oracle中,所有的SQL语句都分三个阶段进行处理:语法分析.执行.返回结果.无论何种工具(如:Oracle Form .Oracle Reports),都要将语句传递到Oracle进行处理.下面我们简单分析这三个阶段. 2.10.2 分析 分析是处

oracle-怎样验证spool中的sql语句是否执行成功

问题描述 怎样验证spool中的sql语句是否执行成功 在linux环境下,执行一个sh脚本,里面包含了spool,里面包含查询语句,将查询结果指定到了一个txt文件,然后我需要知道这个语句是否执行成功.然后再导到另外的日志txt里面去. 求大神解决,或者推荐别的方法. 解决方案 判断SQL语句是否执行成功

探讨JavaScript语句的执行过程_javascript技巧

废话不多说,直奔主题了.javascript的运行原理总结如下: 1.按照html文档流顺序执行javascript代码 浏览器是按照文档流从上到下逐步解析页面结构和信息的,javascript代码作为嵌入的脚本作为html文档的组成部分,所以javascript代码在加载时的执行顺序也是根据脚本标签<script>的出现顺序来确定的. 如果通过脚本标签<script>的src属性来引入外部.js文件,那么它也将按照其语句出现的顺序来执行,而且执行过程是文档加载的一部分.不会因为是

sql-MS SQL如何在执行过程中知道有多少用户变量.

问题描述 MS SQL如何在执行过程中知道有多少用户变量. 在一些语句对SQL的表进行增删改操作时,我想通过触发器把系统和用户操作的变量名称搞个清单列出来.新手求助,谢谢! 解决方案 楼主的意思是想知道执行的sql语句有多少个条件参数?这个还是别用触发器了,在业务逻辑层解析是不是会更好点 解决方案二: 现在情况程序就是在业务逻辑层有变量传入到SQL语句中执行,在追踪中只能拿到追查到绝对值,我想找到这个变量,从而通过触发器得到其变量值,进而引用.

[20160215]超长sql语句与父子光标.txt

[20160215]超长sql语句与父子光标.txt --看<oracle内核技术揭秘>提到sql语句不会进入保留池, 要进入保留池,chunk的大小必须大于_shared_pool_reserved_min_alloc. --而实际上许多sql语句一般最大4096字节.而且这些内存分配的原则是首先从共享池分配,不行并且大于 --shared_pool_reserved_min_alloc才会从保留池申请. --作者视乎忘记一种特殊情况就是超长sql语句,这样父游标保存sql的语句chunk可