oracle学习笔记识别低效sql（十九）

返回行与逻辑读比率：

/*
一般而言，每获取一行开销5个以下的逻辑读是属于基本比较满意的。
例1用statitics_level=all获取执行计划的方式，发现获取1条记录(A-ROWS)，产生1048次逻辑读（Buffers)，可疑！
例2改用autotrace 获取执行计划，发现获取1条记录（1 rows processed），产生1048次逻辑读（1048 consistent gets），可疑！
例3 增加索引后，发现获取1条记录（1 rows processed），产生4次逻辑读（4 consistent gets），比较满意。  

BUFERS/A-ROWS                     (statistics_level方法）
consistent gets/rows processed    (autotrace 方法）
*/

---构造出上例的例子的脚本
DROP TABLE t;
CREATE TABLE t as select * from dba_objects;
--CREATE INDEX idx ON t (object_id);

---例1
alter session set statistics_level=all;

set linesize 1000
set pagesize 2000
select * from t where object_id=6;

SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  8cxbzma1az713, child number 0
-------------------------------------
select * from t where object_id=6

Plan hash value: 1601196873
---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.07 |    1048 |    774 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |     12 |      1 |00:00:00.07 |    1048 |    774 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=6)
Note
-----
   - dynamic sampling used for this statement (level=2)

已选择22行。

---思考：总共获取1条记录(A-ROWS)，产生1048次逻辑读（Buffers)，这个有些可疑！

---例2
set autotrace traceonly
select * from t where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    12 |  2484 |   292   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    12 |  2484 |   292   (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=6)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1048  consistent gets
          0  physical reads
          0  redo size
       1392  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
          
---这里也是类似的思考：总共获取1条记录（1  rows processed），产生1048次逻辑读（1048  consistent gets），可疑！        

--例3

CREATE INDEX idx ON t (object_id);  
set autotrace traceonly
select * from t where object_id=6;
执行计划
----------------------------------------------------------
Plan hash value: 2770274160
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   207 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   207 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=6)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1395  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

　　评估值准确的重要性

/*

请关注Oracle的执行计划中的评估是否准确，这很重要，错误的评估往往意味着低效的执行计划。
请看例1.2中执行计划中的ID=4的部分，预测32行(E-ROWS=32)，实际75808行（A-ROWS=75808），偏差极大，最终BUFFER=94651
    例1.1和例1.2是同样的执行计划，只是用explain plan for的方式， 却难以发现问题所在
请看例2.2中执行计划中的ID=3的部分，预测80000(E-ROWS=80000)，实际75808行(A-ROWS=75808)，偏差极小,最终BUFFER=5173
    例2.1和例2.2是同样的执行计划，只是用explain plan for的方式， 却难以发现问题所在
所以例1的执行计划性能比例2的执行计划更低。 

E-ROWS与A-ROWS(statistics_level=all方法）
 
*/

---构造例子的脚本
DROP TABLE t1;
CREATE TABLE t1 (id, col1, col2, pad)
AS 
SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END, rownum, lpad('*',100,'*')
FROM dual
CONNECT BY level <= 10000;
INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;
COMMIT;
CREATE INDEX t1_col1 ON t1 (col1);
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);
--以下为分析，但是故意不搜集直方图
BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T1', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 1',
   no_invalidate=>FALSE);
END;
/

BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T2', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 1',
   no_invalidate=>FALSE);
END;
/

---例1.1
set linesize 1000
set pagesize 2000
explain plan for 
SELECT  count(t2.col2)
FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3711554156
------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    18 |    48   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                |         |     1 |    18 |            |          |
|   2 |   NESTED LOOPS                 |         |       |       |            |          |
|   3 |    NESTED LOOPS                |         |    32 |   576 |    48   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1      |    32 |   288 |    18   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_COL1 |    32 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | T2_PK   |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2      |     1 |     9 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."COL1"=666)
   6 - access("T1"."ID"="T2"."ID")

已选择20行。

---例1.2
SELECT /*+ gather_plan_statistics */ count(t2.col2)
FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  g048suxnxkxyr, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(t2.col2) FROM t1 ,t2 WHERE
t1.id=t2.id and t1.col1 = 666

