Home » Server Options » Text & interMedia » BITMAP CONVERSION - Performance problem
BITMAP CONVERSION - Performance problem [message #305015] Fri, 07 March 2008 09:13 Go to next message
ankurgodambe
Messages: 45
Registered: March 2005
Member
hi,
I have a concatenated text index created over 5 columns in a table. Recently, started having performance issues with the query and saw that the plan for query has changed.

Old plan:
=======
SQL> select *
from newseims
where
contains(sma_company_name,'ibm',0) > 0
and contains(sma_company_name, 'cottle',1)>0
and contains(sma_company_name,'usa',2)>0
order by score(0) desc, score(1) desc; 3 4 5 6 7

1 row selected.

Elapsed: 00:00:00.52

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=78 Card=1 Bytes=146)
1 0 SORT (ORDER BY) (Cost=78 Card=1 Bytes=146)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'NEWSEIMS' (Cost=11 Car
d=1 Bytes=146)

3 2 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=19)

======

New Plan:

======
SQL> select *
from newseims
where
contains(sma_company_name,'ibm',0) > 0
and contains(sma_company_name, 'cottle',1)>0
and contains(sma_company_name,'usa',2)>0
order by score(0) desc, score(1) desc; 2 3 4 5 6 7

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=74 Card=1 Bytes=146)
1 0 SORT (ORDER BY) (Cost=74 Card=1 Bytes=146)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'NEWSEIMS' (Cost=7 Card
=1 Bytes=146)

3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP AND
5 4 BITMAP CONVERSION (FROM ROWIDS)
6 5 SORT (ORDER BY)
7 6 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)
8 4 BITMAP CONVERSION (FROM ROWIDS)
9 8 SORT (ORDER BY)
10 9 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)
11 4 BITMAP CONVERSION (FROM ROWIDS)
12 11 SORT (ORDER BY)
13 12 DOMAIN INDEX OF 'NEWSEIMS_IDX' (Cost=0)

======

I have tried all sort of things like deleting stats on DR$ tables, index etc, but nothing seems to give me back the old plan. Same column "sma_company_name" appears twice in the query as "within another_column" is passed as bind variable. The response from old plan was better as it was scanning over the domain index only once. Appreciate any help/pointers.

