Oracle memory-instant attack (2)

Memory of oracle-Spur (2) pga is not shared in the Process of the operating system or a memory area specific to the thread. Because pga is specified by a process, it is not allocated in sga. Pga is a memory heap that contains session variable information required by a dedicated server or Shared Server gold lock. The server process needs to allocate some memory structures in the pga. A metaphor is that pga is the work area of a temporary file administrator. This file administrator is a service process and works for the client process ), then, the file administrator divides the work area into many areas to process or store different customer information. When the work is completed, the space in the relevant area will be released. For example, the information about pga allocated by all processes in dedicated server mode is displayed (note that the background process also needs to be allocated with pga ), you can use an initialization parameter to set A large instance pga value, and then each private pga occupies part of the instance pga size:) the pga component content is divided into many different regions and each region has different functions. Below is the memory allocation of pga in dedicated server mode. Not all components are required. Many Masters divide pga into two parts: fixed area and variable area. variable areas are divided into session memory) and private SQL area (private SQL area), private SQL area is divided into permanent area and running area (runtime area and SQL work areas ). This is also meaningful, as shown in figure. Private SQL area: the private SQL area contains information about SQL statement parsing and session information related to other processes. When a service process processes SQL or pl/SQL code, the process uses private SQL to store variable information and statement query execution status information and statement execution region information. In uga, private SQL area stores statement execution plans by using the shared SQL area, which are allocated in sga. 1) shared SQL area: When an SQL statement is executed for the first time, the database uses the shared SQL area to process the fold Statement, which is shared, it can be accessed by other users, including the statement execution analysis tree and execution plan. In this area, each shared SQL area is a unique statement. (Details will be described in sga and shared pool) 2) private SQL area when a session executes an SQL statement, this private SQL area will be allocated in pga. Each session has a private SQL area. If you execute the same SQL statement, it points to the same shared SQL area. For example, if I run select * from t in a session and run the same statement in 10 different sessions, the same SQL statement execution plan will be shared. However, private area may not be shared, because it may store different variable values and data. In fact, when we perform dml and ddl operations, we implicitly open the cursor. Each cursor serves as a pointer to the server side of the client, and each cursor is opened with a corresponding private SQL area. Therefore, in the process of application development, you should release and close the cursor in time to release memory usage to improve memory usage. Client process load management private SQL region, releasing and allocating private SQL area is based on the application, but we can use the open_cursor parameter to control the number of cursors that can be opened by client process. This private SQL area is divided into two areas: runtime area and persistent area1) runtime area when a request is executed, the first step is to allocate the runtime area, which contains query execution status information, for example, when I scan a full table, the running zone will track the progress of the number of queries. When the executed dml SQL statement ends, the region will be released. 2) persistent area: This area contains the variable value (bind variable). When the cursor is closed, the memory is released: eg: select * from t where name =: value; then value is the bind variablesql work area: the work area is private allocated in the pga, use and intensive operations, such as when I perform sort operator, the sort area will be used to store the sort rows. When I perform hash join, I will use the hash area to store the corresponding retrieval content, when bitmap merge is used, bitmap merge area is used to scan bitmap indexes to merge data. Eg:

SQL> select * from student s join class c on s.id=c.id(+) order by s.id desc;        ID NAME                        AGE    CALSSID         ID CLASSNAME---------- -------------------- ---------- ---------- ---------- --------------------         8 h                            20          2         7 g                            26          3         6 f                            25          1         5 e                            23          3         4 d                            23          1         3 c                            22          1          3 3         2 b                            21          2          2 2         1 a                            20          1          1 18 rows selected.SQL> set autotrace trace explain;SQL> r  1* select * from student s join class c on s.id=c.id(+) order by s.id descExecution Plan----------------------------------------------------------Plan hash value: 537866712---------------------------------------------------------------------------------------------| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |             |     8 |   608 |     5  (40)| 00:00:01 ||   1 |  SORT ORDER BY                |             |     8 |   608 |     5  (40)| 00:00:01 ||   2 |   MERGE JOIN OUTER            |             |     8 |   608 |     4  (25)| 00:00:01 ||   3 |    TABLE ACCESS BY INDEX ROWID| STUDENT     |     8 |   408 |     1   (0)| 00:00:01 ||   4 |     INDEX FULL SCAN           | PRIMARY_KEY |     8 |       |     1   (0)| 00:00:01 ||*  5 |    SORT JOIN                  |             |     3 |    75 |     3  (34)| 00:00:01 ||   6 |     TABLE ACCESS FULL         | CLASS       |     3 |    75 |     2   (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   5 - access("S"."ID"="C"."ID"(+))       filter("S"."ID"="C"."ID"(+))Note-----   - dynamic sampling used for this statement (level=2)SQL>

 

In this execution plan, we can see that sort order by is in sort area, and the running area of private SQL summarizes the progress of a full table scan in the calss table, this session executes a left join operation to obtain the corresponding data in two tables. When the work area is very small, it cannot hold the data information of the lock execution statement, so it divides the execution data information into a lot of data piece, then oracle slowly processes piece, and other piece will be temporarily cached in disk, so we should ensure that there is enough memory for the work area to reduce disk I/o, to improve system performance. B) Distribution of components in the pga Shared Server and dedicated server modes.

Memory Area Dedicated Server Shared ServerNature of session memoryPrivateSharedLocation of the persistent areaPGASGALocation of the run-time area for DML/DDL statementsPGAPGA++++++++++++++++++++++++++++++++++++++++++++++++(^ω^)+++++++++++++