troubleshooting Question

Troubleshooting tips for slow performance

Avatar of DalTXColtsFan
DalTXColtsFan asked on
Oracle Database
8 Comments4 Solutions4632 ViewsLast Modified:
Greetings experts,

My company's database application is, in all honesty, not a particularly complicated one.  It's straight up client/server, we support Oracle 9i and Oracle 10g, and we use OLEDB on the Windows XP Professional clients to communicate with the database.

To this point in my career I have never had to worry about performance tuning.  The client's DBAs and network analysts always took care of that for us, and it being such a relativity simple architecture and database, performance was not typically a problem.

I would like to add to my skillset at least being able to make SUGGESTIONS for improving performance - it's never my responsibility, but still, it can only help me to at least have a basic bag of tricks!

Details:

We have a simple client/server setup that accesses a database with tables called Order, OrderProduct, and Product.  
All tables have a field called DBKey (a unique rowID populated by a sequence in a trigger)
The Product table also has fields like UPC, Price, Category, Name, Size etc.  (our actual product table has about 400 fields)
OrderProduct has DBParentProductKey, DBParentOrderKey, Quantity and (for the time being) nothing else.
Order has Date, Place, CustomerName etc.
All tables have an index on DBKey.  Product has an additional index on UPC.
Product will typically have 500,000 rows, Order will typically have 2,000 rows, OrderProduct will be approximately 40,000 rows (assuming an average of about 20 products per order).
The user is presented the list of orders, the user picks one, then the OrderProduct table is queried to get the corresponding Product DBKeys and the list of products and all of their fields is shown to the user.
We require that the client DBA create the database and the tablespaces, then our installation script creates all of the tables, views, procedures, indexes etc.
We require that the client be in charge of its network.

Obviously our app is not THIS simple but I wanted to give something for reference.


So the question I have is, if I have one client for whom certain operations are taking significantly longer than similar operations for other clients (i.e. just opening an order!), what are some suggestions I can make for attempting to improve performance?

I will be generous with points on this one for multiple answers- I'm just trying to have SOME tricks in my pocket!

Thanks
Joe
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 4 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros