Re: How to trace what is happening inside the stored procedure

From: Tim Gorman <tim_at_evdbt.com>
Date: Sun, 06 Feb 2005 20:29:43 -0700
Message-ID: <BE2C2EB7.23052%tim@evdbt.com>

on 2/6/05 2:56 PM, Nuno Souto at dbvision_at_iinet.net.au wrote:

> Tim Gorman apparently said,on my timestamp of 7/02/2005 4:40 AM:
>

>> 
>> The session info (i.e. V$SESSION) is not the only place where MODULE and
>> ACTION are used.  If you notice, they are also used in the Library Cache, in
>> the Shared SQL Area, as exposed by the V$SQL view.  Thus, the same SQL
>> statement executed while either MODULE or ACTION have different values will
>> result in a new entry in V$SQL.

>
> Are you saying that application_info data is duplicated all over the place?
> Last time I looked, the V$* views are based on the X$* views, which are
> direct "windows" to shared memory. I'd say that application_info
> data will be in one place only, and visible in various V$* views.
> Any savings are quite frankly moot.

I said that MODULE and ACTION become part of a "unique key" of V$SQL, in addition to being posted within V$SESSION. It turns out that it is not. They are just recorded in V$SQL when the SQL is parsed. My bad...

>> So, MODULE and ACTION are also taking up space in the Shared Pool, as well
>> as the session global areas.  Not only are they taking up space, but they
>> are being used as part of a "primary key" value.  Like any other "primary
>> key" then, there are a lot of good reasons to keep the values concise.

>
> What good reasons? There are no good reasons that I can see to make a
> primary key concise, even if application_info was such.

I was wrong about them being used as a unique key (as mentioned above), so that's a moot point.

But if you're in the habit of building apps with mile-long unique/primary key values, then I hope I never have to maintain or replicate from applications you've designed or built.

>> Besides, what's wrong with 32 bytes and 48 bytes?  If you can't say
>> something unique in 32-48 characters, it's probably not worth saying.

>
> I don't think so. It doesn't take long for a simple message to exceed
> 32 characters. If I want to note the name of the procedure that is
> executing in a package plus a running counter, I'm already over the limit:
> a name of a procedure is already by itself 30 characters long max.

Then don't do that.

Besides, wouldn't you be better off using the SET_SESSION_LONGOPS procedure in the DBMS_APPLICATION_INFO package to put that type of stuff (i.e. counters, progress, etc) to the V$SESSION_LONGOPS view? MODULE, ACTION, and CLIENT_INFO are not the only option for that type of information...

>> Reference "The Elements of Style" by Strunk and White...  :-)

>
> I'm not sure style of prose has anything to do with monitoring
> a PL/SQL package.

Then you've not referenced it. It is about being concise. Kernighan and Plauger found that message relevant enough to pay homage with "The Elements of Programming Style" in 1978. It has everything to do with writing in general, whether prose or programming.

>> So, I wouldn't call it "short-sightedness", but I would call it poor
>> documentation...

>
> I call it short-sightedness.

You feel that the DBMS_APPLICATION_INFO package is well documented?

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Feb 06 2005 - 22:44:50 CST