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" <[EMAIL PROTECTED]> 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. > [EMAIL PROTECTED] > 618-622-4145 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > > > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
