oracle分區是怎樣優化數據庫的

1、1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE數據庫的分區功能，對于經常訪問的數據庫表是否需要建立索引等。

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：主機域名、雅安服務器托管、營銷軟件、網站建設、云岡網站維護、網站推廣。

2、2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什么樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的數據庫資源是不同的。

3、3、調整數據庫SQL語句。應用程序的執行最終將歸結為數據庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE數據庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。

Oracle SQL調優之分區表

@[toc]

其它類型的表設計可以看博客：

普通表和分區表區別，分區表分成幾部分就有幾個segment，RANGE_PART_TAB是一個分區表

引用Oracle官方文檔的說法， ：

分區類型：分區分為范圍分區、列表分區、HASH分區、組合分區四種，圖來自Oracle官方網站

關鍵字partition by range

散列分區也叫hash分區，partitions后接分區數，盡量設置為偶數，

組合分區又稱復合分區，主要有兩種：oracle11之前只支持范圍列表分區(RANGE-LIST)和范圍散列分區(RANGE-HASH)，oracle11之后支持(范圍范圍分區)RANGE-RANGE、 (列表范圍分區)LIST-RANGE、(列表散列分區)LIST-HASH、(列表列表分區)LIST-LIST這幾種組合，為了避免每個主分區中都寫相同的從分區，可以用模板方式(subpartition template)

圖來自Oracle官方網站：

新增子分區，子分區名稱是P13SUB1

刪除子分區，子分區名稱P13SUB1

TRUNCATE子分區

分區相關查詢

ps:表格來自《收獲，不止SQL調優》一書作者的整理

怎么優化oracle數據庫

1、1、調整數據結構的設計。這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE數據庫的分區功能，對于經常訪問的數據庫表是否需要建立索引等。

2、2、調整應用程序結構設計。這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什么樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的數據庫資源是不同的。

3、3、調整數據庫SQL語句。應用程序的執行最終將歸結為數據庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE數據庫的性能。ORACLE公司推薦使用ORACLE語句優化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調整優化SQL語句。

4、4、調整服務器內存分配。內存分配是在信息系統運行過程中優化配置的，數據庫管理員可以根據數據庫運行狀況調整數據庫系統全局區（SGA區）的數據緩沖區、日志緩沖區和共享池的大小；還可以調整程序全局區（PGA區）的大小。需要注意的是，SGA區不是越大越好，SGA區過大會占用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。

5、5、調整硬盤I/O，這一步是在信息系統開發之前完成的。數據庫管理員可以將組成同一個表空間的數據文件放在不同的硬盤上，做到硬盤之間I/O負載均衡。

6、6、調整操作系統參數，例如：運行在UNIX操作系統上的ORACLE數據庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。

實際上，上述數據庫優化措施之間是相互聯系的。ORACLE數據庫性能惡化表現基本上都是用戶響應時間比較長，需要用戶長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要數據庫管理員有比較全面的計算機知識，能夠敏感地察覺到影響數據庫性能的主要原因所在。另外，良好的數據庫管理工具對于優化數據庫性能也是很重要的。

ORACLE數據庫性能優化工具

常用的數據庫性能優化工具有：

1、1、ORACLE數據庫在線數據字典，ORACLE在線數據字典能夠反映出ORACLE動態運行情況，對于調整數據庫性能是很有幫助的。

2、2、操作系統工具，例如UNIX操作系統的vmstat，iostat等命令可以查看到系統系統級內存和硬盤I/O的使用情況，這些工具對于管理員弄清出系統瓶頸出現在什么地方有時候很有用。

3、3、SQL語言跟蹤工具（SQL TRACE FACILITY），SQL語言跟蹤工具可以記錄SQL語句的執行情況，管理員可以使用虛擬表來調整實例，使用SQL語句跟蹤文件調整應用程序性能。SQL語言跟蹤工具將結果輸出成一個操作系統的文件，管理員可以使用TKPROF工具查看這些文件。

4、4、ORACLE Enterprise Manager（OEM），這是一個圖形的用戶管理界面，用戶可以使用它方便地進行數據庫管理而不必記住復雜的ORACLE數據庫管理的命令。

5、5、EXPLAIN PLAN——SQL語言優化命令，使用這個命令可以幫助程序員寫出高效的SQL語言。

ORACLE數據庫的系統性能評估

信息系統的類型不同，需要關注的數據庫參數也是不同的。數據庫管理員需要根據自己的信息系統的類型著重考慮不同的數據庫參數。

1、1、在線事務處理信息系統（OLTP），這種類型的信息系統一般需要有大量的Insert、Update操作，典型的系統包括民航機票發售系統、銀行儲蓄系統等。OLTP系統需要保證數據庫的并發性、可靠性和最終用戶的速度，這類系統使用的ORACLE數據庫需要主要考慮下述參數：

l l 數據庫回滾段是否足夠？

l l 是否需要建立ORACLE數據庫索引、聚集、散列？

l l 系統全局區（SGA）大小是否足夠？

l l SQL語句是否高效？

2、2、數據倉庫系統（Data Warehousing），這種信息系統的主要任務是從ORACLE的海量數據中進行查詢，得到數據之間的某些規律。數據庫管理員需要為這種類型的ORACLE數據庫著重考慮下述參數：

l l 是否采用B*-索引或者bitmap索引？

l l 是否采用并行SQL查詢以提高查詢效率？

l l 是否采用PL/SQL函數編寫存儲過程？

l l 有必要的話，需要建立并行數據庫提高數據庫的查詢效率

SQL語句的調整原則

SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率是很不相同的。程序員可以使用EXPLAIN PLAN語句來比較各種實現方案，并選出最優的實現方案。總得來講，程序員寫SQL語句需要滿足考慮如下規則：

1、1、盡量使用索引。試比較下面兩條SQL語句：

語句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN

(SELECT deptno FROM emp);

語句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS

(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

這兩條查詢語句實現的結果是相同的，但是執行語句A的時候，ORACLE會對整個emp表進行掃描，沒有使用建立在emp表上的deptno索引，執行語句B的時候，由于在子查詢中使用了聯合查詢，ORACLE只是對emp表進行的部分數據掃描，并利用了deptno列的索引，所以語句B的效率要比語句A的效率高一些。

2、2、選擇聯合查詢的聯合次序。考慮下面的例子：

SELECT stuff FROM taba a, tabb b, tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AMD a.key2 = c.key2;

這個SQL例子中，程序員首先需要選擇要查詢的主表，因為主表要進行整個表數據的掃描，所以主表應該數據量最小，所以例子中表A的acol列的范圍應該比表B和表C相應列的范圍小。

3、3、在子查詢中慎重使用IN或者NOT IN語句，使用where (NOT) exists的效果要好的多。

4、4、慎重使用視圖的聯合查詢，尤其是比較復雜的視圖之間的聯合查詢。一般對視圖的查詢最好都分解為對數據表的直接查詢效果要好一些。

5、5、可以在參數文件中設置SHARED_POOL_RESERVED_SIZE參數，這個參數在SGA共享池中保留一個連續的內存空間，連續的內存空間有益于存放大的SQL程序包。

6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以幫助程序員將某些經常使用的存儲過程“釘”在SQL區中而不被換出內存，程序員對于經常使用并且占用內存很多的存儲過程“釘”到內存中有利于提高最終用戶的響應時間。

CPU參數的調整

CPU是服務器的一項重要資源，服務器良好的工作狀態是在工作高峰時CPU的使用率在90％以上。如果空閑時間CPU使用率就在90％以上，說明服務器缺乏CPU資源，如果工作高峰時CPU使用率仍然很低，說明服務器CPU資源還比較富余。

使用操作相同命令可以看到CPU的使用情況，一般UNIX操作系統的服務器，可以使用sar –u命令查看CPU的使用率，NT操作系統的服務器，可以使用NT的性能管理器來查看CPU的使用率。

數據庫管理員可以通過查看v$sysstat數據字典中“CPU used by this session”統計項得知ORACLE數據庫使用的CPU時間，查看“OS User level CPU time”統計項得知操作系統用戶態下的CPU時間，查看“OS System call CPU time”統計項得知操作系統系統態下的CPU時間，操作系統總的CPU時間就是用戶態和系統態時間之和，如果ORACLE數據庫使用的CPU時間占操作系統總的CPU時間90％以上，說明服務器CPU基本上被ORACLE數據庫使用著，這是合理，反之，說明服務器CPU被其它程序占用過多，ORACLE數據庫無法得到更多的CPU時間。

數據庫管理員還可以通過查看v$sesstat數據字典來獲得當前連接ORACLE數據庫各個會話占用的CPU時間，從而得知什么會話耗用服務器CPU比較多。

出現CPU資源不足的情況是很多的：SQL語句的重解析、低效率的SQL語句、鎖沖突都會引起CPU資源不足。

1、數據庫管理員可以執行下述語句來查看SQL語句的解析情況：

SELECT * FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)');

