OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » enable row movement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

enable row movement [message #559499]

Tue, 03 July 2012 11:00

BeefStu
Messages: 208
Registered: October 2011

Senior Member

I have a job that runs the following commands for each table in a schema.. (I just inherited it ugggg)


alter table ODS.ODS_MONTHLY_MF_AUM enable row movement;
alter table ODS.ODS_MONTHLY_MF_AUM shrink space;
alter table ODS.ODS_MONTHLY_MF_AUM disable row movement;

On occassion I get this error when enabling row movement

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Other than dealing with apps that depend on ROWID's is there any
negative impact to always keeping row movement enabled for tables.

Secondly, does any body have a script that can determine if the space really needs to be shrunk for a table. Would you mind sharing?

Thanks to all who answer.

Report message to a moderator

Re: enable row movement [message #559500 is a reply to message #559499]

Tue, 03 July 2012 11:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

why do you ever need to shrink table?

Report message to a moderator

Re: enable row movement [message #559501 is a reply to message #559499]

Tue, 03 July 2012 11:11

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Other than dealing with apps that depend on ROWID's is there any
negative impact to always keeping row movement enabled for tables.

Current running cursor will return an "invalid rowid" error.
SHRINK operation must be done in maintenance window.
You can split it in 2: SPLIT COMPACT then SHRINK.

Now maybe the correct answer is to disable this job or just put an EXIT at its beginning.

Regards
Michel

Report message to a moderator

Re: enable row movement [message #559502 is a reply to message #559501]

Tue, 03 July 2012 11:17

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Are you saying if row movment is always enabled I will have issues with cursors.

As for COMPACT and SHRINK I will research..

Is there sql statment I can use to see if a table can benefit from shrink.. If so I can change my code to only shrink necessary tables.

Report message to a moderator

Re: enable row movement [message #559503 is a reply to message #559502]

Tue, 03 July 2012 11:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there sql statment I can use to see if a table can benefit from shrink.

SELECT 'SHRINK TABLE' FROM DUAL WHERE 1 = 2;

[Updated on: Tue, 03 July 2012 11:24]

Report message to a moderator

Re: enable row movement [message #559504 is a reply to message #559502]

Tue, 03 July 2012 11:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ You may have.
2/ This is the way Oracle recommends, see MOS note 242090.1
3/ Use dbms_space.space_usage

Regards
Michel

Report message to a moderator

Re: enable row movement [message #559506 is a reply to message #559504]

Tue, 03 July 2012 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

is below a reasonable approximation for SHRINK TABLE environment?
1) new data over time goes into TABLE_SHRINKER
2) periodically some data is purged (rows DELETED)
3) procedure is run to SHRINK TABLE
4) GOTO #1 above

Consider that if step #3 is is NOT done, then the free space will be reused by step #1
and Oracle can avoid growing the table to handle the new data from step #1

Why do you insist on treating the table like an accordion?

Report message to a moderator

Re: enable row movement [message #559510 is a reply to message #559504]

Tue, 03 July 2012 12:15

BeefStu
Messages: 208
Registered: October 2011

Senior Member

at black swan

The reason I think I need to shrink space is to gather all rows in a table into as few
extents as possible so empty extents can be freed. Is there a better way to proceed?

I am looking into Michel suggestion to do compact/shrink.. It sounds like this will lower
the high water mark and be more benefical.

Report message to a moderator

Re: enable row movement [message #559512 is a reply to message #559510]

Tue, 03 July 2012 12:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>. It sounds like this will lower the high water mark and be more benefical.
Only to have new data continuously raise HWM.
What you are doing is similar to having a shrinking gas tank on your car
so it does not have any air in the partially filled tank.
I challenge you to provide SQL & measurable results that any real benefit is obtained by all this movement.

Never confuse movement with progress.
Going around in circles is movement, but most folks do not consider it to be progress.

Report message to a moderator

Re: enable row movement [message #559516 is a reply to message #559512]

Tue, 03 July 2012 12:56

BeefStu
Messages: 208
Registered: October 2011

Senior Member

so your saying shrinking/compacting/lower HWM is just a waste since the data will be put back anyway?

Report message to a moderator

Re: enable row movement [message #559517 is a reply to message #559516]

Tue, 03 July 2012 13:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>so your saying shrinking/compacting/lower HWM is just a waste since the data will be put back anyway?
YES!
I wonder if HWM ever can be lowered.
You can view table like a push down stack.
New rows get added "on top" on the table while old data is removed from the "bottom" of the table.
So what can be done with HWM in this case?

Report message to a moderator

Re: enable row movement [message #560523 is a reply to message #559517]

Fri, 13 July 2012 18:28

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

Shrink Space reuses the sames extents (starting block_id is the same) and removes extra extents. Notice how there are 42 extents before the shrink space and only 41 afterwards. Also the last extent is trimmed down to the highwater mark.

ECSCDAQ > select file_id,extent_id,block_id,bytes from dba_extents
  2  where owner='ECSCDAQ' and segment_name='ALAN';

   FILE_ID  EXTENT_ID   BLOCK_ID      BYTES
---------- ---------- ---------- ----------
         4          0      28257      65536
         4          1      28265      65536
         4          2      28273      65536
         4          3      28281      65536
         4          4      28289      65536
         4          5      28297      65536
         4          6      28305      65536
         4          7      28313      65536
         4          8      28321      65536
         4          9      28329      65536
         4         10      28337      65536
         4         11      28345      65536
         4         12      28353      65536
         4         13      28361      65536
         4         14      28369      65536
         4         15      28377      65536
         4         16      28425    1048576
         4         17      28553    1048576
         4         18      28681    1048576
         4         19      28809    1048576
         4         20      28937    1048576
         4         21      29065    1048576
         4         22      29193    1048576
         4         23      29321    1048576
         4         24      29449    1048576
         4         25      29577    1048576
         4         26      29705    1048576
         4         27      29833    1048576
         4         28      29961    1048576
         4         29      30089    1048576
         4         30      30217    1048576
         4         31      30345    1048576
         4         32      30473    1048576
         4         33      30601    1048576
         4         34      30729    1048576
         4         35      30857    1048576
         4         36      30985    1048576
         4         37      31113    1048576
         4         38      31241    1048576
         4         39      31369    1048576
         4         40      31497    1048576
         4         41      31625    1048576

42 rows selected.

ECSCDAQ > alter table ECSCDAQ.ALAN enable row movement;

Table altered.

ECSCDAQ > alter table ECSCDAQ.ALAN shrink space;

Table altered.

ECSCDAQ > alter table ECSCDAQ.ALAN disable row movement;

Table altered.

ECSCDAQ > select file_id,extent_id,block_id,bytes from dba_extents
  2  where owner='ECSCDAQ' and segment_name='ALAN';

   FILE_ID  EXTENT_ID   BLOCK_ID      BYTES
---------- ---------- ---------- ----------
         4          0      28257      65536
         4          1      28265      65536
         4          2      28273      65536
         4          3      28281      65536
         4          4      28289      65536
         4          5      28297      65536
         4          6      28305      65536
         4          7      28313      65536
         4          8      28321      65536
         4          9      28329      65536
         4         10      28337      65536
         4         11      28345      65536
         4         12      28353      65536
         4         13      28361      65536
         4         14      28369      65536
         4         15      28377      65536
         4         16      28425    1048576
         4         17      28553    1048576
         4         18      28681    1048576
         4         19      28809    1048576
         4         20      28937    1048576
         4         21      29065    1048576
         4         22      29193    1048576
         4         23      29321    1048576
         4         24      29449    1048576
         4         25      29577    1048576
         4         26      29705    1048576
         4         27      29833    1048576
         4         28      29961    1048576
         4         29      30089    1048576
         4         30      30217    1048576
         4         31      30345    1048576
         4         32      30473    1048576
         4         33      30601    1048576
         4         34      30729    1048576
         4         35      30857    1048576
         4         36      30985    1048576
         4         37      31113    1048576
         4         38      31241    1048576
         4         39      31369    1048576
         4         40      31497     327680

41 rows selected.

Report message to a moderator

enable row movement [message #560894 is a reply to message #559499]

Tue, 17 July 2012 13:21

BeefStu
Messages: 208
Registered: October 2011

Senior Member

Alan,

Thanks foe the post. Do you have any conclusive evidence that this actually helped performance? Ie SQL tracs plans, timimgs....

Report message to a moderator

Re: enable row movement [message #560912 is a reply to message #560894]

Tue, 17 July 2012 14:20

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

In the following example, a lot of rows were deleted from the alan table so the shrink space
reclaimed a lot of empty blocks and caused the select before and the select after to process
22 times faster.
The before select reads 3 rows (and a lot of empty buffers) and the after select reads 3 rows.
The before select reads 4914 physical blocks but the after select only 1 physical block is read.
The before select processes 13570 blocks sequentially and the after select 2 blocks sequentially.
The before select processes 1 block at random and after select zero at random.
The before select cost was 3861 before and only 2 after.
The execution plan before estimates 00:00:47 in execution time and 00:00:01 after.
The query out of v$sqlarea shows that the before ran .041763 seconds of elapsed time on the server
and the after ran .001888112 seconds of elaspsed time on the server (22 times faster).
I do not understand why the buffer_gets in v$sqlarea do not match the consistent_gets from "set autotrace on".

ECSCDAD3 > select count(*) from alan;

  COUNT(*)
----------
         3

Execution Plan
----------------------------------------------------------
Plan hash value: 3481749829

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  3861   (1)| 00:00:47 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ALAN |  2097K|  3861   (1)| 00:00:47 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
      13570  consistent gets
       4914  physical reads

ECSCDAD3 > set autotrace off

ECSCDAD3 > select buffer_gets,executions,sql_id,
  2  elapsed_time/1000000/(executions+.001) sec_per_exec,sql_text
  3  from v$sqlarea
  4  where ( upper(sql_text) like upper('select count(*) from alan') );

BUFFER_GETS EXECUTIONS SQL_ID        SEC_PER_EXEC SQL_TEXT
----------- ---------- ------------- ------------ -------------------------
       3279          1 fbq5awswfgpdv      .041763 select count(*) from alan

ECSCDAD3 > alter table alan enable row movement;

Table altered.

ECSCDAD3 > alter table alan shrink space;

Table altered.

ECSCDAD3 > alter table alan disable row movement;

Table altered.

ECSCDAD3 > set autotrace on

ECSCDAD3 > select count(*) from alan;

  COUNT(*)
----------
         3

Execution Plan
----------------------------------------------------------
Plan hash value: 3481749829

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ALAN |     3 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads

ECSCDAD3 > set autotrace off

ECSCDAD3 > select buffer_gets,executions,sql_id,
  2  elapsed_time/1000000/(executions+.001) sec_per_exec,sql_text
  3  from v$sqlarea
  4  where ( upper(sql_text) like upper('select count(*) from alan') );

BUFFER_GETS EXECUTIONS SQL_ID        SEC_PER_EXEC SQL_TEXT
----------- ---------- ------------- ------------ -------------------------
         14          1 fbq5awswfgpdv   .001888112 select count(*) from alan

Report message to a moderator

Previous Topic:	create/drop user/schema in another Oracle database through Oracle Apex Ver:3.2.1.00.10
Next Topic:	what's the difference between checkpoint_change# and controlfile_change#?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:06:37 CDT 2024