ORACLE數據庫性能優化的示例分析

這篇文章給大家分享的是有關ORACLE數據庫性能優化的示例分析的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

ORACLE數據庫的優化方式和MYSQL等很大的區別，今天通過一個ORACLE數據庫實例從表格、數據等各個方便分析了如何進行ORACLE數據庫的優化。

tsfree.sql視圖

這個sql語句迅速的對每一個表空間中的空間總量與每一個表空間中可用的空間的總量進行比較

表空間是數據庫的邏輯劃分，一個表空間只能屬于一個數據庫。所有的數據庫對象都存放在指定的表空間中。但主要存放的是表， 所以稱作表空間。

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 執行過程

1，檢查安全性，確保sql數據執行者有權限執行
2，檢查sql語法
3，可能發生的查詢重新書寫
4，執行

創建執行計劃
生產器接受經過解析的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);

下列情況的SQL語句會導致全表掃：

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 '%substr%'
OR LOWER(SQL_TEXT) LIKE '%to_char%'
OR LOWER(SQL_TEXT) LIKE '%decode%'
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進行數據讀取

索引訪問方式

索引都可以看做一組符合主鍵和ROWID的組合，索引訪問的目的是收集對目標快速讀取時所需要的ROWID

B樹索引，位圖索引 基于函數的索引.

索引范圍掃描：讀取一個或者多個ROWID 索引數值升序排列

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

快速全索引掃描

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

單個索引掃描:讀取一個單獨的ROWID

降序索引范圍掃描:讀取一個或者多個ROWID 索引數值降序排列

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

連接操作

嵌套循環連接

散列連接

散列連接通常快于嵌套循環連接，特別是在驅動表以及在查詢的where子句中過濾，只剩下少量的記錄的情況下

排序合并連接

連接提示：

表反向連接提示，例如，NOT IN, NOT EXISTS
盡量避免使用 NOT IN 子句（它將調用子查詢），而應該使用NOT EXISTS 子句(它將調用相關聯的子查詢)，
因為如果子查詢返回的任何一條記錄中包含空值，那么該查詢將不會返回記錄,如果允許NOT IN 子句查詢為空，那么
這種查詢的性能非常的低，子查詢會在外層查詢塊中對每一條記錄重新執行一次。

排序大小 sort_area_size_init.ora 參數，在控制臺查看 sort_area_size;

查詢語句：show parameter sort_area_size;

磁盤排序的執行速度要比內存排序的的執行速度慢14000倍

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

1，同在內存中進行排序比較，速度太慢
2，磁盤排序耗費臨時表空間的資源

數據庫分配2個臨時表空間：

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

select * from dba_temp_free_space;

Oracle臨時表空間主要充當兩個主要作用：臨時表數據段分配和排序匯總溢出段。

排序匯總溢出的范圍比較廣泛。我們在SQL語句中進行order by/group by等操作，

首先是選擇PGA的內存sort area、hash area和bitmap area。

如果SQL使用排序空間很高，單個server process對應的PGA不足以支撐排序要求的時候，臨時表空間會充當排序段的數據寫入。

而磁盤排序會降低單個任務的速度，同時還會影響Oracle實例中正在執行的其他任務，而且過多的磁盤排序將導致過多的空閑緩沖等待

以及將其他任務的數據塊從緩沖池中分頁出去的昂貴代價。

Oracle首先嘗試在sort_area_size 分配的內存區中進行排序，Oracle只有不能再內存中排序時，才會調用磁盤排序
并將內存框架遷移到TEMP表空間，繼續進行排序。

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

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

表的訪問方式

sql優化器

對于任何一個sql語句來說，存在唯一的優化表訪問方式，而你的工作就是找到這種方式，并且長期使用它。

db_file_multiblock_read_count

目的是為sql語句生成最快 并且好資源最少的執行計劃

1，基于規則的優化器

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

2，基于成本的優化器(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統計資料

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

table scans(short table) -- 對小表全表掃描的次數

table scans(long table) -- 對大表全表掃描的次數，評估是否通過加索引減少大表的掃描次數 或者通過調用Oracle并行（opq）來提高查詢的執行速度。

table scans Rows Gotten -- 這個數目說明全表掃描掃描記錄條數

table scans blocks Gotten -- 掃描獲取數據庫的數目

Table fetch by rowid -- 通過索引訪問記錄的數目，這里的索引通常是嵌套循環連接

table　fetch by Continued Row -- 這個數目說明與其他數據塊連接在一起的記錄數目

程序庫緩存中可以多次使用的SQL

Oracle在辨認"相同的"sql語句是存在問題

例如：select from customer; Select From Customer; 盡管區別字母的大小寫，Oracle會對第二個sql語句進行重新編譯執行；

感謝各位的閱讀！關于“ORACLE數據庫性能優化的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！