2010-04-30

How long will it take?

Reloading a table.


truncate table sometable;

insert /*+append*/ into sometable select ...

commit;


How long will it take?

Table is populated earlier and the space consumed will be about the same after reload.

With following technique I was able to get such an estimate after starting the insert.

Before truncate see how much space there is to be consumed.


select sum(bytes) from user_segments where segment_name = 'SOMETABLE';

4445110272


Check the execution plan of the insert statement. Ensure it will start consuming space at the beginning of execute.

Start the load. Identify the session doing the insert statement and estimate.

select ((targetb-currb)/currb)*interv+currt estimatedfinnish
from (
select currt
-(select sql_exec_start from v$session where sid = :sid) interv
, currb, currt
, :b4445110272 targetb
from (
select systimestamp currt
, sum(bytes) currb
from user_segments
where segment_name like 'SOMETABLE'
));



That is giving some time in the future.

Reasons why the estimate is not correct.

1. The execution plan does not start consuming space at the beginning.
2. The space used before truncate was not the same that it will be at the end of load.
3. Indexes in the table to be populated.

If there are indexes they are builded after insert phase. Estimate was given for the table to be populated. Index building phase may be monitored by looking at V$SESSION_LONGOPS.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.