Oracle实用技巧.doc

Oracle实用技巧1.找出无用索引:DML 性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle 数据表的性能。在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。下面是一段脚本，它能够打开一个系统中所有索引的监视功能:spool run_monitor.sqlselect alter index |owner|.|index_name| monitoring usage;from dba_indexeswhere owner not in (SYS,SYSTEM);spool off;run_monitor你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。select index_name,table_name,mon,usedfrom v$object_usage;在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。SQL select * from v$object_usage where rownum create table a(bm char(4),mc varchar2(20);Table createdSQL 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 insertedSQL commit;Commit completeSQL select rowid,bm,mc from a;ROWID BM MC- - -AAAIRIAAQAAAAJqAAA 1111 aaaaAAAIRIAAQAAAAJqAAB 1112 aaaaAAAIRIAAQAAAAJqAAC 1113 aaaaAAAIRIAAQAAAAJqAAD 1114 aaaaAAAIRIAAQAAAAJqAAE 1111 aaaaAAAIRIAAQAAAAJqAAF 1112 aaaaAAAIRIAAQAAAAJqAAG 1113 aaaaAAAIRIAAQAAAAJqAAH 1114 aaaa8 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 aaaaAAAIRIAAQAAAAJqAAB 1112 aaaaAAAIRIAAQAAAAJqAAC 1113 aaaaAAAIRIAAQAAAAJqAAD 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 aaaaAAAIRIAAQAAAAJqAAF 1112 aaaaAAAIRIAAQAAAAJqAAG 1113 aaaaAAAIRIAAQAAAAJqAAH 1114 aaaa5.控制文件损坏时的恢复根据如下错误信息，我们发现数据库只能启动实例，读控制文件时发生错误。在数据库设计的过程中，从安全的角度考虑，系统使用了三个镜像的控制文件，现在三个控制文件version号不一致。SVRMGRLstartuporacle instance startedtotal system global area 222323980 bytesfixed size 70924 bytesvariable size 78667776 bytesdatabase buffers 143507456 bytesredo buffers 77824 bytesORA-00214: controlfile d:oracleoradataorclcontrol01.ctl version 57460 inconsistent with file d:oracleoradataorclcontrol02.ctl version 57452.根据以上分析，我们试着修改参数文件。将参数文件中的control_file参数修改为一个控制文件，分别使用control01、control02、control03。但数据库都无法启动，说明三个控制文件都已损坏。由于没有控制文件的备份，我们只能采取重建控制文件的做法。D:svrmgrlOracle Server Manager Release 3.1.6.0.0 - Production版权所有 (c) 1997，1999，Oracle Corporation。保留所有权利。Oracle8i Enterprise Edition Release 8.1.6.0.0 - ProductionWith the Partitioning optionJServer Release 8.1.6.0.0 - ProductionSVRMGR connect internal连接成功。SVRMGR shutdowm abort已关闭 ORACLE 实例。SVRMGR startup nomount已启动 ORACLE 实例。系统全局区域合计有 108475660个字节Fixed Size 70924个字节Variable Size 46116864个字节Database Buffers 62210048个字节Redo Buffers 77824个字节SVRMGRcreate controlfile reuse database orcl noresetlogs archivelogLogfile group 1 d:oracleoradataorclredo01.log,group 2 d:oracleoradataorclredo02.log,group 3 d:oracleoradataorclredo03.logdatafile d:oracleoradataorclsystem01.dbf,d:oracleoradataorclusers01.dbf,d:oracleoradataorcltemp01.dbf,d:oracleoradataorcltools01.dbf,d:oracleoradataorclindx01.dbf,d:oracleoradataorcldr01.dbf,d:oracleoradataorclrbs01.dbf;语句已处理。成功地重建控制文件后，我们尝试着打开数据库，但系统报错，提示需要进行介质恢复。SVRMGRrecover datafile d:oracleoradataorclsystem01.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorclusers0101.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorcltemp01.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorcltools01.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorclindx01.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorcldr01.dbf;介质已恢复。SVRMGR recover datafile d:oracleoradataorclrbs01.dbf;介质已恢复。介