當前位置:首頁 » 服務存儲 » 數據表存儲塊優化Oracle
擴展閱讀
webinf下怎麼引入js 2023-08-31 21:54:13
堡壘機怎麼打開web 2023-08-31 21:54:11

數據表存儲塊優化Oracle

發布時間: 2022-08-17 00:52:51

A. oracle表如何優化

原則一:注意WHERE子句中的連接順序:
ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.
尤其是「主鍵ID=?」這樣的條件。
原則二: SELECT子句中避免使用 『 * 『:
ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間 。
簡單地講,語句執行的時間越短越好(尤其對於系統的終端用戶來說)。而對於查詢語句,由於全表掃描讀取的數據多,尤其是對於大型表不僅查詢速度慢,而且對磁碟IO造成大的壓力,通常都要避免,而避免的方式通常是使用索引Index。
使用索引的優勢與代價。
優勢:
1)索引是表的一個概念部 分,用來提高檢索數據的效率,ORACLE使用了一個復雜的自平衡B-tree結構. 通常,通過索引查詢數據比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率.
2) 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW數據類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率.
代價: 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會 使查詢反應時間變慢.。而且表越大,影響越嚴重。
使用索引需要注意的地方:
1、避免在索引列上使用NOT ,
我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的影響. 當ORACLE」遇到」NOT,他就會停止使用索引轉而執行全表掃描.
2、避免在索引列上使用計算.
WHERE子句中,如果索引列是函數的一部分.優化器將不使用索引而使用全表掃描. 舉例:
低效:SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:SELECT … FROM DEPT WHERE SAL > 25000/12;

3、避免在索引列上使用IS NULL和IS NOT NULL
避 免在索引中使用任何可以為空的列,ORACLE性能上將無法使用該索引.對於單列索引,如果列包含空值,索引中將不存在此記錄. 對於復合索引,如果每個列都為空,索引中同樣不存在此記錄.如果至少有一個列不為空,則記錄存在於索引中.舉例: 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值(123,null)的記錄(插入). 然而如果所有的索引列都為空,ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.

低效:(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
高效:(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

4、注意通配符%的影響
使用通配符的情況下Oracle可能會停用該索引。如 :

SELECT…FROM DEPARTMENT WHERE DEPT_CODE like 『%123456%'(無效)。
SELECT…FROM DEPARTMENT WHERE DEPT_CODE = 『123456'(有效)

5、避免改變索引列的類型.:
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換.
假 設 EMPNO是一個數值類型的索引列. SELECT … FROM EMP WHERE EMPNO = 『123' 實際上,經過ORACLE類型轉換, 語句轉化為: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(『123') 幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變. 現在,假設EMP_TYPE是一個字元類型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123 這個語句被ORACLE轉換為: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)=123 因為內部發生的類型轉換, 這個索引將不會被用到! 為了避免ORACLE對你的sql進行隱式的類型轉換, 最好把類型轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值類型到字元類型

6、索引的一些「脾氣」
a. 如果檢索數據量超過30%的表中記錄數.使用索引將沒有顯著的效率提高.
b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!

除了使用索引,我們還有其他能減少資源消耗的方法:

