oracle数据文件过多,学习笔记:Oracle性能优化 定位消耗内存 逻辑读 执行次数 硬盘读 分析次数多的SQL语句...

天萃荷净

oracle 中如何定位重要(消耗资源多)的SQL，统计一下关于查看影响Oracle性能的SQL，定位耗内存 逻辑读 执行次数 硬盘读 分析次数多的SQL语句

1、查看有影响Oracle性能的SQL

select substr(to_char(s.pct,'99.00'),2)||'%'load,

s.executions executes,

p.sql_text

from(select address,

disk_reads,

executions,

pct,

rank()over(order by disk_reads desc) ranking

from(select address,

disk_reads,

executions,

100*ratio_to_report(disk_reads)over() pct

from sys.v_$sql

where command_type!=47)

where disk_reads>50*executions) s,

sys.v_$sqltext p

where s.ranking<=5

and p.address=s.address

order by 1, s.address, p.piece;

2、查看Oracle中消耗系统内存多的sql

select b.username,

a. buffer_gets,

a.executions,

a.disk_reads / decode(a.executions, 0, 1, a.executions),

a.sql_text SQL

from v$sqlarea a, dba_users b

where a.parsing_user_id = b.user_id

and a.disk_reads > 10000

order by disk_reads desc;

3、查看Oracle数据库中逻辑读多的SQL

select*

from(select buffer_gets, sql_text

from v$sqlarea

where buffer_gets>500000

order by buffer_gets desc)

where rownum<=30;

4、查看Oracle数据库中执行次数多的SQL

select sql_text, executions

from (select sql_text, executions from v$sqlarea order by executions desc)

where rownum < 81;

5、查看Oracle数据库中读硬盘多的SQL

select sql_text, disk_reads

from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)

where rownum<21;

6、查看Oracle数据库中排序多的SQL

select sql_text, sorts

from(select sql_text, sorts from v$sqlarea order by sorts desc)

where rownum<21;

7、分析Oracle数据库中的次数太多，执行的次数太少，要用绑变量的方法来写sql

select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"

from v$sqlarea

where executions < 5

group by substr(sql_text, 1, 80)

having count(*) > 30

order by 2;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle性能优化 定位消耗内存 逻辑读 执行次数 硬盘读 分析次数多的SQL语句