oracle v$session,v$sqlarea 性能分析

/****查看资源最多的sql*****/

select 

PARSING_USER_ID ,VERSION_COUNT,SQL_TEXT,CPU_TIME,ELAPSED_TIME,EXECUTIONS,

ROWS_PROCESSED,

HASH_VALUE,

buffer_gets ,

disk_reads

 from V$SQLAREA

WHERE buffer_gets > 10000000 OR disk_reads > 1000000  

ORDER BY buffer_gets + 100 * disk_reads DESC;  

 

/*****查看某条SQL语句的资源消耗：**********

SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls  

FROM V$SQLAREA  

WHERE hash_Value = 228801498 AND address = hextoraw('CBD8E4B0');  

 

/*****************8查找前10条性能差的sql语句**************

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text 

 

FROM v$sqlarea

order BY disk_reads DESC )where ROWNUM<10 ;

 

 

/**************************8分析性能差的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

 

查询共享池中已经解析过的SQL语句及其相关信息 

--EXECUTIONS 所有子游标的执行这条语句次数 

--DISK_READS 所有子游标运行这条语句导致的读磁盘次数 

--BUFFER_GETS 所有子游标运行这条语句导致的读内存次数 

--Hit_radio 命中率 

--Reads_per_run 每次执行读写磁盘数 

笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越

 

高越好 

 

 

/*********************选出最占用资源的查询********/

select b.username username,a.disk_reads reads,a.executions exec,  

    a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,  

    a.sql_text statement  

    from v$sqlarea a,dba_users b  

    where a.parsing_user_id=b.user_id  

    and a.disk_reads>100000  

 

--------------------查看当前会话所执行的语句以及会话相关信息-----

 select  a.sid,a.sql_address,s.address,a.username,a.TERMINAL,a.program, 

 

s.sql_text,s.SQL_TEXT,s.CPU_TIME,s.ELAPSED_TIME,s.FIRST_LOAD_TIME,s.LAST_ACTIVE_TIME from 

 

v$session a

,v$sqlarea s

where a.sql_address = s.address(+)

and a.sql_hash_value = s.hash_value(+)

order by a.username, a.sid