Basic query tracing and profiling question...

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? Received on Tue Apr 17 2007 - 12:48:16 CDT