Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Db Query Tuning

Re: Db Query Tuning

From: Sanjay Mishra <>
Date: Tue, 20 Feb 2007 08:27:20 -0800 (PST)
Message-ID: <>

Thanks Jaffar for the events name in 10g. This is currently 9204 on both dev/QA/prod. I am comparing the Trace and see if something visible along with Statspack    


Syed Jaffar Hussain <> wrote:


  I do agree with Wolfgang Breitling.   

However, if you are on 10g, I would suggest to enable event 10132 in place of 10053.      

  On 2/19/07, Sanjay Mishra <> wrote: Thanks Breitling for great details. let me run the 10053 on the queries as well as follow some of your other suggestion. One interesting point , I have seen that If I make some change in Query Bid variable places, it work with some index hint but will not work for other data. If you have any advice on it.    

  I will also try to import stat from QA to dev and see if it improve.    


Wolfgang Breitling <> wrote:   I can't even begin to speculate what is happening without a lot more information. Just a few comments:
- export/import is not a good way to refresh qa (and probably dev) from production. It repackages all tables which will generally make them smaller and can dramatically affect the clustering factor of indexes. It may also sort the table contents which again has an impact on the clustering factor. You should clone especially your QA database so that it is an exact replica of production (at that time). - Are you using system statistics? If yes, are they the same? If not why not? (that applies to both prior questions). - When copying statistics you should go the other way and copy the prod statistics to QA and dev. It doesn't help you if you fixed your queries using the dev statistics and then the same thing happens when you move the code to production.
- There is no such thing as explain plans being "more or less the same". Either they are (the same), or they are not. - To find the reason for the differences explain the problem queries with the 10053 event trace enabled in both dev and QA. Then compare the traces. Compare forst of all the optimizer parameter and make sure they are the same. Then look for the cardinality estimates for each base table ( in the single table access path portion). If there are significant differences look for differences in the statistics to explain the different cardinality estimate.

At 09:52 AM 2/19/2007, Sanjay Mishra wrote:
>I had faced an interesting scenario yesterday when I moved some code
>from Dev to QA and tried to execute the queries, they behaved very
>differently. QA was newly refreshed with the data using
>Export/Import. I had clalcuated the Stat using 9i DBMS_STATS and
>auto sample size. I had even copied the Stats from dev to QA to make
>sure that stats are same but it still failed. The only difference
>between Dev and QA is that QA might have few more record than Dev as
>Dev was refreshed from Prod one week BAck. Anyway Data will continue
>to change in Production and so I don't think that one week Data
>change shoudl be an issue. There is no Hints been used and all
>init.ora setting including sga size are same.
>I had a script of around 15-20 query and each query is very big
>about 5-20 pages. Half of them are working fine while others are
>hanging. Trace will also show that it is running but I appreciate
>if you can provide any insight as what else can be done, Explain
>Plan is more or less the same except that their sequence are
>different and I don't understand as why it happen when even Stats
>are new or even copied using dbms_stats.export.. procedure.


Wolfgang Breitling
Centrex Consulting Corporation     

  We won't tell. Get more on shows you hate to love (and love to hate): Yahoo! TV's Guilty Pleasures list.   
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA
"Winners don't do different things. They do things differently." 

Sucker-punch spam with award-winning protection.
 Try the free Yahoo! Mail Beta.
Received on Tue Feb 20 2007 - 10:27:20 CST

Original text of this message