#
本文是關于Oracle數據庫調試與優化方面的文章，主要介紹Oracle數據庫中命中率相關的問題，包括不同的算法之間性能的比對。關于Oracle中各個命中率的計算以及相關的調優 1) Library Cache的命中率: 計算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pi
本文是關于Oracle數據庫調試與優化方面的文章，主要介紹Oracle數據庫中命中率相關的問題，包括不同的算法之間性能的比對。關于Oracle中各個命中率的計算以及相關的調優
1)Library Cache的命中率:計算公式:Library Cache Hit Ratio = sum(pinhits) / sum(pins)
|
1 |
SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;
|
通常在98%以上，否則，需要要考慮加大共享池，綁定變量，修改cursor_sharing等參數。
2)計算共享池內存使用率:
|
1
2 |
SELECT (1 - ROUND(BYTES / (&TSP_IN_M * 1024 * 1024), 2)) * 100 || '%'
FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';
|
其中: &TSP_IN_M是你的總的共享池的SIZE(M)
共享池內存使用率，應該穩定在75%-90%間，太小浪費內存，太大則內存不足。
查詢空閑的共享池內存:
|
1
2 |
SELECT * FROM V$SGASTAT WHERE
NAME = 'free memory' AND POOL = 'shared pool';
|
3)db buffer cache命中率:計算公式:Hit ratio = 1 - [physical reads/(block gets + consistent gets)]
|
1
2
3 |
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))
"Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';
|
通常應在90%以上，否則，需要調整,加大DB_CACHE_SIZE
外一種計算命中率的方法(摘自ORACLE官方文檔<<數據庫性能優化>>):
命中率的計算公式為:
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) / (db block gets + consistent gets - physical reads direct - physical reads direct (lob))
分別代入上一查詢中的結果值,就得出了Buffer cache的命中率
|
1
2
3
4 |
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN(
'session logical reads', 'physical reads',
'physical reads direct', 'physical reads direct (lob)',
'db block gets', 'consistent gets');
|
4)數據緩沖區命中率：
|
1
2
3
4
5 |
SQL> select value from v$sysstat where name ='physical reads';
SQL> select value from v$sysstat where name ='physical reads direct';
SQL> select value from v$sysstat where name ='physical reads direct (lob)';
SQL> select value from v$sysstat where name ='consistent gets';
SQL> select value from v$sysstat where name = 'db block gets';
|
這里命中率的計算應該是令 x = physical reads direct + physical reads direct (lob),命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100,通常如果發現命中率低于90%,則應該調整應用可可以考慮是否增大數據緩沖區
5)共享池的命中率：
|
1
2 |
select sum(pinhits-reloads)/sum(pins)*100 "hit radio"
from v$librarycache;
|
假如共享池的命中率低于95%,就要考慮調整應用(通常是沒使用bind var )或者增加內存
6)計算在內存中排序的比率:
|
1 |
SELECT * FROM v$sysstat t WHERE NAME='sorts (memory)';
|
查詢內存排序數
|
1 |
SELECT * FROM v$sysstat t WHERE NAME='sorts (disk)';
|
查詢磁盤排序數caculate sort in memory ratio
|
1 |
SELECT round(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100||'%' FROM dual;
|
此比率越大越好,太小整要考慮調整,加大PGA
7)PGA的命中率:
計算公式:BP x 100 / (BP + EBP)
BP: bytes processed
EBP: extra bytes read/written
|
1 |
SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage';
|
或者從OEM的圖形界面中查看
我們可以查看一個視圖以獲取Oracle的建議值:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 |
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT
FROM V$PGA_TARGET_ADVICE;
The output of this query might look like the following:
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
63 23 367
125 24 30
250 30 3
375 39 0
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
|
在此例中:PGA至少要分配375M
我個人認為PGA命中率不應該低于50%
以下的SQL統計sql語句執行在三種模式的次數: optimal memory size, one-pass memory size, multi-pass memory size:
|
1
2
3 |
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total,4))
percentage FROM (SELECT name, value cnt, (sum(value) over ())
total FROM V$SYSSTAT WHERE name like 'workarea exec%');
|
8)共享區字典緩存區命中率
計算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
命中率應大于0.85
|
1 |
select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;
|
9)數據高速緩存區命中率
計算公式：1-(physical reads / (db block gets + consistent gets))
命中率應大于0.90最好
|
1 |
select name,value from v$sysstat where name in ('physical reads','db block gets','consistent gets');
|
10)共享區庫緩存區命中率
計算公式：SUM(pins - reloads) / SUM(pins)
命中率應大于0.99
|
1 |
select sum(pins-reloads)/sum(pins) from v$librarycache;
|
11)檢測回滾段的爭用
SUM(waits)值應小于SUM(gets)值的1%
|
1 |
select sum(gets),sum(waits),sum(waits)/sum(gets) from v$rollstat;
|
12)檢測回滾段收縮次數
|
1
2 |
select name,shrinks from v$rollstat, v$rollname
where v$rollstat.usn = v$rollname.usn;
|
幾個常用的檢查語句
1. 查找排序最多的SQL:
|
1
2 |
SELECT HASH_VALUE, SQL_TEXT, SORTS, EXECUTIONS
FROM V$SQLAREA ORDER BY SORTS DESC;
|
2.查找磁盤讀寫最多的SQL:
|
1
2
3
4 |
SELECT * FROM (SELECT sql_text,disk_reads "total disk" ,
executions "total exec",disk_reads/executions "disk/exec"
FROM v$sql WHERE executions>0 and is_obsolete='N' ORDER BY
4 desc) WHERE ROWNUM<11 ;
|
3.查找工作量最大的SQL(實際上也是按磁盤讀寫來排序的):
|
1
2
3
4
5
6
7
8 |
select substr(to_char(s.pct, '99.00'), 2) || '%' load,
s.executions executes,p.sql_text from(select address,disk_reads,executions,pct,rank() over
(order by disk_reads desc) ranking from (select
address,disk_reads,executions,100 * ratio_to_report
(disk_reads) over () pct from sys.v_$sql where
command_type != 47) where disk_reads > 50 * executions) s,
ys.v_$sqltext p where s.ranking <= 5 and p.address = s.address
order by 1, s.address, p.piece;
|
4. 用下列SQL工具找出低效SQL:
|
1
2
3
4
5 |
select executions,disk_reads,buffer_gets,round((buffer_gets-
disk_reads)/buffer_gets,2) Hit_radio,round(disk_reads/executions,2)
reads_per_run,sql_text From v$sqlarea Where executions>0 and
buffer_gets >0 and (buffer_gets-disk_reads)/buffer_gets<0.8
Order by 4 desc;
|
5、根據sid查看對應連接正在運行的sql
|
1
2
3
4
5
6
7 |
select /*+ push_subq */command_type,sql_text,sharable_mem,persistent_mem,
runtime_mem,sorts,version_count,loaded_versions,open_versions,
users_opening,executions,users_executing,loads,first_load_time,
invalidations,parse_calls,disk_reads,buffer_gets,rows_processed,
sysdate start_time,sysdate finish_time,’>’||address
sql_address,’N’status From v$sqlarea Where address=
(select sql_address from v$session where sid=&sid);
|
***************Oracle 緩沖區命中率低的分析及解決辦法******************
首先確定下面的查詢結果:
1,緩沖區命中率的查詢(是否低于90%):
|
1
2
3 |
select round((1 - sum(decode(name,'physical reads',value,0)) /
(sum(decode(name,'db block gets',value,0)) + sum(decode(name,'
consistent gets',value,0))) ),4) *100 || '%' chitrati from v$sysstat;
|
2,使用率的查詢(有無free狀態的數據快.):
|
1 |
select count(*), status from v$bh group by status ;
|
3,相關等待事件的查詢(是否有相關等待事件)
|
1
2 |
select event,total_waits from v$system_event where event
in ('free buffer waits');
|
4,當前大小(是否已經很大)
|
1
2 |
select value/1024/1024 cache_size from v$parameter
where name='db_cache_size'
|
5,top等待事件分析(Db file scatered read的比率是否大)
|
1
2
3
4
5
6
7 |
select event ,total_waits,suml from (select
event,total_waits,round(total_waits/sumt*100,2)||'%' suml
from (select event,total_waits from v$system_event ),
(select sum(total_waits) sumt from v$system_event)
order by total_waits desc) where rownum<6 and event
not like 'rdbms%' and event not like 'pmon%' and event
not like 'SQL*Net%' and event not like 'smon%';
|
6,db_cache_advice建議值(9i后的新特性,可以根據他更好的調整cache_size)
|
1
2 |
select block_size,size_for_estimate,size_factor,
estd_physical_reads from v$db_cache_advice;
|
說明分析:
緩沖區命中率(低于90的命中率就算比較低的).
沒有free不一定說明需要增加,還要結合當前cache_size的大小(我們是否還可以再增大,是否有需要增加硬件,增加開銷),
空閑緩沖區等待說明進程找不到空閑緩沖區，并通過寫出灰緩沖區，來加速數據庫寫入器生成空閑緩沖區，當DBWn將塊寫入磁盤后，灰數據緩沖區將被釋放，以便重新使用.產生這種原因主要是:
1，DBWn可能跟不上寫入灰緩沖區：i/0系統較慢，盡量將文件均勻的分布于所有設備，
2，緩沖區過小或過大。
3，可以增加db_writer_processes數量。
4,可能有很大的一個事物，或者連續的大事物
我們需要長期觀察這個事件是否長期存在并數值一直在增大,如果一直在增大,則說明需要增大db_cache大小.或優化sql.
數據分散讀等待,通常表現存在著與全表掃描相關的等待，邏輯讀時，在內存中進行的全表掃描一般是零散地，而并非連續的被分散到緩沖區的各個部分，可能有索引丟失，或被仰制索引的存在。該等待時間在數據庫會話等待多塊io讀取結束的時候產生，并把指定的塊數離散的分布在數據緩沖區。這意味這全表掃描過多，或者io不足或爭用，
存在這個事件,多數都是問題的,這說明大量的全部掃描而未采用索引.
db_cache_advice對我們調整db_cache_size大小有一定的幫助,但這只是一個參考，不一定很精確。
通過上面6種情況的綜合分析,判斷是否需要增加大cache_size. 或者把常用的(小)表放到keep區。
但多數的時候做這些不會解決質的問題,
而真正的問題主要是對sql語句的優化(如:是否存在大量的全表掃描等)
索引是在不需要改變程序的情況下,對數據庫性能，sql語句提高的最實用的方法.
我在生產中遇到過類似的問題,200M的cache_size,命中率很低21%,但通過對sql語句的優化(添加索引,避免全表掃描),命中率增加到96%,程序運行時間由原來的2小時減少到不到10分鐘.
這就提到了怎么定位高消耗的sql問題.全表掃描的問題,在這里不做細致的解說,這里只說明方法,我會在相關的章節專門介紹怎么使用這些工具
1,sql_trace跟蹤session.用tkprof 分別輸出磁盤讀,邏輯讀,運行時間長的sql進行優化.這些高消耗的sql一般都伴隨著全表掃描.
2,statspack分析.在系統繁忙時期進行時間點的統計分析,產看TOP事件是否有Db file scatered read.并查看TOP sql語句是否存在問題等.
還要說一句：當然在硬件允許的情況下，盡量增大db_cache_size 減少磁盤讀，但并不是越大越好，一定要根據自己的庫數據量的程度來調節，因為大的db_cache_size同樣會增大數據庫管理的開銷，當然可能開銷并不會明顯的影響數據庫的性能，硬件價格也越來越低，這就需要我們具體問題具體分析了，在我看來物盡其用就最好了，盡量不要浪費，找到問題的本質。調優是一件很藝術的事。
***********************Oracle數據庫緩沖區命中率*****************
1、查看Oracle數據庫緩沖區命中率
|
1
2
3
4 |
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value))
"BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 40 and b.statistic# = 41 and c.statistic# = 42;
|
2、Tags: oracle
數據庫緩沖區命中率：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14 |
select value from v$sysstat where name ='physical reads';
value 3714179
select value from v$sysstat where name ='physical reads direct';
value 0
select value from v$sysstat where name ='physical reads direct(lob)';
value 0
select value from v$sysstat where name ='consistent gets';
value 856309623
select value from v$sysstat where name ='db block gets';
value 19847790
|
這里命中率的計算應該是
令x=physical reads direct + physical reads direct(lob)
命中率=100-(physical reads -x)/(consistent gets +db block gets -x)*100
通常如果發現命中率低于90%，則應該調整應用可以考慮是否增大數據加
共享池的命中率
|
1 |
select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;
|
如果共享池的命中率低于95%就要考慮調整應用(通常是沒應用bind var)或者增加內存。
關于排序部分
|
1 |
select name,value from v$sysstat where name like '%sort%';
|
如果我們發現sorts(disk)/(sorts(memory)+sorts(disk))的比例過高，則通常意味著sort_area_size部分內存教較小，可考慮調整相應的參數。
關于log_buffer
|
1
2 |
select name,value from v$sysstat where name
in ('redo entries','redo buffer allocation retries');
|
假如redo buffer allocation retries/redo entries的比例超過1%我們就可以考慮增加log_buffer.
1.統計信息簡介 統計信息主要是描述數據庫中表，索引的大小，規模，數據分布狀況等的一類信息。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引字段的行數，不同值的大小等，都屬于統計信息。CBO正是根據這些統計信息數據，計算出不同訪問路
1.統計信息簡介
統計信息主要是描述數據庫中表，索引的大小，規模，數據分布狀況等的一類信息。比如，表的行數，塊數，平均每行的大小，索引的leaf blocks，索引字段的行數，不同值的大小等，都屬于統計信息。CBO正是根據這些統計信息數據，計算出不同訪問路徑下，不同join 方式下，各種計劃的成本，最后選擇出成本最小的計劃。
在CBO(基于代價的優化器模式)條件下，SQL語句的執行計劃由統計信息來決定，若沒有統計信息則會采取動態采樣的方式決定執行計劃！可以說統計信息關乎sql的執行計劃是否正確，屬于sql執行的指導思想，oracle的初始化參數statistics_level控制收集統計信息的級別，有三個參數值:
BASIC :收集基本的統計信息
TYPICAL：收集大部分統計信息(數據庫的默認設置)
ALL：收集全部統計信息
Oracle 10g之后，Query Optimizer就已經將CBO作為默認優化器，并且Oracle官方不再支持RBO服務。但是，通過優化器參數optimizer_mode，我們可以控制Oracle優化器生成不同模式下的執行計劃。
關于優化器的請參考：《SQL性能優化之optimizer_mode參數原理滲透解析》
2.如何收集統計信息
2.1 統計信息的內容：
1）Table statistics
Number of rows --行數量
Number of blocks --block數量
Average row length --平均行的長度.
2）Column statistics
Number of distinct values (NDV) in column --列中distinct的值
Number of nulls in column --列中null的值
Data distribution (histogram) --數據分布
3）Index statistics
Number of leaf blocks --子節點的塊數量
Levels --子節點數量
Clustering factor --集群因子
4）System statistics
I/O performance and utilization --IO性能和利用率
CPU performance and utilization --CPU的性能和利用率
2.2 收集統計信息
Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包來收集，Oracle 建議使用DBMS_STATS包來收集統計信息，因為DBMS_STATS包收集的更廣，并且更準確。analyze 在以后的版本中可能會被移除。
DBMS_STATS常用的幾個過程如下：
|
1
2
3
4
5
6
7
8
9
10
11
12 |
dbms_stats.gather_table_stats 收集表、列和索引的統計信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有對象的統計信息；
dbms_stats.gather_index_stats 收集索引的統計信息；
dbms_stats.gather_system_stats 收集系統統計信息
dbms_stats.GATHER_DICTIONARY_STATS：所有字典對象的統計；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系統模式的統計
dbms_stats.delete_table_stats 刪除表的統計信息
dbms_stats.delete_index_stats 刪除索引的統計信息
dbms_stats.export_table_stats 輸出表的統計信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 設置表的統計
dbms_stats.auto_sample_size
|
analyze 命令的語法如下：
|
1
2
3 |
SQL>analyze table tablename compute statistics;
SQL>analyze table tablename compute statistics for all indexes;
SQL>analyze table tablename delete statistics
|
2.3 統計信息的分類
Oracle 的Statistic 信息的收集分兩種：自動收集和手工收集。
Oracle 的Automatic Statistics Gathering 是通過Scheduler 來實現收集和維護的。Job 名稱是GATHER_STATS_JOB, 該Job收集數據庫所有對象的2種統計信息：
（1）Missing statistics（統計信息缺失）
（2）Stale statistics（統計信息陳舊）
該Job 是在數據庫創建的時候自動創建，并由Scheduler來管理。Scheduler 在maintenance windows open時運行gather job。 默認情況下，job 會在每天晚上10到早上6點和周末全天開啟。該過程首先檢測統計信息缺失和陳舊的對象。然后確定優先級，再開始進行統計信息。
Scheduler Job的stop_on_window_close 屬性控制GATHER_STATS_JOB 是否繼續。該屬性默認值為True. 如果該值設置為False，那么GATHER_STATS_JOB 會中斷， 而沒有收集完的對象將在下次啟動時繼續收集。
Gather_stats_job 調用dbms_stats.gather_database_stats_job_proc過程來收集statistics 的信息。 該過程收集對象statistics的條件如下：
（1）對象的統計信息之前沒有收集過。
（2）當對象有超過10%的rows 被修改，此時對象的統計信息也稱為stale statistics。
但是對于高度變化的表在白天的活動期間被TRUNCATE/DROP并重建或者塊加載超過本身總大小10%的對象；我們可以將這些表上的統計設置為NULL
可以通過以下SQL來查看：
|
1
2
3 |
select job_name, program_name, enabled, stop_on_window_close
from dba_scheduler_jobs
where job_name = 'gather_stats_job';
|
為了決定是否對對象進行監控，Oracle 提供了一個參數STATISTICS_LEVEL。通過設置初始化參數STATISTIC_LEVEL 為TYPICAL 或ALL，就可以自動收集統計信息(默認值為TYPICAL，因此可以隨即啟用自動收集統計信息的功能)。STATISTIC_LEVEL 參數的值可以激活GATHER_STATS_JOB。
在10g中表監控默認是激活的，如果STATISTICS_LEVEL設置為basic，不僅不能監控表，而且將禁掉如下一些10g的新功能：
（1）ASH(Active Session History)
（2）ASSM(Automatic Shared Memory Management)
（3）AWR(Automatic Workload Repository)
（4）ADDM(Automatic Database Diagnostic Monitor)
|
1
2
3
4
5 |
sys@ORCL> show parameter statistics_level;
NAME TYPE VALUE
statistics_level string TYPICAL
|
當啟動對象的監控后，從上次統計信息收集之后的的信息，如inserts，updates，deletes 等，這些改變的信息會記錄到user_tab_modifications 視圖。
當對象的數據發生改變之后， 經過幾分鐘的延時，這些信息寫入到user_tab_modifications視圖，然后dbms_stats.flush_database_monitoring_info過程就會發現這些信息，并講這些信息保存在內存中。
當監控的對象被修改的部分超過10%時，gather_database_stats 或者gather_schema_stats 過程就會去收集這些stale statistics
3.統計信息的存儲位置以及常用數據字典
3.1 統計信息常用數據字典
統計信息收集如下數據：
（1）表自身的分析： 包括表中的行數，數據塊數，行長等信息。
（2）列的分析：包括列值的重復數，列上的空值，數據在列上的分布情況。
（3）索引的分析： 包括索引葉塊的數量，索引的深度，索引的聚合因子等。
這些統計信息存放在以下的數據字典里：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 |
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
|
3.2 表的統計信息
包含表行數，使用的塊數，空的塊數，塊的使用率，行遷移和鏈接的數量，pctfree，pctused的數據，行的平均大小：
|
1
2
3
4
5
6
7 |
SELECT NUM_ROWS,
BLOCKS,
EMPTY_BLOCKS,
AVG_SPACE,
CHAIN_CNT,
AVG_ROW_LEN
FROM USER_TABLES
|
3.3索引列的統計信息
包含索引的深度（B-Tree的級別），索引葉級的塊數量，集群因子（clustering_factor), 唯一值的個數。
|
1
2
3
4
5
6
7 |
SELECT BLEVEL,
LEAF_BLOCKS,
DISTINCT_KEYS,
AVG_LEAF_BLOCKS_PER_KEY,
AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR
FROM USER_INDEXES
|
3.4 列的統計信息
包含唯一的值個數，列最大小值，密度（選擇率），數據分布（直方圖信息），NUll值個數
|
1
2
3
4
5
6
7
8 |
SELECT NUM_DISTINCT,
LOW_VALUE,
HIGH_VALUE,
DENSITY,
NUM_NULLS,
NUM_BUCKETS,
HISTOGRAM
FROM USER_TAB_COLUMNS
|
對于統計信息的搜集，談談個人的幾點理解：
1.統計信息默認是存放在數據字典表中的，也只有數據字典中的統計信息，才會影響到CBO。
2.DBMS_STATS 提供的CREATE_STAT_TABLE 過程，只是生成一個用戶自定義的特定格式的表，用來存放統計信息罷了，這個表中的統計信息是不會影響到統計信息的。
3.GATHER 系列過程中，如果指定stattab，statid，statown 參數（也可以不指定），則是搜集的統計信息除了更新到數據字典外，還在statown 用戶下的stattab 表中存放一份，標示為 statid;
4.EXPORT和IMPORT 系列的過程中,stattab,statid,statown 參數不能為空，分別表示把數據字典中的當前統計信息導出到用戶自定義的表中，以及把用戶表中的統計信息導入到數據字典中，很明顯可以看出，這里的導入操作和上面GATHER 操作會改變統計信息，可能會引起執行執行計劃的改變，因此要慎重操作。
5.每次統計信息搜集前，將舊的統計信息備份起來是很有必要的；特別是保留一份或多份系統在穩定時期的統計信息也是很有必要的。
6.多長時間搜集一次統計信息，對于統計信息如何備份和保留，搜集統計信息時如何選擇合適的采樣，并行，直方圖設置等都比較重要，需要設計一個較好的統計信息搜集策略。
在OCP 10g考試中會有個跟統計信息相關的考試題：
1. In your Oracle 10g database , you have scheduled a job to update the optimizer statistics at 05:00 pm
every Friday. The job has successfully completed. Which three pieces of information would you check to
confirm that the statistics have been collected? (Choose three.)
A. average row size
B. last analyzed date
C. size of table in bytes
D. size of table in database blocks
E. number of free blocks in the free list
F. number of extents present in the table
Answer: ABD
摘要: 以下測試環境為Oracle 10g 10.2.0.4版本，測試對Oracle的統計信息的收集與管理。首先依據dba_objects創建一張測試表：
SQL> create table eygle as select * from dba_objects; Table created對該表進行統計信息收集，這里未指定method_opt，則Oracle將采用 FOR ALL COL...
閱讀全文
摘要: 獲取昨天：
select trunc(SYSDATE-1) from dual;
檢查一下：
select to_char (trunc(SYSDATE-1),'yyyy-mm-dd HH24:MI:SS') from dual;
獲取上個月第一天00:00:00：
select add_months(trunc(sysdate,'MON'),-1) from d...
閱讀全文
Oracle數據庫啟動分為三個步驟：
nomount，找到初始化文件pfile或者spfile，創建SGA并啟動后臺進程但不允許訪問數據庫。
mount，根據初始化文件找到控制文件（Control File），為某些DBA活動裝載數據庫但不允許用戶訪問數據庫。
open，根據控制文件找到數據文件（Data File），重做日志文件（Redo File），使用戶可以訪問數據庫。
關閉數據庫的4個不同命令
shutdown normal(等于shutdown)
正常是關閉的缺省方式正常的數據庫關閉在下列情況下進行
• 不允許新的連接
• 等待會話結束
• 等待事務結束
• 做一個檢查點并關閉數據文件
• 下一次啟動時將不要求實例恢復
shutdown transactional
事務處理關閉防止客戶丟失工作事務處理數據庫關閉在下列情況下進行
• 不允許新的連接
• 不等待會話結束
• 等待事務結束
• 做一個檢查點并關閉數據文件
• 下一次啟動將不要求實例恢復
shutdown immediate
立即關閉數據庫在下列情況下進行
• 不允許新的連接
• 不等待會話結束
• 不等待事務結束
• 未結束的事務自動回滾（rollback）
• 做一個檢查點并關閉數據文件
• 下一次啟動將不要求例程恢復
shutdown abort
如果正常和立即關閉選項不起作用可以中止當前數據庫例程中止例程可以在下列情況下進行
• 不允許新的連接
• 不等待會話結束
• 不等待事務結束
• 不做檢查點且沒有關閉數據文件
• 下一次啟動將要求實例恢復
啟動數據庫相關命令
startup（默認啟動到open狀態）
startup nomount
startup mount
alter database mount;
alter database open;
初始化文件
pfile，文本文件，可以手工編輯該文件
spfile，二進制文件，不能手工修改里面的參數，只能使用數據庫命令進行修改，數據庫啟動默認使用spfile
查看初始化文件路徑
show parameter spfile;
根據spfile創建pfile文件
create pfile from spfile;
create pfile='/u01/app/pfile.ora' fromspfile;
create spfile from pfile='/u01/app/pfile.ora';
查看控制文件的路徑
select name from v$controlfile;
查看控制文件的內容，可以根據ctl.trc重建控制文件
alter database backup controlfile to trace as '/u01/app/ctl.trc';
查看數據文件的位置
select name from v$datafile;
查看重做日志文件的位置
select member from v$logfile;
數據庫的密碼文件
Windows，$ORACLE_HOME/database/PWDorcl.ora
Linux/Unix，$ORACLE_HOME/dbs/orapworcl
密碼文件的作用是允許數據庫的sysdba、sysoper用戶通過口令來遠登陸
查看數據庫是否允許用戶遠程登陸數據庫
show parameter remote_login_passwordfile;
如果remote_login_passwordfile為EXCLUSIVE則表示允許遠程連接數據庫
如果remote_login_passwordfile為NONE則表示不允許遠程連接數據庫
禁止用戶遠程登陸數據庫（需要重啟數據庫生效）
alter system set remote_login_passwordfile=none scope=spfile;
表空間
select * from dba_tablespaces;
數據文件
select * from dba_data_files;
重做日志文件
select * from v$logfile;
重做日志文件的狀態
STALE表示數據已經提交到數據庫中，空白狀態表示正在使用該文件
1. 如何查看及解決最耗CPU的SQL語句1.1. 用top監控服務器負載
|
[root@node1 ~]# top
top - 22:51:02 up 56 min, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 96 total, 1 running, 95 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1035096k total, 351488k used, 683608k free, 24140k buffers
Swap: 2096472k total, 0k used, 2096472k free, 270360k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 15 0 2084 660 560 S 0.0 0.1 0:00.22 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/0
|
如果發現user中的CPU過高，比如oracle中最高的進程pid為1138782，占CPU27%，則執行下一步。 1.2. 查詢數據庫會話的sid、serial#
進入數據庫，根據oracle進程的pid查出對應數據庫會話的sid、serial#：
|
select s.sid,s.serial#
from v$session s,v$process p
where s.paddr=p.addr and p.spid='1138782';
|
查詢出來的結果sid、serial#分別為482、56767 1.3. 查詢SQL語句
根據數據庫會話的sid查出具體的SQL語句：
名稱 是否為空? 類型
----------------------------------------- -------- --------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
COMMAND_TYPE NUMBER
PIECE NUMBER
SQL_TEXT VARCHAR2(64)
|
select sql_text
from v$sqltext a
where a.hash_value=(select sql_hash_value from v$session b
where b.sid='&sid')
order by piece;
|
輸入 sid 的值: 1.4. 處理SQL語句
如果SQL語句影響了數據庫的運行，可以kill掉SQL語句的會話：
①在數據庫中殺死SQL語句的會話：
|
alter system kill session '482,56767';
|
如果不能在數據庫中殺死SQL語句，可在LINUX系統中強制殺死Oracle進程
②在linux系統中強制殺死oracle進程
1.5. SQL語句優化
最后可以根據步驟（3）查詢出來的SQL語句進行優化，以避免再次出現上述消耗CPU的情況。 |
摘要: 一. 分區表理論知識
Oracle提供了分區技術以支持VLDB(Very Large DataBase)。分區表通過對分區列的判斷，把分區列不同的記錄，放到不同的分區中。分區完全對應用透明。
Oracle的分區表可以包括多個分區，每個分區都是一個獨立的段（SEGMENT），可以存放到不同的表空間中。查詢時可以通過查詢表來訪問各個分區中的數據，也可以通過在查詢時直接指定分區的方法來進行查詢。 ...
閱讀全文
1.分區表的維護注意事項
- 若分區表跨不同表空間，做導出、導入時目標數據庫必須預建這些表空間。分表區各區所在表空間在做導入時目標數據庫一定要預建這些表空間！這些表空間不一定是用戶的默認表空間，只要存在即可。如果有一個不存在，就會報錯！
- 默認時，對分區表的許多表維護操作會使全局索引不可用，標記成UNUSABLE。 那么就必須重建整個全局索引或其全部分區。如果已被分區，Oracle 允許在用于維護操作的ALTER TABLE 語句中指定UPDATE GLOBAL INDEXES 來重載這個默認特性，指定這個子句也就告訴Oracle 當它執行維護操作的DDL 語句時更新全局索引，這提供了如下好處：
- 在操作基礎表的同時更新全局索引這就不需要后來單獨地重建全局索引；
- 因為沒有被標記成UNUSABLE， 所以全局索引的可用性更高了，甚至正在執行分區的DDL 語句時仍然可用索引來訪問表中的其他分區,避免了查詢所有失效的全局索引的名字以便重建它們；
另外在指定UPDATE GLOBAL INDEXES 之前還要考慮如下性能因素:
- 因為要更新事先被標記成UNUSABLE 的索引，所以分區的DDL 語句要執行更長時間，當然這要與先不更新索引而執行DDL 然后再重建索引所花的時間做個比較，一個適用的規則是如果分區的大小小于表的大小的5% ，則更新索引更快一點；
- DROP TRUNCATE 和EXCHANGE 操作也不那么快了，同樣這必須與先執行DDL 然后再重建所有全局索引所花的時間做個比較；
- 要登記對索引的更新并產生重做記錄和撤消記錄，重建整個索引時可選擇NOLOGGING；
- 重建整個索引產生一個更有效的索引，因為這更利于使用空間，再者重建索引時允許修改存儲選項；
- 分區索引結構表不支持UPDATE GLOBAL INDEXES 子句。
2.普通表變為分區表
將已存在數據的普通表轉變為分區表，沒有辦法通過修改屬性的方式直接轉化為分區表，必須通過重建的方式進行轉變，一般可以有三種方法，視不同場景使用：
2.1方法一：利用原表重建分區表。
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO t
SELECT Rownum, SYSDATE - Rownum FROM Dba_Objects WHERE Rownum <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2000-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2002-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE))
AS SELECT ID, TIME FROM T;
RENAME T TO T_OLD;
RENAME T_NEW TO T;
優點：方法簡單易用，由于采用DDL語句，不會產生UNDO，且只產生少量REDO，效率相對較高，而且建表完成后數據已經在分布到各個分區中了。
不足：對于數據的一致性方面還需要額外的考慮。由于幾乎沒有辦法通過手工鎖定T表的方式保證一致性，在執行CREATE TABLE語句和RENAME T_NEW TO T語句直接的修改可能會丟失，如果要保證一致性，需要在執行完語句后對數據進行檢查，而這個代價是比較大的。另外在執行兩個RENAME語句之間執行的對T的訪問會失敗。
適用性：適用于修改不頻繁的表，在閑時進行操作，表的數據量不宜太大。
2.2方法二：使用交換分區的方法
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO t
SELECT Rownum, SYSDATE - Rownum FROM Dba_Objects WHERE Rownum <= 5000;
COMMIT;
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2005-9-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (MAXVALUE));
ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;
RENAME T TO T_OLD;
RENAME T_NEW TO T;
優點：只是對數據字典中分區和表的定義進行了修改，沒有數據的修改或復制，效率最高。如果對數據在分區中的分布沒有進一步要求的話，實現比較簡單。在執行完RENAME操作后，可以檢查T_OLD中是否存在數據，如果存在的話，直接將這些數據插入到T中，可以保證對T插入的操作不會丟失。
不足：仍然存在一致性問題，交換分區之后RENAME T_NEW TO T之前，查詢、更新和刪除會出現錯誤或訪問不到數據。如果要求數據分布到多個分區中，則需要進行分區的SPLIT操作，會增加操作的復雜度，效率也會降低。
適用性：適用于包含大數據量的表轉到分區表中的一個分區的操作。應盡量在閑時進行操作。
2.3方法三：Oracle9i以上版本，利用在線重定義功能
Drop table t;
CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
INSERT INTO T
SELECT ROWNUM, SYSDATE - ROWNUM FROM DBA_OBJECTS WHERE ROWNUM <= 5000;
COMMIT;
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'T');
PL/SQL 過程已成功完成。
CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),
PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
PARTITION P4 VALUES LESS THAN (MAXVALUE));
表已創建。
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW');
PL/SQL 過程已成功完成。
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'T', 'T_NEW');
PL/SQL 過程已成功完成。
優點：保證數據的一致性，在大部分時間內，表T都可以正常進行DML操作。只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。而且，可以在切換前進行相應的授權并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。
不足：實現上比上面兩種略顯復雜。
適用性：適用于各種情況。
這里只給出了在線重定義表的一個最簡單的例子，詳細的描述和例子可以參考下面兩篇文章。
Oracle的在線重定義表功能：http://blog.itpub.net/post/468/12855
Oracle的在線重定義表功能（二）：http://blog.itpub.net/post/468/12962
2.4把一個已存在數據的大表改成分區表：
第一種（表不是太大）：
--1.把原表改名：
rename xsb1 to xsb2;
--2.創建分區表：
CREATE TABLE xsb1
PARTITION BY LIST (c_test)
(PARTITION xsb1_p1 VALUES (1),
PARTITION xsb1_p2 VALUES (2),
PARTITION xsb1_p0 VALUES (default))
nologging AS SELECT * FROM xsb2;
--3.將原表上的觸發器、主鍵、索引等應用到分區表上；
--4.刪除原表：
drop table xsb2;
第二種(表很大)：
--1. 創建分區表：
CREATE TABLE x PARTITION BY LIST (c_test) [range ()]
(PARTITION p0 VALUES [less than ](1) tablespace tbs1,
PARTITION p2 VALUES (2) tablespace tbs1,
PARTITION xsb1_p0 VALUES ([maxvalue]default))
AS SELECT * FROM xsb2 [where 1=2];
--2. 交換分區
alter table x exchange partition p0 with table bsvcbusrundatald ;
--3. 原表改名
alter table bsvcbusrundatald rename to x0;
--4. 新表改名
alter table x rename to bsvcbusrundatald ;
--5. 刪除原表
drop table x0;
--6. 創建新表觸發器和索引
create index ind_busrundata_lp on bsvcbusrundatald(。。。) local tablespace tbs_brd_ind ;
或者：
1. 規劃原大表中數據分區的界限，原則上將原表中近期少量數據復制至另一表；
2. 暫停原大表中的相關觸發器；
3. 刪除原大表中近期數據；
4. 改名原大表名稱；
5. 創建分區表；
6. 交換分區；
7. 重建相關索引及觸發器（先刪除之再重建）.
參考腳本：
select count(*) from t1 where recdate>sysdate-2;
create table x2 nologging as select * from t1 where recdate>trunc(sysdate-2);
alter triger trg_t1 disable;
delete t1 where recdate>sysdate-2;
commit;
rename t1 to x1;
create table t1 [nologging] partition by range(recdate)
(partition pbefore values less than (trunc(sysdate-2)),
partition pmax values less than (maxvalue))
as select * from x1 where 1=2;
alter table t1 exchange partition pbefore with table x1;
alter table t1 exchange partition pmax with table x2;
drop table x2;
--重建觸發器
drop table x1;
3.分區的方法：
- 范圍分區Range
- 散列分區Hash
- 列表分區List
- 組合范圍-散列分區Range-Hash
- 組合范圍-列表分區Range-List
可對索引和表分區。全局索引只能按范圍分區，但可以將其定義在任何類型的分區或非分區表上。通常全局索引比局部索引需要更多的維護。
一般組建局部索引，以便反映其基礎表的結構。它與基礎表是等同分區的，即它與基礎表在同樣的列上分區，創建同樣數量的分區或子分區，設置與基礎表相對應的同樣的分區邊界。對局部索引而言，當維護活動影響分區時，會自動維護索引分區。這保證了索引與基礎表之間的等同分區。
關于范圍分區Range：
要想將行映射到基于列值范圍的分區，就使用范圍分區方法。當數據可以被劃分成邏輯范圍時如年度中的月份，這種類型的分區就有用了。當數據在整個范圍中能被均等地劃分時性能最好。如果靠范圍的分區會由于不均等的劃分而導致分區在大小上明顯不同時，就需要考慮其他的分區方法。
關于散列分區Hash：
如果數據不那么容易進行范圍分區，但為了性能和管理的原因又想分區時，就使用散列分區方法。散列分區提供了一種在指定數量的分區中均等地劃分數據的方法。基于分區鍵的散列值將行映射到分區中。創建和使用散列分區會給你提供了一種很靈活的放置數據的方法，因為你可以通過在I/O 驅動器之間播撒(摘掉)這些均等定量的分區，來影響可用性和性能。
關于列表分區List：
當你需要明確地控制如何將行映射到分區時，就使用列表分區方法。可以在每個分區的描述中為該分區列指定一列離散值，這不同于范圍分區，在那里一個范圍與一個分區相關，這也不同于散列分區，在那里用戶不能控制如何將行映射到分區。列表分區方法是特意為遵從離散值的模塊化數據劃分而設計的。范圍分區或散列分區不那么容易做到這一點。進一步說列表分區可以非常自然地將無序的和不相關的數據集進行分組和組織到一起。
與范圍分區和散列分區所不同，列表分區不支持多列分區。如果要將表按列分區，那么分區鍵就只能由表的一個單獨的列組成，然而可以用范圍分區或散列分區方法進行分區的所有的列,都可以用列表分區方法進行分區。
關于組合范圍-散列分區：
范圍和散列技術的組合，首先對表進行范圍分區，然后用散列技術對每個范圍分區再次分區。給定的范圍分區的所有子分區加在一起表示數據的邏輯子集。
關于組合范圍-列表分區：
范圍和列表技術的組合，首先對表進行范圍分區，然后用列表技術對每個范圍分區再次分區。與組合范圍-散列分區不同的是，每個子分區的所有內容表示數據的邏輯子集，由適當的范圍和列表分區設置來描述。
注意：創建或更改分區表時可以指定行移動子句，即ENABLE ROW MOVEMENT 或者DISABLE ROW MOVEMENT ，當其鍵被更改時，該子句啟用或停用將行遷移到一個新的分區。默認值為DISABLE ROW MOVEMENT。
如果表中預期的數據量較大，通常都需要考慮使用分區表，確定使用分區表后，還要確定什么類型的分區（range partition、hash partition、list partition等）、分區區間大小等。分區的創建最好與程序有某種默契，比如創建一個分區表，按自然月份定義分區的，但程序卻在查詢時默認的開始時間與結束時間是：當前日期-30至當前日期，比如當天是9.18號，那查詢條件被產生為8.18-9.18，結果分區后并不沒有大幅提高性能。為此將程序的查詢日期做了調整，按自然月查詢，系統的負載小了很多。
4.補充資料
從Oracle8.0開始支持表分區（MSSQL2005開始支持表分區）。
Oracle9i 分區能夠提高許多應用程序的可管理性、性能與可用性。分區可以將表、索引及索引編排表進一步劃分，從而可以更精細地對這些數據庫對象進行管理和訪問。Oracle 提供了種類繁多的分區方案以滿足所有的業務需要。另外，由于在 SQL 語句中是完全透明的，所以分區可以用于幾乎所有的應用程序。
分區表允許將數據分成被稱為分區甚至子分區的更小的更好管理的塊。索引也可以這么分區。每個分區可以被單獨管理，可以不依賴于其他分區而單獨發揮作用，因此提供了一個更有利于可用性和性能的結構。
分區可以提高可管理性、性能與可用性，從而給各種各樣的應用程序帶來極大的好處。通常，分區可以使某些查詢以及維護操作的性能大大提高。此外，分區還能夠在很大程度上簡化日常管理任務。分區還使數據庫設計人員和管理員能夠解決尖端應用程序帶來的最難的問題。分區是建立上億萬字節數據系統或需要極高可用性系統的關鍵工具。
在多CPU配置環境下，如果打算使用并行執行，則分區提供了另一種并行的方法。通過給表或索引的不同分區分配不同的并行執行服務器，就可以并行執行對分區表和分區索引的操作。
表或索引的分區和子分區都共享相同的邏輯屬性。例如表的所有分區或子分區共享相同的列和約束定義，一個索引的分區或子分區共享相同的索引選項。然而它們可以具有不同的物理屬性如表空間。
盡管不需要將表或索引的每個分區或子分區放在不同的表空間，但這樣做更好。將分區存儲到不同的表空間能夠：
- 減少數據在多個分區中沖突的可能性
- 可以單獨備份和恢復每個分區
- 控制分區與磁盤驅動器之間的映射對平衡I/O 負載是重要的
- 改善可管理性可用性和性能
分區操作對現存的應用和運行在分區表上的標準DML 語句來說是透明的。但是可以通過在DML 中使用分區擴展表或索引的名字來對應用編程，使其利用分區的優點。
可以使用SQL*Loader、Import 和Export 工具來裝載或卸載分區表中的數據。這些工具都是支持分區和子分區的。
4.1分區技術能夠提高數據庫的可管理性：
使用分區技術，維護操作可集中于表的特定部分。例如，數據庫管理員可以只對表的一部分做備份，而不必對整個表做備份。對整個數據庫對象的維護操作，可以在每個分區的基礎上進行，從而將維護工作分解成更容易管理的小塊。
分區技術提高可管理性的一個典型用法是支持數據倉庫中的‘滾動視窗’加載進程。假設數據庫管理員每周向表中加載新數據。該表可以是范圍分區，以便每個分區包含一周的數據。加載進程只是簡單地添加新的分區。添加一個新分區的操作比修改整個表效率高很多，因為數據庫管理員不需要修改任何其他分區。從分區后的表中去除數據也是一樣。你只要用一個很簡便快捷的數據字典操作刪掉一個分區，而不必發出使用大量資源和調動所有要刪除的數據的 ‘DELETE’ 命令。
4.2分區技術能夠提高數據庫的性能:
由于減少了所檢查或操作的數據數量，同時允許并行執行，Oracle9i 的分區功能提供了性能上的優勢。這些性能包括：
- 分區修整：分區修整是用分區技術提高性能的最簡單最有價值的手段。分區修整常常能夠將查詢性能提高幾個數量級。例如，假定應用程序中有包含定單歷史記錄的定單表，該表用周進行了分區。查詢一周的定單只需訪問該定單表的一個分區。如果該定單表包含兩年的歷史記錄，這個查詢只需要訪問一個而不是一百零四個分區。該查詢的執行速度因為分區修整而有可能快一百倍。分區修整能與所有其他 Oracle 性能特性協作。Oracle 公司將把分區修整技術與索引技術、連結技術和并行訪問方法一起聯合使用。
- 分區智能聯接：分區功能可以通過稱為分區智能聯接的技術提高多表聯接的性能。當兩個表要聯接在一起，而且每個表都用聯接關鍵字來分區時，就可以使用分區智能聯接。分區智能聯接將大型聯接分解成較小的發生在各個分區間的聯接，從而用較少的時間完成全部聯接。這就給串行和并行的執行都能帶來顯著的性能改善。
- 更新和刪除的并行執行：分區功能能夠無限地并行執行 UPDATE、DELETE 與 MERGE 語句。當訪問分區或未分區的數據庫對象時Oracle 將并行處理 SELECT 與 INSERT 語句。當不使用位圖索引時，也可以對分區或未分區的數據庫對象并行處理 UPDATE、DELETE 和 MERGE 語句。為了對有位圖索引的對象并行處理那些操作，目標表必須先分區。這些 SQL 語句的并行執行可以大大提高性能，特別是提高 UPDATE 與 DELETE 或 MERGE 操作涉及大量數據時的性能。
4.3分區技術提高可用性：
分區的數據庫對象具有分區獨立性。該分區獨立性特點可能是高可用性戰略的一個重要部分，例如，如果分區表的分區不能用，但該表的所有其他分區仍然保持在線并可用。那么這個應用程序可以繼續針對該分區表執行查詢和事務處理，只要不是訪問那個不可用的分區，數據庫操作仍然能夠成功運行。 數據庫管理員可以指定各分區存放在不同的表空間里，從而讓管理員獨立于其它表分區針對每個分區進行備份與恢復操作。 還有，分區功能可以減少計劃停機時間。性能由于分區功能得到了改善，使數據庫管理員在相對較小的批處理窗口完成大型數據庫對象的維護工作。
將普通表轉換成分區表有4種方法：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
select * from t_user_info_test;
drop table t_phone_test purge;
create table t_phone_test(phone,part) nologging partition by list(part)
(
partition p0 values('0'),
partition p1 values('1'),
partition p2 values('2'),
partition p3 values('3'),
partition p4 values('4'),
partition p5 values('5'),
partition p6 values('6'),
partition p7 values('7'),
partition p8 values('8'),
partition p9 values('9')
)
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test;
select * from t_phone_test partition(p0);
select * from t_phone_test where part='0';
這種方法只是對數據字典中分區和表的定義進行了修改，沒有數據的修改或復制，效率最高。適用于包含大數據量的表轉到分區表中的一個分區的操作。盡量在閑時進行操作。
交換分區的操作步驟如下：
1. 創建分區表，假設有2個分區，P1，P2.
2. 創建表A存放P1規則的數據。
3. 創建表B 存放P2規則的數據。
4. 用表A 和P1 分區交換。 把表A的數據放到到P1分區
5. 用表B 和p2 分區交換。 把表B的數據存放到P2分區。
create table t_phone_test_0 nologging
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test where substr(user_mobile,-1,1)='0';
select count(*) from t_phone_test where part='0';
select count(*) from t_user_info_test where substr(user_mobile,-1,1)='0';
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
delete from t_phone_test_0;
select count(*) from t_phone_test where part='0';
select count(*) from t_phone_test_0;
insert into t_phone_test(phone,part) values('15267046070','0');
insert into t_phone_test_0(phone,part) values('15267046070','1');
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
delete from t_phone_test_0 where part='1';
alter table t_phone_test merge partitions p0,p1 into partition p0;
select count(*) from t_phone_test where part='0';
select count(*) from t_phone_test where part='1';
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test partition(p1);
alter table t_phone_test add partition p10 values(default);
insert into t_phone_test(phone,part) values('15267046010','10');
insert into t_phone_test(phone,part) values('15267046020','20');
select * from
alter table t_phone_test drop partition p10;
alter table t_phone_test add partition p10 values( '10');
alter table t_phone_test exchange partition p10 with table t_phone_test_10;
alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);
alter table t_phone_test merge partitions p0,p10 into partition p0;
partition P0 values ('10', '0')
tablespace APP_DATAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
alter table t_phone_test drop partition p0;
alter table t_phone_test add partition p0 values( '0');
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
drop table t_phone_test_10 purge;
create table t_phone_test_10 nologging
as
select user_mobile phone,substr(user_mobile,-2,2) part
from t_user_info_test where substr(user_mobile,-2,2)='10';
drop table t_phone_test_0 purge;
create table t_phone_test_0 nologging
as
select phone,substr(phone,-1,1) part
from t_phone_test_10;
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
select * from t_phone_test_10;
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test partition(p10);
select count(*) from t_phone_test_10;
select count(*) from t_phone_test_0;
select substr('123456',-1,1),substr('123456',-2,2),substr('123456',-3,2) from dual;
1.創建分區表
drop table t_phone_test purge;
create table t_phone_test(phone,part) nologging partition by list(part)
(
partition p0 values('0'),
partition p1 values('1'),
partition p2 values('2'),
partition p3 values('3'),
partition p4 values('4'),
partition p5 values('5'),
partition p6 values('6'),
partition p7 values('7'),
partition p8 values('8'),
partition p9 values('9')
)
as
select user_mobile phone,substr(user_mobile,-1,1) part
from t_user_info_test;
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test partition(p10);
select count(*) from t_phone_test_10;
select count(*) from t_phone_test_0;
2.創建基表
drop table t_phone_test_10 purge;
create table t_phone_test_10 nologging
as
select phone,substr(phone,-2,2) part
from t_phone_test where substr(phone,-2,2)='10';
select count(*) from t_phone_test_10;
alter table T_PHONE_TEST_10 modify PART VARCHAR2(2);
3.添加分區
alter table t_phone_test add partition p10 values( '10');
select count(*) from t_phone_test partition(p10);
4.交換分區
alter table t_phone_test exchange partition p10 with table t_phone_test_10;
select count(*) from t_phone_test partition(p10);
5.合并分區
alter table t_phone_test merge partitions p0,p10 into partition p0;
select count(*) from t_phone_test partition(p0);
partition P0 values ('10', '0')
tablespace APP_DATAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 1M
next 1M
minextents 1
maxextents unlimited
pctincrease 0
),
6.交換分區
alter table t_phone_test exchange partition p0 with table t_phone_test_10;
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test_10;
6.刪除分區 和添加分區
alter table t_phone_test drop partition p0;
alter table t_phone_test add partition p0 values('0');
7.篩選數據
drop table t_phone_test_0 purge;
create table t_phone_test_0 nologging
as
select phone,substr(phone,-1,1) part
from t_phone_test_10 where substr(phone,-1,1)='0';
select count(*) from t_phone_test_0;
8.交換分區
alter table t_phone_test exchange partition p0 with table t_phone_test_0;
select count(*) from t_phone_test partition(p0);
select count(*) from t_phone_test_0;
Oracle數據庫中，有兩種類型的分區索引，全局索引和本地索引，其中本地索引又可以分為本地前綴索引和本地非前綴索引。下面就分別看看每種類型的索引各自的特點。
全局索引以整個表的數據為對象建立索引，索引分區中的索引條目既可能是基于相同的鍵值但是來自不同的分區，也可能是多個不同鍵值的組合。
全局索引既允許索引分區的鍵值和表分區鍵值相同，也可以不相同。全局索引和表之間沒有直接的聯系，這一點和本地索引不同。
SQL> create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
)
;
Table created.
SQL> create index orders_global_1_idx
on orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition GLOBAL4 values less than (MAXVALUE)
)
;
Index created.
SQL> create index orders_global_2_idx
on orders(part_no)
global partition by range (part_no)
(partition IND1 values less than (555555),
partition IND2 values less than (MAXVALUE)
)
;
Index created.
從上面的語句可以看出，全局索引和表沒有直接的關聯，必須顯式的指定maxvalue值。假如表中新加了分區，不會在全局索引中自動增加新的分區，必須手工添加相應的分區。
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS Q1
ORDERS Q2
ORDERS Q3
ORDERS Q4
ORDERS Q5
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_GLOBAL_1_IDX GLOBAL1
ORDERS_GLOBAL_1_IDX GLOBAL2
ORDERS_GLOBAL_1_IDX GLOBAL3
ORDERS_GLOBAL_1_IDX GLOBAL4
使用全局索引，索引鍵值必須和分區鍵值相同，這就是所謂的前綴索引。Oracle不支持非前綴的全局分區索引，如果需要建立非前綴分區索引，索引必須建成本地索引。
SQL> create index orders_global_2_idx
2 on orders(part_no)
3 global partition by range (order_no)
4 (partition IND1 values less than (555555),
5 partition IND2 values less than (MAXVALUE)
6 )
7 ;
global partition by range (order_no)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed
接下來再來看看本地分區。
本地索引的分區和其對應的表分區數量相等，因此每個表分區都對應著相應的索引分區。使用本地索引，不需要指定分區范圍因為索引對于表而言是本地的，當本地索引創建時，Oracle會自動為表中的每個分區創建獨立的索引分區。
創建本地索引不必顯式的指定maxvalue值，因為為表新添加表分區時，會自動添加相應的索引分區。
create index orders_local_1_idx
on orders(ord_date)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4
)
;
Index created.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4
SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
Table altered.
SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4
ORDERS_LOCAL_1_IDX Q5
這里系統已經自動以和表分區相同的名字自動創建了一個索引分區。同理，刪除表分區時相對應的索引分區也自動被刪除。
本地索引和全局索引還有一個顯著的差別，就是上面提到的，本地索引可以創建成本地非前綴型，而全局索引只能是前綴型。
SQL> create index orders_local_2_idx
on orders(part_no)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4)
;
Index created.
SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
where index_name=upper('orders_local_2_idx');
INDEX_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------------------------
ORDERS_LOCAL_2_IDX LOCAL1 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL2 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL3 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL4 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
從上面的輸出可以看出，雖然索引的鍵值是part_no，但索引分區的鍵值仍然和表的分區鍵值相同，即ord_date，也即是所謂的非前綴型索引。
最后，再引用一個例子說明前綴索引和非前綴索引的應用。
假設有一個使用DATE列分區的大表。我們經常使用一個VARCHAR2列(VCOL)進行查詢，但這個列并不是表的分區鍵值。
有兩種可能的方法來訪問VCOL列的數據，一是建立基于VCOL列的本地非前綴索引，
| |
------- -------
| | (10 more | |
Values: A.. Z.. partitions here) A.. Z..
另一種是建立基于VCOL列的全局索引，
| |
------- -------
| | (10 more | |
Values: A.. D.. partitions here) T.. Z..
可以看出，如果能夠保證VCOL列值的唯一性，全局索引將會是最好的選擇。如果VCOL列值不唯一，就需要在本地非前綴索引的并行查詢和全局索引順序查詢以及高昂的維護代價之間做出選擇。