| How to find out session consuming high resource in the past? [message #580820] |
Thu, 28 March 2013 06:51  |
 |
sysdba007
Messages: 28
Registered: November 2012
|
Junior Member |
|
|
Hello
How can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat
Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information
Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read
Or
Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
Thanks and Regards
sysdba007
|
|
|
|
|
|
| Re: How to find out session consuming high resource in the past? [message #581551 is a reply to message #580820] |
Mon, 08 April 2013 16:42  |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
USERNAME INSTANCE_NUMBER DAY CPU_SECONDS_USED
---------------- --------------- -------------------- -------------------
CDA_PV_RO_USER 2 2013-04-08 Monday 369.9
IPSOFT 1 2013-04-07 Sunday 425.5
SYSTEM 1 2013-04-08 Monday 434.3
IPSOFT 4 2013-04-07 Sunday 444.8
IPSOFT 2 2013-04-07 Sunday 448.4
SYS 3 2013-04-08 Monday 2153.2
SYS 2 2013-04-08 Monday 2410.1
SYS 4 2013-04-08 Monday 2452.4
SYS 1 2013-04-08 Monday 3078.6
CDA_RO_USER 4 2013-04-08 Monday 44854.0
CDA_RO_USER 3 2013-04-08 Monday 59412.8
CDA_RO_USER 2 2013-04-08 Monday 83376.2
SYS 4 2013-04-07 Sunday 140737859.4
SYS 3 2013-04-07 Sunday 140738714.5
CDA_RO_USER 1 2013-04-08 Monday 140793232.9
SYS 1 2013-04-07 Sunday 281478431.8
IPSOFT 3 2013-04-08 Monday 422202158.5
IPSOFT 3 2013-04-07 Sunday 422202344.0
SYS 2 2013-04-07 Sunday 422214541.7
CDA_RO_USER 4 2013-04-07 Sunday 422282306.8
CDA_RO_USER 3 2013-04-07 Sunday 985234215.8
CDA_RO_USER 1 2013-04-07 Sunday 1266714081.6
CDA_RO_USER 2 2013-04-07 Sunday 1548221496.4
ECSCDAP1P > list
1 select a.username,b.instance_number,to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAY",
2 sum(b.TM_DELTA_CPU_TIME)/1000000 cpu_seconds_used
3 from dba_users a, DBA_HIST_ACTIVE_SESS_HISTORY b,sys.wRM$_SNAPSHOT c
4 where b.user_id=a.user_id
5 and b.SNAP_ID >(select max(SNAP_ID)-48 from sys.wRM$_SNAPSHOT)
6 and c.snap_id=b.snap_id
7 and b.instance_number=c.instance_number
8 and c.begin_interval_time > trunc(sysdate-1)
9 group by a.username,b.instance_number,to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
10* order by 4
|
|
|
|
| Re: How to find out session consuming high resource in the past? [message #581552 is a reply to message #581551] |
Mon, 08 April 2013 16:49 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
My mistake, the above query was for CPU. The following for memory.
USERNAME INSTANCE_NUMBER DAY MEMORY_HIGHWATER
---------------- --------------- -------------------- -------------------
CDA_RO_USER 4 2013-04-08 Monday 46935040.0
CDA_RO_USER 3 2013-04-07 Sunday 47721472.0
CDA_RO_USER 1 2013-04-08 Monday 48311296.0
SYS 4 2013-04-07 Sunday 81128448.0
SYS 3 2013-04-08 Monday 81128448.0
SYS 3 2013-04-07 Sunday 81128448.0
SYSTEM 1 2013-04-08 Monday 85003264.0
SYS 4 2013-04-08 Monday 87436288.0
SYS 2 2013-04-08 Monday 111406080.0
SYS 2 2013-04-07 Sunday 111406080.0
SYS 1 2013-04-08 Monday 201845760.0
SYS 1 2013-04-07 Sunday 201845760.0
ECSCDAP1P > list
1 select a.username,b.instance_number,to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAY",
2 max(b.PGA_ALLOCATED) memory_highwater
3 from dba_users a, DBA_HIST_ACTIVE_SESS_HISTORY b,sys.wRM$_SNAPSHOT c
4 where b.user_id=a.user_id
5 and b.SNAP_ID >(select max(SNAP_ID)-48 from sys.wRM$_SNAPSHOT)
6 and c.snap_id=b.snap_id
7 and b.instance_number=c.instance_number
8 and c.begin_interval_time > trunc(sysdate-1)
9 group by a.username,b.instance_number,to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
10* order by 4
|
|
|
|
|
|
| Re: How to find out session consuming high resource in the past? [message #581886 is a reply to message #581883] |
Thu, 11 April 2013 10:38 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
It exists but you may not:
1/ Have right to see them
2/ Installed them
Anyway, to be allowed to use them you MUST have purchased the Performances and Tuning option.
SQL> @v
Version Oracle : 10.2.0.4.0
SQL> desc DBA_HIST_ACTIVE_SESS_HISTORY
Name Null? Type
----------------------------------------------------------------- -------- -------------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
SAMPLE_ID NUMBER
SAMPLE_TIME TIMESTAMP(3)
SESSION_ID NUMBER
SESSION_SERIAL# NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_PLAN_HASH_VALUE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
SQL_OPCODE NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
SERVICE_HASH NUMBER
SESSION_TYPE VARCHAR2(10)
SESSION_STATE VARCHAR2(7)
QC_SESSION_ID NUMBER
QC_INSTANCE_ID NUMBER
BLOCKING_SESSION NUMBER
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_SESSION_SERIAL# NUMBER
EVENT VARCHAR2(64)
EVENT_ID NUMBER
SEQ# NUMBER
P1TEXT VARCHAR2(64)
P1 NUMBER
P2TEXT VARCHAR2(64)
P2 NUMBER
P3TEXT VARCHAR2(64)
P3 NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
TIME_WAITED NUMBER
XID RAW(8)
CURRENT_OBJ# NUMBER
CURRENT_FILE# NUMBER
CURRENT_BLOCK# NUMBER
PROGRAM VARCHAR2(64)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)
FLAGS NUMBER
Regards
Michel
[Updated on: Thu, 11 April 2013 10:39] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
