Home » Infrastructure » Unix » How to Find Out Session Consuming memory on HP-UX database server (Oracle 9i R2 on HP-UX B.11.23 U ia64)
How to Find Out Session Consuming memory on HP-UX database server [message #500237] Fri, 18 March 2011 09:41 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

I am stuckup while executing sqls on a HP-UX box

There isn't much I/O or CPU usage as such I doubt on memory issue

There are 3 databases instances on the server but they were executing fine till today early morning after which everything went worst. Every SQL is hanging.

The physcical reads and consistent gets in v$sess_io hasn't increased for sessions in last 40-45 minutes

The waits in v$session_wait are db_sequential_read or db_scattered_read and the seconds_in_wait is increasing continuously

How can I find which session is consuming most memory?

The options of "top" command aren't working on HP-UX as works on Linux
We have GLANCE on the on HP-UX box. But I don't know how I can find out the process (OS level or DB or anything) consuming most memory.

I have attached snapshot of the screen after executing GLANCE

Please suggest

Regards,
OraKaran

Re: How to Find Out Session Consuming memory on HP-UX database server [message #500239 is a reply to message #500237] Fri, 18 March 2011 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>Please suggest

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

what clues exist in any alert_SID.log files or OS messages file?
Re: How to Find Out Session Consuming memory on HP-UX database server [message #500242 is a reply to message #500239] Fri, 18 March 2011 10:03 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
BlackSwan

The alert.log file is not updated in last 1 hour for the database I monitoring

I shutdown it a hour back to confirm I am the only user on that database, But no use

following is the trace file of the session I am monitoring as can be seen from it I started the trace in between

SELECT UPPER(SUBSTR(GLOBAL_NAME, 1, (INSTR(GLOBAL_NAME, '.')) -1))
FROM
 GLOBAL_NAME


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    175      0.01       0.00          0          0          0           0
Fetch      175      0.01       0.01          0        525          0         175
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      350      0.02       0.01          0        525          0         175

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 8809     (recursive depth: 1)
********************************************************************************

SELECT EIT.ID, EIT.EIT_DATA_TYPE
FROM
 ENVIRONMENT_ITEM_TYPES EIT WHERE EIT.EIT_CODE = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    175      0.00       0.00          0          0          0           0
Fetch      175      0.01       0.00          0        525          0         175
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      350      0.01       0.00          0        525          0         175

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 8809     (recursive depth: 1)
********************************************************************************

SELECT ETM.*
FROM
 ENVIRONMENT_ITEMS ETM WHERE ETM.ETM_DATABASE = :B2 AND ETM.ETM_EIT_ID = :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    350      0.01       0.01          0          0          0           0
Fetch      350      0.00       0.00          0        875          0         175
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      700      0.01       0.01          0        875          0         175

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 8809     (recursive depth: 1)
********************************************************************************

SELECT KEY_VALUE
FROM
 KEYS


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     35      0.00       0.00          0          0          0           0
Fetch       35      0.00       0.00          0        560          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.00       0.00          0        560          0          35

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 10364     (recursive depth: 1)
********************************************************************************

SELECT OLU.OLU_USER_NAME USER_NAME,OLP.ID OLP_ID
FROM
 ONLINE_USERS OLU, ONLINE_APPS OLP WHERE SYSDATE BETWEEN
  OLU.OLU_START_DATETIME AND NVL(OLU.OLU_END_DATETIME,SYSDATE) AND
  OLP.OLP_OLG_ID = OLU.OLU_OLG_ID AND OLP.OLP_CODE IN ('HDQ', 'LI')


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch       17      0.01       0.00          0         17          0          17
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       17      0.01       0.00          0         17          0          17

Misses in library cache during parse: 0
Parsing user id: 8809     (recursive depth: 1)
********************************************************************************

SELECT ETM.ETM_INTEGER_VALUE
FROM
 ENVIRONMENT_ITEM_TYPES EIT, ENVIRONMENT_ITEMS ETM WHERE EIT.ID =
  ETM.ETM_EIT_ID AND EIT.EIT_CODE = :B2 AND ETM.ETM_DATABASE = :B1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     35      0.00       0.00          0          0          0           0
Fetch       35      0.00       0.00          0        210          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.00       0.00          0        210          0          35

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 8809     (recursive depth: 1)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    770      0.02       0.02          0          0          0           0
Fetch      787      0.03       0.02          0       2712          0         612
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1557      0.05       0.04          0       2712          0         612

Misses in library cache during parse: 0

    7  user  SQL statements in session.
    0  internal SQL statements in session.
    7  SQL statements in session.
********************************************************************************
Trace file: dmig1_ora_8358.trc
Trace file compatibility: 9.02.00
Sort options: default

       1  session in tracefile.
       7  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       7  SQL statements in trace file.
       6  unique SQL statements in trace file.
    1637  lines in trace file.


Also attached the trace file saved as txt

Regards,
OraKaran
Re: How to Find Out Session Consuming memory on HP-UX database server [message #500243 is a reply to message #500242] Fri, 18 March 2011 10:07 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Please find another screenshot

It shows memory usage is 99%. I am seeing this since last hour

Regards,
OraKaran
Re: How to Find Out Session Consuming memory on HP-UX database server [message #500244 is a reply to message #500242] Fri, 18 March 2011 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>The alert.log file is not updated in last 1 hour for the database I monitoring
Is disk FULL?

what clues exist in OS messages file?
Re: How to Find Out Session Consuming memory on HP-UX database server [message #500247 is a reply to message #500237] Fri, 18 March 2011 10:16 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello

Disk is not at all full

Where to check OS messages? I don't have Unix expert around

Regards,
OraKaran

[Updated on: Fri, 18 March 2011 10:17]

Report message to a moderator

Re: How to Find Out Session Consuming memory on HP-UX database server [message #500691 is a reply to message #500247] Tue, 22 March 2011 16:17 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
Your issue is probably resolved by now, but in future you can try a PS listing to see the fields you want. Using PS, you'll get all processes reported.

#Solaris
ps -o user,fname,vsz,rss,pmem,pcpu,pid,comm -u oracle


#HP-UX, no pmem
# ps -o user,comm,vsz,sz,pcpu,pid,comm -u oracle


Don't guess at what the figures represent for mem and % ulilization on multi-core machines.

man ps for details...


You'll be able to spot the hign mem users, but HP reports vsz, sz in pages as I recall. To get HP pagesize:
# e.g. 4096 means 4k pagesize
/usr/sbin/kmtune | grep vps_pagesize | awk '{printf "%-12.f\n", $2*1024}'




Re: How to Find Out Session Consuming memory on HP-UX database server [message #501256 is a reply to message #500237] Mon, 28 March 2011 04:24 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello Andrew

Many thanks for the reply

I tried using the command suggested by you

Interpreting the output is taking time

Initially the '-o' option gave me truble and I had to use
Quote:

export UNIX95=

before the command

Similarly 'ps aux' (and -aux) are troubling me. I assume it is because of the unix version I am using

I am trying to resolve it as well as trying to interpret the output of
Quote:

ps -o user,comm,vsz,sz,pcpu,pid,comm -u oracle


Thanks for your help

Regards,
OraKaran


Re: How to Find Out Session Consuming memory on HP-UX database server [message #501319 is a reply to message #501256] Mon, 28 March 2011 09:23 Go to previous messageGo to next message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
Yes, UNIX95 is required, but don't export it, as you only want to enable XPG4 behavior for that command (else you'll break existing code that's also sensitive to it like sed, grep, tr, etc.)
UNIX95= ps -o user,comm,vsz,sz,pcpu,pid,comm -u oracle
Do a 'man ps' for help on the columns, or just try someone else's existing script... See bottom of http://forums13.itrc.hp.com/service/forums/questionanswer.do?threadId=1351913#tdIdName23

[Updated on: Mon, 28 March 2011 11:29]

Report message to a moderator

Re: How to Find Out Session Consuming memory on HP-UX database server [message #502739 is a reply to message #501319] Fri, 08 April 2011 11:08 Go to previous message
andrew again
Messages: 2574
Registered: March 2000
Senior Member
this was a quick solution for me last week (Solaris):
# sort is on rss
/users/home/usr1> ps -o vsz,rss,pmem,pcpu,pid,comm -u oracle | sort -n +1 | tail -3
1229304 1207776  7.4  0.0  4298 oracleDEV1
1229360 1207872  7.4  0.0  1028 oracleDEV1
1231624 1210152  7.4  0.3  1007 oracleDEV1
/users/home/usr1>

Previous Topic: oracle Installation on AIX
Next Topic: shell script
Goto Forum:
  


Current Time: Thu Oct 02 07:08:27 CDT 2014

Total time taken to generate the page: 0.05301 seconds