Oracle性能调优常用语句

Oracle性能调优常用语句

--识别'低效执行'的SQL语句：

SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM  V$SQLAREA

WHERE  EXECUTIONS>0

AND  BUFFER_GETS > 0

AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY  4 DESC;





----查看连接数

select count(*) from v$session; 



----查看并发连接数

Select count(*) from v$session where status='ACTIVE';



----查看最大连接

show parameter processes; 







--查看表空间情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name

  from dba_free_space

group by tablespace_name;



--查看回滚段名称及大小

select segment_name, tablespace_name, r.status,

        (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

        max_extents, v.curext CurExtent

   From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn(+)

order by segment_name ;