From oracle-l-bounce@freelists.org Wed Apr 21 08:39:52 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3LDcsR07041 for ; Wed, 21 Apr 2004 08:39:04 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3LDci606954 for ; Wed, 21 Apr 2004 08:38:54 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E5D472CC14; Wed, 21 Apr 2004 08:31:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29667-89; Wed, 21 Apr 2004 08:31:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E80D072CBDE; Wed, 21 Apr 2004 08:31:42 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 21 Apr 2004 08:30:32 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A2F1B72CB9E for ; Wed, 21 Apr 2004 08:30:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30783-24 for ; Wed, 21 Apr 2004 08:30:31 -0500 (EST) Received: from torntsims03.hsia.fairmont.com (smtp.hsia.fairmont.com [142.131.15.59]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4969C72CB1C for ; Wed, 21 Apr 2004 08:30:31 -0500 (EST) Received: from [142.131.65.172] (unverified [142.131.65.172]) by torntsims03.hsia.fairmont.com (Vircom SMTPRS 2.1.264) with ESMTP id for ; Wed, 21 Apr 2004 09:33:31 -0400 User-Agent: Microsoft-Entourage/10.1.4.030702.0 Date: Wed, 21 Apr 2004 07:42:52 -0600 Subject: Re: SQL Trace Qs From: Tim Gorman To: Message-ID: Mime-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3433 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tim@sagelogix.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Any chance you can post the lines from the "raw" trace file surrounding that EXEC line? You'd want to post any lines after the PARSE and prior to the first FETCH line as well. I'm guessing that there are some WAIT lines, assuming that you've traced at level 8 or above... My suspicion is that it has something to do with the binding of the values for the bind variables, which depending on the API in use might show some "SQL*Net message from client" waits? What API is in use here? JDBC? ODBC? Oracle Forms or Oracle Reports (a.k.a. OCI)? on 4/21/04 5:13 AM, VIVEK_SHARMA at VIVEK_SHARMA@infosys.com wrote: > Folks > > What could be the Cause for HIGH Value of elapsed time=3D3.27 in the > following SELECT Queries? > > NOTE - It is doing a UNIQUE Index Scan=20 > > Will provide any info required > > Thanks > > > ************************************************************************ > **** > SELECT COUNT(*) > FROM > TSD WHERE SCHM_CODE =3D :1 AND FLOW_CODE =3D :2 > > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ > Parse 1 0.00 0.01 0 0 0 0 > Execute 268 0.08 3.27 0 0 0 0 > Fetch 268 0.04 0.11 0 536 0 > 268 > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ > total 537 0.12 3.40 0 536 0 > 268 > > Misses in library cache during parse: 1 > Optimizer goal: CHOOSE > Parsing user id: 12 (TBAGEN) > > Rows Execution Plan > ------- --------------------------------------------------- > 0 SELECT STATEMENT GOAL: CHOOSE > 0 SORT (AGGREGATE) > 0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF > 'TD_SCHM_DEFN_TABLE' > 0 INDEX GOAL: ANALYZED (RANGE SCAN) OF > 'IDX_TD_SCHM_DEFN_TABLE' (UNIQUE) ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------