Discussion on Shared Pool (2)
Sunday, 2004-08-22 21:23 Eygle
LINK:
http://www.eygle.com/internal/shared_pool-2.htm
We continue to expand the previous problems.
In fact, we can monitor the space fragment of Shared Pool inside the database. This involves an internal view x $ kSMSP
X $ KSMSP Name The meaning of: [K] Ernal [S] Torage [M] Emory Management [S] Ga HEA [P] where each line represents a CHUNK in Shared Pool
First recording the test environment:
SQL> SELECT * FROM V $ VERSION;
Banner ------------------------------------- --------------- Oracle9i Enterprise Edition Release 9.2.0.3.0 - ProductionPL / SQL Release 9.2.0.3.0 - ProductionCore 9.2.0.3.0 ProductivityTns for Linux: Version 9.2.0.3. 0 - Productionnlsrtl Version 9.2.0.3.0 - Production
Let's take a look at the structure of x $ KSMSP:
SQL> DESC X $ KSMSP
Name NULL? TYPE
------------------------------------- ---------------------------
AddR Raw (4)
Indx Number
INST_ID NUMBER
KSMCHIDX NUMBER
KSMCHDUR Number
KSMCHCOM VARCHAR2 (16)
KSMCHPTR RAW (4)
KSMCHSIZ Number
KSMCLS VARCHAR2 (8)
KSMCHTYP NUMBER
KSMCHPAR RAW (4)
We pay attention to the following fields:
KSMCHCOM is a comment field, and each memory block is assigned, the comment will add in this field .x $ ksmsp.ksmchsiz represents block size
The x $ ksmsp.ksmchcls column represents the type, there are four types, which describes the following:
Freefree chunks - Does not contain any object of CHUNK, which can be assigned without restrictions.
Recrsentable chunks - contains objects that can be temporarily removed, this object can be recreated when needed. For example, many memory shared SQL code can be rebuilt.
Freeablfreeable chunks - Contains the session cycle or called object, which can then be released. This part of the memory is sometimes released in advance. But note that these objects cannot be temporarily removed from memory due to some objects. Because it is not reconstructive) .Permpermanent Memory Chunks - contains permanent objects. It is usually not independently released.
We can check the number of memory films existing in Shared Pool, but pay attention to: Oracle's some versions (such as: 10.1.0.2) on certain platforms (eg HP-UX PA-RISC 64- Bit) Query the view may result in excessive CPU consumption because BUG is caused.
Let's take a look at the test:
Initial start database, 1259 Chunk in X $ KSMSP
SQL> SELECT Count (*) from x $ kSMSP;
Count (*)
------------
2259
Executive query:
SQL> SELECT Count (*) from DBA_Objects;
Count (*)
------------
10491
At this time, the number of CHUNK in Shared Pool increases
SQL> SELECT Count (*) from x $ kSMSP;
Count (*)
------------
2358
This is due to SQL parsing, request space in Shared Pool, causing the request for free space, allocation, and segmentation to generate more, more finely broken memory CHUNK
From this we can see that if there is a lot of hard eodexes in the database system, the SHRED POOL memory that is not allowed to assign free, in addition to the competing of Shared Pool Latch, it is inevitable to cause more memory in Shared Pool. Debris (of course, when you recover, you may see the situation of the number of Chunk)
We look at the following test:
First restart the database:
SQL> Startup force;
Oracle Instance Started.
Total System Global Area 47256168 Bytes
Fixed size 451176 bytes
Variable size 29360128 bytes
Database buffers 16777216 BYTES
Redo buffers 667648 bytes
Database mounted.
Database opened.
Create a temporary table to save the status of X $ KSMSP before:
SQL> CREATE GLOBAL TEMPORY TABLE E $ KSMSP on Commit Preserve Rows AS
2 SELECT A.KSMCHCOM,
3 Sum (a.chunk) Chunk,
4 sum (a.RECR) RECR,
5 Sum (a.freeabl) FreeAbl,
6 Sum (a.sum) SUM
7 from (SELECT KSMCHCOM, Count (ksmchcom) chunk,
8 DECODE (KSMCHCLS, 'RECR', SUM (KSMCHSIZ), NULL) RECR,
9 Decode (KSMCHCLS, 'Freeabl', SUM (KSMCHSIZ), NULL) FreeAbl,
10 Sum (KSMCHSIZ) SUM
11 from x $ ksmsp group by ksmchcom, ksmchcls) A12 Where 1 = 0
13 group by a.ksmchcom;
Table created.
Save the current shared pool status:
SQL> INSERT INTO E $ KSMSP
2 SELECT A.KSMCHCOM,
3 Sum (a.chunk) Chunk,
4 sum (a.RECR) RECR,
5 Sum (a.freeabl) FreeAbl,
6 Sum (a.sum) SUM
7 from (SELECT KSMCHCOM, Count (ksmchcom) chunk,
8 DECODE (KSMCHCLS, 'RECR', SUM (KSMCHSIZ), NULL) RECR,
9 Decode (KSMCHCLS, 'Freeabl', SUM (KSMCHSIZ), NULL) FreeAbl,
10 Sum (KSMCHSIZ) SUM
11 from x $ kSMSP
12 group by ksmchcom, ksmchcls) a
13 group by a.ksmchcom
14 /
41 rows create.
Executive query:
SQL> SELECT Count (*) from DBA_Objects;
Count (*)
------------
10492
Compare changes to Shared Pool Memory Allocation:
SQL> SELECT A.KSMCHCOM, A.CHUNK, A.SUM, B.CHUNK, B.SUM, (A.chunk - B.chunk) C_DIFF, (a.sum -b.sum) s_diff
2 from
3 (Select a.ksmchcom,
4 Sum (a.chunk) Chunk,
5 Sum (a.RECR) RECR,
6 sum (a.freeabl) FreeAbl,
7 sum (a.sum) SUM
8 from (SELECT KSMCHCOM, Count (KSMCHCOM) CHUNK,
9 Decode (KSMCHCLS, 'RECR', SUM (KSMCHSIZ), NULL) RECR,
10 Decode (KSMCHCLS, 'Freeabl', SUM (KSMCHSIZ), NULL) FreeAbl,
11 Sum (KSMCHSIZ) SUM
12 from x $ kSMSP
13 Group By Ksmchcom, KSMCLS) A
14 Group by a.ksmchcom) A, E $ KSMSP B
15 Where a.ksmchcom = B.KSMCHCOM AND (A.Chunk - B.chunk) <> 0
16 /
KSMCHCOM Chunk Sum Chunk Sum C_Diff S_DIFF
-------------------------------------------------------------------------------- ------ ---------- ---------- KGL Handles 313 102080 302 98416 11 3664
KGLS HEAP 274 365752 270 360424 4 5328
KQR PO 389 198548 377 192580 12 5968
Free Memory 93 2292076 90 2381304 3 -89228
Library Cache 1005 398284 965 381416 40 16868
SQL Area 287 547452 269 490052 18 57400
6 rows selected.
We simply analyze the above results: First, the size of Free Memory decreased by 89228 (increasing to another five components), which shows that the SQL parsing stores takes up a certain memory space and the CHUNK is increased from 93, which indicates that the block is added. .
In the following part, I will showcase the memory structure in the two very important Shared Pools of KGL Handles, KGLS HEAP.
