Monday, January 11, 2010

Section 14.2. Avoid Unnecessary Parsing








 

 












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.


















     

     


    No comments: