RE: Db Query Tuning
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Feb 2007 13:10:57 -0500
Some suggestions
1- Run a quick (say 5 min) set of statspack snapshots to get a picture
of what the db is doing
2- What does v$session_wait show?
3- Print out the spfile parameters and compare them line for line. Look
for differences in PQO, workspace management, optimizer.
4- If nothing is found using the above look at the disk farm.
-- Mark D Powell --
Phone (313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sanjay Mishra
Sent: Monday, February 19, 2007 11:53 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Db Query Tuning
Hi
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.
TIA
Sanjay
________________________________
Everyone is raving about the all-new Yahoo! Mail beta.
<http://us.rd.yahoo.com/evt=45083/*http://advision.webevents.yahoo.com/m
ailbeta>
Other related posts:
