Re: --> outcome each time query ran slow

From: Stephan <test_at_test.com>
Date: Mon, 06 Jan 2003 20:42:34 GMT
Message-ID: <_SlS9.26$JK3.1551@nlnews00.chello.com>

Ok, Sybrand, sorry.
I red the
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch14 _str.htm#9424
and enabled explain in tkprof.

But,to be honest, I am not yet experienced enough to interpret all information.

Could you -or someone else- take a look at it? The query is: "select id_district district_id, district_name FROM district, district_street where
(district_id=id_district) AND (place_street_id=707)"

Thanks
Stephan

TKPROF: Release 8.1.7.0.0 - Production on Ma Jan 6 21:26:17 2003

Trace file: C:\oracle\admin\PREV\udump\ora01604.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.00 0.06 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 1 0.00 0.06 0 0 0 0

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

select 'stephan'
from
dual

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

Parse 2 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.00 0.01 0 1 4 1

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

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'DUAL'

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

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

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.01 0 1 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 0.01 0.01 0 1 5 1

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 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 2 0.00 0.00 0 1 3 1

Misses in library cache during parse: 1
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_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 2 0.00 0.00 0 0 0 0
Execute 1 0.00 1.55 1 3 9 0
Fetch 1 0.01 0.24 7 6 4 3
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 4 0.01 1.79 8 9 13 3

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 [:Q407002]
             SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A1.C0,A1.C1 FROM
               :Q407001 A1,:Q407000 A2 WHERE A2.C0=A1.C0
      0    TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'DISTRICT' [:Q407001]
              SELECT /*+ Q407001 NO_EXPAND ROWID(A1) */ A1."ID_DISTRICT" C0,
              A1."DISTRICT_NAME" C1 FROM "DISTRICT" PX_GRANULE(0,
                BLOCK_RANGE, DYNAMIC)  A1
      0    INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF 'DISTRICT_STREET_4'
               (NON-UNIQUE) [:Q407000]




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

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

Parse 4 0.00 0.01 0 0 0 0
Execute 3 0.00 1.61 1 3 9 0
Fetch 2 0.01 0.24 7 7 8 4
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 9 0.01 1.86 8 10 17 4

Misses in library cache during parse: 2
Misses in library cache during execute: 1

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 2 0.00 0.00 0 1 3 1
Fetch 1 0.01 0.01 0 1 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 5 0.01 0.01 0 2 8 2

Misses in library cache during parse: 2

    5 user SQL statements in session.
    2 internal SQL statements in session.     7 SQL statements in session.
    2 statements EXPLAINed in this session.

Trace file: C:\oracle\admin\PREV\udump\ora01604.trc Trace file compatibility: 8.00.04
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
       2  internal SQL statements in trace file.
       7  SQL statements in trace file.
       5  unique SQL statements in trace file.
       2  SQL statements EXPLAINed using schema:
           PREVENT.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
      73  lines in trace file.

Received on Mon Jan 06 2003 - 14:42:34 CST