Oracle高资源消耗SQL语句定位

梦里梦外; 2022-08-05 01:22 265阅读 0赞

#     #

文章目录

*  [相关系统视图功能与区别][Link 1]
 *  [高资源消耗SQL查找定位][SQL]
 *  [相关视图重要字段][Link 2]

Oracle SQL语句资源消耗监控最常用的系统视图有v$sql、v$sqlarea、v$sqltext和v$session。本文我们先了解这些视图的作用与区别，然后了解如何定位高资源消耗SQL语句，最后再了解一下各视图字段具体含义。

## 相关系统视图功能与区别 ##

**v$sql和v$sqlarea**基本相同，记录了共享SQL区（share pool）中SQL统计信息，如内存消耗、IO（物理磁盘读和逻辑内存读）、排序操作、哈希ID等数据。不同之处在于v$sql为每一条SQL保留一个条目，而v$sqlarea中根据sql\_text（需要注意，该处存储的为当前SQL指针的前1000个字符，也就是说这里记录的SQL可能是不完整的！）进行group by，统计列进行sum()，通过version\_count计算子指针的个数。

然而，文本（sql\_text）相同的SQL语句在数据库中意义可能完全不同。比如数据库中存在两个用户User1和User2，这两个用户各拥有一张数据表EMP。那么当两个用户发出查询select count(\*) from emp;时各自访问自己SCHEMA中的表EMP，而两者表内容不同所以其资源消耗肯定也不同。此时，在v$sql中会有这两条完全一样的SQL各自的统计信息，而在v$sqlarea中sql\_text相同的2个指针会合并起来，执行次数、DISK\_READS、BUFFER\_GETS等统计信息都会累加（sum），version\_count会显示为2，这就是v$sqlarea的聚合作用。

**v$sqltext**中没有统计信息，然而却存储着完整的SQL语句及其哈希ID等。

对于这三者，我们可以使用视图v$fixed\_view\_definition来查看视图的源表，如下：

SELECT view\_definition FROM v$fixed\_view\_definition WHERE view\_name='GV$SQL';

SELECT view\_definition FROM v$fixed\_view\_definition WHERE view\_name='GV$SQLAREA';

SELECT view\_definition FROM v$fixed\_view\_definition WHERE view\_name='GV$SQLTEXT';

注：视图名为V$SQL但该视图的源又是GV$SQL，所以直接使用GV$SQL，其他两个也如此。

通过以上3条语句可以发现，V$SQL数据来源X$KGLCURSOR\_CHILD，其实数据还是来源于X$KGLOB；而V$SQLAREA数据来源X$KGLCURSOR\_CHILD\_SQLID本质是对X$KGLCURSOR\_CHILD按照sql\_id等字段分组汇总后的结果；V$SQLTEXT数据来源X$KGLNA。

**v$session**主要用来确定会话相关信息，如通过SID和SERIAL\#来唯一确定一个session（SID可能会重复）、会话拥有者用户名USERNAME、会话状态（active:正在执行SQL语句、inactive:等待操作、killed:被杀死）、会话由哪个客户端发起（MACHINE、TERMINAL）、正在执行什么SQL（通过SQL\_ADDRESS、SQL\_HASH\_VALUE、SQL\_ID、SQL\_CHILD\_NUMBER确定，有这些再借助v$sqltext就能知道）、甚至上一次执行的SQL是什么（通过PREV\_SQL\_ADDRESS等确定）、锁等待相关信息（如所在表、文件、块、被锁行）等。

## 高资源消耗SQL查找定位 ##

**1）查看读硬盘多或占用内存可能多的SQL：**

select sql\_text, disk\_reads, buffer\_gets, parsing\_schema\_name, executions

from v$sqlarea

order by disk\_reads desc;

说明：单纯从V$sqlarea中是无法查出每个SQL消耗的内存量的，但我们可以借助磁盘读次数间接反映可能的消耗内存量较大的SQL语句，然后再借助执行计划（如v$sql\_plan视图）具体查看。

利用系统视图v$sqlarea，其中disk\_reads是磁盘读次数，也是主要字段，剩余字段均为参考字段。其中，buffer\_gets是内存读次数，parsing\_schema\_name是首次编译者模式名（一般与user名相同），executions是语句执行次数。

需要注意的是，v$sqlarea中sql\_text可能不完整，若需要完整的则需要借助hash\_value或sql\_id结合v$sqltext来查看分析。

**2）查看执行次数多的SQL**

select sql\_text, executions, parsing\_schema\_name

from v$sqlarea

order by executions desc;

**3）查看排序多的SQL**

select sql\_text, sorts, parsing\_schema\_name

from v$sqlarea

order by sorts desc;

该处还应涉及Library Cache命中率、内存命中率等内容，暂不总结，见转载内容“Oracle调优相关的各种命中率、使用率汇总”。

## 相关视图重要字段 ##

**v$sqlarea**

v$sql和v$sqlarea基本类似，而v$sqlarea更常用，故仅对v$sqlarea常用字段进行说明，如下（个人参考Oracle官方文档翻译的，因是最新版本，所以会跟网络上的有些出入）：

*  **SQL\_TEXT**：SQL语句的前1000个字符；
 *  **SQL\_FULLTEXT**：SQL语句的所有字符；
 *  **SQL\_ID**：缓存在高速缓冲区（library cache）中的SQL父游标的唯一标识ID（注，类似于hash\_value，不过hash\_value是4bytes而sql\_id是8bytes，sql\_id更精确后期可能会替代hash\_value）；
 *  SHARABLE\_MEM：SQL语句及其子游标占用的共享内存大小；
 *  PERSISTENT\_MEM：打开SQL语句的生命周期内所占用的固定内存大小（包含子游标）；
 *  RUNTIME\_MEM：游标执行期间所占用的固定内存大小；
 *  **SORTS**：语句执行导致的排序次数；
 *  **VERSION\_COUNT**：在缓存中以该语句为父语句的子游标总数；
 *  LOADED\_VERSIONS：缓存中载入了这条语句上下文堆（KGL heap 6）的子游标数；
 *  OPEN\_VERSIONS：父游标下打开的子游标个数；
 *  USERS\_OPENING：打开子游标的用户个数；
 *  FETCHES：SQL语句的fetch数；
 *  **EXECUTIONS**：包含所有子游标在内该SQL语句共执行次数；
 *  USERS\_EXECUTING：执行过该语句所有子游标的用户总数；
 *  LOADS：语句被载入的总次数；
 *  FIRST\_LOAD\_TIME：父游标被首次载入（编译）的时间；
 *  **PARSE\_CALLS**：父游标下所有子游标解析调用次数；
 *  **DISK\_READS**：该语句通过所有子游标导致的读磁盘次数；
 *  DIRECT\_WRITES：该语句通过所有子游标导致的直接写入次数；
 *  BUFFER\_GETS：该语句通过所有子游标导致的读缓存次数；
 *  APPLICATION\_WAIT\_TIME：应用等待时间；
 *  USER\_IO\_WAIT\_TIME：用户I/O等待时间；
 *  PLSQL\_EXEC\_TIME：PLSQL执行时间；
 *  ROWS\_PROCESSED：该SQL语句处理的总行数；
 *  OPTIMIZER\_COST：此查询优化给出的成本数；
 *  PARSING\_USER\_ID：第一次解析该父语句的用户ID；
 *  PARSING\_SCHEMA\_ID：第一次解析该语句SCHEMA的ID；
 *  PARSING\_SCHEMA\_NAME：解析该语句的SCHEMA的NAME；
 *  KEPT\_VERSIONS：指出是否当前子游标被使用DBMS\_SHARED\_POOL包标记为常驻内存；
 *  **ADDRESS**：当前游标父句柄（唯一指向该游标的一种地址编号）；
 *  **HASH\_VALUE**：该语句在library cache中hash值；
 *  PLAN\_HASH\_VALUE：执行计划的hash值，可依此确定两个执行计划是否相同（取代每行每字符进行比较的方式）；
 *  CPU\_TIME：该语句解析、执行和fetch（取值）所消耗的CPU时间；
 *  ELAPSED\_TIME：该语句解析、执行和fetch（取值）所经过的时间；
 *  LAST\_ACTIVE\_TIME：查询计划最后一次执行的时间；
 *  LOCKED\_TOTAL：所有子游标被锁的次数；

