Oracle Architecture (theory) __oracle

Excerpts from the video, so it might be a bit messy.

Oracle Core Competencies

Scalability Reliability Manageability Manageability 1, fine-tuning how Oracle uses memory 2, how often Oracle writes data to disk
Introduction to Oracle Architecture storage structure memory structure process structure PGA sort area session cursor area stack area data file features: 1, each data file is associated with only one database 2, a table space can contain one or more data File 3, a data file can only belong to one tablespace log file redo log file (Redo log) function: Log database changes, is the user's transaction log archive log file (Archive log) Role: Backup control file for inactive redo logs: is the binary file used by the Oracle server to identify the physical files and database structure during startup 1, the location and size of the database files 2, the location and size of the redo log files 3, database name and creation time 4, log sequence number The parameter file (Parameter file) action: Used to configure the database to contain text files Init<sid>.ora server parameter files Spfile<sid>.ora temporary files when the instance is started These files do contain data, but they are only for temporary operations

Create temporary tablespace temp_tblspace
tempfile ' D:\db\tblspace.dbf '
size 1M
Extent Management Local
uniform size 512K;

Table SpaceA table space is the largest logical storage structure that corresponds to one or more data files. Contains the system table space (data dictionary information) Sysaux table space (auxiliary system tablespace) for reducing system load and improving the operating efficiency of the System User table Space Index table space temporary table space undo table Space Table Space Characteristics1, control the database disk allocation 2, limit the amount of disk space that users can use in the table Space 3, table space with online, offline, readonly, ReadWrite attribute 4, complete partial database backup and restore 5, table space through data file to expand Modifying table Space Properties

Alter tablespace table space name attribute;

Query Table Space Properties

Select Tablespace_name,status from Dba_tablespaces;

the correspondence between query table space and data file

Select Tablespace_name,bytes,file_name from Dba_data_files;

To move a table in a table space

ALTER TABLE name move tablespace new table space;

Modify user Default table space

Alter user username default tablespace;

Block

Block is the smallest I/O unit used by the database, also known as a logical block or Oracle block.

The structure of the data block contains the size, two parts of the store.

Data Block header (data type, block Physical Address) Table directory (used to record which tables are stored in the data block) row directory (used to record the physical address of each row in the block)Storage AreaA free zone (when a block of data is first allocated, it has only a free area, no rows of data, and as rows are inserted, the idle area becomes smaller) the row data area (this is where the actual rows are stored in the block of data)DistrictPanel quantity and disk size can be established in the storage clause of the object to specify the size of the first disk area that initial set for the object next this is the size of the subsequent panel minextents This is the number of disk areas that are allocated immediately maxextents This is the maximum number of disk areas that can be built for this table
Segment ClassificationThe temporary segment rollback segment of the data (table) segment index segment (the location and value before which the change is stored) is an Oracle revocation mechanism.the data contained in the SGA:1, cached data block 2, SQL statements executed on the database 3, stored procedures executed by the user, functions and triggers, etc.
When Oracle allocates memory termination instances for the SGA when creating an instance or starting an instance, the memory occupied by the SGA is releasedthe composition of the SGADB cache Shared pool redo log cache large pooldata Cache database buffer cache (DBC)Two parameters affecting DBC (main effect read frequency) db_block_size buffer block size db_block_buffers buffer block number
DBC How it works when we first access data, Oracle reads the data files on the disk, puts the data in the data cache, and processes the data if the data is already in the data cache, Oracle can manipulate the data directlyShared PoolLibrary Cache data Dictionary area (Dictionary cache)Shared PoolSaves the most recently executed SQL statements, Pl/sql programs, and data dictionary information, which is a memory area for parsing, compiling, and executing SQL statements and PL/SQL programsLibrary BufferParse the SQL statement or Pl/sql program submitted by the user process and save the most recently parsed SQL statement or Pl/sql programData DictionaryUsed to store data dictionary information row redo log cache storage Redo LogLarge PoolLarge memory operations are required to provide relatively independent memory space in order to improve performance. Operations that require a large pool include database backup and recovery, session memory for shared service processes (e.g., a large number of sorted SQL statements), parallel database operations, and so on.PGAA separate server process stores the area of memory for private data. The sort area saves the permissions, roles, and performance statistics of a temporary data session that occurs when an SQL statement that contains a sort operation is executed by the order by, group by, and so on. Cursor extents for staging data stacks that are generated when executing a PL/SQL statement with a cursor (Cursor) Save the session's binding variables, session variables, and memory structure information when the SQL statement runsthe difference between PGA and SGAThe PGA is similar to the SGA, where the Oracle database system allocates the session in server memory, but the shared program is different.ProcessUser process Oracle ProcessOracle ProcessServer process Background ProcessServer ProcessA request to handle a user process connected to this instance. Tasks: 1, parsing and executing the SQL statements issued by the application 2, reading the necessary blocks of data from disk to the SGA shared database buffer 3, returning the results to the application processingPmon (Process monitoring process)1, monitor the server process to ensure that the destruction of damaged or failed processes, release their resources 2, on the host operating system to register the database server with an Oracle listenerSmon (System monitoring process)Perform routine recovery, merge space debris, release temporary segments, and moreDBWR (Database write process)The data used by the buffer user is written to the data file.LGWR (log write process)The contents of the redo log buffer are written to the log file the log write process performs write operations 1 in the following 4 cases, transactions are submitted 2, redo log cache has populated 1/3 3, the number of redo log caches has reached 1MB 4, every 3 secondsARCH (archive process)Writes transaction changes to the redo log to the archive logCKPT (checkpoint process)The process changes the title of all data files when the checkpoint appears. Note: Usually does not start, if the checkpoint significantly reduces system performance, the CKPT process will run, the original LGWR process to perform the work of the checkpoint separated, by the CKPT process implementationCjqo (Job Queue Coordinator process)Plan a process or job (job) that will run in the background of the database in Oracle For example: Users can tell the database to set up a summary table at 12:00 every night, and in this way, you can summarize the information the following dayreco (recovery process)Recovery processes in a distributed database environment restore those failed distributed transactions