Re: How does SQL*Plus's DESCRIBE command work internally?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 22 Apr 2002 18:46:03 -0700
Message-ID: <aa2ecr01uas@drn.newsguy.com>

In article <3CC48036.D9A40B4E_at_peasland.com>, Brian says...
>
>> Take a look at the data dictionary views xxx_arguments.
>>
>> I'm not sure if that is where DESCRIBE is going ... but it certainly could.
>
>What's interesting is that if you set SQL_TRACE=true, then do a describe
>on any object, you won't see any recursive SQL being generated on your
>behalf.......
>
>
>Cheers,
>Brian

ahh, but you will sometimes. Depends on whether the object is in the dictionary cache already or not (might help to answer the question "where does it go...")

Consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter system flush shared_pool; System altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true; Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc scott.emp; ....

from the tkprof then:

alter session set sql_trace=true

select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1 from
 obj$ where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or   remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and   :5 is null)and(subname=:6 or subname is null and :6 is null)

select ts#,file#,block#,nvl(bobj#,0),nvl(tab#,0),intcols,nvl(clucols,0),
  audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt,
  avgspc,chncnt,avgrln,analyzetime, samplesize,cols,property,nvl(degree,1),

  nvl(instances,1),avgspc_flb,flbcnt,kernelcols,nvl(trigflag, 0),nvl(spare1,0)   ,nvl(spare2,0),spare4,nvl(spare3,0)
from
 tab$ where obj#=:1

select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,   NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0) from
 seg$ where ts#=:1 and file#=:2 and block#=:3

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,   i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,   i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,

  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  nvl(i.spare1,i.intcols),i.spare4,spare2,decode(i.pctthres$,null,null,

  mod(trunc(i.pctthres$/256),256))
from
 ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1)))   deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=   :1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=   :1

select pos#,intcol#,col#,spare1
from
 icol$ where obj#=:1

select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
  nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
  scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,

  rowid,col#,property, charsetid,charsetform,spare1,spare2 from
 col$ where obj#=:1 order by intcol#

....

16 statements in there. Do this:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true; Session altered.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> desc scott.emp; ....

again and nothing in the tkprof (until you flush it again)

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Apr 22 2002 - 20:46:03 CDT