oracle 中如何定位重要(消耗资源多)的SQL - bolang100

链接：http://www.xifenfei.com/699.html

1、查看值得怀疑的SQL

select  substr(to_char(s.pct,'99.00'),2)||'%'load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where  s.ranking<=5
  and p.address=s.address
order  by 1, s.address, p.piece;

2、查看消耗内存多的sql

select  b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by disk_reads desc;

3、查看逻辑读多的SQL

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where  rownum<=30;

4、查看执行次数多的SQL

select  sql_text, executions
  from (select  sql_text, executions from  v$sqlarea order  by executions desc)
 where rownum < 81;

5、查看读硬盘多的SQL

select  sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order  by disk_reads desc)
where  rownum<21;

6、查看排序多的SQL

select  sql_text, sorts
from(select sql_text, sorts from v$sqlarea order  by sorts desc)
where  rownum<21;

7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

select  substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"
  from v$sqlarea
 where executions < 5
 group by substr(sql_text, 1, 80)
having  count(*) > 30
 order by 2;
定位重要（消耗资源多）的SQL语句  
 1、statspack-- 在你库上业务最忙得时候抓15分钟的report,看里面的top sql 1 top找到消耗资源多的pid

 
2 确定是oracle的应用进程还是后台进程

 
3 根据v$session,v$process,v$sqlarea定位

 
用oem带的top session  www.2cto.com  

 
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls

 
FROM V$SQLAREA

 
WHERE buffer_gets > 10000000 OR disk_reads > 1000000
ORDER BY buffer_gets + 100 * disk_reads DESC
v$sql，v$sqlarea，v$sqltext有什么区别 


共同点：
1）都存储了sql内容
2) 记录的都是位于内存中的sql内容
3) 因为是内存，所以都不保留历史记录
不同点：
1)存储的为止不都是相同。其中v$sql和v$sqlarea存储的sql都是位于shared sql area中的sql,而v$sqltext是位于sga中的sql。但文档没有明确说明这里的sga是否还包含了psa(私有sql区域－－共享服务器模式下）。
2)存储sql的方式也不同，v$sql和v$sqlarea都是用一行来存储sql全文，而v$sqltext用一行存储sql的一行。
3)v$sql不存储包含group by 的sql语句。通常这个视图，在每个查询执行完成后更新，但对于执行很久的sql,它是每5秒更新一次，这点对于查看sql执行状态是有意义的。
4)存储的明细不同－－这是最基本的。
V$SQL 在子游标级别上列出了在共享sql区域的统计信息，他将原始sql文本展现为一行。V$SQL中的视图信息一般在sql执行的最后进行更新。然而，对于长 时间执行的sql，每5秒会更新一次v$sql视图。这使得很容易查看长时间执行的sql在运行过程中带来的影响。
v$sql列说明，如没有特别说明，均指子游标，存储的是具体的SQL 和执行计划相关信息，实际上，v$sqlarea 可以看做 v$sql 根据 sqltext 等 做了 group by 之后的信息
SQL> desc v$sql 
Name Null? Type 
----------------------------------------- -------- ----------------------------


 


