Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: How can I check what SQL is running

    Is there any command/performance view can check what SQL is running on the Oracle server of all user?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > Is there any command/performance view can check what SQL is running on the Oracle server of all user?
    Yes, use a SELECT command.
    Quantify "PERFORMANCE".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2001
    Posts
    80
    Quote Originally Posted by anacedent
    > Is there any command/performance view can check what SQL is running on the Oracle server of all user?
    Yes, use a SELECT command.
    Quantify "PERFORMANCE".
    Thx anacedent,
    Could you pls. give me the full select statement as I can't find v$performance, thx.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT TEXT FROM V$SQL;
    Rhetorical question - Are you unwilling or incapable of RTFM at http://tahiti.oracle.com yourself?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    As Anacedent said ... Define Performance issues ...

    ex: This shows that top 30 sql statements for logical reads. You have
    to table the hash value and look into v$sqltext to find the actual statment.

    ---- Top 10 by Buffer Gets: ----

    column sql format a40
    set linesize 100
    set pagesize 100
    SELECT * FROM
    (SELECT substr(sql_text,1,40) sql,
    buffer_gets, executions, buffer_gets/DECODE(executions,0,1,executions) "Gets/Exec",
    hash_value,address
    FROM V$SQLAREA
    WHERE buffer_gets > 10000 and sql_text not like 'DECLARE job BINARY_INTEGER%'
    ORDER BY buffer_gets DESC)
    WHERE rownum <= 30
    ;

    But that's only for Logical reads ... are you looking at parses, number of executions, disk reads, or what the data block buffers are doing ??

    You might also check the wait events to try and get a feel for what the
    database is waiting on ...

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •