Tim Gorman's "...Cost-Based Optimizer.doc"

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_at_freelists.org on behalf of Mladen Gogala Sent: Mon 6/13/2005 6:15 PM
To: bbellows_at_usg.com
Cc: oracle-l_at_freelists.org; 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


--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l