| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Insert too slow...
Sathish
Given that your high wait times relate not to the actual Oracle insert process but to communication with the client, the first place I would look is the connection between Oracle and Informatica. Are you going through ODBC? There are options in SQL*Net and in the ODBC driver. I would also pursue the issue with Informatica to see if they have some tuning advice to offer. For example, how may rows are sent to Oracle each time? The classic is one-row-at-a-time. This can be slow, and a lot of waits like you mention will show up.
As to the "SQL*Net more data from client" wait (your #1 wait), here is what Tim Gorman said a few months ago (Tim, hope these comments apply to this situation):
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, October 23, 2002 11:31 AM
To: Multiple recipients of list ORACLE-L
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
: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 currentrows
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
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
-- 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: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM 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:54:16 CDT
 |
 |