Re: An oldie on Blocksize and Performance?

>The correction:
>DBWn writing does NOT result in 'buffer busy waits',
>it results in 'write complete waits'. I seem to make this
>mistake every time someone asks me, and then
>remember the write (no right) answer 2 days later.

|| ok, the buffer busy waits are when another proc is making

    changes, so the block is in XCUR mode....

>
>Expansion
>I believe the 'grab the latch' takes place as the process
>starts to hunt down the chain to see if the block is there.
>The block pin happens if the block is there but a CR copy
>needs to be made, and then another latch grab probably takes
>place to attach the copied block to the chain.

|| this would happen more often with OLTP in a 16k blocksize, yes......

>
>If you really mean 'cache buffers chains', this has nothing to
>do with table scans.

|| you mean full table scans? Or table scans,say, driven from index

    range scans as well? I am not sure why these latter in particular would     not have to acquire a CBC ( of which there appear to be thousands in my     instance, incidentally...)

However, if you have code which does a large tablescan in parallel,

|| no PQO...although process A may scan the table while process B attempts

    to write it, certainly...

and for each row does an indexed
>access into a reference table, then the root block of that index
>will be hit extremely frequently by all the PQ slaves - and there
>will be a lot of contention for that latch (and also possibly for
>a few other branch blocks in the index).

|| most of my wait events other than this latch beast are for db_file_sequential/scattered_read

    which leads me to believe i am piling up on tables rather than indexes. Day four or so into

    looking at this thing, i find that the previous contestants have placed hot tables in disk

    contention, tables in disk contention with indexes, sort space in contention with indexes,

    and most other permutations. A case study in how not to lay out disks and objects, almost.

>
>Select addr, gets, misses, spin_gets from v$latch_children
>where name = 'cache buffers chains'
>and gets > 500000 -- or suitably large number
>
>to see if you have just a couple of chains (hence blocks)
>causing the problem, then

|| There are maybe half a dozen or so in this category. Got the tables for 'em. Why

    does "a chain" correspond to "a block" in your reasoning? You know implicitly

    point to something interesting of which I am unaware....

>
> select dbarfil, dbablk
> from x$bh where hladdr = {an addr from above}
>
>to identity which blocks are causing the problem.

|| Did this. Two or three tables and two or threee indexes. See contention

    comment above......unfortunately, I am losing this nifty test system in a

    day or two....this could have been fun....
>
>
>P.S. Don't need to air-mail burger and beer, my local pizza place takes
order by e-mail.

|| Addresses, please! Don't forget to include source and target address, and toppings, if any.
>
>
>Jonathan Lewis
>Yet another Oracle-related web site: www.jlcomp.demon.co.uk
Received on Tue May 11 1999 - 22:17:32 CDT