Once upon a time, I had a developer come to my desk complaining that their query ran very poorly that day, Monday, and that it ran fine on Friday. And since we had a snapshot of the DB as of that Friday, the dev showed me. The dev also said that the only change was that WE changed the statistics on the tables and screwed up the query. WE screwed up the query.
Completely discounting the notion that stats "screwed up the query" (in this case), I took a look at the 11-table query. Not being able to really understand the joins betwixt all the tables, I fired up TOAD (v7.4 purchased version) and it's SQL Modeler on a test DB. I pulled in the 11 tables and duplicated the inner and outer joins graphically. After some rearranging of the tables, it became very clear that the problem was not the stats on the tables. All the new stats did was to let us know that a few of the joins were flat out wrong. In one case, parts of a segmented index were joined between two different tables because the second segment of the index is common to many tables. In another case, a table that was outer joined from one table was also inner joined to another table, givinging the optimizer fits. A few fixes later and the query now runs 5 times faster than before "the statistics broke the query". Sometimes it helps me to see a picture of a query rather than the text, especially if I'm not familiar with some of the tables (and a lack of RI). Not that it'll help all the time (e.g. Business Objects queries on an OLTP DB), but maybe some of the time. HTH! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -----Original Message----- Sent: Wednesday, April 02, 2003 8:04 AM To: Multiple recipients of list ORACLE-L Stephanie, I've ran into the exact same thing that statistics don't always tell the real story and that the bottom-line has to be elapsed time. I am curious however as to how you wrote the best performing (fastest) query. I have a client who uses the exact same structure and methodology you mention (innate tree structure, views with outer joins, etc) and they have some pretty long and nasty queries (I see your 176 step execution plan and raise you to 298!). I've had pretty decent success re-writing these monsters but would be interested in hearing your different approaches to re-writing such queries to see if I can pick up a few ideas I hadn't thought of. Any generic ideas or methods you use might prove helpful to many. Thanks, Karen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
