Ⅰ oracle資料庫，搜索百萬級別數據分頁優化問題
oracle count 百萬級 分頁查詢記錄總數、總條數優化
Oracle count 百萬級 查詢記錄總數、總條數優化
最近做一個項目時，做分頁時，發現分頁查詢速度很慢，分頁我做的是兩次查詢，一次是查詢總數，一次是查詢分頁結果
[java] view plain
/** 查詢總記錄數 **/
SELECT
COUNT(id)
FROM
USER
order by
id
/** 查詢結果集 **/
select
*
from
( select
row_.*,
rownum rownum_
from
( select
id ,
user_number,
user_name,
user_password,
sex,
Registered_time,
last_login_time,
post
from
USER u
order by
u.id) row_
where
rownum <= ?
)
where
rownum_ > ?
user表中的記錄是128萬多條，這個是沒有查詢條件時的查詢，也就是用戶剛剛進入模塊時的查詢，發現查詢時間是2566ms~2152ms之間，單獨執行每條語句，發現第一條的執行時間在2000ms以上，在PL/sql中執行的結果也證實了我的判斷。所以要對select count語句進行優化。
在網上找了很多優化方案，大多不盡人意，（分表的方式聽上去不錯，不過由於單表是歷史原因，這里就不作考慮）。最後找到一個比較令人滿意的答。就是在語句中加入 /*+ROWID(USER)*/或者/*+ INDEX(USER ID) */ 來提高查詢效果。
聽說這個就是強制使用索引統計結果？如果有哪位大蝦能把原理詳細告訴我，請來多多指點！
[java] view plain
SELECT /*+ROWID(USER)*/ count(*) FROM USER t
或者
SELECT /*+ INDEX(USER ID) */ count(*) FROM USER t
使用後，單條統計總數的查詢在800ms左右，分頁查詢結果基本在900ms~950ms之間，基本在一秒之內，達到了當初設計需求。
當然，這個是沒有加查詢條件的，當把查詢條件加入後，不管前面加不加強制索引，結果時間都在2000ms之間，所以，如果要進行有條件的查詢，就要在where條件中進行優化。特別注意條件欄位查詢前後順序。
Ⅱ java中分頁查詢oracle數據速度慢，怎麼解決好
查詢速度慢
1。sql復雜，多表格關聯。無優化。
2。通信阻礙，網路慢
3。oracle內部查詢慢，無索引。
4。查詢數據量大，pga小。
你可以試一下，簡單的表，簡單的查詢，速度會不會變化，
或則同樣的表，簡單的查詢等。可以確定問題發生在哪個環節，再尋求解決方法。
同樣的sql語句，直接放到oracle內部執行。速度如何。
Ⅲ 如何實現Oracle資料庫的分頁顯示
因為Oracle資料庫沒有Top關鍵字，所以這里就不能夠像微軟的數據據那樣操作，這里有兩種方法：
1）、一種是利用相反的。
PAGESIZE：每頁顯示的記錄數
CURRENTPAGE：當前頁號
數據表的名字是：components
索引主鍵字是：id
select * from components where id not in(select id from components where rownum<=(PAGESIZE*(CURRENTPAGE-1))) and rownum<=PAGESIZE order by id;
如下例：
select * from components where id not in(select id from components where rownum<=100) and rownum<=10 order by id;
從101到記錄開始選擇，選擇前面10條。
2）、使用minus，即中文的意思就是減去，呵呵，這語句非常的有意思，也非常好記
select * from components where rownum<=(PAGESIZE*(CURRENTPAGE-1)) minus select * from components where rownum<=(PAGESIZE*(CURRENTPAGE-2));
如例：select * from components where rownum<=10 minus select * from components where rownum<=5;.
3）、一種是利用Oracle的rownum，這個是Oracle查詢自動返回的序號，一般不顯示，但是可以通過select rownum from [表名]，可以看到，是從1到當前的記錄總數。
select * from (select rownum tid,components.* from components where rownum<=100) where tid<=10;
Ⅳ oracle資料庫如何實現分頁效果
通過ROWNUM來實現
1.
查詢前10條記錄
SELECT
*
FROM
TestTable
WHERE
ROWNUM
<=
10
2.
查詢第11到第20條記錄SELECT
*
FROM
(SELECT
TestTable.*,
ROWNUM
ro
FROM
TestTable
WHERE
ROWNUM
<=20)
WHERE
ro
>
10
3.
Oracle
分頁和排序：按照name欄位升序排列後的前10條記錄
SELECT
*
FROM
(SELECT
*
FROM
TestTable
ORDERY
BY
name
ASC)
WHERE
ROWNUM
<=
10
4.
按照name欄位升序排列後的第11到第20條記錄
SELECT
*
FROM
(SELECT
tt.*,
ROWNUM
ro
FROM
(SELECT
*
FROM
TestTable
ORDER
BY
name
ASC)
tt
WHERE
ROWNUM
<=20)
WHERE
ro
>
10
Ⅳ 資料庫性能優化有哪些措施
1、調整數據結構的設計
這一部分在開發信息系統之前完成，程序員需要考慮是否使用ORACLE資料庫的分區功能，對於經常訪問的資料庫表是否需要建立索引等。
2、調整應用程序結構設計
這一部分也是在開發信息系統之前完成，程序員在這一步需要考慮應用程序使用什麼樣的體系結構，是使用傳統的Client/Server兩層體系結構，還是使用Browser/Web/Database的三層體系結構。不同的應用程序體系結構要求的資料庫資源是不同的。
3、調整資料庫SQL語句
應用程序的執行最終將歸結為資料庫中的SQL語句執行，因此SQL語句的執行效率最終決定了ORACLE資料庫的性能。ORACLE公司推薦使用ORACLE語句優化器(Oracle Optimizer)和行鎖管理器(row-level manager)來調整優化SQL語句。
4、調整伺服器內存分配
內存分配是在信息系統運行過程中優化配置的，資料庫管理員可以根據資料庫運行狀況調整資料庫系統全局區(SGA區)的數據緩沖區、日誌緩沖區和共享池的大小;還可以調整程序全局區(PGA區)的大小。需要注意的是，SGA區不是越大越好，SGA區過大會佔用操作系統使用的內存而引起虛擬內存的頁面交換，這樣反而會降低系統。
5、調整硬碟I/O
這一步是在信息系統開發之前完成的。資料庫管理員可以將組成同一個表空間的數據文件放在不同的硬碟上，做到硬碟之間I/O負載均衡。
6、調整操作系統參數
例如：運行在UNIX操作系統上的ORACLE資料庫，可以調整UNIX數據緩沖池的大小，每個進程所能使用的內存大小等參數。
實際上，上述資料庫優化措施之間是相互聯系的。ORACLE資料庫性能惡化表現基本上都是用戶響應時間比較長，需要用戶長時間的等待。但性能惡化的原因卻是多種多樣的，有時是多個因素共同造成了性能惡化的結果，這就需要資料庫管理員有比較全面的計算機知識，能夠敏感地察覺到影響資料庫性能的主要原因所在。另外，良好的資料庫管理工具對於優化資料庫性能也是很重要的。
一、ORACLE資料庫性能優化工具
常用的資料庫性能優化工具有：
ORACLE資料庫在線數據字典，ORACLE在線數據字典能夠反映出ORACLE動態運行情況，對於調整資料庫性能是很有幫助的。
操作系統工具，例如UNIX操作系統的vmstat，iostat等命令可以查看到系統系統級內存和硬碟I/O的使用情況，這些工具對於管理員弄清出系統瓶頸出現在什麼地方有時候很有用。
SQL語言跟蹤工具(SQL TRACE FACILITY)，SQL語言跟蹤工具可以記錄SQL語句的執行情況，管理員可以使用虛擬表來調整實例，使用SQL語句跟蹤文件調整應用程序性能。SQL語言跟蹤工具將結果輸出成一個操作系統的文件，管理員可以使用TKPROF工具查看這些文件。
ORACLE Enterprise Manager(OEM)，這是一個圖形的用戶管理界面，用戶可以使用它方便地進行資料庫管理而不必記住復雜的ORACLE資料庫管理的命令。
EXPLAIN PLAN——SQL語言優化命令，使用這個命令可以幫助程序員寫出高效的SQL語言。
二、ORACLE資料庫的系統性能評估
信息系統的類型不同，需要關注的資料庫參數也是不同的。資料庫管理員需要根據自己的信息系統的類型著重考慮不同的資料庫參數。
1、在線事務處理信息系統(OLTP)，這種類型的信息系統一般需要有大量的Insert、Update操作，典型的系統包括民航機票發售系統、銀行儲蓄系統等。OLTP系統需要保證資料庫的並發性、可靠性和最終用戶的速度，這類系統使用的ORACLE資料庫需要主要考慮下述參數：
資料庫回滾段是否足夠?
是否需要建立ORACLE資料庫索引、聚集、散列?
系統全局區(SGA)大小是否足夠?
SQL語句是否高效?
2、數據倉庫系統(Data Warehousing)，這種信息系統的主要任務是從ORACLE的海量數據中進行查詢，得到數據之間的某些規律。資料庫管理員需要為這種類型的ORACLE資料庫著重考慮下述參數：
是否採用B*-索引或者bitmap索引?
是否採用並行SQL查詢以提高查詢效率?
是否採用PL/SQL函數編寫存儲過程?
有必要的話，需要建立並行資料庫提高資料庫的查詢效率
三、SQL語句的調整原則
SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率是很不相同的。程序員可以使用EXPLAIN PLAN語句來比較各種實現方案，並選出最優的實現方案。總得來講，程序員寫SQL語句需要滿足考慮如下規則：
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、選擇聯合查詢的聯合次序。考慮下面的例子：
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、在子查詢中慎重使用IN或者NOT IN語句，使用where (NOT) exists的效果要好的多。
4、慎重使用視圖的聯合查詢，尤其是比較復雜的視圖之間的聯合查詢。一般對視圖的查詢最好都分解為對數據表的直接查詢效果要好一些。
5、可以在參數文件中設置SHARED_POOL_RESERVED_SIZE參數，這個參數在SGA共享池中保留一個連續的內存空間，連續的內存空間有益於存放大的SQL程序包。
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、資料庫管理員可以通過v$system_event數據字典中的「latch free」統計項查看ORACLE資料庫的沖突情況，如果沒有沖突的話，latch free查詢出來沒有結果。如果沖突太大的話，資料庫管理員可以降低spin_count參數值，來消除高的CPU使用率。
五、內存參數的調整
內存參數的調整主要是指ORACLE資料庫的系統全局區(SGA)的調整。SGA主要由三部分構成：共享池、數據緩沖區、日誌緩沖區。
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、數據緩沖區。資料庫管理員可以通過下述語句：
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、日誌緩沖區。資料庫管理員可以通過執行下述語句：
select name,value from v$sysstat where name in ('redo entries','redo log space requests');
查看日誌緩沖區的使用情況。查詢出的結果可以計算出日誌緩沖區的申請失敗率：
申請失敗率=requests/entries，申請失敗率應該接近於0，否則說明日誌緩沖區開設太小，需要增加ORACLE資料庫的日誌緩沖區。
昆明北大青鳥 java培訓班轉載自網路 如有侵權請聯系我們 感謝您的關注 謝謝支持