Plan hash value: 3711554156
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.30 |   94651 |
|   1 |  SORT AGGREGATE                |         |      1 |      1 |      1 |00:00:00.30 |   94651 |
|   2 |   NESTED LOOPS                 |         |      1 |        |  75808 |00:00:00.31 |   94651 |
|   3 |    NESTED LOOPS                |         |      1 |     32 |  75808 |00:00:00.19 |   18843 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1      |      1 |     32 |  80016 |00:00:00.08 |    1771 |
|*  5 |      INDEX RANGE SCAN          | T1_COL1 |      1 |     32 |  80016 |00:00:00.03 |     169 |
|*  6 |     INDEX UNIQUE SCAN          | T2_PK   |  80016 |      1 |  75808 |00:00:00.08 |   17072 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2      |  75808 |      1 |  75808 |00:00:00.08 |   75808 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."COL1"=666)
   6 - access("T1"."ID"="T2"."ID")

已选择26行。


SELECT num_rows, distinct_keys, num_rows/distinct_keys AS avg_rows_per_key
FROM user_indexes
WHERE index_name = 'T1_COL1';

 NUM_ROWS DISTINCT_KEYS AVG_ROWS_PER_KEY
--------- ------------- ----------------
   160000          5000               32
   
SELECT count(*) AS num_rows, count(DISTINCT col1) AS distinct_keys, 
       count(nullif(col1,666)) AS rows_per_key_666
FROM t1;

 NUM_ROWS DISTINCT_KEYS ROWS_PER_KEY_666
--------- ------------- ----------------
   160000          5000            79984
   
SELECT histogram, num_buckets
FROM user_tab_col_statistics
WHERE table_name = 'T1' AND column_name = 'COL1';

HISTOGRAM       NUM_BUCKETS
--------------- -----------
NONE                      1

---看看收集直方图后是啥情况

BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T1', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 254',
   no_invalidate=>FALSE);
END;
/
SELECT histogram, num_buckets
FROM user_tab_col_statistics
WHERE table_name = 'T1' AND column_name = 'COL1';

HISTOGRAM       NUM_BUCKETS
--------------- -----------
HEIGHT BALANCED         254

--例2.1
set linesize 1000
set pagesize 2000
explain plan for 
SELECT  count(t2.col2)
FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
select * from table(dbms_xplan.display());

SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 906334482
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    18 |  1425   (1)| 00:00:18 |
|   1 |  SORT AGGREGATE     |      |     1 |    18 |            |          |
|*  2 |   HASH JOIN         |      | 80000 |  1406K|  1425   (1)| 00:00:18 |
|*  3 |    TABLE ACCESS FULL| T1   | 80000 |   703K|   722   (1)| 00:00:09 |
|   4 |    TABLE ACCESS FULL| T2   |   151K|  1332K|   701   (1)| 00:00:09 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID"="T2"."ID")
   3 - filter("T1"."COL1"=666)

已选择17行。

--例2.2
SELECT /*+ gather_plan_statistics */ count(t2.col2)
FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
SQL_ID  g048suxnxkxyr, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(t2.col2) FROM t1 ,t2 WHERE
t1.id=t2.id and t1.col1 = 666

Plan hash value: 906334482
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.17 |    5173 |       |       |          |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.17 |    5173 |       |       |          |
|*  2 |   HASH JOIN         |      |      1 |  80000 |  75808 |00:00:00.41 |    5173 |  2330K|  1381K| 3084K (0)|
|*  3 |    TABLE ACCESS FULL| T1   |      1 |  80000 |  80016 |00:00:00.12 |    2644 |       |       |          |
|   4 |    TABLE ACCESS FULL| T2   |      1 |    151K|    151K|00:00:00.03 |    2529 |       |       |          |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")
   3 - filter("T1"."COL1"=666)

已选择23行。


DROP  TABLE t1;
PURGE TABLE t1;
DROP  TABLE t2;
PURGE TABLE t2;

　　类型转换需认真关注

/*

  请关注执行计划中的Predicate Information (identified by operation id)部分，这里例1出现的类似
  filter(TO_NUMBER....这种情况的，就是发生了类型转换。需引起关注。
  例2中的Predicate Information部分，就没有发生类型转换。
  
Predicate Information(各方法都可见）
  
*/


