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.
Questions:
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 ?
Sanjay is perfectly correct. The same temporary tablespace could have been allocated to multiple users.
If you want to find out whether your query uses temporary tablespace(sorts on disk) then enable autotrace in your
session and then issue the same statement.
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.
Sandy
"Greatest Rewards come only with Greatest Commitments!"
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.
Sandy
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 oravijay While setting the SORT_AREA_SIZE you should also take into account the no. of concurrent users.
No, the number of concurent users should have no *direct influence* on setting the SORT_AREA_SIZE. What actually matters is the number of users that perform sorts concurently.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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 ?
Reply With Quote
TNS
Bookmarks