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
0 Comments:
Post a Comment