Instanzoptimierung der ORACLE-Datenbankleistung

Dieser Artikel stellt hauptsächlich die Analyse von ORACLE-Datenbankleistungsoptimierungsproblemen und -lösungen anhand von Beispielen vor. Ich hoffe, dass er für alle hilfreich ist. Die Optimierungsmethode der ORACLE-Datenbank unterscheidet sich stark von der von MYSQL. Heute werden wir anhand einer ORACLE-Datenbankinstanz analysieren, wie die ORACLE-Datenbank unter verschiedenen Aspekten wie Tabellen und Daten optimiert werden kann.

tsfree.sql view

Diese SQL-Anweisung vergleicht schnell die Gesamtmenge an Speicherplatz in jedem Tabellenbereich mit der Gesamtmenge an verfügbarem Speicherplatz in jedem Tabellenbereich

Tabellenbereich ist eine logische Unterteilung der Datenbank. Ein Tabellenbereich kann nur zu einer Datenbank gehören. Alle Datenbankobjekte werden im angegebenen Tabellenbereich gespeichert. Da er jedoch hauptsächlich Tabellen speichert, wird er Tabellenbereich genannt.

SELECT FS.TABLESPACE_NAME "Talbspace",
(DF.TOTALSPACE - FS.FREESPACE) "Userd MB",
FS.FREESPACE "Free MB",
DF.TOTALSPACE "Total MB",
ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) "Pct Free" FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM
DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME;

Verwendung der Varray-Tabelle

CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT (E_NAME VARCHAR(40));

CREATE OR REPLACE TYPE PRIOR_EMPLOYER_NAME_ARR AS VARRAY(10) OF 
EMPLOYER_NAME;

CREATE OR REPLACE TYPE FULL_MAILLING_ADRESS_TYPE AS OBJECT(STREET 
VARCHAR2(80), CITY VARCHAR2(80), STATE CHAR(2), ZIP VARCHAR2(10));

CREATE OR REPLACE TYPE EMPLOYEE AS OBJECT(LAST_NAME VARCHAR(40), 
FULL_ADDRESS FULL_MAILLING_ADRESS_TYPE, PRIOR_EMPLOYERS 
PRIOR_EMPLOYER_NAME_ARR);

CREATE TABLE EMP OF EMPLOYEE;

INSERT INTO EMP VALUES('Jim', FULL_MAILLING_ADRESS_TYPE('Airplan Ave', 'Rocky', 'NC', '2343'), PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('APPLE'), EMPLOYER_NAME('CNN')));

--Rollback

DROP TYPE PRIOR_EMPLOYER_NAME_ARR FORCE;
DROP TYPE FULL_MAILLING_ADDRESS_TYPE FORCE;
DROP TYPE EMPLOYEE FORCE;
DROP TYPE EMPLOYER_NAME FORCE;
DROP TABLE EMP;
COMMIT;
SELECT P.LAST_NAME, PRIOR_EMPLOYERS.*
FROM EMP P, TABLE(P.PRIOR_EMPLOYERS) PRIOR_EMPLOYERS
WHERE P.LAST_NAME = 'Jim';

SQL-Ausführungsprozess

1. Überprüfen Sie die Sicherheit und stellen Sie sicher, dass der SQL-Datenausführer die Berechtigung zur Ausführung hat
3. Schreiben Sie die mögliche Abfrage neu

创建执行计划
生产器接受经过解析的sql
捆绑执行计划 执行执行计划 读取结果记录 排序结果集

Datenzugriffsmethode:

1，全表扫描 db_file_multiblock_read_count = 128
一次性最大读取block的数量
Oracle开启并行： Alter table employee parallel degree 35;
 顺序读取，直到结尾
1，当表中不存在索引
2，查询中不包含where字句
3，内置函数中的索引无效
4，like操作 %开头
5，使用基于成本优化器 数据量少时
6，当初始化文件中存在optimizer_mode = all_rows
7，负向条件查询不能使用索引 例如 status != 0, not in, not exists 可以优化为 in (2,3);

SQL-Anweisungen führen in den folgenden Situationen zu einem vollständigen Tabellenscan :

1，使用null条件查询导致全表扫，因为索引不能为空
为了绕过全表扫这个问题，可以采取这样的方法 
update emp set name = 'N/A' where name is null; 
select name from emp where name = 'N/A';
2，对没有索引的字段查询，找到where条件后面的查询不带索引的字段，加索引可以
大大提高查询性能。
3，带有like条件的查询 like '%x%' 全表扫描，like 'x%' 不会全表扫，因为like
以字符开始。
4，内置的函数使索引无效，对于Date类型的数据来说非常的严重
内置函数 （to_date，to_char）
如果没有创建与内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器全表扫描
select name from emp where date < sysdate -8;
检查where子句脚本是否含有 substr to_char decode
SELECT SQL_TEXT, DISK_READS, EXECUTIONS, PARSE_CALLS
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE &#39;%substr%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%to_char%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%decode%&#39;
ORDER BY DISK_READS DESC;
使用函数索引解决这个问题

5，all_rows 优化器目标是提高吞吐量而且倾向于使用全表扫描，因此 对于任何一
个要求sql快速查询返回部分结果集而言，optimizer_mode 
应该设置为first_rows

6,经验上，能过滤80%数据时就可以使用索引，对于订单状态，如果状态很少，不宜
使用索引，如果状态值很多可以使用索引。

7，如果查询字段大部分是单条数据查询，使用Hash索引性能更好
原因:B-TREE 索引的时间复杂度是O(log(n))
Hash 索引的时间复杂度是O(1)
   
8,符合索引最左前缀，例如建立符合索引(passWord,userName)
select * from user u where u.pass_word = ? and u.user_name = ? 可以命中索引
select * from user u where u.user_name = ? and u.pass_word= ? 可以命中索引
select * from user u where u.pass_word = ? 可以命中索引
select * from user u where u.user_name = ? 不可以命中索引

So finden Sie die einflussreichsten SQL-Anweisungen heraus

 视图 v$sqlarea ,下列参数按照重要性从高到低排序
 executions ：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。
 disk_reads: 磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。
 rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。
 buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。
 sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。

2 .Serienerfassung

Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入 输出

3, ROWID-Zugriff

Der schnellste Weg, über Rowid auf einzelne Daten zuzugreifen, in der Praxis Sammeln Sie in der Referenz zuerst ROWID aus dem Index und lesen Sie dann Daten über ROWID

Indexzugriffsmethode

索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID

B Baumindex, Bitmap-Index basierend auf dem Funktionsindex.

Indexbereichsscan: Lesen Sie einen oder mehrere ROWID-Indexwerte in aufsteigender Reihenfolge

eg：select * from table where a = 'a';

Schneller vollständiger Index-Scan

z. B.: Wählen Sie eine bestimmte Farbe aus, zählen Sie(*) aus der Tabellengruppe nach Farbe;

Einzelner Index-Scan: Lesen Sie eine einzelne ROWID

Absteigender Indexbereichsscan: Lesen Sie einen oder mehrere ROWID-Indexwerte in absteigender Reihenfolge

UND – GLEICH: Wählen Sie * aus der Tabelle aus, wobei a = 'a' und b > ; Sammeln Sie mehrere ROWIDs aus where-Klauseln

Join-Operation

Nested-Loop-Join

Hash-Join

Hash-Joins sind in der Regel schneller als Nested-Loop-Joins, insbesondere wenn Tabellen gesteuert und in der Where-Klausel der Abfrage gefiltert werden und nur eine kleine Anzahl von Datensätzen übrig bleibt

Sort-Merge-Verbindung

Verbindungsaufforderung:

表反向连接提示，例如，NOT IN, NOT EXISTS
尽量避免使用 NOT IN 子句（它将调用子查询），而应该使用NOT EXISTS 子句(它将调用相关联的子查询)，
因为如果子查询返回的任何一条记录中包含空值，那么该查询将不会返回记录,如果允许NOT IN 子句查询为空，那么
这种查询的性能非常的低，子查询会在外层查询块中对每一条记录重新执行一次。

Sortiergröße sort_area_size_init.ora-Parameter, sort_area_size auf der Konsole anzeigen;

Abfrageanweisung : Parameter sort_area_size anzeigen;

Die Ausführungsgeschwindigkeit der Festplattensortierung ist 14.000-mal langsamer als die Ausführungsgeschwindigkeit der Speichersortierung

Festplattensortierung Daher ist es aus folgenden Gründen teuer :

1. Im Vergleich zur Sortierung im Speicher ist die Geschwindigkeit zu langsam

2. Die Festplattensortierung verbraucht die Ressourcen des temporären Tabellenbereichs

Die Datenbank reserviert 2 Temporäre Tabellenbereiche:

wählen Sie DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE aus dba_users, wobei Benutzername='SYS';

wählen Sie * aus dba_temp_free_space;

Oracle Der temporäre Tabellenbereich spielt hauptsächlich zwei Rollen: die Zuweisung temporärer Tabellendatensegmente und die Sortierzusammenfassung des Überlaufsegments.

Der Umfang des sortierten Zusammenfassungsüberlaufs ist relativ groß. Wir führen „Order by/Group by“ und andere Operationen in der SQL-Anweisung aus.

Wählen Sie zunächst den Speichersortierbereich, den Hash-Bereich und den Bitmap-Bereich des PGA aus.

Wenn der von SQL verwendete Sortierraum sehr groß ist und der einem einzelnen Serverprozess entsprechende PGA nicht ausreicht, um die Sortieranforderungen zu unterstützen, dient der temporäre Tabellenbereich als Datenschreiber für die Sortierung Segment.

Die Festplattensortierung verlangsamt eine einzelne Aufgabe und wirkt sich auch auf andere Aufgaben aus, die in der Oracle-Instanz ausgeführt werden. Eine übermäßige Festplattensortierung führt zu übermäßigen Wartezeiten im Leerlaufpuffer

