Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!l75g2000hse.googlegroups.com!not-for-mail
From: "fitzjarrell@cox.net" <fitzjarrell@cox.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: Help with sql query speed. Explain plan included
Date: 18 Mar 2007 17:23:12 -0700
Organization: http://groups.google.com
Lines: 64
Message-ID: <1174263792.000667.81260@l75g2000hse.googlegroups.com>
References: <1173964477.511820.149350@n76g2000hsh.googlegroups.com>
   <1173973783.877883.51310@y66g2000hsf.googlegroups.com>
   <1174234129.055233.12440@o5g2000hsb.googlegroups.com>
   <1174234439.202646.138400@n59g2000hsh.googlegroups.com>
   <1174239550.893300.203650@l75g2000hse.googlegroups.com>
   <1174240687.027809.235250@o5g2000hsb.googlegroups.com>
   <1174244188.656948.74240@y66g2000hsf.googlegroups.com>
NNTP-Posting-Host: 72.192.67.107
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1174263793 6321 127.0.0.1 (19 Mar 2007 00:23:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 19 Mar 2007 00:23:13 +0000 (UTC)
In-Reply-To: <1174244188.656948.74240@y66g2000hsf.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: l75g2000hse.googlegroups.com; posting-host=72.192.67.107;
   posting-account=B_KC8Q0AAADcSTVy4DZ59utFaLrVLOo4
Xref: usenetserver.com comp.databases.oracle.server:424363
X-Received-Date: Sun, 18 Mar 2007 19:23:13 EST (text.usenetserver.com)

On Mar 18, 1:56 pm, "Charles Hooper" <hooperc2...@yahoo.com> wrote:
> On Mar 18, 1:58 pm, JL19...@HOTMAIL.CO.UK wrote:
>
> > If I recall correctly, Cary Millsap wrote something about Obsessive
> > Tuning Disorder and I think this is what you are talking about too. 70
> > seconds was a show stopper - 10-20 seconds is far better. At least no
> > one suggested we restructure our data. We were prepared to do it but
> > wanted to canvas opinion on tuning rather than software rewrites to
> > start with.
>
> > Thank you again.
> > John
>
> You might be thinking of Gaja Krishna Vaidyanatha (author of "Oracle
> Performance Tuning 101"), rather than Cary Millsap.  Compulsive Tuning
> Disorder is a serious problem.  However, you have not yet reached that
> stage with this problem.
>
> I suspect that if you can eliminate the full tablescan that is
> requiring the processing of  278MB (system has become IO bound), you
> will likely find that the query executes in less than one second.  I
> suspect that with a SORT_AREA_SIZE of 31MB and a HASH_AREA_SIZE of
> 293MB, it will not take but a couple sessions connecting to the
> database and executing SQL statements before the server experiences
> severe memory problems.  My second post (and possibly the first as
> well) suggested two indexes: one index on the RESIDENTID column and a
> second, composite index on the QUESTIONNUMBER and ANSWER columns.
> Since this is a test environment, try creating indexes on those
> columns and then use DBMS_STATS.ANALYZE_TABLE_STATS with the CASCADE
> option set to TRUE to gather statistics.
>
> TKPROF likely does not know what to do with a 10104 trace.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

I can understand creating a composite index on the QUESTIONNUMBER and
ANSWER columns but I fail to see the benefit of having an index on the
RESIDENTID column as the pertinent portion of the WHERE clause reduces
to, essentially:

 where  t1.residentid!=1486674

as proven by the posted query plan.  This returns every row in T1
except 1 and Oracle should be 'smart' enough to not use the index for
that type of condition.  Perhaps rewriting the WHERE clause might also
help:

   where (t0.questionnumber=tx.questionnumber
              and t0.answer=tx.answer)
   and t0.residentid=1486674
   and t0.residentid!=tx.residentid

although with 10g the CBO may already have done that.

The composite index may help the situation by only returning those
RESIDENTID values where the answers match those of the selected
record, thus reducing the size of the filtering operation.  And an
index won't speed filtering.


David Fitzjarrell