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;
