如何知道数据库的临时表空间的使用情况
oracle 使用临时表空间的情况首先临时表数据放在pga内，如果pga容纳不下，临时表数据会放在临时表空间中，这个时候会发生临时表空间的物理读和物理写，session因此会变得缓慢。排除问题的思路如下：
1、确认临时表空间的物理读写的负载，查询数据字典v$tempstat
V$TEMPSTATThis view contains information about file read/write statistics.
ColumnDatatypeDescription
FILE#NUMBERNumber of the file
PHYRDSNUMBERNumber of physical reads done
PHYWRTSNUMBERNumber of times DBWR is required to write
PHYBLKRDNUMBERNumber of physical blocks read
PHYBLKWRTNUMBERNumber of blocks written to disk, which may be the same as PHYWRTS if all writes are single blocks
SINGLEBLKRDSNUMBERNumber of single block reads
READTIMNUMBERTime (in hundredths of a second) spent doing reads if the TIMED_STATISTICS parameter is true; 0 if false
WRITETIMNUMBERTime (in hundredths of a second) spent doing writes if the TIMED_STATISTICS parameter is true; 0 if false
SINGLEBLKRDTIMNUMBERCumulative single block read time (in hundredths of a second)
AVGIOTIMNUMBERAverage time (in hundredths of a second) spent on I/O, if the TIMED_STATISTICS parameter is true; 0 if false
LSTIOTIMNUMBERTime (in hundredths of a second) spent doing the last I/O, if the TIMED_STATISTICS parameter is true; 0 if false
MINIOTIMNUMBERMinimum time (in hundredths of a second) spent on a single I/O, if the TIMED_STATISTICS parameter is true; 0 if false
MAXIORTMNUMBERMaximum time (in hundredths of a second) spent doing a single read, if the TIMED_STATISTICS parameter is true; 0 if false
MAXIOWTMNUMBERMaximum 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.
ColumnDatatypeDescription
USERNAMEVARCHAR2(30)User who requested temporary space
USERVARCHAR2(30)This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in USERNAME.
SESSION_ADDRRAW(4 | 8)Address of shared SQL cursor
SESSION_NUMNUMBERSerial number of session
SQLADDRRAW(4 | 8)Address of SQL statement
SQLHASHNUMBERHash value of SQL statement
SQL_IDVARCHAR2(13)SQL identifier of SQL statement
TABLESPACEVARCHAR2(31)Tablespace in which space is allocated
CONTENTSVARCHAR2(9)Indicates whether tablespace is TEMPORARY or PERMANENT
SEGTYPEVARCHAR2(9)Type of sort segment:
[*]SORT
[*]HASH
[*]DATA
[*]INDEX
[*]LOB_DATA
[*]LOB_INDEX
SEGFILE#NUMBERFile number of initial extent
SEGBLK#NUMBERBlock number of the initial extent
EXTENTSNUMBERExtents allocated to the sort
BLOCKSNUMBERExtents in blocks allocated to the sort
SEGRFNO#NUMBERRelative 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.
ColumnDatatypeDescription
ADDRESSRAW(4 | 8)Used with HASH_VALUE to uniquely identify a cached cursor
HASH_VALUENUMBERUsed with ADDRESS to uniquely identify a cached cursor
SQL_IDVARCHAR2(13)SQL identifier of a cached cursor
COMMAND_TYPENUMBERCode for the type of SQL statement (SELECT, INSERT, and so on)
PIECENUMBERNumber used to order the pieces of SQL text
SQL_TEXTVARCHAR2(64)A column containing one piece of the SQL text
4、如果有必要我们可以对这个session进行trace，看一下具体的执行信息
关于trace的具体信息，我们后面会陆续讲到
注意：在粘贴的时候，格式有点乱，凑乎者也能看。就是再看视图的结构的时候。 thanks for your knowladge
页:
[1]