Geist: Die Seite wurde neu angelegt: „== SQL & PERFORMANCE TUNING == - What is SQL Tuning and why we need it? - Oracle Database Architecture - SQL Tuning Basics - Execution Plans in Details -…“

2022-06-20T09:18:33Z

Die Seite wurde neu angelegt: „== SQL & PERFORMANCE TUNING == - What is SQL Tuning and why we need it? - Oracle Database Architecture - SQL Tuning Basics - Execution Plans in Details -…“

Neue Seite

== 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)

=== table partitioning ===

=== 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

[https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams145.htm#REFRN10145 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

SQL TUNING - Versionsgeschichte

Geist: Die Seite wurde neu angelegt: „== SQL & PERFORMANCE TUNING == - What is SQL Tuning and why we need it? - Oracle Database Architecture - SQL Tuning Basics - Execution Plans in Details -…“