這里parse time cpu是系統服務時間，parse time elapsed是響應時間，用戶等待時間

waite time = parse time elapsed – parse time cpu

由此可以得到用戶SQL語句平均解析等待時間＝waite time / parse count。這個平均等待時間應該接近于0，如果平均解析等待時間過長，數據庫管理員可以通過下述語句

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS;

來發現是什么SQL語句解析效率比較低。程序員可以優化這些語句，或者增加ORACLE參數SESSION_CACHED_CURSORS的值。

2、數據庫管理員還可以通過下述語句：

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

查看低效率的SQL語句，優化這些語句也有助于提高CPU的利用率。

3、3、數據庫管理員可以通過v$system_event數據字典中的“latch free”統計項查看ORACLE數據庫的沖突情況，如果沒有沖突的話，latch free查詢出來沒有結果。如果沖突太大的話，數據庫管理員可以降低spin_count參數值，來消除高的CPU使用率。

內存參數的調整

內存參數的調整主要是指ORACLE數據庫的系統全局區（SGA）的調整。SGA主要由三部分構成：共享池、數據緩沖區、日志緩沖區。

1、 1、 共享池由兩部分構成：共享SQL區和數據字典緩沖區，共享SQL區是存放用戶SQL命令的區域，數據字典緩沖區存放數據庫運行的動態信息。數據庫管理員通過執行下述語句：

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

來查看共享SQL區的使用率。這個使用率應該在90％以上，否則需要增加共享池的大小。數據庫管理員還可以執行下述語句：

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

查看數據字典緩沖區的使用率，這個使用率也應該在90％以上，否則需要增加共享池的大小。

2、 2、 數據緩沖區。數據庫管理員可以通過下述語句：

SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

來查看數據庫數據緩沖區的使用情況。查詢出來的結果可以計算出來數據緩沖區的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。

這個命中率應該在90％以上，否則需要增加數據緩沖區的大小。

3、 3、 日志緩沖區。數據庫管理員可以通過執行下述語句：

select name,value from v$sysstat where name in ('redo entries','redo log space requests');查看日志緩沖區的使用情況。查詢出的結果可以計算出日志緩沖區的申請失敗率：

申請失敗率＝requests/entries，申請失敗率應該接近于0，否則說明日志緩沖區開設太小，需要增加ORACLE數據庫的日志緩沖區。

如何進行oracle數據庫性能優化

