Oracle knowledge sorting DBA thoughts and skies

Oracle knowledge sorting DBA thoughts and skies
The Inventory Oracle Installation Tool OUI is used to manage the Oracle parameter files in the Oracle installation directory. After startup, it reads the parameter files in the following order. If reading fails, it fails to start the database: 1 $ ORACLE_HOME/dbs/SPFILE. ORA2 $ ORACLE_HOME/dbs/spfile. ora3 $ ORACLE_HOME/dbs/init. the nomount phase of the ora Database: 1 reading the parameter file 2 allocating shared memory 3 starting the process Mount phase: 1 reading the control file 2 verifying oracle control files include: 1. physical structure of the database 2 data file 3 redo log online LOG File: solves the problem of database instance sudden downtime or system recovery when the server is down. background process: 1 DBWR database write operation 2 LGWR log Write Program 3 CKPT Checkpoint 4 SMON System Monitoring 5 PMON Process Monitoring 6 ARCH archive 7 RECO recovery 8 LCKn Block 9 Dnnn Scheduling process 10 Snnn server DB Cache1 DB Cache is a buffer organized in bock units. blocks of different sizes correspond to different buffer parameters. 2. The higher the Cache hit rate, better access performance 3 data blocks in the Cache use the hash algorithm to achieve 4 The number of buffers on each chain, the best case is that there is only one buffer5 DBWR process on each chain to control dirty Data Writing 6 in the DB Cache. The same data block may contain scanning of seven major tables of data of multiple versions, hot block conflicts may lead to chunk contention to introduce tch counters, avoiding frequent moving of LRU chains on the LRU chain to search for the maximum depth, there is not enough clean buffers on the LRU-W will trigger the flag bit, make the DBWR process speak about dirty data written to the disk several important data links: 1 LRU list ordinary LRU chain 2 LRU-AUX list identified clean chain, including DBWR data blocks that have been written back to the file and clean fast 3 LRU-XO list reuse object chain 4 L RU-XR list CKPT searches for these buffers to write dirty data into the chain, and then DBWR writes to disk latches for contention: 1. Hot block conflict 2. When a database has a large number of data block scans and hot links use the keep pool to store large tables in a certain period of time, this can reduce physical reads and improve the cache hit rate by analyzing the owi viewpoint and time model, it helps to analyze the database performance. The low cache hit rate means more physical IO, more use of latches, and lower efficiency. In RAC, more inter-instance communication message DB cache tuning, note that the free buffere waits \ writes complete waits two performance indicators SGA include: 1 Data Structure part fixed size 2 data block buffer database buffers3 redo log buffer redo buffers4 Shared Pool variable size permanent use of shared memory: process information data, session information data, memory segments for special purposes increase with the fragmentation of the Sharing pool: the generation of ORA-4031 Error 1 increase the initial allocation value 2 each time expand the internal structure of a group sharing pool: 1 PERMANENT2 FREEABLE3 RECERATEABLE4 freeable with mark through the internal view X $ KSMSP monitoring Shared Pool fragmentation large sharing pool will bring a certain amount of system overhead Shared Pool latches will also increase regular refresh A pool helps to maintain a stable performance of the Shared Pool. Wait time and number of times of timeout --- determine the degree of fragmentation of the Sharing pool cursor: the results of one parent cursor and different subcursor users are different, so they cannot be shared; hard parsing cannot be shared if the execution plan is different: When the SQL statement is executed, the SQL statement is no longer shared with the pool and will generate hard parsing. First, allocate the Shared Pool space, create a parent cursor, and create a child cursor soft resolution: the parent cursor exists, and the Child cursor also has soft resolution: the parent cursor exists, sub-cursors cannot share the cursor sharing technology ACS cursors will not be closed immediately, and will be placed in the cursor cache first. After the session is completed, the number of opened cursors session cursors will be closed. Main functions: put common SQL statements in the session into the session buffer in UGA, so that the next time you call CURSOR_SPACE_FOR_TIME, when the parent cursor is opened, all child cursors and their associated objects are locked, this ensures that the cursor-related information is consistent.

DBA thinking sky: Understanding the essence of Oracle Database high definition PDF download: