复合列扩展统计信息
1. 准备环境：$ . oraenv
ORACLE_SID = ?
The Oracle base remains unchanged with value /u01/app/oracle
$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 16 14:21:31 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.All rights reserved.
SQL> conn sh/oracle_4U
Connected.
SQL> create table t1( avarchar2(20) , b varchar2(20) , c varchar2(20)) ;
Table created.
SQL> begin
2for i in 1..100000
3loop
4 insert into t1 values ( 'a','b','c');
5end loop;
6commit;
7end;
8/
PL/SQL procedure successfully completed.
SQL> insert into t1 values ( 'x','y','c');
1 row created.
SQL> commit;
Commit complete.
SQL> create index ia on t1(a) ;
Index created.
SQL> create index ib on t1(b) ;
Index created.
SQL> create index ic on t1(c) ;
Index created.
收集优化器统计信息：
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强
PL/SQL procedure successfully completed.
2. 第一次执行查询：
SQL> set linesize 10000
SQL> set autot on
SQL> select* from t1 where a='x' and b='y' and c='c';
A B C
-------------------- -------------------- --------------------
x y c
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 25000 | 146K| 69 (2)| 00:00:01 |
|*1 |TABLE ACCESS FULL| T1 | 25000 | 146K| 69 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"='x' AND "B"='y' AND "C"='c')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
53recursive calls
5db block gets
287consistent gets
0physical reads
936redo size
688bytes sent via SQL*Net to client
411bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
13sorts (memory)
0sorts (disk)
1rows processed<b><font size="3">
</font></b>3. 收集复合列扩展统计信息：
declare
v1 varchar2(200);
begin
v1 :=dbms_stats.create_extended_stats('SH','T1','(a,b,c)');
end;
/
begin
dbms_stats.gather_table_stats('sh','t1',estimate_percent=>100,
method_opt=>'for all columns size auto for columns (a,b,c) size skewonly', cascade=>true);
end;
/
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. 第二次执行查询：
SQL> select* from t1 where a='x' and b='y' and c='c';
A B C
-------------------- -------------------- --------------------
x y c
Execution Plan
----------------------------------------------------------
Plan hash value: 38189099
--------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|*1 |TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 6 | 2 (0)| 00:00:01 |
|*2 | INDEX RANGE SCAN | IA | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"='y' AND "C"='c')
2 - access("A"='x')
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
----------------------------------------------------------
12recursive calls
0db block gets
16consistent gets
0physical reads
0redo size
688bytes sent via SQL*Net to client
411bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1rows processed
页:
[1]