Re: improving performance in a decision support database

On Thu, 20 Feb 2003, ray_porter_at_unc.edu wrote:
> Hi everyone, We just migrated a moderately large application from
> Sybase to Oracle (management mandate, not my choice). There is a GUI
> (written by me) developed with Borland Delphi and using ADO to access
> the database and many users use MS Access via ODBC to build ad hoc
> queries against the database. Oracle is new to our Data Management
> Group and they admit they don't have all the answers when tweaking an
> Oracle database, particularly one where users are running large ad hoc
> queries. We are finding that performance over all is slower than with
> the Sybase version -- significantly slower in some places. The
> database structure remained essentially the same with the exception
> that DM required us to only give the users access to views to the base
> tables.

This shouldn't be surprising. Your application was coded, tested and performanced tuned for the Sybase backend. You actually expect that you could just create the same schema and code in Oracle, point the Sybase enhanced application at it and it would magically just perform as well or even better in the Oracle environment?

The nice thing is that you want to find out what is going on. It is your management that actually believes that this could happen. You would rather just stay on the Sybase world so you don't have to go through this painful learning process forced on you by this port.

Well, stick with it. Oracle is a great product and you might find that you will grow to really like it, but right now, its hard to believe it cause you are frustrated.

> We also used stored procedures to retrieve datasets from the database
> in the Sybase version because Sybase stored procedures easily return
> datasets and give a big (10:1 at least) performance boost. I've been
> told by the folks at Oracle that Oracle stored procedures do not
> provide any performance boost over embedded sql.

Certainly not 10-1.

> Performance is much slower on some screens in the GUI (particularly
> those screens that use the database to load list boxes, etc.)

Can you post an example of a list box filling query?

> and many of the ad hoc queries the users create and run via Access are
> several times slower than the same queries running in Sybase.

I'd like to explain the concept of bounded variables.

Take the following query.

    select *
    from account
    where account_id = 10;

Now, rewrite this query, (ie, another user's query)

    select *
    from account
    where account_id = 11;

These are completely different queries because of the 10 and 11.

Now consider what Oracle must do to execute a "new" query.

    Is the SQL syntactically correct?
    Do the objects referenced exist?
    Does the user executing the SQL have access to the objects referenced?     (Other such questions)
    Now, how should I go about actually retrieving this data from disk?     (Iterate many different performance options)     Now, go get the data.

Okay, Oracle has to do those steps for every new piece of SQL. But, now consider if both queries above where written like so. (SQLPLUS syntax)

        variable acct_id number;

    The following replaces the first query;

        exec :acct_id := 10;

        select *
        from account
        where account_id = :acct_id;

    The following replaces the second query;

        exec :acct_id := 11;

        select *
        from account
        where account_id = :acct_id;

These are now the same query and Oracle will execute these very quickly because it will have stored all of the "compilation steps" and use them each time it sees this query.

So, this could be a huge change for your application, but binding of variables can go a long way in helping boost performance while non-binding of variables can go a long way in slowing performance.

> Some simple queries are comparable though everything is at least
> marginally slower. The database also got physically much larger in
> Oracle, despite maintaining the same database design (Sybase less than
> 2GB, Oracle almost 5GB).

Not sure here. This seems quite odd. Could be that your parameters of the page level parameters for your tables on Oracle are setup quite different than Sybase's and Oracle is storing much less data per page?

This could help in explaining some performance differences as well. When retrieving from disk, a database doesn't scan data, it scans disk drives, finds the appropriate pages, loads them into memory and then scans for the appropriate data. Having to "scan" more pages than a competitor to get the same data can greatly degradate side-by-side performance comparisons.

> I'm looking for suggestions I can pass on to our DM group to hopefully
> improve our performance to the point where Oracle (8.1.7) at least
> matches the performance of our Sybase database. For example, there's
> a screen in the GUI that allows the user to search for data by person
> name. It creates a query using wild cards in the WHERE clause to
> allow the user to enter partial names (for example, "POR" when they
> want to find Porter, Porterfield, etc.). Searching for "Smith" in the
> Sybase version completed in less than 20 seconds. Searching for
> "Smith" in the Oracle version takes in excess of q minute. The SQL
> submitted is actually something like this:
>
> SELECT field1, field2, field3
> FROM NAMES
> WHERE SORT-NAME LIKE 'LAST%FIRST%'
> ORDER BY SORT-NAME;
Oracle isn't going to be able to use a standard index for this one. Look up function based indexes.

> The degradation of performance in the GUI is acceptable (though not
> good) in most cases but the loss of performance in user-created ad hoc
> queries is a real sore point with the users. Queries that they
> created and ran in Sybase with execution times in the 1 minute range
> are taking 5-10 minutes in Oracle. The queries that are taking the
> longest are those with sorting or grouping.

These performance degradations won't come out of binding issues. These are related to the paths of execution that Oracle is choosing and you need to compare it to the paths that Sybase already chooses.

Look up explain plan and how to use it. I would bet that if you were to work with the users a bit, have them send you the queries they are executing which are taking too long, you could run an explain plan on it from your own sqlplus session, then go to sybase and ask it to tell you what paths it would take. When you found that out, then you could come back and we could help you a little better.

Sounds to me that you have table scans when you want indexes used.

> Any suggestions will be most welcome. Our DM group really hasn't been
> able to offer anything useful.

Sounds like your DM group heard the hoopla surrounding Oracle and bit onto it hard without understanding what it was they wanted from Oracle that Sybase couldn't provide.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.