Oracle technical Q &

Oracle technical Q &

Today, I talked to a senior Oracle expert about a lot of technical problems. He also came up with a few questions and asked me at random, I found that there are still a lot of dry goods in the seemingly simple questions. Many things seem to understand or not understand them in depth. Due to space limitations, I sorted out some questions and answered some questions correctly, however, I feel that my understanding is not clear enough.

Log buffer

Functions of log buffer

Logbuffer has two main functions: Generate redo records, and write the redo records to the redo log file.

Will the log buffer be refreshed during the commit operation?

In fact, each commit triggers a Change vector to complete a transaction update.

PGA

Functions of pga

The full name of pga is program global area, which is a memory area that contains data and control information related to a specific server process. For each session, the bind variables passed in the SQL statement lock executed by the session will be stored in pga. When you need to execute complicated SQL statements, the memory occupied by session sorting and hash join connections also comes from PGA.

Will PGA be shared between processes?

The memory occupied by pga can only be accessed by the process to which it belongs, but cannot be accessed by other processes. Therefore, the pga does not need a memory structure such as latch to protect the information.

Buffer cache

Buffer cache Algorithm

The algorithms used in buffer cache include the hash algorithm and LRU algorithm.

When the current process sends a select or dml statement, oracle will find the qualified data block according to the execution plan, and then take the request data block address and data block type as the parameter, use the hash algorithm to find the hash bucket where the data block is located, that is, determine the hash bucket where the data block is located.

The LRU algorithm is the list of buffer headers that are least recently used. The buffer headers connected by the LRU chain execute available data blocks, and the buffer is hung in LRUlianbiao shang in the order of use, the buffer used first hangs behind the LRU linked list, and the buffer used later hangs before the LRU linked list.

Status of data blocks in buffer cache

Dirty data blocks, idle data blocks, clean data blocks, and pinned data blocks. Free data blocks and clean data blocks are collectively referred to as available data blocks. Because the content can be overwritten by the new data content.

Shared pool

Where is the data dictionary information stored?

The data dictionary information is stored in the dictionary cache in the shared pool. The dictionary cache stores the information of the data dictionary referenced during SQL statement execution, including the table name, table column, and permission information involved in the SQL statement, the information in the dictionary cache is stored in the form of data rows. So it can also be called row cache.

CKPT

Where does the CKPT process find the corresponding information.

CKPT is the checkpoint position (checkpoint position). The position of the checkpoint is recorded in the control file. The buffer header corresponding to the dirty data block is serialized in the checkpoint queue, each time dbwr writes dirty data blocks, dirty data blocks are also scanned from the checkpoint queue and written to the data file.

Data Recovery

Rollback and rollback during data recovery

If the database instance is shut down abnormally, the checkpoint end scn is null. During data recovery, the checkpoint position is obtained from the control file, then, smon finds the corresponding checkpoint location in the online log file and applies all redo entries to restore the status at that time point before the instance crashes in the buffer cache, this is called rollback.

If the smon process opens the database immediately after the rollback is completed, but at this time the database contains databases in the intermediate state that are neither submitted nor rolled back, that is, dirty data blocks. Because they are not submitted, they need to be rolled back. After the database is opened, smon will perform rollback in the background.

Rman

Rman configuration and modification of the backup file path

You can use the graphical interface in em and grid control to modify the path of the backup file in rman, or use the config command to set the path.

Optimization

Which of the following packages are commonly used for SQL optimization?

Dbms_sqltune, dbms_ SQL _advisor

Through these, I found that I had some knowledge of the database, but I still need to deepen it. If you encounter many problems, you may be swallowed up. If you have any details, you will be stuck. People are constantly improving themselves in this process. It also reflects from the perspective that they have been a little lax recently. They have not been able to invest more efficient energy and work hard. If they do, they will improve. If they do not, they will be crowned, life is not easy, and cannot pass. :)