Analysis of Abnormal pga consumption

Analysis of abnormal consumption of a pga OS: aix6db: 10205 ------ use the OS command to observe the memory consumption of the oracle process # psgv ...... size23396516

Analysis of abnormal consumption of a pga: aix 6 db: 10205 ------ use the OS command to observe the memory consumption of the oracle process # ps gv ...... size 23396516-A 14907: 02 5203 14324376 xx 2058800 90815 38576 2.0 oracle 3.0-A 23396516: 02 14907 5203 14324376 xx 2058800 90815

Analysis of Abnormal pga consumption

OS: aix 6

Db: 10205

------ Use the OS command to observe the memory consumption of oracle Processes
# Ps gv
... Size
23396516-A 14907: 02 5203 14324376 2058800 xx 90815 38576 2.0 oracle
23396516-A 14907: 02 5203 14324376 2058800 xx 90815 38576 2.0 oracle
57540768-A 3711: 33 8331 15108164 15146740 xx 90815 38576 1.9 oracle
16318890-A 10639: 03 187 15110924 15009560 xx 90815 38576 2.0 oracle
33554712-A 17263: 24 13740 9598740 185552 xx 90815 38576 2.1 oracle
41877820-A 10492: 10 826 15110484 15018716 xx 90815 38576 2.0 oracle
..............

A single process consumes nearly 15 GB.

AIX/LINUX how to view memory consumption http://blog.csdn.net/lixora/article/details/24060299 for a single process at the OS level

Here, we provide other methods to check the memory size of processes.

---- View the memory consumption of the corresponding oracle application process
Select p. username, p. spid, p. program, pm. category, pm. used, pm. allocated, pm. max_allocated
From v $ process p, v $ process_memory pm;
----- Where p. pid = pm. pid and program like '% TNS % ';

41877820:
Username spid program category used allocated MAX_ALLOCATED
---------------------------------------------------------------------------------------------------------------------------
Oracle 41877820 oracle @ sssdb SQL 1.3077E + 10 1.3081E + 10 1.3081E + 10
Oracle 41877820 oracle @ sssdb PL/SQL 224 2008 2008
Oracle 41877820 oracle @ sssdb Other 2178876745 2178876745

33554712:
Username spid program category used allocated MAX_ALLOCATED
---------------------------------------------------------------------------------------------------------------------------
Oracle 33554712 oracle @ sssdb SQL 5380315176 5382747784 5382747784
Oracle 33554712 oracle @ sssdb PL/SQL 26240 31224 37560
Oracle 33554712 oracle @ sssdb Other 4398399145 4398399145

Username spid program category used allocated MAX_ALLOCATED
---------------------------------------------------------------------------------------------------------------------------
Oracle 16318890 oracle @ sssdb SQL 1.3066E + 10 1.3070E + 10 1.3070E + 10
Oracle 16318890 oracle @ sssdb PL/SQL 224 2008 2008
Oracle 16318890 oracle @ sssdb Other 2185355529 2185355529

Username spid program category used allocated MAX_ALLOCATED
---------------------------------------------------------------------------------------------------------------------------
Oracle 57540768 oracle @ sssdb SQL 1.3291E + 10 1.3294E + 10 1.3294E + 10
Oracle 57540768 oracle @ sssdb PL/SQL 224 2008 2008
Oracle 57540768 oracle @ sssdb Other 2093690553 2093690553

Username spid program category used allocated MAX_ALLOCATED
---------------------------------------------------------------------------------------------------------------------------
Oracle 23396516 oracle @ sssdb SQL 1.1608E + 10 1.1611E + 10 1.1611E + 10
Oracle 23396516 oracle @ sssdb PL/SQL 224 2008 2008
Oracle 23396516 oracle @ sssdb Other 2769131497 2769131497

Most of the memory is spent on SQL

Select program, MODULE, TERMINAL, MACHINE from v $ session where paddr in (select addr from v $ process where spid in (23396516,57540768, 33554712,16318890, 41877820 ));

---- Determine the corresponding application
SQL> select program, MODULE, TERMINAL, MACHINE from v $ session where paddr in (select addr from v $ process where spid in (23396516,57540768, 33554712,16318890, 41877820 ));

PROGRAM MODULE TERMINAL MACHINE
----------------------------------------------------------
Xxx.exe sfd DC9A29A
Xxx.exe sdf72172 WOR722C72172
Xxx.exe GR5FD WORKG-4715FD
Xxx.exe FSK051 wob120051
Xxx.exe CD3LVB0U MSVWCVB0U

--------- Here, we will continue to track the Sessions of the corresponding application to determine whether the operations in this application have caused such a large memory overhead.
1. Find the application sid and serial #

SQL> select sid, serial # from v $ session where lower (program) like '% isap_client % ';

Sid serial #
--------------------
78 1703

2. Start tracking

SQL> exec sys. DBMS_SYSTEM.set_ SQL _trace_in_session (78,1703, true );

PL/SQL procedure successfully completed.

3. perform operations on the application during the period (ensure interaction with the database)

4. Stop tracing. trace files will be generated in the user_dump_dest directory.

SQL> exec sys. DBMS_SYSTEM.set_ SQL _trace_in_session (78,1703, false );

PL/SQL procedure successfully completed.

5. Find and enter the user_dump_dest directory, and generate the trace file to be used.

SQL> set lines 1024
SQL> show parameter user_dump_dest

NAME TYPE VALUE
--------------------------------------------------------------------------------------------------
User_dump_dest string/opt/oracle/db01/app/oracle/ad
Min/ORCL/udump
SQL> exit

$ Cd/opt/oracle/db01/app/oracle/admin/ORCL/udump
$ Ls-otr | tail-1
-Rw-r ----- 1 oracle 576097 Jan 8 orcl_ora_24884.trc
$

6. Use tkprof to format the trace file. sys = no means you will not view the operations of the sys user.

$ Tkprof orcl_ora_24884.trc report.txt sys = no

TKPROF: Release 9.2.0.4.0-Production on Tue Jan 8 16:19:35 2008

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

$

The report.txt file behind the 72.161_format contains all the stored procedures that occurred during the tracking period (the SQL operations executed in the stored procedures are also displayed one by one) and SQL operations, as well as the statistics on the execution of each SQL statement, you can understand which SQL statements are fast and slow.

$ More report.txt success