From oracle-l-bounce@freelists.org Thu Sep 2 18:39:12 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i82NdAk09584 for ; Thu, 2 Sep 2004 18:39:10 -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 i82NdAI09579 for ; Thu, 2 Sep 2004 18:39:10 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2208372E63D; Thu, 2 Sep 2004 17:49:34 -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 08502-17; Thu, 2 Sep 2004 17:49:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F3F37308BD; Thu, 2 Sep 2004 15:48:55 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Sep 2004 15:46:42 -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 6E42B72D192 for ; Thu, 2 Sep 2004 13:19:33 -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 27935-22 for ; Thu, 2 Sep 2004 13:19:33 -0500 (EST) Received: from amber.monochrome.net (unknown [63.247.74.106]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2FE9F72E135 for ; Thu, 2 Sep 2004 10:50:32 -0500 (EST) Received: from delysid by amber.monochrome.net with local (Exim 4.34) id 1C2tsR-0001Qq-Bq for oracle-l@freelists.org; Thu, 02 Sep 2004 10:51:55 -0500 Received: from 64.37.153.21 ([64.37.153.21]) (SquirrelMail authenticated user thump@cosmiccooler.org) by www.cosmiccooler.org with HTTP; Thu, 2 Sep 2004 08:51:55 -0700 (PDT) Message-ID: <2467.64.37.153.21.1094140315.squirrel@www.cosmiccooler.org> In-Reply-To: <065501c4906d$48df1130$6701a8c0@CVMLAP02> References: <4314.64.37.153.21.1094061706.squirrel@www.cosmiccooler.org> <065501c4906d$48df1130$6701a8c0@CVMLAP02> Date: Thu, 2 Sep 2004 08:51:55 -0700 (PDT) Subject: RE: tracing explaining PL/SQL From: "David" To: oracle-l@freelists.org User-Agent: SquirrelMail/1.4.0 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 X-Priority: 3 Importance: Normal X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - amber.monochrome.net X-AntiAbuse: Original Domain - freelists.org X-AntiAbuse: Originator/Caller UID/GID - [32014 32014] / [47 12] X-AntiAbuse: Sender Address Domain - amber.monochrome.net X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 8974 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: thump@cosmiccooler.org Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Can the event method be used to trace PL/SQL for other session to be more precise with my question? HEre is an example: Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 215 (TEST) ******************************************************************************** begin test.persister.save_message_batch(:message_id, :object_id, :method, :data, :call_time, :guaranteed, :delivery_type, :chunk_size, :enable_db_logging); end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.02 0.02 0 123 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.02 0.02 0 123 0 2 I'm confused why no disk or query fethes, which I equate to LIO's... -- .. David > David, > > Extended SQL trace works great for PL/SQL blocks. The following session > generates a little more than 1MB of trace data on my laptop: > > connect system/manager > exec sys.dbms_support.start_trace(true, true) > / > declare > c number; > begin > select count(*) into c from v$session; > select count(*) into c from dba_source; > end; > / > disconnect > / > > If you have a PL/SQL block for which the block's execution time is far > greater than the sum of its interior SQL statements' execution times, then > you should investigate the DBMS_PROFILER package. > > I believe you can use EXPLAIN PLAN only on an individual SQL statement, > not > a PL/SQL block. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > * Nullius in verba * --- To unsubscribe - mailto:oracle-l-request@freelists.org&subject=unsubscribe To read recent messages - http://freelists.org/archives/oracle-l/09-2004