RE: Insert too slow...

Except for primay key constraint we dont have any other constraints or indexes present during the load.
What does the sql * net wait events signify. Is there cause for concern when we see those events. ??

Thanks,
Sathish.

On Wed, 23 Oct 2002 09:02:21 -0800, "Whittle Jerome Contr NCI" <Jerome.Whittle_at_scott.af.mil> said:
> 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).

-- 
http://fastmail.fm - Access your email from home and the web
-- 
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).