--举例说明：
drop table t_col_type purge;
create table t_col_type(id varchar2(20),col2 varchar2(20),col3 varchar2(20));
insert into t_col_type select rownum,'abc','efg' from dual connect by level<=10000;
commit;
create index idx_id on t_col_type(id);
set linesize 1000
set autotrace traceonly

--例1
select * from t_col_type where id=6;
执行计划
----------------------------------------------------------
Plan hash value: 3191204463
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    36 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_COL_TYPE |     1 |    36 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("ID")=6)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         32  consistent gets
          0  physical reads
          0  redo size
        540  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--实际上只有如下写法才可以用到索引，这个很不应该，是什么类型的取值就设置什么样的字段。

----例2          
select * from t_col_type where id='6';
执行计划
----------------------------------------------------------
Plan hash value: 3998173245
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    36 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_COL_TYPE |     1 |    36 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID     |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"='6')
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed      

　　请小心递归调用部分：

/*

 6种获取执行计划的方法中，只有 autotrace 的方式可以看出递归调用的次数（recursive calls），
 这方面的经验和想法很重要！
 
recursive calls(autotrace 方法）
 
*/

drop table people purge;
create table people (first_name varchar2(200),last_name varchar2(200),sex_id number);

create table sex (name varchar2(20), sex_id number);
insert into people (first_name,last_name,sex_id) select object_name,object_type,1 from dba_objects;
insert into sex (name,sex_id) values ('男',1);
insert into sex (name,sex_id) values ('女',2);
insert into sex (name,sex_id) values ('不详',3);
commit;


create or replace function get_sex_name(p_id sex.sex_id%type) return sex.name%type is
v_name sex.name%type;
begin
select name
into v_name
from sex
where sex_id=p_id;
return v_name;
end;
/

set linesize 1000
set pagesize 2000

set autotrace traceonly

--例1：

select sex_id,
first_name||' '||last_name full_name,
get_sex_name(sex_id) gender
from people;
执行计划
----------------------------------------------------------
Plan hash value: 2528372185
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 80635 |    16M|   137   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| PEOPLE | 80635 |    16M|   137   (1)| 00:00:02 |
----------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
      73121  recursive calls
          0  db block gets
     517142  consistent gets
          0  physical reads
          0  redo size
    3382143  bytes sent via SQL*Net to client
      54029  bytes received via SQL*Net from client
       4876  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73121  rows processed
      
      
---例2
      
执行计划
----------------------------------------------------------
Plan hash value: 1973058250
-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        | 45627 |    10M|    89   (4)| 00:00:02 |
|*  1 |  HASH JOIN         |        | 45627 |    10M|    89   (4)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| SEX    |     3 |    75 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| PEOPLE | 45627 |  9669K|    85   (3)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SEX"."SEX_ID"="P"."SEX_ID")
Note
-----
   - dynamic sampling used for this statement
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3910  consistent gets
          0  physical reads
          0  redo size
    2488557  bytes sent via SQL*Net to client
      40198  bytes received via SQL*Net from client
       3620  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      54277  rows processed  

　　表的访问次数需敏感

/*

 6种获取执行计划的方法中，只有 statisitcs_level=all 的方式可以看出表访问次数（STARTS)，这个很重要！
 
Starts (statistics_level=all 方法）
 
*/

--例1

---构造例子的脚本
DROP TABLE t1;
CREATE TABLE t1 (id, col1, col2, pad)
AS 
SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END, rownum, lpad('*',100,'*')
FROM dual
CONNECT BY level <= 10000;
INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;
COMMIT;
CREATE INDEX t1_col1 ON t1 (col1);
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);
--以下为分析，但是故意不搜集直方图
BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T1', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 1',
   no_invalidate=>FALSE);
END;
/

BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T2', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 1',
   no_invalidate=>FALSE);
END;
/


SELECT /*+ gather_plan_statistics */ count(t2.col2)
FROM t1 ,t2 WHERE t1.id=t2.id and t1.col1 = 666;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  g048suxnxkxyr, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(t2.col2) FROM t1 ,t2 WHERE
t1.id=t2.id and t1.col1 = 666

