怎么實(shí)現(xiàn)優(yōu)化ORACLE數(shù)據(jù)庫(kù)性能

怎么實(shí)現(xiàn)優(yōu)化ORACLE數(shù)據(jù)庫(kù)性能？針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

ORACLE數(shù)據(jù)庫(kù)的優(yōu)化方式和MYSQL等很大的區(qū)別，今天通過(guò)一個(gè)ORACLE數(shù)據(jù)庫(kù)實(shí)例從表格、數(shù)據(jù)等各個(gè)方便分析了如何進(jìn)行ORACLE數(shù)據(jù)庫(kù)的優(yōu)化。

tsfree.sql視圖

這個(gè)sql語(yǔ)句迅速的對(duì)每一個(gè)表空間中的空間總量與每一個(gè)表空間中可用的空間的總量進(jìn)行比較

表空間是數(shù)據(jù)庫(kù)的邏輯劃分，一個(gè)表空間只能屬于一個(gè)數(shù)據(jù)庫(kù)。所有的數(shù)據(jù)庫(kù)對(duì)象都存放在指定的表空間中。但主要存放的是表， 所以稱作表空間。

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;
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 執(zhí)行過(guò)程

1，檢查安全性，確保sql數(shù)據(jù)執(zhí)行者有權(quán)限執(zhí)行
2，檢查sql語(yǔ)法
3，可能發(fā)生的查詢重新書寫
4，執(zhí)行

創(chuàng)建執(zhí)行計(jì)劃
生產(chǎn)器接受經(jīng)過(guò)解析的sql
捆綁執(zhí)行計(jì)劃 執(zhí)行執(zhí)行計(jì)劃 讀取結(jié)果記錄 排序結(jié)果集

數(shù)據(jù)訪問(wèn)方式：

1，全表掃描 db_file_multiblock_read_count = 128
一次性最大讀取block的數(shù)量
Oracle開啟并行： Alter table employee parallel degree 35;
 順序讀取，直到結(jié)尾
1，當(dāng)表中不存在索引
2，查詢中不包含where字句
3，內(nèi)置函數(shù)中的索引無(wú)效
4，like操作 %開頭
5，使用基于成本優(yōu)化器 數(shù)據(jù)量少時(shí)
6，當(dāng)初始化文件中存在optimizer_mode = all_rows
7，負(fù)向條件查詢不能使用索引 例如 status != 0, not in, not exists 可以優(yōu)化為 in (2,3);

下列情況的SQL語(yǔ)句會(huì)導(dǎo)致全表掃：

1，使用null條件查詢導(dǎo)致全表掃，因?yàn)樗饕荒転榭?
為了繞過(guò)全表掃這個(gè)問(wèn)題，可以采取這樣的方法 
update emp set name = 'N/A' where name is null; 
select name from emp where name = 'N/A';
2，對(duì)沒(méi)有索引的字段查詢，找到where條件后面的查詢不帶索引的字段，加索引可以
大大提高查詢性能。
3，帶有l(wèi)ike條件的查詢 like '%x%' 全表掃描，like 'x%' 不會(huì)全表掃，因?yàn)閘ike
以字符開始。
4，內(nèi)置的函數(shù)使索引無(wú)效，對(duì)于Date類型的數(shù)據(jù)來(lái)說(shuō)非常的嚴(yán)重
內(nèi)置函數(shù) （to_date，to_char）
如果沒(méi)有創(chuàng)建與內(nèi)置函數(shù)匹配的基于函數(shù)的索引，那么這些函數(shù)通常會(huì)導(dǎo)致sql優(yōu)化器全表掃描
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 '%substr%'
OR LOWER(SQL_TEXT) LIKE '%to_char%'
OR LOWER(SQL_TEXT) LIKE '%decode%'
ORDER BY DISK_READS DESC;
使用函數(shù)索引解決這個(gè)問(wèn)題

5，all_rows 優(yōu)化器目標(biāo)是提高吞吐量而且傾向于使用全表掃描，因此 對(duì)于任何一
個(gè)要求sql快速查詢返回部分結(jié)果集而言，optimizer_mode 
應(yīng)該設(shè)置為first_rows

6,經(jīng)驗(yàn)上，能過(guò)濾80%數(shù)據(jù)時(shí)就可以使用索引，對(duì)于訂單狀態(tài)，如果狀態(tài)很少，不宜
使用索引，如果狀態(tài)值很多可以使用索引。

