测试结论
1,每次运行附加gather_plan_statistics hint，执行计划的a-rows相关指标一直在递增
原因在于这个a_rows与starts相关,starts即执行计划当前步骤的累计运行次数，
故此，SQL运行多次，starts会一直递增，所以a_rows也会随之递增
2,通过gather_plan_statistics hint的starts可以清晰知道当前步骤运行的次数，
如果基于hash join,merge join,nested loop，可以更好了解这些2表关联方式的运行机制
这样就可以知道到底SQL执行计划哪步有问题，进而采取针对性的措施
3, select * from table(dbms_xplan.display_cursor(null,null,'iostats’))中的最后参数远项iostats或者其它参数值
allstats last是返回SQL上次执行的相关性能指标
iostats是返回SQL累积执行的相关性能指标
最终呈现的形式不同，具体请参见官方手册
4,通过exists关联2表，默认子查询展开，直接2表关联
如强制在子查询内表使用UN_UNNEST，则二表关联后采用FILTER
测试明细
SQL> create table t_s1(a int,b int);
Table created.
SQL> create table t_s2(a int,b int);
Table created.
SQL> insert into t_s1 select level,level from dual connect by level<=3;
3 rows created.
SQL> insert into t_s2 select level,level from dual connect by level<=3;
3 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_s1 where b in (select b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from t_s1 where b in (select b from t_s2);
Execution Plan
----------------------------------------------------------
Plan hash value: 3214991949
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 117 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_S1 | 3 | 78 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_S2 | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"="B")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> select * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
Execution Plan
----------------------------------------------------------
Plan hash value: 25546918
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_S1 | 3 | 78 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_S2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_S2" "T_S2"
WHERE "B"=:B1))
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 1
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | | 33 |00:00:00.01 | 319 |
|* 1 | FILTER | | 11 | | 33 |00:00:00.01 | 319 |
| 2 | TABLE ACCESS FULL| T_S1 | 11 | 3 | 33 |00:00:00.01 | 88 |
|* 3 | TABLE ACCESS FULL| T_S2 | 33 | 1 | 33 |00:00:00.01 | 231 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 1
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | | 36 |00:00:00.01 | 348 |
|* 1 | FILTER | | 12 | | 36 |00:00:00.01 | 348 |
| 2 | TABLE ACCESS FULL| T_S1 | 12 | 3 | 36 |00:00:00.01 | 96 |
|* 3 | TABLE ACCESS FULL| T_S2 | 36 | 1 | 36 |00:00:00.01 | 252 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 6 |00:00:00.01 | 58 |
|* 1 | FILTER | | 2 | | 6 |00:00:00.01 | 58 |
| 2 | TABLE ACCESS FULL| T_S1 | 2 | 3 | 6 |00:00:00.01 | 16 |
|* 3 | TABLE ACCESS FULL| T_S2 | 6 | 1 | 6 |00:00:00.01 | 42 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL>
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID chru42r8468j5, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select b
from t_s2)
Plan hash value: 3214991949
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 3 | 3 |00:00:00.01 | 15 | 1306K| 1306K| 631K (0)|
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T_S2 | 1 | 3 | 3 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"="B")
21 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9jt3m5g0368rd, child number 0
-------------------------------------
select /*+ gather_plan_statisitics use_nl(t_s1,t_s2) */ t_s1.* from
t_s1,t_s2 where t_s1.b=t_s2.b
Plan hash value: 1809320615
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 30 |
| 1 | NESTED LOOPS | | 1 | 3 | 3 |00:00:00.01 | 30 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 22 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_S1"."B"="T_S2"."B")
21 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d984hsskza450, child number 0
-------------------------------------
select /*+ gather_plan_statisitics use_merge(t_s1,t_s2) */ t_s1.* from
t_s1,t_s2 where t_s1.b=t_s2.b
Plan hash value: 2917195530
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 14 | | | |
| 1 | MERGE JOIN | | 1 | 3 | 3 |00:00:00.01 | 14 | | | |
| 2 | SORT JOIN | | 1 | 3 | 3 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 3 | 3 | 3 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| T_S2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T_S1"."B"="T_S2"."B")
filter("T_S1"."B"="T_S2"."B")
24 rows selected.
个人简介
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
贵州移动crm及客服数据库性能优化项目
贵州移动crm及客服务数据库sql审核项目
深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/