Node Eviction due to heart beat fatal error

Questions relating to Oracle Real Application Clusters (RAC) and Clusterware.

Moderator: Tim...

Node Eviction due to heart beat fatal error

Postby Rijo Roy » Tue Aug 27, 2013 6:47 am

Dear Tim

I am in extreme pain to inform you that I am failing in my job in keeping my instances alive even after doing many changes suggested by ADDM reports.

On 4th August as suggested by ADDM,
* I have increased my sga_max_size to 14G from 10G and sga_target to 12G from 8G.

* Increased open_cursors to 2200 from 1500 and session_cached_cursors=75 from 50.

* Frequently used programs were pinned to the shared pool.

* Every Sunday we perform a machine restart as we expect high transaction on Mondays.

* Many poorly written SQL's were tuned as suggested by SQL Advisor and introduced the use of bind variables to avoid hard parsing.

Still every day the AWR, ASH & ADDM shows High contention of latches, High TCP Socket KGAS wait events along with cursor sharing is not happening. High Inter cluster waits. SGA is not adequate etc...

My hardware configuration is like this:
RHEL Itanium
RAM: 24G
Swap: 32G

RAC 2 node

Software: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

But changing the above DB parameters also helped my clients to achieve a record transaction which they havent seen till date. But yesterday it was not at all a high transaction day and we haven't performed the m/c restart. From morning onwards, High concurrency, TCP Socket waits along with contention of latches was shown in ADDM.

And the node evicted as usual at the busy business hour at 5:40pm due to heart beat fatal error.

When one of the nodes was evicting, the other node's alert log was showing this:

Mon Aug 26 17:06:46 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Aug 26 17:06:54 2013
WARNING: inbound connection timed out (ORA-3136)
Mon Aug 26 17:08:43 2013
WARNING: inbound connection timed out (ORA-3136)
(The above message was thrown every time one of the node starts evicting )
&

SMON: Parallel transaction recovery tried

&

Mon Aug 26 17:41:09 2013
PMON failed to acquire latch, see PMON dump
Mon Aug 26 17:42:16 2013
PMON failed to acquire latch, see PMON dump
(The above message was thrown every time when a node was evicting)

In crs alert log

[cssd(29120)]CRS-1612:node appdb1 (1) at 50% heartbeat fatal, eviction in 29.128 seconds
2013-08-26 17:51:56.439
[cssd(29120)]CRS-1612:node appdb1 (1) at 50% heartbeat fatal, eviction in 28.127 seconds
2013-08-26 17:51:56.440
[cssd(29120)]CRS-1607:CSSD evicting node appdb1. Details in /oracle/crs/product/1020/crs_1/log/appdb2/cssd/ocssd.log.


Please help me Tim, I really don't understand where I am going wrong.. :shock:

Hoping to hear from you soon.

Thanks & Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Tue Aug 27, 2013 7:50 am

Hi.

Thoughts:

1) You seem to be getting some advantage from the restart, but that doesn't make sense because by restarting the database you are emptying the shared pool and buffer cache, meaning all SQL will need to be reparsed when it comes to the server, which will add to your latch contention.

2) You mention RAC. Have you put any time into application partitioning? RAC is really good for READ-READ operations and not too bad at WRITE-READ operations, but pretty awful at WRITE-WRITE operations. By that I mean when both sides for the cluster are doing writes to the same tables. Why? Because they invariably starting pinging blocks back and forward over the interconnect. I've never seen a good implementation of RAC where application partitioning has not been implemented. By that I mean, try and focus each node on different aspects of work. You can do this using services with a preferred nodes. If the node goes down, it will still work on the other nodes, but while it is up, it will make sure the nodes aren't fighting with each other over resources.

3) A little googling on "High TCP Socket KGAS" suggests this is a problem with your network, or the TCP server your database is communicating with. Are you doing something like lots of emails with UTL_SMTP, connecting to a slow mail server? Either that, or you just don't have enough network bandwidth to the server? You really need to find out what sessions are causing this event and what work they are doing to cause it.

