Re: SQL tuning nightmare - db file sequential reads

On Thu, 06 Jul 2006 00:22:06 -0700, Gints Plivna wrote:

> Yea in the introduction of the first chapter in his book Jonathan
> mentions it, although in very few words. It is also important to
> distinguish between first_rows and first_rows_n, these are two
> different optimizer modes and at least dont expect them both work the
> same way.
> With all that I can say that at least for me first_rows_n usually
> works.

It does indeed. When I want to emulate RBO, I use it on the session level, in addition to the OPTIMIZER_INDEX* parameters. That way I am getting very decent RBO emulation. The problem with the RBO emulation rears its ugly head when you enter a SQL which is not well suited for RBO-type optimization, like in the case brought by the OP. My worst problems in Oracle version 7.1.3 and 6.0.36 were big reports. RBO had an inclination to use indexes when it wasn't warranted, so I had to disable them by using things like WHERE ename||'' = 'SMITH' or WHERE empno+0=1234. Data warehousing with 7.1.3 was a nightmare. Incidentally, version 7 was the first version with CBO. Now, full 3 major versions later, CBO is almost usable.

> I'm not eager to get to know always every single detail,

I am, especially when it comes to the essential parts of the puzzle, like the optimizer. Most of the problems in my oracle instances come from bad SQL. As a DBA, I'd better be able to make something about it. A good DBA must have a Midas touch: must be able to turn anything into a muffler.

> although it would be nice of course to be able to get that info
> somewhere in Oracle docs. On the other hand if you use optimizer mode
> all_rows then expecting wide use of indexes and range scans is a bit
> strange, because the very name "all_rows" somehow contradicts this. So
> yea I'm a bit of a half blind man walking behind first_rows_n but I'v
> also been in Jonathan's seminar and have his book, quickly scanned it
> and got an at least an overview of what's going on. Yea and of course
> got only overview not because book haven't details but because I
> scanned it too fast.

I met Jonathan on CTOUG course 2 years ago and I have all of his books (both of them). He's one of my 3 favorite Oracle experts. I case you're wondering, the other two are Tom Kyte and Cary Millsap.

-- 
http://www.mgogala.com