From Jacques.Kilchoer@quest.com Fri, 26 Oct 2001 11:02:06 -0700 From: Jacques Kilchoer Date: Fri, 26 Oct 2001 11:02:06 -0700 Subject: RE: v$sql and v$sqlarea Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: v$sql and v$sqlarea > -----Original Message----- > From: Greg Moore [mailto:sqlgreg@pacbell.net] > > I want to understand the difference between v$sql and > v$sqlarea.  Apparently > they are both views of the same x$ table.  Does anyone know > where I can view > the code that creates these two views? v$fixed_view_definition LQS> select view_definition from v$fixed_view_definition   2  where view_name = 'GV$SQL' ; VIEW_DEFINITION -------------------------------------------------------------------------------- select inst_id,kglnaobj, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+k globhs6+kglobt16, kglobt08+kglobt11, kglobt10, kglobt01, decode(kglobhs6,0,0,1),  decode(kglhdlmd,0,0,1), kglhdlkc, kglhdexc, kglobpc6, kglhdldc, substr(to_char( kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), kglhdivc, kglobt12, kglobt13, kglobt14,  kglobt15, kglobt02, decode(kglobt32,        0, 'NONE',        1, 'ALL_ROWS',      2, 'FIRST_ROWS',        3, 'RULE',        4, 'CHOOSE',           'UNKNOWN') , kglobtn0, kglobt17, kglobt18, kglhdkmk, kglhdpar, kglobtp0, kglnahsh, kglobt09 , kglobts0, kglobt19, kglobts1, kglobt20, kglobt21, kglobts2 from x$kglcursor  w here kglhdadr != kglhdpar  and   kglobt02 != 0 LQS> c/GV$SQL/GV$SQLAREA/   2* where view_name = 'GV$SQLAREA' LQS> run   1  select view_definition from v$fixed_view_definition   2* where view_name = 'GV$SQLAREA' VIEW_DEFINITION -------------------------------------------------------------------------------- select inst_id,kglnaobj, sum(kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobh s5+kglobhs6), sum(kglobt08+kglobt11), sum(kglobt10), sum(kglobt01), count(*)-1, sum(decode(kglobhs6,0,0,1)), decode(sum(decode(kglhdlmd,0,0,1)),0,0,sum(decode(k glhdlmd,0,0,1))-1), sum(kglhdlkc)/2, sum(kglhdexc), sum(kglobpc6), sum(kglhdldc) -1, substr(to_char(kglnatim,'YYYY-MM-DD/HH24:MI:SS'),1,19), sum(kglhdivc), sum(k globt12), sum(kglobt13), sum(kglobt14), sum(kglobt15), sum(decode(kglobt09,0,kgl obt02,0)) , decode(count(*)-1, 1,                    decode(sum(decode(kglobt09,  0, kglobt32, 0)),                            0, 'NONE',     1, 'ALL_ROWS',                            2, 'FIRST_ROWS',           3, 'RULE',                            4, 'CHOOSE',            'UNKNOWN'),                    'MULTIPLE CHILDREN PRESENT'), sum(deco VIEW_DEFINITION -------------------------------------------------------------------------------- de(kglobt09,0,kglobt17,0)), sum(decode(kglobt09,0,kglobt18,0)), decode(sum(decod e(kglhdkmk,0,0,1)),0,0,sum(decode(kglhdkmk,0,0,1))-1), kglhdpar, kglnahsh, kglob ts0, kglobt19, kglobts1, kglobt20, sum(kglobt21)   from x$kglcursor group by ins t_id,kglnaobj,kglhdpar,kglnahsh,kglnatim,                             kglobts0, kglobt19, kglobts1, kglobt20   having sum(decode(kglobt09,0,kglobt02,0)) != 0globt19, kglobts1, kglobt20   having sum(decode(kglobt09,0,kglobt02,0)) != 0