Re: performance problem - this is really weird

From: Daud <daud11_at_hotmail.com>
Date: 26 Aug 2002 21:13:47 -0700
Message-ID: <f0bf3cc3.0208262013.10b80e56@posting.google.com>

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<m76kmu05130ft67qihus6aal8ot7s6lcbq_at_4ax.com>...
> The first procedure fetches each record individually to the local
> database. Each fetch, of whatever size (in this case 1 record)
> requires a sqlnet roundtrip
> Add the following to your code
> execute immediate 'alter session set event=''10046 trace name context
> forever, level 12''';
> and you will get a trace file which demonstrates this to you.
> The other procedure doesn't fetch anything to the local PC
>
> Hth
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

I set sql_trace=true and looked at the output from each of the two procedures and I just dont understand the results.

I only have 500 records in the remote testtab table but why would oracle needs to fetch 125250 rows for the second method. I am ok with the result for the first method. Look at the 'rows' column. I also did another test. I added 'where exists (select 'x' from dual)' to the second method and the result looks good. Can someone explain the difference please.

rgds
Daud

SELECT *
FROM
 TESTTAB_at_PDCDB R WHERE NOT EXISTS (SELECT 'x' FROM TESTTAB L WHERE L.SEQ

• R.SEQ )

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse        1      0.00       0.00          1          1          5  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch      501      0.00       0.00          1        501          0  
      500

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

---

total      503      0.00       0.00          2        502          5  
      500

SELECT *
FROM
 TESTTAB_at_PDCDB

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse        1      0.00       0.00          0          0          2  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch      501      0.00       0.00          0          0          0  

   125250
------- ------ -------- ---------- ---------- ---------- ----------

---

total 503 0.00 0.00 0 0 2

   125250

SELECT *
FROM
 TESTTAB_at_PDCDB WHERE EXISTS (SELECT 'x' FROM DUAL )

call     count       cpu    elapsed       disk      query    current  
     rows

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

---

Parse        1      0.00       0.00          0          0          2  
        0
Execute      1      0.00       0.00          0          1          4  
        0
Fetch      501      0.00       0.00          0          0          0  
      500

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

---

total      503      0.00       0.00          0          1          6  
      500

Received on Mon Aug 26 2002 - 23:13:47 CDT