-
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 ?
-
TNS
HOW TO CONFIGURE TNS NAME WITH ORACLE 8 AND FORM 6i in a stand alone system?
-
MMCP,
The space in TEMP tablespace is not only used by your query. It will be used for all user's sort whose temporary tablespace is TEMP.
kbiswas,
For new threads, click "New Thread" link on top, not the "reply" link.
Sanjay
-
Hi,
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.
Thanks.
Vijay.
Say No To Plastics
-
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..
-nagarjuna
-
Hi,
While setting the SORT_AREA_SIZE you should also take into account the no. of concurrent users.
Thanks.
Vijay.
Say No To Plastics
-
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 ?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|