[20230221]19c 調整cluster factor.txt

[20230221]19c 調整cluster factor.txt



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.