From oracle-l-bounce@freelists.org Wed Jun 30 17:46:10 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5UMjde16410 for ; Wed, 30 Jun 2004 17:45:49 -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 i5UMjT616389 for ; Wed, 30 Jun 2004 17:45:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D91D872C5AD; Wed, 30 Jun 2004 17:27:53 -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 17282-90; Wed, 30 Jun 2004 17:27:53 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2565672C566; Wed, 30 Jun 2004 17:27:53 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 30 Jun 2004 17:26:30 -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 B850672C1F7 for ; Wed, 30 Jun 2004 17:26:29 -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 17324-62 for ; Wed, 30 Jun 2004 17:26:29 -0500 (EST) Received: from maillnx-us112.fmr.com (maillnx-us112.fmr.com [192.223.198.27]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 59E2F72C0BB for ; Wed, 30 Jun 2004 17:26:29 -0500 (EST) Received: from virmro111nts.fmr.com (VIRMRO111NTS.fmr.com [172.26.5.99]) by maillnx-us112.fmr.com (Switch-3.1.2/Switch-3.1.0) with SMTP id i5UMnBTY026733 for ; Wed, 30 Jun 2004 18:49:11 -0400 Received: from MSGBOSCLF2WIN.fmr.com ([10.46.17.175]) by MSGMROIM01WIN.DMN1.FMR.COM with Microsoft SMTPSVC(5.0.2195.6713); Wed, 30 Jun 2004 18:49:10 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 content-class: urn:content-classes:message Subject: RE: Latch Free / Shared Pool on Fetch MIME-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit Date: Wed, 30 Jun 2004 18:49:09 -0400 Message-ID: <42BBD772AC30EA428B057864E203C999274399@MSGBOSCLF2WIN.DMN1.FMR.COM> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Latch Free / Shared Pool on Fetch Thread-Index: AcRe8lzL3ACYJnSZTCWDJSH0mHSK1gAAcrqw From: "Khedr, Waleed" To: X-OriginalArrivalTime: 30 Jun 2004 22:49:10.0221 (UTC) FILETIME=[750FAFD0:01C45EF4] X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4128 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Waleed.Khedr@FMR.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Probably to update dynamic dictionary tables/views to reflect the real time status/statistics for the running sql/cursor. Like columns: fetches, executions, etc in v$sql Just guessing! Regards, Waleed -----Original Message----- From: Larry Elkins [mailto:elkinsl@flash.net]=20 Sent: Wednesday, June 30, 2004 6:36 PM To: oracle-l@freelists.org Subject: Latch Free / Shared Pool on Fetch Maybe a simple question, but I cannot find the answer, and I've been looking for 24 hours now ;-) Why is a shared pool latch taken when (between?) fetching rows from a query? I have a trace file where once we start hitting the fetch lines, we are seeing latch free waits on the shared pool (9.2.0.5, p2 of 156=3Dshared pool). #8 is the select statement, #1 is the begin ... end of the anonymous block associated with "execute package.proc". FETCH = #8:c=3D0,e=3D99,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68= 4296371 WAIT #1: nam=3D'latch free' ela=3D 138809 p1=3D60175956 p2=3D156 p3=3D0 FETCH = #8:c=3D0,e=3D91,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68= 4503268 WAIT #1: nam=3D'latch free' ela=3D 207973 p1=3D60175956 p2=3D156 p3=3D0 FETCH = #8:c=3D0,e=3D94,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68= 4779497 WAIT #1: nam=3D'latch free' ela=3D 138264 p1=3D60175956 p2=3D156 p3=3D0 WAIT #1: nam=3D'latch free' ela=3D 70925 p1=3D60175956 p2=3D156 p3=3D1 FETCH = #8:c=3D0,e=3D107,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D6= 85059389 WAIT #1: nam=3D'latch free' ela=3D 207427 p1=3D60175956 p2=3D156 p3=3D0 WAIT #1: nam=3D'latch free' ela=3D 68432 p1=3D60175956 p2=3D156 p3=3D1 FETCH = #8:c=3D0,e=3D97,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68= 5402671 WAIT #1: nam=3D'latch free' ela=3D 278221 p1=3D60175956 p2=3D156 p3=3D0 FETCH = #8:c=3D0,e=3D93,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D1,dep=3D1,og=3D4,tim=3D68= 5747413 WAIT #1: nam=3D'latch free' ela=3D 209616 p1=3D60175956 p2=3D156 p3=3D0 WAIT #1: nam=3D'latch free' ela=3D 69029 p1=3D60175956 p2=3D156 p3=3D1 Anyway, it's just something I don't think I've seen before, and I haven't been able to get an answer on why this occurs. Note that if I take the SQL and run it just as SQL, then the cursor referenced by the FETCH and WAIT lines are the same value. Regards, Larry G. Elkins elkinsl@flash.net 214.954.1781 ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------