Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Unix Session Timed Out for an active sql query

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
Maddy
Asked:
Maddy
  • 3
  • 3
1 Solution
 
woolmilkporcCommented:
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
 
MaddyUNIXAuthor Commented:
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
 
MaddyUNIXAuthor Commented:
Sir, Its possible to send all qustion to you directly in EE.... or chat...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
woolmilkporcCommented:
>> 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
 
MaddyUNIXAuthor Commented:
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
 
woolmilkporcCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now