你最好買一本專門講ORACLE性能優化的書，好好看看\x0d\x0a1、調整數據庫服務器的性能\x0d\x0aOracle數據庫服務器是整個系統的核心，它的性能高低直接影響整個系統的性能，為了調整Oracle數據庫服務器的性能，主要從以下幾個方面考慮： \x0d\x0a1.1、調整操作系統以適合Oracle數據庫服務器運行\x0d\x0aOracle數據庫服務器很大程度上依賴于運行服務器的操作系統，如果操作系統不能提供最好性能，那么無論如何調整，Oracle數據庫服務器也無法發揮其應有的性能。 \x0d\x0a1.1.1、為Oracle數據庫服務器規劃系統資源 \x0d\x0a據已有計算機可用資源, 規劃分配給Oracle服務器資源原則是：盡可能使Oracle服務器使用資源最大化,特別在Client/Server中盡量讓服務器上所有資源都來運行Oracle服務。 \x0d\x0a1.1.2、調整計算機系統中的內存配置 \x0d\x0a多數操作系統都用虛存來模擬計算機上更大的內存，它實際上是硬盤上的一定的磁盤空間。當實際的內存空間不能滿足應用軟件的要求時，操作系統就將用這部分的磁盤空間對內存中的信息進行頁面替換，這將引起大量的磁盤I/O操作，使整個服務器的性能下降。為了避免過多地使用虛存，應加大計算機的內存。 \x0d\x0a1.1.3、為Oracle數據庫服務器設置操作系統進程優先級 \x0d\x0a不要在操作系統中調整Oracle進程的優先級，因為在Oracle數據庫系統中，所有的后臺和前臺數據庫服務器進程執行的是同等重要的工作，需要同等的優先級。所以在安裝時，讓所有的數據庫服務器進程都使用缺省的優先級運行。 \x0d\x0a1.2、調整內存分配\x0d\x0aOracle數據庫服務器保留3個基本的內存高速緩存，分別對應3種不同類型的數據：庫高速緩存，字典高速緩存和緩沖區高速緩存。庫高速緩存和字典高速緩存一起構成共享池，共享池再加上緩沖區高速緩存便構成了系統全程區(SGA)。SGA是對數據庫數據進行快速訪問的一個系統全程區，若SGA本身需要頻繁地進行釋放、分配，則不能達到快速訪問數據的目的，因此應把SGA放在主存中，不要放在虛擬內存中。內存的調整主要是指調整組成SGA的內存結構的大小來提高系統性能，由于Oracle數據庫服務器的內存結構需求與應用密切相關，所以內存結構的調整應在磁盤I/O調整之前進行。 \x0d\x0a1.2.1、庫緩沖區的調整 \x0d\x0a庫緩沖區中包含私用和共享SQL和PL/SQL區，通過比較庫緩沖區的命中率決定它的大小。要調整庫緩沖區，必須首先了解該庫緩沖區的活動情況，庫緩沖區的活動統計信息保留在動態性能表v$librarycache數據字典中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數；Reloads列給出SQL 和PL/SQL塊的隱式分析或對象定義重裝載時在庫程序緩沖區中發生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區的命中率合適；若sum(pins)/sum(reloads)1, 則需調整初始化參數 shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.2、數據字典緩沖區的調整 \x0d\x0a數據字典緩沖區包含了有關數據庫的結構、用戶、實體信息。數據字典的命中率，對系統性能影響極大。數據字典緩沖區的使用情況記錄在動態性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是對相應項請求次數的統計；Getmisses 列是引起緩沖區出錯的數據的請求次數。對于頻繁訪問的數據字典緩沖區，sum(getmisses)/sum(gets)10%～15%。若大于此百分數，則應考慮增加數據字典緩沖區的容量，即需調整初始化參數shared_pool_size來重新調整分配給共享池的內存量。 \x0d\x0a1.2.3、緩沖區高速緩存的調整 \x0d\x0a用戶進程所存取的所有數據都是經過緩沖區高速緩存來存取，所以該部分的命中率，對性能至關重要。緩沖區高速緩存的使用情況記錄在動態性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調整。 \x0d\x0a \x0d\x0aSelect name,value from v$sysstat where name in ('dbblock gets','consistent gets','physical reads'); \x0d\x0a \x0d\x0adbblock gets和consistent gets的值是請求數據緩沖區中讀的總次數。physical reads的值是請求數據時引起從盤中讀文件的次數。從緩沖區高速緩存中讀的可能性的高低稱為緩沖區的命中率，計算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩沖區高速緩存的內存量，即db_block_buffers可設置分配緩沖區高速緩存的數據塊的個數。緩沖區高速緩存的總字節數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數據塊大小的字節數，可查詢 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述數據庫的初始化參數以后，必須先關閉數據庫，在重新啟動數據庫后才能使新的設置起作用。

Oracle 高效的分區消除

1.高效的分區消除

分區表存在的最大意義在于，可以有效地做到分區消除

分區表其實是將一個大對象分成了多個小對象

同樣的語句查詢有相同記錄的表，分區表的查詢代價僅為111，邏輯讀僅為566，而普通表的代價卻為1298，邏輯讀為5990，性能方面有著天壤之別。差別如此之大，應該是和分區表查詢只遍歷了13個分區中的一個有關。在分區表查詢的執行計劃中p_start和p_stop都標記上9，表示只遍歷了第9個分區。這樣避開了對其余12個分區的查詢，就是分區消除

測試針對普通表的增加了 area_code 條件的相同 SQL 語句：

組合分區導致范圍定位得更小了，只產生了60個邏輯讀，比之前的566個邏輯讀還要少。至于普通表的查詢，因為始終是全表掃描，所以邏輯讀依然和之前差不多，達到5931個。

組合分區雖然只有60個邏輯讀，比之前范圍分區的566個要少得多，但是代價卻差不多，甚至還略大于之前的，因為分區數過多，調用有開銷。在試驗中，表的總記錄數不過10萬，全表掃描開銷都不是太大，這時Oracle內部調用的開銷影響就相對較大。如果表是一張超級大表，比如有上億條記錄，那這些開銷相比而言就可以忽略不計了，

分區表應用在大表上更合適，至少要大于100萬條記錄的表方可考慮。

新聞名稱：oracle怎么優化分區,oracle擴展分區
文章轉載：http://vcdvsql.cn/article18/hsdddp.html

成都網站建設公司_創新互聯，為您提供虛擬主機、網站排名、移動網站建設、網站收錄、搜索引擎優化、商城網站

廣告

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