Notes in-line.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Sal L" <member46676_at_dbforums.com> wrote in message
news:3550820.1067801502_at_dbforums.com...
>
> select count(*) from t1;
> count(*)
> ----------
> 811040
>
> create index t1_idx on t1(object_name) nologging;
> alter session set events '10046 trace name context forever, level
8';
> select count(*) from t1 where object_name between 'B' and 'T';
>
> Fetch 2 0.27 0.54 375 376 0
> 1 SORT AGGREGATE (cr=376 r=375 w=0 time=548486 us)
> 94016 INDEX RANGE SCAN OBJ#(48017) (cr=376 r=375 w=0 time=444504
>
>
> You can see there is no multi-block read, thus 1 ms timing can be
> explained by using of file system cache. But what is wrong with
Symmetrix cache?
>
I've just repeated the test on my own laptop using 9.2, and
the most informative part of the trace is in the raw form. As
the index range scan occurs, I get one sequential read that
takes 18,000 microseconds (18 ms) followed by 7 sequential
reads that take about 200 microseconds each. This SUGGESTS
a hardware read-ahead is reading a number of consecutive blocks
into memory, and Oracle is benefting 'by accident'.
Compared to the original:
a) 811,000 rows is small compared to the 30M I think were in
the original test
b) The test was a newly created index, which means a range
scan would be along a set of adjacent Oracle blocks, which
would probably be adjacent disk blocks quite often
c) The original test visited the table - and it is reasonably
likely
that the physical reads were table reads, not index range
reads,
and therefore were likely to be much more randomly scattered
than the index blocks.
As a general rule, a large cache on something like the symmetrix
can be of significant benefit for bursts of intensive disk writes, but
pretty useless for extremely random reads.
> And could you comment the statement regarding tkprof disk
> statistics taken from Knowledge Xpert for Oracle Administration:
>
>
> "disk Number of data blocks read from disk for each parse, fetch,
> or execute. If a single multiblock read returns eight blocks,
> this figure is incremented only once for the physical read."
This one's easy - the statement is incorrect.
Received on Sun Nov 02 2003 - 15:36:24 CST
