Performance Problem ? - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: Performance Problem ?

  1. #21
    Join Date
    Nov 2000
    Posts
    212
    as for sybase beeing faster: MS SQL server long ago (1 year?) outperformed oracle 8i. See TOP DB ratings on internet. (HW used for benchmarking could be an issue as well )
    MS SQL server is derived from sybase.

    As for methods how to do this, then think about:
    1) fixed row size
    2) various denormalized summaries, e.g. in b-tree index can contain number of rows per particular key or per particular b-tree vertex for summary queries.
    3) commit/rollback implementation: oracle is optimized for insert/update (see block cleanout issue). Next thing is that getting block in query mode is pretty expensive as rollback segments has to be visited.
    Some systems just block select(at least MS SQL srv version 6? did that for me) , while oracle not, just gives ora-01555 sometimes, but this can be managed via rollback segments.
    4) quite advanced optimization and/or HW. A time ago I've been surprised by AS400 DB performance.


  2. #22
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    While it is true that Oracle and SQLServer keep leap-frogging each other in terms of performance, the same *cannot* be said about Sybase. While it is true that in v. 4.21, they were the same animal, they have diverged *massively* since then, with SQLServer capturing an ever larger portion of the market and Sybase slowly sinking into obscurity.

    As for DB2, it *is* a very impressive database (although you couldn't pay me enough to actually work with that dinosaur ). With the proper hardware, it can definitely out-perform Oracle. I was assuming, however, that these were similar hardware setups with the same data model on them.

    I *am* interested in the difference of indexes, as you and gtm mention. Sybase appears to have some different types. I'd like to know what they really do.

    I was also assuming that the rollback and blocking issues would *not* apply. Based again on the use of bitmap indexes, I was assuming that this was at least a read-mostly if not a read-only (batch-filled) database.

    I guess the real crux is: Is the sybase database consistently faster, or just on this query? After all, as people have shown, the proper multi-column index can be crafted to make *this particular query* run much, much faster. The problem is, of course, that that single-purpose index would probably not help many other queries. If it is only faster for this particular query, then how important is this query? If very important, add the index.

    - Chris

  3. #23
    Join Date
    Nov 2000
    Posts
    212
    well, comming back to the original query:

    it definetly gets to many blocks from DISK, and order of minutes to perform this query is not a surprise.

    In fact, I believe block gets can not be dramatically minimized (to get perfromance of seconds instead of minutes, this is 100 difference) if b-tree index is used : oracle still needs to scan at least leaf blocks of the index to calculate rowids. While in the query plan it also gets into table blocks...

    So the only way to optimize is to use denormalized stuff or may be bitmap indexes.

    From my understanding, bitmap indexes for this only query should be such that NO GETS OF TABLE BLOCKS are required at all: everything should be calculated from bitmap, and bitmaps are not large, they can fit into RAM.

  4. #24
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by LND
    what is interesting in this example is the fact that table is FIXED width.
    If sybase is able to utilize this fact then it is no surprise it is fast, for example:
    ...SNIP...
    This will probably not contribute anything usefull for the problem discused in this thread, but I'd like to add this because you repeat this "fact" in a couple of your replise here. What makes you think the record size of this table is fixed? with 99.99% accuracy I can say the records are of variable size in this table. For two reasons:
    - it was never said there could be no NULL values in the table
    - there are a couple of NUMBER columns which are never stored as fixed size

    Just my 2 cents....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #25
    Join Date
    Nov 2000
    Posts
    212

    fixed question

    [Jmodaic: fixed witd]
    I just made this assumption, based on the char usage in the table.

    In general it is better to look into sybase to ******** how it works (stores data, retrieves it, etc). Who knows, may be sybase's special indexes (gtm: HG (High Group ) and HNG(High Non Group)) can be modelled in oracle as well.


  6. #26
    Join Date
    Nov 2000
    Posts
    212
    see about sybase(and other) DW techniques:
    http://www.informationweek.com/556/56olbit.htm

    Quite interesting paper.

  7. #27
    Join Date
    Feb 2001
    Location
    Kolkata- India
    Posts
    356
    Create a binary index on Gender as this has very low cardinality.
    There Nothing You cannot Do, The problem is HOW.

  8. #28
    Join Date
    Nov 2000
    Posts
    212
    sudip is right, from what is in http://www.gca.net/solutions/whitepa...ve_server.html paper the only analogous thing in Oracle are bitmap indexes.

    The Sybase IQ is basically DW (readonly db), thus if Oracle's OLTP oriented DB is able to match Sybase IQ at least 10 times worse it will be quite ok.

  9. #29
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, if appropriate materialized views are created, then most of the queries as the one above (count-by-gender-by-zip) will finish in a fraction of a second instead of 7 minutes as it takes now. The question is, are you able to forsee the most probable column combinations/agregations for theis ad-hoc querying sistem.

    P.S. What I've found most interesting in the article was the date of the publication: December 1995
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #30
    Join Date
    Nov 2000
    Posts
    212
    Originally posted by jmodic
    P.S. What I've found most interesting in the article was the date of the publication: December 1995
    I read many interesting articles on Oracle dated for version6: short and clear, opposite to some V7,8,8i.

    However, is it possible and how to have similar performance on Oracle's bitmap indexes(since 6 years already passed) for DSS oriented DB, and ,gtm, what kind of hardware we are talking about?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width