Re: Locally Managed Tablespaces ... again!!!

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:hTrX9.30135

[snip]

> > I submit 960 samples to the DNA sequencing facility, so I supply the
> > application with all the necessary data, and I hit the "submit" button.
> > So 300 seqfac_todo blocks get dirtied, and 500 seq_fac_todo_idx_* blocks
> > get dirtied, and these are next to each other in the cache list with
maybe
> > a few blocks from HR thrown in. No?
>
> No. I don't know what 'next to each other in the cache list' means, but it
> is not intrinsically so in any case. You mean the LRU? So what? You've
800
> buffers that need to be flushed. Either DBWR and your hardware copes, or
it
> doesn't. I could argue that my application updates 300 customer records
and
> 500 sales records for those customers. I've got 800 buffers too. Either my
> DBWR copes, or it doesn't.

I wanted to add this last night, but got distracted by the arrival of my sausages for dinner. So here goes then:

You said that blocks from a table and its index would end up "next to each other in the cache list", and I'm assuming that you mean the LRU list. And if that were true, then presumably they would get flushed out together by DBWR and that might cause contention. I presume that's the point you were making anyway. If not, my apologies for putting words in your mouth.

Now to start with, as I said in the last post: so what? Two table buffers might be next to each other on the LRU list, too, and if that causes contention when DBWR flushes them, then you've a contention problem, and not an index-in-same-tablespace-as-table problem.

But (and here's where the sausages intervened), it quite likely isn't true in any case that the index buffer will be next to the table buffer.

Consider the following:

SQL> create table lotsofrows tablespace users as select * from dba_objects; Table created.

SQL> create index lor_idx on lotsofrows (object_id) tablespace users; Index created.

SQL> analyze table lotsofrows compute statistics; Table analyzed.

SQL> analyze index lor_idx validate structure; Index analyzed.

SQL> select blocks, num_rows from dba_tables   2 where table_name='LOTSOFROWS';

    BLOCKS NUM_ROWS
---------- ----------

       852 29369

SQL> select blocks, lf_rows from index_stats;

    BLOCKS LF_ROWS
---------- ----------

       144 29369

Now that means, on average, there are a mere 34.47 rows per block in the table. But 203.95 index entries per block for the index.

That must mean, on average, that an index leaf node is 5.91 times more likely to be 'hit' by a query or a transaction than a table block (203.95/34.47=5.91).

So whilst an index leaf node is read into the buffer cache, and then very shortly after the corresponding table block is read into the buffer cache, you might very well presume that both buffers are next to each other on the LRU list. And indeed they might be for a moment or three. But if people are querying/modifying table rows, then in no time at all, the index buffer becomes quite popular, and moves up to the hot end of the LRU list, whilst the poorly-populated table block is 6 times as likely to be passed over by subsequent query and update activity... so it will tend to move to the LRU end of the LRU list.

So what does DBWR end up flushing? The table buffer, not the index buffer.

Stick compression into the equation, and an index buffer is even more likely to become 'hot', and drift off to one end of the LRU list, leaving the table buffer behind.

Now, I know these are averages, and broad brush statements.. but the principle is sound. A table buffer is probably less likely to be flushed at the same time as its index buffer than a child table buffer and its parent table buffer.

So there really isn't any intrinsic write contention just because you've a table and its index compared with any other two randomly selected segments.

Just thought I'd mention it.

And the sausages were rather fine, too.

Regards
HJR Received on Wed Jan 22 2003 - 23:59:29 CST