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):
--- Tim Gorman <[EMAIL PROTECTED]> wrote:
> This is never an "idle" event. The phrase "more data from client"
indicates that the individual SQL operation is larger than a single SQL*Net
packet. No big deal; it happens all the time, and SQL*Net handles it with
"continuation packets". Only issue is that the client is taking a lot of
time between each packet sent. Jack's conclusion that the client process
(in the "client-server" database connection) is not providing data in a
"timely fashion" is exactly correct. You most likely have a slow client
process...
>
> Oracle documentation frequently tries to encourage mucking about with
SDU/TDU parameters in SQL*Net configuration files, but I've rarely seen this
be more effective than tuning the client process... :-)
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-----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
( :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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
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).
