| 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: Oracle speed issue between 8i and 9i
Good example -
It's a code "improvement" that didn't quite work. Note, however, the reduction in consistent reads, which means a reduction in latching which, IN PRINCIPLE, means a potential improvement in scalability.
In practise, of course, the extra CPU cost is highly unreasonable. (You could use _old_connect_by_enabled=true to work around this), there is also a hint which I can't remember that relates to controlling the filtering that may help - there's a note about it on metalink somewhere.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html ____Belgium__November (EOUG event - "Troubleshooting") ____UK_______December (UKOUG conference - "CBO") Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "VC" <boston103_at_hotmail.com> wrote in message news:31e0625e.0310221845.21354d7c_at_posting.google.com...Received on Thu Oct 23 2003 - 14:37:17 CDT
> 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.
 |
 |