How SEG$ is updated in DMS
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 01 Feb 2005 11:12:22 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
