复合列扩展统计信息

botang

1. 准备环境：

1. [oracle@cvcdds198 ~]$ . oraenv
   
2. ORACLE_SID = [rcat] ?
   
3. The Oracle base remains unchanged with value /u01/app/oracle
   
4. [oracle@cvcdds198 ~]$ sqlplus /nolog
   
5. 
   
6. SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 16 14:21:31 2022
   
7. Version 19.3.0.0.0
   
8. 
   
9. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
   
10. 
    
11. SQL> conn sh/oracle_4U
    
12. Connected.
    
13. SQL> create table t1( a  varchar2(20) , b varchar2(20) , c varchar2(20)) ;
    
14. 
    
15. Table created.
    

复制代码

1. SQL> begin
   
2.   2  for i in 1..100000
   
3.   3  loop
   
4.   4   insert into t1 values ( 'a','b','c');
   
5.   5  end loop;
   
6.   6  commit;
   
7.   7  end;
   
8.   8  /
   
9. 
   
10. PL/SQL procedure successfully completed.
    
11. 
    
12. SQL> insert into t1 values ( 'x','y','c');        
    
13. 
    
14. 1 row created.
    
15. 
    
16. SQL> commit;
    
17. 
    
18. Commit complete.

复制代码

1. SQL> create index ia on t1(a) ;
   
2. 
   
3. Index created.
   
4. 
   
5. SQL> create index ib on t1(b) ;
   
6. 
   
7. Index created.
   
8. 
   
9. SQL> create index ic on t1(c) ;
   
10. 
    
11. Index created.
    

复制代码

收集优化器统计信息：

1. SQL>  exec dbms_stats.gather_table_stats('hr','t1', estimate_percent=>100, method_opt=>'for all columns size auto',cascade=>true);  --注意如果使用size skewonly会用索引访问，都不必要收集下面的复合列统计信息，说明skewonly功能还是比auto强
   
2. 
   
3. PL/SQL procedure successfully completed.
   

复制代码

2. 第一次执行查询：

1. SQL> set linesize 10000
   
2. SQL> set autot on  
   
3. SQL> select  * from t1 where a='x' and b='y' and c='c';
   
4. 
   
5. A                     B                          C
   
6. -------------------- -------------------- --------------------
   
7. x                     y                          c
   
8. 
   
9. 
   
10. Execution Plan
    
11. ----------------------------------------------------------
    
12. Plan hash value: 3617692013
    
13. 
    
14. --------------------------------------------------------------------------
    
15. | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time         |
    
16. --------------------------------------------------------------------------
    
17. |   0 | SELECT STATEMENT  |         | 25000 |   146K|    69   (2)| 00:00:01 |
    
18. |*  1 |  TABLE ACCESS FULL| T1         | 25000 |   146K|    69   (2)| 00:00:01 |
    
19. --------------------------------------------------------------------------
    
20. 
    
21. Predicate Information (identified by operation id):
    
22. ---------------------------------------------------
    
23. 
    
24.    1 - filter("A"='x' AND "B"='y' AND "C"='c')
    
25. 
    
26. Note
    
27. -----
    
28.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
29. 
    
30. 
    
31. Statistics
    
32. ----------------------------------------------------------
    
33.          53  recursive calls
    
34.           5  db block gets
    
35.         287  consistent gets
    
36.           0  physical reads
    
37.         936  redo size
    
38.         688  bytes sent via SQL*Net to client
    
39.         411  bytes received via SQL*Net from client
    
40.           2  SQL*Net roundtrips to/from client
    
41.          13  sorts (memory)
    
42.           0  sorts (disk)
    
43.           1  rows processed<b><font size="3">
    
44. </font></b>

复制代码

3. 收集复合列扩展统计信息：

1. declare
   
2. v1 varchar2(200);
   
3. begin
   
4. v1 :=dbms_stats.create_extended_stats('SH','T1','(a,b,c)');
   
5. end;
   
6. /
   
7. 
   
8. begin
   
9. dbms_stats.gather_table_stats('sh','t1',estimate_percent=>100,
   
10.                             method_opt=>'for all columns size auto for columns (a,b,c) size skewonly', cascade=>true);
    
11. end;
    
12. /

复制代码

1. select   * from dba_tab_col_statistics where owner='SH' and table_name='T1';   

复制代码

SH    T1    C    1    63    63    0.0000049999500005    0    1    16-MAY-22    100001    YES    NO        2    FREQUENCY    SHARED
SH    T1    SYS_STUM4KJU$CCICS9C1UJ6UWC4YP    2    C55A571A4A12    C55F2A1E5C3A    0.0000049999500005    0    2    16-MAY-22    100001    YES    NO        12    FREQUENCY    SHARED
SH    T1    A    2    61    78    0.0000049999500005    0    2    16-MAY-22    100001    YES    NO        2    FREQUENCY    SHARED
SH    T1    B    2    62    79    0.0000049999500005    0    2    16-MAY-22    100001    YES    NO        2    FREQUENCY    SHARED

4. 第二次执行查询：

1. SQL> select  * from t1 where a='x' and b='y' and c='c';
   
2. 
   
3. A                     B                          C
   
4. -------------------- -------------------- --------------------
   
5. x                     y                          c
   
6. 
   
7. 
   
8. Execution Plan
   
9. ----------------------------------------------------------
   
10. Plan hash value: 38189099
    
11. 
    
12. --------------------------------------------------------------------------------------------
    
13. | Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time           |
    
14. --------------------------------------------------------------------------------------------
    
15. |   0 | SELECT STATEMENT                    |           |         1 |         6 |         2   (0)| 00:00:01 |
    
16. |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   |         1 |         6 |         2   (0)| 00:00:01 |
    
17. |*  2 |   INDEX RANGE SCAN                    | IA   |         1 |           |         1   (0)| 00:00:01 |
    
18. --------------------------------------------------------------------------------------------
    
19. 
    
20. Predicate Information (identified by operation id):
    
21. ---------------------------------------------------
    
22. 
    
23.    1 - filter("B"='y' AND "C"='c')
    
24.    2 - access("A"='x')
    
25. 
    
26. Note
    
27. -----
    
28.    - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
29. 
    
30. 
    
31. Statistics
    
32. ----------------------------------------------------------
    
33.          12  recursive calls
    
34.           0  db block gets
    
35.          16  consistent gets
    
36.           0  physical reads
    
37.           0  redo size
    
38.         688  bytes sent via SQL*Net to client
    
39.         411  bytes received via SQL*Net from client
    
40.           2  SQL*Net roundtrips to/from client
    
41.           0  sorts (memory)
    
42.           0  sorts (disk)
    
43.           1  rows processed
    

复制代码