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

“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”视图来获取需要优化的问题查询。