Re: Performance

Stephan wrote:
> ======================================================
> The total story till now:
>
> Win2000
> 8.1.7.
>
> - I started having 1 instance called PREV
> - In PREV, I imported the PREVENT scheme containing about 30 tables
> - I analyzed (COMPUTE) the PREVENT scheme
> - I query from only 2 tables of that PREVENT-scheme (one -DISTRICT- having
> 20 records, the other -DISTRICT_STREET- 2000).
> - When I select from the PREV database 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 between 200msec and 2sec for generating 3 records (poor)!
>
> - In the PREV database I created a copy of the district table by means of:
> "create table district2 as select * from district"
> - I analyzed (COMPUTE) the PREVENT scheme again
> - When I select from the PREV database by means of:
> "select id_district, district_name FROM district2, district_street where
> (district_id=id_district) AND (place_street_id=707)"
> the performance is between 1msec and 10msec for generating the 3 records
> (good)!
> ==============================================
> So why is the performance using the imported table (20 records!) so bad?
> (does Oracle treat it different than the copy-table? conversion?)
>
>
> Please help...
> Stephan
>

 From your post, it looks like the district and district2 tables are identical. Since that's the case, it's probably not the table that's causing the slowdown. What else enters into the picture? Well, unfortunately, lots of things ... none of which we can guess from your post. But here are some of the things to check (and by all means, do an explain plan):

1. You didn't mention anything about which optimizer you're using ... though I hope it's the cost-based optimizer since you're at 8.1.7 (instead of one of the older versions that had trouble with CBO). If your init.ora doesn't specify CBO, then the analyze will be for naught.
2. You didn't mention anything about indexes. Do you have the same indexes on both tables? On such small tables, an index would be a hindrance instead of a help (though CBO should be able to determine this). Explain plan will tell you.
3. You didn't mention if both test runs are done on a "quiet" system (so you'd not be comparing apples and oranges). Don't want to penalize the slow job because it was competing with a large batch job.
4. You didn't mention whether you ran each test only once ... or if you ran the faster one twice (in which case you'd have the district table - and maybe the district_street table - in cache). In fact, if you were on a quiet system and ran the faster query after running the slower one, you'd probably have both tables in SGA cache anyhow (they are pretty small, unless you have a very small SGA defined). Do you get the same results if you run the queries in the opposite order?