How to identify by PGA size for each server process:
===========================================
SQL> SET LINESIZE 145
SQL> SET PAGESIZE 9999
SQL>
SQL>
SQL> COLUMN sid
FORMAT 99999 HEADING
'SID'
SQL> COLUMN serial_id
FORMAT 999999 HEADING 'Serial#'
SQL> COLUMN session_status
FORMAT a9 HEADING 'Status'
JUSTIFY right
SQL> COLUMN oracle_username FORMAT
a12 HEADING 'Oracle User'
JUSTIFY right
SQL> COLUMN os_username
FORMAT a9 HEADING 'O/S User'
JUSTIFY right
SQL> COLUMN os_pid
FORMAT 9999999 HEADING 'O/S PID'
JUSTIFY right
SQL> COLUMN session_program FORMAT
a18 HEADING 'Session Program' TRUNC
SQL> COLUMN session_machine FORMAT
a8 HEADING 'Machine'
JUSTIFY right TRUNC
SQL> COLUMN session_pga_memory FORMAT
9,999,999,999 HEADING 'PGA Memory'
SQL> COLUMN session_pga_memory_max FORMAT 9,999,999,999
HEADING 'PGA Memory Max'
SQL> COLUMN session_uga_memory FORMAT
9,999,999,999 HEADING 'UGA Memory'
SQL> COLUMN session_uga_memory_max FORMAT 9,999,999,999
HEADING 'UGA Memory MAX'
SQL>
SQL>
SQL> prompt
SQL> prompt
+----------------------------------------------------+
+----------------------------------------------------+
SQL> prompt | User Sessions Ordered by Current PGA Size
|
| User Sessions Ordered by Current PGA Size
|
SQL> prompt
+----------------------------------------------------+
+----------------------------------------------------+
SQL>
SQL>
SQL> SELECT
2 s.sid
sid
3 , s.serial#
serial_id
4 , lpad(s.status,9)
session_status
5 , lpad(s.username,12) oracle_username
6 , lpad(s.osuser,9) os_username
7 , lpad(p.spid,7) os_pid
8 , s.program
session_program
9 , lpad(s.machine,8)
session_machine
10 , sstat1.value
session_pga_memory
11 , sstat2.value
session_pga_memory_max
12 , sstat3.value
session_uga_memory
13 , sstat4.value
session_uga_memory_max
14 FROM
15 v$process p
16 , v$session s
17 , v$sesstat sstat1
18 , v$sesstat sstat2
19 , v$sesstat sstat3
20 , v$sesstat sstat4
21 , v$statname statname1
22 , v$statname statname2
23 , v$statname statname3
24 , v$statname statname4
25 WHERE
26 p.addr (+)
= s.paddr
27 AND s.sid
= sstat1.sid
28 AND s.sid
= sstat2.sid
29 AND s.sid
= sstat3.sid
30 AND s.sid
= sstat4.sid
31 AND statname1.statistic# =
sstat1.statistic#
32 AND statname2.statistic# =
sstat2.statistic#
33 AND statname3.statistic# =
sstat3.statistic#
34 AND statname4.statistic# =
sstat4.statistic#
35 AND statname1.name
= 'session pga memory'
36 AND statname2.name
= 'session pga memory max'
37 AND statname3.name
= 'session uga memory'
38 AND statname4.name
= 'session uga memory max'
39 AND statname2.name
= 'session pga memory max'
40 order by pga_session_memory desc
41 ;
order by pga_session_memory desc
*
ERROR at line 40:
ORA-00904: "PGA_SESSION_MEMORY": invalid identifier
SQL> ed
Wrote file afiedt.buf
1 SELECT
2 s.sid
sid
3 , s.serial#
serial_id
4 , lpad(s.status,9)
session_status
5 , lpad(s.username,12) oracle_username
6 , lpad(s.osuser,9) os_username
7 , lpad(p.spid,7) os_pid
8 , s.program
session_program
9 , lpad(s.machine,8)
session_machine
10 , sstat1.value
session_pga_memory
11 , sstat2.value
session_pga_memory_max
12 , sstat3.value
session_uga_memory
13 , sstat4.value
session_uga_memory_max
14 FROM
15 v$process p
16 , v$session s
17 , v$sesstat sstat1
18 , v$sesstat sstat2
19 , v$sesstat sstat3
20 , v$sesstat sstat4
21 , v$statname statname1
22 , v$statname statname2
23 , v$statname statname3
24 , v$statname statname4
25 WHERE
26 p.addr (+)
= s.paddr
27 AND s.sid
= sstat1.sid
28 AND s.sid
= sstat2.sid
29 AND s.sid
= sstat3.sid
30 AND s.sid
= sstat4.sid
31 AND statname1.statistic# =
sstat1.statistic#
32 AND statname2.statistic# =
sstat2.statistic#
33 AND statname3.statistic# =
sstat3.statistic#
34 AND statname4.statistic# =
sstat4.statistic#
35 AND statname1.name
= 'session pga memory'
36 AND statname2.name
= 'session pga memory max'
37 AND statname3.name
= 'session uga memory'
38 AND statname4.name
= 'session uga memory max'
39 AND statname2.name
= 'session pga memory max'
40* order by session_pga_memory desc
41 /
SID Serial# Status Oracle User
O/S User O/S PID Session Program Machine PGA
Memory PGA Memory Max UGA Memory UGA Memory MAX
------ ------- --------- ------------ --------- -------
------------------ -------- -------------- -------------- --------------
--------------
133 2190 ACTIVE
SYS kumar-PC\ 7040 sqlplus.exe
WORKGROU 4,906,580
5,168,724 1,930,352 3,426,812
166 1 ACTIVE
SYSTEM 3360
ORACLE.EXE (LGWR) KUMAR-PC 4,906,580
5,234,260 91,704
91,704
132 2780 INACTIVE
SYSMAN KUMAR-PC$ 5476 OMS
kumar-PC 2,857,936
4,889,552 2,252,304 3,378,324
167 1 ACTIVE
SYSTEM 1960
ORACLE.EXE (DBW0) KUMAR-PC 2,440,324
2,440,324 91,704
91,704
130 6360 INACTIVE
SYSMAN KUMAR-PC$ 7776 OMS
kumar-PC 1,564,244
1,564,244 1,204,592 1,204,592
145 944 INACTIVE
SYSMAN KUMAR-PC$ 2340 OMS
kumar-PC 1,498,708
1,760,852 884,964
1,393,928
161 1 ACTIVE
SYSTEM 3980
ORACLE.EXE (MMON) KUMAR-PC 1,424,452
2,538,564 877,272
1,756,388
124 656 INACTIVE
DBSNMP NT AUTHOR 6544 emagent.exe
WORKGROU 1,367,636 12,901,972
860,564 4,781,820
140 48 ACTIVE
DBSNMP NT AUTHOR 2296 emagent.exe
WORKGROU 1,302,100 3,333,716
877,272 2,366,172
165 1 ACTIVE
SYSTEM 2716
ORACLE.EXE (CKPT) KUMAR-PC 1,178,340
1,243,876 157,168
157,168
136 1909 ACTIVE
SYSMAN KUMAR-PC$ 6472 OMS
kumar-PC 1,171,028
1,302,100 615,416
746,344
151 1 ACTIVE
SYSTEM 1252
ORACLE.EXE (q001) KUMAR-PC 1,105,492
1,302,100 419,024
811,916
129 2611 ACTIVE
SYSTEM 2472
ORACLE.EXE (J000) KUMAR-PC 1,039,956
3,989,076 91,704
91,704
162 1 ACTIVE
SYSTEM 3744
ORACLE.EXE (CJQ0) KUMAR-PC 851,656
1,572,552 419,024
738,868
164 1 ACTIVE
SYSTEM 3968
ORACLE.EXE (SMON) KUMAR-PC 843,348
2,022,996 288,096
1,472,104
163 1 ACTIVE
SYSTEM 3496
ORACLE.EXE (RECO) KUMAR-PC 515,668
515,668 157,168
222,632
139 1356 INACTIVE
SYSMAN KUMAR-PC$ 5936 OMS
kumar-PC 450,132
450,132 157,168
222,632
160 1 ACTIVE
SYSTEM 2188
ORACLE.EXE (MMNL) KUMAR-PC 384,596
384,596 91,704
157,168
158 331 INACTIVE
SYS kumar-PC\ 4384 sqlplus.exe
WORKGROU 384,596
384,596 157,168
157,168
154 23 ACTIVE
SYSTEM 4060
ORACLE.EXE (QMNC) KUMAR-PC 319,060
319,060 91,704
91,704
138 3417 ACTIVE
SYSTEM 6672
ORACLE.EXE (q002) KUMAR-PC 319,060
319,060 91,704
91,704
170 1 ACTIVE
SYSTEM 2104
ORACLE.EXE (PMON) KUMAR-PC 319,060
319,060 91,704
91,704
169 1 ACTIVE
SYSTEM 1868
ORACLE.EXE (PSP0) KUMAR-PC 319,060
319,060 91,704
91,704
168 1 ACTIVE
SYSTEM 312
ORACLE.EXE (MMAN) KUMAR-PC 319,060
319,060 91,704
91,704
24 rows selected.
SQL> spool off
<html> <head> DBA GROUP</HEAD>
<TITLE>DBA QUERY</TITLE>
<MARQUEE> WELCOME T>O DBA QUERY</MARQUEE>
</HTML>
No comments:
Post a Comment