Re: performance: new info

From: Stephan <test_at_test.com>
Date: Wed, 01 Jan 2003 15:44:08 GMT
Message-ID: <c1EQ9.420$YQ1.396070@nlnews00.chello.com>

Hi,

The total story till now:

I started having 1 instance called PREV and 1 (default/auto created) instance (scott/tiger) called ORCL
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 BAD AND the sort of generated records is random! The execution plan: SELECT STATEMENT Cost = 5 SORT UNIQUE HASH JOIN TABLE ACCESS FULL DISTRICT INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE
In the scott/tiger ORCL database I created a dbLink to PREV
In the scott/tiger ORCL database I created the two tables by means of: "create table district as select * from district_at_rem_scheme" "create table district_street as select * from district_street_at_rem_scheme" I also created the index "DISTRICT_STREET_UNIQUE"
I analyzed (COMPUTE) the scott scheme
When I select from the ORCL 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 GOOD AND the sort of generated records is always the same! The execution plan: SELECT STATEMENT Cost = 5 SORT UNIQUE HASH JOIN INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE TABLE ACCESS FULL DISTRICT
In the PREV database I created a dbLink to ORCL
In the PREV database I created two tables by means of: "create table district2 as select * from district_at_rem_scheme" "create table district2_street as select * from district_street_at_rem_scheme" I also created the index "DISTRICT_STREET_UNIQUE2"
I analyzed (COMPUTE) the PREVENT scheme
When I select from the PREV database by means of: "select id_district, district_name FROM district2, district_street2 where (district_id=id_district) AND (place_street_id=707)" the performance is GOOD AND the sort of generated records is always the same! The execution plan: SELECT STATEMENT Cost = 5 SORT UNIQUE HASH JOIN INDEX FAST FULL SCAN DISTRICT_STREET_UNIQUE TABLE ACCESS FULL DISTRICT

If I query in PREV on the 2 tables the performance is BAD and sort of result-set is RANDOM.
If I create 'copy' tables in PREV instance by means of "create table ... as select from ...." and query on them the performance is GOOD and the sort of the result-set is always the same. (so the same query in the same instance performs different on original tables than on copy of that tables)

Please help...
Stephan

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

Received on Wed Jan 01 2003 - 09:44:08 CST