ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle实验记录 (分析oracle硬解析&软解析&fast soft parse)

oracle实验记录 (分析oracle硬解析&软解析&fast soft parse)

原创 Linux操作系统 作者:fufuh2o 时间:2009-09-29 17:43:55 0 删除 编辑

解析步骤:
1.检查语法
2.检查sql语句涉及object是否存在,不存在则推出,检查需要数据字典 加载row cache
3.转换对象名,(例如翻译synonym 或实际名 test=xh.test),有 问题退出parse
4.检查发出sql的 user是否有对应权限,权限不足退出parse
5.根据object的 统计信息optimizer会建立一个 执行计划
6.将产生的执行计划装入shared pool 中library cache 中的heap
这六步完全执行就是hard parse

 

用户发一条语句 (例如select * from test)oracle将这条语句转为acsII数值,并进行利用 hash 函数进行 hash运算 需要传入hash函数 2个参数(name,namespace)
name 就是sql语句,namespace就是 'sqlarea' 对于SQL语句,计算出hash value(表示该语句被分派到library cache中 此hash bucket中) 然后到library cache 中 对应的hash

bucket中比较下 该bucket里是否存在该语句(是否是第一次运行)这个计算hash value及其搜索library cache找是否以前执行过 存在该语句(即可共享用)需要持有library

cache latch(闩latch是轻量级的串行化设备,用于协调对共享数据结构、对象和文件的多用户访问),找到了为soft parse,执行SQL语句的server process利用找到以前执行过

的sql语句跳过解析步骤中的几步并获取该SQL的执行计划 释放library cache latch 然后开始执行sql,没找到则需要hard parse 释放library cache latch 获得shared pool

latch(需要将SQL语句,执行计划写入 shared pool中library cache) 查找锁定 shared pool 中free space ,释放shared pool latch(主要控制shard pool中空间分配和回收)获

得library cache latch(保护cache在内存中的sql及其执行计划,要向library cache插入新sql及其执行计划时 需要library cache latch)开始解析 然后将sql及执行计划插

入library cache,释放library cache latch
执行sql 整个硬解析过程oracle会持有library cache(TOM:硬解析一个查询时,数据库会更长时间地占用一种低级串行化设备 latch) 所以hard parse非常影响latch 会造成长

时间持有造成latch 争用

 

soft parse:跳过解析步骤中的几步,但最后必须要使用共享的sql执行计划,如果产生新执行计划那么 就是hard parse


SQL> show parameter session_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     30


10G 这个参数default 为20 ,9I 为0
多了这个后oracle 执行不步骤就变了

在算出hash value后 oracle  会查找 该session的PGA有的话直接执行 避开所有parse


