Re: Basic query tracing and profiling question...

On Apr 18, 12:06 pm, Niall Litchfield
<niall.litchfi..._at_dial.pipex.com> wrote:

>
> Well by google's count I've posted 35 messages here in the last 3 months
> - way below what it used to be before one of the regular spammers here
> defaced my site of course, never the less though there will be enough
> people here who recognise my name from other forums nearly all of them
> more constructive than c.d.o.s has been for a while - very few are
> chastisements, I leave that to others more talented in this regard, most
> are. as are my contributions elsewhere, answers.

I miss your site, man. Bring it back.\

Charles:

We don't know which optimizer, do we? If optimizer_mode is the default, the advice might be to either get rid of all statistics or create all statistics. We're talking an old 8i system here.

Brian wrote:

>"Fix your system first" is not a remotely helpful response to a simple
>question. The incoming oracle DBA will "fix our system" when he/she
>gets here, in the meantime I'm under pressure from the powers-that-be to
>do SOMETHING right NOW.

Well, that isn't a db problem, that's a management problem. Figuring out what is wrong requires a methodology. There are many methodologies available, but just "doing random stuff and praying" tends to go in the wrong direction.

So in the sense of "fix your platform first," you seem to understand you need a test system, but seem to miss the point it has to accurately reflect the production system. Now, it is good that you are demonstrating a problem, but without control of the underlying variables you might not be demonstrating the problem you need to fix (though if you are lucky, you may be).

One thing to get you started: Download OEM 9i and use it to look at your problem. That tool makes it easy to check for statistics, for example. You need to figure out which optimizer you are using. You need to figure out if you are using Dictionary Managed Tables or Locally managed tables. What you may be seeing with DMT's is the table that you are using (or maybe some one that you don't know you are using) needs to be extended, and the table in the dictionary that tracks that also needs to be extended, so there is severe contention there. You might want to look at latch waits (there may even be some table named v$waits or something like that, you need to become familiar with the docs at tahiti.oracle.com).

Also see http://www.dbaoracle.net/readme-cdos.htm

jg

--
@home.com is bogus.
Seen at IOUG bookstore:  Tom Kyte's books stacked next to "silver
bullets" books.  Ain't that just all warm 'n fuzzy?