ORACLEデータベースのパフォーマンスのインスタンスの最適化

この記事では主に ORACLE データベースのパフォーマンス最適化の問題の分析と例からの解決策を紹介します。必要な方はぜひ参考にしてください。 ORACLE データベースの最適化方法は MYSQL とは大きく異なります。今回は、ORACLE データベース インスタンスを通じて、テーブルやデータなどのさまざまな側面から ORACLE データベースを最適化する方法を分析します。

tsfree.sql ビュー

この SQL ステートメントは、各表スペースのスペースの合計量と各表スペースの使用可能なスペースの合計量を素早く比較します

テーブルスペースはデータベースの論理的な分割であり、A テーブルスペースです1 つのデータベースにのみ属することができます。すべてのデータベース オブジェクトは、指定されたテーブル スペースに保存されます。ただし、主にテーブルを格納するため、テーブルスペースと呼ばれます。

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;

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

--ロールバック

DROP TYPE PRIOR_EMPLOYER_NAME_ARR FORCE;
DROP TYPE FULL_MAILLING_ADRESS_TYPE FORCE ;
ドロップタイプ従業員部隊;
ドロップタイプ従業員名部隊;
ドロップテーブルEMP ;
COMMIT;
SELECT P.LAST_NAME, PRIOR_EMPLOYERS.*
FROM EMP P, TABLE(P.PRIOR_EMPLOYERS) PRIOR_EMPLOYERS
WHERE P.LAST_NAME = 'Jim';

SQL 実行プロセス

1、セキュリティを確認し、 SQL データ実行プログラムには、
2 を実行する権限があります。SQL 構文をチェックします。
3、発生する可能性のあるクエリを書き換えます。
4、実行する

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

データ アクセス方法: 以下の SQL ステートメント:

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

フルテーブルスキャンになります:

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

影響力の高い SQL ステートメントを見つける方法

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

2. 列を取得します

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

3、ROWID アクセス

Rowid 実際の参照では、まずインデックスから ROWID を収集し、次に ROWID

Index アクセス メソッド

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

B ツリー インデックスを通じてデータを読み取ります。関数 Index.

インデックス範囲スキャン: 1 つ以上の ROWID インデックス値を昇順で読み取ります

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

高速フルインデックススキャン

例: 個別の色、カウント(*) を選択します色別のテーブルグループ;

単一インデックススキャン: 単一の ROWID を読み取ります

降順インデックス範囲スキャン: 1 つ以上の ROWID インデックス値を降順で読み取ります

AND - EQUALS: select * from table where a = ' a' および b > ; 34; where 句から複数の ROWID を収集する

結合操作

ネストされたループ結合

ハッシュ結合

ハッシュ結合は、特に駆動テーブルやフィルターでの場合、通常、高速です。クエリの where 句内で、少数のレコードしか残っていない場合、

sort merge connection

Connection プロンプト:

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

sort_area_size_init.ora パラメーター、コンソールで sort_area_size を表示します。

Query ステートメント: showparametersort_area_size;

ディスク ソートの実行速度は、メモリ ソートの実行速度より 14,000 倍遅いです

ディスク ソートが高価である理由は次のとおりです:

1、同じ メモリ内のソート比較が遅すぎる

2. ディスクのソートは一時テーブルスペースのリソースを消費する

データベースは 2 つの一時テーブルスペースを割り当てます:

select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYS';

select * from dba_temp_free_space;

Oracle 一時表スペースは主に、一時表データ・セグメントの割り当てとソート要約オーバーフロー・セグメントという 2 つの主要な役割を果たします。

ソートされた概要のオーバーフローの範囲は比較的広いです。 SQL ステートメントで order by/group by などの操作を実行します。最初のステップは、PGA のメモリ ソート領域、ハッシュ領域、ビットマップ領域を選択することです。

SQL によって使用されるソート領域が非常に大きく、単一サーバー プロセスに対応する PGA ではソート要件をサポートできない場合、一時テーブル領域がソート セグメントのデータ書き込みとして機能します。

そして、ディスクのソートは単一タスクの速度を低下させ、Oracle インスタンスで実行されている他のタスクにも影響を与えます。ディスクのソートが多すぎると、過剰なアイドル バッファ待機が発生し、他のタスクのデータ ブロックがバッファからページング アウトされることになります

プールは高価です。

Oracle はまず、sort_area_size で割り当てられたメモリ領域でソートを試みます。Oracle がメモリ内でソートできない場合にのみ、ディスク ソート

を呼び出し、メモリ フレームを TEMP テーブル スペースに移行してソートを続行します。

インデックス範囲スキャンを使用する一般原則

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

テーブルアクセス方法

SQLオプティマイザー

どのSQLステートメントにも、テーブルアクセスを最適化する独自の方法があり、あなたの仕事はそれを見つけることです。そして長く使ってください。

db_file_multiblock_read_count

目的は、SQLステートメントの最小リソースで最速の実行計画を生成することです。

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

SQL の SGA 統計

v$sysstat から名前と値を選択します (名前は「table%」のようなものです)

table scans(short table) -- 小さなテーブルのフルテーブルスキャンの数

table scans(long table) -- 大きなテーブルのフルテーブルスキャンの数、大きなテーブルのスキャン数を減らすかどうかを評価しますインデックスを追加するか、Oracle Parallel (opq) を呼び出してクエリの実行速度を向上させます。

table scans Gotten Rows -- この数値は、フルテーブルスキャンによってスキャンされたレコードの数を示します

table scans tables Gotten -- 取得されたデータベーススキャンの数

rowid によるテーブルフェッチ -- レコードの数ここではインデックスを通じてアクセスします。インデックスは通常、ネストされたループ接続です

テーブル、継続行によるフェッチ -- この数値は、他のデータブロックに接続されているレコードの数を示します

ライブラリキャッシュで複数回使用できるSQL

Oracle が識別しています" 同じ "SQL ステートメント" に問題があります

例: select from customer; Select From Customer; 文字の大文字と小文字が異なっていても、Oracle は 2 番目の SQL ステートメントを再コンパイルして実行します。

関連する推奨事項:

詳細な説明Oracle ページング クエリの基本原則

MySQL と Oracle の間の「競合」

Oracle 拡張機能の使用方法の概要

以上がORACLEデータベースのパフォーマンスのインスタンスの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。