![](/upload/ad_content/xuanchuantu-28.jpg)
创新互联公司主要业务有网站营销策划、做网站、网站制作、微信公众号开发、小程序制作、H5页面制作、程序开发等业务。一次合作终身朋友,是我们奉行的宗旨;我们不仅仅把客户当客户,还把客户视为我们的合作伙伴,在开展业务的过程中,公司还积累了丰富的行业经验、成都全网营销资源和合作伙伴关系资源,并逐渐建立起规范的客户服务和保障体系。
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区(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:登录时间;
本文标题:Oracle高资源消耗SQL语句定位
文章源于:
http://bjjierui.cn/article/gicgsj.html