RE: Db Query Tuning

From: Sanjay Mishra <smishra_97_at_yahoo.com>
Date: Mon, 19 Feb 2007 12:10:08 -0800 (PST)
Message-ID: <118659.96695.qm@web51314.mail.yahoo.com>

Mark

Thanks for the email and I had checked Step 2 and 3. Step 2 was showing several Idle Events and 70% are SQL*Message to/from the client, few Rdbms on QA where lots of user are trying to run the application executing those 20 queries. I ran the query manually after I refresh the DB and collected the STATS and it worked fine and I got all the results but after few hours it starts hanging and then afterward, it has never shown me the same result even I bounced the database. As users are trying the stuff again and again and I found there are so many session running the same query.

Some of the hanging queries are having severalsubqueries in single sql and so is there any way I can check as what portion it is actually hanging. Like using any oradebug or so . I tried to start a SQL session and then put oradebug on it and ran the query , but I am not able to see trace file in any destination.

I don't know if snapshot can help as my Dev environment is totally idle but will see if I can get some good understanding from the statspack snapshot.

Tx
Sanjay

"Powell, Mark D" <mark.powell_at_eds.com> wrote:

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra Sent: Monday, February 19, 2007 11:53 AM To: oracle-l_at_freelists.org
Subject: Db Query Tuning

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.

Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.

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

Received on Mon Feb 19 2007 - 14:10:08 CST