Thanks
Re: BITMAP CONVERSION - Performance problem [message #305026 is a reply to message #305015] Fri, 07 March 2008 11:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Presumably your data has grown and thus the best execution plan may be different and responses will be slower. However, your indexes may also have become fragmented. Do you do regular synchronization, optimization, rebuilding? At this point I believe I would drop and recreate the index, then run dbms_stats.gather_table_stats on every table that is used in your concatenated datastore. Also, the fewer contains clauses that you use, the better the performance. So, you might try the following:

select *
from newseims
where
contains(sma_company_name,'ibm and cottle and usa',0) > 0
order by score(0) desc;

Re: BITMAP CONVERSION - Performance problem [message #305079 is a reply to message #305015] Sat, 08 March 2008 03:58 Go to previous messageGo to next message
ankurgodambe
Messages: 45
Registered: March 2005
Member

I recreated the index with 2G index memory which reduced the $I rows, but the plan doesn't change. I am not sure if i understand it correctly but we create a concatenated datastore so that a single domain index scan can fetch results for text queries over multiple columns. Else we can just create seperate text index for every column that is included in the datastore.
Re: BITMAP CONVERSION - Performance problem [message #305098 is a reply to message #305079] Sat, 08 March 2008 08:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The concatenated datastore enables you to use a single index to search all of the columns, IF YOU USE A SINGLE CONTAINS CLAUSE:

http://download.oracle.com/docs/cd/B10501_01/text.920/a96517/aoptim.htm#1007556
Re: BITMAP CONVERSION - Performance problem [message #305152 is a reply to message #305098] Sun, 09 March 2008 07:42 Go to previous messageGo to next message
ankurgodambe
Messages: 45
Registered: March 2005
Member

Thanks Barbara for the link.
But since on earlier occasions it was using a single domain index scan to fetch results, I want it back. I had stats on the schema/tables preserved before dropping the index which I imported back but it does not return me the previous execution path. It looks like optimizer decides the execution from ctxsys.dr$stats table which has details of index stored as blob which unfortunately I didn't backup. Any idea if that can be tricked into using single scan?
Re: BITMAP CONVERSION - Performance problem [message #305158 is a reply to message #305152] Sun, 09 March 2008 10:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You can try adding a NO_EXPAND hint to try to influence the optimizer, but that still does not guarantee it will not do the bitmap conversion. However, you need to understand why it is doing what it is doing. The bitmap conversion is something that it is doing to accommodate the multiple AND conditions with the current data. If you eliminate that, the old execution plan may be slower or you may even get an error. Multiple contains clauses are bad. The proper way to do this is to rewrite the query as I suggested. You should stop trying to make it use a plan on a bad query that is not the best plan for the current data and use a good query and let it choose the best plan for that query with the current data.

[Updated on: Sun, 09 March 2008 10:27]

Report message to a moderator

Re: BITMAP CONVERSION - Performance problem [message #305159 is a reply to message #305152] Sun, 09 March 2008 10:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Here is an example that shows that the proper query produces your old plan. It does not use a concatenated datastore, but the concept is the same.

-- test data:
SCOTT@orcl_11g> create table newseims
  2    (sma_company_name varchar2(60))
  3  /

Table created.

SCOTT@orcl_11g> insert into newseims values ('ibm cottle usa')
  2  /

1 row created.

SCOTT@orcl_11g> insert into newseims select object_name from all_objects
  2  /

68341 rows created.

SCOTT@orcl_11g> create index newseims_idx on newseims (sma_company_name)
  2  indextype is ctxsys.context
  3  /

Index created.

SCOTT@orcl_11g> exec dbms_stats.gather_table_stats ('SCOTT', 'NEWSEIMS')

PL/SQL procedure successfully completed.


-- your query:
SCOTT@orcl_11g> set autotrace on explain
SCOTT@orcl_11g> select *
  2  from   newseims
  3  where  contains (sma_company_name, 'ibm',	  0) > 0
  4  and    contains (sma_company_name, 'cottle', 1) >0
  5  and    contains (sma_company_name, 'usa',	  2) >0
  6  order  by score(0) desc, score(1) desc
  7  /

SMA_COMPANY_NAME
------------------------------------------------------------
ibm cottle usa


Execution Plan
----------------------------------------------------------
Plan hash value: 439738344

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |     1 |    24 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                    |              |     1 |    24 |     1 (100)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | NEWSEIMS     |     1 |    24 |     0   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |              |       |       |            |          |
|   4 |     BITMAP AND                    |              |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|              |       |       |            |          |
|   6 |       SORT ORDER BY               |              |       |       |            |          |
|*  7 |        DOMAIN INDEX               | NEWSEIMS_IDX |       |       |     0   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|              |       |       |            |          |
|   9 |       SORT ORDER BY               |              |       |       |            |          |
|* 10 |        DOMAIN INDEX               | NEWSEIMS_IDX |       |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'ibm',0)>0)
   7 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'cottle',1)>0 AND
              "CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'usa',2)>0)
  10 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'usa',2)>0)


-- suggested query:
SCOTT@orcl_11g> select *
  2  from   newseims
  3  where  contains (sma_company_name, 'ibm and cottle and usa',    0) > 0
  4  order  by score(0) desc
  5  /

SMA_COMPANY_NAME
------------------------------------------------------------
ibm cottle usa


Execution Plan
----------------------------------------------------------
Plan hash value: 1483980325

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    24 |     2  (50)| 00:00:01 |
|   1 |  SORT ORDER BY               |              |     1 |    24 |     2  (50)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| NEWSEIMS     |     1 |    24 |     1   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | NEWSEIMS_IDX |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'ibm and cottle and usa',0)>0)

SCOTT@orcl_11g> 


