Re: Performace on VERY simple database...

From: Bert Bear <bertbear_at_NOSPAMbertbear.net>
Date: Wed, 01 Jan 2003 18:16:33 GMT
Message-ID: <5gGQ9.6233$zD.2906376346@newssvr12.news.prodigy.com>

Stephan,

Ah, didn't you post this question as part of a thread in comp.database.oracle.server?

Did you try the create table as (select WITHOUT going through the database link? I think having "two database" is a Red herring. At a minimum, using two database gives way to the thought of simplifying (e.g. the KISS approach).

Why don't you try copying/creating the table (create table as (select)) inside the PREV database (instead of using the database link). You might also add an ORDER BY clause against the primary district_id and id_district for their respective tables and place_street_id with (I think) district_street

Again, I think the problem has something to do with how the database laid down the data during import. You may want to use the following script to compare the original imported tables and the create table as (select tables. You might see the later have a better organization to them, which might explain the performance.

On the other hand, this might eliminate data fragmentation/chaining from consideration (or give us other thoughts). As Daniel said there are many possibilities. Let us start eliminating some of them!!

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

rem $Header: /usr/local/hotsos/RCS/hds.sql,v 1.8 2002/01/07 18:12:27 hotsos Exp $
rem Copyright (c) 2000-2002 by Hotsos Enterprises, Ltd. All rights reserved. rem Author: jeff.holt_at_hotsos.com
rem Notes: Hotsos data selectivity using a full table scan for the row count.

define v_substr7 = 'substr(rowid,15,4)||substr(rowid,1,8)'
define v_substr8 = 'substr(rowid,7,9)'
define v_over = 'substr(''&_O_RELEASE'',1,1)'

col dummy new_value v_substr

set termout off heading on pause off

select decode(&v_over, '7', '&v_substr7', '&v_substr8') dummy from dual;

set termout on verify off feedback off pages 10

accept p_town prompt 'TableOwner : '
accept p_tname prompt 'TableName : '
accept p_clst prompt 'ColumnList : '
accept p_where prompt 'WhereClause: '
accept p_pgs prompt 'PageSize : '

variable fblks number

declare
tblks number;
tbytes number;
ublks number;

ubytes number;
luefid number;
luebid number;

lublk number;
begin
sys.dbms_space.unused_space(
upper('&p_town'), upper('&p_tname'), 'TABLE', tblks, tbytes, ublks, ubytes, luefid, luebid, lublk, null );
:fblks := tblks - ublks;
end;
/

col blks form 9,999,999,999 heading 'Table blocks below hwm|(B)' just c col nrows form 999,999,999,999 heading 'Table rows|(R)' just c new_value v_nrows

select :fblks blks, count(*) nrows
from &p_town..&p_tname;

col bs form a17 heading 'Block selectivity|(pb = b/B)' just c col nblks form 9,999,999,999 heading 'Block count|(b)' just c col rs form a17 heading 'Row selectivity|(pr = r/R)' just c col nrows form 999,999,999,999 heading 'Row count|(r)' just c

set pause on pause 'More: ' pages &p_pgs

select &p_clst,
lpad(to_char(count(distinct &v_substr)/:fblks*100,'990.00')||'%',17) as bs, count(distinct &v_substr) nblks,
lpad(to_char(count(*)/&v_nrows*100,'990.00')||'%',17) rs, count(*) nrows
from &p_town..&p_tname &p_where
group by &p_clst
order by bs desc;

"Stephan" <test_at_test.com> wrote in message news:HaFQ9.31$VW2.4998_at_nlnews00.chello.com...
> Hi,
>
> Win2000, Ora 8.1.7.
> Enough mem.
> ======================================================
> The total story:
>
> - 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
>
>
>
>
>
>
>
>
Received on Wed Jan 01 2003 - 12:16:33 CST