1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to find query history on Oracle database

Discussion in 'SQL PL/SQL' started by Shekhar81, Feb 16, 2009.

  1. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    I'm trying to find out what the exact queries are that are hitting the database. I don't have the source code of the application we are running so can't look at the sql from there.

    so is there a way in oracle database 10g to view the history of queries run? Sorry if it's something simple but i can't seem to find it!
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The following query will give the current running query with status in Oracle 10g.

    Code (Text):
    SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
    While the following query will give the result in Oracle 9i.

    Code (Text):
    SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
     
  3. Shekhar81

    Shekhar81 Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    hi thanks for the query. It obviously works but now i have got another problem. I had a lot of fnd_global variables thrown in the output that i can't make a head or tail out of, but i guess i will have to look further into it myself. Thanks for your help though...
     
  4. anurag_telenor

    anurag_telenor Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi Raj,

    what if the query has been executed multiple times in past ( assuming that awr had the history available with it ). How can we see it's plan ?

    regards,
    Anurag
     
  5. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Hi all,
    I've tried the code; SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.SQL_ID = b.SQL_ID;
    and of-course I am working under oracle 9i, they code raise an error ORA-00904: "B"."SQL_ID": invalid identifier.
    I will appreciate your help to solve this prob.
    Thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL_ID didn't appear in the data dictionary until 10g; up until then it is necessary to use the ADDRESS and HASH_VALUE columns of both views:

    Code (SQL):
    SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.address = b.address AND a.hash_value = b.hash_value;
     
  7. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Dear David,
    thank you for the reply but unfortunately I am getting the same error; ORA-00904: "A"."HASH_VALUE": invalid identifier
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
     
    SELECT a.USERNAME, b.sql_text, a.STATUS FROM V$SESSION a INNER JOIN V$SQLAREA b ON a.sql_address = b.address AND a.sql_hash_value = b.hash_value;
     
     
    My typo; the above query should work.
     
  9. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Hi all,
    try this one;
    select s.username, s.sid, s.serial#,t.sql_text "Last SQL"
    from gv$session s, gv$sqlarea t
    where s.sql_address =t.address and
    s.sql_hash_value =t.hash_value and
    s.sid = '&sid';
     
    kiran.marla likes this.
  10. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    by the way david, I have tried the last code you have provided, it did work but giving me nothing!!!!!!!
    Do you thing the issue is related to enabling trace at instance level?
    set sql_trace=True ?
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Absolutely not; you've used the same code I gave you, adding the SID value to the where clause and changing the view names to gv$... rather than v$... and you report it does return data. The only difference between my query and yours is that the GV$ views also include INST_ID and are designed for use in RAC environments. Not matching on INST_ID (and not returning that column from one view or the other) in a RAC environment produces basically meaningless results as you won't know which instance the query ran from and the query can return useless results as data for one instance can show up for all RAC nodes.

    I get results from my query so I can't understand why your variant works and mine does not (they are basically the same query).
     
  12. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Hi,
    sorry about the misunderstanding, I am not trying to showoff or whatever and trust me i am in need to learn from you and others otherwise I don't need to be here.
    i was trying to check other code and by mistake I paste the wrong code for the RAC instead of asking you that your code it did run after you have sent it for the second time but still not getting anything except the code it self, I have asked if things related to set trace=True?

    Best
    Regards
    and I appreciate your time
    Liali
     
  13. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Sorry I mean;

    set sql_trace=True ?
     
  14. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Code (SQL):
    SELECT s.username, s.sid, s.serial#,t.sql_text "Last SQL"
    FROM gv$session s, gv$sqlarea t
    WHERE s.sql_address =t.address AND
    s.sql_hash_value =t.hash_value;
    The query provided in this thread is perfectly working....
     
  15. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Thank you :)
     
  16. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    And so does mine.

    There is no link between data in v$sqlarea and sql_trace = True as the latter generates trace files in the user_dump_dest. It has nothing to do with data in v$sqlarea or gv$sqlarea.
     
  17. liali

    liali Active Member

    Messages:
    8
    Likes Received:
    1
    Trophy Points:
    65
    Thank you for the updated information David.
    I do appreciate your help too.