Home » RDBMS Server » Performance Tuning » sessions were hung for long time (oracle 10.2.0.3 solaris 10 64bit)
icon8.gif  sessions were hung for long time [message #326848] Thu, 12 June 2008 18:28 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

i got one problem. in one of my DB 6 session ware hung form long time and they are active for 20 hours. i am not able to find what is going on. i check for the quries running by the session id. quires are working fine i was check execution plan too. no problem with that quires. i am able to see all the session are running same query.

what else i have to look to fix this problem. and any one tell me some suggestions.

[Updated on: Thu, 12 June 2008 18:33]

Report message to a moderator

Re: sessions were hung for long time [message #326849 is a reply to message #326848] Thu, 12 June 2008 18:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

please do NOT describe in words what you think is happening!

use sqlplus along with CUT & PASTE so we can see exactly the details upon which you are basing your conclusions.

>quires are working fine i was check execution plan too. no problem with that quires
Based upon statement above, no problem exists & nothing further to consider.

Report message to a moderator

Re: sessions were hung for long time [message #326852 is a reply to message #326849] Thu, 12 June 2008 19:12 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
i am very sorry my friend.

let me explain. i am facing one problem with 5 session in my database. these 5 session are running same query. please find session information. and please find query details in the attached file which is running by each session id.
these 5 session i found in the awr report and OEM. i found that "read by other session" wait event.
select s.sid,s.STATUS,s.LOGON_TIME from v$session s where s.sid in(353,1444,493,927,1638,465);

353 ACTIVE 6/12/2008 3:39:50 AM 
465 ACTIVE 6/12/2008 2:27:33 AM 
1638 ACTIVE 6/12/2008 2:36:55 AM 
927 ACTIVE 6/12/2008 2:26:49 AM 
493 ACTIVE 6/12/2008 2:49:37 AM 
1444 ACTIVE 6/12/2008 2:23:42 AM

Report message to a moderator

Re: sessions were hung for long time [message #326853 is a reply to message #326848] Thu, 12 June 2008 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK, I see what you see.
Are you sure the clients associated with these sessions are still connected to the database?
SELECT statements never block any other session.
So specifically, what quantifiable adverse impact is resulting from them?
I suspect they are totally benign.

SELECT * from V$SESS_IO where s.sid in(353,1444,493,927,1638,465);

If you run SELECT above a couple of times you see whether or not these sessions are doing any active IO (counts increasing).
I expect the results to be constant.
If so, these session can be ignored.

Report message to a moderator

Re: sessions were hung for long time [message #326861 is a reply to message #326853] Thu, 12 June 2008 21:31 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
hi,

Thanks for your valid suggesions. i found that session is doing I/O activity. please find the detials.

SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
       353         69        [B]35367169[/B]        9750808            78                  8
       465         15        [B]35356676[/B]        3515972            18                  0
       493         15        [B]35360709[/B]        4883970            13                  0
       927         15        [B]35356540[/B]        4192706            33                  2
      1444         68        [B]35356483[/B]        7694286           158                  5
      1638         74        [B]35361193[/B]        3842340           160                  3
02:20:06 :SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
       353         69        35376598        9753152            78                  8
       465         15        35366105        3516762            18                  0
       493         15        35370138        4885179            13                  0
       927         15        35365969        4193727            33                  2
      1444         68        35365912        7696580           158                  5
      1638         74        35370616        3843368           160                  3
02:20:20 :SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
       353         69        35389844        9756585            78                  8
       465         15        35379351        3517885            18                  0
       493         15        35383384        4886964            13                  0
       927         15        35379218        4195156            33                  2
      1444         68        35379161        7699964           158                  5
      1638         74        35383865        3844781           160                  3
02:20:35 :SYSTEM> /

       SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES
---------- ---------- --------------- -------------- ------------- ------------------
       353         69        [B]35536481[/B]        9794601            78                  8
       465         15        [B]35525988[/B]        3530788            18                  0
       493         15        [B]35530021[/B]        4907050            13                  0
       927         15        [B]35525852[/B]        4211482            33                  2
      1444         68        [B]35525795[/B]        7737017           158                  5
      1638         74       [B]35530499[/B]         3860322           160                  3
02:24:11 :SYSTEM>


means sessions are active. i was observed in OEM there are two wait events are high.
1. active session waiting :user I/O
2. read by other session are pretty high.

how do i get more clarification that sessions doing performance degradation and how do i fix this problem.

[Updated on: Wed, 16 March 2011 12:40] by Moderator

Report message to a moderator

Re: sessions were hung for long time [message #326879 is a reply to message #326848] Thu, 12 June 2008 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>means sessions are active
Yes, sessions are active & processing SQL and are NOT "hung".
The SQL previously attached in file is malformed & incomplete.
I state this because as far as I know, "FROM" does not exist in posted file.

Are statistics current for all objects involved with these SQL?
What in EXPLAIN_PLAN for these SQL.

Report message to a moderator

Re: sessions were hung for long time [message #326893 is a reply to message #326879] Fri, 13 June 2008 00:17 Go to previous message
skumar.dba
Messages: 82
Registered: October 2007
Member
Thanks for response. please find the explain plan for this statements. could you please suggest me where my db performance is down. all these explain plans are taking index scans.
:SYSTEM> select * from table(dbms_xplan.display('plan_table','353ses','all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2251080190

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                       |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|*  2 |   COUNT STOPKEY                      |                  |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| TXN              |     5 |  2255 |    12   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | TXN_SID_ITEM_IDX |    47 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TXN@SEL$1
   4 - SEL$1 / TXN@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=500)
   3 - filter("DATE_CREATED">SYSDATE@!-90 AND "TXN"."PRIMARY_CARRIER_NAME"='Verizon')
   4 - access("TXN"."SID"='310004402029071')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

=============================================================
BMVZWPRD:SYSTEM>  select * from table(dbms_xplan.display('plan_table','493ses','all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2251080190

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                       |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|*  2 |   COUNT STOPKEY                      |                  |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| TXN              |     5 |  2255 |    12   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | TXN_SID_ITEM_IDX |    47 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TXN@SEL$1
   4 - SEL$1 / TXN@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=500)
   3 - filter("DATE_CREATED">SYSDATE@!-90 AND "TXN"."PRIMARY_CARRIER_NAME"='Verizon')
   4 - access("TXN"."SID"='310008654669917')

Column Projection Information (identified by operation id):
-----------------------------------------------------------
============================================================
BMVZWPRD:SYSTEM> select * from table (dbms_xplan.display('plan_table','465ses','all'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2251080190

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|   1 |  SORT ORDER BY                       |                  |     5 |  2255 |    13   (8)| 00:00:01 |       |       |
|*  2 |   COUNT STOPKEY                      |                  |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| TXN              |     5 |  2255 |    12   (0)| 00:00:01 | ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN                 | TXN_SID_ITEM_IDX |    47 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / TXN@SEL$1
   4 - SEL$1 / TXN@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=500)
   3 - filter("DATE_CREATED">SYSDATE@!-90 AND "TXN"."PRIMARY_CARRIER_NAME"='Verizon')
   4 - access("TXN"."SID"='310003012825722')

Column Projection Information (identified by operation id):
-----------------------------------------------------------
.

[Updated on: Wed, 16 March 2011 12:40] by Moderator

Report message to a moderator

Previous Topic: diskreads/executions
Next Topic: delete statement is causing lot of performance hurt in DB
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 21:02:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.