[it認(rèn)證]oracle診斷案例-sql_trace(編輯修改稿)
2024-09-17 13:41 本頁面
【文章內(nèi)容簡介】 egoryarticleassign 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索引在以上查詢中都沒有被用到.檢查表結(jié)構(gòu):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) 問題發(fā)現(xiàn):因?yàn)閍rticleid是個(gè)字符型數(shù)據(jù),查詢中給入的articleid= 20030700400141 是個(gè)數(shù)字值oracle發(fā)生潛在的數(shù)據(jù)類型轉(zhuǎn)換,從而導(dǎo)致了索引失效sql select auditstatus,categoryid 2 from 3 categoryarticleassign where articleid=20030700400132。auditstatus categoryid 9 94 0 383 0 695 elapsed: 00:00: 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) 簡單的在參數(shù)兩側(cè)各增加一個(gè),既可解決這個(gè)問題.對(duì)于類似的查詢,我們發(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 current rows parse 1 0 0 0 0execute 1 0 0 0 0fetch 1 0 2 0 0 total 3 0 2 0 0misses in library cache during parse: 1optimizer goal: chooseparsing user id: 20 rows row source operation 0 table acces
黨政相關(guān)相關(guān)推薦
鄂ICP備17016276號(hào)-1