Features for Oracle

Modified date: Monday, June 30, 2025

Introduction
General
Data Types
SQL
Storage and System
Benchmarking
Tools
Export Regulations
Internal
Internal - Optimizer
Internal - Runtime
Internal - Storage Engine

Introduction

In its own words, "Oracle Database is a DBMS developed by Oracle Corporation". It is currently ranked #1 in DB-Engine rankings.

▶️ Online Playground

PostgreSQL Specific Concepts

We list some concpets might be used in the sections to explain things, or words we always heard of.

Concept	Explaination
SGA	The SGA is a shared memory region used by all Oracle server processes. It holds data that needs to be globally accessible, such as data blocks (buffer cache), SQL execution plans (shared pool), undo information, and various caches. The size of SGA is configurable and is crucial for performance.
PGA	PGA is memory allocated to each Oracle server or background process. It is not shared, but private to each process, holding session variables, sort areas, and hash joins data. The more PGA available, the less disk I/O for sorting and joins.
UNDO	Undo stores before-images of data to support transaction rollback and read consistency (Oracle’s MVCC). When a transaction modifies data, the original version is written to undo so that other sessions see consistent data.
Redo Log	Redo logs record all changes made to the database. They ensure data durability and support crash recovery. Even uncommitted changes are written to redo, making it essential for Oracle's ACID compliance.
ASM	ASM is Oracle’s integrated volume manager and file system for managing database files. It automatically handles striping, mirroring, and I/O balancing without requiring third-party storage solutions.
Data Guard	Data Guard provides physical or logical replication for disaster recovery and high availability. Physical standby keeps an exact copy of the primary, while logical standby allows some schema-level divergence.
RAC	Oracle RAC allows multiple servers to mount and operate on the same database simultaneously, providing horizontal scalability and high availability at the database layer.
SCN	SCN is Oracle's internal timestamp mechanism to track database changes. Every committed transaction advances the SCN, and read consistency is based on SCN snapshots.
Tablespace	Logical storage container grouping database objects (tables, indexes, etc.). Tablespaces map to physical datafiles managed by Oracle.
AWR	AWR collects and stores performance statistics, including wait events, execution plans, and system usage metrics. It forms the basis for performance tuning and historical diagnostics.
ASH	ASH samples active sessions every second, providing fine-grained performance data not easily captured by AWR alone. It shows exactly what sessions were waiting on during performance issues.

General

Feature	Value	Definition
intro	Oracle Database is a DBMS developed by Oracle Corporation.	in their own words - but I reserved the rights to remove some bold claims like "the best", unless it is widely recognized.
vendor	Oracle
initial release	1980
latested version	23ai	We don't put a release date here as the software is patching frequently. So tracking it is not much useful.
supported platforms	Windows, Linux, Solaris, HP-UX, AIX, z/OS	supported OS/CPU platforms
db-engines ranking	1	ranks from https://db-engines.com/en/ranking (06/25)
relational?	yes Oracle also has an open source nosql database.	Is it a relational database? (1) Most database are actually with some extensions, for example, nested data types, graph support, etc, which we usually called "multi-model". (2) Some of them are product family, meaning they have more than one database. Here we focus on the main one but explain others when needed.
open source?	no	mainly the engine code
license	commercial
on-premise offering	yes	if no means you can't buy "box" software from them
cloud offering	Oracle Cloud and other cloud vendors
technical doc	https://docs.oracle.com/en/database/

Data Types

Feature	Value	Definition
int: 4-bytes int name	int
char(n): max bytes	2000
BLOB: max size	(4B-1) * pages Page size is defined by DB_BLOCK_SIZE in Oracle, which is ranging from 2K to 32K. This gives BLOB size from 8T to 128TB.
JSON: max size	32MB
Literal: max size	4000	characters or numbers in SQL or PL/SQL

SQL

Feature	Value	Definition
SQL: standard complaince	high
max SQL length	undefined The actual limit may depends on limits of the SUT, for example, its memory/system swap settings: Oracle internally must use a malloc'ed buffer to hold the SQL string.	maximal SQL statement length
PL: main	SQL + PL/SQL	main programming lanage: most database suports SQL because SQL is a well established standard. However, each database would like to extend SQL more or less.
PL: other language support	yes	PL lanaguage other than PL/SQL, like PL/Java, PL/Rust etc
SQL: query hints	complete	if it allows use query hints to guide the optimizer
SQL: explicit locking	yes	Locking is usually an internal matter - so does it allow explicit locking? What levels do they support?
Triggers?	yes	If support triggers
Object-Relational?	some

Storage and System

