Hauptseite: Unterschied zwischen den Versionen

Aktuelle Version vom 10. November 2023, 16:49 Uhr

Inhaltsverzeichnis

1 A
2 B
3 C
4 D

5 F

5.1 Fail2Ban

5.2 FritzBox

6 G

6.1 Git

6.2 GNU/LINUX

6.3 GnuPG/OpenPGP/PGP

6.4 GRUB

7 H

7.1 Hacking-Methoden

8 I

8.1 Image mount

8.2 IP

8.3 IT Berufstitel

9 J

9.1 Jenkins

10 K

10.1 KanBoard

10.2 Kate

11 L

11.1 Linux Build

11.2 Luks

11.3 LXC LXD

12 M

12.1 MariaDB

12.2 MediaWiki

13 N

13.1 NFT

13.2 NMAP

13.3 NTFS

14 O

14.1 OpSec

14.2 OpenSSL

14.3 Oracle

15 P

15.1 PATH

15.2 PHP

15.3 Prometheus

16 Q

16.1 quantum attacks

17 R

17.1 RSYNC

18 S

18.1 Scrum

18.2 Secure-Hash-Cookie

18.3 snap-snapd-snappy

18.4 SQL

18.5 ssh

18.6 SQL TUNING

19 T

19.1 TAR

19.2 TCPIP

19.3 TWRP

20 U

20.1 UUID

21 V

21.1 Vagrant

22 W

22.1 WEBRTC

22.2 Wireshark

23 Y

23.1 Youtube

24 Z

24.1 z4o

A
ADB
Agile
Alpine
Apache
Anacron
Android
API & RESTful API
apt sources.list
apt
B
Backup
bash
Bitcoin
blkid
Blockchain
Blockchain Office
Borg
C
Cookie
Cron
Crypto
Cryptographic Hash Algorithm
D
Debian
DevOps
Docker
Dockerfile
Dolphin KDE
F
Fail2Ban
FritzBox
G
Git
GNU/LINUX
GnuPG/OpenPGP/PGP
GRUB
H
Hacking-Methoden
I
Image mount
IP
IT Berufstitel
J
Jenkins
K
KanBoard
Kate
L
Linux Build
Luks
LXC LXD
M
MariaDB
MediaWiki
N
NFT
NMAP
NTFS
O
OpSec
OpenSSL
Oracle
P
PATH
PHP
Prometheus
Q
quantum attacks
R
RSYNC
S
Scrum
Secure-Hash-Cookie
snap-snapd-snappy
SQL
ssh
SQL TUNING
T
TAR
TCPIP
TWRP
U
UUID
V
Vagrant
W
WEBRTC
Wireshark
Y
Youtube
Z
z4o

@@ Zeile 6: / Zeile 6: @@
 ==='''[[Anacron]]'''===
 ==='''[[Android]]'''===
+==='''[[API & RESTful API]]'''===
 ==='''[[apt sources.list]]'''===
 ==='''[[apt]]'''===
@@ Zeile 15: / Zeile 16: @@
 ==='''[[blkid]]'''===
 ==='''[[Blockchain]]'''===
+==='''[[Blockchain Office]]'''===
 ==='''[[Borg]]'''===
 == ''' C ''' ==
+==='''[[Cookie]]'''===
 ==='''[[Cron]]'''===
 ==='''[[Crypto]]'''===
@@ Zeile 31: / Zeile 34: @@
 == ''' F ''' ==
 ==='''[[Fail2Ban]]'''===
+==='''[[FritzBox]]'''===
 == ''' G ''' ==
@@ Zeile 44: / Zeile 48: @@
 ==='''[[Image mount]]'''===
 ==='''[[IP]]'''===
+==='''[[IT Berufstitel]]'''===
 == ''' J ''' ==
@@ Zeile 72: / Zeile 77: @@
 == ''' P ''' ==
+==='''[[PATH]]'''===
 ==='''[[PHP]]'''===
 ==='''[[Prometheus]]'''===
@@ Zeile 87: / Zeile 93: @@
 ==='''[[SQL]]'''===
 ==='''[[ssh]]'''===
-== SQL & PERFORMANCE TUNING ==
+==='''[[SQL 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
-KB-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
-. Row Header
-. 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
-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
 == ''' T ''' ==

Hauptseite: Unterschied zwischen den Versionen

Aktuelle Version vom 10. November 2023, 16:49 Uhr

Inhaltsverzeichnis

A

B

C

D

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

Y

Z

Navigationsmenü

Suche