Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> [Q] Unefficient SQL statement need help?

[Q] Unefficient SQL statement need help?

From: L <leed_at_chele.cais.net>
Date: Tue, 21 Nov 2000 16:59:02 -0500 (EST)
Message-Id: <10687.122665@fatcity.com>


We have ORACLE 7.3.4 on SIN SPARC SOlaris 2.6 computer. I used "Tkprof" to trace a SQL statement. Can anyone hel me on following :

select distinct FACILITY_ATTRIBUTE.facility_attribute_id,   FACILITY_ATTRIBUTE.title, FACILITY_ATTRIBUTE.internet_query_flag from
 FACILITY, FACILITY_TYPE, FACILITY_MASTER, FACILITY_FEATURE,

  FACILITY_ATTRIBUTE where FACILITY.facility_master_id =
  FACILITY_MASTER.facility_master_id and FACILITY.facility_type_id =
  FACILITY_TYPE.facility_type_id and FACILITY.facility_id =
  FACILITY_FEATURE.facility_id and FACILITY_FEATURE.facility_attribute_id
=
  FACILITY_ATTRIBUTE.facility_attribute_id and FACILITY.internet_query_flag
<> 0 and FACILITY_ATTRIBUTE.internet_query_flag <> 0 order by
  FACILITY_ATTRIBUTE.title

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



Parse 1 0.33 0.33 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 511.75 513.18 666 11294584 4 176
------- ------ -------- ---------- ---------- ---------- ----------

total 3 512.08 513.51 666 11294584 4 176

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

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
  23081 SORT (UNIQUE)
  23081 NESTED LOOPS
2256300     NESTED LOOPS
2256300      NESTED LOOPS
2256300       MERGE JOIN (CARTESIAN)
  12535        TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                   'FACILITY'
    180        SORT (JOIN)
    180         TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                    'FACILITY_ATTRIBUTE'
2256300       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                  'XPKFACILITY_TYPE' (UNIQUE)
2256300      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                 'XPKFACILITY_MASTER' (UNIQUE)
2256300     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
Received on Tue Nov 21 2000 - 15:59:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US