Home » RDBMS Server » Performance Tuning » BITMAP INDEX performance issue (10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production)
BITMAP INDEX performance issue [message #427602] Fri, 23 October 2009 08:07 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
SQL> desc pin_type;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PIN_NO                                    NOT NULL VARCHAR2(20)
 SERIAL_NO                                 NOT NULL VARCHAR2(20)
 AMOUNT                                    NOT NULL FLOAT(12)
 STATUS                                             CHAR(1)


SQL> select count(amount),status,amount from pin_type group by(amount,status);

COUNT(AMOUNT) S     AMOUNT
------------- - ----------
      1192669 N         10
       507332 u         10

SQL> explain plan for update pin_type set status='U' where amount=10 and status='N' and rownum=1;

Explained.

SQL> set line 1000
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3152955204

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                   |     1 |     5 |    62   (0)| 00:00:01 |
|   1 |  UPDATE                       | PIN_TYPE          |       |       |            |          |
|*  2 |   COUNT STOPKEY               |                   |       |       |            |          |
|   3 |    BITMAP CONVERSION TO ROWIDS|                   |  1668K|  8147K|    62   (0)| 00:00:01 |
|*  4 |     BITMAP INDEX SINGLE VALUE | IDX_AMOUNT_STATUS |       |       |            |          |
---------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   4 - access("AMOUNT"=10 AND "STATUS"='N')
       filter("AMOUNT"=10 AND "STATUS"='N')

18 rows selected.


Hardware spec
RHEL 5
CPU 2 Quad Core
RAM : 8 GB

I have gathered statistics,enabled monitoring,disabled logging.
I had problem with ordinary index and so now i have switched to BITMAP index.
My index is
CREATE BITMAP INDEX VLINK.IDX_AMOUNT_STATUS ON VLINK.PIN_TYPE
(AMOUNT, STATUS)


Actual usage in application will be like
update pin_type
set status='U'
WHERE amount=10 and status='N' and rownum=1
returning pin into :pin;



This update takes around 9 secs on 20 tps load.
Note the cpu cost 62.

Any suggestion for improving performance?

Re: BITMAP INDEX performance issue [message #427605 is a reply to message #427602] Fri, 23 October 2009 08:16 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you think you want to use an index at all?
Considering the amount of data that matches the where clause I would assume a full table scan would be better.

And why are you updating random rows?
Re: BITMAP INDEX performance issue [message #427606 is a reply to message #427602] Fri, 23 October 2009 08:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://jonathanlewis.wordpress.com/2009/10/21/bitmap-updates/

Regards

Raj
Re: BITMAP INDEX performance issue [message #427609 is a reply to message #427605] Fri, 23 October 2009 08:19 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
I am fetching a pin(record) from table and marking it as used so no one else will use it again.

I tried without index,with normal index,bitmap index.
But no luck
Any suggestion?

[Updated on: Fri, 23 October 2009 08:23]

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427613 is a reply to message #427609] Fri, 23 October 2009 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
johnbach wrote on Fri, 23 October 2009 14:19
I am fetching a pin(record) from table and marking it as used so no one else will use it again.

That doesn't really answer the question of why you're updating random rows. I would assume that if you're marking records as used you would want to update specific records.

johnbach wrote on Fri, 23 October 2009 14:19

I tried without index,with normal index,bitmap index.
But no luck
Any suggestion?


Post the explain plans without index and with normal index.

EDIT: fixed tags

[Updated on: Fri, 23 October 2009 08:41]

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427614 is a reply to message #427613] Fri, 23 October 2009 08:54 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Quote:
That doesn't really answer the question of why you're updating random rows. I would assume that if you're marking records as used you would want to update specific records.


There are only two set of record for a particular amount-used and not used.

I am fetching a single(any) unused pin from table and marking it as used.

I switched to enterprise edition only for BITMAP index.

Re: BITMAP INDEX performance issue [message #427615 is a reply to message #427614] Fri, 23 October 2009 09:00 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
johnbach wrote on Fri, 23 October 2009 14:54
Quote:
That doesn't really answer the question of why you're updating random rows. I would assume that if you're marking records as used you would want to update specific records.


There are only two set of record for a particular amount-used and not used.

I am fetching a single(any) unused pin from table and marking it as used.


I know. any=random in this case.
Re: BITMAP INDEX performance issue [message #427665 is a reply to message #427615] Fri, 23 October 2009 20:02 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How long does the equivalent SELECT statement take? If the SELECT is much faster than the update of a single row, then it may be that you have a trigger on this table, or perhaps a non-indexed foreign key pointing to this table.

Run trace and check the TKPROF output to find out where the time is being spent.

Ross Leishman
Re: BITMAP INDEX performance issue [message #427825 is a reply to message #427665] Mon, 26 October 2009 01:16 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Thanks for you help,Leishman.
I dont have any trigger/foreign key.
May be the delay is due to BITMAP index.
Please find the attached trace flie.
As per EXPLAIN PLAN , select consumes less cpu(FULL TABLE SCAN)
SQL> explain plan for update pin_type set status='U' where amount=10 and status='N' and rownum=1;
|   0 | UPDATE STATEMENT              |                   |     1 |     5 |    48   (0)| 00:00:01 |
|   1 |  UPDATE                       | PIN_TYPE          |       |       |            |          |
|*  2 |   COUNT STOPKEY               |                   |       |       |            |          |
|   3 |    BITMAP CONVERSION TO ROWIDS|                   |  1256K|  6136K|    48   (0)| 00:00:01 |
|*  4 |     BITMAP INDEX SINGLE VALUE | IDX_AMOUNT_STATUS |       |       |            |          |

SQL>  explain plan for select pin_no from pin_type where amount=10 and status='N' and rownum=1;

|   0 | SELECT STATEMENT   |          |     1 |    19 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| PIN_TYPE |     2 |    38 |     2   (0)| 00:00:01 |


If you have some other solution/design, please suggest
I have 1.7 million vouchers .
To process an online transaction, i pick an unused voucher and mark it as used.
I am expecting a 30 updates(transaction) per second for a 1.7 million records continuously.
I tested with normal and bitmap index.
The update is faster for some 2 to 5 lacs transaction(around 3 milli secs)
But after that it takes 3 to 9 secs.

  • Attachment: trace1.txt
    (Size: 14.39KB, Downloaded 1412 times)
Re: BITMAP INDEX performance issue [message #427880 is a reply to message #427825] Mon, 26 October 2009 05:12 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
johnbach wrote on Mon, 26 October 2009 06:16
I tested with normal and bitmap index.
The update is faster for some 2 to 5 lacs transaction(around 3 milli secs)
But after that it takes 3 to 9 secs.



That'll be because bitmaps aren't designed to work on tables that are updated constantly - their structure gets messed up and slows access down. Bitmaps only really work on datawarehouses which isn't the case here.

You need to abandom the bitmap index.
Post explain plans for the update with a normal index and with no indexes.
Re: BITMAP INDEX performance issue [message #427912 is a reply to message #427880] Mon, 26 October 2009 06:32 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Now i have partitioned the table and created normal local index.

CREATE TABLE PIN_TYPE
   (
    PIN_NO            VARCHAR2(20 BYTE)           PRIMARY KEY,
    SERIAL_NO         VARCHAR2(20 BYTE)           NOT NULL,
    AMOUNT            FLOAT(12)                  NOT NULL,
    STATUS            CHAR(1 BYTE)
   )
PARTITION BY LIST (STATUS)
    (PARTITION VALUES ('N')  ,PARTITION VALUES (DEFAULT) );

CREATE INDEX IDX_AMOUNT_STATUS ON PIN_TYPE(AMOUNT, STATUS) LOCAL


Started load @60 transaction per second and seems to be working fine till now.

Will update you in couple of hours

Thanks
Re: BITMAP INDEX performance issue [message #427947 is a reply to message #427912] Mon, 26 October 2009 09:18 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
After 6 lac transaction
With partitioned table and normal index
Forgot to mention that I have ENABLEd ROW MOVEMENT.

SQL> explain plan for update pin_type set status='U' where amount=10 and status='N' and rownum=1;

Explained.

Elapsed: 00:00:00.47
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4048346353

-------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT        |                   |     1 |    80 |   120   (0)| 00:00:02 |       |       |
|   1 |  UPDATE                 | PIN_TYPE          |       |       |            |          |       |       |
|*  2 |   COUNT STOPKEY         |                   |       |       |            |          |       |       |
|   3 |    PARTITION LIST SINGLE|                   |  5408 |   422K|    57   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     INDEX RANGE SCAN    | IDX_AMOUNT_STATUS |  5408 |   422K|    57   (0)| 00:00:01 |     1 |     1 |
-------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   4 - access("AMOUNT"=10 AND "STATUS"='N')

Note
-----
   - dynamic sampling used for this statement

21 rows selected.

Elapsed: 00:00:00.74


Cpu uasge swings between 3 to 70%
oracle Update time swings between 0.004 seconds to 2 seconds


[Updated on: Mon, 26 October 2009 09:25]

Report message to a moderator

Re: BITMAP INDEX performance issue [message #428043 is a reply to message #427947] Tue, 27 October 2009 00:10 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
At the end of the load,Index automatically disabled

SQL> explain plan for update pin_type set status='U' where amount=10 and status='N' and rownum=1;

Explained.
SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1768009027

----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT        |          |     1 |    33 |  2021   (2)| 00:00:25 |       |       |
|   1 |  UPDATE                 | PIN_TYPE |       |       |            |          |       |       |
|*  2 |   COUNT STOPKEY         |          |       |       |            |          |       |       |
|   3 |    PARTITION LIST SINGLE|          |  1233K|    38M|  2021   (2)| 00:00:25 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL   | PIN_TYPE |  1233K|    38M|  2021   (2)| 00:00:25 |     1 |     1 |
----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   4 - filter("AMOUNT"=10)

17 rows selected.

SQL>
icon10.gif  Re: BITMAP INDEX performance issue [message #429112 is a reply to message #428043] Mon, 02 November 2009 03:24 Go to previous message
johnbach
Messages: 32
Registered: June 2009
Member
Oracle Advanced Queuing solved the problem Smile .
Previous Topic: why oracle change the sql plan from hash join to nested loop when the system resource load is high?
Next Topic: Advanced Queuing correlation id
Goto Forum:
  


Current Time: Sun Dec 08 19:48:05 CST 2024