作為一個開發(fā)/測試人員，或多或少都得和數(shù)據(jù)庫打交道，而對數(shù)據(jù)庫的操作歸根到底都是SQL語句，所有操作到最后都是操作數(shù)據(jù)，那么對sql性能的掌控又成了我們工作中一件非常重要的工作。下面簡單介紹下一些查看oracle性能的一些實用方法：
專注于為中小企業(yè)提供成都網(wǎng)站建設(shè)、網(wǎng)站制作服務(wù),電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)南明免費做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動了超過千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實現(xiàn)規(guī)模擴充和轉(zhuǎn)變。
1、查詢每臺機器的連接數(shù)
select?t.MACHINE,count(*)?from?v$session?t?group?by?t.MACHINE
這里所說的每臺機器是指每個連接oracle數(shù)據(jù)庫的服務(wù)器，每個服務(wù)器都有配置連接數(shù)據(jù)庫的連接數(shù)，以websphere為例，在數(shù)據(jù)源中，每個數(shù)據(jù)源都有配置其最大/最小連接數(shù)。
執(zhí)行SQL后，可以看到每個服務(wù)器連接oracle數(shù)據(jù)庫的連接數(shù)，若某個服務(wù)器的連接數(shù)非常大，或者已經(jīng)達(dá)到其最大連接數(shù)，那么這臺服務(wù)器上的應(yīng)用可能有問題導(dǎo)致其連接不能正常釋放。
2、查詢每個連接數(shù)的sql_text
v$session表里存在的連接不是一直都在執(zhí)行操作，如果sql_hash_value為空或者0，則該連接是空閑的，可以查詢哪些連接非空閑，?web3?是機器名，就是WebSphere?Application?Server?的主機名。
select?t.sql_hash_value,t.*??from?v$session?t?where?t.MACHINE='web3'?and?t.sql_hash_value!=0
這個SQL查詢出來的結(jié)果不能看到具體的SQL語句，需要看具體SQL語句的執(zhí)行下面的方法。
3、查詢每個活動的連接執(zhí)行什么sql
select?sid,username,sql_hash_value,b.sql_text
from?v$session?a,v$sqltext?b
where?a.sql_hash_value?=?b.HASH_VALUE?and?a.MACHINE='web3'
order?by?sid,username,sql_hash_value,b.piece
order?by這句話的作用在于，sql_text每條記錄不是保存一個完整的sql,需要以sql_hash_value為關(guān)鍵id,以piece排序，如圖
Username是執(zhí)行SQL的數(shù)據(jù)庫用戶名，一個sql_hash_value下的SQL_TEXT組合成一個完整的SQL語句。這樣就可以看到一個連接執(zhí)行了哪些SQL。
4、.從V$SQLAREA中查詢最占用資源的查詢
select?b.username?username,a.disk_reads?reads,?a.executions?exec,
a.disk_reads/decode(a.executions,0,1,a.executions)?rds_exec_ratio,
a.sql_text?Statement
from??v$sqlarea?a,dba_users?b
where?a.parsing_user_id=b.user_id
and?a.disk_reads??100000
order?by?a.disk_reads?desc;
用buffer_gets列來替換disk_reads列可以得到占用最多內(nèi)存的sql語句的相關(guān)信息。
V$SQL是內(nèi)存共享SQL區(qū)域中已經(jīng)解析的SQL語句。
該表在SQL性能查看操作中用的比較頻繁的一張表，關(guān)于這個表的詳細(xì)信息大家可以去?上學(xué)習(xí)，介紹得比較詳細(xì)。我這里主要就將該表的常用幾個操作簡單介紹一下：
1、列出使用頻率最高的5個查詢：
select?sql_text,executions
from?(select?sql_text,executions,
rank()?over
(order?by?executions?desc)?exec_rank
from?v$sql)
where?exec_rank?=5;
該查詢結(jié)果列出的是執(zhí)行最頻繁的5個SQL語句。對于這種實用非常頻繁的SQL語句，我們需要對其進(jìn)行持續(xù)的優(yōu)化以達(dá)到最佳執(zhí)行性能。
2、找出需要大量緩沖讀?。ㄟ壿嬜x）操作的查詢：
select?buffer_gets,sql_text
from?(select?sql_text,buffer_gets,
dense_rank()?over
(order?by?buffer_gets?desc)?buffer_gets_rank
from?v$sql)
where?buffer_gets_rank=5;
這種需要大量緩沖讀取（邏輯讀）操作的SQL基本是大數(shù)據(jù)量且邏輯復(fù)雜的查詢中會遇到，對于這樣的大數(shù)據(jù)量查詢SQL語句更加需要持續(xù)的關(guān)注，并進(jìn)行優(yōu)化。
3、持續(xù)跟蹤有性能影響的SQL。
SELECT?*?FROM?(
SELECT?PARSING_USER_ID,EXECUTIONS,SORTS,
COMMAND_TYPE,DISK_READS,sql_text?FROM?v$sqlarea
ORDER?BY?disk_reads?DESC
)
WHERE?ROWNUM10
這個語句在SQL性能查看中用的比較多，可以明顯的看出哪些SQL會影響到數(shù)據(jù)庫性能。
本文主要介紹了使用SQL查詢方式查看oracle數(shù)據(jù)庫SQL性能的部分常用方法。此外還有許多工具也能實現(xiàn)SQL性能監(jiān)控，大家可以在網(wǎng)上搜索相關(guān)知識進(jìn)行學(xué)習(xí)。
轉(zhuǎn)載僅供參考，版權(quán)屬于原作者
一、 磁盤方面調(diào)優(yōu)
1. 規(guī)范磁盤陣列
RAID 10比RAID5更適用于OLTP系統(tǒng)，RAID10先鏡像磁盤，再對其進(jìn)行分段，由于對數(shù)據(jù)的小規(guī)模訪問會比較頻繁，所以對OLTP適用。而RAID5,優(yōu)勢在于能夠充分利用磁盤空間，并且減少陣列的總成本。但是由于陣列發(fā)出一個寫入請求時，必須改變磁盤上已修改的塊，需要從磁盤上讀取“奇偶校驗”塊，并且使用已修改的塊計算新的奇偶校驗塊，然后把數(shù)據(jù)寫入磁盤，且會限制吞吐量。對性能有所影響，RAID5適用于OLAP系統(tǒng)。
2. 數(shù)據(jù)文件分布
分離下面的東西,避免磁盤競爭
? SYSTEM表空間
? TEMPORARY表空間
? UNDO表空間
? 聯(lián)機重做日志（放在最快的磁盤上）
? 操作系統(tǒng)磁盤
? ORACLE安裝目錄
? 經(jīng)常被訪問的數(shù)據(jù)文件
? 索引表空間
? 歸檔區(qū)域（應(yīng)該總是與將要恢復(fù)的數(shù)據(jù)分離）
例：
2 /: System
2 /u01: Oracle Software
2 /u02: Temporary tablespace, Control file1
2 /u03: Undo Segments, Control file2
2 /u04: Redo logs, Archive logs, Control file4
2 /u05: System, SYSAUX tablespaces
2 /u06: Data1 ,control file3
2 /u07: Index tablespace
2 /u08: Data2
通過下列語句查詢確定IO問題
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
3. 增大日志文件
u 增大日志文件的大小，從而增加處理大型INSERT,DELETE,UPDATE操作的比例
查詢?nèi)罩疚募顟B(tài)
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查詢?nèi)罩厩袚Q時間
select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes
from v$log_history a ,v$log_history b
where a.RECID=b.RECID+1
order by a.FIRST_TIME desc
增大日志文件大小，以及對每組增加日志文件（一個主文件、一個多路利用文件）
u 增大LOG_CHECKPOINT_INTERVAL參數(shù)，現(xiàn)已不提倡使用它
如果低于每半小時切換一次日志，就增大聯(lián)機重做日志大小。如果處理大型批處理任務(wù)時頻繁進(jìn)行切換，就增大聯(lián)機重做日志數(shù)目。
alter database add logfile member ‘/log.ora’ to group 1;
alter database drop logfile member ‘/log.ora’;
4. UNDO表空間
修改三個初始參數(shù)：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=#of minutes
5. 不要在系統(tǒng)表空間中執(zhí)行排序
二、 初始化參數(shù)調(diào)優(yōu)
32位的尋址最大支持應(yīng)該是2的32次方，就是4G大小。但實際中32位系統(tǒng)（XP，windows2003等MS32位系統(tǒng), ubuntu等linux32 位系統(tǒng)）要能利用4G內(nèi)存，都是采用內(nèi)存重映射技術(shù)。需要主板及系統(tǒng)的支持。如果關(guān)閉主板BIOS的重映射功能，系統(tǒng)將不能利用4G內(nèi)存，可能只達(dá)3.5G.而在windows下看到的一般為3.25G。所以SGA設(shè)置為內(nèi)存的40%，但不能超過3.25G
1. 重要初始化參數(shù)
l SGA_MAX_SIZE
l SGA_TARGET
l PGA_AGGREGATE_TARGET
l DB_CACHE_SIZE
l SHARED_POOL_SIZE
2. 調(diào)整DB_CACHE_SIZE來提高性能
它設(shè)定了用來存儲和處理內(nèi)存中數(shù)據(jù)的SGA區(qū)域大小，從內(nèi)存中取數(shù)據(jù)比磁盤快10000倍以上
根據(jù)以下查詢出數(shù)據(jù)緩存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
一個事務(wù)處理程序應(yīng)該保證得到95%以上的命中率，命中率從90%提高到98%可能會提高500%的性能，ORACLE正在通過CPU或服務(wù)時間與等待時間來分析系統(tǒng)性能，不太重視命中率，不過現(xiàn)在的庫緩存和字典緩存仍將命中率作為基本的調(diào)整方法。
在調(diào)整DB_CACHE_SIZE時使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';
如果查詢的命中率過低，說明缺少索引或者索引受到限制，通過V$SQLAREA視圖查詢執(zhí)行緩慢的SQL
3. 設(shè)定DB_BLOCK_SIZE來反映數(shù)據(jù)讀取量大小
OLTP一般8K
OLAP一般16K或者32K
4. 調(diào)整SHARED_POOL_SIZE以優(yōu)化性能
正確地調(diào)整此參數(shù)可以同等可能地共享SQL語句，使得在內(nèi)存中便能找到使用過的SQL語句。為了減少硬解析次數(shù)，優(yōu)化對共享SQL區(qū)域的使用，需盡量使用存儲過程、使用綁定變量
保證數(shù)據(jù)字典緩存命中率在95%以上
select ((1- sum(getmisses)/(sum(gets)+sum(getmisses)))*100) hitratio
from v$rowcache
where gets+getmisses 0;
如果命中率小于 99％，就可以考慮增加shared pool 以提高library cache 的命中率
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
通常規(guī)則是把它定為DB_CACHE_SIZE大小的50%－150%，在使用了大量存儲過程或程序包，但只有有限內(nèi)存的系統(tǒng)里，最后分配為150%。在沒有使用存儲過程但大量分配內(nèi)存給DB_CACHE_SIZE的系統(tǒng)里，這個參數(shù)應(yīng)該為10%－20%
5. 調(diào)整PGA_AGGREGATE_TARGET以優(yōu)化對內(nèi)存的應(yīng)用
u OLTP ：totalmemory*80%*20%
u DSS: totalmemory*80%*50%
6. 25個重要初始化參數(shù)
2 DB_CACHE_SIZE：分配給數(shù)據(jù)緩存的初始化內(nèi)存
2 SGA_TARGET：使用了自動內(nèi)存管理，則設(shè)置此參數(shù)。設(shè)置為0可禁用它
2 PGA_AGGREGATE_TARGET：所有用戶PGA軟內(nèi)存最大值
2 SHARED_POOL_SIZE：分配給數(shù)據(jù)字典、SQL和PL／SQL的內(nèi)存
2 SGA_MAX_SIZE：SGA可動態(tài)增長的最大內(nèi)存
2 OPTIMIZER_MODE：
2 CURSOR_SHARING：把字面SQL轉(zhuǎn)換成帶綁定變更的SQL，可減少硬解析開銷
2 OPTIMIZER_INDEX_COST_ADJ：索引掃描成本和全表掃描成本進(jìn)行調(diào)整，設(shè)定在1－10間會強制頻繁地使用索引，保證索引可用性
2 QUERY_REWRITE_ENABLED：用于啟用具體化視圖和基于函數(shù)的索引功能
2 DB_FILE_MULTIBLOCK_READ_COUNT：對于全表掃描，為了更有效執(zhí)行IO，此參數(shù)可在一次IO中讀取多個塊
2 LOG_BUFFER：為內(nèi)存中沒有提交的事務(wù)分配緩沖區(qū)（非動態(tài)參數(shù)）
2 DB_KEEP_CACHE_SIZE：分配給KEEP池或者額外數(shù)據(jù)緩存的內(nèi)存
2 DB_RECYCLE_CACHE_SIZE：
2 DBWR_IO_SLAVES：如果沒有異步IO，參數(shù)等同于DB_WRITER_PROCESSES模擬異步IO而分配的從SGA到磁盤的寫入器數(shù)。如果有異步IO，則使用DB_WRITER_PROCESSES設(shè)置多個寫程序，在DBWR期間更快地寫出臟塊
2 LARGE_POOL_SIZE：分配給大型PLSQL或其他一些很少使用的ORACLE選項LARGET池的總塊數(shù)
2 STATISTICS_LEVEL：啟用顧問信息，并可選擇提供更多OS統(tǒng)計信息來改進(jìn)優(yōu)化器決策。默認(rèn)：TYPICAL
2 JAVA_POOL_SIZE：為JVM使用的JAVA存儲過程所分配的內(nèi)存
2 JAVA_MAX_SESSIONSPACE_SIZE：跟蹤JAVA類的用戶會話狀態(tài)所用內(nèi)存上限
2 MAX_SHARED_SERVERS：當(dāng)使用共享服務(wù)器時的共享服務(wù)器上限
2 WORKAREA_SIZE_POLICY：啟用PGA大小自動管理
2 FAST_START_MTTR_TARGET：完成一次崩潰恢復(fù)的大概時間／S
2 LOG_CHECKPOINT_INTERVAL：檢查點頻率
2 OPEN_CURSORS：指定了保存用戶語句的專用區(qū)域大小，如此設(shè)置過高會導(dǎo)致ORA-4031
2 DB_BLOCK_SIZE：數(shù)據(jù)庫默認(rèn)塊大小
2 OPTIMIZER_DYNAMIC_SAMPLING：控制動態(tài)抽樣查詢讀取的塊數(shù)量，對正在使用全局臨時表的系統(tǒng)非常有用
三、 SQL調(diào)優(yōu)1. 使用提示
1.1 改變執(zhí)行路徑
通過OPTIMIZER_MODE參數(shù)指定優(yōu)化器使用方法，默認(rèn)ALL_ROWS
? ALL_ROWS 可得最佳吞吐量執(zhí)行查詢所有行
? FIRST_ROWS(n) 可使優(yōu)化器最快檢索出第一行:
select /*+ FIRST_ROWS(1) */ store_id,… from tbl_store
1.2 使用訪問方法提示
允許開發(fā)人員改變訪問的實際查詢方式，經(jīng)常使用INDEX提示
? CLUSTER 強制使用集群
? FULL
? HASH
? INDEX 語法：/*+ INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….
當(dāng)不指定任何INDEX時，優(yōu)化器會選擇最佳的索引
SELECT /*+ INDEX */ STORE_ID FROM TBL_STORE
? INDEX_ASC 8I開始默認(rèn)是升序，所以與INDEX同效
? INDEX_DESC
? INDEX_COMBINE 用來指定多個位圖索引，而不是選擇其中最好的索引
? INDEX_JOIN 只需訪問這些索引，節(jié)省了重新檢索表的時間
? INDEX_FFS 執(zhí)行一次索引的快速全局掃描，只處理索引，不訪問具體表
? INDEX_SS
? INDEX_SSX_ASC
? INDEX_SS_DESC
? NO_INDEX
? NO_INDEX_FFS
? NO_INDEX_SS
1.3 使用查詢轉(zhuǎn)換提示
對于數(shù)據(jù)倉庫非常有幫助
? FACT
? MERGE
? NO_EXPAND 語法：/*+ NO_EXPAND */ column1,…
保證OR組合起的IN列表不會陷入困境，/*+ FIRST_ROWS NO_EXPAND */
? NO_FACT
? NO_MERGE
? NO_QUERY_TRANSFORMATION
? NO_REWRITE
? NO_STAR_TRANSFORMATION
? NO_UNSET
? REWRITE
? STAR_TRANSFORMATION
? UNSET
? USE_CONCAT
1.4 使用連接操作提示
顯示如何將連接表中的數(shù)據(jù)合并在一起，可用兩提示直接影響連接順序。LEADING指定連接順序首先使用的表，ORDERED告訴優(yōu)化器基于FROM子句中的表順序連接這些表，并使用第一個表作為驅(qū)動表（最行訪問的表）
ORDERED語法：/*+ ORDERED */ column 1,….
訪問表順序根據(jù)FROM后的表順序來
LEADING語法：/*+ LEADING(TABLE1) */ column 1,….
類似于ORDER，指定驅(qū)動表
? NO_USE_HASH
? NO_USE_MERGE
? NO_USE_NL
? USE_HASH前提足夠的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET
通?？梢詾檩^大的結(jié)果集提供最佳的響應(yīng)時間
? USE_MERGE
? USE_NL 通?？梢砸宰羁焖俣确祷匾粋€行
? USE_NL_WITH_INDEX
1.5 使用并行執(zhí)行
? NO_PARALLEL
? NO_PARALLEL_INDEX
? PARALLEL
? PARALLEL_INDEX
? PQ_DISTRIBUTE
1.6 其他提示
? APPEND 不會檢查當(dāng)前所用塊中是否有剩余空間，而直接插入到表中，會直接將數(shù)據(jù)添加到新的塊中。
? CACHE 會將全表掃描全部緩存到內(nèi)存中，這樣可直接在內(nèi)存中找到數(shù)據(jù)，不用在磁盤上查詢
? CURSOR_SHARING_EXACT
? DRIVING_SITE
? DYNAMIC_SAMPLING
? MODEL_MIN_ANALYSIS
? NOAPPEND
? NOCACHE
? NO_PUSH_PRED
? NO_PUSH_SUBQ
? NO_PX_JOIN_FILTER
? PUSH_PRED
? PUSH_SUBQ 強制先執(zhí)行子查詢，當(dāng)子查詢很快返回少量行時，這些行可以用于限制外部查詢返回行數(shù)，可極大地提高性能
例：select /*+PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc=’1’)
? PX_JOIN_FILTER
? QB_NAME
2. 調(diào)整查詢
2.1 在V$SQLAREA中選出最占用資源的查詢
HASH_VALUE：SQL語句的Hash值。
ADDRESS：SQL語句在SGA中的地址。
PARSING_USER_ID：為語句解析第一條CURSOR的用戶
VERSION_COUNT：語句cursor的數(shù)量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的共享內(nèi)存總數(shù)
PERSISTENT_MEMORY：cursor使用的常駐內(nèi)存總數(shù)
RUNTIME_MEMORY：cursor使用的運行時內(nèi)存總數(shù)。
SQL_TEXT：SQL語句的文本（最大只能保存該語句的前1000個字符）。
MODULE,ACTION：用了DBMS_APPLICATION_INFO時session解析第一條cursor時信息
SORTS: 語句的排序數(shù)
CPU_TIME: 語句被解析和執(zhí)行的CPU時間
ELAPSED_TIME: 語句被解析和執(zhí)行的共用時間
PARSE_CALLS: 語句的解析調(diào)用(軟、硬)次數(shù)
EXECUTIONS: 語句的執(zhí)行次數(shù)
INVALIDATIONS: 語句的cursor失效次數(shù)
LOADS: 語句載入(載出)數(shù)量
ROWS_PROCESSED: 語句返回的列總數(shù)
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS100 order by a.DISK_READS desc;
2.2 在V$SQL中選出最占用資源的查詢
與V$SQLAREA類似
select * from
(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)
where rank_buffers 11
2.3 確定何時使用索引
2 當(dāng)查詢條件只需要返回很少的行（受限列）時，則需要建立索引，不同的版本中這個返回要求不同
V5:20% V7:7% V8i,V9i:4% V10g: 5%
查看表上的索引
select a.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'
2 修正差的索引，可使用提示來限制很差的索引，如INDEX，F(xiàn)ULL提示
2 在SELECT 和WHERE中的列使用索引
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)
對于系統(tǒng)中很關(guān)鍵的查詢，可以考慮建立此類連接索引
2 在一個表中有多個索引時可能出現(xiàn)麻煩，使用提示INDEX指定使用索引
2 使用索引合并，使用提示INDEX_JOIN
2 基于函數(shù)索引，由于使用了函數(shù)造成查詢很慢.必須基于成本的優(yōu)化模式,參數(shù)：
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
2.4 在內(nèi)存中緩存表
將常用的相對小的表緩存到內(nèi)存中，但注意會影響到嵌套循環(huán)連接上的驅(qū)動表
alter table tablename cache;
2.5 使用EXISTS 與嵌套子查詢 代替IN
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);
(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’
(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
四、 使用STATSPACK和AWR報表調(diào)整等待和閂鎖
1. 10GR2里的腳本
在$ORACLE_HOME/RDBMS/ADMIN下
Spcreate.sql 通過調(diào)用spcusr.sql spctab.sql 和spcpkg.sql創(chuàng)建STATSPACK環(huán)境，使用SYSDBA運行它
Spdrop.sql 調(diào)用sptab.sql和spdusr.sql刪除整個STATSPACK環(huán)境，使用SYSDBA運行它
Spreport.sql 這是生成報表的主要腳本，由PERFSTAT用戶運行
Sprepins.sql 為指定的數(shù)據(jù)庫和實例生成實例報表
Sprepsql.sql 為指定的SQL散列值生成SQL報表
Sprsqins.sql 為指定的數(shù)據(jù)庫和實例生成SQL報表
Spauto.sql 使用DBMS_JOB自動進(jìn)行統(tǒng)計數(shù)據(jù)收集（照相）
Sprepcon.sql 配置SQLPLUS變量來設(shè)置像閾值這樣的內(nèi)容的配置文件
Spurge.sql 刪除給定數(shù)據(jù)庫實例一定范圍內(nèi)的快照ID，不刪除基線快照
Sptrunc.sql 截短STATSPACK表里所有性能數(shù)據(jù)
五、 執(zhí)行快速系統(tǒng)檢查1. 緩沖區(qū)命中率
查詢緩沖區(qū)命中率
select (1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0)) +
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
from v$sysstat;
1、使用兩邊加‘%’號的查詢，Oracle是不通過索引的，所以查詢效率很低。
例如：select count(*) from lui_user_base t where t.user_name like '%cs%';
2、like '...%'和 like'%...'雖然走了索引，但是效率依然很低。
3、有人說使用如下sql，他的效率提高了10倍，但是數(shù)據(jù)量小的時候
select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')
我拿100w跳數(shù)據(jù)做了測試，效果一般，依然很慢，原因：
select rowid from lui_user_base t where t.user_name like '%cs%' ? 這條sql執(zhí)行很快，那是相當(dāng)?shù)目?，但是放到select count(*) from lui_user_base where rowid in（）里后，效率就會變的很慢了。
4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0
這種查詢效果很好，速度很快，推薦使用這種。因為我對oracle內(nèi)部機制不是很懂，只是對結(jié)果做了一個說明。
5、有人說了用全文索引，我看了，步驟挺麻煩，但是是個不錯的方法，留著備用：
對cmng_custominfo 表中的address字段做全文檢索：
1，在oracle9201中需要創(chuàng)建一個分詞的東西：
BEGIN
ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');
--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用
end;
2，創(chuàng)建全文檢索：
CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');
3，查詢時候，使用：
select * from cmng_custominfo where contains (address, '金色新城')1;
4，需要定期進(jìn)行同步和優(yōu)化：
同步：根據(jù)新增記錄的文本內(nèi)容更新全文搜索的索引。
begin
ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');
end;
優(yōu)化：根據(jù)被刪除記錄清除全文搜索索引中的垃圾
begin
ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');
end;
5，采用job做步驟4中的工作：
1)該功能需要利用oracle的JOB功能來完成
因為oracle9I默認(rèn)不啟用JOB功能，所以首先需要增加ORACLE數(shù)據(jù)庫實例的JOB配置參數(shù):
job_queue_processes=5
重新啟動oracle數(shù)據(jù)庫服務(wù)和listener服務(wù)。
2)同步 和 優(yōu)化
--同步 sync:
variable jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END;
--優(yōu)化
variable jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');
commit;
END;
其中， 第一個job的SYSDATE + (1/24/4)是指每隔15分鐘同步一次，第二個job的SYSDATE + 1是每隔1天做一次全優(yōu)化。具體的時間間隔，可以根據(jù)應(yīng)用的需要而定。
6，索引重建
重建索引會刪除原來的索引，重新生成索引，需要較長的時間。
重建索引語法如下：
ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;
據(jù)網(wǎng)上一些用家的體會，oracle重建索引的速度也是比較快的，有一用家這樣描述：
Oracle 的全文檢索建立和維護(hù)索引要比ms sql server都要快得多，筆者的65萬記錄的一個表建立索引只需要20分鐘，同步一次只需要1分鐘。
因此，也可以考慮用job的辦法定期重建索引。
你最好買一本專門講ORACLE性能優(yōu)化的書，好好看看\x0d\x0a1、調(diào)整數(shù)據(jù)庫服務(wù)器的性能\x0d\x0aOracle數(shù)據(jù)庫服務(wù)器是整個系統(tǒng)的核心，它的性能高低直接影響整個系統(tǒng)的性能，為了調(diào)整Oracle數(shù)據(jù)庫服務(wù)器的性能，主要從以下幾個方面考慮： \x0d\x0a1.1、調(diào)整操作系統(tǒng)以適合Oracle數(shù)據(jù)庫服務(wù)器運行\(zhòng)x0d\x0aOracle數(shù)據(jù)庫服務(wù)器很大程度上依賴于運行服務(wù)器的操作系統(tǒng)，如果操作系統(tǒng)不能提供最好性能，那么無論如何調(diào)整，Oracle數(shù)據(jù)庫服務(wù)器也無法發(fā)揮其應(yīng)有的性能。 \x0d\x0a1.1.1、為Oracle數(shù)據(jù)庫服務(wù)器規(guī)劃系統(tǒng)資源 \x0d\x0a據(jù)已有計算機可用資源, 規(guī)劃分配給Oracle服務(wù)器資源原則是：盡可能使Oracle服務(wù)器使用資源最大化,特別在Client/Server中盡量讓服務(wù)器上所有資源都來運行Oracle服務(wù)。 \x0d\x0a1.1.2、調(diào)整計算機系統(tǒng)中的內(nèi)存配置 \x0d\x0a多數(shù)操作系統(tǒng)都用虛存來模擬計算機上更大的內(nèi)存，它實際上是硬盤上的一定的磁盤空間。當(dāng)實際的內(nèi)存空間不能滿足應(yīng)用軟件的要求時，操作系統(tǒng)就將用這部分的磁盤空間對內(nèi)存中的信息進(jìn)行頁面替換，這將引起大量的磁盤I/O操作，使整個服務(wù)器的性能下降。為了避免過多地使用虛存，應(yīng)加大計算機的內(nèi)存。 \x0d\x0a1.1.3、為Oracle數(shù)據(jù)庫服務(wù)器設(shè)置操作系統(tǒng)進(jìn)程優(yōu)先級 \x0d\x0a不要在操作系統(tǒng)中調(diào)整Oracle進(jìn)程的優(yōu)先級，因為在Oracle數(shù)據(jù)庫系統(tǒng)中，所有的后臺和前臺數(shù)據(jù)庫服務(wù)器進(jìn)程執(zhí)行的是同等重要的工作，需要同等的優(yōu)先級。所以在安裝時，讓所有的數(shù)據(jù)庫服務(wù)器進(jìn)程都使用缺省的優(yōu)先級運行。 \x0d\x0a1.2、調(diào)整內(nèi)存分配\x0d\x0aOracle數(shù)據(jù)庫服務(wù)器保留3個基本的內(nèi)存高速緩存，分別對應(yīng)3種不同類型的數(shù)據(jù)：庫高速緩存，字典高速緩存和緩沖區(qū)高速緩存。庫高速緩存和字典高速緩存一起構(gòu)成共享池，共享池再加上緩沖區(qū)高速緩存便構(gòu)成了系統(tǒng)全程區(qū)(SGA)。SGA是對數(shù)據(jù)庫數(shù)據(jù)進(jìn)行快速訪問的一個系統(tǒng)全程區(qū)，若SGA本身需要頻繁地進(jìn)行釋放、分配，則不能達(dá)到快速訪問數(shù)據(jù)的目的，因此應(yīng)把SGA放在主存中，不要放在虛擬內(nèi)存中。內(nèi)存的調(diào)整主要是指調(diào)整組成SGA的內(nèi)存結(jié)構(gòu)的大小來提高系統(tǒng)性能，由于Oracle數(shù)據(jù)庫服務(wù)器的內(nèi)存結(jié)構(gòu)需求與應(yīng)用密切相關(guān)，所以內(nèi)存結(jié)構(gòu)的調(diào)整應(yīng)在磁盤I/O調(diào)整之前進(jìn)行。 \x0d\x0a1.2.1、庫緩沖區(qū)的調(diào)整 \x0d\x0a庫緩沖區(qū)中包含私用和共享SQL和PL/SQL區(qū)，通過比較庫緩沖區(qū)的命中率決定它的大小。要調(diào)整庫緩沖區(qū)，必須首先了解該庫緩沖區(qū)的活動情況，庫緩沖區(qū)的活動統(tǒng)計信息保留在動態(tài)性能表v$librarycache數(shù)據(jù)字典中，可通過查詢該表來了解其活動情況，以決定如何調(diào)整。 \x0d\x0a \x0d\x0aSelect sum(pins),sum(reloads) from v$librarycache; \x0d\x0a \x0d\x0aPins列給出SQL語句，PL/SQL塊及被訪問對象定義的總次數(shù)；Reloads列給出SQL 和PL/SQL塊的隱式分析或?qū)ο蠖x重裝載時在庫程序緩沖區(qū)中發(fā)生的錯誤。如果sum(pins)/sum(reloads) ≈0，則庫緩沖區(qū)的命中率合適；若sum(pins)/sum(reloads)1, 則需調(diào)整初始化參數(shù) shared_pool_size來重新調(diào)整分配給共享池的內(nèi)存量。 \x0d\x0a1.2.2、數(shù)據(jù)字典緩沖區(qū)的調(diào)整 \x0d\x0a數(shù)據(jù)字典緩沖區(qū)包含了有關(guān)數(shù)據(jù)庫的結(jié)構(gòu)、用戶、實體信息。數(shù)據(jù)字典的命中率，對系統(tǒng)性能影響極大。數(shù)據(jù)字典緩沖區(qū)的使用情況記錄在動態(tài)性能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調(diào)整。 \x0d\x0a \x0d\x0aSelect sum(gets),sum(getmisses) from v$rowcache; \x0d\x0a \x0d\x0aGets列是對相應(yīng)項請求次數(shù)的統(tǒng)計；Getmisses 列是引起緩沖區(qū)出錯的數(shù)據(jù)的請求次數(shù)。對于頻繁訪問的數(shù)據(jù)字典緩沖區(qū)，sum(getmisses)/sum(gets)10%～15%。若大于此百分?jǐn)?shù)，則應(yīng)考慮增加數(shù)據(jù)字典緩沖區(qū)的容量，即需調(diào)整初始化參數(shù)shared_pool_size來重新調(diào)整分配給共享池的內(nèi)存量。 \x0d\x0a1.2.3、緩沖區(qū)高速緩存的調(diào)整 \x0d\x0a用戶進(jìn)程所存取的所有數(shù)據(jù)都是經(jīng)過緩沖區(qū)高速緩存來存取，所以該部分的命中率，對性能至關(guān)重要。緩沖區(qū)高速緩存的使用情況記錄在動態(tài)性能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調(diào)整。 \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的值是請求數(shù)據(jù)緩沖區(qū)中讀的總次數(shù)。physical reads的值是請求數(shù)據(jù)時引起從盤中讀文件的次數(shù)。從緩沖區(qū)高速緩存中讀的可能性的高低稱為緩沖區(qū)的命中率，計算公式： \x0d\x0a \x0d\x0aHit Ratio=1-(physical reds/(dbblock gets+consistent gets)) \x0d\x0a \x0d\x0a如果Hit Ratio60%～70%，則應(yīng)增大db_block_buffers的參數(shù)值。db_block_buffers可以調(diào)整分配給緩沖區(qū)高速緩存的內(nèi)存量，即db_block_buffers可設(shè)置分配緩沖區(qū)高速緩存的數(shù)據(jù)塊的個數(shù)。緩沖區(qū)高速緩存的總字節(jié)數(shù)=db_block_buffers的值*db_block_size的值。db_block_size 的值表示數(shù)據(jù)塊大小的字節(jié)數(shù)，可查詢 v$parameter 表： \x0d\x0a \x0d\x0aselect name,value from v$parameter where name='db_block_size'; \x0d\x0a \x0d\x0a在修改了上述數(shù)據(jù)庫的初始化參數(shù)以后，必須先關(guān)閉數(shù)據(jù)庫，在重新啟動數(shù)據(jù)庫后才能使新的設(shè)置起作用。
重慶分公司
重慶分公司
