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 ;