SQL_TEXT           //当前正在执行的游标的sql文本的前1000个字符
SQL_FULLTEXT       //CLOB类型 整个sql文本，不用借助于V$SQL_TEXT视图来查看整个文本
SQL_ID           //库缓存中的SQL父游标的标志
SHARABLE_MEM           //子游标使用的共享内存的大小,bytes
PERSISTENT_MEM           //子游标生存时间中使用的固定内存的总量,bytes
RUNTIME_MEM           //在子游标执行过程中需要的固定内存大小，bytes
SORTS           //子游标发生的排序数量
LOADED_VERSIONS          // 显示上下文堆是否载入，1是，0否
USERS_OPENING          // 执行这个sql的用户数
FETCHES          // sql取数据的次数
EXECUTIONS           //自从被载入共享池后，sql执行的次数 
FIRST_LOAD_TIME          // 父游标产生的时间戳
PARSE_CALLS           //解析调用的次数 
DISK_CALLS             //读磁盘的次数
DIRECT_WRITES           //直接写的次数
BUFFER_GETS           //直接从buffer中得到数据的次数
APPLICATION_WAIT_TIME          // 应用等待时间，毫秒
CONCURRENCY_WAIT_TIME           //并发等待时间，毫秒
USER_IO_WAIT_TIME           //用户IO等待时间
ROWS_PROCESSED SQL           //解析sql返回的总行数
OPTIMIZER_MODE           //优化器模式
OPTIMIZER_COST           //优化器对于sql给出的成本
PARSING_USER_ID           //第一个创建这个子游标的用户id
HASH_VALUES           //解析产生的哈希值
CHILD_NUMBER           //该子游标的数量
SERVICE           //服务名
CPU_TIME           //该子游标解析，执行和获取数据使用的CPU时间，毫秒
ELAPSED_TIME           //sql的执行时间，毫秒
INVALIDATIONS           //该子游标的无效次数
MODULE       //第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的模块名
ACTION      //第一次解析该语句时,通过DBMS_APPLICAITON_INFO.SET_ACTION设置的动作名 
IS_OBSOLETE           //标记该子游标过期与否，当子游标过大时会发生这种情况
is_bind_sensitive           //不仅指出是否使用绑定变量窥测来生成执行计划，而且指出这个执行计划是否依赖于窥测到的值。如果是，这个字段会被设置为Y，否则会被设置为N。
is_bind_aware           //表明游标是否使用了扩展的游标共享。如果是，这个字段会被设置为Y，如果不是，这个字段会被设置为N。如果是设置为N，这个游标将被废弃，不再可用。
is_shareable           //表明游标能否被共享。如果可以，这个字段会被设置为Y，否则，会被设置为N。如果被设置为N，这个游标将被废弃，不再可用。


 v$sqlarea的字段定义和v$sql基本一致，不同的是V$SQLAREA是在父游标级别上统计的sql信息，v$sql的汇总表，进行了group by hash_value,sql_id的汇总。


SQL> desc v$sqlarea 
Name Null? Type
SQL_TEXT VARCHAR2(1000) 
SHARABLE_MEM NUMBER 
PERSISTENT_MEM NUMBER 
RUNTIME_MEM NUMBER 
SORTS NUMBER 
VERSION_COUNT NUMBER 
LOADED_VERSIONS NUMBER 
OPEN_VERSIONS NUMBER 
USERS_OPENING NUMBER 
FETCHES NUMBER 
EXECUTIONS NUMBER 
USERS_EXECUTING NUMBER 
LOADS NUMBER 
FIRST_LOAD_TIME VARCHAR2(38) 
INVALIDATIONS NUMBER 
PARSE_CALLS NUMBER 
DISK_READS NUMBER 
BUFFER_GETS NUMBER 
ROWS_PROCESSED NUMBER 
COMMAND_TYPE NUMBER 
OPTIMIZER_MODE VARCHAR2(25) 
PARSING_USER_ID NUMBER 
PARSING_SCHEMA_ID NUMBER 
KEPT_VERSIONS NUMBER 
ADDRESS RAW(4) 
HASH_VALUE NUMBER 
MODULE VARCHAR2(64) 
MODULE_HASH NUMBER 
ACTION VARCHAR2(64) 
ACTION_HASH NUMBER 
SERIALIZABLE_ABORTS NUMBER 
CPU_TIME NUMBER 
ELAPSED_TIME NUMBER 
IS_OBSOLETE VARCHAR2(1) 
CHILD_LATCH NUMBER


 
v$sqltext
本视图包括Shared pool中SQL语句的完整文本，一条SQL语句可能分成多个块被保存于多个记录内。
注：V$SQLAREA和v$sql中的SQL_TEXT字段只包括头1000个字符， SQL_FULLTEXT以CLOB方式包含了所有的字符
V$SQLTEXT列说明
HASH_VALUE           SQL语句的Hash值
ADDRESS           sql语句在SGA中的地址
SQL_TEXT           SQL文本。
PIECE           SQL语句块的序号
SQL_ID           SQL id
COMMAND_TYPE             命令类型，如select、insert等

发表于 2016-11-07 20:28 bolang100 阅读(1698) 评论(0) 收藏举报