How to find out what is consuming all of my bandwidth

Some days ago we received a complain from the networking department stating that some connections from one of our database servers were consuming most of the available bandwidth.
Obviously, the highest chance was that it was an oracle process, more, because it was our server who was sending the data, and the only applicacion in this server is Oracle.

Mapping a process in a remote server to a oracle session

Something we have probably needed many times is to find in a remote server the program that is running a session against our database.
Perhaps and for any reason,  we need to know exactly which concrete process in the remote server is accessing our database (from the
operating system perspective).
Let's say is a sqlplus prompt, but when we log on the remote system, we see that there are many sqlplus processes runnning. Which
is the right one?

Who And What SQL Is Using Temp Segments ?

Recently I came across an incident related to temporary segments. When I checked the view V$SORT_USAGE to take a look at what was using my temporary tablespace, I got shocked to see a query of the type "select 1 from dual". Obviously, this query does not do any kind of sort!

So I decided to take a close look at the issue.

First thing I noticed:

ORA-00030, 00000, "user session ID does not exist"

I think it's worth to write a short entry in my blog regards to it. That happened to me some days ago. First time I met this issue.

We were told that a session was blocking another session and preventing it to run a DELETE on one table. Up to this point, everything is normal. I ran an ALTER SYSTEM KILL SESSION.. on the blocking session, and, to my surprise, appeared this message:

AWR Does not show where's the problem. Just shows what is happening!

It looks like something that does not need to be said, but is a trap where many junior DBA fall. AWR (or Statspack) can be misleading if we do not keep in mind this detail:

What AWR and Statspack report shows is not where or what is the problem, but just what was happenning in the database at the moment of the snapshot!.

That means, that we need to interpret correctly what we are seeing at the report. Here I'd like to share an example that illustrates this point.

Identifying memory pressure - Part 4

Continue from part 3...

MEMORY AVAILABLE

At this point, I want to introduce a new parameter: MemAvailable.

This parameter is available is included in /proc/meminfo since version 3.14. In Red Hat, can be backported to previous versions from kernel-2.6.32-504.el6. Just need to set the value of sysctl parameter vm.meminfo_legacy_layout to 0. Then check at /proc/meminfo the new parameter MemAvailable.

Identifying memory pressure - Part 3

Continue from part 2....

AND WHAT DO WE DO WITH ALL THIS?

From the point of view of forecasting, (that is, if I want to know the amount of memory I have to put on one server, or if the actual memory of a server will be enough for the current workload), I have taken this approach:

Identifying memory pressure - Part 2

Continue from part 1....

OK, ALL THIS STUFF LOOKS GREAT, BUT HOW COULD I ESTIMATE THE SIZE OF A PROCESS IN MEMORY?

Taking into account what have been told, the Resident Set Size is the closest to the size in memory of a process, but when checking it by means of top, ps, or another Unix tool we find a problem. The output of those commands takes into account the shared memory segments and the shared libraries, so the results are oversized for double counting the sizes.

Identifying memory pressure - Part 1

ALL WHAT COMES NEXT HAVE BEEN TESTED ON RED HAT LINUX V 6. MOST OF IT SHOULD BE APPLIABLE TO ANOTHER UNIX OR LINUX SYSTEMS

THINGS CAN CHANGE DEPENDING ON MANY FACTORS (NUMA, Huge Pages, etc), BUT WHAT COMES NEXT SGOULD BE EASILY ADAPTABLE.

In the beginning, my idea was to check the amount of physical memory used by Oracle processesin order to do some forecasting and capacity planning.

Unfortunately, I've seen that is very difficult to calculate the exact amount of memory used by Oracle due to many reasons: