Re: Db Query Tuning
- From: Sanjay Mishra <smishra_97@xxxxxxxxx>
- To: Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx>
- Date: Tue, 20 Feb 2007 08:27:20 -0800 (PST)
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
Tx
Syed Jaffar Hussain <sjaffarhussain@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
(and love to hate): Yahoo! TV's Guilty Pleasures list.
--
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."
---------------------------------
Sucker-punch spam with award-winning protection.
Try the free Yahoo! Mail Beta.
Other related posts:
