When the Temporary Tablespace is used ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: When the Temporary Tablespace is used ?

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Posts
    11

    Question

    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 ?


  2. #2
    Join Date
    Sep 2002
    Posts
    1

    Angry TNS

    HOW TO CONFIGURE TNS NAME WITH ORACLE 8 AND FORM 6i in a stand alone system?

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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


  4. #4
    Join Date
    Mar 2002
    Posts
    301
    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

  5. #5
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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!"

  6. #6
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  7. #7
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    While setting the SORT_AREA_SIZE you should also take into account the no. of concurrent users.

    Thanks.
    Vijay.
    Say No To Plastics

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  10. #10
    Join Date
    Jun 2002
    Posts
    11

    Exclamation

    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
  •  


Click Here to Expand Forum to Full Width