This discussion is archived
8 Replies Latest reply: Mar 31, 2013 5:44 AM by Nikolay Savvinov RSS

How to find last executed query ?

879152 Newbie
Currently Being Moderated
Hi,

My application is running on ASP.Net (Frontend) and Oracle DB (Backend).
When I run one report from Frontend, I want to know which store procedure or query is
fired/executed from backend, or want to know last executed sql query from backend.

Please reply.

Thanks
Solaiman
  • 1. Re: How to find last executed query ?
    sb92075 Guru
    Currently Being Moderated
    876149 wrote:
    Hi,

    My application is running on ASP.Net (Frontend) and Oracle DB (Backend).
    When I run one report from Frontend, I want to know which store procedure or query is
    fired/executed from backend, or want to know last executed sql query from backend.

    Please reply.

    Thanks
    Solaiman
    ALTER SESSION SET SQL_TRACE=TRUE;

    Handle:     876149
    Email:     solaimaneu@gmail.com
    Status Level:     Newbie
    Registered:     Jul 30, 2011
    Total Posts:     33
    Total Questions:     14 (10 unresolved)


    I extend my condolences to you since you rarely get answers to your questions here.
  • 2. Re: How to find last executed query ?
    879152 Newbie
    Currently Being Moderated
    Hi
    Thanks for reply.

    How to find last executed sql query?
  • 3. Re: How to find last executed query ?
    sb92075 Guru
    Currently Being Moderated
    876149 wrote:
    Hi
    Thanks for reply.

    How to find last executed sql query?
    process the resultant trace file using TKPROF.
    The last SQL will be at the end of the output report file from TKPROF; which can also contain all EXPLAIN PLANs
  • 4. Re: How to find last executed query ?
    Rahul_India Journeyer
    Currently Being Moderated
    >


    >
    >
    I extend my condolences to you since you rarely get answers to your questions here.
    lol
  • 5. Re: How to find last executed query ?
    user639304 Explorer
    Currently Being Moderated
    Hi,

    As sb92075 suggested, you could enable SQL_TRACE. But this implies that if you haven't enabled SQL_TRACE before running your query, you could not use trace file (genereated by Oracle when SQL_TRACE has been set to TRUE).
    So, if you can't run again your query, you could try to look at V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY, if you have access to these views. So before continuing in the suggestion of a solution to your concern, I would ask you if you have access to these views.
  • 6. Re: How to find last executed query ?
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    if have you got accesss then :
    SELECT   c.sql_text sqltext
        FROM v$session a
    
        join v$sqltext c
        on a.sql_address = c.address
    
       WHERE a.SID = :session_id 
    ORDER BY a.status, a.last_call_et
  • 7. Re: How to find last executed query ?
    879152 Newbie
    Currently Being Moderated
    Hi,
    I have access all privileges.
  • 8. Re: How to find last executed query ?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    like others already said, the only reliable way to capture all SQL from a certain user/session/module is SQL tracing, which has such serious drawbacks as very significant space consumption (in a few hours, your trace file can eat up several gigabytes of sace) and some performance overhead. Besides, you need access to the database server filesystem to retrieve the trace file, and you'll need to come up with some text parsing tools if you want automate trace file processing.

    It's much easier to retrieve this information directly from the database, e.g. from V$SQL, but here the problem is that retention time for a query in V$SQL is not guaranteed, and if the shared pool is sufficiently busy, the statement can get aged out seconds after the exectuion. If you have the Diagnostic Pack license, you can use AWR views for this purpose (DBA_HIST_SQLSTAT etc.) or ASH (V$ACTIVE_SESSION_HISTORY/DBA_HIST_ACTIVE_SESS_HISTORY), however, here the chances of missing a statement are even higher (becase only top N heaviest statements are captured, where N is given by AWR retention settings).

    So as you can see, this is not a simple task. If you think about it, it should be easy to understand why: Oracle databases are made to process thousands of SQL queries per second, and a large percentage of these queries is system-generated, so a situation when every single SQL would have to be captured are rare and unusual.

    In your case, I think if you explained us in a bit more detail what exactly you are trying to achieve, we could have come up with a better solution for you. Most likely what you need is proper code instrumentation (i.e. an application log table and some database code to populate it).

    Best regards,
    Nikolay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points