|Subject:||Basic SQL Question, re. logging costly sql|
|Posted by:||BD (bobby_dre…@hotmail.com)|
|Date:||19 Oct 2005|
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
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.
select count (*) from v$sql, dual where executions > 6;
select count (*) from v$sql l, v$session n, dual where
n.user#=l.parsing_user_id and executions > 6;
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.