Basic SQL Question, re. logging costly sql

Giganews Newsgroups
Subject: Basic SQL Question, re. logging costly sql
Posted by:  BD (bobby_dre…@hotmail.com)
Date: 19 Oct 2005

Hey, all.

This is actually more of a SQL question than an Oracle question - but,
since it's Oracle-specific, I'll risk a mild slapping about by posting
here.

We have a routine (which I think originally came from Metalink) which
pulls information from v$sql into an external table; we export that
table, import it into a non-production database so we don't affect
prod, and query that table for optimizer cost, execution frequency,
etc. The point is to identify high-cost SQL that requires tuning.

I am attempting to add the ID which invoked the various queries to the
available information. The userID _number_ is included in v$sql (as
parsing_user_id), but to determine the user name I need to join that to
v$session (on user#). In this case, I am finding that doing this join
increases the output record count considerably.

Here I'm selecting count (*) for illustrative purposes, but in reality
I'd be calling for username from v$session, and the remainder of the
fields would come from v$sql.

ORIGINAL QUERY:
select count (*) from v$sql, dual where executions > 6;
OUTPUT: 2211

NEW QUERY:
select count (*) from v$sql l, v$session n, dual where
n.user#=l.parsing_user_id and executions > 6;
OUTPUT: 327589

All I want to do is add the username to the output of my query of v$sql
and v$session. Clearly, the query is returning rows from v$session
which I don't want, but I do want the USER values from v$session. I
admit I'm a SQL noob, and am not sure how to tune my query accordingly.

I suspect it may be that user# and parsing_user_id are not what I
understand them to be, or they do not have a 1:1 relationship as I
think I will require for this to work.

Any help, as always, is appreciated.

Replies