Understanding the difference between a standard SQL query and an Oracle view is an important aspect of performance tuning. This article will outline the basics of Oracle view performance tuning and explain how SolarWinds® Database Performance Analyzer (DPA) can be used to speed up Oracle view performance.
What Are Oracle Views?
Simple Views
Complex Views
Potential Issues With Views
Hints and Views
Oracle View Performance Tuning
Current Activity Views
Current Sessions
Activity Monitors
Waits
Oracle View Performance Tuning With DPA
What Are Oracle Views?
An Oracle view is a validated and named SQL query stored in the Oracle Database’s data dictionary. Views are simply stored queries that can be executed when needed, but they don’t store data. It can be helpful to think of a view as a virtual table, or as the process of mapping data from one or more tables.
Views are useful for several purposes, including information hiding and security, but they can be problematic if they’re nested too deeply. The benefits of using views include reducing the complexity of SQL statements, hiding the NAME and OWNER of the base table, and only sharing specific table rows with other users.
View details can be queried from within the data dictionary, by using ALL_VIEWS, USER_VIEWS, or DBA_VIEWS. Oracle views can be classified as either complex or simple.
Simple Views
A simple view can only contain a single base table. Data Manipulation Language (DML) operations can be performed directly against simple views. DML changes are then applied to the base table of the view. A simple view might look something like this:
CREATE VIEW emp_view AS
SELECT * FROM emp;
CREATE VIEW dept20
AS SELECT ename, sal*12 annual_salary
FROM emp
WHERE deptno=20;
Complex Views
A complex view, unlike a simple view, can be constructed on more than one base table. Complex views can contain a group by clause, an order by clause, and join conditions. DML operations cannot be performed directly against complex views. If you want to enable DML operations on complex views, you’ll need to include INSTEAD OF triggers in your query. This informs Oracle of how the modifications relate to the base table or tables.
A complex view might look something like this:
CREATE VIEW sample_complex_view AS
SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
FROM emp, dept;
CREATE VIEW sample_complex_view AS
SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;
Potential Issues With Views
The use of complex views to represent a large subset of your scheme, and to allow end users and developers to access these views, is likely to result in poor performance. If you choose to work with complex views, here are some circumstances you should avoid, unless it’s not possible to do so:
- Adding complicated WHERE clauses: When a query is performed against a view with complex WHERE clauses, it can override tuning hints within the view. This will result in execution plans being suboptimal.
- Querying subsets: Developers often query a subset of a complex view, without realizing all the tables within the view will be joined.
- Hints and views: Views aren’t finite tables, and adding SQL hints to view queries often results in poorly performing execution plans. When the optimizer becomes “confused,” it will perform a full-table scan. Hints can be used for specific SQL optimization, but use with views isn’t recommended.
Oracle views encapsulate a complex query and should be used with the utmost care. Remember views aren’t intended as a means of improving SQL performance. If you need to encapsulate SQL, you should consider placing it inside a stored procedure instead of using a view. Views are designed, in part, to hide the complexity of the underlying query. This makes it easier for inexperienced programmers or end users to create queries.
Hints and Views
If you intend to use views to tune queries with hints, make sure the view is being used in the proper context. Here are two ways you can combine hints and views without creating performance issues:
- Add hints in the calling query. One of the dangers of using hints in views is it may change the context of the query. When this happens, any existing hints in the view definition might be ignored, which may confuse the optimizer. This will lead to an unnecessary full-table scan, which will impact performance.
- Embed hints in the view definition. This is especially useful in the case of views called without a WHERE clause. However, this approach can negatively impact performance if the view result set is modified by calling the view with a complex WHERE clause. When views are invoked with certain WHERE clauses, the view context may change, along with the functionality of any SQL hints embedded in the view.
When used separately, materialized views, Oracle views, and SQL query hints are useful tools for Oracle performance tuning. However, it’s important to take special care when implementing views to ensure they aren’t misused, which could potentially result in performance problems.
Oracle View Performance Tuning
If you want to optimize Oracle view performance, you should begin by tuning the underlying query. Once you have done this, the next step is to use the view to tune the query.
Views are processed by the Oracle optimizer in two ways:
- The first fully integrates the view into the query. This approach entails the view being processed, followed by the rest of the query conditions being applied, to produce the final result set. This may cause performance problems, depending largely on the size of the tables involved.
- The second method of using the view to tune the query involves the view being processed separately from the rest of the query. In this case, all the conditions of the query can be applied to the view, resulting in a smaller result set.
If a view contains certain set operations, then it cannot be integrated into the query. These set operations include the following:
- GROUP BY
- SUM
- COUNT
- DISTINCT
- MAX
- MIN
When the view produces a large result set or the result set needs to be filtered by additional conditions in the query, integrating the view with the rest of the query is likely the best approach. The Oracle optimizer will perform this process automatically, if it can.
In most cases, you’ll want views to be integrated with the rest of the query. This approach facilitates the best performance. However, in some cases—such as when the query contains a GROUP BY clause—grouping won’t occur until after the tables have been joined together. This can have a negative impact on performance under certain circumstances. To rectify this issue, you could force the view to be evaluated first, by moving the GROUP BY clause into the view. Starting with Oracle version 7.2, users can create implicit views by using a subquery in the FROM clause.
Views play a useful and important role in applications and can be used to boost Oracle performance. Whether a query uses views or not, it will need to be tested and carefully examined. Testing and examination will help you ensure performance won’t be drastically impacted when the application goes live.
Current Activity Views
Oracle has system views designed to show you the current session and wait for information. These can be helpful for troubleshooting activities and performance tuning.
Current Sessions
When a performance problem arises, you will need to locate and examine the current sessions on the database. While there is no sp_who, sp_who2, or sp_lock in Oracle, there is the v$session view. This view displays which sessions are currently active. To see all the queries being run by a session, you can join this view with another view.
Activity Monitors
Oracle gathers statistics as part of the Automatic Workload Repository (AWR) to deliver reports for analyzing database health and detecting performance problems. Snapshots are taken and stored to inform historical views. Viewing these areas of activity can help you troubleshoot performance problems by giving you insight into which areas might be experiencing an overload or responding slowly.
This process might help you identify instances of too many physical I/Os or hard parsing of SQL statements, for example. The “instance activity” section displays values collected since the database has been up and running, or back to the last available snapshot.
Waits
If you encounter Oracle performance issues, checking waits is highly recommended. You can access this information via OEM views. The SQL*Net message from the client event refers to Oracle wait for the client to tell the database server to perform an action. In this instance, the server is simply waiting for instructions, which shouldn’t cause any performance issues. However, some applications may open sessions and then wait for responses before retrieving the data from the database server.
Oracle View Performance Tuning With DPA
SolarWinds DPA for Oracle offers an impressive range of features designed to help you optimize Oracle view performance and query performance. This tool is highly sophisticated and user-friendly, and it offers an array of support functions. This includes support technicians available on a 24/7 basis, and a community of loyal users accessible via the SolarWinds THWACK® forum.
One of DPA’s best features is its focus on response time analysis, which helps you make your Oracle view performance tuning efforts more precise and effective. Response time analysis is a pragmatic approach to database performance tuning and optimization, allowing users to identify issues more easily and deliver measurable results. It assists in optimizing your Oracle database by helping you pinpoint the root causes of issues, identify bottlenecks, and prioritize actions according to the impact of poor performance on the end user. DPA was built to collate SQL statement data every second, making it easier to identify which SQL queries to focus on.
DPA includes numerous functionalities designed to handle database tuning, giving you in-depth insight into critical metrics. With this tool, you can view data in the form of intuitive graphs and charts. These graphical representations of data display poorly performing SQL statements, application wait times, and specific wait types/events that might be causing bottlenecks. You can drill down on specific issues, for troubleshooting purposes, by clicking on graph sections.
DPA gives you access to table tuning advisors, designed to take your Oracle view performance tuning efforts to the next level. The table tuning advisor collates historical data and presents information in the form of bar graphs, making it easier for you to jump into complex database tuning activities. This feature helps you identify problems in need of immediate attention by giving you clear and actionable advice. You can also leverage DPA’s alarms functionality to drill down into data surrounding an issue in real time.
The first step in Oracle view performance tuning is locating the root cause of the issue. DPA features database anomaly detection powered by machine learning, which means it learns and improves over time. Anomaly detection allows you to analyze your instance wait behavior profile, helping you quickly identify what needs fixing. You can then leverage SQL tuning advisors to analyze existing SQL statements and access recommendations on how to modify queries to improve performance.
DPA’s range of features guide the Oracle view performance tuning process, providing actionable advice and advanced analysis capabilities, all within a single interface.
Getting Started With DPA
Oracle view performance tuning is a complicated process and can easily be disrupted by several factors. Query and view optimization is time consuming, labor intensive, and prone to errors. To save yourself time and stress, I recommend using SolarWinds DPA to conduct Oracle view performance tuning activities. A 14-day free trial is available.