4) Cursor Sharing : This is typically down to not using bind variables. Adding more shared pool will not help this. It will just give you a massive shared pool full of unique statements. If you can't correct the code you should consider using the CURSOR_SHARING parameter to force bind variables. Be *careful* though. This can have a negative impact also. You have to test it properly.

5) Contention for latches : You don't say which ones, so I'm guessing this is library cache and down to to many hard parses again. Sort out the bind variables and things will get better.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Tue Aug 27, 2013 9:15 am

Dear Tim

I was closely comparing my AWR reports of yesterday with last weeks till 12th August. I found yesterday's Top 5 Timed Events as:

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
gc buffer busy 21,993 1,108 50 22.2 Cluster
gc cr block 2-way 2,612 833 319 16.7 Cluster
gc current grant busy 1,246 723 581 14.5 Cluster
latch: library cache 397 534 1,344 10.7 Concurrency
gc cr block lost 251 372 1,481 7.4 Cluster

--------------------------------------------------------------------------------------------------------

and in the rest of the days when we reached the record transactions

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
TCP Socket (KGAS) 18,270 1,688 92 49.6 Network
CPU time 1,271 37.4
db file sequential read 21,530 154 7 4.5 User I/O
db file parallel read 3,708 69 19 2.0 User I/O
db file scattered read 9,048 66 7 2.0 User I/O

-------------------------------------------------------

On 20th August, I performed a DB activity in which I performed RANGE partition on a table with 39465553 rows into 11 partitions.

On 23rd August, my DB was bounced again due to heart beat fatal error with all the messages that I told above and the AWR shows :

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 1,688 53.9
TCP Socket (KGAS) 6,564 857 131 27.4 Network
gc cr multi block request 1,480,341 157 0 5.0 Cluster
db file scattered read 77,048 147 2 4.7 User I/O
gc buffer busy 47,459 88 2 2.8 Cluster


Was that a mistake of making that big table into range partition? This table is very frequently used for DML operations.

If so, shall I go for Hash partitioning the same?

Thanks & Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Tue Aug 27, 2013 9:50 am

Hi.

Partitioning is not the simplest thing to discuss because the answer will nearly always be, "It depends!".

Questions:

1) How is the table typically used? If access is always via UK or PK, then the size of the table is not a big factor in access speed. Binary chops of indexes are very quick!

2) What are you hoping to improve by use of the partitioning? Performance or management?

3) If you want to improve performance, then partition pruning will only ever come into play if the partition key is in the predicates of the SQL being fired at the table. If not, then performance may be worse.

4) Have you done load testing using different partitioning schemes and different index partitioning schemes on those partitioned tables.

Remember, there is no one-size-fits-all solution. If it were that simple the database would do it automatically...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Tue Aug 27, 2013 10:42 am

Hi Tim

Thanks for the prompt response.

Answers

1. The table I am talking about is a very large table mainly used for bulk inserts and select.
Access is mainly via FK. Problem with the partition that I talked about is that it contributes for CONCURRENCY.

2. By the use of partition, I want to reduce the concurrency ultimately the performance..

