Most of documents about this topic,tell you that the Temporary TS is used when the "SORTS" can't be done fully in memory (order by, group by,union ...). Attending this,we are confuse,because we have some statements that are using the Temporary TS and don't involve "SORTS" clauses. This query, without Order by, of a table with only "7 records":
"SELECT NOMBRE,DIA_PROCESO FROM ARINCD WHERE NO_CIA = :b1 AND CENTRO = :b2 "
is using the Temporary TS, with 111 extents and 2775 blocks of 8K (consuming almost "22 MB"). Too much bytes for only 7 records! Don't you think so ?
The Explain Plan is:
SELECT STATEMENT Cost = 1
21 TABLE ACCESS BY INDEX ROWID ARINCD
31 INDEX RANGE SCAN UNIQUE PK_ARINCD
We already used the hints FULL,ROWID, ALL_ROWS,INDEX, FIRST_ROWS with the same result on the explain plan.
1. Is the Temporary TS used for other tasks than "SORTS" only ?
2. Is normal this query wastes that amount of bytes (22 MB)with a only "7 records" table? How can we resolve this situation ?
Originally posted by sandycrab If the sort area size is not large enough to fit your sort operation, then temporary ts is used...This does not mean that you should keep the sort area size very large.
Yep, we should not keep sort area size veryy large.. We should set it at an optimal value.. this depends on hardware configuration, user requirement etc.. Setting sort area size too large will result in paging..
Originally posted by MMCP This query, without Order by, of a table with only "7 records ... is using the Temporary TS, with 111 extents and 2775 blocks of 8K (consuming almost "22 MB"). Too much bytes for only 7 records!
How did you discover that THIS query needed 22MB of Temporary segment? I believe you used a wrong method.
Ales The whole difference between a little boy and an adult man is the price of toys
Ok friends!, to find what SQL statement is using my Temporary TS,we used this query:
SELECT s.username, u."USER", u.tablespace, u.contents,
u.extents, u.blocks, a.sql_text, u.sqladdr, u.sqlhash
FROM v$session s, v$sort_usage u, v$sql a
WHERE s.SADDR = u.SESSION_ADDR
and a.hash_value = u.sqlhash and u.contents = 'TEMPORARY'
Right now,we run it and show us that there are 5 users using this TS with this statement: "SELECT NOMBRE,DIA_PROCESO FROM ARINCD WHERE NO_CIA = :b1 AND CENTRO = :b2",with extents between 110 and 115, also with #blocks between 2775 and 2850.
Our block size= 8K, SORT_AREA_SIZE= 192K and the TS TEMPORARY= 240 M, with initial=next = 192K.
We'd like to know,if the above query we are using to analize the Temporary TS is right or wrong ? If you have another better,can you bring it to me ?