Handling of massive usage of temporary oracle tablespace

The core trading system of a telecom operator's client, the temporary table space is occupied, the temporary table space is stretched to 600GB. Is such a problem complicated? Depends on a number of factors, but today's case is not complicated, if we have sufficient knowledge of the circumstances in which temporary tablespaces are used.
First of all, we are going to check what session is taking up temporary table space, how much it takes, and what the specific type of temporary segment is. Just as we want to know the cost of this month is too large, to analyze the reasons to see what is too much overhead, the amount of money spent, the use of expenses and so on.
This step is relatively simple, query v$sort_usage can be:
View Plaincopy to Clipboardprint?
SELECT * FROM
(Select username,session_addr,sql_id,contents,segtype,blocks*8/1024/1024 GB
From v$sort_usage to blocks desc)
where rownum<=200;

USERNAME session_addr sql_id CONTENTS segtype GB
----------  ---------------- ------------- --------- --------- -----------
XXXX 0700002949bcd8a0 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294bd99628 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294cd10480 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294dd1ac88 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294cd68d70 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294dbdf760 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294edb5d10 291nk7db4bwdh temporary SORT. 9677734375
XXXX 070000294fd7d818 291nk7db4bwdh temporary SORT. 9677734375
... More results, ignoring partial output ...
SQL_ID are all the same, does this SQL have its particularity? Segtype for sort indicates that this temporary segment is a "sorted segment," For SQL sorting, and the same size, the session occupies a temporary segment size of nearly 1GB, hundreds of sessions added together, want to not let temporary table space is most difficult.
Look at what SQL is represented by this same SQL ID:
View Plaincopy to Clipboardprint?
Sql> @sqlbyid 291NK7DB4BWDH

Sql_fulltext
--------------------------------------------------------------------------------------------------------------
SELECT A.llevel, A.lmode from table_xxx A WHERE a.service_name =: service_name and state= ' Y '
Obviously, this is a very simple SQL, no order BY, there is no group BY, UNION, distinct, etc. need to sort, table_xxx is a common table, not a view. What's the problem? Could it be that V$sort_usage's sql_id column has errors? We looked at the SQL that one of the sessions was executing:
View Plaincopy to Clipboardprint?
Select sid,prev_sql_id, sql_id from v$session where saddr= ' 070000294ac0d050 ';

SID prev_sql_id sql_id
----------- ------------- -------------
3163 291NK7DB4BWDH
In V$sort_usage, you see that a session does not currently perform any sql,v$sort_usage in sql_id is the SQL that was executed before the session. Why does this show the SQL that was executed before the session, this question is further elaborated, but at least one thing is to be judged: if a large number of temporary segments are generated by the SQL currently executing in the session, then hundreds of sessions are executing SQL that requires a large amount of temporary space, and the system is already crashing. So the occupancy of these temporary tablespaces should not be generated by the SQL currently executing, at least not most of them.
A misconception for most people is that the space currently occupied in the temporary tablespace is generated by the SQL that the session is currently executing. A simple analysis of the above, the situation should not be so. We can analyze the execution process based on query class sql:
Resolves the SQL statement (Parse) to generate a cursor (Open Cursor).
Execute the SQL statement (execute), strictly speaking, executes the newly generated cursor.
Fetch data (FETCH) in the cursor.
Closes the cursor (close Cursor).
The key is in step 3rd. We all know that fetching data has an array size concept that represents how many data is taken from a cursor at a time, which is a cyclic process. If the SQL query gets 1000 data, take 100 at a time, 10 times. For the fetch Cursor, there are two points:
A cursor, or an SQL statement, does not require the client to take out all the data, and it is possible to close the cursor with only a fraction of the data.
As long as the cursor has not been closed, the database maintains the state of the cursor, and if it is a sorted SQL, it also needs to maintain the data that the SQL has ordered.
It is clear from the 2nd above that if a SQL uses a temporary segment to sort, the Oracle database will not release the temporary segment if the SQL corresponding cursor is not closed, because for the Oracle database it will not know whether the client will continue to fetch the cursor's data.
Based on this analysis, we only need to randomly select a session that takes up close to 1GB, query v$open_cursor, and see if there are large data-sorted SQL in its open cursors:
View Plaincopy to Clipboardprint?
Sql> Select Sql_id,sorts,rows_processed/executions from V$sql
2 where parsing_schema_name= ' ACCT ' and executions>0 and sorts>0
3 and sql_id in (select sql_id from V$open_cursor where sid=4505)
4 ORDER by 3;

sql_id Sorts Rows_processed/executions
------------- ----------- -------------------------
... Omit partial output ...
86VP997JBZ7S6 63283 593
CFPDPB526AD43 592 35859.79899
CFPDPB526AD43 188 55893.61702
CFPDPB526AD43 443 71000
The last three cursors, which are actually the same SQL statement, have the largest amount of data, let's take a look at what this SQL is:
View Plaincopy to Clipboardprint?
@sqlbyid cfpdpb526ad43

Sql_fulltext
---------------------------------------------------------------------------------------------------
Select ... from c, B, A, D, e where ... order by d.billing_cycle_id Desc,e.offer_name,a.acc_name
Based on the reasons for customer confidentiality, SQL does the processing to know that the SQL is indeed a sequence, but in the SQL does not see that this SQL does not have any substantial ability to filter large amounts of data conditions. So let's look at the SQL statement of Count (*):
View Plaincopy to Clipboardprint?
COUNT (*)
--------
12122698
Out of the results actually have 1200多万条 data, a foreground application, do not know what to take 1200多万条 data. But from the rows_processed/executions only tens of thousands of of the results, the application after tens of thousands of data, for some reason (the most likely is no longer processing more data), no longer continue to fetch data, but the cursor has not been closed.
It is easier to use temporary tablespace space when the sort by is demonstrated.
View Plaincopy to Clipboardprint?
According to Dba_objects, build a test table T1, so that its data volume reached 20 million lines.
Select COUNT (*) from T1;

COUNT (*)
-----------
20171200

Then set the SQL workspace to manual mode and set the sort memory size limit to 200M:
Alter session set Workarea_size_policy=manual;
Alter session set SORT_AREA_SIZE=209715200;

The query gets the current session SID:
Select Sid from V$mystat where rownum< = 1;

Sid
-----------
2111

Execute the following code:
Declare
2 v_object_name varchar2 (100);
3 v_dummy varchar2 (100);
4 begin
5 for Rec in (SELECT * from T1 ORDER by Object_id,object_name) loop
6 Select Object_type into v_dummy from T1 where rownum<=1;
7 Select object_name into V_object_name from dba_objects where object_id=rec.object_id;
8 Dbms_lock.sleep (60*10);
9 exit;
Ten end Loop;
one end;
12/
This code opens a cursor, sorts 20 million of the data, and then takes only one piece of data in the loop and then goes to sleep. Monitoring in another window to 2111 the event of this session becomes the Pl/sql lock timer and goes to the query v$sort_usage:
Select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 GB
2 from V$sort_usage a,v$session b
3 where a.session_addr=b.saddr
4 and b.sid=2111;

sort_sql_id sql_id prev_sql_id CONTENTS segtype GB
------------- ------------- ------------- --------- --------- -----------
FABH24PRGK2SJ bhzf316mdc07w fabh24prgk2sj Temporary SORT 1.444824219
You can see that the sql_id in V$sort_usage (that is, sort_sql_id in the above results) is consistent with the pre_sql_id in V$session, which is:

@sqlbyid FABH24PRGK2SJ
Sql_fulltext
--------------------------------------------------------
SELECT object_name from Dba_objects WHERE object_id=:b1

In fact, the SQL that is currently executing is:
@sqlbyid bhzf316mdc07w