3. Here I made the mistake :( , partitioning is done on sysdate column which is not the FK column which is used in the SQL statements..

4. I will do it asap.

Now I am asking a very generic question that to reduce the concurrency waits thereby improving the performance would you sugest for HASH PARTITIONING?..

Honestly speaking, you have opened my eyes to many ideas. I was not able to open up with a good start but your views to my posts helped me for a better start.

Thanks a lot Tim


Regards

Rijo :D
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Tue Aug 27, 2013 5:44 pm

Hi.

What are the concurrency issues you are seeing? Is it hot blocks that are being transferred over the interconnect? If so, then there are several things you can do to reduce this...

- Reverse key indexes reduce the likelyhood of consecutive rows being stored in the same block.
- Smaller block size reduces the number of rows stored in the same block.
- Your HASH partitioning may help here, but don't understimate the impact of having to calculate the HASH for every SQL that interacts with the table.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Wed Aug 28, 2013 9:24 am

Hi Tim

Thanks for the response.

Yes ADDM shows the highest wait event in the graph as Concurrency and when I look more into it. When I dig more into it, it shows latches and enq:row level contention etc..

ADDM report says: Individual data segments responsible for significant user I/O wait as well as hot blocks...

I got the below query to find out the hot blocks for the session and learnt that most of the tables have no Indexes and many are not analyzed and they are frequently accessed mainly for inserts and selects.. Please have a look at the below query

select /*+ rule */
de.owner, de.segment_name, de.segment_type,
ash.event,
sum(ash.time_waited) total_time,
count(*) count,
sum(ash.time_waited)/count(*) Avg_wait
from
V$ACTIVE_SESSION_HISTORY ash,
dba_extents de
where
ash.event like 'gc%' and
ash.P1TEXT='file#' and
ash.P2TEXT='block#' and
ash.p1=de.file_id and
ash.time_waited > 0 and
ash.p2 between de.block_id AND (de.block_id+de.blocks-1)
group by de.owner, de.segment_name, de.segment_type, ash.event
order by 5 desc;

Thanks & Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Wed Aug 28, 2013 10:05 am

Hi.

You can't use the times in ASH like that. They are cumulative, so you can't do stuff like SUM. I explain this here:

http://www.oracle-base.com/articles/10g ... istory.php

Time is represented by the number of samples.

v$active_session_history: Time in seconds is COUNT(*)
dba_hist_active_sess_history: Time in seconds is COUNT(*)*10

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Wed Aug 28, 2013 12:49 pm

Hi Tim

Thanks for sharing me the page.

The objective of that SQL that I shared with you is to find out the hot blocks. Now I have removed the aggregate functions as you told me but the modified query is taking a lot of time and even after waiting for 15 mins it is not returning any result then ultimately I terminate my query using "Ctrl+C".

Please help me in modifying the query.


SELECT de.owner, de.segment_name, NVL(a.event, 'ON CPU') AS event, count(*) count, a.time_waited
FROM v$active_session_history a,dba_extents de
WHERE a.event like 'gc%' and
a.P1TEXT='file#' and
a.P2TEXT='block#' and
a.p1=de.file_id and
a.time_waited > 0 and
a.p2 between de.block_id AND (de.block_id+de.blocks-1)
group by de.owner, de.segment_name, a.event, a.time_waited
order by 5 desc;


Thanking you in advance.

Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Wed Aug 28, 2013 1:55 pm

Hi.

I'm not really sure what you are trying to do here. The block information is in ASH, so you can just do this.

Code: Select all
SELECT o.owner,
       o.object_name,
       ash.current_file#,
       ash.current_block#,
       COUNT(*) AS seconds
FROM   v$active_session_history ash
       JOIN dba_objects o ON o.object_id = ash.current_obj#
WHERE  ash.sample_time > SYSTIMESTAMP - 1
GROUP BY o.owner, o.object_name, ash.current_file#, ash.current_block#
HAVING COUNT(*) > 10
ORDER BY 5;


The timestamp can be adjusted to suit you sample period and the HAVING clause can be amended to filter out rows, if your seconds for some events are really high.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Thu Aug 29, 2013 8:02 am

Hi Tim

Thanks for helping me.

My objective is to find which segment is a victim of the an wait event say gc buffer busy.

Today ADDM reported:
Finding: Global Cache Service Processes (LMSn) in other instances were not processing requests fast enough.
Recommendations
Action Increase throughput of the Global Cache Service (LMSn) processes. Increase the number of Global Cache Service processes by increasing the value of the parameter "gcs_server_processes". Alternatively, if the host is CPU bound consider increasing the OS priority of the Global Cache Service processes.
Rationale The value of parameter "gcs_server_processes" was "2" during the analysis period.

Inter-instance messaging was consuming significant database time on this instance.

As you suggested in your first reply to my post:

"2) You mention RAC. Have you put any time into application partitioning? RAC is really good for READ-READ operations and not too bad at WRITE-READ operations, but pretty awful at WRITE-WRITE operations. By that I mean when both sides for the cluster are doing writes to the same tables. Why? Because they invariably starting pinging blocks back and forward over the interconnect. I've never seen a good implementation of RAC where application partitioning has not been implemented. By that I mean, try and focus each node on different aspects of work. You can do this using services with a preferred nodes. If the node goes down, it will still work on the other nodes, but while it is up, it will make sure the nodes aren't fighting with each other over resources.

3) A little googling on "High TCP Socket KGAS" suggests this is a problem with your network, or the TCP server your database is communicating with. Are you doing something like lots of emails with UTL_SMTP, connecting to a slow mail server? Either that, or you just don't have enough network bandwidth to the server? You really need to find out what sessions are causing this event and what work they are doing to cause it."

