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 ;