Ray,
You are going to get lots and lots of feedback on a question like this.
But first, get yourself a couple of good books on performance tuning.
But before that, read and understand the Oracle Concepts manua
(available on OTN). Have you looked at:
- Size (number of) db block buffers
- Multi block read count
- Block size (you did not take the default 2k did you?)
- Indexes -- do you have enough and are those you have appropriate?
- Statistics -- are you stats complete? Did you gather stats using the
dbms_stats package or using analyze statement? -- Do you have stats on
your idexes too? -- Are your stats current.
- Have you enabled parallel query execution?
- Have you analyzed the query execution plans for some of your most
troublesome queries to determine if you can improve the query structure?
- If this is a DW, have you created any materialized views?
- If this is a DW, are you using a star schema? If so, look at the star
schema hints.
I am sure others will add to this list -- These are a few places to look
that I came up with in less than one minute. You and your team
DESPERATELY need to take a courses on Oracle database administration and
performance tuning. Oracle is, in my experience, a well performing
database but it must be operated properly.
--
Good Luck, and have fun
Andrew Allen
Ray Porter 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. 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
>
>
>
Received on Thu Feb 20 2003 - 09:29:22 CST
