Oracle实用技巧

Oracle实用技巧1.找出无用索引:DML性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多Oracle管理人员只要看见在一个SQL查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle数据表的性能。在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTERINDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。下面是一段脚本，它能够打开一个系统中所有索引的监视功能:spoolrun_monitor.sqlselect'alterindex'||owner||'.'||index_name||'monitoringusage;'fromdba_indexeswhereownernotin('SYS','SYSTEM');spooloff;@run_monitor你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。selectindex_name,table_name,mon,usedfromv$object_usage;在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于找出没有使用的索引还是很有用的。SQLselect*fromv$object_usagewhererownum10;INDEX_NAMETABLE_NAMEMONITORINGUSEDSTART_MONITORINGEND_MONITORING----------------------------------------------------------------------------------------------------------------ASDDIM_ACCT_ITEM_TYPE_TEMPYESNO01/15/200413:50:59IDX_ACCOUNT_ACCESSORY_TARIFF1ACCOUNT_ACCESSORY_TARIFFYESNO01/15/200413:50:59IDX_ACCOUNT_QUOTA_LOG1ACCOUNT_QUOTA_LOGYESNO01/15/200413:50:59IDX_ACCOUNT_SYSTEM_PARAMETERS1ACCOUNT_SYSTEM_PARAMETERSYESNO01/15/200413:50:59IDX_ACCT2ACCTYESNO01/15/200413:50:59IDX_ACCT3ACCTYESNO01/15/200413:51:00IDX_ACCT4ACCTYESNO01/15/200413:51:00IDX_ACCT_BIND_DISCT1ACCT_BIND_DISCTYESNO01/15/200413:51:00IDX_ACCT_BIND_DISCT2ACCT_BIND_DISCTYESNO01/15/200413:51:002.查看一个很长的操作已经做了多少:v$session_longops视图可以使Oracle专家减少运行时间很长的DDL和DML语句的运行时间。例如在数据仓库环境中，即使使用并行索引创建技术，构建一个很多G字节大的索引需要耗费很多个小时。这里你就可以查询v$session_longops视图快速找出一个特定的DDL语句已经完成了多少。其实v$session_longops视图也可以用于任何运行时间很长的操作，包括运行时间很长的更新操作。下面的脚本将显示一个状态信息，说明了运行时间很长的DDL操作已经使用的时间。注意你必须从v$session中取得SID并将其插入到下面的SQL语句中:selectsid,start_time,elapsed_seconds,messagefromv$session_longopswheresid=13orderbystart_time;这里是一个输出的例子，显示了运行时间很长的CREATEINDEX语句的运行过程。SIDMESSAGE------------------------------------------------------------------11TableScan:CUST.PK_IDX:732outof243260Blocksdone3.用settransaction命令解决ORA-01555错误在执行大事务时,有时oracle会报出如下的错误:ORA-01555:snapshottooold(rollbacksegmenttoosmall)这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如settransactionuserollbacksegmentroll_abc;deletefromtable_namewhere...;commit;提交结束后ORACLE会自动释放对roll_abc的指定。4.删除表中重复记录方法原理:1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。实现方法:SQLcreatetablea(bmchar(4),mcvarchar2(20));TablecreatedSQLinsertintoavalues('1111','aaaa');SQLinsertintoavalues('1112','aaaa');SQLinsertintoavalues('1113','aaaa');SQLinsertintoavalues('1114','aaaa');SQLinsertintoaselect*froma;4rowsinsertedSQLcommit;CommitcompleteSQLselectrowid,bm,mcfroma;ROWIDBMMC------------------------------------------AAAIRIAAQAAAAJqAAA1111aaaaAAAIRIAAQAAAAJqAAB1112aaaaAAAIRIAAQAAAAJqAAC1113aaaaAAAIRIAAQAAAAJqAAD1114aaaaAAAIRIAAQAAAAJqAAE1111aaaaAAAIRIAAQAAAAJqAAF1112aaaaAAAIRIAAQAAAAJqAAG1113aaaaAAAIRIAAQAAAAJqAAH1114aaaa8rowsselected查出重复记录SQLselectrowid,bm,mcfromawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);ROWIDBMMC------------------------------------------AAAIRIAAQAAAAJqAAA1111aaaaAAAIRIAAQAAAAJqAAB1112aaaaAAAIRIAAQAAAAJqAAC1113aaaaAAAIRIAAQAAAAJqAAD1114aaaa删除重复记录SQLdeletefromaawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);删除4个记录.SQLselectrowid,bm,mcfroma;ROWIDBMMC------------------------------------------AAAIRIAAQAAAAJqAAE1111aaaaAAAIRIAAQAAAAJqAAF1112aaaaAAAIRIAAQAAAAJqAAG1113aaaaAAAIRIAAQAAAAJqAAH1114aaaa5.控制文件损坏时的恢复根据如下错误信息，我们发现数据库只能启动实例，读控制文件时发生错误。在数据库设计的过程中，从安全的角度考虑，系统使用了三个镜像的控制文件，现在三个控制文件version号不一致。SVRMGRLstartuporacleinstancestartedtotalsystemglobalarea222323980bytesfixedsize70924bytesvariablesize78667776bytesdatabasebuffers143507456bytesredobuffers77824bytesORA-00214:controlfile‘d:\oracle\oradata\orcl\control01.ctl’version57460inconsistentwithfile‘d:\oracle\oradata\orcl\control02.ctl’version57452.根据以上分析，我们试着修改参数文件。将参数文件中的control_file参数修改为一个控制文件，分别使用control01、control02、control03。但数据库都无法启动，说明三个控制文件都已损坏。由于没有控制文件的备份，我们只能采取重建控制文件的做法。D:\svrmgrlOracleServerManagerRelease3.1.6.0.0-Production版权所有(c)1997，1999，OracleCorporation。保留所有权利。Oracle8iEnterpriseEditionRelease8.1.6.0.0-ProductionWiththePartitioningoptionJServerRelease8.1.6.0.0-ProductionSVRMGRconnectinternal连接成功。SVRMGRshutdowmabort已关闭ORACLE实例。SVRMGRstartupnomount已启动ORACLE实例。系统全局区域合计有108475660个字节FixedSize70924个字节VariableSize46116864个字节DatabaseBuffers62210048个字节RedoBuffers77824个字节SVRMGRcreatecontrolfilereusedatabaseorclnoresetlogsarchivelogLogfilegroup1‘d:\oracle\oradata\orcl\redo01.log’,group2‘d:\oracle\oradata\orcl\redo02.log’,group3‘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’;语句已处理。成功地重建控制文件后，我们尝试着打开数据库，但系统报错，提示需要进行介质恢复。SVRMGRrecoverdatafile‘d:\oracle\oradata\orcl\