|
Verifying Effectiveness of View Object Fetch Size Using SQL Trace
As I mention in my View Object Tuning Tips article, the "fetch size" parameter of a view object allows you to control how many rows are returned from the database in each round-trip. The default value of 1 is not a great value to use if your query returns more than one row. For example, it means that to fetch the 4 rows in the DEPT table, you'll need to perform 5 database round trips.
- Round-trip to fetch row 1
- Round-trip to fetch row 2
- Round-trip to fetch row 3
- Round-trip to fetch row 4
- Round-trip to attempt to fetch another row to see if there are any rows left.
To see the effect of this, you can enable the Oracle database's SQL Trace feature and then format the SQL trace output with the handy tkprof utility. I performed the following tests using the SQLTrace example project that I've added to my examples page here on this blog.
To enable SQL Trace for my application module, I added an overridden afterConnect() method to my application module impl class that looks like this:
/* Overridden framework method in TestModuleImpl.java file */
protected void afterConnect() {
super.afterConnect();
getDBTransaction().executeCommand("ALTER SESSION SET SQL_TRACE TRUE");
}
Then I started up the Business Components tester and executed a simple "DeptView1" view object instance, pressed the toolbar button to go to the last row (to cause all rows to be fetched), then exited the tester.
I went to the user dump directory under my Oracle database home and found the most recent *.trc file generated by this test.
I formatted the output using tkprof like this:
C:oracleproduct10.2.0adminorcludump> tkprof orcl_ora_2276.trc out.prf
Then I looked inside the *.prf formatted trace output file. Here's the relevant bit I saw...
********************************************************************************
SELECT Dept.DEPTNO, Dept.DNAME, Dept.LOC
FROM
DEPT Dept
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.01 0.02 5 11 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.02 0.06 5 11 0 4
********************************************************************************
When the view object's fetch size is set to 1, you can see in the TKPROF-formatted SQL Trace output above that in order to retrieve the 4 rows in the DEPT table, we performed 5 round-trips (Fetch) to the database. Four were for the 4 rows retrieved, and one additional one was to determine whether there were any more rows.
Next, I modified the fetch size of the view object to 10 by setting the "All rows in batches of:" property on the View Object tuning panel to 10. I repeated the steps above, and looked at the formatted trace output again.
********************************************************************************
SELECT Dept.DEPTNO, Dept.DNAME, Dept.LOC
FROM
DEPT Dept call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 4
********************************************************************************
This showed that by setting the view object's fetch size larger, I was able to get all four rows (as well as figure out that there were no more rows) in a single database round-trip (Fetch count = 1).
You can try out the simple example that I used to produce these results by downloading Example #42 on my Not Yet Documented Examples page.
You might also find Kimberly Floss' article on OTN about Tracing SQL in Oracle Database 10g to be inspiring. It highlights some new tracing features in the 10g database that make doing session tracing of SQL even easier.
|
