Subject: Query slow down under 9.2.0.3 with sql_trace=true
Date: 12 Jun 2003 11:25:18 -0700
Message-ID: <31e0625e.0306121025.47fb4fc4_at_posting.google.com>
Hello everybody,
Please consider this:
OS Windows 2000.
alter session set events '10046 trace name context forever, level 8';
9.2.0.1
select count(*) from (
select
employeeid
from
test1
group by
employeeid
having
count(*) = 4 and sum((case when skillid in (2,3,4,5) then 1 else 0 end)) = 4) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.60 0.60 0 569 0 1
------- ------ -------- ---------- ---------- ---------- ----------
total 4 0.60 0.61 0 569 0 1
Plan:
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE
7589 VIEW
7589 FILTER
100000 SORT GROUP BY 336302 INDEX FAST FULL SCAN OBJ#(40688) (object id 40688)
9.2.0.3
select count(*) from (select
employeeid
from
test1
group by
employeeid
having
count(*) = 4 and sum((case when skillid in (2,3,4,5) then 1 else 0 end)) = 4) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.52 1.52 0 569 0 1
------- ------ -------- ---------- ---------- ---------- ----------
total 4 1.52 1.52 0 569 0 1
Plan:
Rows Row Source Operation
------- --------------------------------------------------- 1 SORT AGGREGATE (cr=569 r=0 w=0 time=1522445 us)7589 VIEW (cr=569 r=0 w=0 time=1514555 us) 7589 FILTER (cr=569 r=0 w=0 time=1497846 us)
100000 SORT GROUP BY (cr=569 r=0 w=0 time=1361804 us) 336302 INDEX FAST FULL SCAN OBJ#(40694) (cr=569 r=0 w=0time=396530 us)(object id 40694)
As you can see, the query runs almost 2.5 times slower under 9.2.0.3 than under 9.2.0.1. With the trace disabled, the query runs in about the same time under both versions. This behaviour make the valuable performance tool (SQL trace) practically useless under 9.2.0.3.
Please comment.
Thanks. Received on Thu Jun 12 2003 - 20:25:18 CEST