SQL语句的处理过程修正(参考http://www.itpub.net/thread-877110-1-5.html讨论 帖)

对照metalink给出的这个示意图,我们可以对SQL的处理过程作如下的描述:
1、检查是否有打开的游标,如果有,则直接通过游标link到位于PGA的private SQL AREA( private SQL area),转步骤11。否则,执行步骤2。
2、检查初始化参数SESSION_CACHED_CURSORS是否被设置,如果被设置,则同样可以通过游标指向到位于PGA的私有SQL AREA,转步骤11。否则执行步骤3。
3、检查HOLD_CURSOR以及RELEASE_CURSOR的设置。如果RELEASE_CURSOR=no(默认no),HOLD_CURSOR=yes(默认为no),当ORACLE执行完SQL语句,为private SQL AREA分配的内存

空间被保留,cursor和private SQL AREA之间的link也被保留,预编译程序不再使用它,同样可以通过这个指针直接在private SQL AREA获得语句,转步骤11。
    这上面的三种情况,实际上都没有作任何parse,都是直接从位于PGA中的private SQL AREA获得语句并直接执行。此为fast parse。
    这三种情况都不存在的情况下,oracle转到步骤4执行。
4、创建一个游标。
5、语法检查Syntax Check:检查语法书写是否正确,是否符合SQL Reference Manual中给出的SQL语法。
6、语义分析Semantic Analysis:查找数据字典,检查表、列是否正确,在所要求的对象上获取语法分析锁,使得在语句的语法分析过程中不改变这些对象的定义, 验证为存取所

涉及的模式对象所需的权限是否满足。
7、将语句转化成ASCII等效数字码,再通过散列算法得到散列值。
8、检查库缓存中是否存在同样hash值的语句。如果存在,转步骤11。否则,执行步骤9。 这就是soft parse。
9、选择执行计划。从可用的执行计划中选择一个最优的执行计划,其中包括存储大纲(srored outline)或物化视图(materialized view)相关的决定。
10、生成该语句的一个编译代码(p-code)。
11、执行语句。

 

 

 

 

SQL> show user
USER 为 "TR"


SQL> select distinct sid from v$mystat;

       SID
----------
       159


SQL> create table t1 (a int, b int);

表已创建。

SQL> declare
  2  begin
  3  for i in 1..1000 loop
  4  insert into t1 values(i,i+1);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute sys.dbms_stats.gather_table_stats('tr','t1');

PL/SQL 过程已成功完成。

 

SQL> conn xh/a123
已连接。
SQL> show user
USER 为 "XH"
SQL> select distinct sid from v$mystat;

       SID
----------
       141

SQL> create table t1 (a int, b int);

表已创建。

SQL> ed
已写入 file afiedt.buf

  1  declare
  2    begin
  3    for i in 1..1000 loop
  4   insert into t1 values(i,i+1);
  5    end loop;
  6    commit;
  7*  end;
SQL> /

PL/SQL 过程已成功完成。

SQL> execute sys.dbms_stats.gather_table_stats('tr','t1');

PL/SQL 过程已成功完成。


SQL> select table_name,owner from all_tables where table_name='T1';

TABLE_NAME                     OWNER
------------------------------ ------------------------------
T1                             XH
T1                             TR

 

SQL> alter system flush shared_pool;

系统已更改。

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1197
session cursor cache count                                               29
parse count (total)                                                     700
parse count (hard)                                                      115

 

SQL> select serial# from v$session where sid=159;

   SERIAL#
----------
         5

SQL> select serial# from v$session where sid=141;

   SERIAL#
----------
       190

 

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 过程已成功完成。

 

SQL> select * from t1 where a=1;(user tr)

         A          B
---------- ----------
         1          2

 


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               29~~~~~~~cache了29个cursor
parse count (total)                                                     713
parse count (hard)                                                      140~~~~~~~~产生hard parse

 

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     1489680637              0
SQL> col sql_text format a30
SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          1           1~~~~~~~~~执行1次


SQL> select * from t1 where a=1;(user tr)

         A          B
---------- ----------
         1          2

 

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          2           2~~~~~~~~~~~~执行2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               29
parse count (total)                                                     714~~~~~~~~总解析次数多1次 这次是soft parse
parse count (hard)                                                      140

 


SQL> select * from t1 where a=1;(user tr)

         A          B
---------- ----------
         1          2
SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          3           3~~~~~~~~~执行3次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1198
session cursor cache count                                               30~~~~~~~~ cache 了执行3次的 cursor
parse count (total)                                                     715~~~~~~~~~~~总解析次数多1次 这次是soft parse
parse count (hard)                                                      140


当某个session对相同cursor 进行3次访问 会在该session的PGA中创建一个标记,当游标关闭也不会换出library cache,此SESSION再执行相同的语句 会跳过所有hard parse
也不用soft parse ,这个为sofer soft parse or fast soft parse 真正得以共享

 


SQL> select * from t1 where a=1;(user tr)

         A          B
---------- ----------
         1          2

 


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          4           4~~~~~~~~~~~执行4次了 这次用的fast soft parse

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1199~~~~~~~~多了一次
session cursor cache count                                               30
parse count (total)                                                     716~~~~~~~~总解析多一次 ,这次是fast soft parse
parse count (hard)                                                      140

所以可以看出fast soft parse 包含在 parse count(total)中

SQL> select * from t1 where a=1;(user tr)

         A          B
---------- ----------
         1          2

 

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          5           5

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1200 ~多了一次
session cursor cache count                                               30
parse count (total)                                                     717~~~~~~~~总解析多一次 ,这次是fast soft parse
parse count (hard)                                                      140

 


SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc  d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 15:01:07 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

 


select *
from
 t1 where a=1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.29       0.36          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         40          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.29       0.36          0         40          0           5

Misses in library cache during parse: 1   可以看到执行5次一次为 hard parse ,fast soft parse 被算在 parse count中了
Optimizer mode: CHOOSE
Parsing user id: 67 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T1 (cr=8 pr=0 pw=0 time=92 us)

 

由于 fast soft parse可以 连soft parse也不用 ,所以它可以一定程度的提高DB性能

小结:
soft parse 2种情况 1是 一个session执行后,另一个session又执行(USER 是一样的 但SID 不一样)此时 不在产生执行计划 将共享执行计划,但还需要, 检查语句对象是否存

在,USER 是否有权限,同译词转换
2.是同一个SESSION 再次执行 相同SQL 会跳过几乎全部解析步骤,但还需要检查权限 因为有可能USER 权限已经改变 所以要检查

这2种只是跳过hard parse中部分步骤(最重要是跳过产生执行计划) 叫soft parse

fast soft parse:PGA中找, 找到后直接共享执行计划 真正避开hard parse

CURSOR CACHE HIT 计算  ~稍高点 比较好

session cursor cache hits / (parse count (total) - parse count (hard))


SQL> alter system flush shared_pool;

SQL> execute dbms_system.set_sql_trace_in_session(159,5,false);

PL/SQL 过程已成功完成。

SQL>

可以看到 解析 5次,执行5次, 其中一次为hard parse (Misses in library cache during parse: 1)


以上就是 hard parse, soft parse ,fast soft parse

 

看看子游标 与parse (与 shared pool library cache有关 会详细实验shared pool  library cache)

 

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1202
session cursor cache count                                               29
parse count (total)                                                     730
parse count (hard)                                                      165

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                44
session cursor cache count                                               30
parse count (total)                                                      81
parse count (hard)                                                       10

SQL> alter session set sql_trace=true;

会话已更改。


SQL> select * from t1 where a=1;(user tr SID 159)

         A          B
---------- ----------
         1          2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     743
parse count (hard)                                                      166~~~~~~~~~多了一次hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     1489680637              0

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          1           1~~~~~~~~~~~执行一次解析一次


SQL> select * from t1 where a=1;(SID 159)

         A          B
---------- ----------
         1          2


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 1          2           2~~~~~~~~~~执行2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744~~总解析多了一次 是soft parse
parse count (hard)                                                      166


SQL> select * from t1 where a=1;(user xh sid141)

         A          B
---------- ----------
         1          2

 

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=141;~~~由于SQL语句一样算出来hash value一样

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     1489680637              0

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 2          3           3~~~~~~~~~~~~~~~执行3次解析3次 ,注意 2个 子游标


SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1489680637;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a=1         0    2          2 TR~~~~~~~~~~~可以看到tr 执行2次 解析2次(1次soft parse,1 次hard parse)
select * from t1 where a=1         1    1          1 XH~~~~~~~~~~~xh 执行1次 解析1次 (1次hard parse )
oracle算出 hash value一样可以放入同一个 bucket(LIBRATY CACHE中bucket)但最后发现 引用了不同的表tr.t1,xh.t1 虽然语句一样 但执行计划可能不一样 所以oracle产生了

新的执行计划 放入这个bucket中 也就产生了一个子游标,由于产生了一个新的执行计划 肯定是一次hard parse(共享执行计划 才是soft parse)

 

 

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                45
session cursor cache count                                               30
parse count (total)                                                      93
parse count (hard)                                                       11~~~~~~~~~~~~~~多了一次hard parse

 


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati         SID 159的没变(与他没关系)
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744
parse count (hard)                                                      166

 

SQL> select * from t1 where a=1;(user xh sid141)

         A          B
---------- ----------
         1          2


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=141  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   15
session cursor cache hits                                                45~~未变
session cursor cache count                                               30
parse count (total)                                                      94~~~~~~~~~~~~总解析次数加1 是一次soft parse
parse count (hard)                                                       11~~未变~

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1489680637;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS~~~~~~~~~~~~~~该buckt中SQL执行4次 解析4次
------------------------------ ------------- ---------- -----------
select * from t1 where a=1                 2          4           4


SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1489680637;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a=1         0    2          2 TR
select * from t1 where a=1         1    2          2 XH~~~~~~~~~~~~~~~~~~~~~~~~~XH 执行 2次 解吸2次 1次soft 1次hard

存在子游标后 再有SESSION 执行对象 XH.T1的 都会到library cache中 存放SQL语句执行计划的bucket中 找到xh.t1的 子游标 使用它的执行计划 产生soft parse

 

 

执行计划的改变 对于SQL的影响

 


SQL> alter system flush shared_pool
  2  ;

系统已更改。
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1203
session cursor cache count                                               29
parse count (total)                                                     744
parse count (hard)                                                      166

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 过程已成功完成。
SQL> set autotrace trace exp
SQL> select * from xh.t1 where a>999;~~~~~~~~~~另一个SESSION 看下执行计划FTS
执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

 


SQL> select * from t1 where a>999; (SID 159 USER TR)

         A          B
---------- ----------
      1000       1001


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204
session cursor cache count                                               29
parse count (total)                                                     757
parse count (hard)                                                      191~~~~~~多了hard parse

 


SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     3166732479              0

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999               1          1           1

 

SQL> select * from t1 where a>999;(SID 159 USER TR)

         A          B
---------- ----------
      1000       1001

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999               1          2           2~~~~执行2次解析2次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204~~~cache hint没变
session cursor cache count                                               29
parse count (total)                                                     758~~总解析次数加1 那么是 soft parse
parse count (hard)                                                      191~~~~~~~~~~hard parse没变

 

 

SQL> select * from t1 where a>999;(sid 159 user tr)

         A          B
---------- ----------
      1000       1001

 

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999               1          3           3~~~~~~~~~~~执行3次

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1204
session cursor cache count                                               30~~~~~~~cache住了
parse count (total)                                                     759~~~~~~~~多了一次soft parse
parse count (hard)                                                      191

 


SQL> select * from t1 where a>999;

         A          B
---------- ----------
      1000       1001

 


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1205~~~现在已经用上fast soft parse了
session cursor cache count                                               30
parse count (total)                                                     760
parse count (hard)                                                      191~~没变


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999               1          4           4~~~~~~~~~~~~执行4次

 

SQL> create index trt1_ind on tr.t1(a);

索引已创建。


SQL> select * from tr.t1 where a>999;~~~~~~~~~~~使用 index

执行计划
----------------------------------------------------------
Plan hash value: 1183216686

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |

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

|   0 | SELECT STATEMENT            |          |     1 |     7 |     3   (0)| 00
:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |     7 |     3   (0)| 00
:00:01 |

|*  2 |   INDEX RANGE SCAN          | TRT1_IND |     1 |       |     2   (0)| 00
:00:01 |

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


SQL> select * from t1 where a>999;(SID 156 USER TR)

         A          B
---------- ----------
      1000       1001


SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=3166732479;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t1 where a>999               1          1           1~~~~执行计划发生了变化 替换了该子游标的执行计划  新执行计划装入了library cahce heap中没有 

                                                                    共享原有的执行计划 为HARD PARSE, 解析                                                    

                   1次( 为hard parse),执行1次

SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=3166732479;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t1 where a>999       0    1          1 TR        ~~~
对应着 这个子游标 将变回  hard parse


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1206
session cursor cache count                                               30
parse count (total)                                                     761
parse count (hard)                                                      192~~~~~hard parse 1次

 

 


SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc  d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 16:24:38 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

select *
from
 t1 where a>999


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.03       0.02          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.01          1         31          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.03       0.04          1         31          0           5

Misses in library cache during parse: 2
Optimizer mode: CHOOSE
Parsing user id: 67 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=85 us)


