Re: Tune a *simple* join on a COUNT()

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0311200251.4b99384f_at_posting.google.com>...
> Hi,
> We have a view here whose query is as simple as
> > SELECT
> > <fields list>
> > FROM stuff stuff,
> > thing_stuff thst
> > WHERE stuff.thst_id=thst.id
> > /
> This view returns 108000 rows.
> STUFF contains 108000 rows
> THING_STUFF contains 96000 rows.
> The field THST_ID in STUFF is an FK that points towards
> THING_STUFF.ID. This field is indexed (check EXPLAIN
> PLAN below).
>
> When we perform a simple COUNT() (instead of specifying
> the required columns) it takes 17 to 20 seconds to get
> the result (108000). The EXPLAIN PLAN is the following:
> #SELECT STATEMENT Optimizer=CHOOSE (Cost=2819 Card=1 Bytes=10)
> # SORT (AGGREGATE)
> # MERGE JOIN (Cost=2819 Card=103571 Bytes=1035710)
> # SORT (JOIN) (Cost=1339 Card=96481 Bytes=482405)
> # INDEX (FAST FULL SCAN) OF 'THST_PK' (UNIQUE) (Cost=135
> # Card=96481 Bytes=482405)
> # SORT (JOIN) (Cost=1480 Card=103571 Bytes=517855)
> # INDEX (FAST FULL SCAN) OF 'STUFF_THST_FK_I' (NON-UNIQUE)
> # (Cost=190 Card=103571 Bytes=517855)
>
> We're on 8.1.7.4 on Sun/Solaris 8 on a Sun Enterprise E4500
> with 8 CPUs (but tens of other instances run on this server
> as well).
>
> I'd like to know if there are means of improving the response
> time on this count (I tried many hints but to no avail), the
> ideal would be to get an *immediate* result... Can one get at
> that ?
>
> Thanks a lot !
> Spendius

The expression 'tens of instances' probably tells it all. As the execution path is perfectly reasonable, I don't think you won't gain anything by hinting the statement. Or you must manage to parallelize your database, which also involves striping the disks, and making sure you are using as many disks as you have CPUs. But, as you run 'tens of instances' on a E4500 (which is AFAIK an entry level system), the server is probably suffocating, at least heavily faulting, and you will probably gain the most by buying more hardware and moving the 'tens of instances' elsewhere or this critical instance elsewhere or reconsider why you need 'tens of instances' on one single server, or consider getting a better instance of Oracle instances, so you find out for yourself why running 'tens of instances' is likely a pennywise/poundfoolish measure (Please check all that all apply)

Why are there so many people who think Oracle can be run properly on undersized hardware?

Sybrand Bakker
Senior Oracle DBA Received on Thu Nov 20 2003 - 08:12:21 CST