Und zu hohen Kosten des Auslagerns von Datenblöcken anderer Aufgaben aus dem Pufferpool.

Oracle versucht zunächst, in dem durch sort_area_size zugewiesenen Speicherbereich zu sortieren. Nur wenn Oracle nicht im Speicher sortieren kann, ruft es die Festplattensortierung

auf und migriert den Speicherrahmen in den TEMP-Tabellenbereich, um mit der Sortierung fortzufahren.

Allgemeine Grundsätze für die Verwendung von Indexbereichsscans

 -- 对于原始排序的表， 仅读取少于40%的表记录查询就应该使用索引范围扫描，反之，多余40%，使用全表扫。
 -- 对于未排序的表， 仅读取少于7%的表记录查询就应该使用索引范围扫描，反之，多余7%，使用全表扫。

Tabellenzugriffsmethoden

SQL-Optimierer

Für jede SQL-Anweisung gibt es eine einzigartige Möglichkeit, den Tabellenzugriff zu optimieren, und Ihre Aufgabe ist es, diese Möglichkeit zu finden und langfristig zu nutzen.

db_file_multiblock_read_count

Der Zweck besteht darin, den schnellsten und am wenigsten ressourcenintensiven Ausführungsplan für SQL-Anweisungen zu generieren

1, regelbasiert Optimierer

步骤 
对于在where子句中的每一个表
-- 生成一个可行的执行计划列表，这个列表中列出所有可以用来访问表的路径
-- 为每一个执行计划指定级别数值
-- 选择级别数值最低的计划
-- 对结果集的选择级别最低 连接方法进行评估
 
基于规则优化器（PBO）特征
- 总是使用索引，使用索引永远比使用全表扫描或使用排序合并连接（排序合并连接不需要索引）更加可取
- 总是从驱动表开始 在from字句的最后一个表是驱动表,在这个表中选择的记录数应该是最少(查询返回值最少)，RBO在执行嵌套循环连接
操作时，将这个驱动表作为第一个操作表。
- 只有在不可避免的情况下才使用全表扫描
-任何索引都可以
- 有时越简单越好

2. Kostenbasierter Optimierer (CBO)

 基于规则优化提供更加复杂的优化替代方案
 ANALYZE TABLE TT_TCAS_HK_QTY COMPUTE STATISTICS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS FOR ALL INDEXED COLUMNS;
 
 CBO在以下情况会选择错误的全表扫描
 1，最高峰值过高
 2，错误的optimizer_mode,如果optimizer_mode设置为all_rows,choose,那么sql优化器会倾向于使用全表扫描。
 3，多表连接，存在多余3张表连接时，即使连接中存在索引，cbo仍然会对这些表进行全表扫描。
 4，不平衡的索引分布，比如 color = 'blue' color字段上有索引,但是只有1%的记录属于blue，

SQL SGA-Statistiken

Wählen Sie Namen und Wert aus v$sysstat aus, wobei der Name etwa „Tabelle%“ lautet

Tabellenscans (kurze Tabelle) – Die Anzahl der vollständigen Tabellenscans für kleine Tabellen.

Tabellenscans (lange Tabelle) – Die Anzahl der vollständigen Tabellenscans für große Tabellen, um zu bewerten, ob dies der Fall ist Fügen Sie Indizes hinzu. Reduzieren Sie die Anzahl der Scans großer Tabellen oder erhöhen Sie die Geschwindigkeit der Abfrageausführung, indem Sie Oracle Parallelism (opq) aufrufen.

Tabellenscans Zeilen erhalten – Diese Zahl gibt die Anzahl der Datensätze an, die von einem vollständigen Tabellenscan gescannt werden

Tabellenscans Blöcke erhalten – Die Anzahl der gescannten Datenbanken

Tabellenabruf nach Zeilen-ID – Die Anzahl der Datensätze, auf die über den Index zugegriffen wird. Der Index ist hier normalerweise eine verschachtelte Schleifenverbindung

Tabellenabruf nach fortgesetzter Zeile – Diese Zahl gibt an die Verbindung mit anderen Datenblöcken Anzahl der Datensätze zusammen

SQL, das im Bibliothekscache mehrfach verwendet werden kann

Oracle hat ein Problem, „gleiche“ SQL-Anweisungen zu identifizieren

Zum Beispiel: „Vom Kunden auswählen“; „Vom Kunden auswählen“; Auch wenn die Schreibweise der Buchstaben abweicht, kompiliert Oracle die zweite SQL-Anweisung neu und führt sie aus:

Detaillierte Erklärung der Grundprinzipien der Oracle-Paging-Abfrage

Der „Wettbewerb“ zwischen MySQL und Oracle

Eine Zusammenfassung der Methoden zur Verwendung von Oracle Erweiterungen

Das obige ist der detaillierte Inhalt vonInstanzoptimierung der ORACLE-Datenbankleistung. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!