Re: Why is sort segment growing extremely big on simple query?

Don Vaillancourt wrote ..

> Actually, I have already done my own tests and it doesn't. I can only
> retrieve 4000 as you already mentioned as opposed to the 64000 we're
> used to, but I think that this is a good trade off considering that we
> were doing almost 5000 queries at a time.

Perhaps you could consider tuning the temp tablespace extent size to retain the ability to fetch 64000 bytes. Consider this test case (9.2.0.5, 8k block size):

create table don (x clob);

declare
  l_clob clob;
begin
  for i in 1..10 loop
    insert into don(x) values (empty_clob())     returning x into l_clob;

• create a 400,000 bytes clob for i in 1..100 loop dbms_lob.append (l_clob, rpad ('*',4000,'*')); end loop; end loop; end; /

create temporary tablespace don_1024
tempfile 'C:\ORACLE\ORA92\ORACLE9I\DON_1024.DBF' size 10M extent management local
uniform size 1024k;

create temporary tablespace don_512
tempfile 'C:\ORACLE\ORA92\ORACLE9I\DON_512.DBF' size 10M extent management local
uniform size 512k;

create temporary tablespace don_64
tempfile 'C:\ORACLE\ORA92\ORACLE9I\DON_64.DBF' size 10M extent management local
uniform size 64k;

select tablespace_name, initial_extent from dba_tablespaces where tablespace_name in ('DON_64','DON_512','DON_1024');

TABLESPACE_NAME                INITIAL_EXTENT
------------------------------ --------------
DON_1024                              1048576
DON_512                                524288
DON_64                                  65536

alter user dellera temporary tablespace don_1024;

(You must exit now to use the new temp tablespace)

select substr (x, 1, 64000) piece from don;

select count(*) from v$temporary_lobs
where sid = (select sid from v$mystat where rownum=1);

  COUNT(*)

         1

(side note: interestingly, even if we fetched 10 rows, we have only 1 temp clob at the end).

select tablespace, segtype, blocks*8*1024 used_bytes from v$tempseg_usage where username=user;

TABLESPACE SEGTYPE                     USED_BYTES
---------- --------------------------- ----------
DON_1024   LOB_DATA                       1048576
DON_1024   LOB_INDEX                      1048576

alter user dellera temporary tablespace don_512;   

TABLESPACE SEGTYPE                     USED_BYTES
---------- --------------------------- ----------
DON_512    LOB_DATA                        524288
DON_512    LOB_INDEX                       524288

alter user dellera temporary tablespace don_64;

TABLESPACE SEGTYPE                     USED_BYTES
---------- --------------------------- ----------
DON_64     LOB_DATA                        327680
DON_64     LOB_INDEX                        65536

So by reducing the extent size we greatly reduce the space allocated to the temp lob_index. I don't know why the lob_data, that should contain 64000 bytes stays to 327,680 for an extent size of 64K. Interestingly, if we select only 1 row:

alter user dellera temporary tablespace don_64;

select substr (x, 1, 64000) piece from don where rownum = 1;

TABLESPACE SEGTYPE                     USED_BYTES
---------- --------------------------- ----------
DON_64     LOB_DATA                        196608
DON_64     LOB_INDEX                        65536

I don't know the reason for this, me not really being a "lob expert"; perhaps temporary LOBs have a different (bigger) CHUNKSIZE and/or PCTVERSION ? Are they updated versus being 'truncated' and then inserted for each row fetched ? I don't know (surely some guru knows better).

Obviously, changing the extent size may adversely affect sort-to-disk and hash-join-to-disk, etc, operations - even if, by using an LMT temp tablespace, the impact may (stress on *may*) be immaterial.

hth
Alberto Dell'Era Received on Mon Nov 08 2004 - 16:10:06 CST