Oracle数据库SQL优化基本概念扩展统计信息01

运维数据库时，可能会遇到以下两类问题：

问题一：

查询语句，谓词有单个列，优化器预估Cardinality基数相对准确，谓词有多个列时，优化器预估Cardinality基数不准确，从而导致无法生成最优的执行计划。

问题二：

谓词列引用了函数，导致优化器无法正确预估列真实使用情况，如果不创建函数索引，是否还有其他方法提高准确度？

以上两个问题，有时可以通过添加Extended Statistics扩展统计信息来解决。

Extended Statistics：

1.Column Group Statistics

当查询的WHERE子句指定单个表中的多个列（多个单列谓词）时，列之间的关系会强烈影响列组的组合选择性。

2.Expression Statistics

当一个函数应用于查询的WHERE子句中的列（function(col1)=constant）时，优化器无法知道该函数如何影响列的选择性。

通过收集表达式函数（col1）的expression statistics，优化器可以获得更准确的选择性值。

测试过程如下：

创建测试表calendar，包含四个列，分别为月、日、星期、星座。

create table calendar(month_name int,day_name int,week_name int,star_sign varchar(10));
insert into calendar values(1,1,1,'摩羯座');
insert into calendar values(1,2,2,'摩羯座');
insert into calendar values(1,20,6,'摩羯座');
insert into calendar values(2,22,4,'水瓶座');
insert into calendar values(3,6,3,'双鱼座');
insert into calendar values(4,9,2,'白羊座');
insert into calendar values(5,12,7,'金牛座');
insert into calendar values(6,3,1,'双子座');
insert into calendar values(7,11,4,'巨蟹座');
insert into calendar values(8,5,1,'狮子座');
insert into calendar values(9,7,2,'处女座');
insert into calendar values(10,8,3,'天秤座');
insert into calendar values(11,15,5,'天蝎座');
insert into calendar values(12,1,5,'射手座');
insert into calendar values(12,5,4,'射手座');
......
COMMIT;

insert into calendar select * from calendar;
/
/
...
commit;

表数据量

SQL> select count(*) from calendar;
COUNT(*)
----------
368640

查看数据分布

set pagesize 100;
select month_name,day_name,week_name,star_sign from calendar group by month_name,day_name,week_name,star_sign order by 1,2,3,4;
MONTH_NAME DAY_NAME WEEK_NAME STAR_SIGN COUNT(*)
---------- ---------- ---------- ---------- ----------
1 1 1 摩羯座 8192
1 1 2 摩羯座 8192
1 2 2 摩羯座 8192
1 4 1 摩羯座 8192
1 9 2 摩羯座 8192
1 13 6 摩羯座 8192
1 16 6 摩羯座 8192
1 18 1 摩羯座 8192
1 20 6 摩羯座 8192
2 10 4 水瓶座 8192
2 20 4 水瓶座 8192
2 22 4 水瓶座 8192
3 3 3 双鱼座 8192
3 6 3 双鱼座 8192
3 7 3 双鱼座 8192
4 3 2 白羊座 8192
4 9 2 白羊座 8192
4 12 2 白羊座 8192
5 5 7 金牛座 8192
5 12 7 金牛座 8192
5 13 7 金牛座 8192
6 3 1 双子座 8192
6 5 1 双子座 8192
6 7 1 双子座 8192
7 7 4 巨蟹座 8192
7 11 4 巨蟹座 8192
7 16 4 巨蟹座 8192
8 5 1 狮子座 8192
8 6 1 狮子座 8192
8 8 1 狮子座 8192
9 7 2 处女座 8192
9 8 2 处女座 8192
9 9 2 处女座 8192
10 8 3 天秤座 8192
10 10 3 天秤座 8192
10 21 3 天秤座 8192
11 11 5 天蝎座 8192
11 15 5 天蝎座 8192
11 19 5 天蝎座 8192
12 1 5 射手座 8192
12 5 4 射手座 8192
12 5 5 射手座 8192
12 6 4 射手座 8192
12 10 4 射手座 8192
12 12 5 射手座 8192


45 rows selected.

收集表统计信息

---EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,CASCADE=> TRUE,no_invalidate=> FALSE);
---EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);
---EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,method_opt=> 'FOR ALL COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,method_opt => 'for all columns size 1',CASCADE=> TRUE,no_invalidate=> FALSE);

参数说明：

1.收集cjc用户下calendar表统计信息
2.estimate_percent收集数据百分比
3.method_opt直方图
4.CASCADE级联收集索引统计信息
5.no_invalidate为false表示立即将在Shared Pool中有依赖关系的shared cursor失效

查看表统计信息

set line 300
col OWNER for a15;
col TABLE_NAME for a15;
select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') LAST_ANALYZED from DBA_TABLES where table_name='CALENDAR';
OWNERTABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
--------------- --------------- ---------- ---------- -----------------
CJCCALENDAR 368640 1252 20240203 19:19:37

查看列统计信息

set line 300
col OWNER for a15;
col TABLE_NAME for a15
col COLUMN_NAME for a15
col LOW_VALUE for a10
col HIGH_VALUE for a10
select OWNER,TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,TO_CHAR(LAST_ANALYZED,'YYYYMMDD HH24:MI:SS') LAST_ANALYZED from DBA_TAB_COL_STATISTICS where table_name='CALENDAR';


OWNERTABLE_NAMECOLUMN_NAMENUM_DISTINCT NUM_NULLS LAST_ANALYZED
--------------- --------------- --------------- ------------ ---------- -----------------
CJC CALENDARSTAR_SIGN 12 0 20240203 19:19:38
CJC CALENDARWEEK_NAME 7 0 20240203 19:19:38
CJC CALENDARDAY_NAME 20 0 20240203 19:19:38
CJC CALENDARMONTH_NAME 12 0 20240203 19:19:38

场景一：

当查询的WHERE子句指定单个表中的多个列（多个单列谓词）时，列之间的关系会强烈影响列组的组合选择性。

查询

SELECT /*+gather_plan_statistics*/ COUNT(*) FROM CALENDAR WHERE MONTH_NAME=10 AND STAR_SIGN='天秤座';
COUNT(*)
----------
24576

查看执行计划

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 || 1 |00:00:00.01 | 1256 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1256 |
|* 2 | TABLE ACCESS FULL| CALENDAR | 1 | 2560 | 24576 |00:00:00.01 | 1256 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_NAME"=10 AND "STAR_SIGN"='天秤座'))


20 rows selected.

其中，预估 Cardinality 值 E-Rows =2560；

因为通过统计信息数据可以看到，表总行数NUM_ROWS=368640，MONTH_NAME 列 NUM_DISTINCT=12，STAR_SIGN列 NUM_DISTINCT=12。

所以预估查询语句返回行数=368640/12/12=2560

结合10053看一下：

分别执行10053

alter session set tracefile_identifier='AA10053';
alter session set events '10053 trace name context forever ,level 1';
SELECT COUNT(*) FROM CALENDAR WHERE MONTH_NAME=10 AND STAR_SIGN='天秤座';
alter session set events '10053 trace name context off';

查看,10053 trc文件， Card: 2560.00

vi cjc_ora_21149_AA10053.trc
......
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CALENDAR Alias: CALENDAR
#Rows: 368640 #Blks: 1252 AvgRowLen: 19.00 ChainCnt: 0.00
Access path analysis for CALENDAR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[CALENDAR]
Column (#1): MONTH_NAME(
AvgLen: 3 NDV: 12 Nulls: 0 Density: 0.083333 Min: 1 Max: 12
Column (#4): STAR_SIGN(
AvgLen: 10 NDV: 12 Nulls: 0 Density: 0.083333


Table: CALENDAR Alias: CALENDAR
Card: Original: 368640.000000 Rounded: 2560 Computed: 2560.00 Non Adjusted: 2560.00
Access Path: TableScan
Cost: 343.88 Resp: 343.88 Degree: 0
Cost_io: 341.00 Cost_cpu: 106298443
Resp_io: 341.00 Resp_cpu: 106298443
Best:: AccessPath: TableScan
Cost: 343.88 Degree: 1 Resp: 343.88 Card: 2560.00 Bytes: 0


***************************************

但是由于谓词的两个列MONTH_NAME=10 AND STAR_SIGN='天秤座'是有关联的，我们知道，天秤座主要集中在10月份（9月24日～10月23日），MONTH_NAME=10筛选出来的数据有很大一部分符合 STAR_SIGN='天秤座'，所以按照ROW_NUM/NDV1/NDV2得出评估值2560远小于实际  Cardinality 值 A-Rows=24576；

如何提高预估Cardinality准确度？

添加Column Group Statistics：

DECLARE
cg_name VARCHAR2(30);
BEGIN
cg_name := DBMS_STATS.CREATE_EXTENDED_STATS(null,'CALENDAR',
'(MONTH_NAME,STAR_SIGN)');
END;
/

收集表统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,method_opt => 'for all columns size 1',CASCADE=> TRUE,no_invalidate=> FALSE);

查看增强统计信息

SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='CALENDAR';
EXTENSION_NAME EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUJAZ#M82V9AIVJF$ZMSI#RN$ ("MONTH_NAME","STAR_SIGN")

自动将"MONTH_NAME","STAR_SIGN"组合合并成一个虚拟列

col COL_GROUP for a30;
SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE e.EXTENSION_NAME=t.COLUMN_NAME
AND e.TABLE_NAME=t.TABLE_NAME
AND t.TABLE_NAME='CALENDAR';


COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
("MONTH_NAME","STAR_SIGN") 12 NONE

col COLUMN_NAME for a30;
SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT as NDV FROM USER_TAB_COL_STATISTICS ORDER BY 1,2;
TABLE_NAMECOLUMN_NAME NDV
--------------- ------------------------------ ----------
CALENDARDAY_NAME 20
CALENDARMONTH_NAME 12
CALENDARSTAR_SIGN 12
CALENDARSYS_STUJAZ#M82V9AIVJF$ZMSI#RN$ 12
CALENDARWEEK_NAME7

再次查看执行计划

SELECT /*+gather_plan_statistics*/ COUNT(*) FROM CALENDAR WHERE MONTH_NAME=10 AND STAR_SIGN='天秤座';
COUNT(*)
----------
24576

查看执行计划

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 || 1 |00:00:00.02 | 1256 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1256 |
|* 2 | TABLE ACCESS FULL| CALENDAR | 1 | 30720 | 24576 |00:00:00.07 | 1256 |
-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("MONTH_NAME"=10 AND "STAR_SIGN"='天秤座'))


20 rows selected.

预估 Cardinality 值 E-Rows =30720；

通过统计信息数据可以看到，表总行数NUM_ROWS=368640，SYS_STUJAZ#M82V9AIVJF$ZMSI#RN$虚拟列 NUM_DISTINCT=12。

所以预估查询语句返回行数=368640/12=30720

查看数据分布

SQL> SELECT MONTH_NAME,STAR_SIGN,COUNT(*) FROM CALENDAR GROUP BY MONTH_NAME,STAR_SIGN ORDER BY 1,2;


MONTH_NAME STAR_SIGNCOUNT(*)
---------- ---------- ----------
1 摩羯座 73728
2 水瓶座 24576
3 双鱼座 24576
4 白羊座 24576
5 金牛座 24576
6 双子座 24576
7 巨蟹座 24576
8 狮子座 24576
9 处女座 24576
10 天秤座 24576
11 天蝎座 24576
12 射手座 49152


12 rows selected.

由于数据分布不均，导致E-Rows不等于A-Rows，但和没使用增强统计信息相比，数据更准确了，再看下10053。

执行10053

alter session set tracefile_identifier='BB10053';
alter session set events '10053 trace name context forever ,level 1';
SELECT COUNT(*) FROM CALENDAR WHERE MONTH_NAME=10 AND STAR_SIGN='天秤座';
alter session set events '10053 trace name context off';

查看,10053 trc文件， Card: 30720

