Tim Gorman's "...Cost-Based Optimizer.doc"
- From: "Marquez, Chris" <cmarquez@xxxxxxxxxxxxxxxx>
- To: <mgogala@xxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Jun 2005 10:52:52 -0400
Tim Gorman's "The Search for Intelligent Life in the Cost-Based Optimizer.doc"
In 11 pages Tim Gorman gives a 200+ page book worth of information about the
history of Oracle SQL processing decisions (RBO, CBO) and the flow and
retention of data from disk through Oracle memory and eye opening insight to
the effects (session "events" & "waits") commonly seen during large "logical"
and "physical" IO queries.
In the end the paper is about Cost-Based Optimizer and the under used
"OPTIMIZER_*" parameters...Tim is even so kind as to give you all the answers
in the first page of his paper.
But those even slightly familiar it with the internals of Oracle engine will
find this doc a great and must read.
Thanks for bring it to my attention...turns out I had this document in my
collection since July 2001...guess I was not able to appreciate it or
comprehend it back them.
One comment / question on OPTIMIZER_INDEX_COST_ADJ;
It seems that on "NON-cached" filesystems (e.g. RAW, OCFS) that there would/do
NOT be a great difference in "db file scattered reads" vs. "db file sequential
reads" AVERAGE_WAITS...as every read from disk (on non-cached filesystem) is a
*real* read from dusk...no OS buffer to help, no?
Thanks,
Chris Marquez
Oracle DBA
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Mladen Gogala
Sent: Mon 6/13/2005 6:15 PM
To: bbellows@xxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; Paul Drake
Subject: Re: High db file sequential reads
Bellows, Bambi wrote:
>Hi Everybody!
>
>
Hi Bambi! Long time no see, where have you been hiding?
>
>I've been administering a system lately that's used almost exclusively
>for Oracle Financials and one of the things that Oracle Financials does
>every well is hide what it's doing. Oh, sure, you can poke around in
>v$sql but that doesn't really do much for you. So I've been rather
>dependent on statspack (lovely thing) and from there I can see what's
>eating up time and analyze the structure and, as needed, apply indexes
>to the tables. There's not really much more you can do than that as you
>have no access to the underlying queries, and, even if you did, you sure
>couldn't change anything, database parameters aside, of course. That
>being said, these percentages seem out of whack to me... what do you
>think?
>
>Top 5 Timed Events
>~~~~~~~~~~~~~~~~~~ %
>Total
>Event Waits Time (s)
>Ela Time
>-------------------------------------------- ------------ -----------
>--------
>db file sequential read 438,536 2,792
>65.14
>CPU time 1,284
>29.96
>db file scattered read 32,239 165
>3.84
>log file parallel write 12,667 14
>.32
>SQL*Net break/reset to client 1,384 9
>.20
> -------------------------------------------------------------
>^LWait Events for DB: PFIN Instance: PFIN Snaps: 3962 -3963
>Has anyone successfully gotten db file sequential reads into any kind of
>sane range for any period of time?
>
>
As you are probably aware of, db_sequential_reads are a consequence of
index scans. Your question
can be reformulated as looking for a way to increase the number of full
table scans without really changing
SQL, as you are using COTS package. Tim Gorman had a ground breaking
article about OPTIMIZER_INDEX
parameters entitled "Searching for intelligent life in CBO" and Jeff
Maresh had a very good article named
"In defense of full table scans". Both articles are on
http://www.evdbt.com.
The parameters described by Tim are normally abused to turn CBO into
RBO, that is into a garden variety of
a database optimizer which strongly favors indexes. The trick is to set
OPTIMIZER_INDEX_CACHING=80
and OPTIMIZER_INDEX_COST_ADJ=20. As a result, optimizer will use index
whenever it is even remotely
possible, which is the behavior of RBO. Nested loops will also be
strongly preferred over any other way of joining
tables. That is the trick that I use to manage OLTP databases. I don't
want any full table scans in my online web
originated transactions. Jonathan has shown, in an article published in
DBAzine, that if your application was seriously
@#$%##! up in the design phase, it is possible for CBO to use wrong
index, so one must take care when doing so.
All of this is very interesting and is exactly the opposite of what you
want. You want to increase the number of full
table scans at the expense of index scans. The only way I can think of
that would actually help you to achieve that goal
is to set OPTIMIZER_INDEX_CACHING=0 and OPTIMIZER_INDEX_COST_ADJ=200. If
you created a huge
hash_area_size and turned off automatic PGA sizing, hash joins would
become much more frequent and your database
would start doing many more full table scans. That would certainly cut
down on db sequential reads and result in
healthy increase of db scattered reads.
Before doing this, you gotta ask yourself one question and one question
only. No, it isn't the "do I feel lucky" question
from the "Dirty Harry" movie. The question that I am talking about is
whether there is anything that is actually broken?
Is anybody complaining? Are you trying to tune the instance (instead of
tuning the application) using time spent in db
scattered reads as an artificial unit, instead of now infamous cache
hit ratio? There is a great furniture sales brochure
called "The Tales Of the Oak Table" in which a furniture salesman named
Gaja Vaidayanatha explains the term "CTD"
or Compulsive Tuning Disorder. So, the question you should ask yourself
is: is anything actually broken or am I suffering
from CTD? If something is actually broken and you have to deal with a
reporting database suffering from far too many index
scans, then the trick described above is the best way to go.
The only other way of achieving the desired goal is to drop few
indexes, but I doubt that your application vendor
would condone that.
--
Mladen Gogala
Oracle DBA
Ext. 121
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
