| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
You can use view objects to read rows of data, create and store rows of transient data, as well as automatically coordinate inserts, updates, and deletes made by end users with your underlying business objects. How you design and use your view objects can definitely affect their performance at runtime. This section provides guidance on configuring your view objects to get the best possible performance.
Whenever the WHERE clause of your query includes values that might change from execution to execution, you should use named bind variables. Their use also protects your application against abuse through SQL injection attacks by malicious end-users.
Bind variables are place holders in the SQL string whose value you can easily change at runtime without altering the text of the SQL string itself. Since the query text doesn't change from execution to execution, the database can efficiently reuse the same parsed statement each time. Avoiding re-parsing of your statement alleviates the database from having to continually re-determine its query optimization plan and eliminates contention by multiple end-users on other costly database resources used during this parsing operation. This savings leads to higher runtime performance of your application. See Section 5.9, "Using Named Bind Variables" for details on how to use named bind variables.
Using bind variables for parameterized WHERE clause values is especially important if their values will be supplied by end-users of your application. Consider the example shown in Example 27-2. It adds a dynamic WHERE clause formed by concatenating a user-supplied parameter value into the statement.
Example 27-2 Using String Concatenation Instead of Bind Variables is Vulnerable to SQL-Injection Attacks
// EXAMPLE OF BAD PRACTICE, Do not follow this approach!
String userSuppliedValue = ... ;
yourViewObject.setWhereClause("BANK_ACCOUNT_ID = "+userSuppliedValue);
A user with malicious intentions — if able to learn any details about your application's underlying database schema — could supply a carefully-constructed "bank account number" as a field value or URL parameter like:
BANK_ACCOUNT_ID
When the code in Example 27-2 concatenates this value into the dynamically-applied where clause, what the database sees is a query predicate like this:
WHERE (BANK_ACCOUNT_ID = BANK_ACCOUNT_ID)
This WHERE clause retrieves all bank accounts instead of just the current user's, perhaps allowing the hacker to view private information of another person's account. This technique of short-circuiting an application's WHERE clause by trying to supply a maliciously-constructed parameter value into a SQL statement is called a SQL injection attack. Using named bind variables instead for these situations as shown in Example 27-3 prevents the vulnerability.
Example 27-3 Use Named Bind Variables Instead of String Concatenation
// Best practice using named bind variables
String userSuppliedValue = ... ;
yourViewObject.setWhereClause("BANK_ACCOUNT_ID = :BankAcccountId");
yourViewObject.defineNamedWhereClauseParam("BankAcccountId", null, null);
yourViewObject.setNamedWhereClauseParam("BankAcccountId",userSuppliedValue);
If a malicious user supplies an illegal value in this case, they receive an error your application can handle instead of obtaining data they are not suppose to see.
View objects can either be related to underlying entity objects or not. When a view object is related to one or more underlying entity objects you can create new rows, and modify or remove queried rows. The view object coordinates with underlying entity objects to enforce business rules and to permanently save the changes. In addition, entity-based view objects:
Immediately reflect pending changes made to relevant entity object attributes made through other view objects in the same transaction
Initialize attribute values in newly created rows to the values from the underlying entity object attributes
Reflect updated reference information when foreign key attribute values are changed
On the other hand, view objects that are not related to any entity object are read-only, do not pickup entity-derived default values, do not reflect pending changes, and do not reflect updated reference information. You need to decide what kind of functionality your application requires and design the view object accordingly. Typically view objects used for SQL-based validation purposes, as well as for displaying the list of valid selections in a dropdown list, can be read-only.
There is a small amount of runtime overhead associated with the coordination between view object rows and entity object rows, so if you don't need any of the functionality offered by an entity-mapped view object, you can slightly increase performance by using a read-only view object with no related entity objects.
After deciding whether your view object should be mapped to entities or not, your attention should turn to the query itself. The Explain Plan button on the Query panel of the View Object Editor allows you to see the query plan that the database query optimizer will use. If you see that it is doing a full table scan, you should consider adding indexes or providing a value for the Optimizer Hint field on the Tuning panel to explicitly control which query plan will be used. These facilities provide some useful tools to the developer to evaluate the query plans for individual view object SQL statements. However, their use is not a substitute for tracing the SQL of the entire application to identify poorly performing queries in the presence of a production environment's amount of data and number of end users.
You can use the Oracle database's SQL Tracing facilities to produce a complete log of all SQL statements your application performs. The approach that works in all versions of the Oracle database is to issue the command:
ALTER SESSION SET SQL_TRACE TRUE
This command enables tracing of the current database session and logs all SQL statements to a server-side trace file until you either enter ALTER SESSION SET SQL_TRACE FALSE or close the connection. To simplify enabling this option to trace your ADF applications, override the afterConnect() method of your application module (or custom application module framework extension class) to conditionally perform the ALTER SESSION command to enable SQL tracing based on the presence of a Java system property as shown in Example 27-4.
Example 27-4 Conditionally Enabling SQL Tracing in an Application Module
// In YourCustomApplicationModuleImpl.java
protected void afterConnect() {
super.afterConnect();
if (System.getProperty("enableTrace") != null) {
getDBTransaction().executeCommand("ALTER SESSION SET SQL_TRACE TRUE");
}
}
After producing a trace file, you use the tkprof utility supplied with the database to format the information and to better understand information about each query executed like:
The number of times it was (re)parsed
The number of times it was executed
How many round-trips were made between application server and the database
Various quantitative measurements of query execution time
Using these techniques, you can decide which additional indexes might be required to speed up particular queries your application performs, or which queries could be changed to improve their query optimization plan.
|
Note: The Oracle 10g database provides the newDBMS_MONITOR package that further simplifies SQL tracing and integrates it with Oracle Enterprise Manager for visually monitoring the most frequently performed query statements your applications perform. |
The Tuning panel of the View Object Editor lets you set various options that can dramatically effect your query's performance.
The Retrieve from the Database section, controls how the view object retrieves rows from the database server. The options for the fetch mode are All Rows, At Most One Row, and No Rows. Most view objects will stick with the default All Rows option, which will be retrieved As Needed or All at Once depending on which option you choose. The "as needed" option ensures that an executeQuery() operation on the view object initially retrieves only as many rows as necessary to fill the first page of a display, whose number of rows is set based on the view object's range size.
For view objects whose WHERE clause expects to retrieve a single row, set the option to At Most One Row for best performance. This way, the view object knows you don't expect any more rows and will skip its normal test for that situation. Finally, if you use the view object only for creating new rows, set the option to No Rows so no query will ever be performed.
The fetch size controls how many rows will be returned in each round trip to the database. By default, the framework will fetch rows in batches of one row at a time. If you are fetching any more than one row, you will gain efficiency by setting this in Batches of value.
However the higher the number, the larger the client-side buffer required, so avoid setting this number arbitrarily high. If you are displaying results N rows at a time in the user interface, it's good to set the fetch size to at least N+1 so that each page of results can be retrieved in a single round trip to the database.
|
Caution: Unless your query really fetches just one row, leaving the default fetch size of one (1) in the in Batches of field on the Tuning panel is a recipe for bad performance due to many unnecessary round trips between the application server and the database. Oracle strongly recommends considering the appropriate value for each view object's fetch size. |
The Query Optimizer Hint field allows you to specify an optional hint to the Oracle query optimizer to influence what execution plan it will use. At runtime, the hint you provide is added immediately after the SELECT keyword in the query, wrapped by the special comment syntax /*+ YOUR_HINT */. Two common optimizer hints are:
FIRST_ROWS — to hint that you want the first rows as quickly as possible
ALL_ROWS — to hint that you want all rows as quickly as possible
There are many other optimizer hints that are beyond the scope of this manual to document. Reference the Oracle 10g database reference manuals for more information on available hints.
It's important to understand the overhead associated with creating view objects at runtime. Avoid the temptation to do this without a compelling business requirement. For example, if your application issues a query against a table whose name you know at design time and if the list of columns to retrieve is also fixed, then create a view object at design time. When you do this, your SQL statements are neatly encapsulated, can be easily explained and tuned during development, and incur no runtime overhead to discover the structure and data types of the resulting rows.
In contrast, when you use the createViewObjectFromQueryStmt() API on the ApplicationModule interface at runtime, your query is buried in code, it's more complicated to proactively tune your SQL, and you pay a performance penalty each time the view object is created. Since the SQL query statement for a dynamically-created view object could theoretically be different on each execution, an extra database round trip is required to discover the "shape" of the query results on-the-fly. Only create queries dynamically if you cannot know the name of the table to query until runtime. Most other needs can be addressed using a design-time created view object in combination with runtime API's to set bind variables in a fixed where clause, or to add an additional WHERE clause (with optional bind variables) at runtime.
Often you will use write code that programmatically iterates through the results of a view object. A typical situation will be custom validation code that must process multiple rows of query results to determine whether an attribute or an entity is valid or not. In these cases, if you intend to read each row in the row set a single time and never require scrolling backward or re-iterating the row set a subsequent time, then you can use "forward only" mode to avoid caching the retrieved rows. To enable forward only mode, call setForwardOnly(true) on the view object.
|
Note: Using a read-only view object (with no entity usages) in forward-only mode with an appropriately tuned fetch size is the most efficient way to programmatically read data. |
You can also use forward-only mode to avoid caching rows when inserting, updating, or deleting data as long as you never scroll backward through the row set and never call reset() to set the iterator back to the first row. Forward only mode only works with a range size of one (1).
