This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Feb 28, 2013 6:26 AM by ji li RSS

to bitmap or not, that is the question

977635 Newbie
Currently Being Moderated
Hello my tuning friends.

I am running Oracle 11.2 (on Solaris 10) for a very active OLTP database, but that is also frequently used to pull reports on the most volatile critical table in the database.

My database has anywhere from 100 to 400 concurrent users.
Our application (REMEDY) is designed to put "tickets" in a single main table (with approx. 560 fields that 40 are clobs).
The dilemma I need help with is whether to put a bit map index on some of the low cardinality columns or not.
And, for that matter, many columns in this table are very low cardinality.
My fear is that I am afraid that putting a bitmap index on a highly active OLTP table might impede performance of ticket creations (new rows in the table).
Or, if I did it, can I have a job run every night that recreates the bitmap indexes on this table?
But what about performance on the table between the time of rebuilding the BM indexes?

In this case, this is a typical query that is run against our main table (containing approx. 6m records and is 16G in size).
When I look in OEM, I see that it does a full table scan on this table, and in this case, only returns 262 records.
Obviously, you can infer the columns in the predicate clause are very low cardinality (by the names of the columns and the values searched).
SELECT "Main_Ticket"."SHORTTICKETNUMBER"      ,
        "Main_Ticket"."OUTAGESEVERITY"        ,
        "Main_Ticket"."OUTAGESEVERITYTEXT"    ,
        "Main_Ticket"."OUTAGESERVICESLIST"    ,
        "Main_Ticket"."OUTAGESTATUS"          ,
        "Main_Ticket"."OUTAGEOVERALLSTARTTIME",
        "Main_Ticket"."OUTAGEOVERALLENDTIME"  ,
        "Main_Ticket"."CITY"                  ,
        "Main_Ticket"."STATE"                 ,
        "Main_Ticket"."MASTEROUTAGE"          ,
        "Main_Ticket"."GROUPCREATEDBY"        ,
        "Main_Ticket"."GROUPASSIGNEDTO"
FROM "ARADMIN"."MAIN_TICKET" "Main_Ticket"
WHERE "Main_Ticket"."OUTAGESEVERITY"<=2
        AND NOT
        (
                "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'BBSG%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'CIA%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'DESIGNED%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'IHD%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NABB%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NCC%'
                OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NFC%'
        )
I'm assuming to put the bitmap index on "OUTAGESEVERITY".
Not sure about GROUPASSIGNEDTO, because it uses the LIKE function and may not use the BM index if it had one.
In this case, would a FBI be better?

