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：登录时间；
Tag标签:「查询 oracle 最慢 资源」更新时间:「2021-11-03 16:52:05」阅读次数:「2207」