找到Oracle数据库中性能最差的查询语句

日期： 2012-06-10 作者：Richard Niemie翻译：冯昀晖 来源：TechTarget中国 英文

“V$SQLAREA”和“ V$SQL”是非常有用的视图，在其中你可以查找发现执行效率最差的需要优化的SQL语句。“DISK_READS”列的值表示在系统中执行该语句读取的磁盘量。 　　这个与执行(DISK_READS/EXECUTIONS)相结合，返回每个语句执行实现最佳磁盘命中率的SQL 语句。排在这个列表中最上方的任何语句都最可能是有问题的查询，需要进行优化。

AWR报告或者Statspack报告也列出了资源密集的查询。 　　下面的查询语句可以用来查找你数据库中性能最差的查询： 　　select b.username username, a.disk_read……

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

“V$SQLAREA”和“ V$SQL”是非常有用的视图，在其中你可以查找发现执行效率最差的需要优化的SQL语句。“DISK_READS”列的值表示在系统中执行该语句读取的磁盘量。

　　这个与执行(DISK_READS/EXECUTIONS)相结合，返回每个语句执行实现最佳磁盘命中率的SQL 语句。排在这个列表中最上方的任何语句都最可能是有问题的查询，需要进行优化。AWR报告或者Statspack报告也列出了资源密集的查询。

　　下面的查询语句可以用来查找你数据库中性能最差的查询：

　　select b.username username, a.disk_reads reads,
　　a.executions exec, a.disk_reads /decode
　　(a.executions, 0, 1,a.executions) rds_exec_ratio,
　　a.sql_text Statement
　　from V$sqlarea a, dba_users b
　　where a.parsing_user_id = b.user_id
　　and a.disk_reads > 100000
　　order by a.disk_reads desc;
　　USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT
　　-------- ------- ----- --------------- ---------------------
　　---------------------------------------------------
　　ADHOC1 7281934 1 7281934 select custno, ordno
　　from cust, orders
　　ADHOC5 4230044 4 1057511 select ordno
　　from orders where trunc(ordno) = 721305
　　ADHOC1 801716 2 400858 select custno,
　　ordno from cust where substr(custno,1,6) = '314159'

　　前面语句中的“DISK_READS”列可以被“BUFFER_GETS”列替代，查出关于需要最大内存量的SQL语句。

　　现在考虑第二个例子的输出，这个例子是对一个有十亿行级的表(EMP3)进行行数统计的，还有对原本1.3亿行数据但删除所有数据后只保留了前面15条数据的表(EMP2)进行行数统计。请注意，Oracle一直根据EMP2的高水位(HWM)计数(它会读取超过80万个块，8k块，尽管所有数据只剩下不到1个块大小了)。下面列表可以展示给你，对表EMP2的错误查询，既然该表只剩下15行了，该查询需要被调整(对该表做分析将不会改善这一点)。

　　USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT
　　-------- ------- ----- --------------- -------------------------
　　SCOTT 5875532 1 5875532 select count(*) from emp3
　　SCOTT 800065 1 800065 select count(*) from emp2

　　对于这个问题，如果EMP2表完全是空的，你可以简单地清空表(truncate)来解决这个问题。但是因为该表仍然有15行数据，所以你有几种处理方案。选择哪种方法取决于你的具体情况。你可以：

　　导出/清空/导入;创建临时表emp2b(CREATE TABLE emp2b AS SELECT * FROM emp2 );然后删除表并重命名临时表(我有必要关心索引和相关对象等等)。

　　执行“ALTER TABLE emp2 MOVE TABLESPACE new1”重建索引。

　　如果有主键，请使用“DBMS_REDEFINITION.CAN_REDEF_TABLE”验证该表可以联机重定义。

　　请检查Oracle文档了解每个选项的语法、优点、缺点以及使用约定(此处为列举全部)，这样你可以为你的实际情况应用最好的选项(这些选项每个都有一些主要缺点，包括用户不能访问表和被删除的相关对象，取决于你用的哪个选项，所以要小心一些)。一旦我重组了表，下一次“count(*)”只会读取一个快，而不是800，065个块(这个问题很值得处理)。要注意在这个查询中，我把表“emp2”改成了“emP2”，这样我可以在缓存中找到游标。

　　alter table emp2 move; -- You can specify a tablespace
　　select count(*)
　　from emP2;
　　select b.username username, a.disk_reads reads,
　　a.executions exec, a.disk_reads /decode
　　(a.executions, 0, 1,a.executions) rds_exec_ratio,
　　a.sql_text Statement
　　from V$sqlarea a, dba_users b
　　where a.parsing_user_id = b.user_id
　　and a.sql_text like '%emP2%'
　　order by a.disk_reads desc;
　　USERNAME READS EXEC RDS_EXEC_RATIO STATEMENT
　　-------- ------- ----- --------------- ---------------------
　　SCOTT 1 1 1 select count(*) from emP2

　　你还可以收缩表、索引组织表，索引，分区，子分区，物化视图或者物化视图日志的空间。你可以使用“ALTER TABLE，ALTER INDEX，ALTER MATERIALIZED VIEW或者ALTER MATERIALIZED VIEW LOG”语句加上“SHRINK SPACE”从句实现这一目的。请参见“Oracle管理员指南”了解更多信息。最后，如果你想使用“ALTER TABLE 表名 MOVE TABLESPACE 表空间名”命令，要考虑使用相同大小的表空间(或者如果可以的话用更小一点的表空间)来回移动数据，这样不会太浪费空间。

　　小贴士

　　查询“V$SQLAREA”视图来获取需要优化的问题查询。

作者

Richard Niemie
Richard Niemie

翻译

冯昀晖
冯昀晖

相关推荐