Database Reference
In-Depth Information
the first query was executed with the ARRAYSIZE of 15, and the ( cr=nnnn ) values in the Row Source Operation
shows we performed 1,374 logical I/Os against the index and then 1,625 logical I/Os against the table (2,899-
1,374; the numbers are cumulative in the Row Source Operation steps). When we increased the ARRAYSIZE to
100 from 15 (via the set arraYsIZe 100 command), the amount of logical I/O against the index dropped to 245,
which was the direct result of not having to reread the index leaf blocks from the buffer cache every 15 rows,
but only every 100 rows. to understand this, assume that we were able to store 200 rows per leaf block. as we
are scanning through the index reading 15 rows at a time, we would have to retrieve the first leaf block 14 times
to get all 200 entries off it. On the other hand, when we array fetch 100 rows at a time, we need to retrieve this
same leaf block only two times from the buffer cache to exhaust all of its entries.
the same thing happened in this case with the table blocks. since the table was sorted in the same order as the index
keys, we would tend to retrieve each table block less often, as we would get more of the rows from it with each fetch call.
so, if this was good for the COLOCATED table, it must have been just as good for the DISORGANIZED table, right?
not so. the results from the DISORGANIZED table would look like this:
select /*+ index( a15 disorganized_pk ) */ *
from disorganized a15 where x between 20000 and 40000
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID BATCHED DISORGANIZED
(cr=21365 pr=0 ...
20001 20001 20001 INDEX RANGE SCAN DISORGANIZED_PK
(cr=1374 pr=0...
select /*+ index( a100 disorganized_pk ) */ *
from disorganized a100 where x between 20000 and 40000
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID BATCHED DISORGANIZED
(cr=20236 pr=0 ...
20001 20001 20001 INDEX RANGE SCAN DISORGANIZED_PK
(cr=245 pr=0...
the results against the index here were identical, which makes sense, as the data stored in the index is just the
same regardless of how the table is organized. the logical I/O went from 1,374 for a single execution of this query
to 245, just as before. But overall the amount of logical I/O performed by this query did not differ significantly:
21,365versus 20,236. the reason? the amount of logical I/O performed against the table did not differ at all—if
you subtract the logical I/O against the index from the total logical I/O performed by each query, you'll find that
both queries did 19,991 logical I/Os against the table. this is because every time we wanted n rows from the
database—the odds that any two of those rows would be on the same block was very small—there was no
opportunity to get multiple rows from a table block in a single call.
every professional programming language I have seen that can interact with Oracle implements this concept of
array fetching. In pL/sQL you may use BULK COLLECT or rely on the implicit array fetch of 100 that is performed
for implicit cursor for loops. In Java/JdBC, there is a prefetch method on a connect or statement object. Oracle
Call Interface (OCI; a C apI) allows you to programmatically set the prefetch size, as does pro*C. as you can see,
this can have a material and measurable effect on the amount of logical I/O performed by your query, and it
deserves your attention.
 
Search WWH ::




Custom Search