difefrences between v$...

Does anyone know what are the exact differences between

v$sql
v$sqlarea
v$sqltext
v$sqltext_with_newlines

cheers

Hi pando

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"

Hope this helps

well actually I dont see the difference between v$sql and v$sqltext and I still dont see the difference :o

Hi pando

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...

well so basically they both show same sql_text? since v$sql also shows shared sql cursors?

Hi pando

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?

Does anyone know the difference between v$sql and v$sqltext :-?

Hi pando

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.

Hmmm so I am not alone :D

i can only tell you that until you started asking question i thought i knew what i was talking about. but going back to me calss material, andthe doc made me understand that you are not alone.
i can only tell you that, and at first i did not whant to put this in writing (for this is just a guess, i can not prov it in the documentation), if you look at the process of sql/plsql statment goes through, from before parsing, v$sqltext_with_newlines, to parsed statment in v$sql, to something ready to be used in v$sqlarea, to something in the sga that is actualy processed by with data feched from the disk (v$sqltext). i do not what to get more into the part of the memory process, but i think that every table is a picture in a different stage of the memory management in the execution process.

hope this help

Hello ..:-)
The v$sqltext comes from (global view) gv$sqltext comes from oracle kernel X$kglna
The v$sql comes from gv$sql comes from x$kglcursor...
So, the x$kglna meens the Kernel-Generic-Layer-Network-Access
The X$kglcursor is the Kernel-Generic-Layer-Cursor...
The 1' keeps information about the network in your db (so what is going on in that moment)
The 2' is a gather-user dependent (what is+was going on)

Quote:

---

Originally posted by aracnid

The 1' keeps information about the network in your db (so what is going on in that moment)
The 2' is a gather-user dependent (what is+was going on)

---

This part makes sense, that´s why i see only the last SQL issued by a user with v$sqltext

Hi aracnid

can you please tell us where to find reading material on the subject of connecting the v$ views to the different sql stament processing phases?

I'm afraid the oracle kernel materials are very-very difficult to be finded(so for the def. of x$k fixed tables). Take a look at GV_$FIXED_VIEW_DEFINITION for the links between v$ and x$

if you have Oracle Perfomance Tuning Tips & Techniques by Richard Niemiec Appendix C contains a list of v$ and the x$ that those v$ use to build the view. Only Oracle 7 and 8 though

thank's guys

shawish_sababa
Are you Alan? :D

My understanding of the difference between v$sql and v$sqltext is this:

v$sqltext is a view of the sql statements currently running in memory.

v$sql is a view of the sql statements that have run over the life of the instance and cumulative statistics regarding their execution (such as how many times that statement has been executed, how many total disk reads and buffer gets are associated with the statement, etc.).

If you want to look at the differences in the actual view definitions against the x$ tables then you can do something like this (under Oracle8):


select 'view name: ' || view_name,
substr(view_definition,1,(instr(view_definition,'from')-1)) def1,
substr(view_definition,(instr(view_definition,'from'))) def2
from v$fixed_view_definition
where view_name like 'GV$SQL%'
order by view_name;


I borrowed the basis for this query from 'Oracle Performance Tuning Tips and Techniques' by Richard Niemiec, which is a great book.

Hope this helps.
Dan

Well no v$sql contains the shared cursors in sql area in the shared pool so it's v$sqltext and that's why it's confusing

If it's as you said v$sql contains all the sql from the day you started the instance if your database runs for one year imagine how big will this view be :D

HI pando

no, i'm not Alan. if you what no contact me, you can email me at [email protected]

Nah just wondering because someone basically cut the question from this forum and pasted in ask tom... :)

pando

what is ask tom? anythng interesting there?

he is really an Oracle guru

http://asktom.oracle.com/

further about v$... see what he answered

http://asktom.oracle.com/pls/ask/f?p...ag385254862843

I didnt ask the question btw someone named Alan did and the question was cut & pasted from this forum :D

thank you for the info. i looked into it, and it looks interesting. but i ave to tell you that aracnid answer was much better then the on from asktom.

yes that´s why i rated with one star only in the answer and other 3 guys rated 5 stars heh