Feature	Value	Definition
arch: server	C/S Oracle has a family of databases, even with nonsql one.	Embedded or traditional C/S?
arch: run in browser?	no	It also known as a client-side database, is a database that is stored and managed within a user's web browser, rather than on a remote server.
arch: clustering/HA	RAC, Data Guard
arch: in-memory support	Oracle Database In-Memory option
arch: Multi-master support?	yes	if multi-master support?
arch: primary/read replica?	yes	if primary + mulitiple read replica supported
replication: sync/async	both	Can commits wait or w/o wait for replicas to acknowledge
replication: WAL shipping	yes	Uses write-ahead log (WAL) shipping for replication
sessions: max	262143
tables: max number per database	undefined
tables: max number of columns	4096	Max number of columns per table
contraints: max per column	undefined
partitions: methods	Range, List, Hash, Composite (sub-partitions supported).	Supported partitioning strategies (range, list, hash, etc.).
partitions: global index	yes	index across partitions
constraints: max per database	4,294,967,293
rows: max rows per table	undefined	The actual number depends on storage etc
index: max allowed index	undefined	Max number of indices allowed per table
index: max allowable size	6400	Max index record size (bytes). This constraint is mainly coming from the fact of the database page size: if we exclude blob data types, database engine usally do not allow a record expand more than one page.
index: max number of fields	32	Max number of columns allowed in one index
partition: max allowed partitions	1M-1	Meta data challenge: 1M partitions just like 1M tables, system have to hold them in memory. Optimizer challenge: O(N) algorithm may lead to very long planning time if there are excessive partitions.
partition: max allowed key columns	16
partition: max number of subpartitions	1M-1
ACID	yes/yes	for DML and DDL
ACID: max isolation level	SI The Snapshot Isolation (SI) implemented by Oracle allows some anomalies, including write skew. But the SI does satisfies ANSI's serializable definition.
ACID: max ANSI isolation level	Serializable
ACID: durability	yes
Materialized View: support?	yes

Benchmarking

Feature	Value	Definition
any official TPC benchmarks?	yes	The TPC benchmark includes a set of tests simulating real-world scenarios to evaluate database performance.
TPCC: most recent tpmC	8,552,523 System cost: 4,663,073 USD
TPCC: most recent submit date	3/26/2013
TPCC: most recent per thread perf	8352
TPCC: best tpmC	30,249,688 System cost: 30,528,863 USD
TPCC: best perf submit date	12/2/2010
TPCC: best perf per thread perf	2188

Tools

Feature	Value	Definition

Export Regulations

Feature	Value	Definition
Jurisdiction	US	Which country controls export
ECCN	5D992.c	An Export Control Classification Number (ECCN) is a five-character alphanumeric code used to categorize items on the Commerce Control List (CCL) for export control purposes. Most database may fall into 5D992.c category, "mass market encryption", which means it has some ordinary encryption related code, for example, the SSL connection code.
Eligible License Exception / CCATS	NLR NLR essentially means that a commodity has been classified within the Commodity Classification Automated Tracking System and determined to not require a BIS export license for its export.	A License Exception is an authorization that allows you to export or reexport items subject to the EAR without needing to obtain a specific export license, provided certain conditions are met. CCATS stands for Commodity Classification Automated Tracking System. The BIS assigns a CCATS number to products that it has classified under the Commerce Control List (CCL).
Encryption Components	TDE, SSL	Crypto functionality that triggers control

Internal

Feature	Value	Definition
concurrency control	MVCC
MVCC: implemented?	yes	if implement MVCC for concurrency control
MVCC: rollback segment	yes	if uses rollback segment (RS) to store old versions. Without RS, old versions and new versions are mixed stored, then the database engine has to find a way to efficiently drop the old versions at certain point.
Implementation language	C	A DBMS may use mulitple programming languages, for example, supports its stored procedure. The major programming language used to implement the engine.

Internal - Optimizer

Feature	Value	Definition
CBO?	yes	if it employees a cost based optimizer
framework	System-R	System-R is more like a dynamic programming, bottom up optimizer, while Cascades/Volcano gebaseerd optimizer is more like top-down optimizer.
query hints?	complete	if it allows use query hints to guide the optimizer

Internal - Runtime

Feature	Value	Definition
resource management	complete	How execution memory is allocated and limited.
support intra-parallel query?	yes	meaning a single query can utilize multi hardware threads to run it
vectorization	yes	Speed up OLAP queries using vectorized execution. A vectorized runtime exchange a bunch of rows between iterators, and these rows are physically sotre with column-oriented order.
iterator: join methods	all 3	Hash Join (HJ), Sort-Merge Join (MJ) and Nested loop Join (NLJ) are 3 major ones

Internal - Storage Engine

Feature	Value	Definition
Page Size	2K-32K	The physical disk block size for storage and I/O.
partition table	complete	Partitioning capabilities for large tables.
partition table: prunning	both	Ability to eliminate partitions at query compile time and runtime
ACID: durability mechanism	WAL	How is it implements durability. Database's classic way is write ahead logging (WAL).