| 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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
