| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: performance: new info
"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message
news:NdGQ9.6229$gu.2905767980_at_newssvr12.news.prodigy.com...
> Stephan,
>
> 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).
Ok, thanks, I will follow your instructions:
For your info (original scripts):
id_district NUMBER(6,0) NOT NULL, district_name VARCHAR2(30) NOT NULL)
id_district_street NUMBER(6,0) NOT NULL, district_id NUMBER(6,0) NOT NULL, place_street_id NUMBER(6,0) NOT NULL, street_part_id NUMBER(6,0) NULL, order_nr NUMBER(6) DEFAULT 1000 NOTNULL
--> prim. key=id_district_street
----------------------------------------------------------------------------
--
ALTER TABLE DISTRICT_STREET ADD (
CONSTRAINT DISTRICT_STREET_UNIQUE
UNIQUE (DISTRICT_ID,
PLACE_STREET_ID,
STREET_PART_ID)
I ran your script, but to be honest I do not exactly know how to use it and what the outcome means....
Some results:
Table blocks below hwm Table rows
(B) (R)
---------------------- ----------------
2 20
Block selectivity Block count Row selectivity Row count
ID_DISTRICT (pb = b/B) (b) (pr = r/R) (r)
----------- ----------------- -------------- ----------------- -------------
---
1 50.00% 1 5.00%
1
2 50.00% 1 5.00%
1
3 50.00% 1 5.00%
1
21 50.00% 1 5.00%
1
22 50.00% 1 5.00%
1
61 50.00% 1 5.00%
1
62 50.00% 1 5.00%
1
63 50.00% 1 5.00%
1
64 50.00% 1 5.00%
1
65 50.00% 1 5.00%
1
66 50.00% 1 5.00%
1
67 50.00% 1 5.00%
1
68 50.00% 1 5.00%
1
69 50.00% 1 5.00%
1
70 50.00% 1 5.00%
1
71 50.00% 1 5.00%
1
72 50.00% 1 5.00%
1
73 50.00% 1 5.00%
1
74 50.00% 1 5.00%
1
81 50.00% 1 5.00%
1
Table blocks below hwm Table rows
(B) (R)
---------------------- ----------------
2 20
Block selectivity Block count Row selectivity Row count
ID_DISTRICT (pb = b/B) (b) (pr = r/R) (r)
----------- ----------------- -------------- ----------------- -------------
---
1 50.00% 1 5.00%
1
2 50.00% 1 5.00%
1
3 50.00% 1 5.00%
1
21 50.00% 1 5.00%
1
22 50.00% 1 5.00%
1
61 50.00% 1 5.00%
1
62 50.00% 1 5.00%
1
63 50.00% 1 5.00%
1
64 50.00% 1 5.00%
1
65 50.00% 1 5.00%
1
66 50.00% 1 5.00%
1
67 50.00% 1 5.00%
1
68 50.00% 1 5.00%
1
69 50.00% 1 5.00%
1
70 50.00% 1 5.00%
1
71 50.00% 1 5.00%
1
72 50.00% 1 5.00%
1
73 50.00% 1 5.00%
1
74 50.00% 1 5.00%
1
81 50.00% 1 5.00%
1
Table blocks below hwm Table rows
(B) (R)
---------------------- ----------------
11 1,925
Block selectivity Block count Row selectivity
ID_DISTRICT_STREET (pb = b/B) (b) (pr = r/R)
------------------ ----------------- -------------- -----------------
Row count
(r)
----------------
1 9.09% 1 0.05%
1
2 9.09% 1 0.05%
1
3 9.09% 1 0.05%
1
4 9.09% 1 0.05%
1
5 9.09% 1 0.05%
1
6 9.09% 1 0.05%
1
7 9.09% 1 0.05%
1
8 9.09% 1 0.05%
1
9 9.09% 1 0.05%
1
10 9.09% 1 0.05%
1
11 9.09% 1 0.05%
1
12 9.09% 1 0.05%
1
13 9.09% 1 0.05%
1
14 9.09% 1 0.05%
1
15 9.09% 1 0.05%
1
16 9.09% 1 0.05%
1
17 9.09% 1 0.05%
1
18 9.09% 1 0.05%
1
19 9.09% 1 0.05%
1
SQL>
Table blocks below hwm Table rows
(B) (R)
---------------------- ----------------
7 1,925
Block selectivity Block count Row selectivity
ID_DISTRICT_STREET (pb = b/B) (b) (pr = r/R)
------------------ ----------------- -------------- -----------------
Row count
(r)
----------------
1 14.29% 1 0.05%
1
2 14.29% 1 0.05%
1
3 14.29% 1 0.05%
1
4 14.29% 1 0.05%
1
5 14.29% 1 0.05%
1
6 14.29% 1 0.05%
1
7 14.29% 1 0.05%
1
8 14.29% 1 0.05%
1
9 14.29% 1 0.05%
1
10 14.29% 1 0.05%
1
11 14.29% 1 0.05%
1
12 14.29% 1 0.05%
1
13 14.29% 1 0.05%
1
14 14.29% 1 0.05%
1
15 14.29% 1 0.05%
1
16 14.29% 1 0.05%
1
17 14.29% 1 0.05%
1
18 14.29% 1 0.05%
1
19 14.29% 1 0.05%
1
SQL>
Why does quering on the imported tables give another performance than
quering on
two copy-tables (which are created by means of a " select .. from " that
imported tables)???
Best regards,
Stephan
>
> 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:c1EQ9.420$YQ1.396070_at_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 - 16:49:11 CST
 |
 |