跟踪oracle中sql语句执行过程及相关知识拓展

  1. 云栖社区>
  2. 博客列表>
  3. 正文

跟踪oracle中sql语句执行过程及相关知识拓展

衣舞晨风 2014-09-02 09:39:00 浏览629 评论0

摘要: select * from v$sqlarea; select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00';        这个方法查询结果每条记录显示一条查询语句,且只能查询sql_text小于1000字符的,多余的会被截断。         改进一下:select * from v$sqlare

select * from v$sqlarea;

select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00';
        这个方法查询结果每条记录显示一条查询语句,且只能查询sql_text小于1000字符的,多余的会被截断。
        改进一下:
select * from v$sqlarea where first_load_time>'2010-11-27/09:30:00'  and sql_text like 'insert%';  //可以进一步筛选
        如果要显示多余1000的用fulltext.

知识拓展:

       1、V$SQLTEXT

        本视图包括 Shared pool中 SQL语句的完整文本,一条 SQL语句可能分成多个块被保存于多个记录内。
  注: V$SQLAREA只包括头 1000个字符。

V$SQLTEXT中的常用列
l         HASH_VALUE: SQL语句的 Hash值
l         ADDRESS: sql语句在 SGA中的地址
l         SQL_TEXT: SQL文本。
l         PIECE: SQL语句块的序号

V$SQLTEXT中的连接列:

Column                                          View                                        Joined Column(s)

HASH_VALUE, ADDRESS         V$SQL, V$SESSION            HASH_VALUE, ADDRESS
HASH_VALUE. ADDRESS         V$SESSION                          SQL_HASH_VALUE, SQL_ADDRESS

        示例:已知 hash_value:3111103299,查询 sql语句:
select * from v$sqltext
where hashvalue= '3111103299'
order by piece
        2、V$SQLAREA

       本视图持续跟踪所有 shared pool中的共享 cursor,在 shared pool中的每一条 SQL语句都对应一列。本视图在分析 SQL语句资源使用方面非常重要。

V$SQLAREA中的信息列

l         HASH_VALUE: SQL语句的 Hash值。
l         ADDRESS: SQL语句在 SGA中的地址。
这两列被用于鉴别 SQL语句,有时,两条不同的语句可能 hash值相同。这时候,必须连同 ADDRESS一同使用来确认 SQL语句。
l         PARSING_USER_ID:为语句解析第一条 CURSOR的用户
l         VERSION_COUNT:语句 cursor的数量
l         KEPT_VERSIONS:
l         SHARABLE_MEM: cursor使用的共享内存总数
l         PERSISTENT_MEM: cursor使用的常驻内存总数
l         RUNTIME_MEM: cursor使用的运行时内存总数。
l         SQL_TEXT: SQL语句的文本(最大只能保存该语句的前 1000个字符)。
l         MODULE,ACTION:使用了 DBMS_APPLICATION_INFO时 session解析第一条 cursor时的信息
V$SQLAREA中的其它常用列
l         SORTS: 语句的排序数
l         CPU_TIME: 语句被解析和执行的 CPU时间
l         ELAPSED_TIME: 语句被解析和执行的共用时间
l         PARSE_CALLS: 语句的解析调用 (软、硬 )次数
l         EXECUTIONS: 语句的执行次数
l         INVALIDATIONS: 语句的 cursor失效次数
l         LOADS: 语句载入 (载出 )数量
l         ROWS_PROCESSED: 语句返回的列总数

V$SQLAREA中的连接列
Column                                      View                                                                      Joined Column(s)
HASH_VALUE, ADDRESS     V$SESSION                                                         SQL_HASH_VALUE, SQL_ADDRESS
HASH_VALUE, ADDRESS     V$SQLTEXT, V$SQL, V$OPEN_CURSOR     HASH_VALUE, ADDRESS
SQL_TEXT                                V$DB_OBJECT_CACHE                                    NAME

示例:
        A.查看消耗资源最多的 SQL:

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 ;
       B、查看某条 SQL 语句的资源消耗:
SELECT hash_value, buffer_gets, disk_reads, executions, parse_calls
FROM V$SQLAREA
WHERE hash_Value = 228801498 AND address = hextoraw( 'CBD8E4B0' );
      C、查找前10条性能差的sql语句:
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ;  
SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM<10 ;
        EXECUTIONS表示同一条SQL语句一共执行了多少次,SORTS表示排序的次数,DISK_READS表示物理读的数量。

        3、v$sql

        一条语句可以映射多个 cursor,因为对象所指的 cursor可以有不同用户 (如例 1)。如果有多个 cursor(子游标 )存在,在 V$SQLAREA为所有 cursor提供集合信息。

       例 1:这里介绍以下 child cursor

user A: select * from tbl
user B: select * from tbl
       大家认为这两条语句是不是一样的啊,可能会有很多人会说是一样的,但我告诉你不一定,那为什么呢?
       这个 tblA看起来是一样的,但是不一定哦,一个是 A用户的 , 一个是 B用户的,这时他们的执行计划分析代码差别可能就大了哦,改下写法大家就明白了 :
select * from A.tbl
select * from B.tbl
        在个别 cursor上, v$sql可被使用。该视图包含 cursor级别资料。当试图定位 session或用户以分析 cursor时被使用。
  PLAN_HASH_VALUE列存储的是数值表示的 cursor执行计划。可被用来对比执行计划。 PLAN_HASH_VALUE让你不必一行一行对比即可轻松鉴别两条执行计划是否相同。

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

原文地址:
http://hi.baidu.com/clebean/item/73297be5da8cba0e8d3ea8e7
http://blog.sina.com.cn/s/blog_8019d3c10100rte3.html

用云栖社区APP,舒服~

【云栖快讯】诚邀你用自己的技术能力来用心回答每一个问题,通过回答传承技术知识、经验、心得,问答专家期待你加入!  详情请点击

网友评论

衣舞晨风
文章787篇 | 关注4
关注
云数据库PPAS版,是阿里云与EnterpriseDB公司合作基于PostgreSQL高度兼... 查看详情
PostgreSQL被业界誉为“最先进的开源数据库”,面向企业复杂SQL处理的OLTP在线事... 查看详情
RDS是一种稳定可靠、可弹性伸缩的在线数据库服务。支持MySQL、SQL Server、Po... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
阿里云总监课正式启航

阿里云总监课正式启航