Please enable JavaScript.
Coggle requires JavaScript to display documents.
Seventh reading, Ariana Vanessa Alvarado Molina - 2021089068 - Coggle…
Seventh reading
Introduction to SQL Tuning
Iterative process of improving the performance of SQL statements.
SQL tuning involves fixing problems in deployed applications.
Once the problem has been identified, we have the following objectives:
Reduce user response time
Improve performance
If you are tuning SQL performance, it is assumed that you have the following knowledge and skills:
Database architecture
SQL and PL/SQL
SQL tuning tools
Tuning depends on many factors, whether the tuning is proactive or reactive.
A SQL adjustment, whether proactive or reactive, generally involves:
Gathering performance-related data
Determining the causes of the problem
Inefficiently designed SQL statements
Suboptimal execution plans
Missing SQL access structures
Stale optimizer statistics
Hardware problems
Defining the scope of the problem
Implementing corrective actions for suboptimally performing SQL statements
Identifying high-load SQL statements
SQL Tuning Tools
A tool is automated if the database itself can provide diagnosis, advice, or corrective actions.
They depend on: dynamic performance views, statistics and metrics collected by the database instance.
Automated SQL Tuning Tools
SQL plan management is a mechanism that can prevent performance regressions.
All tools can use SQL tuning sets as input.
A SQL tuning set (STS) is a database object that includes one or more SQL statements.
Automatic Database Diagnostic Monitor (ADDM)
ADDM is self-diagnostic software built into Oracle Database.
ADDM automatically locates the causes of performance problems.
It identifies areas where no action is required.
SQL Tuning Advisor
SQL Tuning Advisor improves performance by identifying and recommending changes for problematic SQL statements.
The SQL Tuning Advisor improves SQL statements using the Auto Tuning Optimizer.
The advisor performs the following types of analysis:
Checks for missing or outdated statistics.
Creates SQL profiles
Evaluates whether changing the path can significantly improve performance.
Identifies SQL statements.
SQL Access Advisor
SQL Accessor optimizes views and indexes to improve performance.
Automatic Indexing Works
Automatic indexing manages indexes by monitoring load, creating and rebuilding indexes, deleting them as needed, and evaluating their impact on performance.
The task performs the following basic steps:
Creation and evaluation in stealth mode.
Selective reconstruction by the optimizer.
Marked as visible for performance improvements.
Marking as unusable if necessary.
Candidate index identification.
Avoid regressions by managing SQL plans.
Manual index creation requires in-depth knowledge of the model and application.
Automated index management addresses this problem by:
Constant workload monitoring.
Creating new indexes as needed.
Rebuilding and marking indexes as unusable or invisible.
Removing unneeded indexes.
Constant evaluation of performance effects.
Query Optimizer Fundamentals
About SQL Processing
SQL processing: parsing, optimization and execution of SQL statements.
SQL Parsing
First stage: SQL parsing.
It consists of separating the SQL fragments in a data structure.
An application calls the database to execute a SQL statement.
The parse call creates a cursor for the SQL statement and its processing information.
The cursor and the private SQL area are in the program's PGA.
Syntax types
Syntax Check
Oracle Database verifies the syntax of each SQL.
Semantic Check
The semantics of a statement checks whether objects and columns exist.
Shared Pool Check
During analysis, the database checks the cache to optimize processing.
Memory address for the statement
Hash value of an execution plan for the statement
Hard parse If Oracle Database
Soft parse
SQL Optimization
During optimization, Oracle analyzes and optimizes each unique DML statement.
The database does not optimize DDL, except for subqueries that need optimization.
SQL Row Source Generation
Row Source Generator: Receives the optimal execution plan from the optimizer.
Iterative Execution Plan: Produces an iterative execution plan for the rest of the database.
Binary Iterative Plan: binary program that, when executed in SQL, generates the result set.
Combined Steps: The plan takes the form of a combination of steps, where it returns a set of rows.
SQL Execution
In execution, the SQL engine processes the row sources, which is essential for DML.
The SQL engine executes each row source as follows:
The following steps physically retrieve data from a database object.
Table Access Full Departments
Table Access Full Jobs
Index Range Scan
Table Access By Index Rowid employees
The following steps act on the row sources.
Hash Join
Hash Join
How Oracle Database Processes DML
DML with query: when executing a cursor, results are obtained in a set.
How Row Sets Are Fetched
The rows of the set are fetched one by one or in groups.
In the fetching stage, the database selects, sorts and retrieves rows up to the last one.
Read Consistency
A query in Oracle Database ensures read consistency at a single point in time.
Read consistency uses shredded data to retrieve old versions during modifications to a query.
Data Changes
DML modifies data using read consistency with initial criteria.
The statements retrieve blocks, make modifications and generate redo and undo data.
How Oracle Database Processes DDL
Oracle Database processes DDL differently from DML.
When creating a table, the database executes the DDL statement without optimization.
The database uses DDL to define objects in the data dictionary.
Operators: Access Paths and Joins
A row source is affected by a step and manipulated by a SQL operator.
A unary operator affects one input, and a binary operator acts on two outputs.
Introduction to Access Paths
A row source is a set in the execution plan, such as a table, view, or join or grouping result.
Table scan retrieves from one source, while join combines two sources.
Table Access Paths
A table in Oracle is the basic unit of data.
Relational tables:
Organized in heap in no particular order.
Organized by indexes according to the primary key.
External, read-only, with metadata in the database and data outside the database.
About Heap-Organized Table Access
A table is organized as a heap by default, with no specific order.
Rows are added at the first free space, without guaranteeing their retrieval order.
Row Storage in Data Blocks and Segments: A Primer
Rows are stored in data blocks.
An "extent" is a logical set of blocks, and a "segment" stores data and indexes in a table.
Importance of Rowids for Row Access
Each row in a heap table has a unique 10-byte rowid.
This rowid points to the location of the row in the block via an index into the row directory.
Direct Path Reads
In a direct read, the database reads buffers directly to the PGA, without passing through the SGA.
Full Table Scans
A table scan reads all rows of a table -> filters out rows that do not meet the criteria.
When the Optimizer Considers a Full Table Scan
The optimizer chooses a full scan if there are no more efficient options.
How a Full Table Scan Works
In the full scan, blocks are read once under the high water mark.
Table Access by Rowid
The rowid is the precise location of a row in the database.
When the Optimizer Chooses Table Access by Rowid
After indexes, the database accesses the table by rowid, avoiding if the index has all columns.
How Table Access by Rowid Works
Accessing a table by rowid involves getting the rowids of the rows and placing them in the table.
When the Optimizer Chooses a Sample Table Scan
Sample table scan with the keyword SAMPLE.
It can be SAMPLE(sample) for rows or SAMPLE BLOCK(percentage) for blocks.
Table Access Paths
In-Memory Query Controls
You can control in-memory queries using initialization parameters.
INMEMORY_QUERY
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INMEMORY_AWARE
When the Optimizer Chooses an In-Memory Table Scan
The optimizer chooses the most efficient access method, considering the IM memory.
B-Tree Index Access Paths
An index, optional and associated with a table, speeds up data access.
Creating an index on columns facilitates efficient row retrieval, reducing disk I/O.
About B-Tree Index Access
B-Tree Index Structure
A tree index B has two blocks: branch blocks for searching and leaf blocks, which store values.
How Index Storage Affects Index Scans
They appear anywhere in the index segment.
Unique and Nonunique Indexes
In a non-unique index, -> stores the rowid by adding it to the key as another column.
B-Tree Indexes and Nulls
B-tree indexes never store completely null keys.
Index Unique Scans
Returns at most 1 rowid.
When the Optimizer Considers Index Unique Scans
The single index scan occurs with an equality predicate, such as WHERE prod_id=10.
How Index Unique Scans Work
The single index scan searches and stops when the first record is found, using the rowid.
Index Range Scans
Ordered scanning of values.
The range can be delimited on both sides, or not delimited.
The database stores the indexes in ascending order.
Identical keys -> returns in ascending order.
Index Full Scans
Reads the entire index in order.
Remove a separate sort operation.
When the Optimizer Considers Index Full Scans
No predicate, but all columns of the table and query are in the index.
At least one indexed column is not null.
ORDER BY on non-null indexed columns.
How Index Full Scans Work
It reads the root block, then navigates until it reaches a leaf block.
Index Fast Full Scans
Reads the index blocks in unordered order.
Does not use the index to poll the table.
When the Optimizer Considers Index Fast Full Scans
It is taken into account when a query accesses index attributes.
How Index Fast Full Scans Work
The database uses multi-block I/O to read index blocks and obtain entries in leaf blocks.
Index Skip Scans
Occurs when the initial column is "omitted" or not specified.
When the Optimizer Considers Index Skip Scans
Omitting index block scanning is faster.
It is considered if the leading column of a composite index is not in the predicate.
There are many distinct values in the non-main key and few in the main key.
How Index Skip Scans Work
Logically divides an index into smaller subscripts.
Index Join Scans
Hash union of several indexes that return the columns requested by query.
When the Optimizer Considers Index Join Scans
An index join is considered in the following circumstances:
A hash join of multiple indexes.
An index join is usually expensive.
How Index Join Scans Work
Scan the first index to obtain rowids.
Scan the second index to get rowids.
Perform a hash join by rowid to get the rows.
Ariana Vanessa Alvarado Molina - 2021089068