Re: Performance issue

From: bido <sideb_neb_at_yahoo.com>
Date: 10 Sep 2005 11:25:19 -0700
Message-ID: <1126376719.904279.122730@g47g2000cwa.googlegroups.com>

The Oracle server version is 9.2.0.4

EXPLAIN PLAN require the creation of a table. I don't have control over the DB but using the Query plan of the client DBArtisan I get the following execution plans which is similar to the EXPLAIN PLAN I believe. Note that the numbers represent respectively the cost, number of rows and bytes.

Query 1:

---

SELECT STATEMENT,839,1,58
 SORT (AGGREGATE),0,1,58
  FILTER,0,0,0
   NESTED LOOPS,833,1,58
    NESTED LOOPS,832,1,43

     VIEW--VW_NSO_1,803,1,13
       FILTER,0,0,0
        SORT (GROUP BY),803,1,75
          NESTED LOOPS,783,1,75
            HASH JOIN,782,1,64
             HASH JOIN,477,3234,145530
              TABLE ACCESS (BY INDEX ROWID)--BUILD,2,5,75
                INDEX (RANGE SCAN)--BUILD_PARENT_BUILD_ID_IX


(NON-UNIQUE),1,10,0

              TABLE ACCESS (FULL)--EVALUATION,474,89983,2699490
             TABLE ACCESS (FULL)--RATER,303,71441,1357379
            TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,11
              INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,3,0
     TABLE ACCESS (BY INDEX ROWID)--EVALUATION,9,1,30
       INDEX (RANGE SCAN)--EVALUATION_RATER_ID_IX (NON-UNIQUE),2,9,0
    TABLE ACCESS (BY INDEX ROWID)--BUILD,1,1,15
      INDEX (UNIQUE SCAN)--BUILD_PK (UNIQUE),0,41,0

   NESTED LOOPS,6,1,63
    NESTED LOOPS,5,1,52

     NESTED LOOPS,4,1,34
      TABLE ACCESS (BY INDEX ROWID)--RATER,3,1,19
        INDEX (UNIQUE SCAN)--RATER_PK (UNIQUE),2,214324,0
      TABLE ACCESS (BY INDEX ROWID)--BUILD,1,1,15
        INDEX (UNIQUE SCAN)--BUILD_PK (UNIQUE),0,204,0
     TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,18
       INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,91999,0
    TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,11
      INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,91999,0

Query 2:

---

SELECT STATEMENT,839,1,58
 SORT (AGGREGATE),0,1,58
  FILTER,0,0,0
   NESTED LOOPS,833,1,58
    NESTED LOOPS,832,1,43

     VIEW--VW_NSO_1,803,1,13
      FILTER,0,0,0
       SORT (GROUP BY),803,1,75
        NESTED LOOPS,783,1,75
         HASH JOIN,782,1,64
          HASH JOIN,477,3234,145530
           TABLE ACCESS (BY INDEX ROWID)--BUILD,2,5,75
            INDEX (RANGE SCAN)--BUILD_PARENT_BUILD_ID_IX


(NON-UNIQUE),1,10,0

           TABLE ACCESS (FULL)--EVALUATION,474,89983,2699490
          TABLE ACCESS (FULL)--RATER,303,71441,1357379
         TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,11
          INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,3,0
     TABLE ACCESS (BY INDEX ROWID)--EVALUATION,9,1,30
      INDEX (RANGE SCAN)--EVALUATION_RATER_ID_IX (NON-UNIQUE),2,9,0
    TABLE ACCESS (BY INDEX ROWID)--BUILD,1,1,15
     INDEX (UNIQUE SCAN)--BUILD_PK (UNIQUE),0,41,0

   NESTED LOOPS,6,1,63
    NESTED LOOPS,5,1,52

     NESTED LOOPS,4,1,34
      TABLE ACCESS (BY INDEX ROWID)--RATER,3,1,19
       INDEX (UNIQUE SCAN)--RATER_PK (UNIQUE),2,214324,0
      TABLE ACCESS (BY INDEX ROWID)--BUILD,1,1,15
       INDEX (UNIQUE SCAN)--BUILD_PK (UNIQUE),0,204,0
     TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,18
      INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,91999,0
    TABLE ACCESS (BY INDEX ROWID)--PWS_USER,1,1,11
     INDEX (UNIQUE SCAN)--PWS_USER_PK (UNIQUE),0,91999,0

I appreciate your help.

Ben Received on Sat Sep 10 2005 - 13:25:19 CDT