datapump and the clustering factor



The 'clustering factor' has long been discussed with regard to performance tuning and there are lots of excellent articles on that subject. I'm not going to go into any great detail about how this is calculated or indeed how it affects the optimizer plan formulation. The example below is a simple one where i use datapump to change the clustering factor of an index. This may be a useful technique if you are doing a migration anyway but it's not anything you can't do by other means.

For the purpose of this example I'll just say the clustering factor means this:

If most of the rowids in an index block point to the same 'table' block then the clustering factor is 'small' i.e. an index range scan would result in fewer table blocks having to be accessed. A smaller clustering factor makes the index range scan more likely as less i/o is needed.

Hmm even that sounded a bit woolly.... (a picture explains it best - which i don't have)

Anyway lets do the example to show how datapump can be used to help change this number.

First up we create a table and populate with 1 million rows.


SQL> create table demo (col1 number,col2 number,col3 date);

Table created.


SQL> insert into demo select rownum,trunc(dbms_random.value(0,100)),sysdate
  2   from dual connect by level <= 1000000;

1000000 rows created.


This populates col1 with a unique number (from the rownum part of the insert), col2 with a random number between 0 and 100 and col3 with the current date.

Here is some sample data



      COL1       COL2 COL3
---------- ---------- ---------
       820         39 24-FEB-14
       821         55 24-FEB-14
       822         22 24-FEB-14
       823         17 24-FEB-14
       824         49 24-FEB-14
       825         70 24-FEB-14
       826         98 24-FEB-14
       827         41 24-FEB-14
       828         32 24-FEB-14
       829         10 24-FEB-14
       830         82 24-FEB-14



Now lets create 2 indexes, one on col1 and one on col2.

SQL> create index index1 on demo (col1);

Index created.

SQL> create index index2 on demo (col2);

Index created.

SQL>


Now lets look at a few stats about the index (these were autocalculated when the index built)



select index_name,NUM_ROWS,leaf_blocks,CLUSTERING_FACTOR from user_indexes
/                          

INDEX_NAME                       NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
INDEX1                            1000000        2226              2889
INDEX2                            1000000        1952            279845




As you can see index1 has a 'small' clustering factor as the rows were entered sequentially, an index block will contain large ranges of sequential numbers all in the same data blocks. Index2 has a very large clustering factor as the data inserted is randomized so a single index block will point to many data block rowids.

If we now do a quick select to get some stats and a plan we see the following:


select * from demo where col2=42;
9922 rows selected.


Statistics
----------------------------------------------------------
          9  recursive calls
          2  db block gets
       3645  consistent gets
        638  physical reads
        148  redo size
     271576  bytes sent via SQL*Net to client
      13657  bytes received via SQL*Net from client
        663  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9922  rows processed


SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from demo where col2=42

Plan hash value: 4000794843

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DEMO |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------



So a full table scan as the optimizer looked at the clustering factor (decided it was crap) and then went and did a full table scan as it thought this would be quicker.


Now lets do a straightforward export/import of the whole table and see what difference that makes to things.

[oracle@localhost ~]$ expdp rich/rich tables=demo

Export: Release 11.2.0.2.0 - Production on Mon Feb 24 08:21:42 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RICH"."SYS_EXPORT_TABLE_01":  rich/******** tables=demo
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 23 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "RICH"."DEMO"                               19.05 MB 1000000 rows
Master table "RICH"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RICH.SYS_EXPORT_TABLE_01 is:
  /home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
Job "RICH"."SYS_EXPORT_TABLE_01" successfully completed at 08:22:06

[oracle@localhost ~]$ impdp rich/rich tables=demo table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Mon Feb 24 08:22:24 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RICH"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RICH"."SYS_IMPORT_TABLE_01":  rich/******** tables=demo table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RICH"."DEMO"                               19.05 MB 1000000 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "RICH"."SYS_IMPORT_TABLE_01" successfully completed at 08:22:37


SQL> exec dbms_stats.gather_table_stats(USER,'DEMO',cascade=>TRUE);

PL/SQL procedure successfully completed.


So thats all reloaded lets see what the stats look like now:

select * from demo where col2=42
9922 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3563  consistent gets
       2900  physical reads
          0  redo size
     271576  bytes sent via SQL*Net to client
      13657  bytes received via SQL*Net from client
        663  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9922  rows processed

SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic'));
EXPLAINED SQL STATEMENT:
------------------------
select * from demo where col2=42

Plan hash value: 4000794843

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DEMO |
----------------------------------


And it's essentially the same - we can see when we look at the clustering factor stats again that index2 is still 'big'

SQL> select index_name,NUM_ROWS,leaf_blocks,CLUSTERING_FACTOR from user_indexes
/    2 
INDEX1                            1000000        2226              5219
INDEX2                            1000000        1952            280764


 The reason for this? Well so far we haven't really changed anything. we dumped the table out and put it back in - this does nothing to change the order of the data that would influence the clustering factor. The numbers are slightly different but not by any amount that really changes things. So how do we improve the clustering of the data in col2?

This is where we can use datapump query. For the clustering factor to improve (i.e. get smaller) then the col2 rows need to be located close to each other in the table block - that way the index on col2 will be largely pointing to the same table block each time.

So how do we do that - easy add an order by to make the extract sort them as it unloads the data

So here is my parfile

[oracle@localhost ~]$ cat rich.par
include=TABLE:"='DEMO'"
query="where 1=1 order by col2"


Lets tidy up and then run the export

[oracle@localhost ~]$ rm /home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
[oracle@localhost ~]$ expdp rich/rich parfile=rich.par

Export: Release 11.2.0.2.0 - Production on Mon Feb 24 08:30:25 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RICH"."SYS_EXPORT_SCHEMA_01":  rich/******** parfile=rich.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 24 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "RICH"."DEMO"                               19.04 MB 1000000 rows
Master table "RICH"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RICH.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/app/oracle/admin/orcl/dpdump/expdat.dmp
Job "RICH"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:30:51


OK dumpfile now extracted which should have the data in col2 order now. Lets put it back in and see what happens


[oracle@localhost ~]$ impdp rich/rich tables=demo table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Mon Feb 24 08:31:15 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "RICH"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "RICH"."SYS_IMPORT_TABLE_01":  rich/******** tables=demo table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "RICH"."DEMO"                               19.04 MB 1000000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "RICH"."SYS_IMPORT_TABLE_01" successfully completed at 08:31:24


We need to gather stats again as the import puts the 'bad' stats back on - we need to regather fresh stats based on the data now.

SQL> exec dbms_stats.gather_table_stats(USER,'DEMO',cascade=>TRUE);

PL/SQL procedure successfully completed.


Now lets look at the clustering factor


SQL> select index_name,NUM_ROWS,leaf_blocks,CLUSTERING_FACTOR from user_indexes;

INDEX_NAME                       NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
INDEX1                            1000000        2226            990062
INDEX2                            1000000        1952              2905


And we can see that the clustering factor has drastically changed. It is now small for col2 and large for col1. So the data in the table is in col2 order and not col1 order.

Lets now try that select again

select * from demo where col2=42
9922 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1373  consistent gets
         31  physical reads
          0  redo size
     311264  bytes sent via SQL*Net to client
      13657  bytes received via SQL*Net from client
        663  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       9922  rows processed


SQL> select * from table(dbms_xplan.display_cursor(null,null,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from demo where col2=42

Plan hash value: 1024027054

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEMO   |
|   2 |   INDEX RANGE SCAN          | INDEX2 |
----------------------------------------------


14 rows selected.







So the plan has changed as there are now considerably less data blocks that need to be accessed to find where col2=42 as the data is 'clustered' together.

So in this particular case if this query and the clustering of this data is important for our performance then we should look at ordering the data in the table - perhaps by using the datapump method.

However - beware - the clustering factor for col1 is now 'crap' - so you may resolve one issue and cause another.

As with all performance issues - the key thing is to know your data.




0 comments:

Post a Comment