v$sqlarea - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: v$sqlarea

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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]

  2. #12
    Join Date
    Nov 2000
    Posts
    344
    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



  3. #13
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    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
  •  



Click Here to Expand Forum to Full Width