7，如果查詢字段大部分是單條數(shù)據(jù)查詢，使用Hash索引性能更好
原因:B-TREE 索引的時(shí)間復(fù)雜度是O(log(n))
Hash 索引的時(shí)間復(fù)雜度是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語(yǔ)句

 視圖 v$sqlarea ,下列參數(shù)按照重要性從高到低排序
 executions ：越經(jīng)常執(zhí)行的sql語(yǔ)句就應(yīng)當(dāng)越早的調(diào)整，因?yàn)樗鼤?huì)對(duì)整體的性能產(chǎn)生巨大的影響。
 disk_reads: 磁盤讀取，高的磁盤讀取量可能表明查詢導(dǎo)致過(guò)多的輸入輸出量。
 rows_processed:記錄處理，處理大量的記錄的查詢會(huì)導(dǎo)致較大的輸入輸出量，同時(shí)在排序的時(shí)候?qū)EMP表空間產(chǎn)生影響。
 buffer_gets:緩沖區(qū)讀取，高的緩沖讀取量可能表明了一個(gè)高資源使用量的查詢。
 sorts:排序會(huì)導(dǎo)致速度的明顯減低，尤其是在TEMP表空間中進(jìn)行的排序。

2.賽列獲取

Oracle對(duì)單表簇和多表簇進(jìn)行散列存儲(chǔ)，用來(lái)在連接操作中減低輸入 輸出

3，ROWID 訪問(wèn)

通過(guò)Rowid訪問(wèn)單條數(shù)據(jù)最快的方式，在實(shí)際的引用中，首先從索引中收集ROWID,然后通過(guò)ROWID進(jìn)行數(shù)據(jù)讀取

索引訪問(wèn)方式

索引都可以看做一組符合主鍵和ROWID的組合，索引訪問(wèn)的目的是收集對(duì)目標(biāo)快速讀取時(shí)所需要的ROWID

B樹索引，位圖索引 基于函數(shù)的索引.

索引范圍掃描：讀取一個(gè)或者多個(gè)ROWID 索引數(shù)值升序排列

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

快速全索引掃描

eg: select distinct color,count(*) from table group by color;

單個(gè)索引掃描:讀取一個(gè)單獨(dú)的ROWID

降序索引范圍掃描:讀取一個(gè)或者多個(gè)ROWID 索引數(shù)值降序排列

AND - EQUALS: select * from table where a = 'a' and b > 34; 從where字句中收集多個(gè)ROWID

連接操作

嵌套循環(huán)連接

散列連接

散列連接通?？煊谇短籽h(huán)連接，特別是在驅(qū)動(dòng)表以及在查詢的where子句中過(guò)濾，只剩下少量的記錄的情況下

排序合并連接

連接提示：

表反向連接提示，例如，NOT IN, NOT EXISTS
盡量避免使用 NOT IN 子句（它將調(diào)用子查詢），而應(yīng)該使用NOT EXISTS 子句(它將調(diào)用相關(guān)聯(lián)的子查詢)，
因?yàn)槿绻硬樵兎祷氐娜魏我粭l記錄中包含空值，那么該查詢將不會(huì)返回記錄,如果允許NOT IN 子句查詢?yōu)榭?，那?
這種查詢的性能非常的低，子查詢會(huì)在外層查詢塊中對(duì)每一條記錄重新執(zhí)行一次。

排序大小 sort_area_size_init.ora 參數(shù)，在控制臺(tái)查看 sort_area_size;

查詢語(yǔ)句：show parameter sort_area_size;

磁盤排序的執(zhí)行速度要比內(nèi)存排序的的執(zhí)行速度慢14000倍

磁盤排序之所以昂貴，有以下幾個(gè)原因：

1，同在內(nèi)存中進(jìn)行排序比較，速度太慢
2，磁盤排序耗費(fèi)臨時(shí)表空間的資源

數(shù)據(jù)庫(kù)分配2個(gè)臨時(shí)表空間：

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

select * from dba_temp_free_space;

Oracle臨時(shí)表空間主要充當(dāng)兩個(gè)主要作用：臨時(shí)表數(shù)據(jù)段分配和排序匯總溢出段。

排序匯總溢出的范圍比較廣泛。我們?cè)赟QL語(yǔ)句中進(jìn)行order by/group by等操作，

首先是選擇PGA的內(nèi)存sort area、hash area和bitmap area。

如果SQL使用排序空間很高，單個(gè)server process對(duì)應(yīng)的PGA不足以支撐排序要求的時(shí)候，臨時(shí)表空間會(huì)充當(dāng)排序段的數(shù)據(jù)寫入。

而磁盤排序會(huì)降低單個(gè)任務(wù)的速度，同時(shí)還會(huì)影響Oracle實(shí)例中正在執(zhí)行的其他任務(wù)，而且過(guò)多的磁盤排序?qū)?dǎo)致過(guò)多的空閑緩沖等待

以及將其他任務(wù)的數(shù)據(jù)塊從緩沖池中分頁(yè)出去的昂貴代價(jià)。

