oracle sql资源消耗相关视图：v$sql,v$sqlarea,v$sqltext

今天要验证一个Full table scan问题的patch，需要分析一下sql执行情况，用到了v$sqlarea视图，感觉这篇文章讲得挺明白，摘抄一部分做个读书笔记。

1       常用视图说明

Oracle sql语句资源消耗监控，最常用的系统视图有：

v$sql

v$sqlarea

v$sqltext

v$session

 

v$sql与v$sqlarea基本相同，记录共享sql区（share pool）中sql统计信息，如内存消耗、IO（物流磁盘读和逻辑内存读）、排序操作、哈希ID等数据。不同之处在于v$sql为每一条sql保留一个条目，而v$sqlarea中根据sql_text进行group by，统计列进行sum()，通过version_count计算子指针的个数。

         Sql_text相同的sql语句在数据库中意义可能完全不同，此时，v$sql会有这两条完全一样的sql各自的统计信息，而在v$sqlarea中sql_text相同的2个指针会合并，执行次数，DISK_READS，BUFFER_GETS等统计信息会累加，这就是v$sqlarea的聚合作用。

         v$sqltext中没有统计信息，却存储着完整的sql语言及其哈希ID等。

         v$session主要用来确定会话相关信息，如通过SID和SERIAL来确定一个Session、会话拥有者用户名username、会话状态、会话由哪个客户端发起、正在执行什么sql（通过sql_address、sql_hash_value、sql_id、sql_child_number确定，再借助v$sqltext就可以知道）、锁等待相关信息（如所在表、文件、块、被锁行）等。

        

查看视图原表：

SELECTview_definition FROM v$fixed_view_definition WHERE view_name='GV$SQL';

SELECTview_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLAREA';

SELECTview_definition FROM v$fixed_view_definition WHERE view_name='GV$SQLTEXT';

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

 

2       视图重要字段

2.1      v$sqlarea

sql_text: sql 语句的前1000个字符

sql_fulltext: sql语句的所有字符

sql_id：缓存在高速缓冲区中的sql父游标的唯一标识ID

sorts: 语句执行导致的排序次数

version_count：在缓存中以该语句为父语句的子游标总数

executions: 包含所有子游标在内该sql语句共执行次数

parse_calls:父游标下所有子游标解析调用次数

disk_reads: 该语句通过所有子游标导致的读磁盘次数

address：当前游标父句柄

hash_value: 该语句在library cache中的hash值

2.2      v$sqltext

address: 当前游标父句柄

hash_value:该游标在library cache中唯一hash值

3       高资源消耗sql定位

3.1      查看读硬盘多或占用内存可能多的sql

Selectsql_text,disk_reads,buffer_gets,parsing_scheme_name,executions

From v$sqlarea

Order by disk_reads desc

单纯从v$sqlarea 中无法查出每个sql消耗的内存量，但可以借助磁盘读次数间接反映可能的消耗内存较大的sql语句，然后借助执行计划（v$sql_plan）具体查看。

3.2      查看执行次数多的sql

Selectsql_text,executions,parsing_schema_name

From v$sqlarea

Order by executions desc;

3.3      查看排序多的sql

Select sql_text,sorts,parsing_schema_name

From v$sqlarea

Order by sorts desc;

 

摘自：http://www.ecdoer.com/post/oracle-highcost-sql-locate.html