SQL TUNING

SQL & PERFORMANCE TUNING

- What is SQL Tuning and why we need it?
- Oracle Database Architecture
- SQL Tuning Basics
- Execution Plans in Details
- Join Operations
- Basic Tuning Techniques
- Advanced Indexing Techniques
- Hints
- Subjects

Change SQL-Structure or Database-Structure
Need to know of the Database Architecture in Basic

Tools
- SQL plus
- SQL Developer
- SQL Tuning Advisor
is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.
- Automatic SQL Tuning Advisor
- SQL Access Advisor 
is internal diagnostic software that recommends which materialized views, indexes, and  materialized view logs to create, drop, or retain.
- Automatic Database Diagnostic Monitor (ADDM)
ADDM is self-diagnostic software built into Oracle Database.
- TOAD
- AWR
- TKPROF

- Determine the bottlenecks in low-performance Oracle databases
- Use Oracle database performance tuning tools, like: AWR, ASH, V$ views, SQL tracing, real-time monitoring, and EM Express
- Propose solutions to database performance issues
- Implement Oracle methodology in performance tuning

SQL Tuning is a continuous process
You need to tune your queris
- While creating
- After the creating

When to decide tuning?
- By checking the top consuming queries frequently
- After any complaints of bad performance

The reasons of performance loss
- Structural changes
- Changes on the data volume
- Application changes
- Aged statistics
- Database upgrades
- Database parameter changes
- Operating system changes
- hardware changes

Blocks

All data stored in blocks
smallest unit of database storage
2KB-32KB(default 8KB)
stores row data or index data

Eigenschaften:
- Block Header
  Block Type Information
  Table Information
  Row Directory
    ROWID(Like pointer?)
- Rows
Blocks in Blocks
Jeder Block wird in eine bestimmte Stelle geschrieben

PCTFREE or PCTUSE parameters for the space size in blocks

Wenn nicht genug Platz, wird bei jedem update wahrscheinlich die Position geändert
- Verringerte Leistung 
- IO-OPS werden langsamer
- Schlecht für das Tuning
- Verlangsamt die Indexes

Das lesen einer Zeile, von einer Tabelle aus einem Block, ist schneller als aus mehreren Blöcken

1. Row Header
2. Column Data

- Row Overhead
- Number of Columns
- Cluster Key ID(if clustered)
- ROWID of chained Row Pieces(if any)
- Column Length
- Column Value

Wenn die größe der Blocks in den Abfragen verringert wird, steigert man die Leistung

PGA | PROGRAM/PROCESS/PRIVATE GLOBAL AREA

Private for each user

4 Hauptspeicher Bereiche:
PGA
Session AREA
   - Session info for each user
   - Session Variables, login info, session status, etc.
Unnötige verbindungen vermeiden

PSA(Private SQL Area)
PERSISTENT AREA
  - Jede Abfrage wird zu einem Cursor
  - Verwendete Bindvariablen der Cursor werden hier gespeichert
RUNTIME AREA
  - Execution state info
CURSOR AREA
- Information of cursors

SQL WORK AREA
SORT AREA
HASH JOIN AREA
BITMAP MERGE AREA
BITMAP CREATE AREA

Weniger Speicher verringert die LEistung

Speichergröße festlegen
- User
- Oracle selber

SGA | SHARED/SYSTEM GLOBAL AREA

Database buffer cache
Redo log buffer
Shared pool
 - Data dictionary cache
 - Result Cache
 - other
Large pool
- I/O Buffer
- Free memory
- Response queue
- Request queue
JAVA pool
Streams pool

Context switching between pl/sql engine and sql engine

SHARED POOL

DATA DICTIONARY CACHE
- Stores the definitions of the database object and permissions
  - check if Data from query exists
  - check your privileges

RESULT CACHE
- stores result of common used queries
- stores result of functin
  - Bei wiederholter Abfrage wird aus dem RESULT CACHE gelesen
  - Speicher die Abfrageergebnisse
  - Speicher die Funktionsergebnisse

Erhöht die Leistung
Wird von der Datenbank durchgeführt
User kann anweisen Result Cache anweisen daten zu speichern

Library Cache
- Stores the execution plans
- Stores procedures, packages control structures
  Oracle erstellt Ausführungspläne
  Wie die Daten aus der Disc oder dem Puffer gelesen werden
  Die Erstellung eines Ausführungsplans ist ein kostspieliger Vorgang
  Die Verwendung eines Ausführungsplans für ähnliche Abfragen kann sehr effizient sein

