[20230221]19c 调整cluster factor.txt

[20230221]19c 调整cluster factor.txt https://fatdba.com/2022/05/16/bad-clustering-factor-on-12-1-have-you-tried-attribute-clustering/ A quick explanation on the clustering factor, it is a measure of the ordered-ness of an index in comparison to the table that it is based on. That would be a simple explanation on Oracle's clustering factor (CF), but if you want to read more about it I would recommend to read blog posts by Richard Foote, Jonathan Lewis and few other great articles by Randolf Geist. Okay, coming back to the case – We tried few methods (fixed BLevel, Leaf_Blocks, degree, stats recollection etc.), but none of them helped much to stabilize performance of the query, and finally we tried something that was introduced in Oracle 12.1 the 'Attribute Clustering' that helped us to resolve the problem. The attribute clustering improves physical IOs for tables and its partitions. An attribute-clustered table stores data in close proximity on disk in an ordered way based on the values of a certain set of columns in the table or a set of columns in the other tables. Attribute clustering is a user-defined table directive that provides data clustering on one or more columns in a table. The directives can be specified when the table is created or modified. There are two types of attribute clustering:     With Linear Ordering : Linear ordering stores the data according to the order of specified columns. This is the     default type of clustering.     With Interleaved Ordering : It accurately determines exactly where data is located on the disk. This enabled I/O     Pruning. This uses a special multidimensional clustering technique based on Z-order curve fitting. Note: Zone mapping is a separately licensed feature. --//感觉这特性需要licensed许可. 1.环境: SYS@192.168.100.235:1521/orcl> @ pr ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 19.0.0.0.0 BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.测试建立: create table lis.new_test (id number, DOB date, text varchar2(40)) tablespace users; --//注:如果以sys模式建立表,不支持后面的move online操作.oracle估计为了避免意外操作. INSERT into lis.new_test SELECT rownum , sysdate-trunc(dbms_random.value(0, 20000)), 'PRASHANT DIXIT' FROM dual CONNECT BY LEVEL <= 2000000; commit; create index idx_newtest on lis.new_test(dob); @ gts lis.new_test exec dbms_stats.gather_table_stats('LIS', 'NEW_TEST', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) SELECT t.table_name      , i.index_name      , t.blocks      , t.num_rows      , i.clustering_factor   FROM dba_tables t      , dba_indexes i  WHERE t.table_name = i.table_name    AND i.index_name = 'IDX_NEWTEST'    and t.owner='LIS'; TABLE_NAME INDEX_NAME  BLOCKS   NUM_ROWS CLUSTERING_FACTOR ---------- ------------------ ---------- ----------------- NEW_TEST   IDX_NEWTEST  10097    2000000           1989139 3.测试: @sl all select * from lis.new_test where dob between '2017/01/01' and '2017/01/31'; ... SYS@192.168.100.235:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  fu6tg59u9tgw4, child number 0 ------------------------------------- select * from lis.new_test where dob between '2017/01/01' and '2017/01/31' Plan hash value: 3607091976 ------------------------------------------------------------------------------------------------------------------------- | Id  | Operation          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | ------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |          |      1 |        |       |  2765 (100)|          |   3026 |00:00:00.12 |    9382 | |*  1 |  FILTER            |          |      1 |        |       |            |          |   3026 |00:00:00.12 |    9382 | |*  2 |   TABLE ACCESS FULL| NEW_TEST |      1 |   3200 | 89600 |  2765   (2)| 00:00:01 |   3026 |00:00:00.12 |    9382 | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / NEW_TEST@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_DATE('2017/01/31')>=TO_DATE('2017/01/01'))    2 - filter(("DOB">='2017/01/01' AND "DOB"<='2017/01/31')) 27 rows selected. --//因为CF很大,并没有使用索引,执行计划走的是全表扫描. 4.调整CF: SYS@192.168.100.235:1521/orcl> alter table lis.NEW_TEST add clustering by linear order(DOB) without materialized zonemap; Table altered. SYS@192.168.100.235:1521/orcl> alter table lis.NEW_TEST move online; Table altered. -- Now if you check you will the improved CF of the Index. SELECT t.table_name      , i.index_name      , t.blocks      , t.num_rows      , i.clustering_factor   FROM dba_tables t      , dba_indexes i  WHERE t.table_name = i.table_name    AND i.index_name = 'IDX_NEWTEST'    and t.owner='LIS'; TABLE_NAME INDEX_NAME   BLOCKS   NUM_ROWS CLUSTERING_FACTOR ---------- ------------ ------ ---------- ----------------- NEW_TEST   IDX_NEWTEST   10097    2000000              9277 --//CF=9277 select * from lis.new_test where dob between '2017/01/01' and '2017/01/31'; SYS@192.168.100.235:1521/orcl> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  fu6tg59u9tgw4, child number 0 ------------------------------------- select * from lis.new_test where dob between '2017/01/01' and '2017/01/31' Plan hash value: 648771947 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                            | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  | ------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                     |             |      1 |        |       |    26 (100)|          |   3026 |00:00:00.01 |      88 |     10 | |*  1 |  FILTER                              |             |      1 |        |       |            |          |   3026 |00:00:00.01 |      88 |     10 | |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| NEW_TEST    |      1 |   3200 | 89600 |    26   (0)| 00:00:01 |   3026 |00:00:00.01 |      88 |     10 | |*  3 |    INDEX RANGE SCAN                  | IDX_NEWTEST |      1 |   3200 |       |    11   (0)| 00:00:01 |   3026 |00:00:00.01 |      42 |     10 | ------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / NEW_TEST@SEL$1    3 - SEL$1 / NEW_TEST@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_DATE('2017/01/31')>=TO_DATE('2017/01/01'))    3 - access("DOB">='2017/01/01' AND "DOB"<='2017/01/31') --//现在可以使用索引. 5.收尾: SYS@192.168.100.235:1521/orcl> drop table lis.NEW_TEST purge ; Table dropped.