Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









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), 
      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:

FROM FormStats
  WHERE ((DBASE_TIME)/1000) > 4

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:

   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.

 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

 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.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational