Re: Oracle speed issue between 8i and 9i

From: VC <boston103_at_hotmail.com>
Date: 22 Oct 2003 19:45:33 -0700
Message-ID: <31e0625e.0310221845.21354d7c@posting.google.com>

Hello Billy,

[Billy]
> First, we need to establish a baseline. 9i performance is equal or
> better than 8i ito performance using the same query, same table, same
> physical db layout, same stats, same platform, etc. Period.
>
> If this someone wants to dispute this, well then they are saying that
> Oracle's db developers downgraded the performance of 9i in comparison
> with 8i. And that is bullshit.

Please consider this:

/* Create a tree */
create table a1(id int, pid int, value varchar2(50));

insert into a1 values(1,0,'root');
insert into a1 values(2,1,'data');
insert into a1 values(3,1,'data');

insert into a1 select rownum+3, 2, object_name from all_objects; insert into a1 select rownum+300000, 3, object_name from all_objects; insert into a1 select null, rownum+3, object_name from all_objects; insert into a1 select null, rownum+300000, object_name from all_objects;
create index a1_idx on a1(pid);
analyze table a1 compute statistics for table for all indexes for all indexed columns;

/* Run a simple query */
select count(*) from a1 connect by prior id=pid start with pid=0; COUNT(*)

45363

/* tkprof output for 8i */

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      1.04       1.05          0     105935          0  
        1

------- ------ -------- ---------- ---------- ---------- ----------

total        4      1.05       1.05          0     105935          0  
        1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
  45363   CONNECT BY
      2    INDEX RANGE SCAN (object id 1529427)
      1    TABLE ACCESS BY USER ROWID A1
  90725    TABLE ACCESS BY INDEX ROWID A1
  90725     INDEX RANGE SCAN (object id 1529427)

/* tkprof output for 9i */
call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.00          0          0          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      4.23       4.21          0      45589          0  
        1

------- ------ -------- ---------- ---------- ---------- ----------

total        4      4.24       4.21          0      45589          0  
        1


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=45589 r=0 w=0 time=4230000 us)
  45363   CONNECT BY WITH FILTERING (cr=45589 r=0 w=0 time=4090942 us)
      1    NESTED LOOPS  (cr=3 r=0 w=0 time=294 us)
      1     INDEX RANGE SCAN OBJ#(39729) (cr=2 r=0 w=0 time=200
us)(object id 39729)
      1     TABLE ACCESS BY USER ROWID OBJ#(39728) (cr=1 r=0 w=0

time=68 us)
45362 NESTED LOOPS (cr=45586 r=0 w=0 time=3030099 us)

  45363     BUFFER SORT (cr=0 r=0 w=0 time=355757 us)
  45363      CONNECT BY PUMP  (cr=0 r=0 w=0 time=84171 us)
  45362     TABLE ACCESS BY INDEX ROWID OBJ#(39728) (cr=45586 r=0 w=0
time=1427739 us)
  45362      INDEX RANGE SCAN OBJ#(39729) (cr=15276 r=0 w=0

time=661986 us)(object id 39729)

Both tests were run on the same hardware, under the same OS, the same sort_area_size, optimizer_index_cost_adj, et cetera. and yet the trivial query took four times longer under 9i(9.2.0.4) in comparison to 8i(8.1.7.4). The production table is approximately 60 times bigger, and the execution times are one minute under 8i as opposed to 5-7 minutes under 9i.

Now, how would you explain this behaviour in the light of your previous statement and what can be done to improve the query performance ?

Rgds. Received on Wed Oct 22 2003 - 21:45:33 CDT