Home » RDBMS Server » Server Administration » Session Inactive but running queries (Oracle 10.2.0.4 , Solaris 10)
Session Inactive but running queries [message #445827] Thu, 04 March 2010 04:52 Go to next message
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
Member
Hi Experts,
I need your suggestions in debugging the issue on my database.
I am using 10.2.0.4 oracle database on Solaris 10. We are running some Peoplesoft upgrade on one of the database from last 6-7 hours. The status of the session is INACTIVE from couple of hours. And the sql_id is null for these sessions.

select sid,serial#,STATUS,to_char(logon_time,'DD-MON_YY   HH24:MI:SS') from v$session where username like '%&usern%' ORDER BY 4 DESC;
       533       2234 INACTIVE     04-MAR_10   01:59:02
       525       5919 ACTIVE       04-MAR_10   01:54:50
       534       4186 INACTIVE     04-MAR_10   01:05:15
       520       2320 INACTIVE     04-MAR_10   00:29:44
       511       2535 INACTIVE     03-MAR_10   14:23:04
      1083       5562 INACTIVE     03-MAR_10   14:18:45
       513       2231 INACTIVE     03-MAR_10   14:15:16
      1091       6881 INACTIVE     03-MAR_10   14:11:28
      1070       5521 INACTIVE     03-MAR_10   13:42:22


While when i see the current sql from the v$active_session_history, it keep on changing after every 10-15 minutes. Using below query to find the sql_text for the session from v$active_session_history.
select sql_id, sql_text 
from v$sql 
 where sql_id in (
           select sql_id 
           from v$active_session_history 
           where session_id=511
           and sample_time=
                 (select max(sample_time) 
                   from   v$active_session_history 
                   where session_id=&SID 
                 )
           );
Enter value for sid:511
d5jybz8k3sk17
SELECT B.COUNTRY , B.STATE FROM PS_BUS_UNIT_TBL_BI A , ..


While active transactions shows that there are running transactions of these sessions (511 and 1070).

select sid, username, terminal, osuser,
        t.start_time, r.name, t.used_ublk "ROLLB BLKS",
        decode(t.space, 'YES', 'SPACE TX',
           decode(t.recursive, 'YES', 'RECURSIVE TX',
              decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
        )) status
 from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
 where t.xidusn = r.usn
   and t.ses_addr = s.saddr;

/
       511 EP75     pts/5                          fs89dmo  03/03/10 18:00:54 _SYSSMU1
                                                                              0$

      1070 EP75     pts/5                          fs89dmo  03/04/10 02:05:56 _SYSSMU9
                                                                              $


We have not faced issue like this during Peoplesoft upgrade.
Can you please help me out what could be the reason that the status is not changing while the base sqls keep on changing after every 10 minutes.

Regards
Lalit

[Updated on: Thu, 04 March 2010 04:58]

Report message to a moderator

Re: Session Inactive but running queries [message #445829 is a reply to message #445827] Thu, 04 March 2010 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ACTIVE means you are CURRENTLY executing Oracle code.
Between SQL you are INACTIVE.
Have a look at v$session.LAST_CALL_ET.

Regards
Michel
Re: Session Inactive but running queries [message #445830 is a reply to message #445827] Thu, 04 March 2010 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure your queries to check what the sessions are currently doing are actually correct?

I use the following to see what all sessions are currently running (courtesy of Tom Kyte):
column username format a20
column sql_text format a55 word_wrapped
 
set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et ) loop
         
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null)) sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4) loop
                      
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%') then
                 
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
/
Re: Session Inactive but running queries [message #445834 is a reply to message #445827] Thu, 04 March 2010 05:31 Go to previous messageGo to next message
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
Member
Hi Michael/cookiemonster,
Thanks for the prompt reply.
The value of v$session.LAST_CALL_ET is 0.

-Cookiemonster
Thanks for the query. I ran the query and the output came:
EP75(1070,5521) ospid = 6202 program = psae@corpdevapp11 (TNS V1-V3)
Wednesday 13:42  Thursday  03:30 last et = 0
INSERT INTO PS_ITEM_LINES_TBL (SETID, ENTRY_TYPE, EFFDT, ENTRY_REASON, DST_SEQ_NUM, DST_LINE_PERCENT, DEBIT_CREDIT,
ACCOUNT, ALTACCT, DEPTID, OPERATING_UNIT, PRODUCT, FUND_CODE, CLASS_FLD, PROGRAM_CODE, BUDGET_REF, AFFILIATE,
AFFILIATE_INTRA1, AFFILI
--------------------
EP75(533,2234) ospid = 4840:5720 program = sqlplus.exe
Thursday  01:59  Thursday  03:30 last et = 0
SELECT USERNAME||'('||SID||','||SERIAL#|| ') ospid = ' || PROCESS || ' program = ' || PROGRAM USERNAME,
TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME, TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME, SQL_ADDRESS, LAST_CALL_ET
FROM V$SESSION WHERE STATUS



Regards
Lalit
Re: Session Inactive but running queries [message #445841 is a reply to message #445827] Thu, 04 March 2010 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the 2nd bit is the script I gave you, so looks like it's doing an insert at this point.
Re: Session Inactive but running queries [message #445847 is a reply to message #445827] Thu, 04 March 2010 06:20 Go to previous messageGo to next message
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
Member
Ya Cookiemonster,
The query keep on changing, but the status of the session 511 is still INACTIVE and of 1070 now changed to ACTIVE after being inactive for couple of hours.
The current sql is now(from the query you gave):
EP75(1070,5521) ospid = 6202 program = psae@corpdevapp11 (TNS V1-V3)
Wednesday 13:42  Thursday  04:16 last et = 2
UPDATE PS_PENDING_ITEM SET STATE_SHIP_FROM = ( SELECT V.STATE FROM PS_CUST_ADDRESS V WHERE V.SETID = ( SELECT SETID FROM
PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PS_PENDING_ITEM.BUSINESS_UNIT AND RECNAME = 'CUST_ADDRESS') AND V.CUST_ID =
PS_PENDING_ITE



Regards
Lalit
Re: Session Inactive but running queries [message #445849 is a reply to message #445847] Thu, 04 March 2010 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
T. Kyte query only returns the ACTIVE sessions maybe not what you are searching for.

Regards
Michel
Re: Session Inactive but running queries [message #445861 is a reply to message #445827] Thu, 04 March 2010 07:16 Go to previous messageGo to next message
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
Member
Yes Michel,
Thanks for correcting me. Thats why it is not returning the sql query executed by session 511.
But i can get that sql by v$active_session_history.
Michel, Can you please guid me why the session shown as inactive while the queries executing/changing in the base.
I used to face this kind of issue while we are updating big table (nearly 60-70G) and the session gone to INACTIVE and come back to ACTIVE after hours. But the SQL is same during that period.
But here the base sql keep on changing. Sad
Re: Session Inactive but running queries [message #445868 is a reply to message #445861] Thu, 04 March 2010 07:51 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The status is set to ACTIVE when Oracle is during a call, a piece of execution of SQL and INACTIVE between.

Regards
Michel
Previous Topic: Database Sizing
Next Topic: Parallel
Goto Forum:
  


Current Time: Tue Sep 27 12:44:47 CDT 2016

Total time taken to generate the page: 0.11516 seconds