Re: Query does not complete

Are the plans of the two queries the same? Are the tables on both instances analyzed? Are you using the same optimizer setting on both instances? To answer your questions: the quey tools you should be looking at are tkprof, explain plan, and autotrace. 2 logical reads referrs to the number of blocks from memory being read. 3 I'm sure there are lots of third party tools out there, but you should start with the three I mentioned above.

Keith Brockman wrote:

> This is a multi-part message in MIME format.
> --------------36B9A9A46D32BB494605D0A8
> Content-Type: text/plain; charset=iso-8859-1
> Content-Transfer-Encoding: 8bit

> 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?

> --------------36B9A9A46D32BB494605D0A8
> Content-Type: text/x-vcard; charset=us-ascii;
> name="kbrockman.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Keith Brockman
> Content-Disposition: attachment;
> filename="kbrockman.vcf"

> begin:vcard
> n:Brockman;Keith
> tel;fax:608-265-2090
> tel;work:608-265-5043
> x-mozilla-html:FALSE
> url:www.uwsa.edu
> org:University of Wisconsin System Administration;Office of Information
Services
> adr:;;780 Regent Street;Madison;WI;53715;
> version:2.1
> email;internet:kbrockman_at_uwsa.edu
> title:Database Administrator
> fn:Keith Brockman
> end:vcard

> --------------36B9A9A46D32BB494605D0A8--

Posted via www.orafocus.com - Focusing on the World of Oracle Received on Mon Sep 10 2001 - 13:44:18 CDT