Oracle首先嘗試在sort_area_size 分配的內(nèi)存區(qū)中進(jìn)行排序，Oracle只有不能再內(nèi)存中排序時(shí)，才會(huì)調(diào)用磁盤排序
并將內(nèi)存框架遷移到TEMP表空間，繼續(xù)進(jìn)行排序。

使用索引范圍掃描的總體原則

 -- 對(duì)于原始排序的表， 僅讀取少于40%的表記錄查詢就應(yīng)該使用索引范圍掃描，反之，多余40%，使用全表掃。
 -- 對(duì)于未排序的表， 僅讀取少于7%的表記錄查詢就應(yīng)該使用索引范圍掃描，反之，多余7%，使用全表掃。

表的訪問(wèn)方式

sql優(yōu)化器

對(duì)于任何一個(gè)sql語(yǔ)句來(lái)說(shuō)，存在唯一的優(yōu)化表訪問(wèn)方式，而你的工作就是找到這種方式，并且長(zhǎng)期使用它。

db_file_multiblock_read_count

目的是為sql語(yǔ)句生成最快 并且好資源最少的執(zhí)行計(jì)劃

1，基于規(guī)則的優(yōu)化器

步驟 
對(duì)于在where子句中的每一個(gè)表
-- 生成一個(gè)可行的執(zhí)行計(jì)劃列表，這個(gè)列表中列出所有可以用來(lái)訪問(wèn)表的路徑
-- 為每一個(gè)執(zhí)行計(jì)劃指定級(jí)別數(shù)值
-- 選擇級(jí)別數(shù)值最低的計(jì)劃
-- 對(duì)結(jié)果集的選擇級(jí)別最低 連接方法進(jìn)行評(píng)估
 
基于規(guī)則優(yōu)化器（PBO）特征
- 總是使用索引，使用索引永遠(yuǎn)比使用全表掃描或使用排序合并連接（排序合并連接不需要索引）更加可取
- 總是從驅(qū)動(dòng)表開始 在from字句的最后一個(gè)表是驅(qū)動(dòng)表,在這個(gè)表中選擇的記錄數(shù)應(yīng)該是最少(查詢返回值最少)，RBO在執(zhí)行嵌套循環(huán)連接
操作時(shí)，將這個(gè)驅(qū)動(dòng)表作為第一個(gè)操作表。
- 只有在不可避免的情況下才使用全表掃描
-任何索引都可以
- 有時(shí)越簡(jiǎn)單越好

2，基于成本的優(yōu)化器(CBO)

 基于規(guī)則優(yōu)化提供更加復(fù)雜的優(yōu)化替代方案
 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在以下情況會(huì)選擇錯(cuò)誤的全表掃描
 1，最高峰值過(guò)高
 2，錯(cuò)誤的optimizer_mode,如果optimizer_mode設(shè)置為all_rows,choose,那么sql優(yōu)化器會(huì)傾向于使用全表掃描。
 3，多表連接，存在多余3張表連接時(shí)，即使連接中存在索引，cbo仍然會(huì)對(duì)這些表進(jìn)行全表掃描。
 4，不平衡的索引分布，比如 color = 'blue' color字段上有索引,但是只有1%的記錄屬于blue，

SQL 的SGA統(tǒng)計(jì)資料

select name,value from v$sysstat where name like 'table%'

table scans(short table) -- 對(duì)小表全表掃描的次數(shù)

table scans(long table) -- 對(duì)大表全表掃描的次數(shù)，評(píng)估是否通過(guò)加索引減少大表的掃描次數(shù) 或者通過(guò)調(diào)用Oracle并行（opq）來(lái)提高查詢的執(zhí)行速度。

table scans Rows Gotten -- 這個(gè)數(shù)目說(shuō)明全表掃描掃描記錄條數(shù)

table scans blocks Gotten -- 掃描獲取數(shù)據(jù)庫(kù)的數(shù)目

Table fetch by rowid -- 通過(guò)索引訪問(wèn)記錄的數(shù)目，這里的索引通常是嵌套循環(huán)連接

table　fetch by Continued Row -- 這個(gè)數(shù)目說(shuō)明與其他數(shù)據(jù)塊連接在一起的記錄數(shù)目

程序庫(kù)緩存中可以多次使用的SQL

Oracle在辨認(rèn)"相同的"sql語(yǔ)句是存在問(wèn)題

例如：select from customer; Select From Customer; 盡管區(qū)別字母的大小寫，Oracle會(huì)對(duì)第二個(gè)sql語(yǔ)句進(jìn)行重新編譯執(zhí)行；

關(guān)于怎么實(shí)現(xiàn)優(yōu)化ORACLE數(shù)據(jù)庫(kù)性能問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。