I. Introduction: the most important part of database adjustment is to rewrite the SQL code with poor running efficiency. The SQL code after rewriting may be significantly different from the previous one in terms of running efficiency!
However, when the performance still fails to break through the bottleneck after rewriting or you are a user (unable to access the SQL code), you can adjust the Oracle shared memory structure SGA (system global area ), maximize performance!
The most important component in SGA is the shared pool, which is used to cache SQL statements at high speed! The sharing pool is managed by a least recently used (LRU, least recently used) algorithm!
Benefits of the Shared Pool:
1. Select cust_id, cust_name from col_cust
2. The algorithm of the preceding statement is converted into ASCII code, and then a separate hash value is generated through a hash algorithm ~~ Process then checks whether the hash value exists in the shared pool,
If yes, execute the statements in the cache.
3. If the statement does not exist, you must perform syntax analysis on the statement. These Analysis Steps incur additional system overhead. This operation is costly!
4. The matching SQL statement is called a high-speed buffer hit (Cache hit)
5. The opposite is high-speed buffer miss)
6. Note the case sensitivity.
Select cust_id, cust_name from col_cust
2. The Shared Pool consists of three components: Library cache (database cache area), data dictionary cache (data directory cache area), and user global area (User global area)
1. Library cache (database cache area) is a place for caching SQL statements. You can use the following sentence to query dynamic views and check the content of library cache.
Select P. username, L. SQL _text,
Lpad ('', 4 * (level-2) | operation |'' | options | ''| object_name as" Execution Plan"
From (
Select S. username, P. Address, P. hash_value, P. Operation, P. Options, P. object_name, P. ID, P. parent_id
From v $ SQL _plan P, V $ session s
Where (P. Address = S. SQL _address and P. hash_value = S. SQL _hash_value) and S. Username = 'citictest'
) P, V $ SQL L
Where (L. Address = P. Address and L. hash_value = P. hash_value)
Start with ID = 0
Connect by prior id = parent_id
2. Data Dictionary cache (data directory cache): The data directory is used to check whether the tables referenced by the SQL statement already exist and whether the column names and data types are correct! Library cache and data dictionary cache use an independent LRU mechanism. The advantage is that the statements released by subsequent users are similar to but different from those released by previous users, although the matching cannot be found in the library cache, it exists in the data directory and improves the performance.
III. (1) measure the performance of library Cache
Select namespace, gethitratio, pinhitratio, reloads, invalidations
From v $ librarycache
Where namespace in ('SQL _ region', 'table/procedure', 'body', 'trigger ');
Gethitratio and pinhitratio> 90% indicate that select sum (reloads)/sum (PINs) "reload ratio" is fully adjusted"
From v $ librarycache
When <1% means that statements that were previously loaded to the library cache are not frequently re-analyzed by syntax
(2) measure the performance of data dictionary cache (data directory cache)
Select 1-(sum (getmisses)/sum (gets) "data dictionary hit ratio"
From v $ rowcache;
When the value is greater than 85%, it indicates that the Shared Pool performance is improved by improving the library cache and data dictionary cache.
(1). Get the size of the current shared pool.
Select pool, sum (bytes) "size" from V $ sgastat where pool = 'shared pooled 'group by pool;
(2). Obtain the recommended shared pool size.
Set echo off
Set feedback off
Set serveroutput on
Declare
V_total_plsql_mem number: = 0;
V_total_ SQL _mem number: = 0;
V_total_sharable_mem number: = 0;
Begin
Select sum (sharable_mem) into v_total_plsql_mem from V $ db_object_cache;
Select sum (sharable_mem) into v_total_ SQL _mem from V $ sqlarea where executions> 10;
V_total_sharable_mem: = v_total_plsql_mem + v_total_ SQL _mem;
Dbms_output.put_line ('Estimated required shared pool size is: '| to_char (v_total_sharable_mem, 'fm9, 999,999,999,999') | 'bytes ');
End;
/
(3) dynamically increase the shared pool size
Alert System set shared_pool_size = 200 m;
* The size cannot exceed the value of sga_max_size.
(4) Calculation of initial SGA size
(Tsga) Total SGA size = physical server memory x 0.55 (60%-75% for physical memory above 1 GB)
(Tsgai) Total SGA size of each instance = number of instances on tsga/Oracle
Total Shared Pool memory = tsgai * 0.45
(5). The above four points are actually completed in the same way, that is, to make the Shared Pool larger.
(6). PL/SQL programs can be packaged into the Shared Pool reserved area (shared pool reserved area)
Shared_pool_reserved_size is used to set the size of this region. The default value is 5%, which is not enough.
Select owner, name, sharable_mem from V $ db_object_cache
Where Type in ('package', 'package body') order by sharable_mem DESC;
The preceding statement shows the name and size of the PL/SQL package in the current cache. If the size is larger than shared_pool_reserved_size, it indicates that the size of the reserved zone is insufficient. You need to increase the value of shared_pool_reserved_size.
(7). Keep important PL/SQL code in memory
You can set the commonly used procedure pin (pinning) in shared_pool_reserved_size.
The procedure is as follows: a) Log On As A sys user
B) Run @ % ORACLE_HOME %/rdbms/admin/dbmspool. SQL
C) SQL> execute dbms_shared_pool.keep ('procedurename') (pin, which must be completed by sys)
D) Find the pin object select owner, name, type from V $ db_object_cache where kept = 'yes ';
E) PIN to separate SQL statements that cannot be operated. Make the large statement procedure as much as possible. You can use the following statement to find a relatively large statement.
Select substr (SQL _text, 1, 45), length (SQL _text) "stmt_size" from V $ sqlarea where command_type = 47 order by length (SQL _text) DESC;
F) You can write a script to run the script after the instance is started. Execute all the statements that need to be released.
H) The unkeep is canceled only when the unkeep or instance is disabled.
(8). Other adjustments to library cache Parameters
A) open_cursors: The default value is 50.
B) The default value of cursor_space_for_time is false.
C) session_cached_cursors defaults to 0 (no cursor cache)
D) cursor_sharing exact by default -- the two SQL statements must be completely matched to share the analyzed code cached in the shared pool.
Similar -- allow two SQL statements that are literally different to share the analyzed code cached in the shared pool.
Example: Select cust_id from col_cust where cust_name = 'wang'
Select cust_id from col_cust where cust_name = 'huangt'
The preceding two statements are equal in similar mode, and the cached analyzed code can be used.
