Re: Performance issue

From: Bjarke Wedemeijer <bjarke_at_wedemeijer.dk>
Date: 11 Sep 2005 08:32:36 -0700
Message-ID: <1126452756.728490.17690@g44g2000cwa.googlegroups.com>

Hi Bido,

please provide a 10053 trace or an explain output. 10053 for your query can you enable by using dbms_system.set_ev / or you can run your queries by SQL*Plus by using:set autotrace on exp stat (see example below)

Without decent explain or 10053 output its just a guess why your query performance badly,

//best regards,
//bjarke.

SQL>
SQL> set autotrace on exp stat
SQL> select * from sys.dual

  2 ;

D
-

X

Execution Plan

---

Plan hash value: 272002086

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time  |

---

| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |

---

Statistics

---

          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

bido skrev:

> Hi all,
> I have two long and very similar SQL queries. The only difference is in
> this part:
>
> Query 1: ... AND build_id IN (SELECT build_id from build where  .... )
> ....
>
> Query 2: ... AND build_id IN ('xxxx', 'yyyy' ,.....) ....
>
> Note that the same snaphost above is repeated three times in both
> queries and that the SELECT statement above in Query 1 returns exactly
> the records specified inside the IN block of Query 2 meaning 'xxxx',
> 'yyyy' and so.
>
> Other than that both queries are exactly the same.
>
> The first query takes around 25 seconds to execute the second takes 1
> minute 25 seconds!!!
>
> Note that both queries are programmatically generated by our
> application based on the user input. We had to change Query 1 into
> Query 2 due to the introduction of a new mandatory feature.
>
> How come Query 2 is much slower than Query 1 and is there a way to make
> it faster?!
> 
> Thx,
> 
> Ben

Received on Sun Sep 11 2005 - 10:32:36 CDT