eric@pyramid.pyramid.com (Eric Bergan) (03/20/90)
In article <1990Mar14.205620.11636@lia> thomas@lia.UUCP (Chris Thomas) writes: >We are evaluating a number of database products for inclusion in a >distributed network of a few hundred Sun 4s and SPARCs, and I've >noticed some amazing differences in performance between Oracle >(version 6) and Sybase. > >In one of our tests, we perform a join between two tables which, >together, contain 150k rows of 46 bytes each -- about 600 of the rows >filter through the join. In Sybase, this completes in anywhere from >3 to 7 minutes (depending on other load), but Oracle seems to take >several -hours- to do the same thing. > >In running some further tests with Oracle, we've used smaller >databases (10% of full size, 20%, etc.), and there appears to be >a sharply exponential relationship between table size and time. Almost certainly, Sybase and Oracle are picking different query execution plans for the join. Sybase has a cost-based optimizer, which means it tries to calculate the costs of each of the plans, and then picks the "best". Oracle uses some heuristics based on what the query "looks like", and goes from there. Typically, you can effect the query plan Oracle chooses by varying the order the tables are listed in the FROM clause, and I also believe varying the order of the conditions in the WHERE clause will also effect it. Whether you consider this to be "cheating" or not depends on what your environment will look like. If it will be lots of ad hoc queries, it is obviously too much to expect your users to do this. If you are doing an application with "fixed" transactions, then the re-ordering of query predicates may not be a significant deal. The exponential relationship just indicates that Oracle is using an O(n**2) algorithm for the join. Probably means Oracle is doing the join first, then the restriction, while Sybase is restricting first, then joining. By the way, I have seen various join-intensive benchmarks where any given database system beats the others. (I.E. Oracle over Sybase, Sybase over INGRES, INGRES over Oracle, Informix over whomever, etc.) No optimizer implementation is 100% effective, and there is always a query that can trip it up. -- eric ...!pyramid!eric
sjm@ohsuhcx.ohsu.edu (Sandra McMaster) (03/21/90)
In article <106035@pyramid.pyramid.com> eric@pyramid.pyramid.com (Eric Bergan) writes: >In article <1990Mar14.205620.11636@lia> thomas@lia.UUCP (Chris Thomas) writes: >>In Sybase, this completes in anywhere from >>3 to 7 minutes (depending on other load), but Oracle seems to take >>several -hours- to do the same thing. > > Almost certainly, Sybase and Oracle are picking different >query execution plans for the join. There is an EXPLAIN facility (mostly undocumented, I believe) in Oracle V6 which will allow you to determine how the query is being executed. I have a paper which explains the EXPLAIN facility. Let me know if you are interested... I'll check with the author and see if I can duplicate it. Sandra McMaster Oregon Health Sciences University sjm@ohsu.edu {nosun, tektronix, ogicse, qiclab}!ohsuhcx!sjm
tslee@oracle.uucp (Terry S. Lee) (03/22/90)
In article <404@ohsuhcx.ohsu.edu> sjm@ohsuhcx.UUCP (Sandra McMaster) writes: >In article <106035@pyramid.pyramid.com> eric@pyramid.pyramid.com (Eric Bergan) writes: >>In article <1990Mar14.205620.11636@lia> thomas@lia.UUCP (Chris Thomas) writes: >>>In Sybase, this completes in anywhere from >>>3 to 7 minutes (depending on other load), but Oracle seems to take >>>several -hours- to do the same thing. >> >> Almost certainly, Sybase and Oracle are picking different >>query execution plans for the join. > >There is an EXPLAIN facility (mostly undocumented, I believe) in >Oracle V6 which will allow you to determine how the query is being >executed. I have a paper which explains the EXPLAIN facility. Let >me know if you are interested... I'll check with the author and see >if I can duplicate it. > >Sandra McMaster >Oregon Health Sciences University >sjm@ohsu.edu >{nosun, tektronix, ogicse, qiclab}!ohsuhcx!sjm EXPLAIN and TKPROF are documented in Chapter 7 of "Performance Tuning Guide", Part # 5317-v6.0
bgh@ice9.uucp (barry hannigan) (03/22/90)
[stuff deleted] >EXPLAIN and TKPROF are documented in Chapter 7 of "Performance Tuning Guide", >Part # 5317-v6.0 and of little use in real life. explain [sql] examines the execution trace without actually running the statement. i much prefered the trace() statement which could be included in sqlforms to examine everything. cryptic output but when isnt it. when i was taught sql, one of the driving points was that it was an non-procedural language. apparently that was marketting stuff. -- return path = uunet!alberta!ncc!ice9!bgh