Recently I came across an incident related to temporary segments. When I checked the view V$SORT_USAGE to take a look at what was using my temporary tablespace, I got shocked to see a query of the type "select 1 from dual". Obviously, this query does not do any kind of sort!
So I decided to take a close look at the issue.
First thing I noticed:
SYS @ LAB > select VIEW_DEFINITION from v$fixed_view_definition where view_name = 'GV$SORT_USAGE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, p
rev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMP
ORARY'), 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#
The field SQL_ID from v$SORT_USAGE points to the field PREV_SQL_ID from v$session!
(also in 12c).
So, what we see when we check run a query on V$SORT_USAGE is the SQL_ID of the previously executed SQL by the session.
V$TEMPSEG_USAGE is a synonym for v$SORT_USAGE:
SYS @ LAB > select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME = 'GV$TEMPSEG_USAGE';
SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
GV$TEMPSEG_USAGE SYS
GV_$SORT_USAGE
Then, I performed some tests with different kinds of temporary segments to see the behaviour. Won't put here all ths tests I did, since would make this article too long. Just will show the results.
In my queries, I added a field from X$KTSSO which is available from 11.2.0.2 onwards
This is the query:
SYS @ LAB > select ses.sid as SID, ses.program as PROGRAM, ses.sql_id as "V$SESSION SQLID", ses.prev_sql_id as "V$SESSION PREV_SQLID", so.sql_id as "V$SORT SQL_ID", kt.KTSSOSQLID, so.segtype
2 from
3 v$session ses, v$sort_usage so, x$ktsso kt
4 where
5 ses.saddr = so.SESSION_ADDR and
6 ses.saddr = kt.KTSSOSES;
Previously, I modified the parameters for sorting, so the query had to do the sorting in disk:
SQL> alter session set workarea_size_policy = 'MANUAL';
SQL> alter session set sort_area_size=1000;;
I ran a query to create some SORT and HASH temporary segments. This is the output:
SID PROGRAM V$SESSION SQL V$SESSION PRE V$SORT SQL_ID KTSSOSQLID SEGTYPE
---- ------------------------------- ------------- ------------- ------------- ------------- ---------
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 459f3z9u4fb3u 459f3z9u4fb3u b5qky49hdcr45 HASH
The same kind of output could be seen while running the query. (more or less segments, but the SQL_IDs did not change). After the query ended and we got the full output, the query no longer showed results.
So:
SEGTYPE: HASH and SORT
SQL:
V$SORT_USAGE.SQL_ID: 459f3z9u4fb3u
V$SESSION_PREV_SQL_ID: 459f3z9u4fb3u
V$SESSION.SQL_ID: b5qky49hdcr45
X$KTSSO.SQL_ID: b5qky49hdcr45
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = 'b5qky49hdcr45';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select a.table_name as A , b.table_name as B from dba_tables a, dba_tables b ord
er by a asc
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = '459f3z9u4fb3u';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select value$ from props$ where name = 'GLOBAL_DB_NAME'
PRO SYS @ LAB >
As we can see, the SQL_ID from the view V$SORT_USAGE matches the field PREV_SQL_ID from v$SESSION and they do not correspond to the query who used the temporary segment.
(actually, I think that the query 459f3z9u4fb3u would be the one who gets the result to show the name of my instance at the prompt).
Let's try with segtype DATA. I will do the test using a temporary table:
PRO SYS @ LAB >create global temporary table test (campo number) on commit preserve rows;
Table created.
PRO SYS @ LAB >
PRO SYS @ LAB > declare
x integer;
begin
for x in 1..100
loop
insert into test values(x);
end loop;
end;
/ 2 3 4 5 6 7 8 9
PL/SQL procedure successfully completed.
PRO SYS @ LAB >
SID PROGRAM V$SESSION SQL V$SESSION PRE V$SORT SQL_ID KTSSOSQLID SEGTYPE
---------- ------------------------------------------------ ------------- ------------- ------------- ------------- ---------
144 sqlplus@myserver (TNS V1-V3) 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 DATA
PRO SYS @ LAB >
SEGTYPE: DATA
SQL:
V$SORT_USAGE.SQL_ID: 7uhrqz2gc3ad9
V$SESSION_PREV_SQL_ID: 7uhrqz2gc3ad9
V$SESSION.SQL_ID:
X$KTSSO.SQL_ID: d0p6uv6pamwk5
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = '7uhrqz2gc3ad9';
SQL_FULLTEXT
--------------------------------------------------------------------------------
declare
x integer;
begin
for x in 1..100
loop
insert into test values(x);
end loop;
end;
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = 'd0p6uv6pamwk5';
SQL_FULLTEXT
--------------------------------------------------------------------------------
INSERT INTO TEST VALUES(:B1 )
PRO SYS @ LAB >
(Before and after the commit)
SID PROGRAM V$SESSION SQL V$SESSION PRE V$SORT SQL_ID KTSSOSQLID SEGTYPE
---- --------------------------------- ------------- ------------- ------------- ------------- ---------
144 sqlplus@myserver (TNS V1-V3) 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 DATA
PRO SYS @ LAB >
We can see that now, the fields V$SORT_USAGE.SQL_ID and V$SESSION_PREV_SQL_ID both show the SQL who created the segment, and the field X$KTSSO.SQL_ID shows the SQL that , let's say, makes use of it.
But, what if we ran some more queries in the session with SID 144?:
We run the query that made the SORT in the previous example:
PRO SYS @ LAB >select a.table_name as A , b.table_name as B from dba_tables a, dba_tables b order by a asc;
And let's see the segments. While running:
PRO SYS @ LAB >/
SID PROGRAM V$SESSION SQL V$SESSION PRE V$SORT SQL_ID KTSSOSQLID SEGTYPE
---- --------------------------------- ------------- ------------- ------------- ------------- ---------
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 SORT
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 HASH
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 d0p6uv6pamwk5 DATA
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 DATA
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 DATA
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 7uhrqz2gc3ad9 7uhrqz2gc3ad9 b5qky49hdcr45 DATA
We see some SORTS and HASH where the field KTSSOSQLID corresponds to the PL/SQL. I could not check it, but I guess that this is because are reusing this segment.
Once ends:
PRO SYS @ LAB >/
SID PROGRAM V$SESSION SQL V$SESSION PRE V$SORT SQL_ID KTSSOSQLID SEGTYPE
---- --------------------------------- ------------- ------------- ------------- ------------- ---------
144 sqlplus@myserver (TNS V1-V3) b5qky49hdcr45 b5qky49hdcr45 d0p6uv6pamwk5 DATA
PRO SYS @ LAB >
SQL:
V$SORT_USAGE.SQL_ID: b5qky49hdcr45
V$SESSION_PREV_SQL_ID: b5qky49hdcr45
V$SESSION.SQL_ID:
X$KTSSO.SQL_ID: d0p6uv6pamwk5
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = 'd0p6uv6pamwk5';
SQL_FULLTEXT
--------------------------------------------------------------------------------
INSERT INTO TEST VALUES(:B1 )
PRO SYS @ LAB >select sql_fulltext from v$sql where sql_id = 'b5qky49hdcr45';
SQL_FULLTEXT
--------------------------------------------------------------------------------
select a.table_name as A , b.table_name as B from dba_tables a, dba_tables b ord
er by a asc
So, we see that the fields V$SESSION_PREV_SQL_ID and V$SESSION_PREV_SQL_ID no longer hold the SQL_ID corresponding to the query that created the segment, but X$KTSSO.SQL_ID still keeps the SQL_ID of the query that first used the segment.
Once the session ends, the segments are released.
I do not want this article to become much longer, so I will not write here the test I did with LOB_DATA, but the behaviour was the same as with DATA segtypes. I guess that the behaviout with INDEX and LOB_INDEX should be the same as with DATA and LOB_DATA. Could not test it yet.
So, as a conclusion: when we are having a temporary segment issue, be careful when looking at v$SORT_USAGE, since, as we have seen, not always shows what created or used the segment.
I advice to use the given query and take into account those facts:
If segtype is SORT OR HASH, the fields V$SESSION.SQL_ID and
X$KTSSO.SQL_ID show who created the segment. Those segments will be released once the query ends.
If segtype is DATA, LOB_DATA (and possibly, INDEX and LOB_INDEX), the field X$KTSSO.SQL_ID will show the SQL_ID of the first command that used the segment (not who created it).
As seen, if t he session doing the sort runs another query after the query that did the sorting, V$SORT_USAGE will no longer hold the SQL_ID of the query that created the segment. (as it's V$SORT_USAGE.SQL_ID is only a reference to V$SESSION.PREV_SQL_ID.
By the way, regarding why SQL_ID did not appear in the field SQL_ID of v$session, check this article:
https://coskan.wordpress.com/2011/11/11/where-is-the-sql_id-of-active-session/
No hay comentarios:
Publicar un comentario