Re: tunning hellp!!

I think you should definitely do an explain plan.

I'm guessing that you are doing an index join on the two indexes on n(applicationid) and n(instanceid). Can you combine these into a composite index n(instanceid,applicationid)?

I would switch to using an IN clause instead of an EXISTS so that the subquery is only executed once instead of for every matching row.

In article <3861E3E5.6C92C63_at_totalise.co.uk>,   Martin Hepworth <maxsec_at_totalise.co.uk> wrote:
> Hi
> A few thoughts.
>
> If you are running the 'choose' optimiser have you analysed the tables
> recently?
>
> Do the 'exists' first in the 'from'.
>
> run trace/explain plan (or use toad www.toadsoft.com) to the do the
same
> so you can see whats actually happening.
>
> try a outer join rather than the union.
>
> Get the O'Reilly book on Oracle Performance Tuning - excellant SQL
> tuning sections.
>
> martin
>
> ibm_97_at_yahoo.com wrote:
> >
> > Oracle 8i for Solaris.
> >
> > 'n' and 'a' table are big, each has 1 million rows.
> > The other tables are small, the biggest one is 100 rows.
> > 'test' and 'test1' only have 2 rows, 5 rows.
> >
> > I create indexes on those columns:
> > n(nfalarmid), n(applicationid), n(instanceid), n(eventid)
> > a(eventid)
> > m(nfalarmid), m(nfalarmgroupid)
> > g(nfalarmgroupid)
> >
> > Do I have to create index g(description)? I also use 'exists'
instead
> > of 'IN'. Any performance gain could be get? Thanks a lot!!
> >
> > select g.description, count(*)
> > from nfevent n, nfalarmgroups g, nfalarmgroupmappings m
> > where m.nfalarmid=n.nfalarmid
> > and n.applicationid = 1
> > and n.instanceid = 1
> > and m.nfalarmgroupid = g.nfalarmgroupid
> > and exists(select 'x' from test t where
t.test=g.nfalarmgroupid)
> > group by m.nfalarmgroupid, g.description
> > union
> > select g.description, SUM(a.bytes)
> > from nfevent n, pixappdetails a, nfalarmgroups g,
nfalarmgroupmappings m
> > where n.eventid = a.eventid
> > and m.nfalarmid=n.nfalarmid
> > and n.applicationid = 1
> > and n.instanceid = 1
> > and m.nfalarmgroupid = g.nfalarmgroupid
> > and exists(select 'x' from test1 t1 where
> > t1.test1=g.nfalarmgroupid)
> > group by m.nfalarmgroupid, g.description;
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 26 1999 - 11:51:32 CST