Summarizing Forms Server Log Information Oracle Application Server Tips by Burleson Consulting

The collected data is stored in an Oracle table with the following code (Listing 10.3).  This is the same approach that you will use to capture and analyze other Oracle9iAS logs, so the principles in this section will apply to all areas of Oracle9iAS performance collection.

create table FormStats ( 
      FORM_ID           VARCHAR2(120), 
      EVENT             VARCHAR2(120), 
      FSERVER_TIME      NUMBER, 
      DBASE_TIME        NUMBER, 
      NWORK_TIME        NUMBER, 
      CLIENT_TIME       NUMBER, 
      DATE              DATE) 
;

Listing 10.3: An iasdb table to hold Forms Server information

Here we see that the FormsStats table contains these columns, which can be easily populated by a procedural program:

• A unique Form ID ? This is unique to each transaction and corresponds to the Form name in the Form Server.  Unfortunately, you cannot capture the exact Form name and track response time by Form name.

• The event ? This is a response process on the Form.  These are named events (e.g. add New Item), and correspond to buttons on the Form.

• Forms server response time - You can monitor the response time within the Forms Server.

• Database response time - You can monitor the total time the transaction spent inside the Oracle database.

• Network response time ? You can monitor the time in the network and in the client processing information, but not interacting with the user.

• Client response time - You can monitor the response time within the client layer.

• Date ? This is the exact date that the transaction was invoked.

You can populate the file with a program snippet (usually written in Perl or Java) to process the Forms Server log, one line at a time, extract the event and Form name, and store the data into your table (Listing 10.4).  This pseudocode shows the basic follow of the code snippet:

while (((str = in.readLine()) != null)
  if (str.startsWith("TSE")) { Add time to appropriate tier}
 if (str.startsWith("# "))    
    { Extract Event and Form Name;
      Load record into database;
      Clear times;
    }
}

Listing 10.4: The pseudocode for an Oracle9iAS Forms Server log extract

This task is normally scheduled via dbms_job or cron to run daily and then re-initialize the Forms Server log file.  Once collected, you can use SQL to do easy reporting from your FormStats table.  For example, here we find the number of Events with database access time greater than 4 secs:

SELECT 
   COUNT(*)
FROM FormStats
  WHERE ((DBASE_TIME)/1000) > 4
  AND DATE >= SYSDATE-1
  AND DATE <= SYSDATE
;

You can also use the data in the FormsStats table to locate those Forms with the greatest total response time.  The example below finds the Form with the greatest time in the Forms Server:

 SELECT
   form_id,
   fserver_time
FROM
   FormStats
Where
   fserver = (SELECT MAX(fserver) FROM FormStat)
;

Once extracted and summarized, this response time data can be easily pasted into a spreadsheet and plotted to produce valuable trend reports (Figure 10.2).  This was done by using the MS-Excel chart Wizard.  For details on this techniques, see Oracle9i High-Performance Tuning with STATSPACK by Oracle Press, page 600.

Figure 10.2: Forms Server aggregation of total application response time 

We can also use this data to show average total response time and the components of total response time for the Oracle9iAS Forms application (Figure 10.3).

Figure 10.3: Breakdown of response time components

As we can see, the Forms Server data can be aggregated to provide very specific response time information.  This is very useful for Oracle9iAS shops that have Service Level Agreements (SLA), guaranteeing good response time for all Oracle9iAS transactions.

Transaction-level response time monitoring

The data in the Oracle9iAS Forms Server file can also be used to get the average response time for all Forms Server transactions (Figure 10.4).  Here we produce a top-ten report showing the slowest transactions in the system.

Figure 10.4: A top-ten list of slowest Oracle9iAS Forms Server Transactions

Oracle9iAS component response time breakdown

The Oracle9iAS Forms Server data also provides details about the amount of time spent in the Forms Server and the amount of time spent in the back-end Oracle database (Figure 10.5).  This information can provide critical clues about the best place to start tuning Oracle9iAS Forms Server transactions.

Total
 Response Time
 Response         # of
 Time       Trans
 =======    ======
 <01  secs 177,013
 1-2  secs  48,851
 2-3  secs  34,033
 3-4  secs  21,974
 4-5  secs  15,894
 5-6  secs  10,084
 6-7  secs   7,608
 7-8  secs   5,366
 8-9  secs   4,087
 9-10 secs   3,160

 Database
 Response Time
 Response        # of
 Time       Trans
 =======    ======
 <01  secs 346,528
 1-2  secs   2,037
 2-3  secs   3,853
 3-4  secs   2,982
 4-5  secs     486
 5-6  secs     366
 6-7  secs     321
 7-8  secs     234
 8-9  secs     160
 9-10 secs     163

 Forms Server
 Response Time
 Response       # of
 Time       Trans
 =======    ======
 <01  secs 345,934
 1-2  secs   8,116
 2-3  secs   2,857
 3-4  secs     648
 4-5  secs     240
 5-6  secs     133
 6-7  secs      50
 7-8  secs      39
 8-9  secs      30
 9-10 secs       2

Figure 10.5: A breakdown of Oracle9iAS Forms Server transactions with database time 

While the complete coding details are beyond the scope of this text, the concepts should be clear about how you can extract the raw from the Forms Server log file and provide accurate response time reports.  Next, let?s examine some of the Oracle9iAS log tools for providing performance statistics.

This is an excerpt from "Oracle 10g Application Server Administration Handbook" by Don Burleson and John Garmany.