Oracle Performance and Engineering

declare
   cursor ccc is
   select owner, table_name, sequence_name, derived_column_name,
          count(*) cnt
     from iview
 group by owner, table_name, sequence_name, derived_column_name;


begin
  for c in ccc loop
    delete from trm_tab_sequences
     where table_name = c.table_name
       and sequence_name = c.sequence_name
       and derived_column_name = c.derived_column_name
       and rownum < c.cnt;
    end loop;
end;
/

DELETE FROM iview t1
   WHERE ROWID <> ( SELECT MAX(ROWID)
                       FROM iview b
                      WHERE b.owner = t1.owner
                        AND b.table_name = t1.table_name
                        AND b.sequence_name = t1.sequence_name
                        AND b.derived_column_name = t1.derived_column_name);

DELETE FROM iview WHERE ROWID IN
  (SELECT rid
     FROM
    (SELECT ROWID rid,
          row_number() over (PARTITION BY owner, table_name, sequence_name, derived_column_name ORDER BY ROWID) rn
         FROM iview )
   WHERE rn <> 1 );

The second solution uses "analytic function" and is more elegant. However, based on some tests - depending on what columns are duplicated and are indexed - the first solution could indeed be faster in some cases.

posted by Nilendu  # 2:28 PM 0 comments

SELECT *
  FROM knta_exceptions ex
 WHERE (source_id IN (
              SELECT kp2.project_id
                FROM kdrv_projects kp2, kdrv_projects kp1
               WHERE kp1.project_id = :p_source_id
                 AND kp2.project_path_id_list LIKE kp1.project_path_id_list || '%'
                 AND kp2.project_type_code = 'TASK')
       OR
       (source_id IN (
               SELECT kp2.project_id
                 FROM kdrv_projects kp2, kdrv_projects kp1
                WHERE kp1.project_id = :p_source_id
                AND kp2.project_path_id_list LIKE kp1.project_path_id_list || '%'
                AND kp2.project_type_code = 'PROJECT')
        AND source_entity_id = 59))
  AND source_entity_id IN (59, 6);

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name             | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |  4049 |   478K|   168 |
|   1 |  FILTER                       |                   |       |       |       |
|   2 |   TABLE ACCESS FULL           | KNTA_EXCEPTIONS   |  4049 |   478K|   168 |
|   3 |   NESTED LOOPS                |                   |     1 |    78 |     3 |
|   4 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     |     1 |    36 |     2 |
|   5 |     INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1  |     1 |       |     1 |
|   6 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     |     1 |    42 |     1 |
|   7 |     INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1  |     1 |       |       |
|   8 |   NESTED LOOPS                |                   |     1 |    78 |     3 |
|   9 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     |     1 |    36 |     2 |
|  10 |     INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1  |     1 |       |     1 |
|  11 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     |     1 |    42 |     1 |
|  12 |     INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1  |     1 |       |       |
-----------------------------------------------------------------------------------

       SELECT *  FROM knta_exceptions ex
              WHERE source_id IN ( SELECT kp2.project_id
                                     FROM kdrv_projects kp2,
                                          kdrv_projects kp1
                                    WHERE kp1.project_id =:p_source_id
                                      AND kp2.project_path_id_list LIKE kp1.project_path_id_list||'%'
                                      AND (kp2.project_type_code = 'TASK' OR (kp2.project_type_code = 'PROJECT' AND source_entity_id = 59)))
-- just one OR condition that takes care of both.
                                      AND source_entity_id IN (59, 6)

-----------------------------------------------------------------------------------
| Id  | Operation                     |  Name             | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |  1794 |   348K|   639 |
|   1 |  HASH JOIN                    |                   |  1794 |   348K|   639 |
|   2 |   NESTED LOOPS                |                   | 14112 |  1074K|   385 |
|   3 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     |     1 |    36 |     2 |
|   4 |     INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1  |     1 |       |     1 |
|   5 |    TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS     | 14112 |   578K|   383 |
|   6 |     INDEX RANGE SCAN          | KDRV_PROJECTS_N2  |  2540 |       |    16 |
|   7 |   TABLE ACCESS FULL           | KNTA_EXCEPTIONS   | 61507 |  7267K|   168 |
-----------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |  9953 |  1846K|   146 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | KNTA_EXCEPTIONS    |     1 |   112 |     1 |
|   2 |   NESTED LOOPS                 |                    |  9953 |  1846K|   146 |
|   3 |    NESTED LOOPS                |                    | 14112 |  1074K|     5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS      |     1 |    36 |     1 |
|   5 |      INDEX UNIQUE SCAN         | KDRV_PROJECTS_U1   |     1 |       |     1 |
|   6 |     TABLE ACCESS BY INDEX ROWID| KDRV_PROJECTS      | 14112 |   578K|     4 |
|   7 |      INDEX RANGE SCAN          | KDRV_PROJECTS_N2   |  2540 |       |     0 |
|   8 |    INLIST ITERATOR             |                    |       |       |       |
|   9 |     INDEX RANGE SCAN           | KNTA_EXCEPTIONS_N1 |     2 |       |     0 |
-------------------------------------------------------------------------------------

