|
解析步骤:
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语句的serverprocess利用找到以前执行过
的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./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
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
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
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
|
|
|
来自： wghbeyond > 《oracle_sql》
