When the Temporary Tablespace is used? Most documents say that it is used when "SORTS" can't be done fully in memory (order by, group by, union). Attending this, we tried to see what was happening with 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'Our confusion is that the query is showing some users with this Sql_Text(among others):"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 (almost 22 MB each one!).
- Is the query used to determine what sql_texts use Temp TS wrong? Have you a better one?
- Why, when I run the Sql_Text in TOAD: "SELECT NOMBRE,DIA_PROCESO FROM ARINCD..." and then look V$SORT_USAGE, is it empty? No sort activity. Is there a difference between running a statement in SQL*Plus(TOAD) and a form ? Maybe this answer is related to question #1.
- Is the Temporary TS used for segments other than "SORTS" only? If so, do you have a query or script to see this?
The v$sort_usage query you run is fine. Just remember that v$sort_usage shows currently active sorts for the instance. Therefore, you will only see information returned via this query if a sort is currently active (i.e. you do not see the history of all sorts that have occurred).
The following query can identify some statistics regarding the users performing sorts. This helps identify the particular sessions that may be issuing resource-intensive sorts. You can also determine whether an in-memory or disk sort is being performed. The following query lists the users performing sorts a well as the type of sort:
SELECT substr(vs.username,1,20) "db user", substr(vs.osuser,1,20) "os user", substr(vsn.name,1,20) "Type of Sort", vss.value FROM v$session vs, v$sesstat vss, v$statname vsn WHERE (vss.statistic#=vsn.statistic#) AND (vs.sid = vss.sid) AND (vsn.name like '%sort%') ORDER BY 2,3;The query you seem to be seeing frequently (SELECT NOMBRE,DIA_PROCESO....) must be a frequently run query (likely run within one application accessing the database). The reason why you don't see anything when you run the query in TOAD and then check v$sort_usage is because the query is over when you check....and as I mentioned earlier, v$sort_usage only shows currently active sorts.
If you want to see which objects (tables, indexes, etc) are stored in which tablespaces, you'll need to query dba_segments. It will show you a list of objects by name and type and by tablespace.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2002