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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:
> <ctcgag_at_hotmail.com> wrote in message news:20030120195527.382> Actually,
> it appears it would be for AGRINDEX and AGRINDEX4, as this whole
> > threadlet has been on the topic of write contention, not read
> > contention.
> >
>
> Nope.

I joined this discussion when you said "As was done to death here a while back, it's simply not true. When a table is updated, the index maintenance activities are *serialized*, so that they take place after the table update," and that is what this subthread has been about. While I have seen separation of indices and tables done to death several month ago, including bits in the inherently serial access to tables and indices, I have seen nothing on the inherently serial writing of data and indices, which is what my questions revolve around.

> This thread has been about a simple question, which you won't
> answer. Why should tables and indexes have some kind of special
> relationship regarding read OR write contention?

Because blocks from any random pair of segments are dirtied together only by accident, while blocks from a table and index are dirtied together by design. p(accident) < p(design).

> Tell me. Explain to me the mechanism.

I have. Over and over again.

> Show me how that which is
> inherently serial can suddenly engender especial amounts of parallelized
> contention.

This is what you have refused to answer. I accept that the access of index and data is inherently serial from within any one query. I do not yet accept that the writing of blocks from index an data is inherently serial. Why is the writing of blocks from data and index inherently serial?

> You admitted DBWR doesn't give a damn what sort of buffer it flushes. So
> how the hell do index buffers and table buffers suddenly start to contend
> on the write?

Because they are there.

> I'll shut up if you explain one plausible mechanism to explain what you
> are pushing as a read/write anomaly: reads don't contend. But writes
> suddenly do.

The DBWR has to write what is in the dirty list and/or found at the LRU.

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?

A second later, someone hits "Submit" on 80 samples they set up for the NMR facility. 300 nmrfac_todo blocks and 240 nmrfac_todo_idx_* blocks are dirtied, and next to each other in the cache list with maybe a few from requisition thrown in.

Now a memory hungry process comes along, runs through whatever else we had at LRU and knocks up 700 blocks of the dirty bloack to the dirty list. These are almost all going to seqfac_todo and seqfac_todo_idx_*, and so DBWR has to put them there. If that's on the same spindle, I don't see how you avoid the contention. A few seconds later, another memory hungry process knocks up more blocks to the dirty list, and these are mostly going to nmrfac_todo and nmrfac_todo_idx_*.

> ...Because you have a database to manage, and you don't
> want to admit that maybe, just maybe, you've been doing it wrong all
> these years. Me- I'm "only" theory. So what do I know?? Nothing really.

Wow. I am amazed at how wrong you are here. It makes me wonder if you actually read any of my posts you have been responding to. I don't have a database to manage, except a lap-top demo version. I haven't been doing it wrong, because I haven't been doing it all. The storage for the real databases are behind the IT curtain, I wouldn't know what spindle or how many spindles any thing is on, and even the DBA of record wouldn't know this. I'm almost only theory, and new to this, and as I have said I my interest in how to tune a database that isn't SAME is only pedagogical. I say *almost* only theory because every now and then I run into a problem, take a theoretical look at it, recommend a change, and--if I can convince the DBA with years of experience to listen to me--usually solve the problem.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB