| How to Find Out Session Consuming memory on HP-UX database server [message #500237] |
Fri, 18 March 2011 09:41  |
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 #500242 is a reply to message #500239] |
Fri, 18 March 2011 10:03  |
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 #500691 is a reply to message #500247] |
Tue, 22 March 2011 16:17 |
andrew again
Messages: 2577
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 |
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 |
andrew again
Messages: 2577
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 [Updated on: Mon, 28 March 2011 11:29] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
