Re: performance

Stephan wrote:
> Oracle 8.1.7 NT2000:

OK good, fine, thanks for including!

>
> Running 2 databases on the same machine, 'same' init parameters.

Hmmm, and these are? BTW, running both instances simultaniously?

>
> - 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).

Should have no impact at all. You do not "load" tables when not used (the odd database startup trigger aside, that 'pins' 'em in memory - not likely)

>
> * 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....
>

As expected, see above. Something else going on, like swapping? Fire up your process manager, and see how much memory is used. Compare that to the memory installed.
See if the same thing haooens when you start with querying db1, and db2 later, after a fresh boot.
And: is login time included, or not? Did you run the query just once, or is the timing done on the second or third execution? This would give Oracle a chance to cache what it can cache.

Frank Received on Mon Dec 30 2002 - 15:08:17 CST