Donald K. Burleson Oracle Tips

Monitoring import speed

One of the confounding issues for an Oracle administrator is tracking the execution of an Oracle import.  When a production system is down, the IT managers are anxious to know the progress that is being made by the Oracle import utility.  For very large tables, the Oracle import utility can take many hours, and the DBA needs to know the rate at which rows are being added into the table.

If you need to monitor how fast rows are imported from a running import job, try one of the following methods:

select

   substr(sql_text,instr(sql_text,'INTO "'),30) table_name,

   rows_processed,

   round((sysdate-

    to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,

   trunc(rows_processed/

    ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute

from  

   sys.v_$sqlarea

where 

   sql_text like 'INSERT %INTO "%'

and 

   command_type = 2

and 

   open_versions > 0;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm