Re: performance

"Stephan" <test_at_test.com> wrote in message news:<QgmQ9.3$rz1.19222_at_nlnews00.chello.com>...
> -> I am running 2 instances simultaniously
> -> Memory is no problem, so swapping does not occur
> -> There are two tablespaces: 100Mb each, storage: initial 250K next 250K
>
> Today I loaded the two tables in the scott/toger default 8.1.7 ORCL
> database.
> As I expect, the performance is perfect on that one.
> I installed again the full scheme in another instance, selected from the
> same tables
> using same query and response is bad...
>
> -> So it seems the other tables DO influence...

Were there many more records in one of the tables at one time? You seem to be doing a cartesian join between the tables, so if one or both of the tables has a high high-water mark the full table scan could take a long time. Try truncating (not dropping) and reloading both tables, and rebuild the index.
>
> Stephan
>
>
>
> "Stephan" <test_at_test.com> wrote in message
> news:Jq%P9.23$Ua7.11811_at_nlnews00.chello.com...
> > Oracle 8.1.7 NT2000:
> >
> > Running 2 databases on the same machine, 'same' init parameters.
> >
> > - in database 1 I imported the full user-scheme (say 30 tables).
> > - in the database 2 I imported only 2 tables of that user-scheme (one
> > having
> > 20 records, the other 2000).
> >
> > * When I select from database 2 by means of:
> > "select id_district, district_name FROM district, district_street where
> > (district_id=id_district) AND (place_street_id=707)"
> > the performance is good.
> > * Running the same query against database 1 gives very poor
> > performance: 2
> > seconds for retrieving the 3 records!!
> >
> > The explain-plan for the querie is the same on datab1 and datab2.
> > SELECT STATEMENT Cost = 3
> > HASH JOIN
> > TABLE ACCESS FULL DISTRICT
> > INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE
> >
> > When I drop all objects from database 1 execept the 2 tables, the
> > performance stays poor....
> >
> > Please help me!
> >
> > Thanks
> > Stephan
> >
> >
> >
> >
> >

jg

--
@home is bogus - "I was consumed with maternal guilt.  I know now that
it was virtually impossible for me to take advice and make decisions
when I was responding emotionally as a mother, not thinking rationally
as a public official." - Governor Jane Swift