11 Replies Latest reply: Dec 14, 2010 11:38 AM by 560551 RSS

    getting sql from v$sqlarea

    694352
      Hi
      I get the sql text of the session by using below query:

      SELECT s1.sql_text
      from v$sqlarea s1,v$session s2
      where s2.SID=...
      and
      s2.serial#=....



      However I notice that there are
      7237 rows.
      more than 200 select statements and updates, which is impossible.

      how can I get the exact sql of the session?

      Oracle 9i
        • 1. Re: getting sql from v$sqlarea
          OrionNet
          Hello,

          You can try using sql_id column to join of your session.

          Regards
          • 2. Re: getting sql from v$sqlarea
            694352
            there is no sql_id column in v$sqlarea.
            Can you give an example....
            • 3. Re: getting sql from v$sqlarea
              OrionNet
              Hello,
              select sql_id from v$sqlarea
              Regards
              • 4. Re: getting sql from v$sqlarea
                694352
                SQL> select sql_id from v$sqlarea;
                select sql_id from v$sqlarea
                *
                ERROR at line 1:
                ORA-00904: "SQL_ID": invalid identifier

                ????
                • 5. Re: getting sql from v$sqlarea
                  589087
                  there is no sql_id in 9i, it only appeared in 10g
                  you should join v$session.SQL_ADDRESS = v$sqlarea.ADDRESS
                  this will get you the currently executing SQL
                  • 6. Re: getting sql from v$sqlarea
                    589087
                    this will return the currently executing sql

                    select s.sid,sq.sql_text from v$session s, v$sqlarea sq
                    where s.SQL_ADDRESS = sq.ADDRESS and s.sql_hash_value=sq.hash_value
                    and s.sid=&SESSION_ID
                    /

                    this will get you all open cursors:

                    select s.sid,sq.sql_text
                    from v$session s, v$sqlarea sq, v$open_cursor oc
                    where s.sid=&SESSION_ID
                    and s.saddr = oc.saddr
                    and sq.address=oc.address and sq.hash_value=oc.hash_value
                    /
                    • 7. Re: getting sql from v$sqlarea
                      694352
                      Thanks Andrei

                      so this might be the query :


                      SELECT s1.sql_text
                      from v$sqlarea s1,v$session s2
                      where s2.SID=&sid
                      and
                      s2.serial#=&serial
                      and
                      s2.SQL_ADDRESS = s1.ADDRESS



                      I tried this
                      I have just seen two oracle top sessions in unix and try to view their sqls with above query , but unfortunately no rows selected.
                      I guess they were thrown out of the cache very quickly
                      • 8. Re: getting sql from v$sqlarea
                        589087
                        if the v$session.status='INACTIVE' then SQL_ADDRESS will be empty, meaning the execution has ended
                        in this case you can see which sql was the last executed
                        just join on v$session.prev_sql_addr instead of sql_address
                        • 9. Re: getting sql from v$sqlarea
                          694352
                          so this is the last query:




                          SELECT s1.sql_text
                          from v$sqlarea s1,v$session s2
                          where s2.SID=&sid
                          and
                          s2.serial#=&serial
                          and
                          s2.prev_sql_addr = s1.ADDRESS


                          I will try that, Thanks Andrei
                          • 10. Re: getting sql from v$sqlarea
                            Jonathan Lewis
                            Andrei Kübar wrote:
                            if the v$session.status='INACTIVE' then SQL_ADDRESS will be empty, meaning the execution has ended
                            in this case you can see which sql was the last executed
                            just join on v$session.prev_sql_addr instead of sql_address
                            It's better to join on the hash_value rather than the address (and include the address if you are are worried about two statements having the same hash_value) as the hash_value is a "pseudo-"indexed access path into the underlying x$ structure.

                            It's also better in 9i to join to v$sql, rather than v$sqlarea - although this will give you multiple copies when a statement has multiple child cursors - as v$sqlarea is an aggregate of the underlying x$ and may make it impossible for oracle to find an efficient method for getting the data.


                            (Things change in 10g - where the sql_id appears, the child_number gets including in extra views, and v$sqlstat comes into existence to substitute for v$sqlarea, and v$sqlarea is no longer an aggregate view anyway and operates on mutexes rather than latches).

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk

                            "Be very, very careful what you put into that head because you will never, ever get it out."
                            Cardinal Wolsey
                            • 11. Re: getting sql from v$sqlarea
                              560551
                              Hello
                              I have a question about this.
                              A user session have a lot of cursors opened... Like this:

                              USER_NAME SID # CURSORS PERCENTAGE
                              ------------------------------ ---------- ---------- ----------
                              USER 126 1091 83.92

                              Then with your query, I have the output of all the querys executed by this user..There are a lot of INSERT.
                              So my question is:
                              If the INSERT statment do not have a commit every n records...but just one at the end of the script, this can be the cause of all this opened cursors?
                              If we add in the script a commit every 1000 records for exemple, each commit will close the cursor opened by the insert statment?

                              Thanks for all the info.
                              Cheers