Optimisation des instances des performances de la base de données ORACLE

Cet article présente principalement l'analyse des problèmes d'optimisation des performances de la base de données ORACLE et des solutions à partir d'exemples. Les amis dans le besoin peuvent s'y référer. J'espère qu'il sera utile à tout le monde. La méthode d'optimisation de la base de données ORACLE est très différente de celle de MYSQL. Aujourd'hui, à travers une instance de base de données ORACLE, nous analyserons comment optimiser la base de données ORACLE sous divers aspects tels que les tables et les données.

Vue tsfree.sql

Cette instruction SQL compare rapidement la quantité totale d'espace dans chaque espace table avec la quantité totale d'espace disponible dans chaque espace table

L'espace table est une division logique de la base de données. Un espace table ne peut appartenir qu'à une seule base de données. Tous les objets de base de données sont stockés dans l'espace table spécifié. Mais il stocke principalement des tables, c’est pourquoi on l’appelle espace table.

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;

Utilisation de la table varray

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';

Processus d'exécution SQL

1. Vérifiez la sécurité et assurez-vous que l'exécuteur de données SQL a l'autorisation d'exécuter
2 Vérifiez la syntaxe SQL
3 Réécrivez la requête possible
4. >

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

Méthode d'accès aux données :

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

Les instructions SQL dans les situations suivantes entraîneront une analyse complète de la table :

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 = ? 不可以命中索引

Comment découvrir les déclarations SQL les plus influentes

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

2. Acquisition de la série

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

3, accès ROWID

Le moyen le plus rapide d'accéder à des données uniques via Rowid, en pratique Dans la référence, collectez d'abord ROWID à partir de l'index, puis lisez les données via ROWID

Méthode d'accès à l'index

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

B Tree Index, index bitmap basé sur l'index de fonction.

Analyse de la plage d'index : lire une ou plusieurs valeurs d'index ROWID par ordre croissant

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

Analyse rapide de l'index complet

par exemple : sélectionnez une couleur distincte, comptez (*) dans le tableau, groupez par couleur ;

Analyse d'un seul index : lisez un seul ROWID

Analyse de la plage d'index décroissante : lisez une ou plusieurs valeurs d'index ROWID par ordre décroissant

AND - EQUALS : sélectionnez * dans le tableau où a = 'a' et b > ; Collecter plusieurs ROWID à partir des clauses Where

Opération de jointure

Jointure en boucle imbriquée

Jointure par hachage

Les jointures par hachage sont généralement plus rapides que les jointures par boucles imbriquées, en particulier lors du pilotage des tables et du filtrage dans la clause Where de la requête, ne laissant qu'un petit nombre d'enregistrements

Trier la connexion par fusion

Invite de connexion :

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

Paramètre Sort size sort_area_size_init.ora, affichez sort_area_size sur la console ;

Instruction de requête : show paramètre sort_area_size;

La vitesse d'exécution du tri des disques est 14 000 fois plus lente que la vitesse d'exécution du tri de la mémoire

Tri des disques C'est donc cher pour les raisons suivantes :

1. Par rapport au tri en mémoire, la vitesse est trop lente
2. Le tri sur disque consomme les ressources de l'espace table temporaire

La base de données en alloue 2. espaces de table temporaires :

sélectionnez DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE dans dba_users où username='SYS' ;

sélectionnez * dans dba_temp_free_space ;

Oracle L'espace table temporaire joue principalement deux rôles principaux : l'allocation de segments de données de table temporaire et le segment de débordement de résumé de tri.

La portée du débordement de résumé trié est relativement large. Nous effectuons un tri/un regroupement par et d'autres opérations dans l'instruction SQL

Tout d'abord, sélectionnez la zone de tri de la mémoire, la zone de hachage et la zone bitmap du PGA.

Si l'espace de tri utilisé par SQL est très élevé et que le PGA correspondant à un seul processus serveur n'est pas suffisant pour prendre en charge les exigences de tri, l'espace table temporaire servira d'écriture des données du tri segment.

Le tri des disques ralentira une seule tâche et affectera également d'autres tâches en cours d'exécution dans l'instance Oracle, et un tri excessif des disques entraînera des attentes excessives de tampon inactif

Et le coût élevé de blocs de données de pagination provenant d'autres tâches hors du pool de mémoire tampon.

Oracle essaie d'abord de trier dans la zone mémoire allouée par sort_area_size. Ce n'est que lorsque Oracle ne peut pas trier en mémoire qu'il appellera le tri sur disque

et migrera la trame mémoire vers l'espace table TEMP pour continuer le tri.

Principes généraux d'utilisation des analyses de plage d'index

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

Méthodes d'accès aux tables

optimiseur SQL

Pour toute instruction SQL, il existe une manière unique d'optimiser l'accès aux tables, et votre travail consiste à trouver cette méthode et à l'utiliser à long terme.

db_file_multiblock_read_count

Le but est de générer le plan d'exécution le plus rapide et le moins gourmand en ressources pour les instructions SQL

1, basé sur des règles optimiseur

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

2. Optimiseur basé sur les coûts (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，

Statistiques SQL SGA

sélectionnez le nom, la valeur de v$sysstat où le nom ressemble à 'table%'

analyses de table (table courte) -- Le nombre d'analyses de table complètes pour les petites tables

analyses de table (table longue) -- Le nombre d'analyses de table complètes pour les grandes tables, évaluant s'il faut ajouter des index Réduisez le nombre d'analyses de grandes tables ou augmentez la vitesse d'exécution des requêtes en appelant le parallélisme Oracle (opq).

analyses de table Lignes obtenues -- Ce nombre indique le nombre d'enregistrements analysés par une analyse de table complète

analyses de table blocs obtenus -- Le nombre de bases de données analysées

Récupération de table par rowid -- Le nombre d'enregistrements accessibles via l'index. L'index ici est généralement une connexion en boucle imbriquée

Récupération de table par ligne continue -- Ce nombre indique. la connexion avec d'autres blocs de données Nombre d'enregistrements ensemble

SQL pouvant être utilisé plusieurs fois dans le cache de la bibliothèque

Oracle a un problème pour identifier les "mêmes" instructions SQL

Par exemple : select from customer ; Select From Customer ; Même si la casse des lettres diffère, Oracle recompilera et exécutera la deuxième instruction SQL

Recommandations associées :

Explication détaillée Les principes de base de la requête de pagination Oracle

La "concurrence" entre MySQL et Oracle

Un résumé des méthodes d'utilisation extensions Oracle

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!