1、用EXISTS替換DISTINCT:
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果.
例子:

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
And E.sex =man
(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D
WHERE EXISTS
( SELECT 『X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO
And E.sex =man
);

2、用(UNION)UNION ALL替換OR (適用於索引列)
通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描.
注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.
高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL
SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = 「MELBOURNE」
低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 「MELBOURNE」

3、用UNION-ALL 替換UNION ( 如果有可能的話):
當 SQL語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合並, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是,UNION ALL 將重復輸出兩個結果集合中相同記錄. 因此各位還是要從業務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內存. 對於這塊內存的優化也是相當重要的.
4、Order By語句加在索引列,最好是主鍵PK上。
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE(低效)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_CODE (高效)

5、避免使用耗費資源的操作:
帶 有DISTINCT,UNION,MINUS,INTERSECT的SQL語句會啟動SQL引擎 執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強

6、使用Where替代Having(如果可以的話)
優化GROUP BY:
提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.

低效:
SELECT JOB , AVG(SAL)
FROM EMP GROUP JOB HAVING JOB = 『PRESIDENT'AND AVG(SAL)>XXX
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = 『PRESIDENT'
OR JOB = 『MANAGER' GROUP JOB Having AND AVG(SAL)>XXX

7、通常來說,如果語句能夠避免子查詢的 使用,就盡量不用子查詢。因為子查詢的開銷是相當昂貴的。具體的例子在後面的案例「一條SQL的優化過程」中。

B. 關於優化Oracle存儲過程的問題

你好:


您的程序我也看了。

先這么入手吧:


您的程序是包含在一個循環語句中,可否把循環語句的cursor單獨拉出來,查一下執行計劃:

selectdistinctv.procinstid_,v.value_,r.purc_report_id,r.purc_pro_id,r.purc_pro_name,t.team_name,r.purc_type,r.requi_form,r.build_name

fromcg_purchase_reportr,cg_bid_teamt,jbpm4_hist_varv

wherer.bid_team=t.bid_team_id

andr.purc_report_id=v.value_

andv.varname_='pkId'

andv.procinstid_like'requirementsReport%'

andr.purc_report_state=3;


一般來說對程序效率造成最大的問題的就是cursor,當然也包括了對INDEX列的DML。那我們先一步步來。您先查一下,給個截圖。或者簡單描述下您查到的執行計劃的結果。

-------------------------------------------------------------------------------------------------------------------------

另外, 指出一下(或者說探討一下)樓上wwtburning所提出的時間上用to_number, to_date的問題。 這個問題本身確實可能會造成效率下降, 但是不是由於使用了轉換, 您的代碼中全部都是 select __________ from al, 沒有用在 where句中, 就不可能通過index而造成影響。所以這個方向不需要考慮了。

------------------------------------------------------------------------------------------------------------------------


提2個問題:

  1. 您的cursor查詢結果是2000, 但是您的from 後面跟的幾個表的數據集是多大?如果可能, 可否給您的幾個表的索引一個截圖。目的是如果是driving cursor的效率不高, 那麼需要提升這個cursor的效率, 需要看下他的索引是怎麼寫的, 順便也可以了解一下有多少個索引。

  2. 您的這個過程中有少許幾個update語句,通過您的程序的命名法看出很多數據是為了商業報表而准備的。 這個您要更新的語句是staging table嗎,或者是數據倉庫嗎? 索引是怎樣的? 問這個問題是因為, 為報表而准備的數據在設計過程中為的是用報表的人可以讀的快,也就是說select盡量快, 因此, 如果我們第一步檢查cursor走不通, 就可能要考慮是否是您這個目標表的效率問題。

C. Oracle 大數量 存儲 優化 DML

利用rowid分塊實現非分區表的並行update與delete
大表中海量歷史數據的更新與刪除一直是令DBA非常頭痛的事情,在表已經分區的前提下我們還可以利用並行或者truncate parition等手段來為UPDATE或者DELETE提速, 但是如果對象是普通的非分區對表(non-partitioned heap table)的話,似乎就沒有太好的加速方法了, nologging或parallel 對非分區表都沒有效果。

之前我也有介紹過一個利用rowid將非分區表分割成指定數量個區間塊的方法,見<Script:partition table into rowid extent chunks>;利用該腳本可以獲取到這些分割後的區間塊的起始rowid和結尾rowid,之後利用between start_rowid and end_rowid的條件構造多條DML語句, 因為這些DML語句所更新的數據都是在互不相關的區間內的,所以可以在多個終端內並行地運行這些DML語句,而不會造成鎖的爭用或者Oracle並行執行協調(Parallel Execution coordinator ) 所帶來的一些開銷。

http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8rowid%E5%88%86%E5%9D%97%E5%AE%9E%E7%8E%B0%E9%9D%9E%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E5%B9%B6%E8%A1%8Cupdate%E4%B8%8Edelete.html

D. oracle資料庫存儲過程執行慢時如何優化

1 首先看看,先分析慢的原因,一部分是因為循環次數多,一部分是因為查詢數據量大慢。
2 可以從優化查詢入手,比如某次查詢的sql裡面的數據,條件欄位沒有建索引,導致了全表掃描,
是不是 只需要幾個欄位,但是你寫了 select * 等等,總之要優化數據的速度。
2 可以從循環邏輯看起,有些循環可能是不必要的,能不能通過條件查詢來替代循環,總之要從邏輯上優化代碼

E. Oracle資料庫存儲優化問題

創建存儲過程的語句如下:
create[or
replace]
procere<過程名>
<參數1>,「方式l]<數據類型1>,
<參數2>,[
方式2]<數據類型2>,
……)
is|as
(is或as完全等價)
begin
pl/sql過程體
end<過程名>
例如,下面是一個刪除表的存儲過程:
--參數:tablename
要刪除的表名
create
or
replace
procere
del_table(tablename
varchar2)
as
--定義變數
sql_del_table
long;
begin
--構造sql語句
sql_del_table
:=
'drop
table
'
||
tablename;
--執行
execute
immediate
sql_del_table;
end
del_table;

F. oracle資料庫優化有哪些方法

