Solved

Unix Session Timed Out for an active sql query

Posted on 2013-02-04
6
483 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:Maddy
[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:Maddy
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:Maddy
ID: 38850282
Sir, Its possible to send all qustion to you directly in EE.... or chat...
0
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
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:Maddy
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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Fine Tune your automatic Updates for Ubuntu / Debian
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses

623 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