improving performance in a decision support database

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. 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. Performance is much slower on some screens in the GUI (particularly those screens that use the database to load list boxes, etc.) 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. 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).

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; 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.

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

--
================================
Ray Porter
Applications Analyst Programmer
Administrative Information Services, UNC-CH
Phone: (919) 966-5878
Fax: (919) 962-5840
Email: ray_porter_at_unc.edu
Web: http://www.unc.edu/~dragon