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

IT小Chen 2024-02-04

395

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

问题一：

查询语句，谓词有单个列，优化器预估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

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

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

评论