邮箱：lhb_immortal@hotmail.com

正在学习oracle中，如果有什么不对的地方，请牛大大们多多指正！

目录：

   

   1. TOPSQL 成因

     2. 查找TOPSQL信息

        2.1 按 disk_reads 的次数找 topsql信息

     2.2 按event waits 查找  topsql 信息

       2.3 按buffer_gets 查询 topsql 信息

    2.4 按 parse_calls 查询 topsql 信息

    2.5按sharable_mem 查询 topsql 信息

3.查看SQL 执行计划

正文：

1.Topsql成因：

       产生top sql 的原因 , 细化起来有以下原因 :         1 表结构设计的不合理         2 索引创建不合理         3 不必要的表关联         4 隐式数据类型转换导致没走索引         5 使用 table 类型的函数而没有指定合适的 hints                   6 不使用绑定变量         7 表与索引的统计信息陈旧 

2. 查找TOPSQL信息

 2.1  按 disk_reads 的次数找 topsql信息

SELECT b.username username,a.sql_id, a.executions exec, a.buffer_gets buffer, a.disk_reads disk,

 a.parse_calls parse,a.sql_text statement

  FROM V$SQLAREA a, dba_users b

 WHERE a.parsing_user_id=b.user_id and ( a.buffer_gets > 10000000 OR a.disk_reads > 1000000 )

 ORDER BY  a.buffer_gets + 100 *  a.disk_reads DESC;

也可以将v$sqlarea 换成 v$sql ，再增加一个 child_number 字段，就可以找到子游标 在 10.2 的版本中推荐在 v$sqlstats 找

        

 2.2  按event waits 查找  topsql 信息

SELECT EVENT, 

         SUM(P3) SLEEPS,

         SUM(SECONDS_IN_WAIT) SECONDS_IN_WAIT

FROM    V$SESSION_WAIT

WHERE EVENT LIKE 'latch%'

GROUP BY EVENT;

 

select inst_id,

      event,

      count(1) 

from gv$session_wait  

where event not in ('SQL*Net message to client','rdbms ipc message','SQL*Net message from client','smon timer') 

group by inst_id,event;

 

 

 SELECT   /*+ ordered */         hash_value,sql_text

    FROM v$sqltext a

   WHERE (a.hash_value, a.address) IN (

            SELECT DECODE (sql_hash_value,

                           0, prev_hash_value,

                           sql_hash_value

                          ),

                   DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)

              FROM v$session b

WHERE b.sid  in (select sid from v$session_wait where event = 'db file%read' ))

ORDER BY hash_value,piece ASC

 

  

  2.3 按buffer_gets 查询 topsql 信息

SELECT * FROM (SELECT substr(sql_text,1,40) sql,         buffer_gets, executions, buffer_gets/executions "Gets/Exec",         hash_value,address    FROM V$SQLAREA   WHERE buffer_gets > 10000 ORDER BY buffer_gets DESC) WHERE rownum <= 10 ;

   

  2.4 按 parse_calls 查询 topsql 信息

SELECT * FROM (SELECT substr(sql_text,1,40) sql,         parse_calls, executions, hash_value,address    FROM V$SQLAREA   WHERE parse_calls > 1000 ORDER BY parse_calls DESC) WHERE rownum <= 10 ;

 

   2.5按sharable_mem 查询 topsql 信息

SELECT * FROM  (SELECT substr(sql_text,1,40) sql,         sharable_mem, executions, hash_value,address    FROM V$SQLAREA   WHERE sharable_mem > 1048576 ORDER BY sharable_mem DESC) WHERE rownum <= 10 ;

 

3.查看SQL 执行计划

方法1         -- 实时查询计划

 SQL>set autotrace on --打开自动分析统计，并显示 SQL 语句结果 ;

 SQL>set autotrace traceonly --打开自动分析统计，不显示 SQL 语句结果 ;

       当执行SQL 语句时，就会显示 SQL 语句的执行计划。

方法2         --explain plan 命令查询执行计划 ;

  首先建立plan table, 通过脚本执行：

 SQL>@?/rdbms/admin/utlxplan.sql

  然后使用explain plan for 语句解析 SQL 语句执行计划：

 SQL>explain plan for

 2 select [column1,column2，……， columnn] from table_name;

  Explained.

 SQL> select * from table(dbms_xplan.display) --查询刚刚解析的语句的执行计划 ;

方法3.        --SQL_TRACE 系统参数 + dbms_system.set_SQL_TRACE_in_session  

                包查询 ;

  1.设置参数或更改会话设置（ 对其他用户session 设置

                             通过DBMS_SYSTEM.SET_EV 系统包来实现 ）

    Pfile/spfile中加入 SQL_TRACE=TRUE

      或：

Alter session set SQL_TRACE=TRUE/FALSE;

  2.跟踪语句

   SQL>select sid,serial#,username from v$session where username='***';

   SQL>exec dbms_system.set_SQL_TRACE_in_session(sid,serial#,true)

                  --等待片刻，执行中断跟踪 SQL 命令。

   SQL>exec dbms_system.set_SQL_TRACE_IN_SESSION(sid,serial#,false);

  3.获得跟踪文件

  3.1 跟踪文件路径 --user_dump_dest 参数所指定的文件路径下。

      SQL>show parameter dump;

  3.2 格式化跟踪文件

       SQL>$tkprof

                 文件路径   /hsjf_ora_ 1026 .trc 

                 文件路径   /hsjf_ora_ 1026 .txt