Temporary Segments

The Temporary Segments window shows temporary segments used by sessions and provides useful detail about sorting, hash joins, and temporary table activities. It is based on v$sort_usage view. Use this information to check the distribution of temp segments across tempfiles when tuning Parallel queries or when experiencing contention on temporary tablespace(s).

Viewing options.

Some sessions may use several segments (when executing PQ, using temporary tables etc). To quickly identify all segments related to the same session, click on any temporary segment of that session. All segments belonging to the coordinator session and all related slave sessions will be highlighted.

Below is an example of this window showing a group of highlighted segments related to the same session. The picture also shows the location of hot spots: the SID and OWNER columns. Clicking on these hot spots opens the Session Details window and selects the corresponding session in the session list.

Descriptions of the columns in the Temporary Segments tabular view.

• N - Instance ID / Node number (RAC only) (gv$sort_usage.INST_ID);
• SID - Session ID (v$session.SID);
• SERIAL# - Session serial number (v$session.SERIAL#);
• USERNAME- Session user name (v$session.USERNAME);
• OWNER - SID of the PQ coordinator (derived from v$session.OWNERID);
• TS_NAME - Tablespace in which segment is allocated (v$sort_usage.TABLESPACE);
• BLOCKS- Size of segment in blocks (v$sort_usage.BLOCKS);
• Size,M - Size of segment in MB (product of v$sort_usage.BLOCKS and corresponding dba_tablespaces.BLOCK_SIZE);
• SEGTYPE - Name of operation this segment is used for (v$sort_usage.SEGTYPE);
• ObjectName - Name of temporary table or index (x$ktsso.KTSSOOBJN - object ID, then converted into the object name);
• File# - File number of initial extent (v$sort_usage.SEGFILE#);
• TempFile - Name of the database file (dba_data_files.FILE_NAME);

Note: ObjectName column is shown only if x$ktsso fixed table is accessible. Please see x$ suggested views in Installation and Setup page.