要对sql语句进行分析，首先就是要去看sql语句的执行计划是否存在问题，oracle在10g之后，默认采用CBO基于代价的优化器，sql语句的执行计划根据统计信息分析来决定，如果统计信息未收集，则采取动态采样的方式来决定最优的执行计划！
一: 获取sql语句的执行计划，在使用执行计划前，先以sys用户运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql，该脚本创建了plustrace角色，并给该角色查询v$sessstat,v$statname,v$mystat三个动态性能视图的权限；最后将plustrace角色授予普通用户
1：创建测试表，填充数据，创建索引
SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
77262
SQL> update t set id=99 where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 77261
99 1
SQL> create index i_t_id on t(id);
Index created.
2：获取sql语句的执行计划，‘dynamic sampling used for this statement (level=2)’表示采取级别2的动态采样；执行计划的步骤为靠右靠上先执行，而不是第一列的id顺序，在本例中先执行缩进最靠右的I_T_ID索引范围扫描，然后根据索引扫描出来的结果定位到T表相应行的rowid，谓词中的"2 - access("ID"=99)"表示where后条件id=99会对id为2的INDEX RANGE SCAN造成决定行的影响，这个也很好理解，在本例中如果where语句后面为id=1，则必然选择全表扫描才是最优的执行计划；rows则会返回的结果集行数，统计信息中对应select语句主要看物理读和一致性读的个数
SQL> set autot traceonly
SQL> select * from t where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
67 consistent gets
1 physical reads
0 redo size
491 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3：使用dbms_stats包对表进行收集统计信息，在本例中可以看出进行分析过的表，将不会继续使用动态采样的方式收集信息
SQL> exec dbms_stats.gather_table_stats('HR','T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select * from t where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
491 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4：对表分析后的表进行update操作，在未重新进行分析前，将产生错误的执行计划，重新分析后正常;因而在生产环境中，经常有DML操作的表应当根据实际情况进行分析，否则将产生类似的问题
SQL> set autot off
SQL> update t set id=99 where id=1;
77261 rows updated.
SQL> update t set id=1 where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select id,count(*) from t group by id;
ID COUNT(*)
---------- ----------
1 1
99 77261
SQL> set autot traceonly
SQL> select * from t where id=99;
77261 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11017 consistent gets
0 physical reads
13840 redo size
3170997 bytes sent via SQL*Net to client
57070 bytes received via SQL*Net from client
5152 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77261 rows processed
SQL> select /*+ full(t) */ * from t where id=99;
77261 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 378 | 101 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 14 | 378 | 101 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5477 consistent gets
0 physical reads
0 redo size
2357600 bytes sent via SQL*Net to client
57070 bytes received via SQL*Net from client
5152 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77261 rows processed
SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select * from t where id=99;
77261 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77241 | 2036K| 101 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 77241 | 2036K| 101 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10845 consistent gets
0 physical reads
0 redo size
3170997 bytes sent via SQL*Net to client
57070 bytes received via SQL*Net from client
5152 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77261 rows processed
5：执行计划的另一种查看方法，使用explain，同set autot on方式比(set autot traceonly只产生执行计划和统计信息，不执行实际的sql语句，因而广泛用于生产环境中)，不产生"Statistics"信息
SQL> explain plan for select * from t where id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
14 rows selected.
二：直方图(histogram），指数据在列上的值分布情况；倾斜表场合，应当做对索引列做直方图,否则同样会产生错误的执行计划
DBMS_STATS包对表进行分析主要包含
1:表本身的分析，包括表的行数，数据块数，每一行的长度等信息
2:列的分析，包括列的重复数，列的空值数，列的值分布情况等
3:索引的分析，包括索引的块数，索引的深度(blevel),索引的聚合因子等
SQL> create table t1 as select 1 id,object_name from dba_objects;
Table created.
SQL> update t1 set id=99 where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> create index i_t1_id on t1(id);
Index created.
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
ograms where table_name='T1';
no rows selected
SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
ograms where table_name='T1';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
T1 ID 0 1
T1 OBJECT_NAME 0 2.4504E+35
T1 ID 1 99
T1 OBJECT_NAME 1 6.2963E+35
SQL> select count(*),id from t1 group by id;
COUNT(*) ID
---------- ----------
77267 1
1 99
SQL> set autot traceonly
SQL> select * from t1 where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 14 | 378 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 14 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
351 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t1 where id=1;
77267 rows selected.
Execution Plan
---------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77254 | 2036K| 101 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T1 | 77254 | 2036K| 101 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
5489 consistent gets
0 physical reads
0 redo size
2357825 bytes sent via SQL*Net to client
57081 bytes received via SQL*Net from client
5153 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
77267 rows processed
SQL> set autot off
SQL> exec dbms_stats.delete_column_stats('HR','T1','ID');
PL/SQL procedure successfully completed.
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
ograms where table_name='T1';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
T1 OBJECT_NAME 0 2.4504E+35
T1 OBJECT_NAME 1 6.2963E+35
SQL> set autot traceonly
SQL> select * from t1 where id=99;
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 773 | 20871 | 77 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 773 | 20871 | 77 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 309 | | 75 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=99)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
351 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from t1 where id=1;
77267 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1111474805
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 773 | 20871 | 77 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 773 | 20871 | 77 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_ID | 309 | | 75 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10781 consistent gets
0 physical reads
0 redo size
3171208 bytes sent via SQL*Net to client
57081 bytes received via SQL*Net from client
5153 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77267 rows processed
SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autot off
SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
ograms where table_name='T1';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
T1 ID 5527 99
T1 ID 5526 1
T1 OBJECT_NAME 0 2.4504E+35
T1 OBJECT_NAME 1 6.2963E+35
三：动态采样，动态采样分0-10，11个级别，级别越高，采样的结果越精确，需要消耗的数据库成本也越高
level0:不进行动态采样
level1:对没有进行分析的表进行动态采样，要求同时满足下列4个条件；
sql语句中至少有一个未分析的表
未分析的表出现在关联查询或者子查询中
未分析的表没有索引
未分析的表占用的数据块大于动态采样默认的数据块(32个)
level2:对所有未分析的表做分析，动态采样的数据块是默认数据块的2倍
level3:采样的表包含满足level2定义的所有表，同时包括，谓词中包含的潜在的需要动态采样的表
level4:采样的表满足level3定义的所有表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列
level5,6,7,8,9:采样的表包含满足level4定义的所有表，同时分别使用默认数据库的2,4,8,32,128倍的数量做动态采样
level10:采样的表满足level9定义的所有表,同时对表的所有数据进行动态采样
1：创建基表，未收集统计信息前，user_tables视图中的相关信息未填充，11g版本中，user_indexes视图中的信息会被填充
SQL> create table t2 as select object_id,object_name from dba_objects;
Table created.
SQL> create index i_t2_id on t2 (object_id);
Index created.
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab
le_name='T2';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- ---------------
SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in
dexes where table_name='T2';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
---------- ----------- ------------- ---------- -------------------
1 171 77265 77265 2012-06-07:13:55:04
2：执行查询，毫无疑问的采用动态采样，也能获取正确的执行计划，‘1 - filter("OBJECT_ID">30)’表示对结果进行过滤
SQL> set autot traceonly
SQL> select * from t2 where object_id > 30;
77236 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58208 | 4490K| 105 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 58208 | 4490K| 105 (0)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
5586 consistent gets
6 physical reads
0 redo size
3005346 bytes sent via SQL*Net to client
57059 bytes received via SQL*Net from client
5151 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
77236 rows processed
3：使用hint提示符，禁用动态采样，将产生错误的执行计划
SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;
77236 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3661687773
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1556 | 120K| 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1556 | 120K| 8 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T2_ID | 280 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">30)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10897 consistent gets
165 physical reads
0 redo size
3314234 bytes sent via SQL*Net to client
57059 bytes received via SQL*Net from client
5151 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
77236 rows processed
3：收集统计信息后，user_tables视图相应的内容会被填充，禁用动态采样也能获取正确的执行计划
SQL> set autot off
SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab
le_name='T2';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------------
77266 29 381 2012-06-07:14:05:52
SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in
dexes where table_name='T2';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
---------- ----------- ------------- ---------- -------------------
1 171 77265 77265 2012-06-07:14:05:52
SQL> set autot traceonly
SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;
77236 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 77242 | 2187K| 106 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T2 | 77242 | 2187K| 106 (1)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
5516 consistent gets
0 physical reads
0 redo size
3005346 bytes sent via SQL*Net to client
57059 bytes received via SQL*Net from client
5151 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
77236 rows processed
本文以《让oracle跑的更快》为指导，如有雷同，不胜荣幸！
©著作权归作者所有：来自51CTO博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任
oracleexecute planxplanSQL/PER Tuning
随时随地看视频
