Re: fastest way to determine if 0 or more records are present in table?

From: FC <flavio_at_tin.it>
Date: Sat, 21 Feb 2004 22:21:17 GMT
Message-ID: <xRQZb.4408$IT2.151869@news4.tin.it>

Hi all,
here is my homework on the topic and below are my findings:

Entries 1-10 show results for simple record existence checks in a given table.
Entries 11-19 refer to a slightly more complex existence check involving an arbitrary where clause.

Entries 20-22 refer to TKPROF output of sessions running each method concurrently with other 5 sessions in a tight loop of 1000 iterations. The statistics refer to the "median" session, the third one.

These results are the most impressive when it comes to show the real scalability certain approaches, as you can see there is a huge overhead caused by parsing dynamic sql.

Despite the simple nature of the query, this has been a rather useful exercise for me, I realize better now the impact a simple function call can have on performance, especially when the function call is repeated over and over again.

Bye,
Flavio

CREATE OR REPLACE
FUNCTION Any_Records(

         par_sql_stmt in varchar2)
RETURN PLS_INTEGER
IS

   flag number;
BEGIN    EXECUTE IMMEDIATE 'select 1 from dual where exists (' || par_sql_stmt || ')' INTO flag;

   RETURN 1; EXCEPTION
   WHEN no_data_found THEN

      RETURN 0;
   WHEN OTHERS THEN RAISE;
END;

---

CREATE TABLE benchmark (col1 NUMBER(10,0), col2 VARCHAR2(10));

---

---

Results:

---

#1 (SQL function, empty table, SQL statement parsed for the first time)

SQL> select any_records('select * from benchmark') from dual;

ANY_RECORDS('SELECT*FROMBENCHMARK')

---

                                  0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

***************************************

#2 (SQL function, empty table, cached statement, cached blocks)

SQL> select any_records('select * from benchmark') from dual;

ANY_RECORDS('SELECT*FROMBENCHMARK')

---

                                  0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

*******************************************

Run #3 (plain SQL, empty table, cached blocks, SQL statement parsed for the first time)

SQL> select count(*) from benchmark where rownum < 2;

  COUNT(*)

---

         0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'BENCHMARK'

Statistics

---

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

#4 (SQL function, 100K records table, cached statement)

SQL> select any_records('select * from benchmark') from dual;

ANY_RECORDS('SELECT*FROMBENCHMARK')

---

                                  1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

#5 (plain SQL, 100K records table, cached blocks)

SQL> select count(*) from benchmark where rownum < 2;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'BENCHMARK'

Statistics

---

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

#6 (plain SQL, alternate query with EXISTS clause, 100K records table) SQL> select count(*) from dual where exists (select 1 from benchmark where rownum < 2);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL'
   4    2       COUNT (STOPKEY)
   5    4         TABLE ACCESS (FULL) OF 'BENCHMARK'

Statistics

---

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

#7 (plain SQL, alternate query with EXISTS clause, 100K records table, analyzed table)

