Query slow down under 9.2.0.3 with sql_trace=true

From: VC <boston103_at_hotmail.com>
Date: 12 Jun 2003 03:52:05 -0700
Message-ID: <31e0625e.0306120252.791f35bb@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=0

time=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 - 05:52:05 CDT