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

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);---例1alter session set statistics_level=all;set linesize 1000set pagesize 2000select * 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=6Plan 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)，这个有些可疑！---例2set autotrace traceonlyselect * 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），可疑！ --例3CREATE INDEX idx ON t (object_id); set autotrace traceonlyselect * 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 dualCONNECT 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.1set linesize 1000set pagesize 2000explain 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.2SELECT /*+ 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 WHEREt1.id=t2.id and t1.col1 = 666Plan 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_keyFROM user_indexesWHERE 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_666FROM t1; NUM_ROWS DISTINCT_KEYS ROWS_PER_KEY_666--------- ------------- ---------------- 160000 5000 79984 SELECT histogram, num_bucketsFROM user_tab_col_statisticsWHERE 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_bucketsFROM user_tab_col_statisticsWHERE table_name = 'T1' AND column_name = 'COL1';HISTOGRAM NUM_BUCKETS--------------- -----------HEIGHT BALANCED 254--例2.1set linesize 1000set pagesize 2000explain 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.2SELECT /*+ 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 WHEREt1.id=t2.id and t1.col1 = 666Plan 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 1000set autotrace traceonly--例1select * 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 isv_name sex.name%type;beginselect nameinto v_namefrom sexwhere sex_id=p_id;return v_name;end;/set linesize 1000set pagesize 2000set autotrace traceonly--例1：select sex_id,first_name||' '||last_name full_name,get_sex_name(sex_id) genderfrom 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 dualCONNECT 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 WHEREt1.id=t2.id and t1.col1 = 666Plan 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 1000set 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 wheret1.object_id = t2.id1) a where a.rn >= 1 and a.rn <= 10Plan 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 wheret1.object_id = t2.id1 and rownum<=10) a where a.rn >= 1Plan 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 1000set pagesize 2000set autotrace traceonlyselect * 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 1000set pagesize 2000drop table t purge;create table t as select * from dba_objects;--试验1.1set autotrace traceonlyselect * 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 offalter 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_idPlan 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 traceonlyselect * 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 offalter 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_idPlan 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

检查统计信息是否被收集--10gselect t.job_name,t.program_name,,t.state,t.enabled from dba_scheduler_jobs twhere job_name = 'GATHER_STATS_JOB';--11gselect client_name,status from dba_autotask_client;CLIENT_NAME STATUS---------------------------------------------------------------- --------auto optimizer stats collection ENABLEDauto space advisor ENABLEDsql tuning advisor ENABLEDselect window_next_time,autotask_status from DBA_AUTOTASK_WINDOW_CLIENTS;WINDOW_NEXT_TIME AUTOTASK_STATUS-------------------------------------------------------------------------------- ---------------13-1月 -14 10.00.00.000000 下午 +08:00 ENABLED14-1月 -14 10.00.00.000000 下午 +08:00 ENABLED15-1月 -14 10.00.00.000000 下午 +08:00 ENABLED16-1月 -14 10.00.00.000000 下午 +08:00 ENABLED17-1月 -14 10.00.00.000000 下午 +08:00 ENABLED11-1月 -14 06.00.00.000000 上午 +08:00 ENABLED12-1月 -14 06.00.00.000000 上午 +08:00 ENABLED

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

prompt

检查哪些未被收集或者很久没收集(表、分区、子分区）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

检查哪些列很久没被收集统计信息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 dualCONNECT 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

检查哪些索引未被收集或者很久没收集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

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。