Oracle诊断案例-Sql_trace

问题描述 :

这是帮助一个公司的诊断案例 .

应用是一个后台新闻发布系统 .

症状是 , 通过连接访问新闻页是极其缓慢

通常需要十数秒才能返回 .

这种性能是用户不能忍受的 .

操作系统 :SunOS 5.8

数据库版本 :8.1.7

1. 检查并跟踪数据库进程

诊断时是晚上 , 无用户访问

在前台点击相关页面 , 同时进行进程跟踪

查询 v$session 视图 , 获取进程信息

代码 : --------------------------------------------------------------------------------  

SQL> select sid,serial#,username from v$session;

       SID    SERIAL# USERNAME

---------- ---------- ------------------------------

         1          1

         2          1

         3          1

         4          1

         5          1

         6          1

         7        284 IFLOW

        11        214 IFLOW

        12        164 SYS

        16       1042 IFLOW

10 rows selected.

--------------------------------------------------------------------------------

 　  启用相关进程 sql_trace   代码 :   --------------------------------------------------------------------------------    

SQL> exec dbms_system.set_sql_trace_in_session(7,284,true)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(11,214,true)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(16,1042,true)

PL/SQL procedure successfully completed.

SQL>

等候一段时间 , 关闭 sql_trace  

SQL> exec dbms_system.set_sql_trace_in_session(7,284,false)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(11,214,false)

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_sql_trace_in_session(16,1042,false)

PL/SQL procedure successfully completed.   　2. 检查 trace 文件   检查发现以下语句是可疑的   ********************************************************************************  

s elect auditstatus,categoryid,auditlevel

from

 categoryarticleassign a,category b where b.id=a.categoryid and articleId=

  20030700400141 and auditstatus>0

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.81       0.81          0       3892          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.81       0.81          0       '3892'          0       

--------------------------------------------------------------------------------  

这里显然是根据 articleId 进行新闻读取的 .

很可疑的是 query 读取有 3892     

select auditstatus,categoryid

from

 categoryarticleassign where articleId=20030700400138 and categoryId in ('63',

  '138','139','140','141','142','143','144','168','213','292','341','346',

  '347','348','349','350','351','352','353','354','355','356','357','358',

  '359','360','361','362','363','364','365','366','367','368','369','370',

  '371','372','383','460','461','462','463','621','622','626','629','631',

  '634','636','643','802','837','838','849','850','851','852','853','854',

  '858','859','860','861','862','863','-1')

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      4.91       4.91          0       2835          7           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      4.91       4.91          0       2835          7           1

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 41 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1 'TABLE ACCESS FULL CATEGORYARTICLEASSIGN'

  我们注意到，这里有一个全表扫描存在    　3. 登陆数据库 , 检查相应表结构  　     

 　   我们注意到 ,IDX_ARTICLEID 索引在以上查询中都没有被用到 .   检查表结构 :   

SQL> desc categoryarticleassign

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 CATEGORYID                                NOT NULL NUMBER

'ARTICLEID                                 NOT NULL VARCHAR2(14)'

 ASSIGNTYPE                                NOT NULL VARCHAR2(1)

 AUDITSTATUS                               NOT NULL NUMBER

 SORTID                                    NOT NULL NUMBER

 UNPASS                                             VARCHAR2(255)

问题发现 :

因为 ARTICLEID 是个字符型数据 , 查询中给入的 articleId= 20030700400141 是一个数字值

Oracle 发生潜在的数据类型转换 , 从而导致了索引失效

　  代码: --------------------------------------------------------------------------------  

SQL>  select auditstatus,categoryid

  2   from

  3    categoryarticleassign where articleId=20030700400132;

AUDITSTATUS CATEGORYID                                    

----------- ----------                                    

          9         94                                    

          0        383                                     

          0        695                                    

Elapsed: 00:00:02.62

Execution Plan

----------------------------------------------------------                

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=110 Card=2 Bytes=38)   

   1    0   TABLE ACCESS (FULL) OF 'CATEGORYARTICLEASSIGN' (Cost=110 Card=2 Bytes=38)

-------------------------------------------------------------------------------- 　4. 解决方法   简单的在参数两侧各增加一个 ', 既可解决这个问题 .   对于类似的查询 , 我们发现 Query 模式读取降低为 2  　   代码 :   --------------------------------------------------------------------------------  ******************************************************************************** 

select unpass

from

 categoryarticleassign where articleid='20030320000682' and categoryid='113'

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      0.00       0.00          0          2          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      0.00       0.00          0          2          0           0

Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 20 

Rows     Row Source Operation

-------  ---------------------------------------------------

      0  TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN

      1   INDEX RANGE SCAN (object id 3080)

********************************************************************************

--------------------------------------------------------------------------------

  至此 , 这个问题得到了完满的解决 .   5. 总结   在 Oracle 开发中 , 我们应该尽量避免使用隐式的数据类型转换 因为隐式数据类型转换可能会带来索引失效的问题 .   这些问题，在开发阶段就应该被避免 .   使用函数导致索引失效的问题与此类似 .