14.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.
14.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 could be rewritten to
use bind variables. In that case, the SQL statement in question could
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 multi-user 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.
14.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 defined
The 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 could rewrite this statement as follows:
SELECT CUSTOMER.CUST_NBR, CUSTOMER.NAME, CUST_ORDER.ORDER_NBR
FROM CUSTOMER, CUST_ORDER
WHERE 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, 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.
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.
 |
Table aliases are not limited to one character in length. Table
aliases can be up to 30 characters in length.
|
|
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 in a query, you
will encounter errors. For example:
SELECT C.CUST_NBR, C.NAME, O.ORDER_NBR
FROM CUSTOMER C, CUST_ORDER O
WHERE CUSTOMER.CUST_NBR = CUST_ORDER.CUST_NBR;
WHERE CUSTOMER.CUST_NBR = CUST_ORDER.CUST_NBR
*
ERROR at line 3:
ORA-00904: invalid column name
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 your 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.
|
