Re: A free sql analysis tool

From: Daniel Hanks <hanksdc_at_about-inc.com>
Date: Thu, 08 Jan 2004 13:54:25 -0800
Message-ID: <F001.005DC2E3.20040108135425@fatcity.com>

On Thu, 8 Jan 2004, Jared Still wrote:

> interesting.
>
> you might want to hack it for time slices being in uS
> on 9i, otherwise you get some rather interesting results.
>
> Jared
>

Ah. Yeah. Still using 8i here. *sigh*. I've updated the script to allow you to set a variable to determine the resolution. It'll also now try and figure out the Oracle version on its own if it can.

I also tried the script on the sample 9i trace that comes with Oracle::Trace, and it croaks. Looks like I expect a PARSE line immediately following the END OF STMT line closing each PARSING IN CURSOR section. I'll have to fix that. Can anybody shed any light on why there are no PARSE lines after END OF STMT in the following two trace sections:

<snip>

PARSING IN CURSOR #1 len=32 dep=0 uid=63 oct=42 lid=63 tim=1046586568955488 hv=4220954983 ad='55afb3bc' alter session set sql_trace true
END OF STMT
EXEC #1:c=16000,e=50842,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1046586568924750 *** 2003-12-17 15:44:32.818

PARSING IN CURSOR #1 len=24 dep=0 uid=63 oct=3 lid=63 tim=1046586594549056 hv=4113743782 ad='55b0eef4' select sysdate from dual
END OF STMT
dep=0,og=4,tim=1046586594548851

EXEC #1:c=3000,e=19436,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1046586594577246
FETCH #1:c=1000,e=1235,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1046586594583271
FETCH #1:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1046586594615367

*** 2003-12-17 15:44:50.253
</snip>

Looking at Cary/Jeff's book, this is probably the answer: (page 104)

"The kernel did not emit information about the parse of the ALTER SESSION command, because tracking wasn't enabled until after the parse had completed."

That explains the first absence, but what about it not being there for the select statement? And what about that line beginning with "dep=0"...Is that normal? I looked through a cople of dozen of trace files I have (8i) and don't see any lines like that...Should I ignore such lines?

Thanks for any input,

Dan
Daniel Hanks - Systems/Database Administrator About Inc., Web Services Division
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Hanks INET: hanksdc_at_about-inc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jan 08 2004 - 15:54:25 CST