**v$sqltext**

*  **ADDRESS**：当前游标父句柄（唯一指向该游标的一种地址编号）；
 *  **HASH\_VALUE**：该游标（子游标）在library cache中唯一hash值；
 *  SQL\_ID：缓存游标中该SQL的一个唯一标识值；
 *  COMMAND\_TYPE：SQL语句类型，如select、insert、update等；
 *  PIECE：排序SQL文本的碎片数；
 *  SQL\_TEXT：包含一个完整SQL中的某一小块SQL文本字符（要完整的SQL语句需要把这些碎片组合起来）；

**v$session**

*  SADDR：session地址；
 *  SID：session标识值，常跟serial\#联合唯一确定一个session（在杀进程时，有时SID会重用，造成误杀。而serial会增加但不会重复，sid 在同一个instance的当前session中是一个unique key，而sid ,serial\#则是在整个instance生命期内的所有session中是unique key）；
 *  SERIAL\#：会话序列号，用于在一个会话结束而另一个会话重用这该会话的SID时，唯一确定一个会话；
 *  AUDSID：审计会话ID，可以通过audsid查询当前session的sid，select sid from v$session where audsid=userenv('sessionid')；
 *  PADDR：进程地址，关联v$process的addr字段，通过这个可以查询到进程对应的session；
 *  USER\#：同于dba\_users中的user\_id，Oracle内部进程user\#为0；
 *  USERNAME：会话拥有者用户名，等于dba\_users中的username，Oracle内部进程的username为空；
 *  COMMAND：正在执行的SQL语句类型，如1为create table、3为select等；
 *  OWNERID：如果该列值为2147483644则值无效，否则值用于会话迁移、并行等；
 *  TADDR：Address of transaction state object；
 *  LOCKWAIT：标识当前查询是否处于锁等待状态，为空则表示无等待；
 *  STATUS：标识session状态，Active正执行SQL语句，inactive等待操作，killed被标注为杀死；
 *  SERVER：服务器类型，DEDICATED专用、SHARED共享等；
 *  SCHEMA\#：SCHEMA标识ID值，Oracle内部进程的schema\#为0；
 *  SCHEMANAME：SCHEMA用户名，Oracle内部进程的为sys；
 *  OSUSER：客户端操作系统用户名；
 *  PROCESS：客户端操作系统进程ID；
 *  MACHINE：操作系统机器名；
 *  TERMINAL：操作系统终端名；
 *  PROGRAM：操作系统应用程序名，如EXE或sqlplus.exe；
 *  TYPE：会话类型，如BACKGROUND或USER；
 *  SQL\_ADDRESS：和SQL\_HASH\_VALUE一起使用标识正在执行的SQL语句；
 *  SQL\_HASH\_VALUE：和SQL\_ADDRESS一起使用标识正在执行的SQL语句；
 *  SQL\_ID：正在执行的SQL语句的标识ID；
 *  SQL\_CHILD\_NUMBER：正在执行的SQL语句的子ID；
 *  FIXED\_TABLE\_SEQUENCE：当session完成一个user call后就会增加的一个数值，也就是说，如果session挂起，它就不会增加。因此可以根据这个字段来监控某个时间点以来的session性能情况。例如，一个小时前某个session的此字段数值为10000，而现在是20000，则表明一个小时内其user call较频繁，可以重点关注此session的performance statistics。
 *  ROW\_WAIT\_OBJ\#：被锁定行所在table的object\_id，和dba\_object中的object\_id关联可以得到被锁定的table name；
 *  ROW\_WAIT\_FILE\#：被锁定行所在的datafile id，和v$datafile中的file\#关联可以得到datafile name；
 *  ROW\_WAIT\_BLOCK\#：被锁定的块ID；
 *  ROW\_WAIT\_ROW\#：被锁定的当前行；
 *  LOGON\_TIME：登录时间；

[Link 1]: http://www.ecdoer.com/post/oracle-highcost-sql-locate.html#title-0
[SQL]: http://www.ecdoer.com/post/oracle-highcost-sql-locate.html#title-1
[Link 2]: http://www.ecdoer.com/post/oracle-highcost-sql-locate.html#title-2