Oracle實用技巧
2024-08-29 13:43:29
供稿：網(wǎng)友
Oracle的幾個使用技巧
1.找出無用索引:
DML 性能低下，其中最嚴重的原因之一是無用索引的存在。所有SQL的插入，更新和刪除操作在它們需要在每一行數(shù)據(jù)被改變時修改大量索引的時候會變得更慢。許多Oracle 治理人員只要看見在一個SQL 查詢的WHERE語句出現(xiàn)了一列的話就會為它分配索引。雖然這個方法能夠讓SQL運行得更快速，但是基于功能的Oracle 索引使得數(shù)據(jù)庫治理人員有可能在數(shù)據(jù)表的行上過度分配索引。過度分配索引會嚴重影響要害Oracle 數(shù)據(jù)表的性能。
在Oracle9i出現(xiàn)以前，沒有辦法確定SQL查詢沒有使用的索引。Oracle9i有一個工具能夠讓你使用ALTER INDEX命令監(jiān)視索引的使用。然后你可以查找這些沒有使用的索引并從數(shù)據(jù)庫里刪除它們。
下面是一段腳本，它能夠打開一個系統(tǒng)中所有索引的監(jiān)視功能:
spool run_monitor.sql
select ’alter index ’owner’.’index_name’ monitoring usage;’
from dba_indexes
where owner not in (’SYS’,’SYSTEM’);
spool off;
@run_monitor
你需要等待一段時間直到在數(shù)據(jù)庫上運行了足夠多的SQL語句以后，然后你就可以查詢新的V$OBJECT_USAGE視圖。
select index_name,table_name,mon,used
from v$object_usage;
在下面，我們可以看見V$OBJECT_USAGE有一列被稱作USED，它的值是YES或者NO。它不會告訴你Oracle使用了這個索引多少次，但是這個工具對于找出沒有使用的索引還是很有用的。
SQL> select * from v$object_usage where rownum < 10;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_accessORY_TARIFF1 ACCOUNT_ACCESSORY_TARIFF YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_QUOTA_LOG1 ACCOUNT_QUOTA_LOG YES NO 01/15/2004 13:50:59
IDX_ACCOUNT_SYSTEM_PARAMETERS1 ACCOUNT_SYSTEM_PARAMETERS YES NO 01/15/2004 13:50:59
IDX_ACCT2 ACCT YES NO 01/15/2004 13:50:59
IDX_ACCT3 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT4 ACCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT1 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
IDX_ACCT_BIND_DISCT2 ACCT_BIND_DISCT YES NO 01/15/2004 13:51:00
2.查看一個很長的操作已經(jīng)做了多少:
v$session_longops視圖可以使Oracle專家減少運行時間很長的DDL和DML語句的運行時間。例如在數(shù)據(jù)倉庫環(huán)境中，即使使用并行索引創(chuàng)建技術(shù)，構(gòu)建一個很多G字節(jié)大的索引需要耗費很多個小時。這里你就可以查詢v$session_longops視圖快速找出一個特定的DDL語句已經(jīng)完成了多少。其實v$session_longops視圖也可以用于任何運行時間很長的操作，包括運行時間很長的更新操作。
下面的腳本將顯示一個狀態(tài)信息，說明了運行時間很長的DDL操作已經(jīng)使用的時間。注重你必須從v$session中取得SID并將其插入到下面的SQL語句中:
select sid,start_time,elapsed_seconds,message
from v$session_longops
where sid = 13
order by start_time;
這里是一個輸出的例子，顯示了運行時間很長的CREATE INDEX語句的運行過程。
SID MESSAGE
--- ---------------------------------------------------------------
11 Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done
3.用set transaction 命令解決ORA-01555錯誤
在執(zhí)行大事務(wù)時,有時oracle會報出如下的錯誤:
ORA-01555:snapshot too old (rollback segment too small)
這說明oracle給此事務(wù)隨機分配的回滾段太小了,這時可以為它指定一個足夠大的回滾段,以確保這個事務(wù)的成功執(zhí)行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ... ;
commit;
提交結(jié)束后ORACLE會自動釋放對 roll_abc 的指定。
4.刪除表中重復(fù)記錄
方法原理:
1、Oracle中，每一條記錄都有一個rowid，rowid在整個數(shù)據(jù)庫中是唯一的， rowid確定了每條記錄是在ORACLE中的哪一個數(shù)據(jù)文件、塊、行上。
2、在重復(fù)的記錄中，可能所有列的內(nèi)容都相同，但rowid不會相同，所以只要確定出重復(fù)記錄中那些具有最大rowid的就可以了，其余全部刪除。
實現(xiàn)方法:
SQL> create table a(bm char(4),mc varchar2(20));
Table created
SQL> insert into a values(’1111’,’aaaa’);
SQL> insert into a values(’1112’,’aaaa’);
SQL> insert into a values(’1113’,’aaaa’);
SQL> insert into a values(’1114’,’aaaa’);
SQL> insert into a select * from a;
4 rows inserted
SQL> commit;
Commit complete
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
AAAIRIAAQAAAAJqAAA 1111 aaaa
AAAIRIAAQAAAAJqAAB 1112 aaaa
AAAIRIAAQAAAAJqAAC 1113 aaaa
AAAIRIAAQAAAAJqAAD 1114 aaaa
AAAIRIAAQAAAAJqAAE 1111 aaaa
AAAIRIAAQAAAAJqAAF 1112 aaaa
AAAIRIAAQAAAAJqAAG 1113 aaaa
AAAIRIAAQAAAAJqAAH 1114 aaaa
8 rows selected
查出重復(fù)記錄
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------ ---- --------------------
AAAIRIAAQAAAAJqAAA 1111 aaaa
AAAIRIAAQAAAAJqAAB 1112 aaaa
AAAIRIAAQAAAAJqAAC 1113 aaaa
AAAIRIAAQAAAAJqAAD 1114 aaaa
刪除重復(fù)記錄
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
刪除4個記錄.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
AAAIRIAAQAAAAJqAAE 1111 aaaa
AAAIRIAAQAAAAJqAAF 1112 aaaa
AAAIRIAAQAAAAJqAAG 1113 aaaa
AAAIRIAAQAAAAJqAAH 1114 aaaa
5.控制文件損壞時的恢復(fù)
根據(jù)如下錯誤信息，我們發(fā)現(xiàn)數(shù)據(jù)庫只能啟動實例，讀控制文件時發(fā)生錯誤。在數(shù)據(jù)庫設(shè)計的過程中，從安全的角度考慮，系統(tǒng)使用了三個鏡像的控制文件，現(xiàn)在三個控制文件version號不一致。
SVRMGRL>startup
oracle instance started
total system global area 222323980 bytes
fixed size 70924 bytes
variable size 78667776 bytes
database buffers 143507456 bytes
redo buffers 77824 bytes
ORA-00214: controlfile ‘d:/oracle/oradata/orcl/control01.ctl’ version 57460 inconsistent with file ‘d:/oracle/oradata/orcl/control02.ctl’ version 57452.
根據(jù)以上分析，我們試著修改參數(shù)文件。將參數(shù)文件中的control_file參數(shù)修改為一個控制文件，分別使用control01、control02、control03。但數(shù)據(jù)庫都無法啟動，說明三個控制文件都已損壞。
由于沒有控制文件的備份，我們只能采取重建控制文件的做法。
D:/>svrmgrl
Oracle Server Manager Release 3.1.6.0.0 - PRodUCtion
版權(quán)所有 (c) 1997，1999，Oracle Corporation。保留所有權(quán)利。
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
SVRMGR> connect internal
連接成功。
SVRMGR> shutdowm abort
已關(guān)閉 ORACLE 實例。
SVRMGR> startup nomount
已啟動 ORACLE 實例。
系統(tǒng)全局區(qū)域合計有 108475660個字節(jié)
Fixed Size 70924個字節(jié)
Variable Size 46116864個字節(jié)
Database Buffers 62210048個字節(jié)
Redo Buffers 77824個字節(jié)
SVRMGR>create controlfile reuse database orcl noresetlogs archivelog
Logfile group 1 ‘d:/oracle/oradata/orcl/redo01.log’,
group 2 ‘d:/oracle/oradata/orcl/redo02.log’,
group 3 ‘d:/oracle/oradata/orcl/redo03.log’
datafile ‘d:/oracle/oradata/orcl/system01.dbf’,
‘d:/oracle/oradata/orcl/users01.dbf’,
‘d:/oracle/oradata/orcl/temp01.dbf’,
‘d:/oracle/oradata/orcl/tools01.dbf’,
‘d:/oracle/oradata/orcl/indx01.dbf’,
‘d:/oracle/oradata/orcl/dr01.dbf’,
‘d:/oracle/oradata/orcl/rbs01.dbf’;
語句已處理。
成功地重建控制文件后，我們嘗試著打開數(shù)據(jù)庫，但系統(tǒng)報錯，提示需要進行介質(zhì)恢復(fù)。
SVRMGR>recover datafile ‘d:/oracle/oradata/orcl/system01.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/users0101.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/temp01.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/tools01.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/indx01.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/dr01.dbf’;
介質(zhì)已恢復(fù)。
SVRMGR> recover datafile ‘d:/oracle/oradata/orcl/rbs01.dbf’;
介質(zhì)已恢復(fù)。
介質(zhì)恢復(fù)后，重新打開數(shù)據(jù)庫，提示日志文件也需恢復(fù)。
SVRMGR> recover database until cancel;
日志已恢復(fù)。
控制文件、數(shù)據(jù)文件、日志文件全部恢復(fù)后，將三種文件同步，并打開數(shù)據(jù)庫，成功地完成了數(shù)據(jù)庫的恢復(fù)工作。
SVRMGR> alter database open resetlogs;
數(shù)據(jù)庫已更改。
立即關(guān)閉數(shù)據(jù)庫，并進行數(shù)據(jù)庫的冷備份，將數(shù)據(jù)庫的數(shù)據(jù)完整地保存下來。
