Oracle高资源消耗SQL语句定位
Oracle高资源消耗SQL语句定位
http://www.ecdoer.com/post/oracle-highcost-sql-locate.html
Oracle SQL语句资源消耗监控最常用的系统视图有v$sql、v$sqlarea、v$sqltext和v$session。本文我们先了解这些视图的作用与区别，然后了解如何定位高资源消耗SQL语句，最后再了解一下各视图字段具体含义。
v$sql和v$sqlarea基本相同，记录了共享SQL区（sharepool）中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等确定）、锁等待相关信息（如所在表、文件、块、被锁行）等。
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
v$session
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
