Oracle Performance and Engineering

Today, I was working with one of the "big six" DBA groups who were bothered about the performance of this query on a 9.2.05 DB -

SELECT   top_proj.project_id AS project_id, kpw.period_id AS period_id
    FROM kdrv_project_costs kpc,
         kdrv_projects kp,
         kdrv_projects top_proj,
         knta_periods kpw
   WHERE top_proj.project_id = :b1
     AND kpc.project_id = kp.project_id
     AND kp.project_path_id_list LIKE top_proj.project_path_id_list || '%'
     AND kp.project_type_code = 'TASK'
     AND kpw.period_type_id = 3
     AND kpw.period_id IN (SELECT period_id
                             FROM kdrv_cost_metrics_by_period
                            WHERE project_id = :b1); 
  

This was taking over a minute to execute. The tables, apparently, were indexed well, analyzed recently and was not too big (less than million records).

I took a look at the explain plan -

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT GROUP BY
      0   HASH JOIN 
   1201    TABLE ACCESS FULL KNTA_PERIODS
      0    MERGE JOIN 
2316760     SORT JOIN
2316760      MERGE JOIN CARTESIAN
     68       NESTED LOOPS 
      1        TABLE ACCESS BY INDEX ROWID KDRV_PROJECTS
      1         INDEX UNIQUE SCAN KDRV_PROJECTS_U1 (object id 33383)
     68        SORT UNIQUE
     68         INDEX RANGE SCAN KDRV_COST_METRICS_BY_PERIOD_U2 (object id 33358)
2316760       BUFFER SORT
  34070        TABLE ACCESS FULL KDRV_PROJECT_COSTS
      0     FILTER 
1687420      SORT JOIN
  91622       TABLE ACCESS FULL KDRV_PROJECTS

So, what's wrong? Let's see where Oracle is doing most work.

2316760 SORT JOIN
2316760 MERGE JOIN CARTESIAN

Looking at the query logically, we see the table KNTA_PERIODS is not joined with any other table than one in the IN-clause. So, effectively Oracle has two parts in this query -

(a) Every other table in the FROM clause other than KNTA_PERIODS
(b) KNTA_PERIODS joined with the IN-list.

Now, take a look at both the bind variables -

WHERE project_id = :b1 -- IN-clause


WHERE top_proj.project_id = :b1 -- main WHERE clause

These two project_ids are related. In fact, PROJECT_ID in kdrv_cost_metrics_by_period table (IN-clause) is a foreign key to PROJECT_ID in kdrv_projects. So, we expected Oracle to use "transitive closure" -- i.e.,

if, a= b and b=c
Oracle automatically will use a=c for the query optimization.

For some reason, here (sub-query, disabled foreign-key constraints?) it was not doing so.

So I changed the query to

SELECT  /*+  */  top_proj.project_id AS project_id, kpw.period_id AS period_id
         FROM kdrv_project_costs kpc,
         kdrv_projects kp,
         kdrv_projects top_proj,
         knta_periods kpw
   WHERE top_proj.project_id = :b1
     AND kpc.project_id = kp.project_id
     AND kp.project_path_id_list LIKE top_proj.project_path_id_list || '%'
     AND kp.project_type_code = 'TASK'
     AND kpw.period_type_id = 3
     AND kpw.period_id IN (SELECT  period_id
                             FROM kdrv_cost_metrics_by_period
                            WHERE project_id = top_proj.project_id)

The response time was less than 0.1 sec (down from 62 sec).

The new explain plan shows how every table is well joined this time.

---------------------------------------------------------------------------------------------------
| Id  | Operation                       |  Name                           | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                                 |     1 |   694 |     1 |
|   1 |  NESTED LOOPS                   |                                 |     1 |   694 |     1 |
|   2 |   NESTED LOOPS                  |                                 |     1 |   686 |     1 |
|   3 |    NESTED LOOPS                 |                                 |     1 |   660 |     1 |
|   4 |     NESTED LOOPS                |                                 |     1 |   328 |     1 |
|   5 |      TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS                   |     1 |   315 |     1 |
|*  6 |       INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1                |     1 |       |       |
|   7 |      INDEX FULL SCAN            | KDRV_PROJECT_COSTS_U2           |     1 |    13 |       |
|*  8 |     TABLE ACCESS BY INDEX ROWID | KDRV_PROJECTS                   |     1 |   332 |       |
|*  9 |      INDEX UNIQUE SCAN          | KDRV_PROJECTS_U1                |     1 |       |       |
|  10 |    SORT UNIQUE                  |                                 |       |       |       |
|* 11 |     INDEX RANGE SCAN            | KDRV_COST_METRICS_BY_PERIOD_U2  |     1 |    26 |       |
|* 12 |   TABLE ACCESS BY INDEX ROWID   | KNTA_PERIODS                    |     1 |     8 |       |
|* 13 |    INDEX UNIQUE SCAN            | KNTA_PERIODS_U1                 |     1 |       |       |
---------------------------------------------------------------------------------------------------