Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 5645 invoked from network); 8 Aug 2008 21:00:26 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 8 Aug 2008 21:00:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 83E028E44E1; Fri, 8 Aug 2008 22:00:11 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 12750-02-15; Fri, 8 Aug 2008 22:00:11 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 895868E2427; Fri, 8 Aug 2008 22:00:10 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 08 Aug 2008 21:58:11 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 95FDD8E4423 for ; Fri, 8 Aug 2008 21:58:10 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 11541-07 for ; Fri, 8 Aug 2008 21:58:10 -0400 (EDT) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.191]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5AF7E8E435D for ; Fri, 8 Aug 2008 21:58:02 -0400 (EDT) Received: by fk-out-0910.google.com with SMTP id 18so949557fks.2 for ; Fri, 08 Aug 2008 18:57:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=JbjFNV/7pbPeQR1rVGervR2TwQb6h4hDvfUDvhE2RuE=; b=uyKws0fVDu0AgSrVRHfGzgAiWCbJBShdH+GBn+n3tb275MIFL8bqoLWmUZmefc52ZU uVxfTEoqVaBj6sTszrJSa05M3h2dnzoKvqmMsFWlfCKt/LTMReAtHd8yfjDTatET11Zz lKDT4FqK6I4Uxfq0n8FGcSYc/b5C6VcJgcGh4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=QtaOfcyX8Gjq3nmGTU4xY1h+mI/anUWqBibpk8Qh/j9wFPimMHusSjgUCPg/eWRWGT k6Vp2EDASru3yTFsRrdVjp+08CVs/MT71gFjiuyJjxn+AFW2BTIAA8pGzVx+cNdAtgk7 KfboV+7Oik5w30KR+tQctRPc1FVWace860pJk= Received: by 10.181.22.8 with SMTP id z8mr2480360bki.78.1218247077779; Fri, 08 Aug 2008 18:57:57 -0700 (PDT) Received: by 10.125.164.15 with HTTP; Fri, 8 Aug 2008 18:57:57 -0700 (PDT) Message-ID: Date: Fri, 8 Aug 2008 20:57:57 -0500 From: "Bradd Piontek" To: ajayoraclel@yahoo.com Subject: Re: SQL*Net message from client - But very high CPU Cc: oracle-l@freelists.org In-Reply-To: <445091.91077.qm@web57507.mail.re1.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11339_21384079.1218247077772" References: <445091.91077.qm@web57507.mail.re1.yahoo.com> X-archive-position: 10021 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: piontekdd@gmail.com Precedence: normal Reply-to: piontekdd@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_11339_21384079.1218247077772 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Looks like you are peforming a full table scan of a rather large table. 67,207,044 rows. I'd start looking there. Bradd Piontek Oracle Blog: http://piontekdd.blogspot.com Linked In: http://www.linkedin.com/in/piontekdd On Fri, Aug 8, 2008 at 8:36 PM, Ajay Thotangare wrote: > Hi List, > I need help in solving performance problem of a query. My database is > 10.2.0.3 on HP-UX Itanium. > I traced the session during the run and noticed the session is using very > high CPU but the wait event in v$session shows "SQL*Net message from > client". > In top(UNIX command) this session is the top most. Not able to interpret > whats happening. > Below is the trace details > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > Parse 1 0.00 0.00 0 0 > 0 0 > Execute 13266 25.92 28.85 0 0 > 0 0 > Fetch 13267 1768.47 1875.36 47 67207044 0 > 23592 > ------- ------ -------- ---------- ---------- ---------- ---------- > ---------- > total 26534 1794.39 1904.21 47 67207044 0 > 23592 > Misses in library cache during parse: 0 > Optimizer mode: ALL_ROWS > Parsing user id: 998 > Noticed fetch is using 1768.47 CPU of the elapsed time 1875.36. > Any help appreciated. > --regards, > Ajay > > ------=_Part_11339_21384079.1218247077772 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Looks like you are peforming a full table scan of a rather large table. 67,207,044 rows. I'd start looking there.

Bradd Piontek
Oracle Blog: http://piontekdd.blogspot.com
Linked In: http://www.linkedin.com/in/piontekdd


On Fri, Aug 8, 2008 at 8:36 PM, Ajay Thotangare <ajayoraclel@yahoo.com> wrote:
Hi List,
I need help in solving performance problem of a query. My database is 10.2.0.3 on HP-UX Itanium.
I traced the session during the run and noticed the session is using very high CPU but the wait event in v$session shows "SQL*Net message from client".
In top(UNIX command) this session is the top most. Not able to interpret whats happening.
Below is the trace details
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  13266     25.92      28.85          0          0          0           0
Fetch    13267   1768.47    1875.36         47   67207044          0       23592
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    26534   1794.39    1904.21         47   67207044          0       23592
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 998 
Noticed fetch is using 1768.47 CPU of the elapsed time 1875.36.
Any help appreciated.
--regards,
Ajay


------=_Part_11339_21384079.1218247077772-- -- http://www.freelists.org/webpage/oracle-l