Session altered.
dexter@STARTREK> select * from tuning4_Tab whereowner='SYS' or owner='PUBLIC' ;
59253 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 989038285
------------------------------------------------------------------------------------------------------
| 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(
AvgLen: 6NDV: 31 Nulls: 0 Density: 0.032258
Table:TUNING4_TAB Alias: TUNING4_TAB
Card:Original: 72643.000000 Rounded: 4687 Computed: 4686.65 Non Adjusted: 4686.65
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
------------------------------------------------------------ --------------- --------------------------------------------
TUNING4_TAB OWNER 0 3.3913E+35
TUNING4_TAB OWNER 1 4.5831E+35
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(*)
------------------------------ ----------
OWBSYS 2
APPQOSSYS 5
SCOTT 6
SI_INFORMTN_SCHEMA 8
OUTLN 8
ORACLE_OCM 8
BI 8
ORDPLUGINS 10
PM 10
FLOWS_FILES 11
OWBSYS_AUD