链接: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”
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
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
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
等
select
、
insert
等
原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/7192.html