Monitor import speed or insert speed
                                                 Last update (2010-07-27 12:35:26)
                                                                                                                   Date added (2008-03-02 00:09:21)

Summary
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 "PRESTAGE_DW"%'
AND command_type = 2
AND open_versions > 0;

TABLE_NAME                     ROWS_PROCESSED    MINUTES ROWS_PER_MINUTE
------------------------------ -------------- ---------- ---------------
INTO "PRESTAGE_DW"."COSTEDEVEN         395703       13.4           29493
INTO "PRESTAGE_DW"."PROCESSINS         124291       20.3            6112

Elapsed: 00:00:00.93
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search