v$sqlarea

Hi

Does anyone know how to show sql_text in v$sqlarea fully? I have tried sqlplus and toad and I always get truncated results :0

And I dont get all the SQL statements from one user if I query v$sqlarea...

[Edited by pando on 03-05-2001 at 12:25 PM]

Unfortunately, that's because v$SQLArea only holds truncated text. If you want the full test, you will need to use v$SQLText, which holds the full statements, but broken into little chunks. I have a query to join them lying around somewhere. I'll post it if I find it.

Also, this view is of a cache, basically, and statements get cached out. This would most likely explain why you don't get all the SQL you would like to from it.

HTH,

- Chris

Well the strange thing is that with Quest Spotlight for Oracle I can see all the sql statement issued by the user but from sqlplus if I issue

select username, buffer_gets, substr(sql_text, 1, 4000)
from v$sqlarea b, v$session a
where b.address=a.sql_address
and username='XXXX'
order by buffer_gets desc

I only see about 8 queries when there are about 30 (and all fully)!
And i can not find out which table is Spotlight quering to get those results :o

[Edited by pando on 03-05-2001 at 12:45 PM]

You could always SELECT * FROM V$SQLAREA WHERE SQL_TEXT LIKE '%V$%' to see all the selects against the dynamic performance views. This should allow you to find the query that Spotlight is using.

As for the query you are using, it looks a little suspect. There is only one entry per connection in v$session, hence only one SQL_Address. Why would you expect this singular SQL_Address to point to all the statements this session has run? This just doesn't seem to make sense, but it could just be me :). I would expect that this would point to the current or latest statement that the session ran.

- Chris

[Edited by chrisrlong on 03-05-2001 at 01:55 PM]

try this --

SELECT T.SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE S.SID = 100 AND S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE

If you don't know the SID, you will have to use the username etc. to filter.

- Rajeev

I think sql_address and address corresponds to each other so hash_value and sql_hash_value because i queried both views v$session and v$sqlarea and checked for those values and the results of the query I issued made sense since all of them were issued but then again a lot were missing!

Rsuri I still cant see all te sql_text with that query :o

Pando,

If you match the rows to v$sqltext_with_newlines instead of v$sql_text then the sql text will be formatted as the user entered it rather than all mashed together..

-John

Well I am trying to find the offensive SQL statements that's why I am querying v$sqlarea, v$sqltext_with_newlines I used it to find the last statement issued by an user...

Pando,

take the value out of the ADDRESS column out of the row in V$SQL you are interested in and use it in this query...

select sql_text
from sys.v_$sqltext_with_newlines
where address = :addr
ORDER BY piece

forget the HASH_ columns. they don't always match up so well. I don't understand why, but they don't.

-John

EZSQL (www.ezsql.net) has a really easy interface for finding the bad queries... then you can right-click any of the queries to see the full text, or get an explain plan...

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