Inventory Oracle Installation Tool OUI to manage the Oracle installation directory
Oracle's parameter file , starting after the parameter file is read in the following order, if the read fails, start the database failure:1 $ORACLE _home/dbs/spfile. ORA2 $ORACLE _home/dbs/spfile.ora3 $ORACLE _home/dbs/init.ora
The Nomount phase of the database :1 Reading the parameter file2 allocating shared memory3 START Process
Mount stage :1 reading control files2 Verifying
Oracle's control files include:1 Physical structure of the database2 Data Files3 REDO LOG
Online log files: resolves a system recovery problem with a sudden outage or server outage of a DB instance
Background process:1 DBWR Database write operations2 LGWR log writer3 CKPT Check Point4 Smon System monitoring5 Pmon Process monitoring6 ARCH Archive7 RECO Recovery8 Lckn Blockade9 dnnn Scheduling processTen snnn Server
DB Cache1 DB Cache is a buffer that is organized in Bock, and blocks of different sizes correspond to different buffer parametersthe higher the hit rate of the 2 DB cache, the better the access performance3 Data blocks in the cache are implemented by hashing algorithms4 The number of buffers on each chain, the best case is only one buffer per chain5 DBWR Process Control dirty data write6 in DB Cache, multiple versions of data may exist in the same data block7 Large table scans, hot block collisions can lead to latch contention introduction of tch counters to avoid frequent movement on LRU chains If the search on the LRU chain reaches the maximum depth, there is not enough clean buffers on the lru-w to trigger the tag bit, which causes the DBWR process to write dirty data to the disk
a few important data chains:1 LRU list common LRU chain2 Lru-aux list confirmed clean chain, including DBWR has been written back to the data block of the file and cleanly fast3 Lru-xo List Reuse object chain4 LRU-XR List ckpt searches for these buffer, writes dirty data to the chain, and writes the DBWR to the disk
latch contention:1 Hot block conflict2 The database has a large number of data block scans, thermal chains at a certain time period use keep pool to store large tables to reduce physical reads and improve cache hit rates use OWI View and time model analysis to help analyze database performance The DB cache hit ratio is low, which means more physical IO, more latch usage, and lower efficiency. More instances of inter-instance communication messages in RAC DB Cache tuning, note free Buffere waits \ Writes total waits two performance indicators The
SGA includes:1 data structure part fixed size2 data Block buffer database buffers3 Redo Log buffer redo buffers4 Shared pool variable size
permanently used shared memory : Process information data, session information data, special-purpose memory segments Increasing
fragmentation of shared pools : generating ORA-4031 errors1 Increase the initial allocation value2 Expand one group at a time
Shared pool internal structure:1 PERMANENT2 freeable3 recerateable4 freeable with MARK monitoring of shared pool fragmentation through internal view X$ksmsp a larger shared pool brings some overheadShared pool latch contention will also increaseperiodically refreshing a shared pool can help keep the shared pool performance stable average wait time for shared pool-related latches, number of timeouts---determine the degree of fragmentation of the shared pool cursors: A parent cursor, multiple child cursors Different users, the results are different from one SQL, so they cannot be shared, the execution plan is different, nor can they be shared hard parse: When SQL is executed, SQL is no longer shared in the pool, resulting in hard parsing. First allocate shared pool space, create a parent cursor, create a child cursorsoft and soft parsing: A parent cursor exists, and a child cursor existsSoft Parse: Parent cursor exists, child cursor is not shareable cursor Sharing technology ACS The cursor is not closed immediately, it is placed in the cursor cache, waiting for the call to complete, and closing the number of cursors opened by the open_cursors sessionsession_cached_cursors callback buffer, main function: Put the common SQL in the session in the UGA session buffer, so that the next call Cursor_space_for_time all child cursors and their associated objects are locked when the parent cursor is opened, ensuring that cursor-related information is consistent
"Interview abuse"--oracle knowledge finishing "DBA's Thought Sky"
