oracle数据库查询表空间使用率，IO吞吐量，内存使用率sql

1.查询表空间使用率

select a.tablespace_name, total, free,(total-free) as usage from

(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,

(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name;

结果展示：

2.查看总消耗时间最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.elapsed_time desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

3.查看CPU消耗时间最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.cpu_time desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

4.查看消耗磁盘读取最多的前10条SQL语句

select *

from (select v.sql_id,

v.child_number,

v.sql_text,

v.elapsed_time,

v.cpu_time,

v.disk_reads,

rank() over(order by v.disk_reads desc) elapsed_rank

from v$sql v) a

where elapsed_rank <= 10;

 

5. IOps和IO吞吐量 （oracle 11g）

select sum(decode(name,'physical read IO requests',value,'physical write IO requests',value,0)) as iops,

sum(decode(name,'physical read bytes',value,'physical write bytes',value,0)) / 1024 / 1024 as mbps from v$sysstat

where name in ('physical read IO requests','physical write IO requests','physical read bytes','physical read total bytes',

'physical write bytes','physical write total bytes','physical read total IO requests','physical write total IO requests');

结果展示：

 

参考：https://www.2cto.com/database/201309/241170.html

 

6.内存使用情况

SGA / PGA 使用情况

 

-- pctused: 使用率

 

select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from

(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,

(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)

union

select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (

select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,

(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);

 

--内存使用率（待续）