OTHER AREAS

BUFFER CACHE

Largest memory area of sga
Stores the copies of the blocks read from the disc
Available for all the users
First check Buffer than Disc, for the data
Check Disc only if blocks are missing
Much faster than disc
Size of the memory is endless
Maintained with a complex algorithm
Can't save all data in buffer cache

IN-MEMORY-DATABASES
Stores the most recently used & most toched ones
Database writer process handles the write operations to the disc
Writes not one by one, writes all changed blocks in one step
dirty blocks
Stores Tabledata and Indexdata

Performance for same
- queries
- tables
- indexes

REDO LOG BUFFER

-> Log Writer Process(LGWR)
-> Redo Log Entries
-> Redo Log Files
-> Server Process 

Redo-Log-Files
Oracle Guarantees Not to Lose Data
- A Redo Log Entry is created when insert, update, delete, create, alter, drop occurs
Create Redo Log entries for all changes
Redo Log Entries has the changes made to the database
They are used for recovery operatins
You need privileges as a developer
Redo Log Entries are Stored in the redo log buffer
Starts every 3 sec and write to disc
Redo Log buffer is a circular buffer
Rollback is not done with redo log data
Redo do recovery on rollback redo log files are deleted

UNDO

What is rollback and recovery
The original data stored into the memory(undo tablespace) is called as undo data
buffer cache for the modifications
undo data is not modified because
  - Used for rollback operations
  - Used for providing read consistency
  - Used for providing flashback feature

BLOCKS > EXTENTS > SEGMENTS > TABLESPACE

DML PROCESS AND COMMIT

Optimize Queries and DML's
- Checks the Shared SQL AREA for similar statements to use
- checks the data dictionary cache and checks if query is valid
- Checks buffer cache & undo segements for the related data
- Locks the related blocks
- Makes the changes to the blocks in the buffer cache
- the changes are applied to the redo log buffer before the buffer cache
- the server returns the feedback for the changes

When the user commits:
- System change number write to redo log files
- the server creates a commitrecord with scn
- the lgwr process write redo log entries in the redo log buffer to the redo log files
- the dbwn writes the dirty blocks to the disc & unlocks the blocks
- The server returns a feedback about the transaction completion

Automatic memory management

The size of each memory ara is important for the execution performance of your queries
Wenn des Speicherbereich nicht ausreicht verringert das die performance
It can manage both SGA & PGA memory 
In früheren versionen von oracle musste PGA manuell angegeben werden
it is recommended to leave automatic memory management enabled to increase the performance
will prefent out of memory errors

Oracle database storage architecture

- Storage = Discs
- Control files storage the pyhsical structure information of the database(without no access to data)
- Data Files - Stores data(Tables, procedures, application, data
- Online Redo log files: stores redo log entries
- archived redo log files - online redo log files are constantly mover ehre
- backup files - Stores the exact copy of the data files for disaster recovery
- Parameter files - Stores the configuration data of the database instance
- password file - Stores the passwords of the amin user(sysdba,sysoper,sysasm)
- alert log & trace files - stores log message and errors occured in the database

Some Informations are for the dev's and some for the oracle support service

Logical and Physical Database structure

- Blocks | smallest units of storage(2kb-32kb)
- Extents | combination of several consecutive data blocks.
  used for stroing secific type of info
- segments | combination of several extents
 used for storing some big data(tables,indexes, etc)
  - data segments
  - index segments
  - undo segments
  - temporary segments
- tablespaces | combination of many segments. used for grouping the related data in one container
 - Temporary Tabelspace
   Stores teh temporary data of a session
 - Permanent Tablespace
   Stores the persistent schema objects

DB must have min 2 Tablespaces
- System Tablespace
- SYSAUX Tablespace
DBA can create more Tablespaces

Check for Performance
Set Tablespaces Online and Offline Status
Can do backup or recovery of a give tablespace
Import or export Tablespaces
Create a transportable tablespace and move too other db

Schema

Ein Schema ist eine Sammlung von Datenbankobjekten die einem Datenbankbenutzer gehören

PERFORMANCE TUNING BASICS

SQL Tuning is a continuous process
You nedd to tune your queries
 - on creating
 - After the creation
 - On new Indexes created
 - Change of data volume

When to decide tuning?
 - checking the top consuming queries frequently
 - after any complaints of bad performance

two goals in tuning:
- reduce user response time, decreasing time between statement and response
- Improve throughput

Knowledge and skills:
- know your db architecture
- sql
- sql tuning tools

Check oracle database statistics

Betriebssystem
- Software
- Services
- Updates
- Datenbank, Oracle Datenbank Architektur

Häufige Ursache für Performance Verlust, bei Änderung von:
- Tabellen, DB Struktur
- Datenvolumen
- Applikationen
- Datenbank-Update
- Datenbank-Parameter
- Betriebssystem
- Betriebssystem-Update
- Betriebssystem-Konfiguration
- Hardware

Welche Oracle Packages für:
- Modularity
- Easy Maintenance
- Encapsulation & Security
- Performance
- Functionality
- Overloading

Tuning-Maßnahmen | SQL & PERFORMANCE TUNING
- SQL Tuning Basics
- Execution Plans in Details
- Join Operations
- Basic Tuning Techniques
- Advanced Indexing Techniques
- Hints
- Subjects

BAD SQL

Unnecessary:
- parse time
- I/O operations
- CPU Time
- waits

TIME on Wait(CPU) + Time on Execution = DB TIME
The reason of a bad sql
- bad design
- poor coding
- inefficient execution plan

Effective Schema Design

- Assign data types as much as needed
- datatypes with variable-length
- check data-consistency problems
- select exactly the same data type between parent-child-keys
- don't use varchar2 for the most of the datatypes/strings/values

Enforce data integrity
- correct PK and FK
- use normalization well
- smaller tables, faster join operation
- select right table type
  - heap-organized table
  - Index Clustered tables
  - Hash Clustered tables
  - Index-Organized tables
  - External tables
  - and more
- Create Clusters
- use indexes often and select index type carefully
- create good indexes methods
- Create index-organized tables(IOT)

SQL STATEMENT PROCESSED

-> Syntax check
-> Semantic check
-> Privilege check
-> Allocate private SQL AREA
Existing shared SQL AREA
NO-> HARDPARSE(Library cache miss)
 -> Allocate shared sql area
 -> optimization
 -> row source generation
YES-> SOFTPARSE
-> EXECUTE STATEMENT

Softparse is faster, use for tuning
- Hashwert in Libcache
 - Key for the executionplan in shared sql area
- executionplan is stored in shared library

Optimierer ist Software die Abfrage als Eingabe erhält und den besten ExecutionPlan erstellt

Full-table scan
Index scan

1. Optimization
2. Execution plans
3. Row Source Generation

Result-Cache

Select * from... STEPS
-> Check Schema Information
-> Find possible access paths
  -> Use Index
  -> Read whole table
-> check statistics

selective query

Don't use Index if Result is bigger than 25% of the whole table?

Access Methods

- Table Access Path
  - Full-Table scan
  - Table Access by ROWID
- B-Tree Indexx Access Path
  - Index unique scan
  - Index range scan
  - Index skip scan
  - Full Index scan
- Bitmap index Access Path
- Table Cluster Access Path

In-List- Iterator

Optimizer overview

- Optimizer
- RBO | RuleBasedO(not in use anymore since 10g)
- CBO | CostBaseO
  Plan is dynamic
-> Query Transformer(Transform Query)
-> Estimator(Query+estimates)
^-> Plan Generator(Query plan to row source generation)

Query Transformer

Query transforms the query into a better performing one but semantically equivalent of it
If the transform isn't better, it use the original one
Time restriction and old statistics may lead a wrong plan creation
Or Expansion
 Using or in the where clause will prevent index usage
 
SELECT * FROM ID WHERE ID = 1 OR ID = 2;
Faster:
SELECT * FROM ID WHERE ID = 1;
UNION ALL
SELECT * FROM ID WHERE ID = 2;

Subquery Unnesting
Verschachtelte Abfrage in Join-Anweisung
SELECT * FROM ID WHERE ID IN
   (SELECT ID FROM CLIENTS)
The otimizer transfomrs a nested query into a join statement
SELECT ID,*
FROM ID, CLIENTS
WHERE ID.ID = CLIENTS.ID;

Complex VIEW

Selectivity & Cardinality

Selectivity = NUMBER OF ROWS FROM QUERY / TOTAL NUMBERS OF THE ROWS
Cardinality = Total numbers of rows x Selectivity

Why selectivity and cardinality is important?
- SEL affects the estimates in I/O cost
- SEL affects the sort cost
- CAR is used to determine join, sort and filter costs
- Incorrect SEL and CAR = incorrect plan cost estimation

COST IN DETAIL

- Cost is the optimizer's best estimate of the numbers of I/O to execute statement
- To estimate the cost, the estimator uses:
 - Disk I/O
 - CPU usage
 - Memory usage

COST = SINGLE-BLOCK I/O COST + MULTI-BLOCK I/O COST + CPU COST / SINGLE-BLOCK READ TIME
- SINGEL BLOCK READ TIME
- MULTI BLOCK READ TIME
- CPU CYCLES / CPU SPEED

PLAN GENERATOR

Basierend auf ausprobieren von verschiedenen Zugriffspfaden,JOIN-Methoden und JOIN-Reihenfolgen

ROW SOURCE GENERATOR

once the plan generator generates the optimum plan, it handles that to the row source generator
generates an interative execution plan usabel for the database
is an area that we get the row set(Table,view,result of join or groups)
row source tree show the following information:
- execution order
- access methods
- join methods
- data operatons(filter,sort,...)

SQL TUNING PRINICPLES AND STRATEGIES

SQL TUNING PRINCIPALS
- IDENTIFYING the issue
- carify teh details of that issue
- collection data
- analyze the data
- choose an appropriate tuning strategy

SQL TUNING STRATEGIES
- Parse time reduction
- plan comparison strategy
- quick solution strategy
- finding & implementing a good plan
- Query analysis strategy

SQL TUNING ADVISOR(braucht zugriffsrechte)
otimierungsmodus ändern
index-caching
etc..

Use dynamic stats to create better plans

QUERY ANALYSIS STRATEGIE

- quick tuning strategies did not work, and we have time to work on this problem
- query can be modified
- determine the underlying cause

What to do on this strategy
- statistics and parameters
- query structure
- access paths
- join orders & join methods(changes?)
- others

Collecting Data
- Execution plan
- information of the objects in the query
- collecting statistics
  - object stats
  - systems stats
- histograms
The available tools are
- sqlt
- dbms_stats
- tkprof
- awr report
- etc..

Systemstatistiken und Histogramme

PRE-ANALYZE of the Query
- check the
  - volumes of resulting data
  - predicates
  - problematic constructs
    - OUTER JOIN
    - VIEWS
    - SUBQUERIES
    - IN or OR list
    - Hierarchical queries

Analyzing the execution plan
Tools to get the execution plan
  - SQL TRACE
  - TKPROF
  - V$_SQL_PLAN
  - DBMS_MONITOR
  - AWRSQRPT.SQL
  - etc..

How to read the execution plan:
- check the access paths
- check the join order and the join type
- compare actual & estimated number of rows
- find the steps where cost and logical reads are different

Analyzing by considering the query tuning techniques

Find a possible solution
- updating statitics
- using dynamic stats
- creating or re-create an index
- creating index-organized tables(IOT)
- using hints
- others

EXECUTION PLAN AND EXPLAIN IN DETAIL

An execution plan is a list of steps how to execute an sql statement

- Operations
- Object_name
- Cardinality
- Cost
- Partition_Start
- Partition_Stop

Stat types
- system stats
- optimizer stats
System stats
 - used by the optimizer to estimate I/O and CPU
 - costs
 - should be generated regularly
- shoud be gathered during a normal workload

regenaration/recreate on every hardware change the system stats

Access Predicates
Filter Preddiactes

USE OF:
EXEC dbms_stats.gather_system_stats('Start');
Prozedur
collect_system_stats()

Berechtigung auf Tabelle aux_stats$

Optimizer Statitics
- can be gathered manually or autmatically

Dynamic stats?

Job für Automatische Erfassung der Statistiken

ANALYZE TABLE [TABLE_NAME] COMPUTE STATISTICS;
Sollte nicht mehr genutzt werden

DBMS_STATS package
PROCEDURES:
GATHER_DATABASE_STATS 
GATHER_DICTIONARY_STATS
GATHER_SCHEMA_STATS
GATHER_TABLE_STATS | very fast
GATHER_INDEX_STATS | faster for singel indexes

How can see the optimizer statistics
- DBA_TABLES
- DBA_TAB_STATISTICS
- DBA_COL_STATISTICS
- DBA_INDEXES
- DBA_CLUSTERS
- DBA_TAB_PARTITIONS
- DBA_IND_PARTITIONS
- DBA_PART_COL_STATISTICS

Generating Execution plan

To analyze an execution plan
  - explain plan
  - autotrace
  - V$SQL_PLAN
EXPLAIN PLAN
EXPLAN PLAN FOR [QUERY];
generates the explain plan and save into plan_table

select * from plan_table;

AUTOTRACE

SET AUTOTRACE ON;
SET AUTOTRACE ON [EXPLAIN|STATISTICS];
SET AUTOTRACE TRACE[ONLY] ON [EXPLAIN|STATISTICS];
SET AUTOTRACE OFF;

Autotrace uses plan table like the explain plan
Bindvariablen mit implizierter typenkonvertierung, klappt manchmal nicht

SQLPLUS>> 
SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from sales s, customers c where s.cust_id = c.cust_id and s.cust_id = 987;
SQL> set autotrace traceonly statistics;
SQL> set autotrace traceonly statistics;
SQL> set autotrace traceonly;
SQL> set autotrace on;
SQL> show autotrace;

AUTOTRACE WITH SQL DEVELOPER

Kann man den Code nicht durch generieren des explain-plans optimieren, nutzt man autotrace-methoden
Bei DML-OPS zuerst den EXPLAIN-PLAN

V$SQL_PLAN VIEW

There are a lot of performance views tha can be used for tuning
- V$SQLAREA
- V$SQL_WORKAREA
- V$SQL
- V$SQL_PLAN
- V$SQL_PLAN_STATISTICS
- V$SQL_PLAN_STATISTICS_ALL

V$SQL_PLAN
- Actaul execution plans are stored here
- It is very similar to plan_table
Wenn es eine Unterschied zwischen beiden gibt, gilt der Wert von V$SQL_PLAN als wahr
- V$SQL view
Sie können beide die Tabellenfunktin und das dbms_xplan Paket aufrufen für eine gute Ausgabe

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('XXXXX'));

READING THE EXECUTION PLANS

Zugriffsprädikate werden für die Zeilenquellen verwendet
Bitmap-KOnvertierung in ROWIDS die ROWIDS aus der eingehenden Quelle
Tabellen Zugrif durch lokale Index-ROWID
Oft werden 0 Costs angezeigt, welche aber nicht 0 sind sondern einfach nur zu gering

2 Arten von Optimierung
- kostenbasierte | COST-BASED-OPTIMIZATION (Empfehlung von Oracle)
- regelbasierte  | RULE-BASED-OPTIMIZATION (Alte Art, nicht so genau)

ALL_ROWS
ANALYZED

first_rows & .... und später den rest, schnellere Reaktionszeit

Parameter type 	String
Syntax 	OPTIMIZER_MODE =

{ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value 	all_rows
Modifiable 	ALTER SESSION, ALTER SYSTEM

OPTIMIZER_MODE

Detailierte Tuning Strategie vs schnelle Tuning Strategie

Where to look?
- Cost
- Access Methods
- Cardinality
- JOIN Methods & JOIN TYPES
- Partition Pruning
- Others
##??
- Parse count
- Number of fetches
- etc..

Optimizer hints

- To command the optimizer, we use optimizer hints
- optimizer hints force the optimizer to pick a specific action
- The optimizer may not follow your hints
- If hint is not reasonable, the optimizer will ignore it
- Hints can be operating on a single hash, multi-tables, a query block, a specific statement

Categories of the hints:
- Hints for optimization approaches
- Access paths hints
- Query Transformation Hints
- Join order Hints
- Parallel Execution hint
- Others..

SELECT /*+ hint_name(para1 para2 para3) */ id from db;
- hints can used after a select, update or delete keywords
- you can use the table name or its alias as the hint parameter. but if there is an alias, you cannot use the table name
- There can be only one hint area
- be careful on the hints you selected, especially if you are using multiple hints
  you may lead the optimizer to a bad execution plan

Gathering stats

optimizer statistics are crucialto sql tuning

Search for * in execution plan

- cardinality
- Access method
- Join method
- Join type
- join order
- Partition pruning
- parallel execution