Re: BITMAP CONVERSION - Performance problem [message #305161 is a reply to message #305159] Sun, 09 March 2008 14:08 Go to previous messageGo to next message
ankurgodambe
Messages: 45
Registered: March 2005
Member

Multiple contains is needed. The query which I see in the database with bind variable is this:

= = = =
select * from newseims where
contains(sma_company_name, :1, 0) > :2 and contains(sma_compan
y_name, :3, 1) > :4 order by score(0) desc, score(1) desc

= = = =

But the bind variable values are:

= = = =
:1 = '(ibm) within sma_company_name or (ibm) within sma_dba_name'
:2 = 0
:3 = '(101) within sma_address1_raw'
:4 = 0
= = = =

So the actual query that is used is:

= = = =
select * from newseims where
contains(sma_company_name, '(ibm) within sma_company_name or (ibm) within sma_dba_name', 0) > 0 and
contains(sma_company_name, '(101) within sma_address1_raw', 1) > 50
and contains(sma_company_name, '(123456) within sma_postal_code') > 0 order by score(0) desc, score(1) desc ;

= = = =

I substitued bind variables with some constants for generating plan cause thats how the optimizer is seeing the query( not considering the "within" clause on the concatenated index column). The plan in v$sql_plan is same as what i have mentioned in first post.
The ordering by score "0" and "1" is very important here on which the whole internal algorithm depend and hence multiple
"contains". Can i combine the actual query in single "contains" and preserve the ordering by "sma_company_name " desc and "sma_postal_code" asc ?
Re: BITMAP CONVERSION - Performance problem [message #305163 is a reply to message #305161] Sun, 09 March 2008 18:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You can put all of the within clauses in one contains query. You can also multiply by weights that can be whole numbers to increase the weight or fractions to decrease the weight for each within clause. I have provided another example below, using a multi_column_datastore and supplying simple default values for the numbers.

-- test data:
SCOTT@orcl_11g> create table newseims
  2    (sma_company_name varchar2(16),
  3  	sma_dba_name	 varchar2(16),
  4  	sma_address1_raw varchar2(16),
  5  	sma_postal_code  varchar2(16))
  6  /

Table created.

SCOTT@orcl_11g> insert into newseims values ('ibm', 'ibm', 101, '123456')
  2  /

1 row created.

SCOTT@orcl_11g> insert into newseims
  2  select a.ename, b.ename, c.ename, d.ename
  3  from   emp a, emp b, emp c, emp d
  4  /

38416 rows created.

SCOTT@orcl_11g> begin
  2    ctx_ddl.create_preference ('your_datastore', 'multi_column_datastore');
  3    ctx_ddl.set_attribute ('your_datastore', 'columns',
  4  	 'sma_company_name, sma_dba_name, sma_address1_raw, sma_postal_code');
  5    ctx_ddl.create_section_group ('your_sec_group', 'basic_section_group');
  6    ctx_ddl.add_field_section ('your_sec_group', 'sma_company_name', 'sma_company_name');
  7    ctx_ddl.add_field_section ('your_sec_group', 'sma_dba_name', 'sma_dba_name');
  8    ctx_ddl.add_field_section ('your_sec_group', 'sma_address1_raw', 'sma_address1_raw');
  9    ctx_ddl.add_field_section ('your_sec_group', 'sma_postal_code', 'sma_postal_code');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> create index newseims_idx on newseims (sma_company_name)
  2  indextype is ctxsys.context
  3  parameters
  4    ('datastore	your_datastore
  5  	 section group	your_sec_group')
  6  /

Index created.

SCOTT@orcl_11g> exec dbms_stats.gather_table_stats ('SCOTT', 'NEWSEIMS')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> variable a varchar2(100)
SCOTT@orcl_11g> variable b number
SCOTT@orcl_11g> variable c varchar2(100)
SCOTT@orcl_11g> variable d number
SCOTT@orcl_11g> variable e varchar2(100)
SCOTT@orcl_11g> variable f number
SCOTT@orcl_11g> variable g number


-- original query:
SCOTT@orcl_11g> begin
  2    :a := 'ibm within sma_company_name or ibm within sma_dba_name';
  3    :b := 0;
  4    :c := '101 within sma_address1_raw';
  5    :d := 0;
  6    :e := '123456 within sma_postal_code';
  7    :f := 0;
  8    :g := 0;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> set autotrace on explain
SCOTT@orcl_11g> select newseims.*, score(0), score(1), score(2)
  2  from   newseims
  3  where  contains (sma_company_name, :a, 0) > :b
  4  and    contains (sma_company_name, :c, 1) > :d
  5  and    contains (sma_company_name, :e, 2) > :f
  6  order  by score(0) desc, score(1) desc
  7  /

SMA_COMPANY_NAME SMA_DBA_NAME     SMA_ADDRESS1_RAW SMA_POSTAL_CODE    SCORE(0)   SCORE(1)   SCORE(2)
---------------- ---------------- ---------------- ---------------- ---------- ---------- ----------
ibm              ibm              101              123456                   17         17         17


Execution Plan
----------------------------------------------------------
Plan hash value: 439738344

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |     1 |    23 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY                    |              |     1 |    23 |     9  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID     | NEWSEIMS     |     1 |    23 |     8   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |              |       |       |            |          |
|   4 |     BITMAP AND                    |              |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|              |       |       |            |          |
|   6 |       SORT ORDER BY               |              |       |       |            |          |
|*  7 |        DOMAIN INDEX               | NEWSEIMS_IDX |       |       |     4   (0)| 00:00:01 |
|   8 |      BITMAP CONVERSION FROM ROWIDS|              |       |       |            |          |
|   9 |       SORT ORDER BY               |              |       |       |            |          |
|* 10 |        DOMAIN INDEX               | NEWSEIMS_IDX |       |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",:E,2)>TO_NUMBER(:F))
   7 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",:A,0)>TO_NUMBER(:B) AND
              "CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",:C,1)>TO_NUMBER(:D))
  10 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",:C,1)>TO_NUMBER(:D))


-- suggested query:
SCOTT@orcl_11g> begin
  2    :a := 'ibm within sma_company_name or ibm within sma_dba_name';
  3    :b := 1;
  4    :c := '101 within sma_address1_raw';
  5    :d := 1;
  6    :e := '123456 within sma_postal_code';
  7    :f := 1;
  8    :g := 0;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> select newseims.*, score(0)
  2  from   newseims
  3  where  contains
  4  	      (sma_company_name,
  5  	       '(' || :a || ') * ' || :b || ' and ' ||
  6  	       '(' || :c || ') * ' || :d || ' and ' ||
  7  	       '(' || :e || ') * ' || :f,
  8  	       0) > :g
  9  order  by score(0) desc
 10  /

SMA_COMPANY_NAME SMA_DBA_NAME     SMA_ADDRESS1_RAW SMA_POSTAL_CODE    SCORE(0)
---------------- ---------------- ---------------- ---------------- ----------
ibm              ibm              101              123456                   17


Execution Plan
----------------------------------------------------------
Plan hash value: 1483980325

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    19 |   437 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY               |              |    19 |   437 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| NEWSEIMS     |    19 |   437 |     8   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | NEWSEIMS_IDX |       |       |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("SMA_COMPANY_NAME",'('||:A||') * '||:B||' and
              '||'('||:C||') * '||:D||' and '||'('||:E||') * '||:F,0)>TO_NUMBER(:G))

SCOTT@orcl_11g> 


Re: BITMAP CONVERSION - Performance problem [message #305478 is a reply to message #305015] Mon, 10 March 2008 23:54 Go to previous message
ankurgodambe
Messages: 45
Registered: March 2005
Member
Thanks Barbara for the explanation. This should help in combining the query in single contains.
Previous Topic: intermedia in oracle 10g
Next Topic: How do I list Index Preferences I have created?
Goto Forum:
  


Current Time: Thu Dec 08 00:24:16 CST 2016

Total time taken to generate the page: 0.15366 seconds