Optmizing View Object Run time performance

Fetch Tuning Parameters	Usage
Fetch Mode	The default fetch option is the All Rows option, which will be retrieved As Needed (FetchMode="FETCH_AS_NEEDED") or All at Once (FetchMode="FETCH_ALL"), depending on which option is desired. The As Needed option ensures that anexecuteQuery() 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.
Fetch Size	In conjunction with the Fetch Mode option, the in Batches of field controls the number of records fetched at one time from the database (FetchSize in the view object XML). The default value is 1, which will give poor performance unless only one row will be fetched. The suggested configuration is to set this value to n+1 where n is the number of rows to be displayed in the user interface.
Max Fetch Size	The default max fetch size for a view object is -1, which means that there is no limit to the number of rows the view object can fetch. In cases where the result set should contain only n rows of data, the option Only up to row number should be selected and set to n. The developer can alternatively call setMaxFetchSize(n) to set this programmatically or manually add the parameter MaxFetchSize to the view object XML. For view objects whose WHERE clause expects to retrieve a single row, set the option At Most One Row. This way the view object knows you don't expect any more rows and it will skip its normal test for that situation. As mentioned earlier, setting a maximum fetch size of 0 (zero) makes the view object insert-only. In this case, no select query will be issued, so no rows will be fetched. When you want to specify a global threshold for all view object queries in the application, you can configure the Row Fetch Limit property in the adf-config.xml file. Setting this property means you can avoid changing the Max Fetch Size for individual query operations. If you do specify a fetch limit for individual view objects, the Row Fetch Limit setting will be ignored in those cases. For more details about Row Fetch Limit, see Section 42.1.1, "Limiting the View Object Max Fetch Size to Fetch the First n Rows."
Forward-only Mode	If a data set will only be traversed going forward, then forward-only mode can help performance when iterating through the data set. This can be configured by programmatically calling setForwardOnly(true) on the view object. Setting forward-only will also prevent caching previous sets of rows as the data set is traversed.

When you tune view objects, you should also consider these issues:

• Large data sets: View objects provide a mechanism to page through large data sets such that a user can jump to a specific page in the results. This is configured by calling setRangeSize(n) followed by setAccessMode(RowSet.RANGE_PAGING) on the view object where n is the number of rows contained within one page. When the user navigates to a specific page in the data set, the application can call scrollToRangePage(P) on the view object to navigate to page P. Range paging fetches and caches only the current page of rows in the view object row cache at the cost of another query execution to retrieve each page of data. Range paging is not appropriate where it is beneficial to have all fetched rows in the view object row cache (for example, when the application needs to read all rows in a dataset for an LOV or page back and forth in records of a small data set.
• Spillover: There is a facility to use the data source as "virtual memory" when the JVM container runs out of memory. By default, this is disabled and can be turned on as a last resort by setting jbo.use.pers.coll=true. Enabling spillover can have a large performance impact.
• SQL platform: If the generic SQL92 SQL platform is used to connect to generic SQL92-compliant databases, then some view object tuning options will not function correctly. The parameter that choosing the generic SQL92 SQL platform affects the most is the fetch size. When SQL92 SQL platform is used, the fetch size defaults to 10 rows regardless of what is configured for the view object. You can set the SQL platform when you define the database connection or you can define it as global project setting in the adf-config.xml file. By default, the SQL platform will beOracle. To manually override the SQL platform, you can also pass the parameter -Djbo.SQLBuilder="SQL92" to the JVM upon startup.

Additionally, you have some options to tune the view objects' associated SQL for better database performance:

• Bind variables: If the query associated with the view object contains values that may change from execution to execution, use bind variables. Using bind variables in the query allows the query to reexecute without needing to reparse the query on the database. You can add bind variables to the view object in the Query page of the overview editor for the view object. For more information, see Section 5.10, "Working with Bind Variables."
• Query optimizer hints: The view object can pass hints to the database to influence which execution plan to use for the associated query. The optimizer hints can be specified in the Retrieve from the Database group box in the Tuning section of the overview editor for the view object. For information about optimizer hints, see Section 42.2.4.3, "Specify a Query Optimizer Hint if Necessary."

Reference : http://docs.oracle.com/cd/E16162_01/web.1112/e16182/bcqueryresults.htm#ADFFD19565