Re: Basic query tracing and profiling question...

On Apr 17, 12:48 pm, Brian K <brikee..._at_geemail.invalid> wrote:
> I really have absolutely no idea what I'm doing in Oracle beyond logging
> in with the administrative tools. A full-bore Oracle DBA is on the way,
> but right now I am facing some crippling issues.
>
> My environment is an oracle 8.1.7 database on a quad-processor, four-gig
> machine.
>
> My problem is that an application that USED to run fine now seems to
> "lock up" at a specific point each time. if I leave it long enough, it
> WILL continue, but it takes anywhere from a half-hour to forty-five minutes.
>
> When I open up perfmon and look at CPU usage/memory faults/disk
> access/other basic counters, the only suspicious activity I see is that
> disk IO, generally minimal, shoots through the ceiling during this
> "freeze" time.
>
> I suspect this to be a sign of oracle performing a table scan on a large
> system table, and sure enough as I truncate various amounts of data from
> table XXXX, performance gradually improves until, with an empty table,
> the application shows no signs of locking up.
>
> How can I identify the problematic query and how can I verify the
> execution plan/whatever oracle calls it?

You need to do far more than simply examine the query plan. My first guess is this is on a Windows machine; strike one. My next guess is you don't have but two disks to process on: strike two. Correct me if I'm wrong but it appears your configuration is lacking in a lot of resources. Simply because you have installed 4 gig of memory on this box doesn't mean Oracle can use much of it. And it appears your physical configuration for this installation is far from ideal, or even workable.

Before anyone can go any further with this you need to report the following:

Operating system you're running Oracle on Available disk resources
What files you have on which disks
The init.ora file for this instance
How many users connect to this database
A Statspack report generated during the time when this occurs

Hopefully your 'full-bore DBA' will have enough knowledge and presence of mind to obtain this information. However, if you want help here we need that data. We can't answer you without it.

David Fitzjarrell Received on Tue Apr 17 2007 - 12:57:32 CDT