Monitor Import Speed

출처 :  http://www.idevelopment.info/



by Don Burleson <www.dba-oracle.com>

One of the more perplexing issues for an Oracle administrator is tracking the execution of an Oracle import. When a production system is down, IT managers are anxious to know the progress of 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 the utility is adding rows to the table. To monitor how fast rows are imported from a running import job, try the following method. 


    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;


Don Burleson, one of the world's top Oracle database experts, has written 12 books, has published more than 70 articles in national magazines, and serves as editor in chief of Oracle Internals, a leading Oracle database journal. As a leading corporate database consultant, Don has worked with numerous Fortune 500 corporations creating robust database architectop of IT trendstures for mission-critical systems