Re: Performance issue while loading large amount of data

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 16 Jan 2003 13:07:29 -0000
Message-ID: <3e26ae91$0$240$ed9e5944@reading.news.pipex.net>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:LUwV9.25533$jM5.67666_at_newsfeeds.bigpond.com...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3e257155$0$235$ed9e5944_at_reading.news.pipex.net...
>
> < cutty roo>

smirk

> > alter system dump datafile 2 block 398 (which I read as being the undo
> block
> > referred to above) I get a whole set of undo records *none* of which
refer
> > to objn 30399. Presumably I'm doing something daft here?
> >
>
> Not sure ? The undo typically has some dd stuff ...
>

Ok So Maybe I was being a bit daft.

As before

SQL> insert /*+ append */ into insert_demo select * from dba_objects;

29524 rows created.

SQL> @show_transaction_stats

START_UBAFIL UBAFIL START_UBABLK UBABLK USED_UBLK USED_UREC ------------ ---------- ------------ ---------- ---------- ----------

           2 0 937 0 1 1

OK so this time its file 2 block 937. Dump it and scan through more carefully. My comments with ####

*-----------------------------

* Rec #0x34 slt: 0x19 objn: 1(0x00000001) objd: 30407 tblspc: 9(0x00000009)

 #### obj# 1 that's curious I don't have one * Layer: 14 (Transaction Extent) opc: 5 rci 0x00  #### Ah ha an extent map type thing. Makes sense since I just truncated the table prior to doing all this work.

Undo type: Regular undo Last buffer split: No Temp Object: No
Tablespace Undo: Yes
rdba: 0x00000000

*-----------------------------
kteopu undo - undo operation on extent map
       segdba: 0x240004b  class: 4  mapdba:0x240004b  offset: 3

 rbr extent - dba: 0x0 nbk: 0x0
kteop redo - redo operation on extent map

   ADD: dba:0x24000c1 len:8 at offset:15    DEFAULT: ???
   SETSTAT: exts:16 blks:128 lastmap:0x0 mapcnt:0

 #### both the undo and redo data appear to be recorded in the same place, that really does look odd.

*-----------------------------

* Rec #0x35 slt: 0x19 objn: 60(0x0000003c) objd: 10 tblspc: 0(0x00000000)
* Layer: 11 (Row) opc: 1 rci 0x34 Undo type: Regular undo Last buffer split: No Temp Object: No
Tablespace Undo: No
rdba: 0x00000000

*-----------------------------

KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0001.01c.0000321b uba: 0x008002b0.01d0.1f

                      flg: C---    lkc:  0     scn: 0x0000.00a91a2a

KDO Op code: URP row dependencies Disabled   xtype: XA bdba: 0x0040005d hdba: 0x00400059

itli: 2  ispac: 0  maxfr: 4863
tabn: 2 slot: 0(0x0) flag: 0x6c lock: 0 ckix: 0
ncol: 7 nnew: 6 size: 0
col  1: [ 1]  80
col  2: [ 3]  c2 02 5d
col  3: [ 3]  3e 64 66
col  4: [ 1]  80

col 5: [ 1] 80
col 6: [ 1] 80

 #### what is this?
  1 select object_name from dba_objects   2* where object_id=60
SQL> / OBJECT_NAME

---

TSQ$ SQL> ah of course tablespace quotas, everyone always forgets about them when dealing with LMT's, here they are.

So in summary I appear to have the physical blocks being allocated and updates to the DD -specifically TSQ$.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Jan 16 2003 - 07:07:29 CST