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.
Mark Hoxey 
We’re having similar issues. We query the same view to try to determine if a job is already running. The performance has been degrading for some time and is now very poor.
Can you comment on what your solution was?
Hi Chad,
If I recall correctly we resolved or problem by reworking the process and removing the query altogether. Sorry, probably not the answer you are looking for.
Mark