Database Views: the good, the bad and the ugly

Views are good as they can be used to hide complexity.

Views are bad as they can hide complexity.

It’s when views are used for the first point above without consideration of the second point that things can turn ugly. Here’s recently encountered example…

A developer decided to modify a process so that it would determine when it was being run from within the internal database job scheduler. The following simple query was added to the code:

SELECT job_name
INTO   l_result
FROM   user_scheduler_running_jobs
where  session_id = l_sid;

What the developer overlooked was that user_scheduler_running_jobs is a view and even though it’s an Oracle data dictionary view the underlying table structure might not be as simple as the query would suggest. The following is the execution plan that Oracle selected:

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                             |       |       |     6 (100)|          |        |      |            |
|   1 |  VIEW                                | USER_SCHEDULER_RUNNING_JOBS |     3 |   198 |     6  (34)| 00:00:01 |        |      |            |
|   2 |   UNION-ALL                          |                             |       |       |            |          |        |      |            |
|   3 |    MERGE JOIN CARTESIAN              |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|*  4 |     HASH JOIN OUTER                  |                             |     1 |   190 |     5  (20)| 00:00:01 |        |      |            |
|   5 |      NESTED LOOPS                    |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|   6 |       NESTED LOOPS                   |                             |     1 |   171 |     4   (0)| 00:00:01 |        |      |            |
|*  7 |        HASH JOIN OUTER               |                             |     1 |   138 |     1   (0)| 00:00:01 |        |      |            |
|   8 |         NESTED LOOPS                 |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|   9 |          NESTED LOOPS                |                             |     1 |    99 |     1   (0)| 00:00:01 |        |      |            |
|  10 |           MERGE JOIN CARTESIAN       |                             |     1 |    73 |     0   (0)|          |        |      |            |
|* 11 |            FIXED TABLE FULL          | X$KCCDI                     |     1 |    15 |     0   (0)|          |        |      |            |
|  12 |            BUFFER SORT               |                             |     1 |    58 |     0   (0)|          |        |      |            |
|* 13 |             PX COORDINATOR           |                             |       |       |            |          |        |      |            |
|  14 |              PX SEND QC (RANDOM)     | :TQ10000                    |     1 |    26 |     0   (0)|          |  Q1,00 | P->S | QC (RAND)  |
|* 15 |               VIEW                   | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q1,00 | PCWP |            |
|* 16 |                FIXED TABLE FULL      | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q1,00 | PCWP |            |
|* 17 |           INDEX UNIQUE SCAN          | SCHEDULER$_JOB_PK           |     1 |       |     0   (0)|          |        |      |            |
|* 18 |          TABLE ACCESS BY INDEX ROWID | SCHEDULER$_JOB              |     1 |    26 |     1   (0)| 00:00:01 |        |      |            |
|* 19 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  20 |          PX SEND QC (RANDOM)         | :TQ20000                    |     1 |    28 |     0   (0)|          |  Q2,00 | P->S | QC (RAND)  |
|* 21 |           VIEW                       | GV$SESSION                  |       |       |            |          |  Q2,00 | PCWP |            |
|  22 |            MERGE JOIN CARTESIAN      |                             |     1 |    28 |     0   (0)|          |  Q2,00 | PCWP |            |
|  23 |             NESTED LOOPS             |                             |     1 |    12 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 24 |              FIXED TABLE FIXED INDEX | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 25 |              FIXED TABLE FIXED INDEX | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q2,00 | PCWP |            |
|  26 |             BUFFER SORT              |                             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 27 |              FIXED TABLE FIXED INDEX | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q2,00 | PCWP |            |
|* 28 |        INDEX RANGE SCAN              | I_OBJ1                      |     1 |       |     2   (0)| 00:00:01 |        |      |            |
|  29 |       TABLE ACCESS BY INDEX ROWID    | OBJ$                        |     1 |    33 |     3   (0)| 00:00:01 |        |      |            |
|  30 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  31 |       PX SEND QC (RANDOM)            | :TQ30000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | P->S | QC (RAND)  |
|  32 |        VIEW                          | GV$PROCESS                  |       |       |            |          |  Q3,00 | PCWP |            |
|* 33 |         FIXED TABLE FULL             | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q3,00 | PCWP |            |
|  34 |     BUFFER SORT                      |                             |     1 |       |     5  (20)| 00:00:01 |        |      |            |
|  35 |      FIXED TABLE FULL                | X$KCCDI2                    |     1 |       |     0   (0)|          |        |      |            |
|* 36 |    HASH JOIN OUTER                   |                             |     1 |   286 |     1 (100)| 00:00:01 |        |      |            |
|* 37 |     HASH JOIN OUTER                  |                             |     1 |   267 |     0   (0)|          |        |      |            |
|* 38 |      FILTER                          |                             |       |       |            |          |        |      |            |
|* 39 |       HASH JOIN OUTER                |                             |     1 |   228 |     0   (0)|          |        |      |            |
|  40 |        NESTED LOOPS                  |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  41 |         NESTED LOOPS                 |                             |     1 |   189 |     0   (0)|          |        |      |            |
|  42 |          NESTED LOOPS                |                             |     1 |    97 |     0   (0)|          |        |      |            |
|* 43 |           PX COORDINATOR             |                             |       |       |            |          |        |      |            |
|  44 |            PX SEND QC (RANDOM)       | :TQ40000                    |     1 |    26 |     0   (0)|          |  Q4,00 | P->S | QC (RAND)  |
|* 45 |             VIEW                     | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  Q4,00 | PCWP |            |
|* 46 |              FIXED TABLE FULL        | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  Q4,00 | PCWP |            |
|  47 |           TABLE ACCESS BY INDEX ROWID| SCHEDULER$_LIGHTWEIGHT_JOB  |     1 |    39 |     0   (0)|          |        |      |            |
|* 48 |            INDEX UNIQUE SCAN         | SCHEDULER$_LWJOB_PK         |     1 |       |     0   (0)|          |        |      |            |
|* 49 |          INDEX RANGE SCAN            | SCHEDULER$_LOBJ_UK          |     1 |       |     0   (0)|          |        |      |            |
|* 50 |         TABLE ACCESS BY INDEX ROWID  | SCHEDULER$_LWJOB_OBJ        |     1 |    92 |     0   (0)|          |        |      |            |
|* 51 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  52 |         PX SEND QC (RANDOM)          | :TQ50000                    |     1 |    39 |            |          |  Q5,00 | P->S | QC (RAND)  |
|* 53 |          VIEW                        | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q5,00 | PCWP |            |
|* 54 |           FIXED TABLE FULL           | X$JSKMIMRT                  |     1 |    39 |            |          |  Q5,00 | PCWP |            |
|* 55 |      PX COORDINATOR                  |                             |       |       |            |          |        |      |            |
|  56 |       PX SEND QC (RANDOM)            | :TQ60000                    |     1 |    28 |     0   (0)|          |  Q6,00 | P->S | QC (RAND)  |
|* 57 |        VIEW                          | GV$SESSION                  |       |       |            |          |  Q6,00 | PCWP |            |
|  58 |         MERGE JOIN CARTESIAN         |                             |     1 |    28 |     0   (0)|          |  Q6,00 | PCWP |            |
|  59 |          NESTED LOOPS                |                             |     1 |    12 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 60 |           FIXED TABLE FIXED INDEX    | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 61 |           FIXED TABLE FIXED INDEX    | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  Q6,00 | PCWP |            |
|  62 |          BUFFER SORT                 |                             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|* 63 |           FIXED TABLE FIXED INDEX    | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  Q6,00 | PCWP |            |
|  64 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  65 |      PX SEND QC (RANDOM)             | :TQ70000                    |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | P->S | QC (RAND)  |
|  66 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  Q7,00 | PCWP |            |
|* 67 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  Q7,00 | PCWP |            |
|* 68 |    HASH JOIN OUTER                   |                             |     1 |   202 |     1 (100)| 00:00:01 |        |      |            |
|  69 |     MERGE JOIN CARTESIAN             |                             |     1 |   183 |     0   (0)|          |        |      |            |
|* 70 |      HASH JOIN OUTER                 |                             |     1 |   179 |     0   (0)|          |        |      |            |
|* 71 |       HASH JOIN                      |                             |     1 |   140 |     0   (0)|          |        |      |            |
|* 72 |        HASH JOIN                     |                             |     1 |    82 |     0   (0)|          |        |      |            |
|* 73 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  74 |          PX SEND QC (RANDOM)         | :TQ80000                    |     1 |    69 |            |          |  Q8,00 | P->S | QC (RAND)  |
|* 75 |           VIEW                       | GV$SCHEDULER_INMEM_RTINFO   |       |       |            |          |  Q8,00 | PCWP |            |
|* 76 |            FIXED TABLE FULL          | X$JSKMIMRT                  |     1 |    69 |            |          |  Q8,00 | PCWP |            |
|  77 |         PX COORDINATOR               |                             |       |       |            |          |        |      |            |
|  78 |          PX SEND QC (RANDOM)         | :TQ90000                    |     1 |    13 |            |          |  Q9,00 | P->S | QC (RAND)  |
|  79 |           VIEW                       | GV$SCHEDULER_INMEM_MDINFO   |       |       |            |          |  Q9,00 | PCWP |            |
|  80 |            FIXED TABLE FULL          | X$JSKMIMMD                  |     1 |    13 |            |          |  Q9,00 | PCWP |            |
|* 81 |        PX COORDINATOR                |                             |       |       |            |          |        |      |            |
|  82 |         PX SEND QC (RANDOM)          | :TQ100000                   |     1 |    26 |     0   (0)|          |  10,00 | P->S | QC (RAND)  |
|* 83 |          VIEW                        | GV$SCHEDULER_RUNNING_JOBS   |       |       |            |          |  10,00 | PCWP |            |
|* 84 |           FIXED TABLE FULL           | X$JSKSLV                    |     1 |    26 |     0   (0)|          |  10,00 | PCWP |            |
|* 85 |       PX COORDINATOR                 |                             |       |       |            |          |        |      |            |
|  86 |        PX SEND QC (RANDOM)           | :TQ110000                   |     1 |    28 |     0   (0)|          |  11,00 | P->S | QC (RAND)  |
|* 87 |         VIEW                         | GV$SESSION                  |       |       |            |          |  11,00 | PCWP |            |
|  88 |          MERGE JOIN CARTESIAN        |                             |     1 |    28 |     0   (0)|          |  11,00 | PCWP |            |
|  89 |           NESTED LOOPS               |                             |     1 |    12 |     0   (0)|          |  11,00 | PCWP |            |
|* 90 |            FIXED TABLE FIXED INDEX   | X$KSLWT (ind:1)             |     1 |     8 |     0   (0)|          |  11,00 | PCWP |            |
|* 91 |            FIXED TABLE FIXED INDEX   | X$KSLED (ind:2)             |     1 |     4 |     0   (0)|          |  11,00 | PCWP |            |
|  92 |           BUFFER SORT                |                             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|* 93 |            FIXED TABLE FIXED INDEX   | X$KSUSE (ind:1)             |     1 |    16 |     0   (0)|          |  11,00 | PCWP |            |
|  94 |      BUFFER SORT                     |                             |     1 |     4 |     0   (0)|          |        |      |            |
|  95 |       TABLE ACCESS CLUSTER           | USER$                       |     1 |     4 |     0   (0)|          |        |      |            |
|* 96 |        INDEX UNIQUE SCAN             | I_USER#                     |     1 |       |     0   (0)|          |        |      |            |
|  97 |     PX COORDINATOR                   |                             |       |       |            |          |        |      |            |
|  98 |      PX SEND QC (RANDOM)             | :TQ120000                   |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | P->S | QC (RAND)  |
|  99 |       VIEW                           | GV$PROCESS                  |       |       |            |          |  12,00 | PCWP |            |
|*100 |        FIXED TABLE FULL              | X$KSUPR                     |   173 |  2595 |     1 (100)| 00:00:01 |  12,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------

Yep, to top it all off, that’s a parallel query execution too.

The ugly part occurred when this query got deployed and the queue-based processing that contained the modified code went from processing 500+ messages per second down to just 2. Ouch!

Thankfully the problem was trivial to diagnose and easily fixed so no damage was done.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s