How SEG$ is updated in DMS

 
While running a large CREATE INDEX using a Tablespace Temporary which is
Dictionary Managed,
I "grabbed" my session from v$SORT_USAGE :

SQL> l
  1  select s.username, s.sid, p.spid, su.blocks, st.sql_text
Current_SQL_Text
  2  from v$process p, v$session s, v$sqlarea st, v$sort_usage su
  3  where
  4  s.saddr=su.session_addr
  5  and su.sqladdr=st.address
  6  and su.sqlhash=st.hash_value
  7* and p.addr=s.paddr
SQL> /

USERNAME                              SID SPID          BLOCKS
------------------------------ ---------- --------- ----------
CURRENT_SQL_TEXT
----------------------------------------------------------------------------
----
HEMANT                                 36 152177        215039
update seg$ set
type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL,
:13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16,
spare1=DECODE(:17,0,NULL,:
17) where ts#=:1 and file#=:2 and block#=:3


1 row selected.

SQL> 

At this stage, it is still creating the Temporary Segment.  Why does it have
to update ALL the columns of SEG$
for each Extent being allocated ?  No wonder that DMS Temporary Tablespaces
are called "slow".




Hemant K Chitale
http://web.singnet.com.sg/~hkchital

[1]

----------------------------------------------------------------------------


----------------------------------------------------------------------------



--- Links ---
   1 http://web.singnet.com.sg/~hkchital
--
http://www.freelists.org/webpage/oracle-l

Other related posts: