分类: Oracle

Oracle查看执行最慢与查询次数、高消耗资源最多的sql语句

在ORACLE数据库应用调优中,一个SQL的执行次数/频率也是常常需要关注的,因为某个SQL执行太频繁,要么是由于应用设计有缺陷,需要在业务逻辑上做出优化处理,要么是业务特殊性所导致。如果执行频繁的SQL,往往容易遭遇一些并发性的问题:
查询执行最慢的sql

select *
 from (select sa.SQL_TEXT,
        sa.SQL_FULLTEXT,
        sa.EXECUTIONS "执行次数",
        round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
        round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
        sa.COMMAND_TYPE,
        sa.PARSING_USER_ID "用户ID",
        u.username "用户名",
        sa.HASH_VALUE
     from v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum <= 50;

查询次数最多的 sql

select *
 from (select s.SQL_TEXT,
        s.EXECUTIONS "执行次数",
        s.PARSING_USER_ID "用户ID",
        rank() over(order by EXECUTIONS desc) EXEC_RANK
     from v$sql s
     left join all_users u
      on u.USER_ID = s.PARSING_USER_ID) t
 where exec_rank <= 100;

根据用户ID查询用户名:

select USERNAME from dba_users where USER_ID = '89'; 

读硬盘多或占用内存可能多的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来查看分析。
查看排序次数最多的SQL

select sql_text, sorts, parsing_schema_name
from v$sqlarea
order by sorts desc;

V$SQL中的列说明:

SQL_TEXT:SQL文本的前1000个字符 
SHARABLE_MEM:占用的共享内存大小(单位:byte) 
PERSISTENT_MEM:生命期内的固定内存大小(单位:byte) 
RUNTIME_MEM:执行期内的固定内存大小 
SORTS:完成的排序数 
LOADED_VERSIONS:显示上下文堆是否载入,1是0否 
OPEN_VERSIONS:显示子游标是否被锁,1是0否 
USERS_OPENING:执行语句的用户数 
FETCHES:SQL语句的fetch数。 
EXECUTIONS:自它被载入缓存库后的执行次数 
USERS_EXECUTING:执行语句的用户数 
LOADS:对象被载入过的次数 
FIRST_LOAD_TIME:初次载入时间 
INVALIDATIONS:无效的次数 
PARSE_CALLS:解析调用次数 
DISK_READS:读磁盘次数 
BUFFER_GETS:读缓存区次数 
ROWS_PROCESSED:解析SQL语句返回的总列数 
COMMAND_TYPE:命令类型代号 
OPTIMIZER_MODE:SQL语句的优化器模型 
OPTIMIZER_COST:优化器给出的本次查询成本 
PARSING_USER_ID:第一个解析的用户ID 
PARSING_SCHEMA_ID:第一个解析的计划ID 
KEPT_VERSIONS:指出是否当前子游标被使用DBMS_SHARED_POOL包标记为常驻内存 
ADDRESS:当前游标父句柄地址 
TYPE_CHK_HEAP:当前堆类型检查说明 
HASH_VALUE:缓存库中父语句的Hash值 
PLAN_HASH_VALUE:数值表示的执行计划。 
CHILD_NUMBER:子游标数量 
MODULE:在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。 
ACTION:在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。 
SERIALIZABLE_ABORTS:事务未能序列化次数 
OUTLINE_CATEGORY:如果outline在解释cursor期间被应用,那么本列将显示出outline各类,否则本列为空 
CPU_TIME:解析/执行/取得等CPU使用时间(单位,毫秒) 
ELAPSED_TIME:解析/执行/取得等消耗时间(单位,毫秒) 
OUTLINE_SID:outline session标识 
CHILD_ADDRESS:子游标地址 
SQLTYPE:指出当前语句使用的SQL语言版本 
REMOTE:指出是否游标是一个远程映象(Y/N) 
OBJECT_STATUS:对象状态(VALID or INVALID) 
IS_OBSOLETE:当子游标的数量太多的时候,指出游标是否被废弃(Y/N) 

v$sqlarea常用字段说明

 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:登录时间;

相关文章

发表新评论