<pre name="code" class="html">SQL> alter session set statistics_level=all;
Session altered.
SQL> select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<2000;
-------------------省略输出结果----------------------
8 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 70xt266nm9y32, child number 0
-------------------------------------
select e.ename,e.job,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal<2000
Plan hash value: 351108634
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 4 | 8 |00:00:00.01 | 18 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 4 | 8 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 8 | 1 | 8 |00:00:00.01 | 10 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 8 | 1 | 8 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL"<2000)
4 - access("E"."DEPTNO"="D"."DEPTNO")
21 rows selected.
NESTED LOOPS表示嵌套循环,Starts表示某个操作执行的次数,那么这里嵌套循环操作一共执行了1次,EMP表离NESTED LOOPS最近,表示EMP表作为驱动表,
它执行了1次(全表扫描)返回了8条记录。Oracle这时候就会从这8条记录中读取一行,然后去匹配内部表,
因为驱动表返回了8条记录,所以内部循环会执行8次。
技巧1:查看真实基数
比如下面这个SQL
SQL> explain plan for SELECT UOM.PROD_SKID,
2 MAX (UOM.RELTV_CURR_QTY) RELTV_CURR_QTY,
3 MAX (UOM.STAT_CURR_VAL) STAT_CURR_VAL,
4 MAX (UOM.BAR_CURR_CODE) BAR_CURR_CODE
5 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit begin
6 FROM OPT_REF_UOM_TEMP_SDIM UOM,
7 REF_PROD_DIM PROD
8 WHERE UOM.RELTV_CURR_QTY = 1
9 AND PROD.CURR_IND = 'Y'
10 AND PROD.PROD_END_DATE = TO_DATE ('31-12-9999', 'dd-mm-yyyy')
11 AND PROD.PROD_SKID = UOM.PROD_SKID
12 AND PROD.BUOM_CURR_SKID = UOM.UOM_SKID
13 --modified by Daniel on 10/26/2010 for CR 326285 of QMR10.1 hypercare retrofit end
14 GROUP BY UOM.PROD_SKID;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 3215660883
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 1 | HASH GROUP BY | | 78 | 4212 | 15507 (1)| 00:01:47 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3034 | 159K| 15506 (1)| 00:01:47 |
|* 4 | TABLE ACCESS FULL | OPT_REF_UOM_TEMP_SDIM | 2967 | 101K| 650 (14)| 00:00:05 |
|* 5 | INDEX RANGE SCAN | PROD_DIM_PK | 3 | | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID| PROD_DIM | 1 | 19 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("UOM"."RELTV_CURR_QTY"=1)
5 - access("PROD"."PROD_SKID"="UOM"."PROD_SKID")
6 - filter("PROD"."BUOM_CURR_SKID" IS NOT NULL AND "PROD"."PROD_END_DATE"=TO_DATE('
9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PROD"."CURR_IND"='Y' AND
"PROD"."BUOM_CURR_SKID"="UOM"."UOM_SKID")
22 rows selected.
请看ID=4这步,CBO估算它只返回2967行数据。ID=4这步前面有*,表示它有谓词过滤。
4 - filter("UOM"."RELTV_CURR_QTY"=1)
UOM是表OPT_REF_UOM_TEMP_SDIM 的别名
那么我们手工计算它应该返回的真实基数:
SQL> select count(*) from OPT_REF_UOM_TEMP_SDIM where "RELTV_CURR_QTY"=1;
COUNT(*)
----------
946432
CBO估算它只返回2967条数据,但是它应该返回94W条数据,这里相差太大了,说明执行计划走错了,它后面的索引会被扫描94W次。
Oracle里的Rows 表示CBO估算它返回的行数
Rows(E-Rows) 预估操作返回的记录条数
在MySQL里呢?
mysql> explain select t1.* from t2 ,t1 where t2.id=t1.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
rows rows Estimate of rows to be examined 评估要检查的行
ows
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数