Transcript Week 1, Lecture 1

Chapter 2
Oracle Architecture & Concepts
1
The Oracle Architecture

Need to understand Oracle database
technology to effectively tune, including:
–
–
–
–
2
Oracle API’s
The Oracle Query Optimizer
Oracle Server Architecture
Application schema objects & segments
The Oracle API’s


Application Programming Interface (API)
Common API’s include:
–
–
–
–
–
3
Oracle Call Interface (OCI)
Java Database Connectivity (JDBC)
.NET programs
Open source languages (e.g. Perl, Python)
PL/SQL
The Oracle API’s (cont.)

API interface includes common set of steps:
–
–
–
–
–
–
–
4
Create a cursor
Check if cached
Parse SQL (if necessary)
Assign bind variables
Execute SQL
Fetch rows
Close the cursor
The Cursor


Also known as “context area”
Is an area of memory where SQL is stored
–
–
–


5
Includes parsed and unparsed versions
Includes execution plan
Includes pointer to current row
Is freed upon completion
Corresponds to an SQL object / statement handle
Parsing the SQL




6
Checks that SQL is syntactically valid
Ensure objects referenced in SQL exist
Verifies user’s permission to objects
Determines execution plan for statement
Associating Bind Variables


SQL contains variable values that change from execution
to execution
These are represented as either:
–
Literals
Select first_name, last_name
From hr.employees
Where employee_id = 206
–
Bind variables
Select first_name, last_name
From hr.employees
Where employee_id = :bind_employee_number
7
Associating Bind Variables (cont.)

Statements with literals change with each
execution
–

Statements with bind variables do not change
–


8
i.e. the “hash” value of the statement itself changes
i.e. the “hash” value of the statement remains the same
Shared pool finds statement matches in memory
based on this “hash” value
Bind variables are typically prefaces with a colon
Associating Bind Variables (cont.)

Key reasons to use bind variables:
–
–

Key reasons NOT to use literals:
–
–
–
9
Reduces SQL Parsing
Any session can use the SQL stored in memory
(shared easily)
Every execution requires SQL Parsing
Shared Pool fills with non-sharable SQL statements
New SQL statements require internal locks
Optimizing Oracle API Calls



Ensure SQL statements are reused within the
application
Use Bind Variables
Enable array fetching
–
–
–
10
Improves performance
Reduces the number of calls to Oracle
Reduces network traffic
The Oracle Query Optimizer



An Oracle-provided set of algorithms
Determines how data is retrieved from database
Oracle supports cost-based optimization (10g or
greater)
–
–

Rule-based optimization supported in older versions
–
11

Calculates cost of each alternative algorithmically
Chooses cheapest method based on these algorithms
Based on set of predefined rules (e.g. using indexes are better
than table scans)
Optimizer determine the “execution plan” for query
The Oracle Query Optimizer (cont.)

The cost-based optimizer (CBO) is determined based
on various factors:
–
–
–
–
–

12
Estimated number of database reads
Sorting requirements
Memory
CPU and IO
Query parallelism
Chooses best option based on analysis of many of
the possible execution paths
The Oracle Query Optimizer (cont.)

OPTIMIZER_GOAL parameter
–
ALL_ROWS


–
FIRST_ROWS


Chooses best plan that would return “first n” rows for statement
Optimizer statistics: metadata about your data
–
–
–
–
13
Chooses cheapest plan that would return all rows for statement
Is the default value
–
Number of rows & blocks
Density of blocks
Average row length
Cardinality of column data
Distribution of values for column data (histograms)
The Oracle Query Optimizer (cont.)

Bind variable peeking
–

Hints
–

Within SQL, telling the optimizer the execution path you want
Outlines, Profiles, & Baselines
–
–
–
14
Looking at first variable to help determine execution plan
Allows consistent execution plans for a given statement
Also known as “plan stability”
Gives predictable, repeatable execution plans to queries
Oracle Server Architecture





15
Instances vs. databases
The System Global Area (SGA)
The Program Global Area (PGA)
Data Caching
Memory Management
Segments and Files

The different types of tables
–
–
–
–
–
–

The different types of indexes
–
–
–
16
Heap-organized
Clustered & Nested
Index organized (IOT)
Object
External
Temporary
–
B-Tree
Bitmap
Function-based
Virtual
Segments and Files

Structural Hierarchy
–
–
–
–
–

Logging
–
–
–
17
Blocks
Extents
Segments
Tablespaces
Data Files
Redo
Archived Redo
Flashback
Oracle Processes

Server Processes
–
–
–

Background Processes
–
–
–
–
–
–
18
Dedicated vs. Shared
Parallel Query
Job and Scheduler
Database Writer
Log Writer
Archiver
Recovery Writer
Process & System Monitor
A couple of dozen in all
Real Application Clusters






19
aka RAC
Shared-disk clustered database
Single set of database files
Multiple Instances
Connected via ‘cluster interconnect’
Done for high availability & load balancing