OraFAQ Forum: Performance Tuning » BITMAP INDEX performance issue

Home » RDBMS Server » Performance Tuning » BITMAP INDEX performance issue (10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

BITMAP INDEX performance issue [message #427602]

Fri, 23 October 2009 08:07

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?

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427605 is a reply to message #427602]

Fri, 23 October 2009 08:16

cookiemonster
Messages: 13923
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?

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427606 is a reply to message #427602]

Fri, 23 October 2009 08:16

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

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427609 is a reply to message #427605]

Fri, 23 October 2009 08:19

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

cookiemonster
Messages: 13923
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

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.

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427615 is a reply to message #427614]

Fri, 23 October 2009 09:00

cookiemonster
Messages: 13923
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.

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427665 is a reply to message #427615]

Fri, 23 October 2009 20:02

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

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427825 is a reply to message #427665]

Mon, 26 October 2009 01:16

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 1264 times)

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427880 is a reply to message #427825]

Mon, 26 October 2009 05:12

cookiemonster
Messages: 13923
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.

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427912 is a reply to message #427880]

Mon, 26 October 2009 06:32

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

Report message to a moderator

Re: BITMAP INDEX performance issue [message #427947 is a reply to message #427912]

Mon, 26 October 2009 09:18

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

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>

Report message to a moderator

Re: BITMAP INDEX performance issue [message #429112 is a reply to message #428043]

Mon, 02 November 2009 03:24

johnbach
Messages: 32
Registered: June 2009

Member

Oracle Advanced Queuing solved the problem Smile

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 18:08:57 CDT 2024