Old bird comprehensively describes oracle database objects

Oracle has a lot to learn about. Here we mainly introduce Oracle database objects, including Oracle instances. Each Oracle instance is composed of a group of Oracle background processes and a memory zone of SGA. This group of background processes automatically read and write database data files, and the database performance can be affected by these factors:
◆ Is the distribution of various parts of SGA reasonable and the use efficiency normal;
◆ Is there much competition between I/O and lock.

1. SGA allocation and Efficiency

The memory allocated to each instance, that is, the efficiency of SGA, will greatly affect the performance of the database system. SGA consists of the following parts: Shared Pool, data block buffer, redo log buffer, and large pool.

Shared Pool storage database cache storage shared SQL and PL/SQL zone) and data dictionary cache Oracle database object information) and session period information for MTS ). Because the information is frequently accessed by applications, the information must maintain a high hit rate. You can use the following statement to confirm the data hit rate in the Shared Pool:

◆ Database cache:

 
 

  
  
Select gethitratio from v $ librarycache should be greater than 90%
  
  
Select sum (reloads)/sum (pins) from v $ librarycache must be less than 1%
 
 

◆ Data dictionary cache:

 
 

  
  
Select sum (getmisses)/sum (gets) from v $ rowcache must be less than 15%
 
 

Due to the uneven level of the program designer, there may be large anonymous blocks, which will lead to SQL not being reused. Therefore, you need to find a large anonymous block and convert it into a stored procedure for reuse:

 
 

  
  
select * from v$sqlarea where command_type=47 and length(sql_text)>500 
 
 

For SQL objects that are frequently used by some application systems, such as stored procedures, functions, and packages, you can pin them in the memory to prevent the shared pool from being removed because it is too small:

 
 

  
  
exec dbms_shared_pool.keep 
 
 

◆ Data block buffer Db block buffer ):

The data block buffer zone stores the data block content of data files frequently accessed by users and the data content modified by users. The database reads the content in the data file into the memory and reads it directly from the memory the next time it needs to, thus reducing disk I/O and response time. Of course, generally only small data tables, such as common code tables, are cached in the memory.

Since it is impossible to store all data in the data fast buffer, you can use the LRU algorithm to determine which data blocks to remove, but try to ensure a high data hit rate.

The SQL statement for viewing the data block hit rate is:

 
 

  
  
select 1-(phy.value/(cur.value+con.value)) 
from v$sysstat cur,v$sysstat con,v$sysstat phy  
  
  
where cur.name='db block gets' and con.name='consistent gets' 
  
  
and phy.name='physical gets' 
 
 

If the hit rate is less than 0.85, You need to allocate more memory for the data block buffer.

◆ Redo Log buffer ):

Redo log buffer stores the redo entries of each DML or DDL statement copied from the user memory area. If the buffer allocation is too small, there is not enough space for redo entries to wait.

2. I/O and Resource Competition

Since many processes need to write data files, I/O adjustment is required to solve the I/O bottleneck problem. If the rational allocation of tablespaces is effectively considered in the design phase, I/O competition can be effectively reduced to a certain extent. During database operation, due to the dynamic growth of data, the space originally allocated to tables or Indexes has been used up, and Oracle will automatically allocate space to these Oracle database objects. This dynamic allocation will affect the system performance, so the requirements are as follows:

Avoid dynamic space management

Localized Management of tablespaces to reduce disk competition with data dictionary tablespaces.

In the system design and test run phase, SQL statements with relatively small data volume and low efficiency may not affect the system response time. However, when the system data volume increases to a certain extent, when the system is running, you need to monitor and find out which sqls cannot use indexes effectively or lack indexes, and make corresponding adjustments: create indexes and modify the SQL statement syntax.

In addition, some mechanisms are required in Oracle to ensure the stability and Data Consistency of Oracle database objects during use, such as latch and lock. Therefore, competing resources related to these mechanisms will affect the database performance. To reduce resource competition, you can adjust database initialization parameters such as db_block_lru_latches and dml_locks to reduce resource contention and optimize database performance.

1. Old bird comprehensively describes oracle database objects
2. How to adjust the Oracle database
3. What is Oracle Database in five minutes?
4. Introduction to Oracle database backup and recovery features
5. Horizontal comparison between SQL Server and Oracle Database Transaction Processing