RE: v$sql and v$sqlarea

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 26 Oct 2001 11:02:06 -0700
Message-ID: <F001.003B5EE8.20011026110024@fatcity.com>

> -----Original Message-----
> From: Greg Moore [mailto:sqlgreg_at_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 Received on Fri Oct 26 2001 - 13:02:06 CDT