oracle的幾個使用技巧
2024-08-29 13:30:33
供稿：網友
作者：劉穎博 收集整理
時間：2004年4月29日
轉載請注明出處，謝謝！
1.找出無用索引：
dml 性能低下，其中最嚴重的原因之一是無用索引的存在。所有sql的插入，更新和刪除操作在它們需要在每一行數據被改變時修改大量索引的時候會變得更慢。許多oracle 管理人員只要看見在一個sql 查詢的where語句出現了一列的話就會為它分配索引。雖然這個方法能夠讓sql運行得更快速，但是基于功能的oracle 索引使得數據庫管理人員有可能在數據表的行上過度分配索引。過度分配索引會嚴重影響關鍵oracle 數據表的性能。
在oracle9i出現以前，沒有辦法確定sql查詢沒有使用的索引。oracle9i有一個工具能夠讓你使用alter index命令監視索引的使用。然后你可以查找這些沒有使用的索引并從數據庫里刪除它們。
下面是一段腳本，它能夠打開一個系統中所有索引的監視功能：
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
你需要等待一段時間直到在數據庫上運行了足夠多的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.查看一個很長的操作已經做了多少：
v$session_longops視圖可以使oracle專家減少運行時間很長的ddl和dml語句的運行時間。例如在數據倉庫環境中，即使使用并行索引創建技術，構建一個很多g字節大的索引需要耗費很多個小時。這里你就可以查詢v$session_longops視圖快速找出一個特定的ddl語句已經完成了多少。其實v$session_longops視圖也可以用于任何運行時間很長的操作，包括運行時間很長的更新操作。
下面的腳本將顯示一個狀態信息，說明了運行時間很長的ddl操作已經使用的時間。注意你必須從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錯誤
在執行大事務時,有時oracle會報出如下的錯誤:
ora-01555:snapshot too old (rollback segment too small)
這說明oracle給此事務隨機分配的回滾段太小了,這時可以為它指定一個足夠大的回滾段,以確保這個事務的成功執行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ... ;
commit;
提交結束后oracle會自動釋放對 roll_abc 的指定。
4.刪除表中重復記錄
方法原理：
1、oracle中，每一條記錄都有一個rowid，rowid在整個數據庫中是唯一的， rowid確定了每條記錄是在oracle中的哪一個數據文件、塊、行上。
2、在重復的記錄中，可能所有列的內容都相同，但rowid不會相同，所以只要確定出重復記錄中那些具有最大rowid的就可以了，其余全部刪除。
實現方法：
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
查出重復記錄
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
刪除重復記錄
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.控制文件損壞時的恢復
根據如下錯誤信息，我們發現數據庫只能啟動實例，讀控制文件時發生錯誤。在數據庫設計的過程中，從安全的角度考慮，系統使用了三個鏡像的控制文件，現在三個控制文件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. 根據以上分析，我們試著修改參數文件。將參數文件中的control_file參數修改為一個控制文件，分別使用control01、control02、control03。但數據庫都無法啟動，說明三個控制文件都已損壞。 由于沒有控制文件的備份，我們只能采取重建控制文件的做法。
d:/>svrmgrl
oracle server manager release 3.1.6.0.0 - production
版權所有 (c) 1997，1999，oracle corporation。保留所有權利。
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
已關閉 oracle 實例。
svrmgr> startup nomount
已啟動 oracle 實例。
系統全局區域合計有 108475660個字節
fixed size 70924個字節
variable size 46116864個字節
database buffers 62210048個字節
redo buffers 77824個字節
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 ystem01.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’;
語句已處理。 成功地重建控制文件后，我們嘗試著打開數據庫，但系統報錯，提示需要進行介質恢復。
svrmgr>recover datafile ‘d:/oracle/oradata/orcl ystem01.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/users0101.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/temp01.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/tools01.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/indx01.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/dr01.dbf’;
介質已恢復。
svrmgr> recover datafile ‘d:/oracle/oradata/orcl/rbs01.dbf’;
介質已恢復。 介質恢復后，重新打開數據庫，提示日志文件也需恢復。
svrmgr> recover database until cancel;
日志已恢復。 控制文件、數據文件、日志文件全部恢復后，將三種文件同步，并打開數據庫，成功地完成了數據庫的恢復工作。
svrmgr> alter database open resetlogs;
數據庫已更改。 立即關閉數據庫，并進行數據庫的冷備份，將數據庫的數據完整地保存下來。
