Re: Query does not complete

Keith

"Session Logical Reads” means reads from the buffer cache, reading cached blocks.
Why does it stop reading? A few sugestions. The query ran out of temp (sort) space,
session owner has mistakenly got SYSTEM as temp tablespace, maxextents exceeded?,
forgot some or all of the where clause => generating cartisian product, resource manager
invoked and logical/physical reads, cpu time exhausted.... Anyway - check the alert log file - there is most likely a hint or a trace file hidden there.
Or set sql_trace=true, run query, analyze trace file.

/Svend Jensen

Keith Brockman wrote:

> Problem:
> The problem is an sql query that ran in Oracle 8.0.6 and took less than
> 30 minutes, does not run to completion under Oracle 8.1.7.2.
>
> Hardware and Software:
> The hardware is a 64-bit four processor IBM RS/6000 running AIX 4.3.2.
> The software running is Oracle Enterprise Edition Server 64-bit
> 8.1.7.2.0.
>
> Work done:
> I had the developer run the query. Opening up Oracle Enterprise Manager
> Console, I went into Tools | Diagnostic Pack | Top Sessions. In reading
> the Top Sessions window the query session in question registers Active
> for status and for the first ten minutes running the query “Session
> Logical Reads” counter increments. After ten minutes the “Session
> Logical Reads” stops incrementing and the status remains Active. We let
> the query run from noon until midnight, which is when the database shuts
> down for backup, the query results never returns.
>
> Questions:
> 1) What queries, tools, or methods may I use to find why the query does
> not work?
> 2) I am new to Oracle Enterprise Manger Top Sessions. What exactly does
> “Session Logical Reads” measure? ( Number of Rows being retrieved?
> Blocks fetched? Bytes read?)
> 3) Are there other third party tools that may help in this situation,
> that Oracle Enterprise Manger does not do?
Received on Mon Sep 10 2001 - 13:07:50 CDT