How SEG$ is updated in DMS

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 01 Feb 2005 11:12:22 +0800
Message-Id: <6.2.0.14.0.20050201110932.05151558@pop.singnet.com.sg>

 

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

Received on Mon Jan 31 2005 - 22:17:19 CST