oracle architecture - ghdrako/doc_snipets GitHub Wiki

Instance

System Global Area (SGA)

• Shared Pool – przechowuje buforowane instrukcje SQL i struktury kontrolne.
• Database Buffer Cache – buforuje bloki danych przed zapisaniem na dysk.
• Redo Log Buffer – przechowuje informacje o zmianach przed zapisaniem w plikach Redo Log.
• Large Pool, Java Pool, Streams Pool – dodatkowe bufory dla specjalnych funkcji.

SGA Components

Component	Description
Database buffer cache	Before data stored in the database can be queried or modified, it must be read from a disk and stored in the buffer cache. All user processes connected to the database share access to the buffer cache. For optimal performance, the buffer cache should be large enough to avoid frequent disk I/O operations.
Shared pool	The shared pool caches information that is shared among users:SQL statements that can be reused Information from the data dictionary such as user account data, table and index descriptions, and privileges Stored procedures, which are executable code that is stored in the database
Redo log buffer	This buffer improves performance by caching redo information until it can be written to the physical online redo log files stored on disk..
Large pool	This optional area is used to buffer large I/O requests for various server processes.
In-Memory Area	This optional component contains the In-Memory Column Store (IM column store).The IM column store contains copies of tables, partitions, and materialized views in a columnar format optimized for rapid scans. The IM column store supplements the database buffer cache, which stores data in traditional row format.
Memoptimize Pool	This optional component contains the buffers for use with the MEMOPTIMIZE FOR READ feature for fast lookup.
Java pool	The Java pool is an area of memory that is used for all session-specific Java code and data within the Java Virtual Machine (JVM).
Streams pool	The Streams pool is an area of memory that is used by the Oracle Replication feature.
Result cache	The result cache buffers query results. If a query is run for which the results are stored in the result cache, then the database returns the query results from the result cache instead of rerunning the query. This SGA component speeds the execution of frequently run queries.

Process Global Area (PGA)

Memory region that contains data and control information for a server process. It is nonshared memory created by Oracle Database when a server process is started. Access to the PGA is exclusive to the server process. There is one PGA for each server process. Background processes also allocate their own PGAs. The total PGA memory allocated for all background and server processes attached to an Oracle Database instance is referred to as the total instance PGA memory, and the collection of all individual PGAs is referred to as the total instance PGA, or just instance PGA. The amount of PGA memory used and the contents of the PGA depend on whether the instance is running in dedicated server or shared server mode. The PGA is used to process SQL statements and to hold logon and other session information. A large part of the PGA is dedicated to SQL work areas, which are working memory areas for sorts and other SQL operations.

Procesy tła (Background Processes)

• DBWR (Database Writer) – zapisuje zmodyfikowane bloki danych z bufora do plików bazy danych.
• LGWR (Log Writer) – zapisuje dane z Redo Log Buffer do plików dziennika odzyskiwania.
• CKPT (Checkpoint) – oznacza moment zapisu danych do plików bazy.
• SMON (System Monitor) – przywraca bazę po awarii.
• PMON (Process Monitor) – zarządza uszkodzonymi procesami użytkowników.

Struktura fizyczna

• Pliki danych (Data Files) – przechowują rzeczywiste dane tabel i indeksów.
• Pliki dziennika powtórzeń (Redo Log Files) – zapisują informacje o transakcjach dla możliwości odtworzenia po awarii.
• Pliki kontrolne (Control Files) – zawierają metadane o strukturze bazy.

The files associated with an instance are simply

• Parameter files: These files tell the Oracle instance where to find the control files, and they also specify certain initialization parameters that define how big certain memory structures are, and so on. We will investigate the two options available for storing database parameter files.
• Trace files: These are diagnostic files created by a server process, generally in response to some exceptional error condition.
• Alert files: These are similar to trace files, but they contain information about “expected” events, and they also alert the DBA in a single, centralized file of many database events.

The files that make up the database are

• Datafiles: These are for the database; they hold your tables, indexes, and all other data segment types.
• Temp files: These are used for disk-based sorts and temporary storage.
• Control files: These tell you where the datafiles, temp files, and redo log files are, as well as other relevant metadata about their state. They also contain backup information maintained by RMAN (Recovery Manager, the backup and recovery tool).
• Redo log files: These are your transaction logs.
• Password files: These are used to authenticate users performing administrative activities over the network. We will not discuss these files in any great detail. These files are required if you want to connect remotely (over the network) as the SYS user to an instance. These files are required in a Data Guard configuration (primary and standby database).
• Server-Parameter-File (SPFILE)
• Archived-Redo-Log-Dateien‌ (optional)
• Flashback-Log-Dateien‌ (optional)
• Block-Change-Tracking-File‌ (optional)
• Alertlog- und Incident-Dateien

Struktura logiczna

• Bloki danych (Data Blocks) – najmniejsze jednostki przechowywania.
• Ekstenty (Extents) – grupy bloków danych.
• Segmenty (Segments) – składają się z extentów i przechowują dane tabel lub indeksów.
• Tablespace – logiczna jednostka zarządzania danymi, która grupuje segmenty.

Bloki danych to najmniejsza jednostka pamięci masowej bazy danych Oracle. Rozmiary 2, 4, 8, 16 i 32 kb są dozwolone w Oracle 19c.

Extent to grupy bloków danych, które są przechowywane w w pliku jako sojny ciągły obszar

Typy segmentow:

• CLUSTER‌
• INDEX‌
• INDEX PARTITION‌
• LOB PARTITION‌
• LOB INDEX‌
• LOB SEGMENT‌
• NESTED TABLE‌
• ROLLBACK SEGMENT‌
• TABLE
• TABLE PARTITION
• TYPE2 UNDO‌

Przestrzenie tabel to kontenery na obiekty, takie jak tabele i indeksy. Obiekty te można umieszczać w różnych przestrzeniach tabel w oparciu o różne kryteria, takie jak logika aplikacji lub wydajność. Przestrzeń tabel SYSTEM zawiera między innymi katalog bazy danych. Przestrzeń tabel SYSAUX jest przeznaczona dla repozytoriów i tabel narzędzi. Przestrzeń tabel TEMPORARY służy do przechowywania tymczasowych segmentów. Przestrzeń tabelowa może składać się z jednego lub większej liczby plików danych.