Path: news.easynews.com!easynews!sjc-peer.news.verio.net!news.verio.net!newsfeed.berkeley.edu!ucberkeley!sjc1.nntp.concentric.net!newsfeed.concentric.net!global-news-master
From: "Jim Poe" <jpoe@fulcrumit.com>
Newsgroups: comp.databases.oracle.server
Subject: query performance
Date: 10 Oct 2001 20:44:01 GMT
Organization: Concentric Internet Services
Lines: 23
Message-ID: <9q2buh$7bp@dispatch.concentric.net>
NNTP-Posting-Host: 208.177.224.80
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
Xref: easynews comp.databases.oracle.server:120856
X-Received-Date: Wed, 10 Oct 2001 13:43:49 MST (news.easynews.com)

I have two databases, TEST and DEVL.  I open TOAD and log into DEVL and run
a typical query out of our application.  It takes 2 seconds to return the
result.  I log into TEST and run the same query.  It takes 177 seconds for
the query to return the result set.

The view that the query is based on contains 106 rows in DEVL and 165 rows
in TEST.

I have compared all the Oracle parameters of both databases.  I only had a
few differences including open_cursors, shared_pool_reserved_size, and
shared_pool_size.  In all cases the TEST database had a larger value than
DEVL.  The differences were less than 10% except with open_cursors.  TEST
had 500, DEVL had 300.

What would explain the difference in performance between the two databases?
What else should I be looking at?

Thanks for any help.

--
Jim Poe (jpoe@fulcrumit.com)