Re: Separating data, index objects

DA Morgan wrote:
> Joel Garry wrote:
> >>Actually there is no such thing as "all blocks contiguous." As amazing
> >>as it may be many operating systems won't lay down a single file on a
> >>clean drive that way.
> >
> >
> > I think this is a red herring, as whatever it is, it appears to Oracle
> > as contiguous, and if Oracle has to read more stuff, it makes no
> > difference.
>
> If RAW I would agree with you. But otherwise Oracle isn't doing the
> reading.
>
> >>We could talk about RAW but that is quite a different matter. And even
> >>on your single user database ... there will be a substantial amount of
> >>recursive SQL. In Oracle you are never alone.
> >
> > And heck, why not talk about raw? And why is there recursive SQL on a
> > single table scan?
>
> Didn't say there was. But Oracle will be running it in the background
> and issuing reads and writes that may very well get in the middle of
> what you, the end user, perceive to be a single read request.
>
> You go to Oracle and say SELECT col1, col6 FROM t;
>
> And whats the first thing that happens?
>
> select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
> sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
> spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2;
>
> And it has two indexes that I can guarantee you are in the SYSTEM
> tablespace.
>
> In Oracle you are never alone. Even on a single user system.

Well, if it is the first thing that happens, that will happen before the time of interest. Unless you are saying it does this at random times in the middle of a full table scan, which I would highly doubt. I have no doubt it would happen regardless even with a full scan hint, since hints are just suggestions. But again, that should happen at the beginning in all test cases, so is a red herring.

Remember, I'm talking about the odd but non-trivial case of something running by itself in the middle of the night on a system that is normally OLTP/shared. Say, a sum of a column in a table with many rows. Any Oracle housekeeping would be that of a pretty bored system, and should even out over several test runs of the two situations. I'm not going to worry about 10G trying to gather information to retune the instance because of this, yet.

jg

-- 
@home.com is bogus.
http://www.goodsnails.com/