环境:OEL+Oracle 10.2.0.5 Rac
今天在itpub上回答一个网友的提问,RT:我第一次执行了一条sql之后,这条sql通过了硬解析,得到了执行计划,当再次执行这条sql时,会进行软解析是吧,不会通过优化器得到新的执行计划。如果我增加了一条索引,通过索引执行这条sql更好,在执行这条sql是进行软解析吗?(统计信息的改变,会导致sql进行硬解析吗?)
我当时的回答是:有索引了,统计信息变了。走索引了,执行计划变了。 但是软硬解析是对于SQL语句而言的吧?只要共享池中存在此SQL副本,将直接执行软解析;个人认为未经analyze表前,会被软解析
答案应该是硬解析;
中午午休的时候,趴那儿回顾了一下这个案例,但是思前想后总感觉有点不对,一切以事实说话,决定起来测试一下;测试过程和结果如下:
SQL> show user
USER is "SYS"
SQL> drop index tt_idx;
drop index tt_idx
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> drop table tt purge;
drop table tt purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
创建一张新表TT:
SQL> create table tt as select * from dba_objects;
Table created.
打开autotrace并开始第一次根据条件检索新表tt:
SQL> set autotrace on;
SQL> select object_id,object_name from tt where object_id=10;
OBJECT_ID OBJECT_NAME</span>
---------- --------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
68 recursive calls
0 db block gets
785 consistent gets
701 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
可以很清晰的从执行计划中看出,优化器走了全表扫描,并执行了硬解析;
再次执行上述查询:
SQL> select object_id,object_name from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- ---------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
707 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
执行计划虽依然走全表扫描,但是执行了软解析,这里的主要原因是在共享池中存在同样的SQL语句的一个副本(两条语句一模一样)
SQL> select object_id,OBJECT_NAME from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- -------------------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
779 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select OBJECT_ID,OBJECT_NAME from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- ----------------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 156 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TT | 8 | 632 | 156 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
779 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
如上,SQL语句不同,便会执行硬解析。
ps:由于没有对表TT做分析,所以以上执行计划中的ROWS值为8而不是1(只要一条返回记录);
继续测试:
在表TT的object_id列添加索引TT_IDX,并执行上面一样的SQL语句进行检索:
SQL> create index tt_idx on tt(object_id);
Index created.
SQL>
SQL> select object_id,object_name from tt where object_id=10;
OBJECT_ID OBJECT_NAME
---------- ----------------------------------------
10 C_USER#
Execution Plan
----------------------------------------------------------
Plan hash value: 2072537773
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TT_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
79 consistent gets
1 physical reads
0 redo size
481 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
实验证明,虽然该条语句跟在之前执行过,并且SQL共享区有该语句的副本,但是依然执行了硬解析,且优化器走了索引;
在10g及以上版本中,如果表没有经过分析,oracle 自动通过动态采样的方式来收集分析数据。
--------------------------------------------------------------------------------------------
版权所有,转载请注明作者及原文链接,否则追究法律责任!
QQ: 584307876
作者: Seven
原文链接:http://blog.csdn.net/sunjiapeng/article/details/10592205
邮箱: seven_ginna@foxmail.com