Re: --> outcome each time query ran slow

From: Stephan van Hoof <test_at_test.nl>
Date: Tue, 7 Jan 2003 10:36:09 +0100
Message-ID: <1041932084.34728@newsreader2.wirehub.nl>

Thanks Sybrand!
You pointed me in the right direction!

The results, after running 10 times the query, without changing anything
(you see: slow):

---

---

select id_district district_id, 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 20 0.14 0.16 2 187 2 0
Execute 10 0.02 1.32 0 0 30 0
Fetch 10 0.01 17.67 5 50 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 40 0.17 19.15 7 237 32 30

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

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   HASH JOIN [:Q242002]
             SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A2.C0,A1.C1,A1.C2,
               A2.C1 FROM :Q242000 A1,:Q242001 A2 WHERE A1.C0=A2.C0
      0    TABLE ACCESS (BY INDEX ROWID) OF 'DISTRICT_STREET' [:Q242000]
      0     INDEX (RANGE SCAN) OF 'DISTRICT_STREET_PLACE_STR_FK_I'
                (NON-UNIQUE)
      0    TABLE ACCESS (FULL) OF 'DISTRICT' [:Q242001]
              SELECT /*+ Q242001 NO_EXPAND ROWID(A1) */ A1."ID_DISTRICT" C0,
              A1."DISTRICT_NAME" C1 FROM "DISTRICT" PX_GRANULE(0,
                BLOCK_RANGE, DYNAMIC)  A1




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

---

The results, after running 10 times the query, after: ALTER TABLE DISTRICT PARALLEL 1;
(you see: fast!):

Can you tell me how to recognize the parallel execution? Can you tell me how to disable parallel for whole instance?

---

---

select id_district district_id, 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 20 0.05 0.07 1 174 2 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.03 6 60 40 30
------- ------ -------- ---------- ---------- ---------- ---------- ------

---

total 40 0.05 0.10 7 234 42 30

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

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   HASH JOIN
      0    TABLE ACCESS (BY INDEX ROWID) OF 'DISTRICT_STREET'
      0     INDEX (RANGE SCAN) OF 'DISTRICT_STREET_PLACE_STR_FK_I'
                (NON-UNIQUE)
      0    TABLE ACCESS (FULL) OF 'DISTRICT'




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

---

Received on Tue Jan 07 2003 - 03:36:09 CST