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