Sql_fulltext
---------------------------------------------------------------------------
Declare
V_object_name varchar2 (100);
V_dummy varchar2 (100);
Begin
For rec in (SELECT * from T1 ORDER by Object_id,object_name) loop
Select Object_type into V_dummy from T1 where rownum<=1;
Select object_name into V_object_name from dba_objects where object_id=rec.object_id;
Dbms_lock.sleep (60*10);
Exit
End Loop;
End
Problem analysis Here, it is clear that there is a problem with the application, perhaps the business logic problem, perhaps based on the conditions chosen by the foreground of the concatenation of SQL, but there is no condition to query all the data. The next step is to find a developer, and the following things don't have much to do with the subject. We can go further in this case to explore more knowledge of the temporary tablespace.
The 1th point to expand here is that the sql_id in V$sort_usage is not the SQL that the session is executing, so let's take a look at the view fixed_view_definition know:
View Plaincopy to Clipboardprint?
Select x$ktsso.inst_id, username, username, ktssoses, Ktssosno, Prev_sql_addr, Prev_hash_value,
prev_sql_id, KTSSOTSN, decode (ktssocnt, 0, ' permanent ', 1, ' temporary '), decode (Ktssosegt, 1,
' SORT ', 2, ' HASH ', 3, ' DATA ', 4, ' INDEX ', 5, ' Lob_data ', 6, ' Lob_index ', ' UNDEFINED ', KTSSOFNO,
Ktssobno, Ktssoexts, Ktssoblks, ktssorfno from X$ktsso, v$session where ktssoses = v$session.saddr
and Ktssosno = v$session.serial#
Originally in the definition of v$sort_usage, it is clear that the sql_id column is the prev_sql_id column in V$session, not the current SQL. As to why such a definition is, to be honest, it is not known yet.
However, starting with this version of 11.2.0.2, a field ktssosqlid is added to the base table X$ktsso of V$sort_usage, representing the SQL that the temporary segment is really associated with, taking the test results above as an example, the results of the query base table are as follows:
View Plaincopy to Clipboardprint?
Select Ktssosqlid from X$ktsso, v$session where ktssoses = v$session.saddr
2 and Ktssosno = v$session.serial#
3 and v$session.sid=2111;

Ktssosqlid
-------------
60t6fmjsw6v8y

@sqlbyid 60t6fmjsw6v8y

Sql_fulltext
---------------------------------------------------------------------------
SELECT * from T1 ORDER by Object_id,object_name
What you can see is that we are querying the SQL that actually generates the temporary segment.
All along, the sql_id in v$sort_usage misled a lot of people. Fortunately, Oracle has made up the 11.2.0.2 from the beginning, and there are documents in MOS:
Bug 17834663-include SQL ID for statement so created a temporary segment in Gv$sort_usage (document ID 17834663.8)
In previous versions, it is not possible to identify the SQL ID
Of the statement that created a given temporary segment in
eg. (G) V$sort_usage.
@ Via The fix for bug:8806817 we added the SQL ID to the X$ktsso
@ table (ktssosqlid), but it wasn't exposed in the Gv$sort_usage
@ View until now.
The SQL ID of the statement is in column sql_id_tempseg
This is the fix cannot be provided as a interim patch.
Let's refine the v$sort_usage and use the following query instead:
View Plaincopy to Clipboardprint?
Select k.inst_id "inst_id",
Ktssoses "Saddr",
Sid
Ktssosno "serial#",
Username "username",
Osuser "Osuser",
Ktssosqlid "sql_id",
KTSSOTSN "Tablespace",
Decode (ktssocnt, 0, ' permanent ', 1, ' temporary ') "CONTENTS",
Note that tablespace and contents have changed in the v$sort_usage definition of 12c.
Decode (Ktssosegt, 1, ' SORT ', 2, ' HASH ', 3, ' DATA ', 4, ' INDEX ',
5, ' Lob_data ', 6, ' Lob_index ', ' UNDEFINED ', ' Segtype ',
Ktssofno "segfile#",
Ktssobno "segblk#",
Ktssoexts "Extents",
Ktssoblks "BLOCKS",
Round (ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
Ktssorfno "segrfno#"
From X$ktsso K, V$session s,
(select value from V$parameter where name= ' db_block_size ') p
where ktssoses = S.saddr
and Ktssosno = s.serial#;
The 2nd thing to expand is the meaning of the different values of the Segtype columns in V$sort_usage:
Sort:sql the temporary segments used by the sort, including order by, Group by, Union, distinct, window function (Windows functions), indexing, and so on.
Data: The temporary table (Global temporary table) stores the segments that are being made available.
Index: The segment used by the index built on the temporary table.
Hash:hash algorithm, such as the temporary segment used by a hash connection.
Lob_data and Lob_index: temporary segments used by temporary lob.
According to the above paragraph type, generally can be divided into three types of occupation:
SQL statement ordering, HASH join occupancy
Temporary table occupancy
Temporary LOB objects occupy
The exception occupancy of a temporary tablespace, a slow-growing, another condition: to be full. Usually a large amount of data is sorted or a great index is created. Slow growth, similar to the system's memory is gradually occupied, there is a "leak." For example, a sorted SQL cursor is not closed, such as a case in this article, such as a session-level temporary table that has not been purged since the data was generated, and a temporary LOB object has not been cleaned up or compromised. The first two are better to analyze, but the issue of temporary lob leaks is much more complicated.
Take a look at a test:
View Plaincopy to Clipboardprint?
Select Sid from V$mystat where rownum<=1;

Sid
-----------
1773
Declare
2 V_lob Clob;
3 begin
4 dbms_lob.createtemporary (v_lob,true);
5 Dbms_lob.writeappend (V_lob,1000,lpad (' A ', 1000, ' a '));
6 end;
7/
After the above code is executed, in another window, we query v$sort_usage:
View Plaincopy to Clipboardprint?
Select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, contents,segtype,blocks*8/1024/1024 GB
2 from V$sort_usage a,v$session b
3 where a.session_addr=b.saddr
4 and b.sid=1773;

sort_sql_id sql_id prev_sql_id CONTENTS segtype GB
------------- ------------- ------------- --------- --------- -----------
9BABJV8YQ8RU3 9babjv8yq8ru3 temporary lob_data. 0004882813

@sqlbyid 9BABJV8YQ8RU3

Sql_fulltext
---------------------------------------------------------------------------
BEGIN Dbms_output. Get_lines (: LINES,: Numlines); End;
As you can see, this session has produced a temporary segment of type Lob_data. Although the SQL code has finished executing, the session is already idle, but the temporary segment still exists.
LOB variables in Oracle, similar to pointers in C statements, or database connection connection similar to Java code, need to be freed. The problematic code above is missing the code to release the LOB: Dbms_log.freetemporary (V_LOB). Fortunately for this scenario, Oracle provides a remedy by setting the 60025 event to automatically clean out the inactive lob and simply add event= ' 60025 trace name context forever to the parameter file.
In an Oracle database, the XmlType type is also actually a lob type, and a xmltype type of data operation may produce more lob temporary segments. The changes on the LOB type fields, such as LOB stitching, also generate lob temporary segments. If a large number of LOB-type temporary segments are found in v$sort_usage, it is usually due to a problem with the code, the LOB is not released, or the Oracle itself is a bug. On MOS, if you search with the LOB temporary keyword, you will find quite a few leaks or temporary segments about the LOB temporary segment that do not release the related documents.
Finally, no matter what the situation is causing the temporary table space is too much occupied, usually restart the application can release the temporary segment, because after the session exit, the corresponding temporary segment will be released. It seems that the "restart" Dafa is useful in this case