How to Identify User and SQLs in an Oracle database?

This is a very common scenario for a DBA to be in a situation where a DBA needs to find out one of the following:

• Which user is running which SQL?
• A particular SQL is being run by which user?
• A particular user is running which SQL?

As it is evident from these questions – all these refer to the current scenario or situation. In fact current activitiy gives you much more than the above. Examples like:

• Which all users are logged on?
• Computers being used by the users?
• How long is the query running?

All these situations related to the topic of performance tuning your Oracle database. There maybe be poor performing SQLs in the Oracle database or the Oracle database may not be configured to take the load of users and SQLs. Either way, you need to identify users, SQLs so that you can attack the problem.

All these information is embedded in the v$ tables and also available via OEM. If you do not have access via OEM, you can request SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY grant. Alternatively, if you have SELECT grant on v$session and v$sqlarea, then also you are fine.

Related topic: Performance Tuning using v$

Once you have the select privilege in place, try running this SQL:

SELECT
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS,
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC