Home » RDBMS Server » Performance Tuning » How to find out session consuming high resource in the past? (Oracle 10.2.0.3.0 AIX)
How to find out session consuming high resource in the past? [message #580820] Thu, 28 March 2013 06:51 Go to next message
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 #580839 is a reply to message #580820] Thu, 28 March 2013 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 21936
Registered: January 2009
Senior Member
post SQL & results that show SQL having high memory consumption in the present as a starting point.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #581883 is a reply to message #581552] Thu, 11 April 2013 10:15 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello alan

In both the queries columns mentioned in DBA_HIST_ACTIVE_SESS_HISTORY view do not exist
At least in 10g!
Though I have not tried in 11g considering the view name it does not look like PGA_ALLOCATED would fall under DBA_HIST_ACTIVE_SESS_HISTORY

Could you please suggest

Regards
sysdba007
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57592
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: How to find out session consuming high resource in the past? [message #581887 is a reply to message #580820] Thu, 11 April 2013 10:47 Go to previous messageGo to next message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Hello Michel

I might be making mistake but do you want to say that "PGA_ALLOCATED" and "TM_DELTA_CPU_TIME" exists in DBA_HIST_ACTIVE_SESS_HISTORY?

Regards
sysdba007
Re: How to find out session consuming high resource in the past? [message #581888 is a reply to message #581887] Thu, 11 April 2013 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57592
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! you were talking about the columns... I thought the view... sorry.
They indeed are there only in 11g:
mikb2=MIKB2/ILLUVATAR MICHEL> desc DBA_HIST_ACTIVE_SESS_HISTORY 
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SNAP_ID                          NOT NULL NUMBER
 DBID                             NOT NULL NUMBER
 INSTANCE_NUMBER                  NOT NULL NUMBER
 SAMPLE_ID                        NOT NULL NUMBER
 SAMPLE_TIME                      NOT NULL TIMESTAMP(3)
 SESSION_ID                       NOT NULL NUMBER
 SESSION_SERIAL#                           NUMBER
 SESSION_TYPE                              VARCHAR2(10)
 FLAGS                                     NUMBER
 USER_ID                                   NUMBER
 SQL_ID                                    VARCHAR2(13)
 IS_SQLID_CURRENT                          VARCHAR2(1)
 SQL_CHILD_NUMBER                          NUMBER
 SQL_OPCODE                                NUMBER
 SQL_OPNAME                                VARCHAR2(64)
 FORCE_MATCHING_SIGNATURE                  NUMBER
 TOP_LEVEL_SQL_ID                          VARCHAR2(13)
 TOP_LEVEL_SQL_OPCODE                      NUMBER
 SQL_PLAN_HASH_VALUE                       NUMBER
 SQL_PLAN_LINE_ID                          NUMBER
 SQL_PLAN_OPERATION                        VARCHAR2(64)
 SQL_PLAN_OPTIONS                          VARCHAR2(64)
 SQL_EXEC_ID                               NUMBER
 SQL_EXEC_START                            DATE
 PLSQL_ENTRY_OBJECT_ID                     NUMBER
 PLSQL_ENTRY_SUBPROGRAM_ID                 NUMBER
 PLSQL_OBJECT_ID                           NUMBER
 PLSQL_SUBPROGRAM_ID                       NUMBER
 QC_INSTANCE_ID                            NUMBER
 QC_SESSION_ID                             NUMBER
 QC_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
 SESSION_STATE                             VARCHAR2(7)
 TIME_WAITED                               NUMBER
 BLOCKING_SESSION_STATUS                   VARCHAR2(11)
 BLOCKING_SESSION                          NUMBER
 BLOCKING_SESSION_SERIAL#                  NUMBER
 BLOCKING_INST_ID                          NUMBER
 BLOCKING_HANGCHAIN_INFO                   VARCHAR2(1)
 CURRENT_OBJ#                              NUMBER
 CURRENT_FILE#                             NUMBER
 CURRENT_BLOCK#                            NUMBER
 CURRENT_ROW#                              NUMBER
 TOP_LEVEL_CALL#                           NUMBER
 TOP_LEVEL_CALL_NAME                       VARCHAR2(64)
 CONSUMER_GROUP_ID                         NUMBER
 XID                                       RAW(8)
 REMOTE_INSTANCE#                          NUMBER
 TIME_MODEL                                NUMBER
 IN_CONNECTION_MGMT                        VARCHAR2(1)
 IN_PARSE                                  VARCHAR2(1)
 IN_HARD_PARSE                             VARCHAR2(1)
 IN_SQL_EXECUTION                          VARCHAR2(1)
 IN_PLSQL_EXECUTION                        VARCHAR2(1)
 IN_PLSQL_RPC                              VARCHAR2(1)
 IN_PLSQL_COMPILATION                      VARCHAR2(1)
 IN_JAVA_EXECUTION                         VARCHAR2(1)
 IN_BIND                                   VARCHAR2(1)
 IN_CURSOR_CLOSE                           VARCHAR2(1)
 IN_SEQUENCE_LOAD                          VARCHAR2(1)
 CAPTURE_OVERHEAD                          VARCHAR2(1)
 REPLAY_OVERHEAD                           VARCHAR2(1)
 IS_CAPTURED                               VARCHAR2(1)
 IS_REPLAYED                               VARCHAR2(1)
 SERVICE_HASH                              NUMBER
 PROGRAM                                   VARCHAR2(64)
 MODULE                                    VARCHAR2(48)
 ACTION                                    VARCHAR2(32)
 CLIENT_ID                                 VARCHAR2(64)
 MACHINE                                   VARCHAR2(64)
 PORT                                      NUMBER
 ECID                                      VARCHAR2(64)
 TM_DELTA_TIME                             NUMBER
 TM_DELTA_CPU_TIME                         NUMBER
 TM_DELTA_DB_TIME                          NUMBER
 DELTA_TIME                                NUMBER
 DELTA_READ_IO_REQUESTS                    NUMBER
 DELTA_WRITE_IO_REQUESTS                   NUMBER
 DELTA_READ_IO_BYTES                       NUMBER
 DELTA_WRITE_IO_BYTES                      NUMBER
 DELTA_INTERCONNECT_IO_BYTES               NUMBER
 PGA_ALLOCATED                             NUMBER
 TEMP_SPACE_ALLOCATED                      NUMBER


Regards
Michel
Re: How to find out session consuming high resource in the past? [message #581889 is a reply to message #580820] Thu, 11 April 2013 10:55 Go to previous message
sysdba007
Messages: 28
Registered: November 2012
Junior Member
Thank you Michel and alan

so at least in 11g we can check some resource usage as happened in the past

That's fantastic!


Regards
sysdba007
Previous Topic: Regarding trace file &TKprof
Next Topic: Tuning options while using RBO
Goto Forum:
  


Current Time: Wed Apr 16 00:06:39 CDT 2014

Total time taken to generate the page: 0.12258 seconds