Plan hash value: 3711554156
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.30 |   94651 |
|   1 |  SORT AGGREGATE                |         |      1 |      1 |      1 |00:00:00.30 |   94651 |
|   2 |   NESTED LOOPS                 |         |      1 |        |  75808 |00:00:00.31 |   94651 |
|   3 |    NESTED LOOPS                |         |      1 |     32 |  75808 |00:00:00.19 |   18843 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1      |      1 |     32 |  80016 |00:00:00.08 |    1771 |
|*  5 |      INDEX RANGE SCAN          | T1_COL1 |      1 |     32 |  80016 |00:00:00.03 |     169 |
|*  6 |     INDEX UNIQUE SCAN          | T2_PK   |  80016 |      1 |  75808 |00:00:00.08 |   17072 |
|   7 |    TABLE ACCESS BY INDEX ROWID | T2      |  75808 |      1 |  75808 |00:00:00.08 |   75808 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."COL1"=666)
   6 - access("T1"."ID"="T2"."ID")

已选择26行

　　注意表真实访问行数

/*

 例1中的T1表访问了73156行（例1执行计划中ID=5的部分，A-ROWS=73156），
 例2中的T2表访问了10行   （例1执行计划中ID=5的部分，A-ROWS=10)
 这就是例1中BUFFERS=1052和例2中BUFFERS=9的性能差异，请关注A-ROWS!
 另：其中例2的执行计划中的（COUNT STOPKEY）这个关键字体现了这个局部访问的算法
 
隆重推出本期关键字如下：
A-ROWS 与 COUNT STOPKEY 
(其中A-ROWS是 statistics_level=all 方法 而执行计划中的COUNT STOPKEY是所有方法都可查）
 
*/




drop table t1 cascade constraints;
create table t1 as select * from dba_objects;
drop table t2 cascade constraints;
create table t2 (id1,id2) as 
select rownum ,rownum+100 from dual connect  by level <=1000;

alter session set statistics_level=all;
set linesize 1000
set pagesize 2000

--例1（未优化）
select *
  from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1) a
 where a.rn >= 1
   and a.rn <= 10;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));

SQL_ID  ayzfn8k0j3sms, child number 0
-------------------------------------
select *   from (select t1.*, rownum as rn from t1, t2 where
t1.object_id = t2.id1) a  where a.rn >= 1    and a.rn <= 10

Plan hash value: 3062220019
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     10 |00:00:00.11 |    1052 |    749 |       |       |          |
|*  1 |  VIEW                |      |      1 |   1008 |     10 |00:00:00.11 |    1052 |    749 |       |       |          |
|   2 |   COUNT              |      |      1 |        |    943 |00:00:00.11 |    1052 |    749 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |   1008 |    943 |00:00:00.11 |    1052 |    749 |  1036K|  1036K| 1197K (0)|
|   4 |     TABLE ACCESS FULL| T2   |      1 |   1000 |   1000 |00:00:00.01 |       4 |      0 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |  70183 |  73156 |00:00:00.08 |    1048 |    749 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("A"."RN"<=10 AND "A"."RN">=1))
   3 - access("T1"."OBJECT_ID"="T2"."ID1")
Note
-----
   - dynamic sampling used for this statement (level=2)


已选择28行。


--例2（优化后，请观察A-ROWS）
      
select *
  from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) a
 where a.rn >= 1; 
 
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID  7wzvqay91x14y, child number 0
-------------------------------------
select *   from (select t1.*, rownum as rn from t1, t2 where
t1.object_id = t2.id1 and rownum<=10) a  where a.rn >= 1

Plan hash value: 1802812661
------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |     10 |00:00:00.01 |       9 |       |       |          |
|*  1 |  VIEW                |      |      1 |     10 |     10 |00:00:00.01 |       9 |       |       |          |
|*  2 |   COUNT STOPKEY      |      |      1 |        |     10 |00:00:00.01 |       9 |       |       |          |
|*  3 |    HASH JOIN         |      |      1 |   1008 |     10 |00:00:00.01 |       9 |  1036K|  1036K| 1210K (0)|
|   4 |     TABLE ACCESS FULL| T2   |      1 |   1000 |   1000 |00:00:00.01 |       4 |       |       |          |
|   5 |     TABLE ACCESS FULL| T1   |      1 |  70183 |     10 |00:00:00.01 |       5 |       |       |          |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."RN">=1)
   2 - filter(ROWNUM<=10)
   3 - access("T1"."OBJECT_ID"="T2"."ID1")
Note
-----
   - dynamic sampling used for this statement (level=2)

