Home > Performance > Plan and Table Fragmentation

Plan and Table Fragmentation


Performance and table fragmentation are related, specailly there are lots free blocks in the table extents.   A frequent DELETE leaves a table fragmented and can dent the performance of the SQL queries.  Oracle comes with SHRINK command or MOVE etc to re-organise the table and pack the blocks thickly.

Well, the question here is can the ‘Plan’ take a different route due to fragmentation?  The answer is yes.

I have created two identical test tables  (TEST and TEST1  from DBA_OBJECTS) with index OWNER column.    TEST table contains 26698 and TEST1 contains 10 respectively.

SQL> create table test as select * from dba_objects;

Table created.

SQL> insert into test select * from test;

13349 rows created.

SQL> commit ;

Commit complete.

SQL> create table test1 as select * from dba_objects where rownum < 11;

Table created.

SQL> create index i_test on test(owner);

Index created.

SQL> create index i_test1 on test1(owner);

Index created.
SQL> exec dbms_stats.gather_table_stats(user,’Test1′,cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,’Test’,cascade=>true);

PL/SQL procedure successfully completed.
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
———————————— ———– ——————————
optimizer_mode                       string      ALL_ROWS

 As expected 10 record table used a FTS while the TEST tbale used and index, see below.  From TEST table SELECT shows and index read as

SQL> select object_name from test where owner=’POB’;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
—————————————————
SQL_ID  94nznfdv2dss8, child number 0
————————————-
select object_name from test where owner=’POB’

Plan hash value: 1085635733

————————————————————————————–
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |       |       |    56 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |  1907 | 45768 |    56   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TEST |  1907 |       |     5   (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   2 – access(“OWNER”=’POB’)

 

While from TEST1, we do not expect an index read as there are only 10 records, rather a FTS is optimal. 

SQL> select object_name from test1 where owner=’POB’;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
———————————————–
SQL_ID  0vgddbvrsvfk5, child number 0
————————————-
select object_name from test1 where owner=’POB’

Plan hash value: 3852271815

—————————————————————————
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |    11 |     2   (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“OWNER”=’POB’)

So far everything work well as expected.  Now DELETE all rows from TEST leaving 10 – 15 records.

SQL> delete from test where rownum < 26686;

26685 rows deleted.

SQL> commit ;

Commit complete.
SQL> select count(*) from test;

  COUNT(*)
———-
        13

Flush the shared pool and gather fresh STATISTICS.

SQL> exec dbms_stats.gather_table_stats(user,’Test’,cascade=>true);

PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;

System altered.

Re-execute the same SQL and see what is the plan.

SQL> select object_name from test where owner=’POB’;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
————————————————————-
SQL_ID  94nznfdv2dss8, child number 0
————————————-
select object_name from test where owner=’POB’

Plan hash value: 1085635733

————————————————————————————–
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST   |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_TEST |     1 |       |     1   (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   2 – access(“OWNER”=’POB’)

 With merly 13 records, optimizer is selected an index read, while similar table with 10 records employs FTS!  The only difference is that number of free blocks or location of high water mark, otherwise both tables are identical.

Now move the table to reset the high water mark, so that empty blocks will be released.

SQL> alter table test move;

Table altered.

SQL> alter index I_TEST rebuild;

Index altered.

SQL> exec dbms_stats.gather_table_stats(user,’Test’,cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

With fresh statistics, run the same SQL.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
—————————————
SQL_ID  94nznfdv2dss8, child number 0
————————————-
select object_name from test where owner=’POB’

Plan hash value: 217508114

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    27 |     2   (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

   1 – filter(“OWNER”=’POB’)

 

Wow, once the fragmentation is removed, optimizer started using FTS.  So, a bad fragmentation can lead to different plan, interestingly for the sake of improving the performance.  CBO is intelliant enough to avoid an FTS while there are too many empty blocks under the HWM, no matter how many records are in the table.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: