In case you have been wondering how to measure the effect of an “ALTER TABLE MOVE” command on a full table scan then this little test will show it to you.

Using ALTER TABLE MOVE is a good idea if you want to reorganize your tables but do not wish to enable row movement and export-import is also not an option. One big advantage of MOVE is that there is no need to recreate the indexes of the table, a quick ALTER INDEX REBUILD is enough.

After deleting a large set of records (~30%) from a table with a size of 1,8GB I did a test with full table scan to measure the number of reads. This was important as in many cases a full table scan is not possible to avoid and parallelizing is not the best solution in a consolidated environment where more databases are using the same host or even instance.

So here is the table:

SQL> select sum(bytes)/1024 "used space in kb" from dba_segments where segment_name='MYTABLE';
used space in kb
----------------
         1912832

As the table has some indexes on it I am using the FULL SQL hint to force a full table scan for the count. I am also using the gather_plan_statistics SQL hint in order to gather actual wait statistics during SQL execution.

SQL> select /*+ FULL(e) gather_plan_statistics */ count(*) from MYSCHEMA.MYTABLE e;
COUNT(*)
----------
   2156876

set lines 600
set pages 0
SQL> select * from table (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));

SQL_ID 40rnfmvf1jxc7, child number 0
-------------------------------------
select /*+ FULL(e) gather_plan_statistics */ count(*) from
MYSCHEMA.MYTABLE e

Plan hash value: 1355506960
------------------------------------------------------------------------------------------------------------
| Id| Operation          |Name   | Starts| E-Rows| Cost (%CPU)| E-Time | A-Rows| A-Time    | Buffers| Reads|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |       |     1 |       | 64620 (100)|        |     1 |00:00:23.02|     240K| 238K|
| 1 |  SORT AGGREGATE    |       |     1 |     1 |            |        |     1 |00:00:23.02|     240K| 238K|
| 2 |   TABLE ACCESS FULL|MYTABLE|     1 |  2156K|   64620 (1)|00:12:56|  2156K|00:00:14.72|     240K| 238K|
------------------------------------------------------------------------------------------------------------

So we can see that before the ALTER TABLE MOVE operation there were 238K physical disk reads performed during a full table scan.

SQL> ALTER TABLE MYSCHEMA.MYTABLE MOVE;

Table altered.


SQL> select sum(bytes)/1024 "used space in kb" from dba_segments where segment_name='MYTABLE';

used space in kb
----------------
         1464320

As you can see after MOVE the table segment is now using less space. Now I execute my query again:

set lines 600
set pages 0
SQL> select * from table (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALL ALLSTATS OUTLINE PEEKED_BINDS LAST'));
SQL_ID  40rnfmvf1jxc7, child number 0
-------------------------------------
select /*+ FULL(e) gather_plan_statistics */ count(*) from
MYSCHEMA.MYTABLE e

Plan hash value: 1355506960

------------------------------------------------------------------------------------------------------------
| Id| Operation          |Name   | Starts| E-Rows| Cost (%CPU)| E-Time | A-Rows|   A-Time  | Buffers| Reads|
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |       |      1|       | 64620 (100)|        |     1 |00:00:03.24|    177K|  177K|
| 1 |  SORT AGGREGATE    |       |      1|     1 |            |        |     1 |00:00:03.24|    177K|  177K|
| 2 |   TABLE ACCESS FULL|MYTABLE|      1|  2156K| 64620   (1)|00:12:56|  2156K|00:00:04.01|    177K|  177K|
------------------------------------------------------------------------------------------------------------

Using DBMS_XPLAN.DISPLAY_CURSOR with the ALLSTATS and LAST parameters we can display the actual resource consumption of the query at its last execution. After the MOVE operation we can see that the number of physical reads is just 177K which can make a big difference.