已选择29行。


--注意，你试验看看如果使用set autotrace traceonly能有收获吗？
set linesize 1000
set pagesize 2000
set autotrace traceonly
select *
  from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1) a
 where a.rn >= 1
   and a.rn <= 10;
   
执行计划
----------------------------------------------------------
Plan hash value: 3062220019
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1008 |   216K|   297   (2)| 00:00:04 |
|*  1 |  VIEW                |      |  1008 |   216K|   297   (2)| 00:00:04 |
|   2 |   COUNT              |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |  1008 |   216K|   297   (2)| 00:00:04 |
|   4 |     TABLE ACCESS FULL| T2   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T1   | 70183 |    13M|   293   (1)| 00:00:04 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."RN"<=10 AND "A"."RN">=1)
   3 - access("T1"."OBJECT_ID"="T2"."ID1")
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1052  consistent gets
          0  physical reads
          0  redo size
       1812  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed 
              
select *
  from (select t1.*, rownum as rn from t1, t2 where t1.object_id = t2.id1 and rownum<=10) a
 where a.rn >= 1; 

执行计划
----------------------------------------------------------
Plan hash value: 1802812661
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    10 |  2200 |    10  (20)| 00:00:01 |
|*  1 |  VIEW                |      |    10 |  2200 |    10  (20)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |  1008 |   420K|    10  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T2   |  1000 | 13000 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T1   | 70183 |    13M|     6  (17)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."RN">=1)
   2 - filter(ROWNUM<=10)
   3 - access("T1"."OBJECT_ID"="T2"."ID1")
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       1812  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed
         
set autotrace off

　　谨慎的观察排序与否

/*
 试验1.1和1.2是存在排序的情况（1.1和1.2其实是同一个试验，只是用了不同的获取执行计划的手法而已）
 试验2.1和2.2是消除排序的情况（2.1和2.2其实是同一个试验，只是用了不同的获取执行计划的手法而已）
 
 sorts (memory) sorts (disk) (autotrace的方法,其中如果出现sorts(disk)有值，说明再磁盘中排序了，情况就糟了。）
 Used-Mem（statistics_level=all的方法，如出现类似9118K (0)表示还没交换到磁盘，如果是9118K (1)就表示交换到磁盘）
 
*/


set linesize 1000
set pagesize 2000
drop table t purge;
create table t as select * from dba_objects;

--试验1.1
set autotrace traceonly
select * from t where object_id>2 order by object_id;

执行计划
----------------------------------------------------------
Plan hash value: 961378228
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 81694 |    16M|       |  3973   (1)| 00:00:48 |
|   1 |  SORT ORDER BY     |      | 81694 |    16M|    19M|  3973   (1)| 00:00:48 |
|*  2 |   TABLE ACCESS FULL| T    | 81694 |    16M|       |   293   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID">2)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1047  consistent gets
          0  physical reads
          0  redo size
    3517144  bytes sent via SQL*Net to client
      54051  bytes received via SQL*Net from client
       4878  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      73155  rows processed

--试验1.2      
set autotrace off
alter session set statistics_level=all;
select * from t where object_id>2 order by object_id;
--漫长的打屏输出后....
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  7dv0pnqt14nqf, child number 1
-------------------------------------
select * from t where object_id>2 order by object_id
Plan hash value: 961378228
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |  73155 |00:00:00.23 |    1047 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |  81694 |  73155 |00:00:00.23 |    1047 |    10M|  1234K| 9118K (0)|
|*  2 |   TABLE ACCESS FULL| T    |      1 |  81694 |  73155 |00:00:00.03 |    1047 |       |       |          |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_ID">2)
Note
-----
   - dynamic sampling used for this statement (level=2)

已选择23行。


--试验2.1 (在排序列有了索引后）     
create index idx_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;
执行计划
----------------------------------------------------------
Plan hash value: 2041828949
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               | 81694 |    16M|  1303   (1)| 00:00:16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             | 81694 |    16M|  1303   (1)| 00:00:16 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID | 81694 |       |   177   (1)| 00:00:03 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">2)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10953  consistent gets
          0  physical reads
          0  redo size
    3517144  bytes sent via SQL*Net to client
      54051  bytes received via SQL*Net from client
       4878  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      73155  rows processed
      
      
      
--试验2.2(在排序列有了索引后）          
set autotrace off
alter session set statistics_level=all;
select * from t where object_id>2 order by object_id;      
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  7dv0pnqt14nqf, child number 1
-------------------------------------
select * from t where object_id>2 order by object_id
Plan hash value: 2041828949
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |  73155 |00:00:00.18 |   10953 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |  81694 |  73155 |00:00:00.18 |   10953 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_ID |      1 |  81694 |  73155 |00:00:00.10 |    5029 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID">2)
Note
-----
   - dynamic sampling used for this statement (level=2)


已选择23行。

　　查看自动收集统计信息是否开启

prompt <p>检查统计信息是否被收集
--10g
select t.job_name,t.program_name,,t.state,t.enabled
  from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB';


--11g
select client_name,status from dba_autotask_client;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

select window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;WINDOW_NEXT_TIME                                                                 AUTOTASK_STATUS
-------------------------------------------------------------------------------- ---------------
13-1月 -14 10.00.00.000000 下午 +08:00                                           ENABLED
14-1月 -14 10.00.00.000000 下午 +08:00                                           ENABLED
15-1月 -14 10.00.00.000000 下午 +08:00                                           ENABLED
16-1月 -14 10.00.00.000000 下午 +08:00                                           ENABLED
17-1月 -14 10.00.00.000000 下午 +08:00                                           ENABLED
11-1月 -14 06.00.00.000000 上午 +08:00                                           ENABLED
12-1月 -14 06.00.00.000000 上午 +08:00                                           ENABLED

　　哪些表统计信息未收集或过时了

prompt <p>检查哪些未被收集或者很久没收集(表、分区、子分区）
select table_name, blocks, num_rows, last_analyzed
  from user_tab_statistics t
 where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
   and table_name not like 'BIN$%'
   order by last_analyzed ;
   
select table_name, blocks, num_rows, last_analyzed
  from user_tab_partitions t
 where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
   and table_name not like 'BIN$%'
   order by last_analyzed ;


select table_name, blocks, num_rows, last_analyzed
  from user_tab_subpartitions t
 where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
   and table_name not like 'BIN$%'
   order by last_analyzed ;

　　哪些列统计信息未收集或过时了

prompt <p>检查哪些列很久没被收集统计信息

select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_tab_col_statistics t
 where t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;
   
select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_part_col_statistics t
 where  t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;
   
   
select t.table_name,
       t.column_name,
       t.num_distinct,
       t.low_value,
       t.high_value,
       last_analyzed
  from user_subpart_col_statistics t
 where t.last_analyzed < sysdate - 100
   and table_name not like 'BIN$%'
 order by table_name,last_analyzed;
     
---构造例子的脚本
DROP TABLE t1;
CREATE TABLE t1 (id, col1, col2, pad)
AS 
SELECT rownum, CASE WHEN rownum>5000 THEN 666 ELSE rownum END, rownum, lpad('*',100,'*')
FROM dual
CONNECT BY level <= 10000;
INSERT INTO t1 SELECT id+10000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+20000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+40000, col1, col2, pad FROM t1;
INSERT INTO t1 SELECT id+80000, col1, col2, pad FROM t1;
COMMIT;
CREATE INDEX t1_col1 ON t1 (col1);
DROP TABLE t2;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE mod(col2,19) != 0;
ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (id);
--以下为分析，但是故意不搜集直方图
BEGIN
 dbms_stats.gather_table_stats(
   ownname=>user, 
   tabname=>'T1', 
   cascade=>TRUE,
   estimate_percent=>100,
   method_opt=>'for all columns size 1',
   no_invalidate=>FALSE);
END;
/
      

　　哪些索引统计信息未收集或过时

prompt <p>检查哪些索引未被收集或者很久没收集

select t.table_name,
       t.index_name,
       t.blevel,
       t.leaf_blocks,
       t.num_rows,
       t.last_analyzed
  from user_ind_statistics t
 where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
   and table_name not like 'BIN$%'
 order by table_name,index_name;

　　排查全局临时表被收集统计信息

prompt <p>被收集统计信息的临时表
select table_name, 
       t.last_analyzed, 
       t.num_rows,
       t.blocks
  from user_tables t
where t.temporary = 'Y'
   and last_analyzed is not null;