vi cjc_ora_21149_BB10053.trc
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CALENDAR Alias: CALENDAR
#Rows: 368640 #Blks: 1252 AvgRowLen: 31.00 ChainCnt: 0.00
Access path analysis for CALENDAR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[CALENDAR]
Column (#1): MONTH_NAME(
AvgLen: 3 NDV: 12 Nulls: 0 Density: 0.083333 Min: 1 Max: 12
Column (#4): STAR_SIGN(
AvgLen: 10 NDV: 12 Nulls: 0 Density: 0.083333
Column (#5): SYS_STUJAZ#M82V9AIVJF$ZMSI#RN$(
AvgLen: 12 NDV: 12 Nulls: 0 Density: 0.083333
ColGroup (#1, VC) SYS_STUJAZ#M82V9AIVJF$ZMSI#RN$
Col#: 1 4 CorStregth: 12.00
ColGroup Usage:: PredCnt: 2 Matches Full: #1 Partial: Sel: 0.0833
Table: CALENDAR Alias: CALENDAR
Card: Original: 368640.000000 Rounded: 30720 Computed: 30720.00 Non Adjusted: 30720.00
Access Path: TableScan
Cost: 343.88 Resp: 343.88 Degree: 0
Cost_io: 341.00 Cost_cpu: 106298443
Resp_io: 341.00 Resp_cpu: 106298443
Best:: AccessPath: TableScan
Cost: 343.88 Degree: 1 Resp: 343.88 Card: 30720.00 Bytes: 0




***************************************

场景二：

谓词列引用了函数，导致优化器无法正确预估列真实使用情况，如果不创建函数索引，是否还有其他方法提高准确度？

先看没有使用函数的查询语句执行计划

SELECT /*+gather_plan_statistics*/ COUNT(*) FROM CALENDAR WHERE STAR_SIGN='天秤座';
COUNT(*)
----------
24576

查看执行计划

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 || 1 |00:00:00.02 | 1256 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 1256 |
|* 2 | TABLE ACCESS FULL| CALENDAR | 1 | 30720 | 24576 |00:00:00.01 | 1256 |
-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("STAR_SIGN"='天秤座'

可以看到E-Rows和A-Rows相差不大，其中E-Rows 是通过表总行数NUM_ROWS=368640 除以 STAR_SIGN NDV=12得到的；

E-Rows：368640/12=30720

使用函数

SELECT /*+gather_plan_statistics*/ COUNT(*) FROM CALENDAR WHERE UPPER(STAR_SIGN)='天秤座';
COUNT(*)
----------
24576

查看执行计划

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 || 1 |00:00:00.07 | 1256 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.07 | 1256 |
|* 2 | TABLE ACCESS FULL| CALENDAR | 1 | 3686 | 24576 |00:00:00.05 | 1256 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("STAR_SIGN")='天秤座')

当列上有函数时，优化器默认无法知道函数对列的影响，统一认为返回1%的数据，所以预估基数为3686，和真实数据相差较大。

E-Rows：368640*0.01=3686

通过10053看下执行计划

alter session set tracefile_identifier='CC10053';
alter session set events '10053 trace name context forever ,level 1';
SELECT COUNT(*) FROM CALENDAR WHERE UPPER(STAR_SIGN)='天秤座';
alter session set events '10053 trace name context off';

查看：

vi cjc_ora_21149_CC10053.trc
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CALENDAR Alias: CALENDAR
#Rows: 368640 #Blks: 1252 AvgRowLen: 31.00 ChainCnt: 0.00
Access path analysis for CALENDAR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[CALENDAR]
Table: CALENDAR Alias: CALENDAR
Card: Original: 368640.000000 Rounded: 3686 Computed: 3686.40 Non Adjusted: 3686.40
Access Path: TableScan
Cost: 344.84 Resp: 344.84 Degree: 0
Cost_io: 341.00 Cost_cpu: 141626443
Resp_io: 341.00 Resp_cpu: 141626443
Best:: AccessPath: TableScan
         Cost: 344.84  Degree: 1  Resp: 344.84  Card: 3686.40  Bytes: 0


***************************************

如果不创建函数索引，是否还有其他方法提高准确度？

添加增强统计信息，Expression Statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'CALENDAR', method_opt =>'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (UPPER(STAR_SIGN))');

收集表统计信息

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 'calendar',estimate_percent=>100,method_opt => 'for all columns size 1',CASCADE=> TRUE,no_invalidate=> FALSE);

查看增强统计信息

SELECT EXTENSION_NAME, EXTENSION FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='CALENDAR';
EXTENSION_NAME EXTENSION
------------------------------ ---------------------------------------------------
SYS_STUJAZ#M82V9AIVJF$ZMSI#RN$ ("MONTH_NAME","STAR_SIGN")
SYS_STU3YNUSG336CSVO605Z94BBZS (UPPER("STAR_SIGN")

自动为UPPER("STAR_SIGN")创建了一个虚拟列

col COL_GROUP for a30;
SELECT e.EXTENSION col_group, t.NUM_DISTINCT, t.HISTOGRAM
FROM USER_STAT_EXTENSIONS e, USER_TAB_COL_STATISTICS t
WHERE e.EXTENSION_NAME=t.COLUMN_NAME
AND e.TABLE_NAME=t.TABLE_NAME
AND t.TABLE_NAME='CALENDAR';


COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
("MONTH_NAME","STAR_SIGN") 12 NONE
(UPPER("STAR_SIGN")) 12 NONE

查看NDV

col COLUMN_NAME for a30;
SELECT TABLE_NAME,COLUMN_NAME,NUM_DISTINCT as NDV FROM USER_TAB_COL_STATISTICS ORDER BY 1,2;
TABLE_NAMECOLUMN_NAME NDV
--------------- ------------------------------ ----------
CALENDARDAY_NAME 20
CALENDARMONTH_NAME 12
CALENDARSTAR_SIGN 12
CALENDARSYS_STU3YNUSG336CSVO605Z94BBZS 12
CALENDARSYS_STUJAZ#M82V9AIVJF$ZMSI#RN$ 12
CALENDARWEEK_NAME7

使用函数

SELECT /*+gather_plan_statistics*/ COUNT(*) FROM CALENDAR WHERE UPPER(STAR_SIGN)='天秤座';
COUNT(*)
----------
24576

查看执行计划

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 || 1 |00:00:00.09 | 1256 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 1256 |
|* 2 | TABLE ACCESS FULL| CALENDAR | 1 | 30720 | 24576 |00:00:00.12 | 1256 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER("STAR_SIGN")='天秤座')

可以看到E-Rows和A-Rows已经相差不大了，

之前 E-Rows =3686，A-Rows=24576

现在 E-Rows =30720，A-Rows=24576

其中E-Rows 是通过表总行数NUM_ROWS=368640 除以 STAR_SIGN NDV=12得到的；

E-Rows：368640/12=30720

通过10053看下执行计划

alter session set tracefile_identifier='EE10053';
alter session set events '10053 trace name context forever ,level 1';
SELECT COUNT(*) FROM CALENDAR WHERE UPPER(STAR_SIGN)='天秤座';
alter session set events '10053 trace name context off';

vi cjc_ora_21149_EE10053.trc
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CALENDAR Alias: CALENDAR
#Rows: 368640 #Blks: 1252 AvgRowLen: 41.00 ChainCnt: 0.00
Access path analysis for CALENDAR
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CALENDAR[CALENDAR]
***** Virtual column Adjustment ******
Column name SYS_STU3YNUSG336CSVO605Z94BBZS
cost_cpu 150.00
cost_io 179769313486231570814527423731704356798070567525844996598917476803157260780028538760589558632766878171540458953514382464234321326889464182768467546703537516986049910576551282076245490090389328944075868508455133942304583236903222948165808559332123348274797826204144723168738177180919299881250404026184124858368.00
***** End virtual column Adjustment ******
Column (#6): SYS_STU3YNUSG336CSVO605Z94BBZS(
AvgLen: 10 NDV: 12 Nulls: 0 Density: 0.083333
Table: CALENDAR Alias: CALENDAR
Card: Original: 368640.000000 Rounded: 30720 Computed: 30720.00 Non Adjusted: 30720.00
Access Path: TableScan
Cost: 344.84 Resp: 344.84 Degree: 0
Cost_io: 341.00 Cost_cpu: 141626443
Resp_io: 341.00 Resp_cpu: 141626443
Best:: AccessPath: TableScan
Cost: 344.84 Degree: 1 Resp: 344.84 Card: 30720.00 Bytes: 0




***************************************

#也可以删除增强统计信息

#EXEC DBMS_STATS.DROP_EXTENDED_STATS(null,'calendar','(MONTH_NAME,STAR_SIGN)');

###chenjuchao 20240203###

参考链接：

【 Oracle 公益课堂 】Oracle 统计信息管理
Home / Database / Oracle Database Online Documentation 11g, Release 2 (11.2) / Database Administration/Database Performance Tuning Guide/13 Managing Optimizer Statistics
https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#i41810