-
May be shall I join v$sqltext_with_newlines and v$sqlarea? because I am really interested in buffer gets and disk reads in v$sqlarea view which are abesent from v$sqltext_with_newlines and v$sqltext
About EZSQL, its not bad but I am trying to work out this without any GUI tools, trying the hard way then the easy way! I had to use Spotlight because the results I got querying the views were a lot less than it should be, I know there are quite a few statmenets issued by an user but the view only gave me 11 rows where as spotlight returned around 40 which I was expecting! And will full sql text dunno how the heck they did it, may be you cant deal with varchar2(1000) in SQL PLUS?
[Edited by pando on 03-05-2001 at 04:31 PM]
-
Hi Pando,
I Don't know for sure how they do it in spotlight, but my guess is that they issue separate queries to get the data, then just show it to you all in one grid, giving you the 'effect' of getting this all in one query. you could trace your spotlight session to see for yourself....
As for joining v$sql and v$sqltext_with_newlines, sure you can do that but you will have a LOT of extra rows since sqltext_with_newlines breaks the query into 100 characters per row (or something like that). Maybe you can build a procedure that will return spool the query info (reads, sorts, execs, etc) and then the query itself all out through dbms_output based on some number of reads that you supply. You could put a cursor on V$SQL, looping though the rows that meet your criteria, then inside of that you could run the query against V$SQLTEXT_WITH_NEWLINES.
-John
-
I use top command on sun solaris to find most offensive querries. Then with PID on the view top provides and run the following script:
rem this is ps_view.sql
col username format a15
col osuer format a10
col program format a20
set verify off
select a.username, a.osuser, a.program, spid, sid, a.serial#
from v$session a, v$process b
where a.paddr = b.addr
and spid = '&pid';
rem this is ps_sql.sql
set verify off
column username format a15
column sql_text format a60
undefine sid
undefine serial#
accept sid prompt 'sid: '
accept serial prompt 'serial#: '
select 'SQL Currently Executing: '
from dual;
select b.username, a.sql_text
from v$sql a, v$session b
where b.sql_address = a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';
select 'Open Cursors: '
from dual;
select b.username 'USERNAME', a.sql_text 'SQL_TEXT'
from v$open_cursor a, v$session b
where b.sql_address =a.address
and b.sql_hash_value = a. hash_value
and b.sid = &sid
and b.serial# ='&serial';
The script will prompt for PID, which you can get from top command and it will return sid and servial#. By further providing sid and serial# to the running script. You will get the sqltext the session is running. It works fine for me.
Cheers
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|