详解ORACLE数据字典--V$SQL_WORKAREA_ACTIVE

Streamoutput 2019-07-08

概述

今天主要介绍Oracle一个数据字典: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 视图确是对真实发生的情况统计出来的数据,所以根据这个数据,可以对PGA做适当地调整。


V$SQL_WORKAREA_ACTIVE的结构

SQL> desc V$SQL_WORKAREA_ACTIVE

详解ORACLE数据字典--V$SQL_WORKAREA_ACTIVE

列说明如下:

详解ORACLE数据字典--V$SQL_WORKAREA_ACTIVE

通过这张视图,可以看到当前SQL_WORKAREA_ACTIVE的相关操作如sort,hash join等及内存信息。


常用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;

详解ORACLE数据字典--V$SQL_WORKAREA_ACTIVE

这里pass都是0就是PGA里面算,ESIZE是优化器期望大小,mem是实际使用大小,max mem是PGA曾经使用的最大值 by sid,tssize是溢出大小。


后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~

详解ORACLE数据字典--V$SQL_WORKAREA_ACTIVE

相关推荐