I spoke with my network team to enquire about the network bandwidth issues. They do agree about bandwith problems at times but they are saying there are no TCP errors. As I don't understand much about network & interconnects, I would request you to help me.

Below is the o/p of netstat -s from my node 1
Ip:
2497415080 total packets received
354 with invalid addresses
0 forwarded
0 incoming packets discarded
1500870427 incoming packets delivered
3752676270 requests sent out
81 outgoing packets dropped
1210447814 reassemblies required
213903515 packets reassembled ok
175521524 fragments received ok
Icmp:
171523 ICMP messages received
39460 input ICMP message failed.
ICMP input histogram:
destination unreachable: 95818
timeout in transit: 5
echo requests: 75648
echo replies: 52
174135 ICMP messages sent
0 ICMP messages failed
ICMP output histogram:
destination unreachable: 98487
echo replies: 75648
Tcp:
297316 active connections openings
1051910 passive connection openings
39984 failed connection attempts
45225 connection resets received
679 connections established
1175066915 segments received
3469580239 segments send out
64993 segments retransmited
0 bad segments received.
230829 resets sent
Udp:
325525650 packets received
18801 packets to unknown port received.
496 packet receive errors
282922377 packets sent
TcpExt:
4 resets received for embryonic SYN_RECV sockets
4 packets pruned from receive queue because of socket buffer overrun
583310 TCP sockets finished time wait in fast timer
31 time wait sockets recycled by time stamp
47 packets rejects in established connections because of timestamp
975558 delayed acks sent
5648 delayed acks further delayed because of locked socket
Quick ack mode was activated 232 times
88276734 packets directly queued to recvmsg prequeue.
9436818 packets directly received from backlog
7251417368 packets directly received from prequeue
23599043 packets header predicted
53812788 packets header predicted and directly queued to user
136521643 acknowledgments not containing data received
992963885 predicted acknowledgments
52 times recovered from packet loss due to SACK data
Detected reordering 6 times using time stamp
15 congestion windows fully recovered
225 congestion windows partially recovered using Hoe heuristic
TCPDSACKUndo: 67
157 congestion windows recovered after partial ack
35 TCP data loss events
62 timeouts after SACK recovery
1 timeouts in loss state
115 fast retransmits
23 forward retransmits
518 retransmits in slow start
44800 other TCP timeouts
5 sack retransmits failed
2494 times receiver scheduled too late for direct processing
127 packets collapsed in receive queue due to low socket buffer
401 DSACKs sent for old packets
58331 DSACKs received
19872 connections reset due to unexpected data
11350 connections reset due to early user close
4823 connections aborted due to timeout

Could you tell me in seeing the above statistics whether there is an issue with my network?

ADDM is suggesting to increase the parameter "gcs_server_processes". Can you give me an idea of impact by increasing the same?

Thanks & Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Thu Aug 29, 2013 12:38 pm

Hi.

There are two approaches to improving the performance of the interconnect:

1) Make the interconnect faster. Like buying a faster interconnect such as infiniband. Or using Jumbo frames to reduce the impact of large block sizes over small frames. Your network guys should understand this point.

2) Reduce the load on the interconnect, so any interconnect performance issues are not such a big deal. This is where application partitioning comes in.

So, my first suggestion to you would be to identify distinct workloads in your system. Create a service for each of them, each with a preferred node, and try to partition your application, thereby reducing the traffic over the interconnect. As I said previously, I've never seen a successful implementation of RAC when people have ignored application partitioning.

I do not believe the gcs_server_processes setting is relevant. Look it the doc for this. The default should be adequate. Making it begger will make more processes available, which will all be trying to do coms over a slow network. That is never going to help.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby Rijo Roy » Mon Sep 16, 2013 1:46 pm

