Re: Performance Monitoring

From: joel garry <joel-garry_at_home.com>
Date: Fri, 05 Oct 2007 14:06:47 -0700
Message-ID: <1191618407.720514.305960@o80g2000hse.googlegroups.com>

On Oct 5, 4:08 am, Jerome Vitalis <vitalismanN0S..._at_gmail.com.invalid> wrote:
> joel garry wrote:
> > On Sep 28, 9:53 am, Jerome Vitalis
> > <vitalismanN05..._at_gmail.com.invalid> wrote:
> >> On Wed, 26 Sep 2007 13:54:08 -0700, joel garry wrote:
>
> >> Well said Joel!
> >> But isn't there a typo in number 2?
>
> > What might that be? (It's hard to be more specific than what I wrote,
> > given parallel and pga and LRU chains and what-all. But if I didn't
> > say something correctly, let us know what you think, it may be
> > enlightening for everyone.
>
> > ...
>
> Shouldn't point 2 read as follows?
> "[...] For example, if some silly SQL reads an entire table to get a
> few rows, you wil likely have a lot of unnecessary I/O that *will* fill
> up the SGA."

No.

For example, I ran this:

SQL> select count(*), orig_document_number from ic_movements   2 group by orig_document_number;

  COUNT(*) ORIG_DOCUM
---------- ----------
  19079104
   5043841

I purposefully picked that field knowing that it is not in an index.

Then, in another session as /, I ran this (this is out of the performance manual, google v$bh jonathan lewis for better information):

COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(1) number_of_blocks   FROM DBA_OBJECTS o, V$BH bh
 WHERE o.object_id = bh.objd
   AND o.owner != 'SYS'
 GROUP BY o.object_name
 ORDER BY count(1);

OBJECT_NAME                              NUMBER_OF_BLOCKS
---------------------------------------- ----------------
[snip]
IC_MOVEMENTS_2                                         22
[snip]
IC_MOVEMENTS_1                                         28
IC_MOVEMENTS                                           41

[snip]

We can guess 24M rows can't fit in 41 8K blocks. The explain plan also shows a full table scan (can't paste it in since I'm viewing it in OEM).

The reason this doesn't fill up the SGA is a full table scan reads the blocks onto the LRU end of the buffer cache in chunks of block_size * db_file_multiblock_read_count, IIRC. The general idea is you won't be rereading those blocks soon, no reason to keep them around. If you _are_ re-reading them (and it isn't a small table), you obviously have a problem. Less obvious is bad programming doing things like too many commits, forcing too many re-reads. That sort of thing will load up the SGA, because it is doing too many reads and _not_ doing full table scans. That's also the sort of thing where people start fiddling with magic init parameters or throw hardware at a problem when they shouldn't.

See http://www.evdbt.com/SearchIntelligenceCBO.doc for a nice explanation of the reads.

Parallel and direct load operations will bypass the SGA.

jg

--
@home.com is bogus.
"..and meanwhile, as if it wasn't already obvious enough, can I please
remind would-be members here that anything even vaguely resembling a
freebie, anonymous email account will render your application rejected
in an instant. That also covers 'vanity domains': home-brew websites
with home-brew email addresses. If you abuse your membership
privileges here, I want an ISP and/or an employer I can complain to,
not yourself in your self-appointed "web administrator" role!" - some
dork of an admin of a vanity domain who highly overestimates the
effect his complaints will have.  I find this very humorous since I
discovered hotmail and sbcglobal reject my ISP's domain, but not my
own, coming through my ISP.

Received on Fri Oct 05 2007 - 16:06:47 CDT