Oracle-L: Insert too slow...

From: <sat0789_at_fastmail.fm>
Date: Wed, 23 Oct 2002 08:30:53 -0800
Message-ID: <F001.004F1286.20021023083053@fatcity.com>

Hello All,
  We are running oracle 9.2 on a sun box in a DW env.   Data loads are thorugh informatica.
  During one such load of fact table, the load time per sec is about 300   rows per sec.
  The table is suppose to load about 5 mil rows.   During the load i happen to do
  sys.dbms_system.set_ev(66,312,10046,8,'');   After 20 min.. i ran
  sys.dbms_system.set_ev(66,312,10046,0,'');

Then i did a tkprof on the trace file with wait events set to Yes. The result is pasted below............The top events are

 SQL*Net more data from client
 SQL*Net message to client   
 SQL*Net message from client

This load varies with number of rows loaded per sec jumping to 600 about 4 days back.
Any ideas would be appriciated.

Thanks,

Sathish.

INSERT INTO
DM_TRANS_PYMT_HIST(PERIOD_KEY,MORTGAGE_LOAN_KEY,TRANSACTION_KEY, GEOGRAPHY_KEY,ORIGINATION_SOURCE_KEY,TRANCHE_KEY,LOAN_TYPE_KEY,INVESTOR_KEY,

  TRANSACTION_TYPE_KEY,BANKRUPTCY_KEY,FORECLOSURE_KEY,CUR_CREDIT_SCORE_KEY,
  ORG_CREDIT_SCORE_KEY,CUR_LTV_KEY,LOAN_CLASSIFICATION_KEY,
  PRODUCT_GROUPING_KEY,PRODUCT_KEY,CODES_TRANS_KEY,A_H_PD_AMT,BATCH_NBR,
  BSC_PD_AMT,CORP_ADV_PAYEE_CD,CORP_ADV_REP_AMT,CR_LIFE_AMT,
  CUR_MTH_UNAPPLIED_FACTOR_AMT,CUR_MTH_BUYDOWN_FACTOR_AMT,
  CUR_MTH_ESCROW_FACTOR_AMT,CUR_MTH_INTEREST_FLOAT_DAYS,
  CUR_MTH_PI_ADV_TRANS_AMT,CUR_MTH_PI_FLOAT_INCOME_AMT,
  CUR_MTH_PRINCIPAL_FLOAT_DAYS,DISB_CHECK_NBR,DUE_DT,FHA_PENALTY_AMT,

ESC_PD_AMT,GROSS_SERVICE_FEE_AMT,HUD_FEE_235_AMT,HUD_PART_AMT,LOAN_NBR, INT_LOST_ON_PAYOFF_TRANS_AMT,INT_LOST_ON_CURTAIL_AMT,INT_PAID_AMT, INTEREST_REMIT_AMT,INTEREST_REMIT_DUE_DT,L_C_PD_AMT,L_C_REASON_CD,

  LIFE_PD_AMT,MISC_PD_AMT,NEXT_MTH_INTEREST_FLOAT_DAY_CT,
  NEXT_MTH_ESCROW_FACTOR_AMT,NEXT_MTH_PI_FLOAT_INCOME_AMT,
  NEXT_MTH_PRIN_FLOAT_DAY_CT,NON_REC_CORP_ADV_ADJ_AMT,
  OTHER_MTH_INT_FLOAT_DAY_CT,OTHER_MTH_PI_FLOAT_INCOME_AMT,

OTHER_MTH_PRIN_FLOAT_DAY_CT,ORIGINATION_FEE_AMT,PRINCIPAL_PAID_AMT, PRINCIPAL_REMIT_AMT,PRINCIPAL_REMIT_DUE_DT,PREPAY_FEE_COLLECTED_AMT, REC_CORP_ADV_ADJ_AMT,REPLACEMENT_RESERVE_AMT,RESTRICTED_ESCROW_PAID_AMT, SERVICE_FEES_COLLECTED_AMT,SCND_MTH_INT_FLOAT_DAY_CT, SCND_MTH_PI_FLOAT_INCOME_AMT,SCND_MTH_PRIN_FLOAT_DAY_CT,SUSPENSE_PAID_AMT, TXN_DT,TOTAL_INTEREST_FLOAT_DAY_CT,TOTAL_PI_FLOAT_INCOME_AMT, TOTAL_PRIN_FLOAT_DAY_CT,TOTAL_RECD_AMT,REMIT_TYPE_CD) VALUES
( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17,
:18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32,
:33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46,
:47,
:48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61,
:62,
:63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0     
     0
Execute   3230     42.29      41.48          0       8234    1695407     
239020
Fetch        0      0.00       0.00          0          0          0     
     0

------- ------ -------- ---------- ---------- ---------- ----------

total 3230 42.29 41.48 0 8234 1695407 239020

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 46 (NEVADMIN)

Rows Execution Plan

-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
SQL*Net more data from client 29045 0.00 1.07 SQL*Net message to client 3229 0.00 0.01 SQL*Net message from client 3229 9.23 935.00 latch free 2 0.01 0.03 log file switch completion 2 0.01 0.03

-- 
http://fastmail.fm - A no graphics, no pop-ups email service
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: sat0789_at_fastmail.fm

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Oct 23 2002 - 11:30:53 CDT