1. 示例说明直方图的作用。 初始化数据 dexter@STARTREK select count(*) fromall_objects ; COUNT(*) ---------- 72642 dexter@STARTREK create table tuning4_tabnologging as select * from all_objects ; Table created. dexter@STARTREK select count(*
1. 示例说明直方图的作用。
初始化数据
dexter@STARTREK> select count(*) fromall_objects ;
COUNT(*)
----------
dexter@STARTREK> create table tuning4_tabnologging as select * from all_objects ;
Table created.
dexter@STARTREK> select count(*) fromall_objects ;
COUNT(*)
----------
dexter@STARTREK> create indexidx_tuning4_tab_owner on tuning4_tab (owner) ;
Index created.
dexter@STARTREK> @gather_tab
Enter value for tbname: tuning4_tab
PL/SQL procedure successfully completed.
在这里碰到了一个小问题，因为数据的倾斜比较严重，而且oracle数据库在执行gather_table_stats的时候没有收集owner列的统计信息，这里优化器选择了错误的执行计划。下面记录了完整的处理过程。
出现错误的执行计划
dexter@STARTREK> select* from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information(identified by operation id):
---------------------------------------------------
3 - access("OWNER"='PUBLIC' OR"OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9581 consistent gets
0 physical reads
0 redo size
6805858 bytes sent via SQL*Net to client
43970 bytes received via SQL*Net from client
3952 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59253 rows processed
下面的 输出可以看到，其实使用全表扫描的效率要高于indexrange scan。
dexter@STARTREK> select /*+full(tuning4_tab)*/ *from tuning4_Tab where owner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 4687 | 443K| 290 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 4687 | 443K| 290 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4927 consistent gets
0 physical reads
0 redo size
3035580 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK>
trace 一下
dexter@STARTREK> alter session settracefile_identifier=histogram ;
Session altered.
dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';
Session altered.
dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TUNING4_TAB | 4687 | 443K| 139 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN |IDX_TUNING4_TAB_OWNER | 4687 | | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -access("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9581 consistent gets
0 physical reads
0 redo size
6805858 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK> alter session set events '10053trace name context off ';
Session altered.
dexter@STARTREK>
从10053中看到
Access path analysis for TUNING4_TAB
***************************************
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]
Column(#1): OWNER(
Table:TUNING4_TAB Alias: TUNING4_TAB
Rounded:
4687
实际：
59253
明显是由于统计信息不准确造成的。我们看一下它的直方图信息。
其实从执行计划
| 0 | SELECTSTATEMENT | | 4687 | 443K| 139 (0)| 00:00:01 |
也可以看到它的统计信息不准确。
确定问题根源
dexter@STARTREK> select* from user_tab_histograms where table_name='TUNING4_TAB' andcolumn_name='OWNER' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------------------------------------ --------------- --------------------------------------------
dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------------------------------------ ---------------
TUNING4_TAB OWNER NONE
TUNING4_TAB OBJECT_NAME NONE
TUNING4_TAB SUBOBJECT_NAME NONE
TUNING4_TAB OBJECT_ID NONE
TUNING4_TAB DATA_OBJECT_ID NONE
TUNING4_TAB OBJECT_TYPE NONE
TUNING4_TAB CREATED NONE
TUNING4_TAB LAST_DDL_TIME NONE
TUNING4_TAB TIMESTAMP NONE
TUNING4_TAB STATUS NONE
TUNING4_TAB TEMPORARY NONE
TUNING4_TAB GENERATED NONE
TUNING4_TAB SECONDARY NONE
TUNING4_TAB NAMESPACE NONE
TUNING4_TAB EDITION_NAME NONE
15 rows selected.
没有直方图信息。
我们来直接查看表中数据的分布情况
dexter@STARTREK> select owner,count(*) fromtuning4_Tab group by owner order by 2 ;
OWNER COUNT(*)
------------------------------ ----------
31 rows selected.
倾斜很严重，并且因为gather_table_stats的时候默认没有收集直方图信息，导致优化器没有选择正确的执行计划，我们来收集一下它的直方图。
默认为FOR ALL COLUMNS SIZEAUTO没有收集直方图。
dexter@STARTREK> selectdbms_stats.get_param('METHOD_OPT') from dual ;
DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
收集列的直方图信息
dexter@STARTREK> execdbms_stats.gather_table_stats(user,'tuning4_tab',cascade=>true,method_opt=>'FORALL columns size skewonly') ;
PL/SQL procedure successfully completed.
dexter@STARTREK> select table_name , column_name, histogram from user_tab_col_statistics where table_name='TUNING4_TAB' ;
TABLE_NAME COLUMN_NAME HISTOGRAM
------------------------------------------------------------ ---------------
TUNING4_TAB OWNER FREQUENCY
TUNING4_TAB OBJECT_NAME HEIGHT BALANCED
TUNING4_TAB SUBOBJECT_NAME FREQUENCY
TUNING4_TAB OBJECT_ID NONE
TUNING4_TAB DATA_OBJECT_ID HEIGHT BALANCED
TUNING4_TAB OBJECT_TYPE FREQUENCY
TUNING4_TAB CREATED HEIGHT BALANCED
TUNING4_TAB LAST_DDL_TIME HEIGHT BALANCED
TUNING4_TAB TIMESTAMP HEIGHT BALANCED
TUNING4_TAB STATUS FREQUENCY
TUNING4_TAB TEMPORARY FREQUENCY
TUNING4_TAB GENERATED FREQUENCY
TUNING4_TAB SECONDARY FREQUENCY
TUNING4_TAB NAMESPACE FREQUENCY
TUNING4_TAB EDITION_NAME NONE
15 rows selected.
owner为频率直方图，比较正确。
dexter@STARTREK> select * from user_tab_histograms wheretable_name='TUNING4_TAB' and column_name='OWNER' ;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBERENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
------------------------------------------------------------ --------------- -------------------------------------
21 rows selected.
再次测试
已经收集好了直方图，我们再来看一下执行计划以及10053事件。
set autotrace traceonly
alter session set tracefile_identifier=histogram ;
alter session set events '10053 trace name contextforever ,level 12';
select * from tuning4_Tab where owner='SYS' orowner='PUBLIC' ;
alter session set events '10053 trace name contextoff ';
已经选择了正确、最优的执行计划。
dexter@STARTREK> set autotrace traceonly
dexter@STARTREK> alter session settracefile_identifier=histogram ;
Session altered.
dexter@STARTREK> alter session set events '10053trace name context forever ,level 12';
Session altered.
dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | 59137 | 5601K| 291 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TUNING4_TAB | 59137 | 5601K| 291 (1)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -filter("OWNER"='PUBLIC' OR "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4927 consistent gets
0 physical reads
0 redo size
3035580 bytes sent via SQL*Net toclient
43970 bytes received via SQL*Netfrom client
3952 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
59253 rows processed
dexter@STARTREK> alter session set events '10053trace name context off ';
Session altered.
dexter@STARTREK> exit
SINGLE TABLE ACCESS PATH
SingleTable Cardinality Estimation for TUNING4_TAB[TUNING4_TAB]
Column(#1):
Column(#1): OWNER(
Table:TUNING4_TAB Alias: TUNING4_TAB
比较正确了。也选择了全表扫描作为最佳的accesspath。