Re: My woes continue

In article <33c3df20.21069889_at_resunix.sickkids.on.ca>,  owrkrj_at_mailhub.sickkids.on.ca says...

I would STRONGLY suggest that you use EXPLAIN PLAN on your complex query to see how Oracle is processing it. Hopefully your DBA can help you learn how to use EXPLAIN and how to interpret the output. In my opinion, knowledge of explain and the optimizer is a critical skill for anyone who writes SQL, both developers and DBAs.

90% of the performance problems that I see are related to design problems with either the data structure or with the SQL. This is very common when the database is designed before anyone has a handle on the types of queries will be run against it. Bad sql will still run poorly even on a tuned database.

There are lots of things that you can do to tune a query: you can add indexes to the tables; you can play with the syntax of the SELECT to change the access path that is used; you can use hints to tell Oracle specifically how you want the SELECT to be processed; you can create summary tables for frequently run queries. Before you do anything, you MUST know how Oracle is processing the query. Don't guess. Use EXPLAIN.

The process for tuning sql can be a bit tedious and confusing, but is by far the best think that you can do. When I have a complex query to tune, I break it down into smaller pieces and tune them individually. Then I can see if the problem is in the pieces, or in how they are brought together. You can get some pretty dramatic performance improvements, depending on what you are doing. Just last week I helped a developer take a 6 minute query and tune it so that it runs in under 3 seconds.

You should also learn how to use ALTER SESSION SET SQL_TRACE = TRUE and tkprof. These tools allow you to generate a trace file of your session and get statistics on how many resources are used by each step of the query.

Good luck.

>
>To all DBA's:
>My DBA is making my job rather difficult, but I'm having trouble
>exposing some of his incompetencies to my superiors... Should any of
>the following statements be true for a well-tuned Oracle 7.3.2
>database server running on an IBM AIX and serving an organization of
>50 users, about 15 of whom are data entry staff, and about 10 of whom
>run regular queries and reports:
>
>1) A complex query can 'crash' the database and cause corruption of
>data.
>
>2) Running Personal Oracle on a client (user) machine can 'crash' the
>database and cause corruption of data
>
>3) Running Developer/2000 on a client machine can 'crash' the database
>and cause corruption of data.
>
>My suspicion is that if any of these are true, there is a server
>tuning problem. My DBA is suggesting no queries be run while data
>entry is occuring (during business hours) and is threatening to remove
>Personal Oracle and Dev/2000 from my computer because of the damage
>I'm supposedly causing. Am I going crazy?--
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com Received on Wed Jul 16 1997 - 00:00:00 CEST