Edited by: 974632 on Feb 27, 2013 6:29 AM
  • 1. Re: to bitmap or not, that is the question
    TSharma-Oracle Guru
    Currently Being Moderated
    If a session modifies the indexed data, then all of the rows that index entry points to are effectively locked in most cases. Oracle cannot lock an individual bit in a bitmap index entry; it locks the entire bitmap index entry.
    In an OLTP multi-user system with update, delete and insert you have better not implement Bitmap indexes at all in order to avoid deadlocks.
    If you will use bitmap on a table with hundreds of concurrent session writing to a single index entry, it would kill the performance.

    TOM KYTE's says:
    DROP THE BITMAP INDEX RIGHT NOW. bitmapped indexes work in a read mostly, reporting/warehouse environment. They absolutely DO NOT WORK in OLTP - period, nothing will change that - not ever.
    This link will help too

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2049072400346231785

    Also, try creating index on 'GROUPASSIGNEDTO'. As you are using leading characters in the clause, query optimizer should be able to use an index. Give this a shot.

    Edited by: TSharma on Feb 27, 2013 9:56 AM
  • 2. Re: to bitmap or not, that is the question
    Bjoern Rost Oracle ACE Director
    Currently Being Moderated
    putting a bitmap index on a table with heavy DML sounds like a very bad idea and I wouldn't even be so sure if that would help your query (what is the number of values <=2 compared to the total number of rows? - if this is a significant amount, then a full scan is propably in order anyway.). Put an FBI on the text column is also not very likely to help you out, especially with the NOT clause. It would be much easier to reverse the logic here like ... AND GROUPASSIGNEDTO IN ('GROUP1', 'GROUP2'...) as long as the number of different values can be handled.

    In general, how would you like the idea to keep a second copy of this table for reporting? You could create a materialized view from this table and then create your bitmap indexes on that MV and run your reports against that. Maybe you could get away with just one refresh a day? And that way you would not loose DML performance against the real table while still boosting performance on reports. Just an idea

    Bjoern
  • 3. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    Thank you for your answer TSharma.
    Yes, that was my thought as well, especially with regards to the LIKE function on GROUPASSIGNEDTO.
    Obviously, columns such as "OUTAGESEVERITY" has a very low cardinality of possible 4 (Sev 1, Sev 2, Sev 3 and Sev 4).
    And Group Assigned To can only have a limited number of possible groups as well, so also would have very low cardinality (possibly 100 at the most out of 6million records).

    Then, while reading your posting, it occurred to me that creating a MV on the table for the columns used by the reporting might be best.
    Then I read the next reply to my posting... LOL Hit the nail on the head.
    Okay, so then, my question with using the MV is what would be the performance impact of have an MV that is updated every time a change is made on the main table since the main table is so active?
  • 4. Re: to bitmap or not, that is the question
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    clobs).
    The dilemma I need help with is whether to put a bit map index on some of the low cardinality columns or not.
    The whole "low cardinality" thing for bitmap indexes is very misleading - the primary consideration for bitmap indexes is whether or not your queries can COMBINE enough of them to produce high-precision access path into the table. (And, of course, they're almost always very bad news on an OLTP system).
    When I look in OEM, I see that it does a full table scan on this table, and in this case, only returns 262 records.
    Is this because a very small fraction of the rows have "OUTAGESEVERITY"<=2, or do a lot of them match that predicate but disappear on on the next predicate ?

    >
    FROM "ARADMIN"."MAIN_TICKET" "Main_Ticket"
    WHERE "Main_Ticket"."OUTAGESEVERITY"<=2
    AND NOT
    (
    "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'BBSG%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'CIA%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'DESIGNED%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'IHD%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NABB%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NCC%'
    OR "Main_Ticket"."GROUPASSIGNEDTO" LIKE 'NFC%'
    )
    In this case, would a FBI be better?
    Depends on why you get only 262 rows as a final result, and whether or not you're allowed to change the code. A simple b-tree index on outagseverity with a frequency histogram on the column MIGHT be enough if there are only a small number of rows with outageseverity = 2.

    An FBI might be more stable if this form of query is common and you are only interested in "rare" values - in which case a simple option would be to create an FBI (or virtual column with index) that was null for common values, and exposed the outageseverity for "rare" values.

    If you need to cross-check the groupassignedto at the same time as checking for "relatively rare" values, you could create a two column index where the first column exposed the "relatively rare" values of outageseverity, and the second column exposed the groupassignedto only if the first column was not null.

    You'd have to modify the SQL suitably to use the FBI's though - which is why the availability of virtual columns might be of some benefit.

    Tables with more than 254 columns are a bad idea, by the way, and get worse for every (approximate) multiple of 256.

    Regards
    Jonathan Lewis
  • 5. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    Thank you very much for your reply Bjoern.

    I believe your answer is most correct in this case because that occurred to me as well.
    Before I mark this answer as correct, can you please answer this question.

    My question now would be that with using the MV is what would be the performance impact of have an MV that is updated every time a change is made on the main table since the main table is so active?
  • 6. Re: to bitmap or not, that is the question
    Carlovski Pro
    Currently Being Moderated
    Sounds like a more fundamental design issue to me. Are the 'Groups Assigned to' really free text fields?
    Presumably these must match up to a list of 'Groups' somewhere? It looks like you are using naming structure and likes to simulate some sort of group hierarchy.

    you may also want to think about splitting out the ticket header information and ticket details - presumably for these kind of queries you don't have to see all of the clob based data, until you go into the ticket details? If nothing else you could give yourself a much skinnier table to full scan.

    Anyway, to duplicate what has already been said, a bitmap on this table will absolutely kill performance.
  • 7. Re: to bitmap or not, that is the question
    Carlovski Pro
    Currently Being Moderated
    Also, when you say typical - is this a typical end user query, i.e ran lots of times in a typical day? Or a one off with someone doing some data analysis?
    Either way, it seems unlikely that they are really interested in every row in the table - is there not something fairly selective that would reduce the set of data? e.g an 'open' flag or just the last day/week/month, which could be simply indexed with a Btree index?
  • 8. Re: to bitmap or not, that is the question
    TSharma-Oracle Guru
    Currently Being Moderated
    Sometimes you have to keep an eye on MLOG$ table, sometime in highly active Master table ,MLOG$ table gets so big that it takes more time to scan the LOG$ table which could potentially impact the performance. Just keep an eye on LOG$ table asa caution.
  • 9. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    Arg... well crud. yep, I can see that might be something to consider as well.

    Well, as noted above, creating a MV on only the subset of columns from the master table would significantly reduce the performance impacts of doing the full table scans on the entire 560 columns into memory so often. I literally see this user account which is used by anyone who generates a report from within the REMEDY application, constantly running FTSs on the main table and killing performance every time it does it.

    So, related on this subject, if I create an MV which is constantly updated, then I'm assuming I still can't put a BMI on any of the columns of the MV because of the same reasons, right?

    Well, at least using a MV would significantly reduce the number of blocks read into memory when they ran the FTS all the time.
    Better of course would be to rewrite their queries, such as avoiding the use of "AND NOT" and the use of all the LIKE statements, but that might be the way the application creates the queries and we might not have any option to avoid that since we can't change the application.
  • 10. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    Yes, I agree about the fundamental design of the query, but as I noted above, I don't believe we have an option to change that, but rather, it is built into the application's reporting interface where "it" writes the queries rather than our developers or report writers.
  • 11. Re: to bitmap or not, that is the question
    TSharma-Oracle Guru
    Currently Being Moderated
    I'm assuming I still can't put a BMI on any of the columns of the MV because of the same reasons
    I beleive you can use bit map index on MV. The reason to avoid bitmap index on OLTP is because of many concurrent sessions trying to update same index block resulting in deadlocks.
    But here only one session will be updating your MV, so it should not be a problem. I would say you should test creating bitmap on MV...it should not be a problem.
  • 12. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    Hi Jonathan, and thanks for your reply.
    As always, it is an honor to have your input.

    Some of what you mentioned is above my knowledge level of SQL and indexes, but I'm always trying to understand and learn as much as possible. That's what makes it enjoyable.

    Anyway, unfortunately, the reporting interface they use is part of their application and creates the queries dynamically by their selection from the GUI screen they use for reporting. That doesn't help much, does it?

    So, your ideas sound good except that I can say that while it might apply for this particular query, but the next query might be similar enough and different enough that it could not apply universally to all queries and might actually hurt others.

    I'm not sure if the reason they only returned 262 rows was due to the OUTAGE SEVERITY = 2, but I suspect that it was also because of the rest of the predecate clause with the consideration of the GROUPASSIGNEDTO.

    So, a B-tree index might be a more logical choice if the rare enough? Hmmm... I might say that it could be for this query, but the next query might look for severity = 1, which might be the most common. In that case, would the B-tree index benefit or impede performance. I would still think the b-tree would reduce the number of blocks read into memory for the next stage of sorting and narrowing down the number or rows that meet the rest of the criteria.
  • 13. Re: to bitmap or not, that is the question
    977635 Newbie
    Currently Being Moderated
    I beleive you can use bit map index on MV. The reason to avoid bitmap index on OLTP is because of many concurrent sessions trying to update same index block resulting in deadlocks.
    But here only one session will be updating your MV, so it should not be a problem. I would say you should test creating bitmap on MV...it should not be a problem.
    Oh really? I see. That would be great. I like that idea. Thanks.

    I'm also considering Jonathan's idea of using virtual columns, but since we can't modify the queries themselves, I'm not sure how helpful that would be. But we can modify the main view in this case. The main table they are selecting from is actually a view which is a query of the exact table (including all 560 columns including the CLOBS).

    yes, i agree with Jonathan that having a table with more than 256 columns is a bad idea. I know there are bugs related to tables with over 256 columns as well, but my thought is that why should I query a table for only a few columns that is 16G? I have to scan every block of this huge table, including several empty blocks, just to get the values from a couple of columns. Seems like a waste of a lot of resources to me.
  • 14. Re: to bitmap or not, that is the question
    Bjoern Rost Oracle ACE Director
    Currently Being Moderated
    My question now would be that with using the MV is what would be the performance impact of have an MV that is updated every time a change is made on the main table since the main table is so active?
    Do you really have to update the MV on every commit? How about just refreshing it periodically, like every hour or maybe just once a day. You'd loose that real-time reporting but you'd gain much performance and also decouple the MV refreshes from the DML on the base table. Maybe you could even get away without Mlogs if you only update once a day or so. And I must say I have no idea what the effect of locking (due to bitmap indexes on the MV) would be for on commit refreshes without trying it out.

    Bjoern
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points