SQL> select count(*) from dual where exists (select 1 from benchmark where rownum < 2);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   4    2       COUNT (STOPKEY)
   5    4         TABLE ACCESS (FULL) OF 'BENCHMARK' (Cost=34 Card=100

Statistics

---

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

***********************************************

#8 (plain SQL, alternate query with EXISTS clause, empty table after truncate, analyzed table)

SQL> select count(*) from dual where exists (select 1 from benchmark where rownum < 2);

  COUNT(*)

---

         0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   4    2       COUNT (STOPKEY)
   5    4         TABLE ACCESS (FULL) OF 'BENCHMARK' (Cost=1 Card=1)

Statistics

---

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

#9 (Same as #2, but analyzed table).

SQL> select any_records('select * from benchmark') from dual;

ANY_RECORDS('SELECT*FROMBENCHMARK')

---

                                  0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

#10 (Same as #3, but analyzed table)

SQL> select count(*) from benchmark where rownum < 2;

  COUNT(*)

---

         0

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'BENCHMARK' (Cost=1 Card=1)

Statistics

---

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

SECOND TEST SET

---

#11 (plain SQL, 100K records table)

SQL> select count(*) from benchmark where rownum < 2 and col1 = 50000;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'BENCHMARK'

Statistics

---

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

*********************************************************

#12 (plain SQL, alternate query with EXISTS clause, 100K records table)

SQL> select any_records('select 1 from benchmark where col1 = 50000') from dual;

ANY_RECORDS('SELECT1FROMBENCHMARKWHERECOL1=50000')

---

                                                 1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

*********************************************************

#13 (SQL function, 100K records table)

SQL> select count(*) from dual where exists (select 1 from benchmark where col1 = 50000 and rownum < 2);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL'
   4    2       COUNT (STOPKEY)
   5    4         TABLE ACCESS (FULL) OF 'BENCHMARK'

Statistics

---

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

*******************************************

#14 (Same as #11, analyzed table)

SQL> select count(*) from benchmark where rownum < 2 and col1 = 50000;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=1 Bytes=4)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'BENCHMARK' (Cost=34 Card=1 Byt

Statistics

---

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

#15 (Same as #12, analyzed table)

SQL> select count(*) from dual where exists (select 1 from benchmark where col1 = 50000 and rownum < 2);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   4    2       COUNT (STOPKEY)
   5    4         TABLE ACCESS (FULL) OF 'BENCHMARK' (Cost=34 Card=1 B

Statistics

---

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

***********************************************

#16 (Same as #13, analyzed table)

SQL> select any_records('select 1 from benchmark where col1 = 50000') from dual;

ANY_RECORDS('SELECT1FROMBENCHMARKWHERECOL1=50000')

---

                                                 1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

************************************************

create unique index idx_benchmark on benchmark(col1);

---

#17 (Same as #14, analyzed table and index)

SQL> select count(*) from benchmark where rownum < 2 and col1 = 50000;

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)    1 0 SORT (AGGREGATE)

   2    1     COUNT (STOPKEY)
   3    2       INDEX (UNIQUE SCAN) OF 'IDX_BENCHMARK' (UNIQUE) (Cost=

Statistics

---

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

*********************************************

#18 (Same as #15, analyzed table & index)

SQL> select count(*) from dual where exists (select 1 from benchmark where col1 = 50000 and rownum < 2);

  COUNT(*)

---

         1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   4    2       COUNT (STOPKEY)
   5    4         INDEX (UNIQUE SCAN) OF 'IDX_BENCHMARK' (UNIQUE) (Cos

Statistics

---

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

************************************************

#19 (Same as #16, analyzed table and index)

SQL> select any_records('select 1 from benchmark where col1 = 50000') from dual;

ANY_RECORDS('SELECT1FROMBENCHMARKWHERECOL1=50000')

---

                                                 1

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'DUAL' Statistics

---

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

===========================================

Iterated procedures

---

#20 (1000 iterations on Any_records function)

TKPROF: Release 8.1.7.0.0 - Production on Sat Feb 21 18:46:02 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ORA01176.TRC
Sort options: default

---

---

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

---

BEGIN benchmark1; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.01 0.01 0 0 0 0
Execute 1 2.60 11.96 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 2.61 11.97 0 0 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25

---

---

select 1
from
 dual where exists (select 1 from benchmark where col1 = 50000)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1000 1.59 8.20 0 0 0 0
Execute 1000 0.18 0.34 0 2000 0 0
Fetch 1000 0.14 0.14 0 1000 4000 1000
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3000 1.91 8.68 0 3000 4000 1000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25 (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  FILTER
      1   TABLE ACCESS FULL DUAL
      1   INDEX UNIQUE SCAN (object id 38345)

****************************************************************************

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 3 0.01 0.01 0 0 0 0
Execute 4 2.60 11.96 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 7 2.61 11.97 0 0 0 2

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1001 1.59 8.20 0 0 0 0
Execute 1001 0.18 0.34 0 2000 0 0
Fetch 1001 0.14 0.14 0 1002 4000 1001
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 3003 1.91 8.68 0 3002 4000 1001

Misses in library cache during parse: 0

 1004 user SQL statements in session.

    1 internal SQL statements in session.  1005 SQL statements in session.

---

---

Trace file: ORA01176.TRC
Trace file compatibility: 8.00.04
Sort options: default

       1 session in tracefile.
    1004 user SQL statements in trace file.

       1 internal SQL statements in trace file.     1005 SQL statements in trace file.

       6 unique SQL statements in trace file.    10052 lines in trace file.

---

#21 (1000 iterations on simple query)

TKPROF: Release 8.1.7.0.0 - Production on Sat Feb 21 18:49:53 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ORA01636.TRC
Sort options: default

---

---

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

---

alter session set sql_trace=true

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.02 0.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 1 0.02 0.02 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25

---

---

BEGIN benchmark2; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.03 1.58 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 1.03 1.58 0 0 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25

---

---

SELECT COUNT(*)
FROM
 BENCHMARK WHERE COL1 = 50000 AND ROWNUM < 2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.01 0.01 0 0 0 0
Execute 1000 0.08 0.07 0 0 0 0
Fetch 1000 0.08 0.07 0 2000 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2001 0.17 0.15 0 2000 0 1000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25 (recursive depth: 1)

---

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 3 0.01 0.01 0 0 0 0
Execute 4 1.05 1.60 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 7 1.06 1.61 0 0 0 2

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 2 0.01 0.01 0 0 0 0
Execute 1001 0.08 0.07 0 0 0 0
Fetch 1001 0.08 0.07 0 2002 0 1001
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2004 0.17 0.15 0 2002 0 1001

Misses in library cache during parse: 0

    5 user SQL statements in session.
    1 internal SQL statements in session.     6 SQL statements in session.

---

---

Trace file: ORA01636.TRC
Trace file compatibility: 8.00.04
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       6  SQL statements in trace file.
       6  unique SQL statements in trace file.

    2057 lines in trace file.

---

#22 (1000 iterations on alternate query)

TKPROF: Release 8.1.7.0.0 - Production on Sat Feb 21 18:51:47 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ORA01344.TRC
Sort options: default

---

---

count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
****************************************************************************

---

BEGIN benchmark3; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.04 3.35 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2 1.04 3.35 0 0 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25

---

---

SELECT COUNT(*)
FROM
 DUAL WHERE EXISTS (SELECT 1 FROM BENCHMARK WHERE COL1 = 50000 AND   ROWNUM < 2 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.15 0.59 0 2000 0 0
Fetch 1000 0.11 0.34 0 1000 4000 1000
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2001 0.26 0.93 0 3000 4000 1000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25 (recursive depth: 1)

---

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 3 0.01 0.71 0 0 0 0
Execute 4 1.05 3.36 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 7 1.06 4.07 0 0 0   2

Misses in library cache during parse: 0

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

Parse 2 0.00 0.00 0 0 0 0
Execute 1001 0.15 0.59 0 2000 0 0
Fetch 1001 0.11 0.34 0 1002 4000 1001
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 2004 0.26 0.93 0 3002 4000 1001

Misses in library cache during parse: 0

    5 user SQL statements in session.
    1 internal SQL statements in session.     6 SQL statements in session.

---

---

Trace file: ORA01344.TRC
Trace file compatibility: 8.00.04
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
       1  internal SQL statements in trace file.
       6  SQL statements in trace file.
       6  unique SQL statements in trace file.

    2057 lines in trace file. Received on Sat Feb 21 2004 - 16:21:17 CST