Solved

Unix Session Timed Out for an active sql query

Posted on 2013-02-04
6
474 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
  • 3
  • 3
6 Comments
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Sir, Its possible to send all qustion to you directly in EE.... or chat...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now