概述

今天主要介紹Oracle一個數(shù)據(jù)字典：V$SQL_WORKAREA_ACTIVE，下面一起來看看吧~

V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_ACTIVE contains an instantaneous view of the work areas currently allocated by the system. You can join this view against V$SQL_WORKAREA on WORKAREA_ADDRESS to access the definition of that work area. If a work area spills to disk, then this view contains information for the temporary segment created on behalf of this work area.

V$SQL_WORKAREA_HISTOGRAM 視圖確是對真實發(fā)生的情況統(tǒng)計出來的數(shù)據(jù)，所以根據(jù)這個數(shù)據(jù)，可以對PGA做適當(dāng)?shù)卣{(diào)整。

V$SQL_WORKAREA_ACTIVE的結(jié)構(gòu)

SQL> desc V$SQL_WORKAREA_ACTIVE

列說明如下：

通過這張視圖，可以看到當(dāng)前SQL_WORKAREA_ACTIVE的相關(guān)操作如sort，hash join等及內(nèi)存信息。

常用sql

SQL> SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
 operation_type OPERATION,
 trunc(EXPECTED_SIZE / 1024) ESIZE,
 trunc(ACTUAL_MEM_USED / 1024) MEM,
 trunc(MAX_MEM_USED / 1024) "MAX MEM",
 NUMBER_PASSES PASS,
 trunc(TEMPSEG_SIZE / 1024) TSIZE
 FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1, 2;

這里pass都是0就是PGA里面算，ESIZE是優(yōu)化器期望大小，mem是實際使用大小，max mem是PGA曾經(jīng)使用的最大值 by sid，tssize是溢出大小。