link:http://www.eygle.com/case/sql_trace_1.htm問(wèn)題描述:這是幫助一個(gè)公司的診斷案例.應(yīng)用是一個(gè)后臺(tái)新聞發(fā)布系統(tǒng).癥狀是,通過(guò)連接訪問(wèn)新聞頁(yè)是極其緩慢通常需要十?dāng)?shù)秒才能返回. 這種性能是用戶不能忍受的.操作系統(tǒng):SunOS 5.8數(shù)據(jù)庫(kù)版本:8.1.71.檢查并跟蹤數(shù)據(jù)庫(kù)進(jìn)程 診斷時(shí)是晚上,無(wú)用戶訪問(wèn)在前臺(tái)點(diǎn)擊相關(guān)頁(yè)面,同時(shí)進(jìn)行進(jìn)程跟蹤查詢(xún)v$session視圖,獲取進(jìn)程信息SQL> select sid,serial#,username from v$session; SID SERIAL# USERNAME---------- ---------- ------------------------------ 11 21 31 41 51 61 7284 IFLOW11214 IFLOW12164 SYS16 1042 IFLOW10 rows selected. 啟用相關(guān)進(jìn)程sql_traceSQL> 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---------- ---------- ------------------------------ 11 21 31 41 51 61 7284 IFLOW11214 IFLOW12164 SYS16 1042 IFLOW10 rows selected.等候一段時(shí)間,關(guān)閉sql_traceSQL> 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文件檢查發(fā)現(xiàn)以下語(yǔ)句是可疑的********************************************************************************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 currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse1 0.00 0.00000 0Execute 1 0.00 0.00000 0Fetch1 0.81 0.810 38920 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total3 0.81 0.8103892 0 1******************************************************************************** 這里顯然是根據(jù)articleId進(jìn)行新聞讀取的.很可疑的是query讀取有3892這個(gè)內(nèi)容引起了我的注重.假如碰到過(guò)類(lèi)似的問(wèn)題，大家在這里就應(yīng)該知道是怎么回事情了.假如沒(méi)有碰到過(guò)的朋友，可以在這里思考一下再往下看.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 currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse1 0.00 0.00000 0Execute 1 0.00 0.00000 0Fetch1 4.91 4.910 28357 1------- ------ -------- ---------- ---------- ---------- ---------- ----------total3 4.91 4.910 28357 1Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 41 Rows Row Source Operation------- --------------------------------------------------- 1 'TABLE ACCESS FULL CATEGORYARTICLEASSIGN'我們注重到，這里有一個(gè)全表掃描存在********************************************************************************3.登陸數(shù)據(jù)庫(kù),檢查相應(yīng)表結(jié)構(gòu)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_ARTICLEIDCATEGORYARTICLEASSIGNARTICLEIDIND_ARTICLEID_CATEGCATEGORYARTICLEASSIGNARTICLEID IND_ARTICLEID_CATEGCATEGORYARTICLEASSIGNCATEGORYIDIDX_SORTID CATEGORYARTICLEASSIGNSORTID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGNARTICLEID PK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGNCATEGORYIDPK_CATEGORYARTICLEASSIGN CATEGORYARTICLEASSIGNASSIGNTYPEIDX_CAT_ARTICLE CATEGORYARTICLEASSIGNAUDITSTATUS IDX_CAT_ARTICLE CATEGORYARTICLEASSIGNARTICLEID IDX_CAT_ARTICLE CATEGORYARTICLEASSIGNCATEGORYIDIDX_CAT_ARTICLE CATEGORYARTICLEASSIGNASSIGNTYPE11 rows selected. 我們注重到,IDX_ARTICLEID索引在以上查詢(xún)中都沒(méi)有被用到.檢查表結(jié)構(gòu):SQL> desc categoryarticleassign NameNull? 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) 問(wèn)題發(fā)現(xiàn):因?yàn)锳RTICLEID是個(gè)字符型數(shù)據(jù),查詢(xún)中給入的articleId= 20030700400141 是一個(gè)數(shù)字值Oracle發(fā)生潛在的數(shù)據(jù)類(lèi)型轉(zhuǎn)換,從而導(dǎo)致了索引失效SQL> select auditstatus,categoryid 2 from 3 categoryarticleassign where articleId=20030700400132;AUDITSTATUS CATEGORYID ----------- ---------- 9 94 0383 0695 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.解決方法簡(jiǎn)單的在參數(shù)兩側(cè)各增加一個(gè)',既可解決這個(gè)問(wèn)題.對(duì)于類(lèi)似的查詢(xún),我們發(fā)現(xiàn)Query模式讀取降低為2幾乎不需要花費(fèi)CPU時(shí)間了********************************************************************************select unpass from categoryarticleassign where articleid='20030320000682' and categoryid='113' call count cpu elapsed disk query currentrows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse1 0.00 0.00000 0Execute 1 0.00 0.00000 0Fetch1 0.00 0.00020 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total3 0.00 0.00020 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)********************************************************************************至此,這個(gè)問(wèn)題得到了完滿的解決.