oracle 性能指标查询

1. 查看表空间大小以及数据文件大小

select dt.TABLESPACE_NAME, concat(dt.MAX_SIZE,'(MB)') total, concat(ddf.total_space,'(MB)') used
from dba_tablespaces dt,
     (SELECT tablespace_name,
             sum(bytes) / (1024 * 1024) total_space
      FROM dba_data_files
      where TABLESPACE_NAME like 'EAM_DATA'
      group BY tablespace_name) ddf
where dt.TABLESPACE_NAME = ddf.TABLESPACE_NAME
  and dt.TABLESPACE_NAME like 'EAM_DATA';

表空间名称记得改成自己的

2. 查看CPU 使用率

SELECT  ROUND((BusyTicks/(IdleTicks+BusyTicks))*100,2) AS "CPU Usage %" FROM
(
   SELECT
   (SELECT VALUE FROM V$OSSTAT WHERE upper(STAT_NAME)='IDLE_TIME') AS IdleTicks,
   (SELECT VALUE FROM V$OSSTAT WHERE upper(STAT_NAME)='BUSY_TIME') AS BusyTicks
   FROM DUAL
) WHERE ROWNUM=1;

3. 查看内存使用情况

select name,round(total,2) total_MB,round(total-free,2) used_MB, round(free,2) free_MB, round((total-free)/total*100,2)||'%' USED_PER 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);