15.2 Avoid Unnecessary Parsing
Before your SQL can be executed by Oracle, it needs to be parsed. The importance of parsing when it comes to tuning SQL lies in the fact that no matter how many times a given SQL statement is executed, it needs to be parsed only once. During parsing, the following steps are performed (not necessarily in the sequence shown):
The syntax of the SQL statement is verified.
The data dictionary is searched to verify table and column definitions.
The data dictionary is searched to verify security privileges on relevant objects.
Parse locks are acquired on the relevant objects.
The optimal execution plan is determined.
The statement is loaded into the shared SQL area (also known as the library cache) in the shared pool of the system global area (SGA). The execution plan and parse information are saved here in case the same statement is executed once again.
If a SQL statement involves any remote objects (e.g., database links), then these steps are repeated for the remote objects. As you can see, lots of work is performed during the parsing of a SQL statement. However, a statement is parsed only if Oracle doesn't find an identical SQL statement already in the shared SQL area (library cache) of the SGA.
Before parsing a SQL statement, Oracle searches the library cache for an identical SQL statement. If Oracle finds an exact match, there is no need to parse the statement again. However, if an identical SQL statement is not found, Oracle goes through all the aforementioned steps to parse the statement.
The most important keyword in the previous paragraph is "identical." To share the same SQL area, two statements need to be truly identical. Two statements that look similar, or that return the same result, need not be identical. To be truly identical, the statements must:
Have the same uppercase and lowercase characters
Have the same whitespace and newline characters
Reference the same objects using the same names, which must in turn have the same owners
If there is a possibility that your application executes the same (or similar) SQL statements multiple times, by all means try to avoid unnecessary parsing. This will improve the overall performance of your applications. The following techniques can help you reduce SQL parsing:
Use bind variables.
Use table aliases.
15.2.1 Using Bind Variables
When multiple users use an application, they actually execute the same set of SQL statements over and over, but with different data values. For example, one customer service representative may be executing the following statement:
SELECT * FROM customer WHERE cust_nbr = 121;
while another customer service representative will be executing:
SELECT * FROM customer WHERE cust_nbr = 328;
These two statements are similar, but not "identical"?the customer ID numbers are different; therefore, Oracle has to parse twice.
Because the only difference between these statements is the value used for the customer number, this application can be rewritten to use bind variables. In that case, the SQL statement in question can be as follows:
SELECT * FROM customer WHERE cust_nbr = :x;
Oracle needs to parse this statement only once. The actual customer numbers would be supplied after parsing for each execution of the statement. Multiple, concurrently executing programs could share the same copy of this SQL statement while at the same time supplying different customer number values.
In a multiuser application, situations such as the one described here are very common, and overall performance can be significantly improved by using bind variables, thereby reducing unnecessary parsing.
15.2.2 Using Table Aliases
The use of table aliases can help to improve the performance of your SQL statements. Before getting into the performance aspects of table aliases, let's quickly review what table aliases are and how they are used.
When you select data from two or more tables, you should specify which table each column belongs to. Otherwise, if the two tables have columns with the same name, you will end up with an error:
SELECT cust_nbr, name, order_nbr
FROM customer, cust_order;
SELECT cust_nbr, name, order_nbr
*
ERROR at line 1:
ORA-00918: column ambiguously definedThe error in this case occurs because both the customer and cust_order tables have columns named cust_nbr. Oracle can't tell which cust_nbr column you are referring to. To fix this problem, you can rewrite this statement as follows:
SELECT customer.cust_nbr, customer.name, cust_order.order_nbr
FROM customer JOIN cust_order
ON customer.cust_nbr = cust_order.cust_nbr;
CUST_NBR NAME ORDER_NBR
---------- ------------------------------ ----------
1 Cooper Industries 1001
1 Cooper Industries 1000
5 Gentech Industries 1002
4 Flowtech Inc. 1003
4 Flowtech Inc. 1004
8 Zantech Inc. 1005
1 Cooper Industries 1006
5 Gentech Industries 1007
5 Gentech Industries 1008
1 Cooper Industries 1009
1 Cooper Industries 1012
1 Cooper Industries 1011
5 Gentech Industries 1015
4 Flowtech Inc. 1017
4 Flowtech Inc. 1019
8 Zantech Inc. 1021
1 Cooper Industries 1023
5 Gentech Industries 1025
5 Gentech Industries 1027
1 Cooper Industries 1029
20 rows selected.Note the use of the table name to qualify each column name. This eliminates any ambiguity as to which cust_nbr column the query is referring to.
Instead of qualifying column names with full table names, you can use table aliases, as in the following example:
SELECT c.cust_nbr, c.name, o.order_nbr
FROM customer c JOIN cust_order 0
ON c.cust_nbr = o.cust_nbr;
CUST_NBR NAME ORDER_NBR
---------- ------------------------------ ----------
1 Cooper Industries 1001
1 Cooper Industries 1000
5 Gentech Industries 1002
4 Flowtech Inc. 1003
4 Flowtech Inc. 1004
8 Zantech Inc. 1005
1 Cooper Industries 1006
5 Gentech Industries 1007
5 Gentech Industries 1008
1 Cooper Industries 1009
1 Cooper Industries 1012
1 Cooper Industries 1011
5 Gentech Industries 1015
4 Flowtech Inc. 1017
4 Flowtech Inc. 1019
8 Zantech Inc. 1021
1 Cooper Industries 1023
5 Gentech Industries 1025
5 Gentech Industries 1027
1 Cooper Industries 1029
20 rows selected.The letters "c" and "o" in this example are table aliases. You can specify these aliases following their respective table names in the FROM clause, and they can be used everywhere else in the query in place of the table name. Table aliases provide a convenient shorthand notation, allowing your queries to be more readable and concise.
|
An important thing to remember while using table aliases is that if you define aliases in the FROM clause, you must use only those aliases, and not the actual table names, in the rest of the query. If you alias a table, and then use the actual table name elsewhere in the query, you will encounter errors. For example:
SELECT c.cust_nbr, c.name, o.order_nbr
FROM customer c JOIN cust_order o
ON customer.cust_nbr = cust_order.cust_nbr;
WHERE customer.cust_nbr = cust_order.cust_nbr
*
ERROR at line 3:
ORA-00904: invalid column nameMany developers make the mistake of forgetting to use their table aliases while writing hints. Once you define an alias, you must specify the alias instead of the actual table name in any hints; otherwise, those hints will be silently ignored. Here's an example of this common mistake:
SELECT /*+ USE_HASH(customer cust_order) */ c.cust_nbr, c.name, o.order_nbr FROM customer c JOIN cust_order o ON c.cust_nbr = o.cust_nbr;
The USE_HASH hint specifies the customer and cust_order tables. However, the FROM clause provides aliases for both those tables. Because of the aliases, the table names have no meaning, and the hint is ignored, possibly to the detriment of performance. Following is the correct version of this query:
SELECT /*+ USE_HASH(c o) */ c.cust_nbr, c.name, o.order_nbr FROM customer c JOIN cust_order o ON c.cust_nbr = o.cust_nbr;
This time, the USE_HASH hint properly uses the table aliases that have been defined in the FROM clause. This hint will have the desired effect.
When selecting data from multiple tables, it makes sense to qualify each column name with its corresponding table alias. The column cust_nbr appears in both the customer and cust_order tables. Without proper qualification, this column is said to be "ambiguously defined" in the query. Therefore, you must qualify the cust_nbr column with a table alias (or a full table name, if you are not using aliases). However, the other two columns used in the query are not ambiguous. Therefore, the following statement, which only qualifies the cust_nbr column, is valid:
SELECT c.cust_nbr, name, order_nbr
FROM customer c, cust_order o
WHERE c.cust_nbr = o.cust_nbr;
CUST_NBR NAME ORDER_NBR
---------- ------------------------------ ----------
1 Cooper Industries 1001
1 Cooper Industries 1000
5 Gentech Industries 1002
4 Flowtech Inc. 1003
4 Flowtech Inc. 1004
8 Zantech Inc. 1005
1 Cooper Industries 1006
5 Gentech Industries 1007
5 Gentech Industries 1008
1 Cooper Industries 1009
1 Cooper Industries 1012
1 Cooper Industries 1011
5 Gentech Industries 1015
4 Flowtech Inc. 1017
4 Flowtech Inc. 1019
8 Zantech Inc. 1021
1 Cooper Industries 1023
5 Gentech Industries 1025
5 Gentech Industries 1027
1 Cooper Industries 1029
20 rows selected.This is where the performance aspect of using table aliases comes into play. Since the query doesn't qualify the columns NAME and ORDER_NBR, Oracle has to search both the CUSTOMER and CUST_ORDER tables while parsing this statement to find which table each of these columns belongs to. The time required for this search may be negligible for one query, but it does add up if you have a number of such queries to parse. It's good programming practice to qualify all columns in a query with table aliases, even those that are not ambiguous, so that Oracle can avoid this extra search when parsing the statement.
