difefrences between v$... - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 26

Thread: difefrences between v$...

  1. #11
    Join Date
    Jul 2000
    Posts
    243
    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

  2. #12
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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)

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    [QUOTE][i]Originally posted by aracnid [/i]
    [B]
    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) [/B][/QUOTE]

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

  4. #14
    Join Date
    Jul 2000
    Posts
    243
    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?

  5. #15
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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$

  6. #16
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  7. #17
    Join Date
    Jul 2000
    Posts
    243
    thank's guys

  8. #18
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    shawish_sababa
    Are you Alan? :D

  9. #19
    Join Date
    Mar 2001
    Posts
    1
    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

  10. #20
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width