Database News You Can Use
A monthly newsletter for Oracle usersSeptember 2003
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Slow Query Response Times Thanks to Jonathan G. for submitting last month’s question on null events. You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question gets published, you’ll receive a free copy of our reference poster on Oracle Wait Events. So, stay tuned for future issues of The Specialist! Resource Round-up from Oracle Users Groups
This month’s question comes from Kimberly R. in San Francisco, California: Why do queries that access views with columns based on PL/SQL functions sometimes have such slow response times?
Brian Keating of the Database Specialists team responds: Oracle views usually have columns defined as one-to-one mappings to columns in base tables. For example, a column called “policy_idn” in a view may be defined as a one-to-one mapping to the “idn” column in a table called Policies. However, it is also possible to define columns in views as PL/SQL function calls. When a column is defined as a function call, the PL/SQL object in question must be executed in order to retrieve the data in that column. There are some very definite performance issues that can arise when columns are defined as function calls, which I will discuss in this column.
An example of a view that has a column defined as a function call can be seen in the “WF_Worklist_V” view, which is an object in the Oracle Workflow product. An excerpt of that view’s definition is as follows (the definition of the “Subject” column is listed in blue):
SQL> select text from dba_views where view_name = 'WF_WORKLIST_V'; TEXT ----------------------------------------------------------------------- select /* $Header: wfntfv.sql 25.4 2000/05/24 17:49 dlam ship $ */ WN.ROWID, WN.NOTIFICATION_ID, WN.PRIORITY, WIT.DISPLAY_NAME, WN.RECIPIENT_ROLE, Wf_Notification.GetSubject(notification_id), ...
From the view definition, we can see that the Subject column is defined as a function call—whenever a SQL statement accesses the Subject column from that view, the GetSubject function has to be executed to retrieve that data.
The more important item to note, though, is that the GetSubject function takes an argument—it requires a notification_id to be passed to it. This means that if a SQL statement accesses the Subject column in this view, then the GetSubject function will look for a notification_id value in that SQL statement. If a notification_id is provided in the SQL statement, then GetSubject will use that value as its argument. However, if a notification_id is not provided in the statement, then what happens?
It turns out that if a notification_id is not provided in the SQL statement, then the GetSubject function has to be executed one time for each notification_id that exists in the entire possible result set. This is because the function requires a notification_id – so if a notification_id is not provided, the function will not know what value to use; and so it will have to call the function for every possible value that could appear in the view.
In this case, the notification_id value in the GetSubject function call is referring to the values in the notification_id column of the WF_Notifications table. When I first investigated this issue on a recent project, there were about 80,000 unique notification_id values in the WF_Notifications table – and therefore, whenever a SQL statement accessed the Subject column of WF_Worklist_V without specifying a notification_id, the GetSubject function had to be executed 80,000 times! This caused those SQL statements to have extremely slow response times (several minutes) – despite the fact that the explain plans for the statements did not look that bad.
I will describe this issue in greater detail in an upcoming paper. Stay tuned!
If you missed the Summer Conferences of the Northern California and Los Angeles Oracle Users Groups, be sure to check out their websites. You’ll find the latest resources made available by the outstanding speakers at their meetings. On the NoCOUG website at http://www.nocoug.org/presentations.html, you’ll find topics such as: Free Oracle Tutorials and Resources
On the LAOUG website, you’ll find topics such as:
You’ll find lots of Oracle tutorials and resources at a website dedicated to free programming resources compiled from hundreds of sources. There’s information on everything from Java to Cold Fusion to Flash and C/C++. Oracle information includes: A Light-Hearted Look at the DBA Profession
Check it out at http://www.freeprogrammingresources.com/oracle.html.
Author and Oracle expert Don Burleson took a light-hearted look at the DBA profession in a recent issue of DBAzine. In his article titled What Type of DBA Are You?—Tales From the Trenches, Don writes, “As a whole, I find that DBAs are intelligent, persistent and hard-working IT professionals. However, I am always surprised at their diverse personalities. A DBA can be shy or gregarious, immaculate or slovenly, charming or abrasive.
“As a DBA consultant, I have worked with hundreds of DBAs over the past 20 years and I meet new DBAs every week. As a consultant, I do not always have time to get to know a DBA on a personal level, and I must make a quick evaluation based on limited data. Over the years, I have developed three personality types for DBAs to help me to understand them better and to allow me to work with them more effectively. I call my categories the Scientist DBA, the Gung-Ho DBA, and the Empathetic DBA.”