QUERY TUNING

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 09 Aug 2001 16:58:05 -0700
Message-ID: <F001.00365E70.20010809103550@fatcity.com>

Hi,

We have a query which is taking a long time as compared to time taken last month......
this query does a select on view1..this view1 is based on join oof 2 views view2 and view3..
view2 is based on join of 6 tables while view3 is based on 9 tables..with each table have significant number or rows about 200000.......it is now taking about 150 sec to execute while it used to take 45 sec......
--we r using COST based optimizer.........

• i rebuild all the indexes on tables involved......
• i make of copy of production machine on test machine with export/import so no fragmentation on test machine still takin 120 sec on test machine as well......
• schema is analyzed with compute clause........
  --indexes r properly build.....explain plan showing it is using all the
  indexes properly and doing nested loop..........
• SGA is propely sized w.r.t to RAM................. What else to check for this performance degradation........... i am only working on test machine so no other user intervention.........

Thanks
-Harvinder
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Harvinder Singh
  INET: Harvinder.Singh_at_MetraTech.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Aug 09 2001 - 18:58:05 CDT