oracle性能分析语句

SQL性能优化

最新推荐文章于 2023-09-13 09:23:20 发布

原创最新推荐文章于 2023-09-13 09:23:20 发布 · 188 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #C #C++ #C# #SQL

database 专栏收录该内容

17 篇文章

订阅专栏

本文提供了一系列SQL查询语句，用于诊断和优化Oracle数据库中CPU负载高及磁盘IO高的问题。通过这些查询，可以快速定位执行效率低下的SQL语句，并对其进行优化。

1.cpu负载高的语句


select b.sql_text, 
a.buffer_gets, 
a.executions, 
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions), 
c.username 
from V$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
order by a.buffer_gets desc , b.piece 
;

select distinct ss from (
select a.sql_text ss, 
a.buffer_gets, 
a.executions, 
a.buffer_gets/decode(a.executions , 0 , 1 , a.executions), 
c.username 
from V$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
and  to_char(a.LAST_LOAD_TIME,'yyyy-MM-dd hh:mi:ss')  > '2011-01-25 12:00:00'
order by a.disk_reads  desc , b.piece )

select distinct ss from (
select a.sql_text ss
from V$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
order by a.buffer_gets desc , b.piece );

2.磁盘IO高的语句

select b.sql_text, 
a.disk_reads, 
a.executions, 
a.disk_reads/decode(a.executions , 0 , 1 , a.executions), 
c.username 
from v$sqlarea a, 
v$sqltext_with_newlines b, 
dba_users c 
where a.parsing_user_id = c.user_id 
and a.address = b.address 
order by a.disk_reads desc , b.piece 
;