Solved

Unix Session Timed Out for an active sql query

Posted on 2013-02-04
6
482 Views
Last Modified: 2013-02-10
Hi Experts,

Good Morning !

I have Redhat Linux VM hosting Oracle DB... One of my database engineer try to connect Putty connection manager, and while sql query active the putty session becomes inactive..

what is the solution.

sk..
0
Comment
Question by:sivakumar_experts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
ID: 38850231
Hi sivaji,

Good Morning and a Happy New Year!

You should configure PuTTY to send keepalive packets.

PuTTY Configuration -> Connection -> "Sending null packets to keep session active" -> "Seconds between keepalive".

Specify a value > 0 (e.g. 15)

Reduce this value if there are still timeouts, decrease it if you're experiencing too much traffic (unlikely, except with very slow WAN connections).

wmp
0
 

Author Comment

by:sivakumar_experts
ID: 38850265
Hello Sir,

It’s my pleasure to meet your here....It has been long time sir….

Very good Morning And Happy New year to your and family....

Sir, thank you for the solution....However, can you help me with below to sir…

http://www.experts-exchange.com/OS/Linux/Q_28011216.html

Kind Regards,

SK
0
 

Author Comment

by:sivakumar_experts
ID: 38850282
Sir, Its possible to send all qustion to you directly in EE.... or chat...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 68

Expert Comment

by:woolmilkporc
ID: 38850317
>> It has been long time <<

You should simply ask more AIX questions. I'm always here to help with that.

As for the other question - the answers the participating experts gave seem rather reasonable.

All I can do is give you an SQLPLUS query to check Oracle's memory usage (see below).
Have a close look at "DB Buffer Cache". Thats what Oracle uses all its allowed memory for which is not assigned to other SGA areas.

And No, EE doesn't provide a means to contact people directly, and there is no chat facility either.

wmp
set echo off
set feedback off
set linesize 512
set pagesize 24
set newpage 1
clear screen

PROMPT ================================================================================ ;
PROMPT =  SGA-Informationen ;
PROMPT ================================================================================ ;

COLUMN dummy      noprint
COLUMN area                     format a20 heading 'Main SGA Areas'
COLUMN name                     format a20
COLUMN pool                     format a20
COLUMN bytes                    format 999,999,999,999
COLUMN sum(bytes) LABEL "Sum"   format 999,999,999,999

break on report

compute sum label "Sum" of sum(bytes) on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)
FROM gv$sgastat
WHERE pool is null and
      name = 'buffer_cache'
GROUP BY name
UNION ALL
SELECT 2, 'Shared Pool', pool, sum(bytes) 
FROM gv$sgastat
WHERE pool = 'shared pool'
GROUP BY pool
UNION ALL
SELECT 3, 'Large Pool', pool, sum(bytes) 
FROM gv$sgastat
WHERE pool = 'large pool'
GROUP BY pool
UNION ALL
SELECT 4, 'Java Pool', pool, sum(bytes) 
FROM gv$sgastat
WHERE pool = 'java pool'
GROUP BY pool
UNION ALL
SELECT 5, 'Redo Log Buffer', name, sum(bytes)
FROM gv$sgastat
WHERE pool is null and
      name = 'log_buffer'
GROUP BY name
UNION ALL
SELECT 6, 'Fixed SGA', name, sum(bytes)
FROM gv$sgastat
WHERE pool is null and
      name = 'fixed_sga'
GROUP BY name
ORDER BY 4 desc;
COLUMN area       format a20 heading 'Shared Pool Areas'

PROMPT ;
PROMPT SGA Memory Map (shared pool) ;
PROMPT ;

SELECT 'Shared Pool' area, name, sum(bytes)
FROM gv$sgastat
WHERE pool = 'shared pool' and
      name in ('library cache','dictionary cache','free memory','sql area')
GROUP BY name
UNION ALL
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes) 
FROM gv$sgastat
WHERE pool = 'shared pool' and
      name not in ('library cache','dictionary cache','free memory','sql area')
GROUP BY pool
ORDER BY 3 desc;
SELECT
 100 * (1-(a.value / (b.value + c.value)))
 "Buffer Cache Hit Ratio %"
 FROM v$sysstat a, v$sysstat b, v$sysstat c
 WHERE a.name = 'physical reads'
 AND b.name = 'db block gets'
 AND c.name = 'consistent gets';

Open in new window

0
 

Author Comment

by:sivakumar_experts
ID: 38850687
Sir after running the above SQL statement, I got below !


Main SGA Areas       NAME                 SUM(BYTES)
-------------------- -------------------- ----------
DB Buffer Cache      buffer_cache         1644167168
Shared Pool          shared pool          1476484432
Large Pool           large pool             16777216
Java Pool            java pool              16777216
Redo Log Buffer      log_buffer              7352320
Fixed SGA            fixed_sga               2222768
                                          ----------
                                          3163781120



And

Shared Pool Areas    NAME                 SUM(BYTES)
-------------------- -------------------- ----------
Shared Pool          miscellaneous        1190441576
Shared Pool          free memory           286042856
                                          ----------
Sum                                       1476484432

AND

Buffer Cache Hit Ratio %
------------------------
              88.4680418




> free -m
             total       used       free     shared    buffers     cached
Mem:        128860     128434        426          0        555     108775
-/+ buffers/cache:      19103     109757
Swap:        16383      16250        133




What you can find..?? Its OS issue or Oracle DB issue..

Thanks,
Sk
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 38850758
Well,

do you have an SGA Target of just 3 GB, or is the DB very poorly utilized? I don't assume so, looking at the low Buffer Cache Hit Ratio.

You have a 128 GB box, and just 3 GB SGA - why that?
Are there lots of other databases/applications?

On the other hand you have those 128 GB of main memory backed by just 16 GB of swap. This is not a very uncommon setup, but with such a setup there is also no reason to wonder at a high swap utilization.

Roundabout 108 GB of your memory are used for cache, an area which holds data that might be needed again in the future, but an area that can be used for other purposes by the OS at any time.

Now it seems that there must have been a (possibly short) peak in memory utilization, maybe due to reading of a really large file or the like. This lead to an increase in OS cache utilization and to consumption of swap space.
This peak is probably long gone, but the data remain in cache and swap - just because they might be needed again in the future. This is a pure OS thing and is not directly related to Oracle.

As I said, this is no reason to get worried, but if you don't like to see such a full swap area you can try to drop the caches, which might free up swap space as well.

There is a tunable "drop_caches" and you can free the pagecache this way:

sync ; sync
echo 1 > /proc/sys/vm/drop_caches

This operation is non-destructive, because dirty (in use) pages will not be dropped.


Reagardless of what you can or can't achieve with the above - consider increasing the SGA target of the DB. Your "Buffer Cache Hit Ratio" doesn't really look good.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question