原文地址：

https://www.cnblogs.com/adolfmc/p/4796266.html

v$sqltext：存储的是完整的SQL,SQL被分割



v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）



v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）



 



根据sid查找完整sql语句：



select sql_text from v$sqltext a where a.hash_value = (select sql_hash_value from v$session b where b.sid = '&sid'    )

order by piece asc



 



select a.CPU_TIME,--CPU时间 百万分之一（微秒）

       a.OPTIMIZER_MODE,--优化方式

       a.EXECUTIONS,--执行次数

       a.DISK_READS,--读盘次数

       a.SHARABLE_MEM,--占用shared pool的内存多少

       a.BUFFER_GETS,--读取缓冲区的次数

       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)

       a.SQL_TEXT,--Sql语句

       a.SHARABLE_MEM,

       a.PERSISTENT_MEM,

       a.RUNTIME_MEM,

       a.PARSE_CALLS,

       a.DISK_READS,

       a.DIRECT_WRITES,

       a.CONCURRENCY_WAIT_TIME,

       a.USER_IO_WAIT_TIME

  from SYS.V_$SQLAREA a

 WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间

 order by a.CPU_TIME desc



 



引用：http://jenniferok.iteye.com/blog/700985



从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;