Basic SQL Question, re. logging costly sql

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. Received on Wed Oct 19 2005 - 15:14:43 CDT