Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Insert too slow...

RE: Insert too slow...

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Wed, 23 Oct 2002 09:02:21 -0800
Message-ID: <F001.004F1419.20021023090221@fatcity.com>


How may indexes are on DM_TRANS_PYMT_HIST? If there are a lot, you might want to drop the indexes, except for the primary key or unique constraints, insert the records, rebuild the dropped indexes, and analyze those indexes.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: sat0789_at_fastmail.fm [SMTP:sat0789_at_fastmail.fm]
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Whittle Jerome Contr NCI
  INET: Jerome.Whittle_at_scott.af.mil

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 - 12:02:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US