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!

Query to know what a specific session is doing takes so long

Discussion in 'General' started by alexcol, Aug 24, 2017.

  1. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good evening, I am new to oracle and i need your help

    Ive got a list of actvive session but i want to track down a secific sid what is doing but take more than an hour and ive got to cancel it with no resultes: ie

    this is an example of an active session:

    Code (SQL):
    11586      58197 ICONTABLE_SCL  oracle@proiuc01c (TNS V1-V3)   oracle                         ACTIVE  
     
    This is the query ive got and is takes so long and have no results

    Code (SQL):
    SELECT a.sid, a.serial#, b.sql_text
    FROM v$session a, v$sqlarea b
    WHERE a.sql_address=b.address
    AND a.username='ICONTABLE_SCL'
    AND a.sid= '11586';
     
    Is there any other query thant i can approach this much faster and show me the specific DML what is doing?

    I appreciate your help in advanced
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    What do you want to find and see?

    Correctly, use to AUDSID for identification by session.

    May be helpful:
    1) "If this query prints the result, then your session performs a transaction"
    Code (Text):

     SELECT b.start_time, a.inst_id, a.sid, a.username, a.program, b.used_urec, b.used_ublk
        FROM gv$session a, gv$transaction b
       WHERE a.saddr = b.ses_addr
         and a.inst_id = b.inst_id
    and  a.sid = 11586
       ORDER BY b.used_ublk DESC;
     
    2) current command on your session
    Code (Text):

    SELECT SUBSTR(x.USERNAME,1,15) USERNAME,
    SUBSTR(X.status,1,8) STATUS,
    SUBSTR(X.server,1,10) SERVER,
    SUBSTR(X.TYPE,1,10) TYPE,
    SUBSTR(X.event,1,20) "WAIT EVENT",
    DECODE(X.command,
           1,'Create Table',
           2,'Insert',
           3,'Select',
           6,'Update',
           7,'Delete',
           8,'Drop',
           9,'Create Index',
          10,'Drop Index',
          12,'Drop Table',
          17,'Grant',
          26,'Lock Table',
          42,'Alter Session',
          43,'Alter User',
          44,'Commit',
          45,'Rollback',
          X.command) COMMAND
    FROM v$session X, v$session_wait Z
    WHERE (X.sid = Z.sid)
    AND X.sid = 11586
    ORDER BY X.username;

     
    3) see on result from v$longops
    Code (Text):

    SELECT s.sid,
           s.serial#,
           s.machine,
           ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
           ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
           ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
    FROM   v$session s,
           v$session_longops sl
    WHERE  s.sid     = sl.sid
    and  s.sid = 11586
    AND    s.serial# = sl.serial#;

     
     
    Last edited: Aug 24, 2017
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SID is a NUMBER, not a string, so you're going through implicit conversion you don't need to execute; take the single quotes OFF of the SID value. Modifying your WHERE clause to match users and sids in my database I see:

    Code (SQL):
    BING @ quanghoo > SET timing ON
    BING @ quanghoo > SELECT a.sid, a.serial#, b.sql_text
      2  FROM v$session a, v$sqlarea b
      3  WHERE a.sql_address=b.address
      4  AND a.username='BING'
      5  AND a.sid= 8;

           SID    SERIAL#
    ---------- ----------
    SQL_TEXT
    --------------------------------------------------------------------------------
             8       9989
    SELECT a.sid, a.serial#, b.sql_text FROM v$session a, v$sqlarea b WHERE a.sql_ad
    dress=b.address AND a.username='BING' AND a.sid= 8


    Elapsed: 00:00:00.04
    BING @ quanghoo >
    Notice this too really no time at all to execute so there's nothing wrong with the query except for what I just told you about the correct data type for SID.

    You need to see what this query is doing; change the WHERE clause as I suggested and run it again, possibly with autotrace on (set autotrace on at the SQL> prompt). Post the results.