Re: UNTO TBS behavior in 9i
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Thu, 25 Jan 2007 13:19:43 -0600
Thanks again to all.
As you all mentioned I used the direct path with nologging which cut down my
logging and the UNTO TBS usage. I was also able to speed up the process very
well to a level where they are happy. I got pulled into applying DST patches
now.
When I find some time I will test this thing on my own without
NOAPPEND hint. I will check the v$transaction, also look for trigger
updates, statspack, auditing etc. and post the results.
Meanwhile here is the INSERT statement *before* the append hint, nothing
big:
__________________________________________________________________________________________
INSERT INTO REG_PRI_C2
select /*+PARALLEL(REG_PRI_OC, 4) */ a.process_date, a.record_no,
a.change_code, a.exec_firm_no, a.option_type, a.account_no, a.entry_account,
a.commd_code, a.exec_brker, a.trdr_id, a.trans_group, a.rate_group,
a.trans_code, a.sub_trans_cd, a.givup_code, a.spad_ind, a.rate_category,
a.delegate, a.surcharge_rate, a.contract_month, a.contract_year, a.day_ind,
a.exercise_price, a.fee_rate, a.frmtd_price, a.trade_date, a.trade_price,
a.trade_quantity, a.trade_type, a.waiver_code, a.trn_brkr_elig, a.brkr_elig,
0, 0, ' ', 0, 'N', a.mis_id, a.origin, ' ', ' ', a.RCD_ENTERED_TM,
a.te_source, a.ts_exc_tm_src, 'N', 'CXX4AB01', sysdate, a.CARD_ORDER,
a.RECORD_ID, a.COMPLEX_CD, a.SUB_COMPLEX_CD, a.BUSINESS_CODE,
a.MTHLY_RATE_CAT, a.MTHLY_FEE_RATE, a.EXECUTOR from REG_PRI_OC a
where a.process_date BETWEEN to_date('11/01/2006','MM/DD/YYYY')
AND to_date('11/31/2006','MM/DD/YYYY')
and a.record_status = 'X'
__________________________________________________________________________________________
Some extras: It inserts 14 million rows. Selects from one partitioned table
and inserts to another partitioned table. Data comes from one partition only
and goes into one partition only. There are local indexes on the partitions.
Daniel: "For example, for an insert the undo vector is *basically* just the
row address and a delete indicator." This means the UNDO generated should be
lot less than the actual data being inserted!
On 1/24/07, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Presuming you have enough time to test it, what happens when you do only
the select without attempting the insert?
Possibly you could shoot us the statement in question so it could be
examined for possibly side-effect problems.
-----Original Message-----
*From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx
]*On Behalf Of *Ram Raman
*Sent:* Wednesday, January 24, 2007 12:20 PM
*To:* Hemant K Chitale
*Cc:* oracle-l
*Subject:* Re: UNTO TBS behavior in 9i
Thanks for your replies.
1. We are not doing multiple commits. Just one INSERT INTO SELECT
statement with one commit at the end. There were no other transactions when
we started this one.
2. The space for both data and index is about 5.5Gb.
Last time we ran the process, the 30GB UNDO TBS was close to 100% free
when we started the process and the UNDO TBS became 100% full and then erred
out because of lack of space. Why would the undo TBS grow out to be almost
30GB causing the process to fail esp. when no other process is running in
the database. The only other active processes are Oracle background
processes. Oracle is 9206.
We do have the option of using Direct path insert and turning the logging
off, but this behavior is perplexing.
On 1/23/07, *Hemant K Chitale* < hkchital@xxxxxxxxxxxxxx> wrote:
1. If you are doing multiple commits during the insert run, each
committed batch remains in UNDO for the duration of 2 hours. You should
reduce UNDO_RETENTION.
2. UNDO is maintained for both Table Rows and Index updates -- eg
when you INSERT,
you are adding entries to indexes on the table as well. So UNDO is
maintained for that
as well.
3. You can monitor the size of your transaction by querying
V$TRANSACTION -- that
would show the number of undo "records" (table+index entries) and
undo blocks used
between every commit.
4. You could avoid undo and redo both -- eg
alter table target_table nologging ;
insert /*+ APPEND */ into target_table ....
<finally> commit ; alter table target_table logging;
At 02:19 PM Tuesday, Ram Raman wrote:
>Hi all,
>
>We are running a batch process to insert data from one table to
>another. The data inserted is over than 10 million rows. We had
>about 20GB of UNDO tablespace. UNDO_RETENTION is 7200. When we start
>the insert process no other process runs and all the time during the
>insert process nothing else runs. We insert about 5.5 Gb of data
>including indexes.
>
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantoracledba.blogspot.com
Other related posts:
