oracle 使用临时表空间的情况首先临时表数据放在pga内，如果pga容纳不下，临时表数据会放在临时表空间中，这个时候会发生临时表空间的物理读和物理写，session因此会变得缓慢。
排除问题的思路如下：
1、确认临时表空间的物理读写的负载，查询数据字典v$tempstat

V$TEMPSTATThis view contains information about file read/write statistics.

Column

Datatype

Description

FILE#

NUMBER

Number of the file

PHYRDS

NUMBER

Number of physical reads done

PHYWRTS

NUMBER

Number of times DBWR is required to write

PHYBLKRD

NUMBER

Number of physical blocks read

PHYBLKWRT

NUMBER

Number of blocks written to disk, which may be the same as PHYWRTS if all writes are single blocks

SINGLEBLKRDS

NUMBER

Number of single block reads

READTIM

NUMBER

Time (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is true; 0 if false

WRITETIM

NUMBER

Time (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is true; 0 if false

SINGLEBLKRDTIM

NUMBER

Cumulative single block read time (in hundredths of a second)

AVGIOTIM

NUMBER

Average time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if false

LSTIOTIM

NUMBER

Time (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is true; 0 if false

MINIOTIM

NUMBER

Minimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is true; 0 if false

MAXIORTM

NUMBER

Maximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is true; 0 if false

MAXIOWTM

NUMBER

Maximum time (in hundredths of a second) spent doing a single write, if the TIMED_STATISTICS parameter is true; 0 if false

确认临时表空间的使用是否有问题。
2、我们关心到底是哪个session在如何使用我们的临时表空间，最经典的就是data（临时表、排序、hash、index、lob）
查询数据字典
V$TEMPSEG_USAGEThis view describes temporary segment usage.

Column

Datatype

Description

USERNAME

VARCHAR2(30)

User who requested temporary space

USER

VARCHAR2(30)

This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in USERNAME.

SESSION_ADDR

RAW(4 | 8)

Address of shared SQL cursor

SESSION_NUM

NUMBER

Serial number of session

SQLADDR

RAW(4 | 8)

Address of SQL statement

SQLHASH

NUMBER

Hash value of SQL statement

SQL_ID

VARCHAR2(13)

SQL identifier of SQL statement

TABLESPACE

VARCHAR2(31)

Tablespace in which space is allocated

CONTENTS

VARCHAR2(9)

Indicates whether tablespace is TEMPORARY or PERMANENT

SEGTYPE

VARCHAR2(9)

Type of sort segment:

• SORT
• HASH
• DATA
• INDEX
• LOB_DATA
• LOB_INDEX
  
  

SEGFILE#

NUMBER

File number of initial extent

SEGBLK#

NUMBER

Block number of the initial extent

EXTENTS

NUMBER

Extents allocated to the sort

BLOCKS

NUMBER

Extents in blocks allocated to the sort

SEGRFNO#

NUMBER

Relative file number of initial extent

3、确认sql语句，使用上面的视图中查询到的sqlhash，使用下面的视图进行查找
V$SQLTEXTThis view contains the text of SQL statements belonging to shared SQL cursors in the SGA.

Column

Datatype

Description

ADDRESS

RAW(4 | 8)

Used with HASH_VALUE to uniquely identify a cached cursor

HASH_VALUE

NUMBER

Used with ADDRESS to uniquely identify a cached cursor

SQL_ID

VARCHAR2(13)

SQL identifier of a cached cursor

COMMAND_TYPE

NUMBER

Code for the type of SQL statement (SELECT, INSERT, and so on)

PIECE

NUMBER

Number used to order the pieces of SQL text

SQL_TEXT

VARCHAR2(64)

A column containing one piece of the SQL text

4、如果有必要我们可以对这个session进行trace，看一下具体的执行信息
关于trace的具体信息，我们后面会陆续讲到

thanks for your knowladge

注意：在粘贴的时候，格式有点乱，凑乎者也能看。就是再看视图的结构的时候。