Re: Re. Re. Quality of Index
>Subject: Re. Re. Quality of Index >From: PHW@langley.softwright.co.uk (Peter Whittleton) >Date: 7 Feb 1997 11:43:34 -0500 >Message-ID: <5dfm3m$dee@cssun.mathcs.emory.edu > > >> >> 2) run "select * from syssqexplain order by sqx_estcost desc" from the >> sysmaster database. This will display your current queries in the engine >> by cost. By studying that, you will be easily able to see where your >> costs are being incurred. DO NOT RUN THIS ON ANY ENGINE PRIOR TO >> 7.13!!!!!!!!! >> >>>>> >Strictly speaking this is not correct! What this will show >you is what the optimiser thought the cost was going to be >BEFORE it executed the query, not what actually happened.> > >And we all believe the optimiser knows best don't we ;-} > >Seriously, I have had (and still have) real problems trying >to figure out eg. which is the better of two queries, whether >a query will run faster with or without a certain index, etc. >All help, advice, etc., gratefully received. > >incidentally Madison, what happens to your query on the >syssqexplain view if you do run it on a pre-7.13 engine? > >Peter Whittleton (whittle@ssax.com) What Peter said is true. The estimated cost is not the real cost. However, the estimated cost is what the optimizer is using to determine which querry plan is going to be used. I've used this technique several times to isolate major performance problems that customers are encountering. Generally ( and I emphasize Generally ), by studying the more expensive querries and trying to understand why they are expensive, insite can be gained in how to improve their performance. This is especially true if these expensive queries occur several times in the engine (by different sessions). I've found examples where adding a single made a vast improvement. Also, I've found several situations where customers were doing Cartesian Joins that they were unaware of. The reason I suggested that this NOT be used in prior 7.13 versions is that there were some problems with this pseudo table which resulted in occasional SEGVs. Madison Pruet