DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: v$sqlarea

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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

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

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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]

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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


    Rajeev Suri

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

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

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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...

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

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


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