|
----------------------------------------------------------------
Plan hash value: 2223038180
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| A | 500 | 9500 | 25 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" LIKE '%A%') --LIKE '%A%'对于cbo而言太复杂了，没有真正跑的话，cbo根本不知道真正返回多少行。
SQL>
已选择13行。
SQL> col OBJECT_NAME for a30
SQL> select OBJECT_NAME,count(*) from a group by OBJECT_NAME having count(*)>3 order by count(*) desc;
OBJECT_NAME COUNT(*)
------------------------------ ----------
DBMS_REPCAT_AUTH 5
已选择 1 行。
SQL> explain plan for select count(*) from a where OBJECT_NAME='DBMS_REPCAT_AUTH';
已解释。
SQL> @getplan
'general,outline,starts'
Enter value for plan type:general
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 2223038180
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 25 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS FULL| A | 1 | 19 | 25 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME"='DBMS_REPCAT_AUTH') --这个不复杂了吧，rows=1，一样不准，直方图也不可能保证100%准确的
所以说，并不是所有字段都适合做直方图。distinct值非常多的，根本不适合做直方图，默认的桶数也装不下。
只有字段值倾斜非常严重，distinct值少，而且用到的sql中where条件包含了这个字段。如果sql中都没有用到这个字段，那也没必要做直方图，
因为做直方图是非常cpu性能的。
@脚本
--anatab.sql
set timing on
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '&ownname',
tabname => '&tabname' ,
estimate_percent => &estimate_percent,
method_opt => 'for all columns size &skewonly_repeat_auto',
no_invalidate => FALSE,
degree => °ree,
cascade => TRUE);
END;
/
set timing off
--anatab_col.sql
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => '&owner',
tabname => '&table_name',
estimate_percent => 100,
method_opt => 'for columns &columns ', --such as:col1,col2,col3...
no_invalidate => FALSE,
degree => 4,
granularity => 'ALL',
cascade => TRUE);
END;
/
--getcolstat.sql
col COLUMN_NAME for a30
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets,
a.last_analyzed
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = upper('&owner')
and a.table_name = upper('&table_name');
--getplan.sql
set feedback off
pro 'general,outline,starts'
pro
acc type prompt 'Enter value for plan type:' default 'ge |