可以看到 执行5次,解析 5次          2次为 hard parse (其中一次为执行计划发生了变化 重新装入library cache heap中 造成 hard parse)
********** 所以可以看出hard parse与 执行计划有关系  SQL语句 PLAN 改变了会造成HARD PARSE************

 

 

~~看下加INDEX 但执行计划不变的情况
SQL> show user;
USER 为 "TR"
SQL> create table t2(a int, b int);

  1  declare
  2  begin
  3  for i in 1..1000 loop
  4  insert into t2 values (i,i+1);
  5  end loop;
  6  commit;
  7* end;
  8  /

PL/SQL 过程已成功完成。

SQL> execute dbms_stats.gather_table_stats('tr','T2');

PL/SQL 过程已成功完成。

SQL> execute dbms_system.set_sql_trace_in_session(159,5,true);

PL/SQL 过程已成功完成。


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549
session cursor cache count                                               30
parse count (total)                                                    1007
parse count (hard)                                                      320

 

SQL> select * from t2 where a>100;(sid 159 user tr)

 

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549~~~没变
session cursor cache count                                               30
parse count (total)                                                    1015
parse count (hard)                                                      321~~~一次hard parse

SQL> select PREV_HASH_VALUE,sql_HASH_VALUE from v$session where sid=159;

PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
     1871633534              0

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100               1          1           1~~~~~~~~~~~解析一次执行一次


 
SQL> select * from t2 where a>100;

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100               1          2           2

SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549~~~没变
session cursor cache count                                               30
parse count (total)                                                    1016~~~总解析次数变了 是一次soft parse
parse count (hard)                                                      321~~~没变

 

SQL> select * from tr.t2 where a>100;

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   901 |  6307 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   901 |  6307 |     3   (0)| 00:00:01 |~~~~~~~~~~~~~此时计划是FTS
--------------------------------------------------------------------------

 


SQL> create index ind_t2 on tr.t2(a);~~加了一个INDEX

索引已创建。

SQL> select * from tr.t2 where a>100;

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   901 |  6307 |     3   (0)| 00:00:01 |~~~~~~~~~PLAN 没变
|*  1 |  TABLE ACCESS FULL| T2   |   901 |  6307 |     3   (0)| 00:00:01 |


SQL> select * from t2 where a>100;


SQL> select b.name,a.value from v$sesstat a,v$statname b where a.statistic#=b.stati
stic# and a.sid=159  and b.name in('parse count (total)','parse count (hard)','open
ed cursors current','session cursor cache hits','session cursor cache count');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors current                                                   22
session cursor cache hits                                              1549
session cursor cache count                                               30
parse count (total)                                                    1017
parse count (hard)                                                      322~~~~~~~~多了一次hard parse

SQL> select  SQL_TEXT,VERSION_COUNT,EXECUTIONS,PARSE_CALLS from v$sqlarea where has
h_value=1871633534;

SQL_TEXT                       VERSION_COUNT EXECUTIONS PARSE_CALLS
------------------------------ ------------- ---------- -----------
select * from t2 where a>100               1          1           1~~~执行1次 1次hard parse


SQL> select sql_text,to_char(child_number,'xxxx') childnum,to_char(parse_calls,'xxx
')parses,executions,PARSING_SCHEMA_NAME from v$sql where hash_value=1871633534;

SQL_TEXT                       CHILD PARS EXECUTIONS PARSING_SC
------------------------------ ----- ---- ---------- ----------
select * from t2 where a>100       0    1          1 TR

 


SQL> host tkprof D:\oracle\product\10.2.0\admin\xh\udump\xh_ora_37116.trc  d:\t1par
se.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期二 9月 29 17:14:28 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


select *
from
 t2 where a>100


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.01          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch      183      0.00       0.00          0        201          0        2700
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      189      0.01       0.02          0        201          0        2700

Misses in library cache during parse: 2
Optimizer mode: CHOOSE
Parsing user id: 67 

Rows     Row Source Operation             一共解析3次,2次hard parse
-------  ---------------------------------------------------
    900  TABLE ACCESS FULL T2 (cr=67 pr=0 pw=0 time=2740 us)

分析:与上例加入INDEX执行计划改变一样,当加入一个INDEX 时,oracle 会根据统计信息尝试进行计算INDX SCNA 的COST,计算FTS 的COST,将其结果比较 选择最优 此例中为FTS 然

后将最优 PLAN 装入 LIBRARY CACHE HEAP中,虽然这个PLAN 还是FTS 不过是最新经过比较后产生的PLAN 并将其重新装入了 LIBRARY CACHE HEAP中(相当于执行计划发生了变化),

替换了原有的执行计划 没有共享原有的执行计划 为 HARD PARSE

 

 

 


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-615870/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    182
  • 访问量
    426821