SQL tuning with views

Oracle views provide a benefit in these important 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.

But 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).

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.  Read on to see important tips and tricks for tuning SQL that contains views.

http://www.dba-oracle.com/t_tuning_sql_execution_views.htm


*****************************************
Can you see your Oracle signatures?

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:

www.ion-dba.com

*****************************************

Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf