Sharing pool for Oracle Performance Optimization

1. Introduction to shared pool:
 
The location, parts, and functions of the Shared Pool;
 
Ii. Set and view the shared pool size:
 
In 9i, we use the Shared_pool_size parameter to set the size of the Shared Pool. Let's talk about the setting in 10 Gb. In 9i or 10g, the value of Shared_pool_size does not necessarily represent the true size of the Shared Pool. the actual size of the shared pool may differ from the value of this parameter. To view the size of a shared pool, you can use either of the following methods:
 
1) Use Show sga
 
The following is the running result of Show sga:
 
SQL> show sga
 
Total System Global Area 448790528 bytes
 
Fixed Size 1249488 bytes
 
Variable Size 79695664 bytes
 
Database Buffers 360710144 bytes
 
Redo Buffers 7135232 bytes
 
This command shows the size of each part of the SGA. Here, Fixed size is a Fixed area, which is used to store some management information that manages the memory structure. DBA does not need to adjust the memory. Database buffers is the Buffer cache size. Redo buffers indicates the size of the Redo cache. Variable Size is a Variable region, and the shared pool accounts for about 8% and of the Size, it also includes the Java pool, large pool, and other SGA pools in addition to fixed areas, Buffer cache, and redo cache. In addition, there are some administrative information in this variable region. For example, the V $ System Dynamic Performance view that we often use is derived from the management information in this variable region (the V $ information comes from X $, the data in X $ comes from some structures in the Variable Region ). Because the shared pool accounts for most of the variable areas, we can generally use it to understand the size of the shared space.
 
In addition to Show Sga, we can also use the V $ sgastat view to display the size of each memory structure of SGA. In this view, we can precisely see the size of the Shared Pool, the following is the display information style of this view:
 
SQL> select * from v $ sgastat where rownum <= 10;
 
POOL NAME BYTES
 
------------------------------------------------
 
Fixed_sga 1249488
 
Buffer_cached 360710144
 
Log_buffer 7135232
 
Shared pool dpslut_kfdsg 256
 
Shared pool hot latch diagnostics 80
 
Shared pool enqueue stats 8360
 
Shared pool transaction 264528
 
Shared pool KCB buffer wait statistic 3352
 
Shared pool invalid low rba queue 320
 
Shared pool, KQF, optimizer, stats table 2396
 
..............................................................................
 
..............................................................................
 
In 10 Gb, this view shows more than 600 rows. All rows with POOL columns not empty are the parts of the variable region. Rows with empty POOL values can be seen in the NAME column as fixed areas, Buffer cache, and redo cache. The size of the three rows is the same as that shown in Show sga. Next we will group by POOL to see the size of each memory POOL in the Variable Region:
 
SQL> select pool, sum (bytes)/1024/1024 | 'mb' from v $ sgastat where pool is not null group by pool;
 
Pool sum (BYTES)/1024/1024 | 'mb'
 
-------------------------------------------------------
 
Java pool 4 MB
 
Shared pool 64.00447845458984375 MB
 
Large pool 4 MB
 
We can see that there are Java pool and Large pool in the variable region, the size is 4 MB, and the size of the shared pool is a little larger than 64 MB. Adding these three pools does not Show the variable area size shown in Show sga. That's because in addition to the three pools, variable areas also have administrative information such as the X $ structure.
 
This view can be used in 9i and 10g, but another view in 10g can provide more accurate information about the size of the Shared Pool. Next, let's take a look at the improvements made to memory management in 10 Gb.
 
 
3. Set the size of the Shared Pool in 10 GB:
 
In 10 Gb, the simple setting of the Shared Pool is more convenient. We only need to define the value of the SGA_TARGET parameter, that is, the size of the target SGA. Oracle will automatically set the size of the Shared Pool according to the target value. In addition, if the shared pool memory is insufficient, Oracle can automatically expand the size of the shared pool if the system still has idle memory. Note: as we have just said, this is a simple setting of the Shared Pool. In fact, this simple setting does not need to be set at all. You only need to tell the SGA size, it can automatically set all memory components in SGA. This is a new feature of 10 Gbit/s compared with 9i, which simplifies DBA's work. Oracle itself is becoming more and more intelligent and continues to grow. Many people worry that DBAs will not be unemployed. As software becomes more intelligent and simpler, DBA will be eliminated one day. Most people who hold this idea do not have a deep understanding of computers. When we continue to study deeply, we will find that our current level of artificial intelligence is so naive and ridiculous, we will not discuss this topic here. If you are interested, you can read a variety of books on artificial intelligence algorithms, such as Neural Networks and genetic algorithms. Naturally, you will understand the development level of artificial intelligence. Back to our own topic, it is undeniable that software is evolving towards intelligence and automation, but the more intelligent it is, the more complicated it is. Just like the human brain, how many generations of complex scientists do not fully understand their operating principles. Once such a complex system encounters a problem, it can be solved only by a person with a higher degree. For example, if you are driving a car by yourself, the car is faulty and can be repaired in many places. Our Shenzhou rocket, however, has already set its operating orbit. It does not require pilots to drive it in person, and its automation is much higher than that of cars. But if there is a problem with the Shenzhou rocket, who can fix it? Our database will also develop like this in the future. With the increase in intelligence, a large number of junior DBAs will be eliminated, but the demand for senior DBAs will only be greater than before. In addition, the salary level of senior DBA is higher than before. As we have already said, the higher the intelligence level, the more complicated the software itself. More and more complex software requires more and more DBAs. Just like the sharing pool we are about to talk about, its simple setting is as simple as no need to set it again. However, after determining the SGA_TARGET size, what if the size of the shared pool automatically defined by Oracle is not suitable? In many cases, we cannot rely entirely on software intelligence, obviously, Oracle clearly understands this. Therefore, it reserves the right to allow DBAs to manually adjust SGA memory components such as the shared pool, the adjustment at 10g is a little more complex than 9i. The following describes the considerations for adjusting the sharing pool at 10g.
 
The Shared_pool_size parameter should be used to adjust the Shared Pool in 10 Gb, but it is no longer the deciding factor of the shared pool size. In 10 Gb, it only determines the minimum size of the shared pool, that is, it is the lower limit of the shared size. If you set it to 1 GB, Oracle will never allocate a Shared Pool lower than 1 GB of memory. It may allocate 1 GB as required, or it may allocate a shared pool with more than 1 GB memory. Generally, if you use this parameter to set a larger Shared Pool, this setting will take effect immediately. If you find that the shared pool is a little large, it should be set to a smaller value. If you use this parameter to set the Shared Pool to a smaller value, this setting will not take effect immediately. Why? This parameter is only the lower limit of the shared pool size. Your shared pool already has 1.5 GB. If you set this parameter to 1 GB, Oracle will think that you just want to minimize the Shared Pool to 1 GB, the size of the current shared pool is GB, which meets your requirements. Therefore, Oracle will not reduce the size of the Shared Pool. When does it think that 1.5g is too big, it should be smaller, then it will reduce the sharing pool. That is to say, in 10 Gb, Oracle strengthens its control over the shared pool, and reserves only the right to set the lower limit for DBA.
 
We can use a view V $ SGA_DYNAMIC_COMPONENTS to understand the size of each memory component in SGA:
 
Step 1: manually set the value of shared_pool_size to 50 M:
 
SQL> alter system set shared_pool_size = 50 m;
 
The system has been changed.
 
Step 2: display the V $ SGA_DYNAMIC_COMPONENTS View:
 
SQL> SELECT COMPONENT, CURRENT_SIZE, MIN_SIZE, USER_SPECIFIED_SIZE from V $ SGA_DYNAMIC_COMPONENTS;
 
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE
 
-----------------------------------------------------------------------
 
Shared pool 92274688 92274688 54525952
 
Large pool 8388608 8388608 8388608
 
Java pool 4194304 4194304 0
 
Streams pool 0 0 0
 
DEFAULT buffer cache 247463936 243269632 209715200
 
KEEP buffer cache 0 0 0
 
RECYCLE buffer cache 0 0 0
 
DEFAULT 2 K buffer cache 0 0 0
 
DEFAULT 4 K buffer cache 8388608 8388608 8388608
 
DEFAULT 8 K buffer cache 0 0 0
 
DEFAULT 16 K buffer cache 0 0 0
 
 
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE
 
-----------------------------------------------------------------------
 
DEFAULT 32 K buffer cache 0 0 0
 
ASM Buffer Cache 0 0 209715200
 
 
13 rows have been selected.
 
The meanings of these columns are described as follows:
 
COMPONENT: name of the memory COMPONENT in SGA
 
CURRENT_SIZE: memory size currently occupied
 
MIN_SIZE: the minimum size of the memory component considered by Oracle through data analysis
 
USER_SPECIFIED_SIZE: the size specified by the user. That is, DBA sets the size by setting Shared_pool_size.
 
The above is the display result after shared_pool_size is set to 50 MB. From the above results, we can see that USER_SPECIFIED_SIZE is 52 MB, because the memory is allocated by block, in 10 Gb, It is 4 MB, and 52 MB is exactly 13 memory blocks. Oracle cannot allocate 50 MB because 50 cannot be fully divided by 4. From the MIN_SIZE column, Oracle considers that the sharing pool of 88M should be the smallest share pool size, so the current size recorded in the CURRENT_SIZE column is 88 MB.
 
 
Step 3: continue the experiment. What if I increase the memory of the Shared Pool:
 
SQL> alter system set shared_pool_size = 100 m;
 
The system has been changed.
 
Set the Shared Pool memory to 100 MB
 
 
Step 4: Display Results
 
SQL> SELECT COMPONENT, CURRENT_SIZE, MIN_SIZE, USER_SPECIFIED_SIZE from V $ SGA_DYNAMIC_COMPONENTS where COMPONENT = 'shared pooled ';
 
COMPONENT CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE
 
-----------------------------------------------------------------------
 
Shared pool 104857600 92274688 104857600
 
 
This time, I only show the shared pool. We can see that the size specified by the user (USER_SPECIFIED_SIZE) is already 100 MB, and the current size is also MB. Oracle considers the minimum size of MIN_SIZE to be 88 mb. This is because we have not performed many operations. Oracle considers that the database load is not heavy and the minimum size is not increased.
 
The current size of the Shared Pool is 100 MB. If you set the size of the Shared Pool to 88 MB, the current size will not change to 88 MB immediately. Because shared_pool_size is only used to tell Oracle users the minimum size of the Shared Pool, But Oracle does not immediately reduce the Shared Pool. Oracle will reduce the size of the shared pool when it deems it appropriate.
 
 
4. Optimization of the Shared Pool:
 
The Shared Pool Miss is larger than other pools, and the database cache should be optimized.
 
This article is from the "ye shaochen" blog