Okay I am answering my own problem, but for the :-
1st Query, I modified the query to :-
SELECT r.username, b.sql_text, executions , serial#
r.user_id,machine,terminal,program,a.username,osuser, sid,
process,ROUND(((disk_reads * 100) / DECODE(executions, 0, 1,
executions)))
FROM sys.dba_users r, v$session a, v$sqlarea , v$sqltext b
WHERE parsing_user_id=user_id
AND b.address=a.sql_address(+)
AND a.sql_hash_value = b.hash_value
and parsing_user_id=user_id
ORDER BY executions desc
/
Now I am getting the required fields, like serial#,
r.user_id,machine,terminal,program,a.username,osuser, sid
Is the above query correct? Please help and respond
For the 2nd query, I modified the query to :-
SELECT b.sql_text, executions ,serial#,machine,terminal,
program,a.username,osuser, sid, process,
FLOOR(buffer_gets / DECODE(executions, 0, 1, executions))
FROM v$session a, v$sqlarea , v$sqltext b
where a.sql_address = b.address
AND a.sql_hash_value = b.hash_value
ORDER BY executions desc
This also provides me the required output. But I want to ask you whether the above is
correct? Please respond.
Thanks and Regards,
Raja
On Wed, 28 Nov 2001 20:15:22
Viraj Luthra wrote:
>Hello all,
>
>I am running the following query :-
>
>SELECT a.username, sql_text, executions ,
> a.user_id,machine,terminal,program,
> a.username,osuser, sid, process,
> ROUND(((disk_reads * 100) / DECODE(executions, 0, 1,
> executions)))
>FROM sys.dba_users a, v$session, v$sqlarea
>WHERE parsing_user_id=user_id AND address=sql_address(+)
>ORDER BY executions desc
>
>
>But when I run this query, I dont get the username, user_id, machine, terminal,
>program, osuser, sid, process of all records.
>
>Why is this, that is why I get these fields populated in some records and others I
>dont get?
>
>Also I am running another query:-
>
>SELECT a.username,osuser, sid, process, serial#,length(sql_text),
> sql_text,
> executions,
> FLOOR(buffer_gets / DECODE(executions, 0, 1, executions))
> FROM sys.dba_users a, v$session, v$sqlarea
> WHERE parsing_user_id=user_id AND address=sql_address(+)
> ORDER BY executions desc
>
>Here also I dont get the username, osuser, sid, process, serial# populated all the
>time, Why?
>
>Please help.
>
>Thanks and Regards,
>
>Raja
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Viraj Luthra
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viraj Luthra
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
