V$SQL       Child cursor details for V$SQLAREA

V$SQLAREA   Shared pool details for statements/anonymous blocks

V$SQLTEXT   SQL text of statements in the shared pool

            都是通过ADDRESS和HASH_VALUE可以为一判定一个SQL

            在两个不同的用户里分别建立wwm表，用两个用户分别运行select * from wwm.则在v$sql里是两条记录（两个cursor）， ADDRESS和HASH_VALUE却是相同的。但在v$sqlarea,v$sqltext却只有一条记录。V$sqlarea 是v$sql合并cursor后的结果.可以通过v$sql的PLAN_HASH_VALUE来判断两个SQL的执行计划是否相同。

            V$sqltext能查到SQL语句的全部（将一个SQL分为多条记录，每条记录64字节，通过HASH VALUE来判断是否属于一条SQL），但v$sql, v$sqlarea只显示1000字节。所以要访问V$SQLTEXT最好根据piece去排序，来组织正确的SQL。

            用A用户执行SELECT * FROM WWM ORDER BY ID 1次，用B用户执行SELECT * FROM WWM ORDER BY ID 3次，则

select sql_text,sorts from v$sql

where sql_text like '%wwm%'

SQL_TEXT                                         SORTS

--------------------------------------------------------------------------------

select * from wwm order by id           3

select * from wwm order by id           1

select sql_text,sorts from v$sqlarea

where sql_text like '%wwm%'

SQL_TEXT                                              SORTS

--------------------------------------------------------------------------------

select * from wwm order by id           4

查找耗费资源的SQL

  1  SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls

  2    FROM V$SQLAREA

  3   WHERE buffer_gets > 10000000

  4      OR disk_reads > 1000000

  5*  ORDER BY buffer_gets + 100*disk_reads DESC

SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS

---------- ---------- ----------- ---------- -----------

2653134598          2       73426      33572           2

1813062183       7424     1127737        293        6497

2964631876       9377      818505          5        9365

1169272907      31598      521484          0       12817

 664977058       1202      301733         11           2

3195782399       4209      266893         13        4209

 895481279      15786      236850        227       12853

   3722848       7285      124008          0        5050

 264196705       7285      109275        118        4925