分享
 
 
 

oracle的几个使用技巧

王朝oracle·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

作者:刘颖博 收集整理

时间: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\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’;

语句已处理。

成功地重建控制文件后,我们尝试着打开数据库,但系统报错,提示需要进行介质恢复。

SVRMGR>recover datafile ‘d:\oracle\oradata\orcl\system01.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;

数据库已更改。

立即关闭数据库,并进行数据库的冷备份,将数据库的数据完整地保存下来。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有