Row Movement & Shrink Space

Thursday, August 7, 2008

Few days ago one developer deleted several rows from table and committed before he realized that he made a mistake with deletion.

I've decided to use flashback for restoring table to previous state - but after I executed statement "ORA-08189: cannot flashback the table because row movement is not enabled" showed on my screen. To use flashback, you must have row movement enabled.


With enabling row movement you are signalizing to Oracle that rows of the table could be moved for various operations.
We need row movement enabled for flashback operations, for row migrations in partitioned tables after partition key value is updated, and for space savings. I'll demonstrate performance boost after using "shrink space" on specified table.



Online shrinking of database segments is operation that you should run after enabling row movement. You can use this feature only on locally managed tablespaces with automatic segment management (ASSM).
Segment shrink is online operation so DML operations could be issued during data movement. Indexes are maintained during shrink operation and remain usable after operation is complete. Operation does not require extra disk space to shrink operation, but it will use processing resources while running.
Segment shrink compacts the segment, adjusts high water mark, and releases reclaimed space.
NOTE!
Disable rowid-based triggers defined on object as shrinking requires that rows must be moved to new locations.

We could expect better performance on full scans or index scans as more rows per block are packed together.


TEST CASE

"BT" table is very similar to "BIG_TABLE" that Tom Kyte is using for his testing. I've deleted/added some rows to get fragmented free space for testing purposes. Also I'm using Tom Kyte's package "runstats_pkg".


SQL> select count(*) from bt;

COUNT(*)
----------
2666668

1 row selected.

SQL> select sum(bytes) from dba_extents where segment_name='BT';

SUM(BYTES)
----------
508887040

1 row selected.


I will create another table with ROW MOVEMENT enabled. Also I'll execute SHRINK SPACE on that table.
SQL> create table bt_rm as select * from bt;

Table created.

Elapsed: 00:01:03.54
SQL> alter table bt_rm enable row movement;

Table altered.

Elapsed: 00:00:00.14
SQL> alter table bt_rm shrink space;

Table altered.

Elapsed: 00:00:00.07
SQL> select sum(bytes) from dba_extents where segment_name='BT_RM';

SUM(BYTES)
----------
339542016

1 row selected.

Elapsed: 00:00:00.96


As you can see my space saving is : 169345024 bytes.


I'll test performance of full table scan on tables.
select count(*) from bt;
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 | 16879   (1)| 00:03:23 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BT   |  2671K| 16879   (1)| 00:03:23 |
-------------------------------------------------------------------



SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.95
SQL> select count(*) from bt;

COUNT(*)
----------
2666668

1 row selected.

Elapsed: 00:00:12.18
SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL> select count(*) from bt_rm;

COUNT(*)
----------
2666668

1 row selected.

Elapsed: 00:00:08.09



Run1 ran in 2812 hsecs
Run2 ran in 2136 hsecs
run 1 ran in 131.65% of the time

LATCH.SQL memory manager worka          733  474  -259
STAT...recursive calls                  630  345  -285
LATCH.library cache load lock           316  6  -310
LATCH.enqueues                          694  334  -360
LATCH.enqueue hash chains               709  339  -370
STAT...user I/O wait time             1,195  775  -420
STAT...DB time                        1,312  807  -505
LATCH.cache buffers lru chain         1,391  854  -537
LATCH.object queue header oper        1,403  863  -540
STAT...physical read total mul        1,797  1,207  -590
STAT...Number of read IOs issu        1,886  1,275  -611
LATCH.simulator lru latch               672  11  -661
STAT...physical read total IO         2,003  1,339  -664
STAT...physical read IO reques        2,003  1,339  -664
STAT...Elapsed Time                   2,838  2,138  -700
LATCH.simulator hash latch              774  69  -705
LATCH.cache buffers chains           15,342  1,262  -14,08
STAT...physical reads                62,596  42,02  -20,576
STAT...table scan blocks gotte       61,827  41,239  -20,588
STAT...consistent gets direct        61,765  41,174  -20,591
STAT...physical reads direct         61,765  41,174  -20,591
STAT...no work - consistent re       62,029  41,283  -20,746
STAT...session logical reads         62,161  41,377  -20,784
STAT...consistent gets               62,137  41,348  -20,789
STAT...session pga memory           -65,536  0  65,536
LATCH.session allocation             67,003  338  -66,665
LATCH.kokc descriptor allocati      160,346  0  -160,346
LATCH.shared pool simulator         242,496  132  -242,364
LATCH.shared pool                   335,646  357  -335,289
LATCH.row cache objects              743,83  285  -743,545
STAT...table scan rows gotten     4,002,710  2,670,842  -1,331,868
STAT...Effective IO time         11,168,449  7,209,080  -3,959,369
STAT...physical read bytes      512,786,432  344,227,840  -168,558,592
STAT...physical read total byt  512,786,432  344,227,840  -168,558,592


Run1 latches total versus runs -- difference and pct
Run1              Run2         Diff                Pct
1,573,023       6,348   -1,566,675    24,779.82%


Yo could see various improvements of full table can on shrinked table. I have time savings, less physical reads, latches...


What will happen if I execute insert statement?


SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL> insert into bt select * from big_table where rownum<250000;

249999 rows created.

Elapsed: 00:00:19.56
SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> insert into bt_rm select * from big_table where rownum<250000;

249999 rows created.

Elapsed: 00:00:09.62
SQL> exec runstats_pkg.rs_stop;
Run1 ran in 3475 hsecs
Run2 ran in 2358 hsecs
run 1 ran in 147.37% of the time

STAT...db block gets from cach          23,359       8,790     -14,569
STAT...consistent gets from ca          26,884      11,545     -15,339
STAT...consistent gets                  26,884      11,545     -15,339
LATCH.MinActiveScn Latch                23,128       7,701     -15,427
STAT...redo entries                     37,918      16,788     -21,130
LATCH.DML lock allocation                    2      21,158      21,156
LATCH.cache buffers lru chain           35,220      13,334     -21,886
STAT...db block changes                 53,190      24,139     -29,051
STAT...calls to kcmgrs                  49,501      18,743     -30,758
LATCH.object queue header oper          59,862      23,298     -36,564
STAT...db block gets                    73,619      29,778     -43,841
STAT...db block gets from cach          73,619      29,778     -43,841
STAT...session logical reads           100,503      41,323     -59,180
STAT...session uga memory               65,464           0     -65,464
STAT...session pga memory               65,536           0     -65,536
LATCH.cache buffers chains             278,778     119,181    -159,597
STAT...undo change vector size       1,458,840     980,844    -477,996
STAT...redo size                    33,580,972  30,986,092  -2,594,880
STAT...physical read total byt     114,049,024  53,665,792 -60,383,232
STAT...physical read bytes         114,049,024  53,665,792 -60,383,232

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
438,934     228,987    -209,947    191.69%




Row movement is not enabled by default, but as you can see there are many benefits you can gain enabling it.



RELATED DOCUMENTS
Managing Space for Schema Objects


Share/Bookmark

0 comments:

Post a Comment