ͨ¹ýv$sqlarea,v$sql²éѯ×îÕ¼ÓÃ×ÊÔ´µÄ²éѯ

ͨ¹ýv$sqlarea,v$sql²éѯ×îÕ¼ÓÃ×ÊÔ´µÄ²éѯ ÊÕ²Ø
 ÒýÓÃ:http://blog.chinaunix.net/u/3866/showart_396335.html
-----------------------
v$sqlarea,v$sql
-----------------------
´ÓV$SQLAREAÖвéѯ×îÕ¼ÓÃ×ÊÔ´µÄ²éѯ
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
order by a.disk_reads desc;
ÓÃbuffer_getsÁÐÀ´Ìæ»»disk_readsÁпÉÒԵõ½Õ¼ÓÃ×î¶àÄÚ´æµÄsqlÓï¾äµÄÏà¹ØÐÅÏ¢¡£
 
V$SQLÊÇÄÚ´æ¹²ÏíSQLÇøÓòÖÐÒѾ­½âÎöµÄSQLÓï¾ä¡£
ÁгöʹÓÃƵÂÊ×î¸ßµÄ5¸ö²éѯ£º
select sql_text,executions
from (select sql_text,executions,
   rank() over
    (order by executions desc) exec_rank
   from v$sql)
where exec_rank <=5;
ÏûºÄ´ÅÅ̶ÁÈ¡×î¶àµÄsql top5£º
select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
     (order by disk_reads desc) disk_reads_rank
   from v$sql)
where disk_reads_rank <=5;
ÕÒ³öÐèÒª´óÁ¿»º³å¶ÁÈ¡£¨Âß¼­¶Á£©²Ù×÷µÄ²éѯ£º
select buffer_gets,sql_text
from (select sql_text,buffer_gets,
   dense_rank() over
     (order by buffer_gets desc) buffer_gets_rank
   from v$sql)
where buffer_gets_rank<=5;
±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/kingmaxno1/archive/2009/01/13/3766368.aspx