Tip:
Highlight text to annotate it
X
>>John: When is a full table scan direct huge changes through the releases? And if it direct,
will it be direct or indirect? That decision is sometimes good, sometimes bad. You need
to consider your session. You need to consider other sessions.
[pause]
In summary, serial full table scans were always indirect before 11.2, parallel scans always
direct before 11.2. These are the two critical parameters.
That hidden parameter _serial_direct_read, remember, defaults to auto so your application
may start behaving very differently as you upgrade from 11.2.01 or 11.2.02.
Parallel_degree_policy, that defaults to manual. But again, it becomes an option when you go
to 11.2 to enable the automatic facility which allows not only automatic tuning to the degree
of parallelism but also enables the ability to make indirect reads when doing a full table
scan.
[pause]
These two parameters were completely in the hands of Oracle. What is the algorithm that
it uses? How does the cost-based optimizer actually decide how to do it?
[pause]
Well, it's based on a whole set of hidden parameters. I've listed four of them here.
Adaptive_direct_read - the name tells you that's fairly critical. Then we have small_table_threshold,
very_large_table_threshold. Also, direct_read_decision_statistics_driven. Whether that decision between direct to indirect
should take account to a system statistics and object statistics.
These are some of the parameters that it will consider. And looking at the default and experimenting
with them, you can influence the algorithm.
[pause]
There are of course also other dependencies. It appears to consider the size of the buffer
cache, and perhaps most interestingly, the number of blocks of the object that are currently
cached.
[pause]
If you Google around, you'll find various people who attempted to reverse engineer the
algorithm. They'll tell you that if the table is more than 5x small_table_threshold or whatever
then you will always get a certain type of access method. I'm not going to present those
algorithms because I haven't reversed engineered them to my own satisfaction yet. But the end
result of all of these is that the algorithm Oracle uses may not be right for you in terms
of your session, but furthermore it may not be right for any other sessions.
This changing behavior for serial full table scans and parallel full table scans can have
a very huge effect on the performance of your applications and you have to investigate what's
going on.