BITMAP INDEX performance issue [message #427602] |
Fri, 23 October 2009 08:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 #427613 is a reply to message #427609] |
Fri, 23 October 2009 08:40 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
johnbach wrote on Fri, 23 October 2009 14:19I 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
johnbach wrote on Fri, 23 October 2009 14:54Quote: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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #427880 is a reply to message #427825] |
Mon, 26 October 2009 05:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
johnbach wrote on Mon, 26 October 2009 06:16I 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|