bootom rows in a
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: bootom rows in a

  1. #1
    Join Date
    Mar 2001
    Posts
    65
    to isolate errors i want to query v$sqlarea for the query that is fired as soon as an application user takes a particular action.
    as v$sqlarea is a view iam unable to use rowid and rownum to get to the last few rows.
    iam unable to make out the table from where v$sqlarea derives the sql_text.

    how to handle?

  2. #2
    This query is the one I use to get the top ten queries ordered by executions.

    SELECT /*+ RULE */
    substr(rownum,1,2) as "No",
    sql_text,
    executions,
    buffer_gets,
    disk_reads,
    rows_processed
    FROM v$sql st, dual
    WHERE -1*st.executions = DECODE(dual.dummy(+), 'X',NULL, NULL)
    AND rownum <= 10
    ORDER BY st.executions DESC

    The decode and join to dual forces the order of the rows returned and gets around the problem of restricting by rownum with an order by. You should be able to modify this to pull out the sql just fired. Incedentally, every sql your user fires will appear in the v$sqlarea so how do you know how many rows you want to restrict to?

    -Bob

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