實(shí)例分析ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化

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;
  
  

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

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

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

1，使用null條件查詢(xún)導(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)有索引的字段查詢(xún)，找到where條件后面的查詢(xún)不帶索引的字段，加索引可以
大大提高查詢(xún)性能。
3，帶有l(wèi)ike條件的查詢(xún) like '%x%' 全表掃描，like 'x%' 不會(huì)全表掃，因?yàn)閘ike
以字符開(kāi)始。
4，內(nèi)置的函數(shù)使索引無(wú)效，對(duì)于Date類(lèi)型的數(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快速查詢(xún)返回部分結(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，如果查詢(xún)字段大部分是單條數(shù)據(jù)查詢(xún)，使用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 = ? 不可以命中索引
 

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

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

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

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

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

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

步驟 
對(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)表開(kāi)始 在from字句的最后一個(gè)表是驅(qū)動(dòng)表,在這個(gè)表中選擇的記錄數(shù)應(yīng)該是最少(查詢(xún)返回值最少)，RBO在執(zhí)行嵌套循環(huán)連接
操作時(shí)，將這個(gè)驅(qū)動(dòng)表作為第一個(gè)操作表。
- 只有在不可避免的情況下才使用全表掃描
-任何索引都可以
- 有時(shí)越簡(jiǎn)單越好
 

 基于規(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，