DEXTER 25

APEX 33

HR 35

IX 48

DBSNMP 57

OE 112

ORDDATA 239

SH 299

EXFSYS 308

WMSYS 312

CTXSYS 384

SYSTEM 516

XDB 517

OLAPSYS 717

MDSYS 1545

APEX_030200 2251

ORDSYS 2512

SYSMAN 3392

PUBLIC 28027

SYS 31226

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

------------------------------------------------------------ --------------- -------------------------------------

TUNING4_TAB OWNER 1 3.3913E+35

TUNING4_TAB OWNER 179 3.3913E+35

TUNING4_TAB OWNER 206 3.4959E+35

TUNING4_TAB OWNER 212 3.5442E+35

TUNING4_TAB OWNER 215 3.5448E+35

TUNING4_TAB OWNER 244 3.6006E+35

TUNING4_TAB OWNER 246 3.7551E+35

TUNING4_TAB OWNER 249 3.8082E+35

TUNING4_TAB OWNER 370 4.0119E+35

TUNING4_TAB OWNER 383 4.1159E+35

TUNING4_TAB OWNER 422 4.1174E+35

TUNING4_TAB OWNER 423 4.1186E+35

TUNING4_TAB OWNER 436 4.1186E+35

TUNING4_TAB OWNER 636 4.1186E+35

TUNING4_TAB OWNER 2824 4.1711E+35

TUNING4_TAB OWNER 2855 4.3242E+35

TUNING4_TAB OWNER 5199 4.3277E+35

TUNING4_TAB OWNER 5455 4.3277E+35

TUNING4_TAB OWNER 5500 4.3277E+35

TUNING4_TAB OWNER 5525 4.5330E+35

TUNING4_TAB OWNER 5567 4.5831E+35

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