数据库系统性能调优 查询调优Word格式文档下载.docx

数据库系统性能调优 查询调优Word格式文档下载.docx

《数据库系统性能调优 查询调优Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库系统性能调优 查询调优Word格式文档下载.docx（12页珍藏版）》请在冰豆网上搜索。

,20）,dbms_random.value（18,25）,dbms_random.value（1,4））;

ENDLOOP;

COMMIT;

END;

/

（3）distinct重写

原始查询：

selectdistinctsno

fromstudent

wheresnobetween100and200

重写后：

selectsno

效果差别明显吗？

是不是跟预期值有很大的不一样？

为什么？

实验时我使用的是oracle11g，运行此重写没有时间上的区别，网上查了一下，是版本问题，11g和9i是有区别的。

（4）group-by重写

selectavg（grade）

groupbyagehavingage=20

whereage=20

很明显，重写的后语句执行速度更快。

未重写的语句有一条FILTER操作，所以更耗时。

（5）相关子查询

selectsno,sname,age

fromstudents1

whereage=

（selectmax（age）

fromstudents2

wheres1.sname=s2.sname

）

Createtabletmpas

selectsname,max（age）asmaxAge

groupbysname;

selecta.sno,a.sname,a.age

fromstudenta,tmp

wherea.age=tmp.maxAge

droptabletmp;

（6）谓词重写

selectsname,age

wheregrade!

=2andage>

all

（selectage

fromstudent

wheregrade=2

）

selectsname,age

wheregrade!

（selectmin（age）

二、物化视图

1.实验要求

假设有1个视图，它是2张基本表的连接，每次查询该视图，都需要对2张表进行全表扫描再连接。

利用查询重写，不改动sql文本内容的情况下，执行计划采用物化视图。

（1）将创建物化视图的权限授予给scott

grantcreatematerializedviewtoscott;

（2）参数query_rewrite_enabled需要是true。

NAME 

TYPE 

VALUE

---------------------------------------------------------

query_rewrite_enabled 

string 

TRUE

query_rewrite_integrity 

enforced

（3）创建2张表：

createtableqrw01

（idnumber,

a1char（10）,

a2char（10）,

a3CHAR（10）,

a4CHAR（10）,

a5CHAR（10）,

other1CHAR（10）,

other2CHAR（10）,

other3CHAR（10）

）;

createtableqrw02

a1CHAR（10）,

a2CHAR（10）,

b1CHAR（10）,

b2CHAR（10）,

b33CHAR（10）

生成数据插入表

begin

foriin1..100000loop

insertintoqrw01values（trunc（dbms_random.value（0,50））,

dbms_random.string（'

2）,dbms_random.string（'

2）,

2））;

ifmod（i,1000）=0then

commit;

endif;

endloop;

end;

declare

varsqlvarchar2（200）;

CURSORmycuris

selectid,a1,a2,a3,a4,a5

fromqrw01;

foriin1..5loop

forvarsqlinmycurloop

insertintoqrw02values（varsql.id,varsql.a1,varsql.a2,varsql.a3,varsql.a4,varsql.a5,

2张表的数据量：

SQL>

SELECTCOUNT（*）FROMQRW02;

COUNT（*）

----------

500000

SELECTCOUNT（*）FROMQRW01;

100000

（4）创建一个普通视图：

createviewv_qrw01as

selectq1.id,

q1.other1,q1.other2,q1.other3,q2.b1,q2.b2,q2.b33

Fromqrw01q1,qrw02q2

whereq1.id=q2.id

andq1.a1=q2.a1

andq1.a2=q2.a2

andq1.a3=q2.a3

andq1.a4=q2.a4

andq1.a5=q2.a5;

该视图查出来的数据量有50w。

selectcount（*）fromv_qrw01;

（5）下面一条SQL语句的开销，2张表的全表扫描：

selectid,other1,other2,other3,b1,b2,b33fromscott.v_qrw01whereid=14;

查看执行计划

（6）在2张表上创建物化视图：

creatematerializedviewlogonqrw01withrowid;

creatematerializedviewlogonqrw02withrowid;

创建物化视图，查询语句和前面视图的语句相同。

creatematerializedviewqrw_mv01

refreshfast

withrowid

oncommit

enablequeryrewrite

as

selectq1.rowidq1rowid,q2.rowidq2rowid,q1.id,

fromqrw01q1,qrw02q2

对表进行分析：

execDBMS_STATS.GATHER_TABLE_STATS（'

liaody'

'

qrw01'

PL/SQL过程已成功完成。

qrw02'

qrw_mv01'

（7）相同的sql，执行计划变为扫描物化视图。

selectid,other1,other2,other3,b1,b2,b33fromliaody.v_qrw01whereid=14;