v$sql the details -- if you have multiple copies of the query:
"select * from T" in your shared pool, v$sql will have a row per query.
This can happen if user U1 and user U2 both have a table T and both issue "select * from T".
Those are entirely different queries with different plans and so on.
v$sql will have 2 rows.
v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql. "select * from T" will appear there.
It is not clear to me how you are joing v$session to v$sql to get more then one row.
If you wish to see the queries a session has open (maybe open, we cache
cursors so you might see some queries that are closed) use v$open_cursor by sid.
v$sqltext is simply a way to see the entire query. the v$sql and v$sqlarea
views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.
v$sqltext_with_newlines is v$sqltext without the whitespace replacment.
V$SQL lists statistics on shared SQL area without the
GROUP BY clause and contains one row for each child of the original SQL text entered.
V$SQLAREA lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.
This view contains the text of SQL statements belonging to shared SQL cursors in the SGA.
V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache.
This view is identical to the
V$SQLTEXT view except that, to improve legibility,
V$SQLTEXT_WITH_NEWLINES does not replace newlines and tabs in the SQL statement with spaces.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7794469/viewspace-886382/，如需转载，请注明出处，否则将追究法律责任。