Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Tuning Oracle SQL that has views

Oracle Database Tips by Donald BurlesonJuly 2, 2015

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

See my notes here on the benefits of 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.  Also see: Guard against performance issues when using Oracle hints and 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.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



 

   

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.