parse time cpu 2412
parse time elapsed 4792
parse count (total) 62015
parse count (hard) 8059
parse count (failures) 64
BALLONTT@PROD> select count(*) from t where id='b';
COUNT(*)
----------
20000
BALLONTT@PROD> select name,value from v$sysstat where name like '%parse%';
NAME VALUE
----------------------------------------- ----------
parse time cpu 2412
parse time elapsed 4792
parse count (total) 62017
parse count (hard) 8060
parse count (failures) 64
硬解析次数加1
BALLONTT@PROD> select sql_text,sql_id,child_number,executions from v$sql where sql_text like 'select count(*) from t where id%';
SQL_TEXT SQL_ID CHILD_NUMBER EXECUTIONS
----------------------------------------- ---------------- ------------- ------------
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0 1
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1 1
BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_Text like 'select count(*) from t where id%';
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS
----------------------------------------------- ------------- ------------- ---------
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 2 2
使用了绑定变量，但是两次查询生成了两个子游标(child cursor)，即对应两个执行计划。
为了更清楚地看到两次sql使用了两个不同的子游标，即对应着使用了两个执行计划，我们再来分步查询一次：
BALLONTT@PROD> select count(*) from t where id='d';
COUNT(*)
----------
6
BALLONTT@PROD> select sql_text,sql_id,child_number from v$sql where sql_text like '%select count(*) from t where id%';
SQL_TEXT SQL_ID CHILD_NUMBER
----------------------------------------------- ------------- -------------
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0
查看执行计划：
BALLONTT@PROD> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID g82ztj8p3q174, child number 0
-------------------------------------
select count(*) from t where id=:"SYS_B_0"
Plan hash value: 3666266488
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IND_ID | 1 | 2 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
执行计划为索引路径
再次查询：
BALLONTT@PROD> select count(*) from t where id='b';
COUNT(*)
----------
20000
BALLONTT@PROD> select sql_text,sql_id,child_number from v$sql where sql_text like 'select count(*) from t where id%'
2 ;
SQL_TEXT SQL_ID CHILD_NUMBER
----------------------------------------------- ------------- -------------
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 0
select count(*) from t where id=:"SYS_B_0" g82ztj8p3q174 1
查看子游标（chilid_number=1）的执行计划：
BALLONTT@PROD> select * from table(dbms_xplan.display_cursor('g82ztj8p3q174',1,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID g82ztj8p3q