Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.wirehub.nl!trev!amsnews01.chello.com!nlnews00.chello.com.POSTED!not-for-mail From: "Stephan" Newsgroups: comp.databases.oracle.server References: Subject: Re: performance: new info Lines: 133 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 Message-ID: Date: Wed, 01 Jan 2003 15:44:08 GMT NNTP-Posting-Host: 62.195.54.29 X-Complaints-To: abuse@chello.nl X-Trace: nlnews00.chello.com 1041435848 62.195.54.29 (Wed, 01 Jan 2003 16:44:08 MET) NNTP-Posting-Date: Wed, 01 Jan 2003 16:44:08 MET Organization: chello broadband Xref: newsfeed1.easynews.com comp.databases.oracle.server:170935 X-Received-Date: Wed, 01 Jan 2003 08:43:41 MST (news.easynews.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@rem_scheme" "create table district_street as select * from district_street@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@rem_scheme" "create table district2_street as select * from district_street@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" wrote in message news:QgmQ9.3$rz1.19222@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" wrote in message > news:Jq%P9.23$Ua7.11811@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 > > > > > > > > > > > >