Performance, new info!

From: Stephan <test_at_test.com>
Date: Sun, 05 Jan 2003 18:14:59 GMT
Message-ID: <DC_R9.24$%_1.6967@nlnews00.chello.com>

Ora 8.1.7.
NT2000

I enabled sql_trace en timed_statistics. Several times I ran the query:
"select id_district district_id, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)"

(district table is imported an contains 20 records, district_street table is imported an contains 2000 records)

Each run I changed the query a little: alias 'district_id' to district_id1, district_id2, district_id3 etc. (see below).

Look a the outcome of the last run!
I took more than 2 seconds!

Can someone tell me what the reason can be?

select id_district district_id1, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.11 0.32 0 272 0 0
Execute 1 0.01 1.98 1 3 9 0
Fetch 1 0.01 0.35 7 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.13 2.65 8 281 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

SELECT ORA_TQ_BASE$.NEXTVAL
FROM
DUAL call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 13 0.04 0.04 0 3 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.04 0.10 0 13 65 13
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 39 0.08 0.14 0 16 65 13

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

Rows Row Source Operation
------- ---------------------------------------------------

      1  SEQUENCE
      1   TABLE ACCESS FULL DUAL

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

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9
where
obj#=:1

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

Parse 13 0.02 0.04 0 0 0 0
Execute 13 0.00 0.23 0 13 45 13
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 26 0.02 0.27 0 13 45 13

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

Rows Row Source Operation
------- ---------------------------------------------------

      1  UPDATE SEQ$
      1   INDEX UNIQUE SCAN (object id 99)

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

select id_district district_id2, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.01 0.02 0 3 9 0
Fetch 1 0.00 0.16 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.02 0.19 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id3, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.00 0.03 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.06 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id4, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.02 0.02 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.00 0.02 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.02 0.06 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id5, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.03 0 3 9 0
Fetch 1 0.00 0.02 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.06 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id6, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.00 0.04 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.07 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id7, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.01 0.04 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.02 0.07 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id8, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.01 0.15 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.17 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id9, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.01 0.15 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.17 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id10, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.00 0.04 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.07 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id11, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.02 0.02 0 0 0 0
Execute 1 0.00 0.01 0 3 9 0
Fetch 1 0.00 0.14 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.02 0.17 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id12, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.02 0 3 9 0
Fetch 1 0.00 0.04 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 0.07 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

select id_district district_id13, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)

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

Parse 2 0.01 0.01 0 0 0 0
Execute 1 0.00 0.04 0 3 9 0
Fetch 1 0.00 2.08 0 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 2.13 0 9 13 3

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20

Received on Sun Jan 05 2003 - 12:14:59 CST