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

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

虞城網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁設計、網(wǎng)站建設、微信開發(fā)、APP開發(fā)、響應式網(wǎng)站等網(wǎng)站項目制作，到程序開發(fā)，運營維護。成都創(chuàng)新互聯(lián)于2013年創(chuàng)立到現(xiàn)在10年的時間，我們擁有了豐富的建站經(jīng)驗和運維經(jīng)驗，來保證我們的工作的順利進行。專注于網(wǎng)站建設就選成都創(chuàng)新互聯(lián)。

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

tsfree.sql視圖

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

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

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í)行過程

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

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

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

1，全表掃描 db_file_multiblock_read_count = 128
一次性最大讀取block的數(shù)量
Oracle開啟并行： Alter table employee parallel degree 35;
 順序讀取，直到結尾
1，當表中不存在索引
2，查詢中不包含where字句
3，內(nèi)置函數(shù)中的索引無效
4，like操作 %開頭
5，使用基于成本優(yōu)化器 數(shù)據(jù)量少時
6，當初始化文件中存在optimizer_mode = all_rows
7，負向條件查詢不能使用索引 例如 status != 0, not in, not exists 可以優(yōu)化為 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，帶有l(wèi)ike條件的查詢 like '%x%' 全表掃描，like 'x%' 不會全表掃，因為like
以字符開始。
4，內(nèi)置的函數(shù)使索引無效，對于Date類型的數(shù)據(jù)來說非常的嚴重
內(nèi)置函數(shù) （to_date，to_char）
如果沒有創(chuàng)建與內(nèi)置函數(shù)匹配的基于函數(shù)的索引，那么這些函數(shù)通常會導致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ù)索引解決這個問題

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

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

7，如果查詢字段大部分是單條數(shù)據(jù)查詢，使用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 ,下列參數(shù)按照重要性從高到低排序
 executions ：越經(jīng)常執(zhí)行的sql語句就應當越早的調(diào)整，因為它會對整體的性能產(chǎn)生巨大的影響。
 disk_reads: 磁盤讀取，高的磁盤讀取量可能表明查詢導致過多的輸入輸出量。
 rows_processed:記錄處理，處理大量的記錄的查詢會導致較大的輸入輸出量，同時在排序的時候對TEMP表空間產(chǎn)生影響。
 buffer_gets:緩沖區(qū)讀取，高的緩沖讀取量可能表明了一個高資源使用量的查詢。
 sorts:排序會導致速度的明顯減低，尤其是在TEMP表空間中進行的排序。

2.賽列獲取

Oracle對單表簇和多表簇進行散列存儲，用來在連接操作中減低輸入 輸出

3，ROWID 訪問

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

索引訪問方式

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

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

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

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

快速全索引掃描

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

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

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

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

連接操作

嵌套循環(huán)連接

散列連接

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

排序合并連接

連接提示：

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

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

查詢語句：show parameter sort_area_size;

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

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

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

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

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

select * from dba_temp_free_space;

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

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

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

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

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

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

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

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

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

表的訪問方式

sql優(yōu)化器

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

db_file_multiblock_read_count

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

關于怎么實現(xiàn)優(yōu)化ORACLE數(shù)據(jù)庫性能問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關知識。

網(wǎng)站名稱：怎么實現(xiàn)優(yōu)化ORACLE數(shù)據(jù)庫性能
鏈接地址：http://m.kartarina.com/article28/gogccp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供服務器托管、ChatGPT、云服務器、商城網(wǎng)站、網(wǎng)頁設計公司、網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)