Re: Performance issue while loading large amount of data

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:EzZU9.24111$jM5.64133_at_newsfeeds.bigpond.com and I quote:

>
> What's the before image of any DML for? So that you can re-construct
> what the block used to look like. If you insert one row into a table,
> chances are it will slot into a block which already has committed rows.

Not quite. That's only if you have PCTUSED set way too high. On a large number of inserts, the vast majority of your rows will go into blank "brand new" blocks. Only the first few will go to a block with rows already in it (the prior "last block"). The example you provided with pctfree 99 pctused 0 was perfect.

To rollback new blocks, all Oracle has to do is restore the header and the row directory of them blocks as they were. Only for the very first block at the prior "end" of the table may this be an issue, and then only if we take PCTUSED into account AND there has been space compaction in the block.

> By storing the rowid in rollback, you allow Oracle to determine which of
> the many possible rows it is that is to be reversed out when you say
> 'rollback'.

All it has to do is restore the original row dir in the header. Admittedly, if the block has been "defragged" as a result of the INSERT, there will be some more work. But that is entering another big variable here, the PCTUSED.

Let's not go there: in the examples I was citing and the original question, it was a vanilla case of dumping a few million rows after the current "end" of a table. The vast majority of those will go to "brand new" blocks.

>
> But that won't work. When you have to prepare, at 10.06, a
> read-consistent image of a block that's been inserted into, it's not
> sufficient to roll the entire block to the way it was at 10.00am. You've
> got to roll some rows back, and not others. It must be at the row level.

Let's look into that in more detail. Reconstruct a block for a read-consistent query can work two ways.

One: the query is using an index. If insert hasn't committed yet, there is no way new rowids will be found in the index by the query. Table blocks don't need to be re-built, the index will never take you there. If your insert has committed, they still won't be found (long running query read-consistent case, the one that generates 1555 errors on occasion...). Only the index needs "reconstruction", not the table.

Two: the query is running a full scan. OK, just re-build the header row directory. That's all. You won't find the new rows in the original header.

This is only needed for the "last" block of the table at the time the insert started. All other new blocks inserted after simply won't be visible.

Of course, I'm not for a moment suggesting that Oracle goes and determine if a long running query is using an index or not in order to make decisions about block reconstruction. It merely requests to read an index, or to read a block in a table. Reconstruction happens wherever needed. The description above was just to provide context.

>
> Go for it.
>

Will do. I think blocks won't have their data area cleaned on an insert that gets rolled back. Just the headers re-set. I'll have to figure out how to prove this. Fascinating stuff!
I wish I really had more time to play with this. Still haven't finished another thing I promised to get back to Richard before Xmas: just not enough time. Having heaps of fun getting VPDs to work for the ADF at the moment. Some trickery required.  

>
> Now 39/453 = 8.6%. So clearly the block header business has a role to
> play, but it's not proportional.

Hmmm, quite near isn't it? But as you note, not exact. Done a few tests as well with different block size databases and it's definitely not quite there either way. Except for 4K block size where I got very near to a correct number. Within 1 block.

Is there an easy way of dumping the contents of an in-use undo block? Short of dumping the whole tablespace and pouring to find the one in use, I mean? Is there some v$ where I can find the DBA of the undo blocks in use?

> So it can't be just down to block headers.

Yup. There will be other overhead of the undo mechanism itself. Their own little pointers and whatever else is needed, I suppose. That may indeed skew any numbers we get. But with 4K block size it was awfully close. Pre-calculated 6 undo blocks based on headers and got 5. As close as I could get. And it kept close with a larger table.

>
> Don't forget that all data dictionary stuff gets put into the SYSTEM
> rollback segment, so it's irrelevant for the purposes of this
> discussion.
>

A very good point! Thanks.

>
> "An insert will generate the least amount of undo since all Oracle needs
> to record for this is row ID to 'delete'".
>
> Just thought I'd mention it.

Yeah, I know. I'm not 100% sure of that though. I can't tally rowids against the numbers I get. With block headers I can. Closer anyway than with rowids. That worries me.

Tom, if you're following this can you please chip-in with how to check these things? Or where to look? Not doubting what you say, just want to verify meself.

>
> Time to start block dumping, I think.
>

Hmmmm, bugger! Any easy way to find and dump in-use undos? I really don't feel like going through heaps and heaps of blocks to find just the one: Steve Adams would find that riveting, but me I'd fall asleep... :D

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam