Re: Db Query Tuning

Sanjay,

I do agree with *Wolfgang Breitling.*

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

http://jonathanlewis.wordpress.com/2006/11/27/event-10132/

On 2/19/07, Sanjay Mishra <smishra_97_at_yahoo.com> 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.
>
> Tx
>
> *Wolfgang Breitling <breitliw_at_centrexcc.com>* 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.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
>
>
> ------------------------------
> We won't tell. Get more on shows you hate to love<http://us.rd.yahoo.com/evt=49980/*http://tv.yahoo.com/collections/265%0A>
> (and love to hate): Yahoo! TV's Guilty Pleasures list.<http://us.rd.yahoo.com/evt=49980/*http://tv.yahoo.com/collections/265%0A>
>
>

-- 
Best Regards,
Syed Jaffar Hussain
Oracle ACE
8i,9i & 10g OCP DBA

http://jaffardba.blogspot.com/
http://www.oracle.com/technology/community/oracle_ace/ace1.html#hussain
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."

--
http://www.freelists.org/webpage/oracle-l