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).
