Shulou(Shulou.com)06/01 Report--

Today, the user executed a very simple SQL, and the output result set was only tens of thousands, involving three tables, and the maximum amount of data was only 1 million. However, the result reported that there was insufficient table space. Theoretically, such SQL should not eat so much temporary table space.

Query temporary table space usage:

select USERNAME,SESSION_NUM,SQL_ID,TABLESPACE,SEGTYPE,BLOCKS*8192/1024/1024||' MB' as MB from v$sort_usage order by 6 desc

v$sort_usage displays information for each sort segment of a given instance, and only operations that occur in the temporary table space update the view. Not only is the sort action recorded here, but it is recorded as long as the operation occurs in the temporary table space, and the sort occurs in memory, and the view is not updated.

General fixed thinking I will think that this SQL has a problem, and then I executed once, immediately threw an exception, I immediately reacted to this time is not right, because SQL if you want to temporarily use a lot of temporary table space, but also gradually a little bit to take up space, this exception thrown too fast, and I went to query dba_temp_files, really because of design problems, temporary table space creation unreasonable, really is the space is too small:

alter database tempfile xxx autoextend on next 200m maxsize unlimited;

Several scenarios for temporary table space usage:

1、order by or group by ;

2. Index creation and re-creation;

3, distinct, SORT-MERGE JOINS, HASH JOIN and other operations;

4、union & intersect & minus sort-merge joins;

5. Other abnormal operations

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.