SQL tuning with views

The relational database model gave us the concept of views to simplify relational algebra, providing a way to “name” a complex query and treat it as-if it were a discrete table:
create view
myview
as
select   stuff from   tableA natural join  tableB
natiral join  tableC;
Now, we can logically simplify the query:
select stuff from myview where xxx=yyy;
The benefits to views
In sum, views provide a benefit in these areas:
·    Code reuse – Views ensure that everyone uses the exact same SQL to access their tables
·    Column access security – Using the “grant” security model, views can be used for column-level security, such that some columns in a table may be “hidden” by not specifying them in a view.
All benefits come at a cost, and one downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources (either I/O or CPU resources, depending on your optimizer goal).
The problem of tuning SQL that contains views
While it’s clear that views are useful for end-user ad-hoc queries and cases where we want to simplify the syntax of complex SQL queries, we have a serious problem when queries contain views.
·    Predicate pushing – The downside to re-usable code is that where clause predicates must be “pushed” down the hierarchy of views, down to the base query.  This adds processing overhead to the optimizer and increases the chances of a bad execution plan.
·    Non mergeable views – Because a view is an encapsulation of a complex query, it is used as-if it were a discrete relational table.  Hence, Oracle must pre-materialize a view whenever it is used in a query.  This create a hidden sub-plan that must be exposed for SQL tuning.
·    Unnecessary overhead – Views are abused when they introduce unnecessary complexity.  For example, you may call a view that is composed of 10 tables where the result set only requires data from two tables.
·    Excessive hard parsing – Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:
SELECT *
FROM
vw_layer_two_dept_100
WHERE
empid=:b100;
To understand why views make it difficult to tune your SQL, let’s see what happens when a views is used with a where clause.  To function, Oracle must “push” the where clause predicate to the underlying view.
Predicate pushing with views
The Oracle SQL tuning problem becomes a nightmare when views are nested within other views.  Oracle supports pushing of predicates into a given view. Assume we have a set of nested views, like this, where view1 id referenced inside view2:
– View One
CREATE VIEW
vw_layer_one
AS
SELECT * FROM emp;
– view two
CREATE VIEW
vw_layer_two_dept_100
AS
SELECT * FROM vw_layer_one
WHERE deptno=100;
Then assume we issued this query:
SELECT *
FROM
vw_layer_two_dept_100
WHERE empid=100;
The predicate in this SQL is the where empid=100 statement. You may have one of tens or even hundreds of predicates. The Oracle optimizer is written to “push” predicates down into the views that are being referenced in the SQL. Thus, Oracle will transform the vw_layer_one view into a SQL statement that looks like this:
CREATE VIEW
vw_layer_one
AS
SELECT * FROM emp
WHERE
deptno=100
AND
empid=100;
Note that both the predicate from view two (where deptno=100) and the predicate from the SQL statement being executed (where empid=100) are pushed down into the final view that is executed. This can have significant performance benefits because now the bottom view can possibly use an index if one exists on deptno and/or empid.
Predicate pushing can get very complex and predicate pushing also has several performance gotchas:
·    Unnecessary overhead – Views are abused when they introduce unnecessary complexity.  For example, you may call a view that is composed of 10 tables where the result set only requires data from two tables.
·    Hard parsing – Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure you use bind variables instead of literals in SQL code calling views. Thus, our SQL should look something like this instead for best performance:
SELECT *
FROM
vw_layer_two_dept_100
WHERE
empid=:b100;

Abusing views in Oracle SQL
Views are very handy but they get badly abused, which is a shame. I’ve seen views that return 50 columns, and have 40 predicates used to return just two or three columns that could easily have been retrieved from a simple SQL query. This is clearly a case of view abuse, and can lead to badly performing views.
Stacked views can also mask performance problems. Again, they can result in innumerable columns being returned when all you really need are a few of those columns. Also, predicate pushing tends to break down as you stack more views on top of more views.
If you are going to start stacking views, carefully review the rules for predicate pushing in the Oracle documentation. But beware, the predicate pushing rules are long and involved and may change between each release of Oracle!
Merging views and SQL tuning
As we have seen, at optimization time, the CBO will attempt to “flatten out” the views by building and optimizing one large query against the base tables that comprise the view.  This is known as “merging” views, and a merge is critical to avoid a hidden sub-plan from being introduced into your SQL.
Whenever you are tuning SQL and you see the VIEW notation in  the explain plan, you may have a non-mergeable view:
select
count(1) from(
select distinct pat_id from
(
select
p.pat_id,
p.pat_last_name,
p.pat_first_name,
v.visit_id,
v.arrive_dt_tm,
v.depart_dt_tm,
r.test_id,
r.test_name,
r.result_dt_tm,
r.result_val
from
patient p,
pat_visit v,
pat_result r
where
p.pat_id=1
and
p.pat_id=v.pat_id
and
v.pat_id=r.pat_id)
);
Here we see the VIEW in the execution plan, indicating a sub-plan:

ID    PID    Operation Name                           Rows    Bytes    Cost    CPU Cost    IO Cost
0        SELECT STATEMENT                                1        2     8     25M          6
1    0      SORT AGGREGATE                               1        2
2    1        VIEW                                       1        2     8     25M          6
3    2          SORT UNIQUE NOSORT                       1       24     8     25M          6
4    3            HASH JOIN                            125     3000     7     8484914      6
5    4              NESTED LOOPS                         5       90     3     42157        3
6    5                INDEX UNIQUE SCAN    XPKPATIENT    1        9     0     1050         0
7    5                TABLE ACCESS FULL    PAT_VISIT     5       45     3     41107        3
8    4              TABLE ACCESS FULL     PAT_RESULT    25      150     3     63107        3

Note that the VIEW notation in an explain plan means that the view is not mergeable and Oracle must run a sub-plan (which is hidden).  Now, let’s revue some tips for ensuring that you get the fastest possible execution time when using views:
Tips for tuning SQL with views
It’s ironic that views make life simple for the developers but make life complex for the DBA’s who must tune the execution!  Here are some tricks for tuning SQL with views:
·    Use plan stability and SQL profiles – SQL profiles and optimizer plan stability )stored outlines) can swap-out a bad view plan with a correct plan using the base tables.
·    Use a stored procedure instead – Instead of a view, encapsulate the complex SQL inside a stored procedure.  This way, the SQL optimizer will not see any views because you manage the SQL within your own application.
·    Train your developers – If possible, teach the developers to write SQL using the base tables instead of relying on the cosmetic simplicity of views.
·    Optimize to use views – The all-powerful Oracle optimizer parameters can be used for holistic tuning of your entire workload.  For example, using optimizer_secure_view_merging causes Oracle to materialize the view results, resulting in faster query performance.
In sum, Oracle views are a necessary evil, but nesting of views with complex queries are a frequent cause of poor SQL performance.