From oracle-l-bounce@freelists.org Fri Sep 30 10:11:52 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j8UFBkdb002369 for ; Fri, 30 Sep 2005 10:11:52 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j8UFBT6H002284 for ; Fri, 30 Sep 2005 10:11:39 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DB9F21F0531; Fri, 30 Sep 2005 10:11:11 -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 03936-05; Fri, 30 Sep 2005 10:11:11 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 62D341F053B; Fri, 30 Sep 2005 10:11:11 -0500 (EST) Message-ID: <1128093009.433d55511b0d8@mopp.namemagic.com> Date: Fri, 30 Sep 2005 16:10:09 +0100 From: scott.hutchinson@interact-analysis.com To: Thomas.Mercadante@labor.state.ny.us Cc: oracle-l@freelists.org Subject: RE: Millisecond timer in PL/SQL References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 User-Agent: Internet Messaging Program (IMP) 3.0 X-Originating-IP: 194.9.188.22 X-archive-position: 26172 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: scott.hutchinson@interact-analysis.com Precedence: normal Reply-To: scott.hutchinson@interact-analysis.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.5 required=5.0 tests=AWL,BAYES_00,NO_REAL_NAME autolearn=no version=2.63 Thomas, Unfortunatetly this only goes to 100th's of a second (same result as SELECT HSECS FROM V$TIMER). I'm after milliseconds. Thanks, Scott. Quoting "Mercadante, Thomas F (LABOR)" : > Scott, > > How about: > > select dbms_utility.get_time from dual? > > > > function get_time return number; > -- Find out the current time in 100th's of a second. > -- Output argukments: > -- get_time > -- The time is the number of 100th's of a second from some > -- arbitrary epoch. > > > > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org] On Behalf Of > scott.hutchinson@interact-analysis.com > Sent: Friday, September 30, 2005 10:31 AM > To: oracle-l@freelists.org > Subject: Millisecond timer in PL/SQL > > All, > > I'm looking for a way to measure the elapsed time (in milliseconds) of > executing a bunch of functions within a PL/SQL package. I've written a > "timer" > function that records this by using SYSTIMESTAMP, however is causes the > sessions to spend a significant amount of time waiting on "cache buffers > > chains". > > Coding "w_date := SYSTIMESTAMP;" will result in a recursive "SELECT > SYSTIMESTAMP FROM DUAL", and this is the sql being executed by sessions > waiting > on this latch (from v$session_wait and v$sqlarea). > > Does anyone have suggestions for ways that I can improve this? > > I have 50 concurrent sessions (batch jobs) running this, and each will > call > the "timer" function about 60 times per second. Originally I used > V$TIMER, > however the HSECS from this was not granular enough. > > Thanks, > > Scott Hutchinson > Interact Analysis Ltd > > ::This message sent using the free Web Mail service from > http://TheName.co.uk > -- > http://www.freelists.org/webpage/oracle-l > -- > http://www.freelists.org/webpage/oracle-l > ::This message sent using the free Web Mail service from http://TheName.co.uk -- http://www.freelists.org/webpage/oracle-l