你最好買一本專門講ORACLE性能優化的書,好好看看
1、調整資料庫伺服器的性能
Oracle資料庫伺服器是整個系統的核心,它的性能高低直接影響整個系統的性能,為了調整Oracle資料庫伺服器的性能,主要從以下幾個方面考慮:
1.1、調整操作系統以適合Oracle資料庫伺服器運行
Oracle資料庫伺服器很大程度上依賴於運行伺服器的操作系統,如果操作系統不能提供最好性能,那麼無論如何調整,Oracle資料庫伺服器也無法發揮其應有的性能。
1.1.1、為Oracle資料庫伺服器規劃系統資源
據已有計算機可用資源, 規劃分配給Oracle伺服器資源原則是:盡可能使Oracle伺服器使用資源最大化,特別在Client/Server中盡量讓伺服器上所有資源都來運行Oracle服務。
1.1.2、調整計算機系統中的內存配置
多數操作系統都用虛存來模擬計算機上更大的內存,它實際上是硬碟上的一定的磁碟空間。當實際的內存空間不能滿足應用軟體的要求時,操作系統就將用這部分的磁碟空間對內存中的信息進行頁面替換,這將引起大量的磁碟I/O操作,使整個伺服器的性能下降。為了避免過多地使用虛存,應加大計算機的內存。
1.1.3、為Oracle資料庫伺服器設置操作系統進程優先順序
不要在操作系統中調整Oracle進程的優先順序,因為在Oracle資料庫系統中,所有的後台和前台資料庫伺服器進程執行的是同等重要的工作,需要同等的優先順序。所以在安裝時,讓所有的資料庫伺服器進程都使用預設的優先順序運行。
1.2、調整內存分配
Oracle資料庫伺服器保留3個基本的內存高速緩存,分別對應3種不同類型的數據:庫高速緩存,字典高速緩存和緩沖區高速緩存。庫高速緩存和字典高速緩存一起構成共享池,共享池再加上緩沖區高速緩存便構成了系統全程區(SGA)。SGA是對資料庫數據進行快速訪問的一個系統全程區,若SGA本身需要頻繁地進行釋放、分配,則不能達到快速訪問數據的目的,因此應把SGA放在主存中,不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統性能,由於Oracle資料庫伺服器的內存結構需求與應用密切相關,所以內存結構的調整應在磁碟I/O調整之前進行。
1.2.1、庫緩沖區的調整
庫緩沖區中包含私用和共享SQL和PL/SQL區,通過比較庫緩沖區的命中率決定它的大小。要調整庫緩沖區,必須首先了解該庫緩沖區的活動情況,庫緩沖區的活動統計信息保留在動態性能表v$librarycache數據字典中,可通過查詢該表來了解其活動情況,以決定如何調整。

Select sum(pins),sum(reloads) from v$librarycache;

Pins列給出SQL語句,PL/SQL塊及被訪問對象定義的總次數;Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區中發生的錯誤。如果sum(pins)/sum(reloads) ≈0,則庫緩沖區的命中率合適;若sum(pins)/sum(reloads)>1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。
1.2.2、數據字典緩沖區的調整
數據字典緩沖區包含了有關資料庫的結構、用戶、實體信息。數據字典的命中率,對系統性能影響極大。數據字典緩沖區的使用情況記錄在動態性能表v$librarycache中,可通過查詢該表來了解其活動情況,以決定如何調整。

Select sum(gets),sum(getmisses) from v$rowcache;

Gets列是對相應項請求次數的統計;Getmisses 列是引起緩沖區出錯的數據的請求次數。對於頻繁訪問的數據字典緩沖區,sum(getmisses)/sum(gets)<10%~15%。若大於此百分數,則應考慮增加數據字典緩沖區的容量,即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。
1.2.3、緩沖區高速緩存的調整
用戶進程所存取的所有數據都是經過緩沖區高速緩存來存取,所以該部分的命中率,對性能至關重要。緩沖區高速緩存的使用情況記錄在動態性能表v$sysstat中,可通過查詢該表來了解其活動情況,以決定如何調整。

Select name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads');

dbblock gets和consistent gets的值是請求數據緩沖區中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區高速緩存中讀的可能性的高低稱為緩沖區的命中率,計算公式:

Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))

如果Hit Ratio<60%~70%,則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區高速緩存的內存量,即db_block_buffers可設置分配緩沖區高速緩存的數據塊的個數。緩沖區高速緩存的總位元組數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的位元組數,可查詢 v$parameter 表:

select name,value from v$parameter where name='db_block_size';

在修改了上述資料庫的初始化參數以後,必須先關閉資料庫,在重新啟動資料庫後才能使新的設置起作用。