posted by Nilendu  # 7:11 PM 0 comments

• (if 9i) Statspack report (snapshots at level 6) or (if 10g) AWRStats report
• Depending on the problem (isolated or system-wide) session trace (event 10046, level 12)
• table and index statistics
• select * from v$parameter
• the alert.log file and, if applicable, the user dump files (if generated apart from tracing)
• "top-N" queries from data dictionary based on CPU time, elapsed time, Buffer Gets etc

The first customer issue -

Fortunately, this customer had already pinned on one SQL that was a performance disaster. They'd uploaded the email trail discussing about the SQL and I used the email to get an idea about the SQL. After pruning the SELECTed fields, the query looked like -

SELECT
task_id, last_update_date,
local_currency_id
FROM
kdrv_my_tasks_v v
WHERE
state_id IN (9, 10, 11, 12)
AND parent_state_id IN (4, 5, 15, 16)
AND master_project_id = '30062'
AND TRUNC (scheduled_start_date) <= (CURRENT_DATE + 1 + 14)
AND 75 < (SELECT SUM (p.percent_complete)
FROM kdrv_projects p
WHERE p.project_id IN
(SELECT pr.predecessor_project_id
FROM kdrv_predecessors pr
WHERE pr.project_id = v.task_id))
AND assignment_user_id = '49745'
ORDER BY scheduled_start_date ASC, task_name;

1. kdrv_my_tasks_v is a view that has the table KDRV_PROJECTS in it's WHERE clause
2. The blue highlighted part in the query maps with the blue highlighted part in the access plan - and is almost fully responsible for the CPU time (147 secs to full-scan 14 million rows as a result full-scan of kdrv_projects -- sounds logical).
3. The blue highlighted part in the query has an unnecessary IN-list in the correlated sub-query. This could simply have been an equi-join.

i.e., we could rewrite -

(SELECT SUM (p.percent_complete)
FROM kdrv_projects p
WHERE p.project_id IN
(SELECT pr.predecessor_project_id
FROM kdrv_predecessors pr
WHERE pr.project_id = v.task_id))

to -

(SELECT SUM (p.percent_complete)
FROM kdrv_projects p,
kdrv_predecessors pr
WHERE p.project_id = pr.predecessor_project_id
AND pr.project_id = v.task_id)

Then the query becomes -

SELECT task_id,
last_update_date,
local_currency_id
FROM kdrv_my_tasks_v v
WHERE state_id IN (9, 10, 11, 12) AND
parent_state_id IN (4, 5, 15, 16) AND
master_project_id = '30062'AND
TRUNC (scheduled_start_date) <= (CURRENT_DATE + 1 + 14)AND
75 < (SELECT SUM (p.percent_complete)
FROM kdrv_projects p,
kdrv_predecessors pr
WHERE p.project_id = pr.predecessor_project_id
AND pr.project_id = v.task_id) AND
assignment_user_id = '49745'
ORDER BY scheduled_start_date ASC, task_name;

The new Logical IO falls down to just about 1500 (from 57 million) and time taken falls to about 08 secs! There's no more a "full table scan" on kdrv_projects table. On a finishing note, how many times have we seen this type of "redundant" IN-lists with a single table replacing an equi-join?

posted by Nilendu  # 3:52 PM 0 comments

• simple view merging
• complex view merging
• subquery 'flattening'
• transitive predicate generation
• common subexpression elimination
• predicate pushdown and pullup
• group pruning for "cube" queries
• outer-join inner join conversion

posted by Nilendu  # 2:14 PM 2 comments