Hi Tim

As suggested, we have implemented Jumbo Frames in our network(interconnects) and we saw certain improvements till last friday. But today was a high business transaction as per the expectations and calculations made by my client. Today at around 5:30pm when the total sessions reached at around 1200 in node1 and 1349 in node 2 my node 2 got evicted by node 1 saying heart beat fatal error. Still I am seeing the spike in total sessions as well as the active sessions intermittently.

SQL> sho parameter process

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 3
db_writer_processes integer 1
gcs_server_processes integer 2
job_queue_processes integer 20
log_archive_max_processes integer 2
processes integer 2000

the parameter "sessions " is 2205.

Is there anything to do with these parameters with todays node eviction. Please guide me how to optimize these parameters..

Thanking you in advance.

Warm Regards

Rijo
Rijo Roy
Member
 
Posts: 35
Joined: Wed Apr 17, 2013 5:39 am

Re: Node Eviction due to heart beat fatal error

Postby Tim... » Mon Sep 16, 2013 6:50 pm

Hi.

A node eviction will probably not be caused by that. It is more likely that something else is the cause...

If the CPU usage stays consistently high for a long period of time, the clusterware can go screwy. Why? Because the clusterware relies on the network. Networking involves the CPU. If the CPU is being hammered, the network processing on the server may slow enough to make the cluster think the heartbeat is broken, hence node eviction. This is only one scenario of course.

Question:

1) With 1000+ sessions connecting to the database, are you using dedicated or shared server? I would have thought that many user processes would not be a great idea, so you should consider testing shared server, so see if the overall footprint of your connections is reduced.

2) What is actually connecting to your server? Are your applications using connection pooling? If so, how many connections do they spawn. If you check out Graham Wood's talk on connection pooling, you will see you shouldn't have more that 1-10 connections per core hitting the database. Beyond that, the performance tails off badly. Connection pooling is designed to reduce the footprint of connections to reduce wasted resource on your database servers.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17936
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Node Eviction due to heart beat fatal error

Postby caesardutta » Wed Oct 16, 2013 5:17 am

Dear Tim:

Thanks many times for helping my friend Rijo working with me.

As per your suggestions we have seen that there are no interconnect issue after implementation of "Jumbo frame" between the two DB instances. However, as
mentioned by you that N/w also depends upon CPU and high clogging of CPU by active waiting sessions will result in poor N/w performance and hence eviction
this is still happening on high transaction days.

Our observation - <1> Not high number of sessions per node but <2> High number of sessions with High number of Waiting Active Sessions (waiting for CPU) leads to heartbeat failure and thus Node Eviction.

But we are facing another peculiar issue after introduction of Jumbo Frame and this is unpredictable and has nothing to do with High Sessions (per node) or High number of Waiting Active Sessions. The problem is that we notice that either one of the two instance is not having any sessions. Say just 1 or 2 sessions
and ther other isnatnce is taking all the load (say 1010 sessions, 998 sessions or 1300 sessions etc). What we are doing is "SHUTDOWN ABORT" of the
instance with few sessions and then STARTUP. After waiting for few minutes we see that total number of sessions in the instance gradually increases and
eventually the session load gets balanced.

Please provide some insight which we can look into.

Thanks and best regards,

caesar

PS : SQL used for looking sessions
Code: Select all
select    count(sid) Total_session,
   count(case status when 'ACTIVE' THEN 1 ELSE NULL END) Active_session,
   count(case status when 'INACTIVE' THEN 1 ELSE NULL END ) Inactive_Session,
   inst_id
from gv$session
WHERE  USERNAME IN ( 'USER_1')
-- AND LOGON_TIME >= SYSDATE - INTERVAL '5' MINUTE
group by  inst_id order by 4

USER_1 is the schema setting used in data sources / connection pools in Oracle 10G Application Servers hosting the Java J2EE application
caesardutta
Senior Member
 
Posts: 50
Joined: Thu Jul 15, 2010 11:11 am

Next

Return to Oracle RAC and Clusterware

Who is online

Users browsing this forum: No registered users and 1 guest