Oracle诊断案例-Sql

oracle

link:

http://www.eygle.com/case/sql_trace_1.htm

问题描述:

这是帮助一个公司的诊断案例.
应用是一个后台新闻发布系统.

症状是,通过连接访问新闻页是极其缓慢
通常需要十数秒才能返回.

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

操作系统: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 IFLOW10 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> 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 IFLOW10 rows selected.

等候一段时间,关闭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文件

检查发现以下语句是可疑的

 

********************************************************************************select auditstatus,categoryid,auditlevel from categoryarticleassign a,category b where b.id=a.categoryid and articleId= 20030700400141 and auditstatus>0call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.81 0.81 0 3892 0 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.81 0.81 0 3892 0 1********************************************************************************

这里显然是根据articleId进行新闻读取的.
很可疑的是query读取有3892

这个内容引起了我的注意.
如果遇到过类似的问题,大家在这里就应该知道是怎么回事情了.
如果没有遇到过的朋友,可以在这里思考一下再往下看.

Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 41 Rows Row Source Operation------- --------------------------------------------------- 1 NESTED LOOPS 2 INDEX RANGE SCAN (object id 25062) 1 TABLE ACCESS BY INDEX ROWID CATEGORY 2 INDEX UNIQUE SCAN (object id 25057)********************************************************************************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 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 4.91 4.91 0 2835 7 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 4.91 4.91 0 2835 7 1Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 41 Rows Row Source Operation------- --------------------------------------------------- 1 'TABLE ACCESS FULL CATEGORYARTICLEASSIGN'我们注意到,这里有一个全表扫描存在********************************************************************************

 

3.登陆数据库,检查相应表结构

 

SQL> select index_name,table_name,column_name from user_ind_columns 2 where table_name=upper('categoryarticleassign');INDEX_NAME TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ -------------------- IDX_ARTICLEID CATEGORYARTICLEASSIGN ARTICLEIDIND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN ARTICLEID IND_ARTICLEID_CATEG CATEGORYARTICLEASSIGN CATEGORYID IDX_SORTID CATEGORYARTICLEASSIGN SORTID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ARTICLEID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN CATEGORYID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGN ASSIGNTYPE IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN AUDITSTATUS IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ARTICLEID IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN CATEGORYID IDX_CAT_ARTICLE CATEGORYARTICLEASSIGN ASSIGNTYPE 11 rows selected.

我们注意到,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.62Execution 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
几乎不需要花费CPU时间了

 ********************************************************************************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 0Execute 1 0.00 0.00 0 0 0 0Fetch 1 0.00 0.00 0 2 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3 0.00 0.00 0 2 0 0Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 20 Rows Row Source Operation------- --------------------------------------------------- 0 TABLE ACCESS BY INDEX ROWID CATEGORYARTICLEASSIGN 1 INDEX RANGE SCAN (object id 3080)********************************************************************************

 

 

至此,这个问题得到了完满的解决.

 

时间: 2016-02-09

Oracle诊断案例-Sql的相关文章

Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题

oracle|解决|问题 Oracle诊断案例----如何捕获问题SQL解决过度CPU消耗问题 --使用vmstat,top等辅助解决Oracle数据库性能问题 Last Updated: Sunday, 2004-10-24 0:37 Eygle       问题描述:开发人员报告系统运行缓慢,影响用户访问. 1.登陆数据库主机 使用vmstat检查,发现CPU资源已经耗尽,大量任务位于运行队列: bash-2.03$ vmstat 3 procs memory page disk fault

Oracle诊断案例-Job任务停止执行

oracle|执行 Oracle诊断案例-Job任务停止执行 Last Updated: Saturday, 2004-11-20 12:47 Eygle         昨天接到研发人员报告,数据库定时任务未正常执行,导致某些操作失败. 开始介入处理该事故.系统环境:SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4 Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

Oracle诊断案例-Job任务停止执行[最终版]

oracle|执行 Oracle诊断案例-Job任务停止执行 Last Updated: Friday, 2004-11-26 9:48 Eygle         昨天接到研发人员报告,数据库定时任务未正常执行,导致某些操作失败. 开始介入处理该事故.系统环境:SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4 Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production 1.首

Oracle诊断案例-Spfile案例一则

oracle Oracle诊断案例-Spfile案例一则   link: http://www.eygle.com/case/spfile.htm 情况说明:系统:SUN Solaris8数据库版本:9203问题描述:工程人员报告,数据库在重新启动时无法正常启动.检查发现UNDO表空间丢失.问题诊断及解决过程如下:   1. 登陆系统检查alert.log文件 检查alert.log文件是通常是我们诊断数据库问题的第一步 SunOS 5.8 login: rootPassword: Last l

Oracle诊断案例-SGA与Swap之一

oracle     link: http://www.eygle.com/case/sga1.htm 案例描述: 用户报告,服务器启动一段时间以后,无法建立数据库连接重新启动几分钟以后,再次无法连接 系统无法正常使用. 1.登陆系统 SunOS 5.8 login: rootPassword: Last login: Tue Mar 23 13:56:59 from 172.16.31.41Sun Microsystems Inc. SunOS 5.8 Generic Patch Octobe

Oracle诊断案例:Job任务停止执行

摘要: 本文通过一次Oracle Job任务异常案例诊断,分析其原因及解决过程,从内部揭示Oracle Job任务调度及内部计时机制. 问题及环境 接到研发人员报告,数据库定时任务未正常执行,导致某些操作失败. 开始介入处理该事故. 系统环境: 以下为引用的内容: SunOS DB 5.8 Generic_108528-21 sun4u sparc SUNW,Ultra-4Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production 解决过

Oracle诊断案例-SGA与Swap之二

oracle     link: http://www.eygle.com/case/sga2.htm 案例描述: 这是一个大型生产系统问题出现时系统累计大量用户进程用户请求得不到及时响应,新的进程不断尝试建立连接连接数很快被用完 数据库版本:9.2.0.3操作系统:Solaris8   1.检查alert文件 日志中记录如下错误信息,说明磁盘异步IO出现问题:   WARNING: aiowait timed out 2 timesTue Aug 26 15:33:32 2003WARNING

《Oracle性能优化与诊断案例精选》导读

前言 Oracle性能优化与诊断案例精选数据驱动,成就未来最近两年来,很多朋友经常会问我,接下来会不会继续写书,会写一本什么样的书. 其实我也一直在思考,什么样的作品能够以最小的篇幅带来超越时间的价值,尽可能地帮助那些准备进入和刚刚进入这个领域的广大技术人员. 本书缘起2015年年底,我在成都和老熊聊天的时候,忽然有了一个想法,如果我能够将云和恩墨的专家团队聚集起来,让每个人都把自己最宝贵的经验方法.经典案例呈现出来,那累计超过100年的从业经验一定可以帮助很多人更深层次地了解数据库技术. 于是

《Oracle性能优化与诊断案例精选》——第2章 回首向来萧瑟处,也无风雨也无晴

第2章 回首向来萧瑟处,也无风雨也无晴 Oracle性能优化与诊断案例精选--我的十年Oracle DBA奋斗路(侯圣文) 题记 迄今为止,我觉得这辈子最幸运的两件事,一件是遇见了我太太,另一件就是结识了Oracle.没有早一步也没有晚一步,刚巧赶上了,在最适合谈恋爱的年纪谈了一场没有分手的恋爱,在最适合干事业的年纪做了一份不曾放弃的事业.