What I am here to present, is real time production issue happened recently in an Application. Here is the snippet of code benchmarked in development database and solution to that applied in production.
rajesh@10GR2> desc xx_comment_test;
Name Null? Type
---------------------------- -------- --------------
COMMENT_KEY NOT NULL NUMBER(10)
COMMENT_DESC NOT NULL VARCHAR2(500)
CREATE_DT NOT NULL DATE
rajesh@10GR2> select ui.index_name, ui.column_name, ui.column_length,u.uniqueness,u.index_type
2 from user_ind_columns ui, user_indexes u
3 where ui.table_name ='XX_COMMENT_TEST'
4 and u.index_name = ui.index_name
5 /
INDEX_NAME COLUMN_NAME COLUMN_LENGTH UNIQUENES INDEX_TYPE
-------------------- -------------------- ------------- --------- ---------------------------
COMMENT_DESC_IDX COMMENT_DESC 500 UNIQUE NORMAL
Elapsed: 00:00:00.15
rajesh@10GR2>
rajesh@10GR2> select count(*) from xx_comment_test;
COUNT(*)
----------
529646
Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> select count(*)
2 from xx_comment_test
3 where comment_desc like '%AUDITRECON%'
4 /
COUNT(*)
----------
32
Elapsed: 00:00:00.12
rajesh@10GR2>
The table is having around 0.5M records and 32 of them has the word AUDITRECON in them, and Unique B* index available on COMMENT_DESC column.
When firing the below query from application layer it gets plan like this.
select comment_key
from xx_comment_test
where comment_desc like '%AUDITRECON%'
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 2 0.12 0.71 1614 5671 0 32
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.12 0.71 1614 5671 0 32
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
32 TABLE ACCESS FULL XX_COMMENT_TEST (cr=5671 pr=1614 pw=0 time=635693 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 131 0.01 0.10
db file scattered read 571 0.01 0.43
SQL*Net message from client 2 0.00 0.00
Two things to be noted here, Almost 5K logical IO and nasty *FTS*. The correct solution here is loose B*Tree index and have Domain index in place like below.
rajesh@10GR2> CREATE INDEX search_idx ON xx_comment_test
2 (comment_desc)
3 INDEXtype IS ctxsys.context;
Index created.
Elapsed: 00:01:00.99
rajesh@10GR2>
select comment_key
from xx_comment_test
where contains(comment_desc,'%AUDITRECON%') > 0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 207 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 26 0 32
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 233 0 32
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 55
Rows Row Source Operation
------- ---------------------------------------------------
32 TABLE ACCESS BY INDEX ROWID XX_COMMENT_TEST (cr=4824 pr=4582 pw=0 time=1817186 us)
32 DOMAIN INDEX SEARCH_IDX (cr=4803 pr=4582 pw=0 time=1817297 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.06 0.07
Now the plan changes and thats far better than previous plan.
No comments:
Post a Comment