the diff between v$sql and v$sqlarea is a group by in $sqlarea. this makes a select statment on v$sql a less expencive in resource terms.
V$SQLTEXT contains the text of sql statements belonging to shared cursors in the sga. the diff btween this and te first two is that there main interest is to show statistics on shared sql area. in v$sqltext you sometimes see the sql statment broken into parts. you caqn connect them in the piece order for row with thesame hash_value
for table v$sqltext_with_newlines, well i neverused it, but looking at the oracle documentation i saw this (copyed for oracle doc):"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. For more information"
in v$sqltext you just have the text of sql statements in the shared sql cursors. in v$sql you have statistics like the number of users executing the sql statement, the amount of sharable memory this cursor and so on...
If you look at it from the text point of view, well yes. in v$sqlarea the text limited to 1000 characters. in $sqltext, full text is available in 64 byte chunks, order by column piece
and connected by hash_value.
hmm I sm wondering when I query v$sql joining with v$session and filter by a SID I get more than one SQL (expected) but if I join v$sqltext with v$session with same conditions I get one sql statement only (last SQL issued) why is this?
I can only tell you that i looked at oracle documentation, and on the metalink, and my confusion just got bigger. this is the reason why i did not answer you until now. if any one can tell me on a good book to read about this. i'll be more then happy.