Oracle Forms Server Monitoring Oracle Application Server Tips by Burleson Consulting

Oracle has two methods for retrieving Forms Server response time metrics:

• Forms Server 6i ? Extract data from OS flat file

• Forms Server 9i ? Extract data from Oracle performance tables

Oracle Forms Server 9i provides a method whereby a database is loaded from OS object files.  The OS object files are loaded into the ??? directory, and the xxx utility is executed to load the performance data into Oracle performance tables.  (send over script and schema create syntax).

Once loaded, SQL statements can be executed against the performance tables to extract response time details for all Forms Server 9i tasks.

Oracle Forms Server 6i provides a flat file log that contains all of the information required to measure end-to-end Oracle response time.  This response time monitoring mechanism can be used for end-user applications using SQL*Forms (Forms Server 6i) and Oracle 9iAS (Oracle9i Internet Application Server).  This approach works with any application that uses the Forms Server, including SQL*Forms or web apps that use the Oracle9iAS Forms Server. 

Using a scripting language (e.g. Javascript), you can extract details from the Forms Server logs, and get a complete breakdown of Oracle response time, including client response time, network response time, database response time and forms server response time (Figure 10.1).

Figure 10.1: Components of Oracle Forms Server response time

 The forms server log is a flat file that contains details for all transactions (Listing 10.2).  Note that the statistics are displayed on separate lines, requiring a program to read and summarize the performance data.

Forms Runtime Performance Collection Log

File Name: perf_392

Process ID: 392

Client IP: 172.16.1.76:1789

Forms 6.0 (Forms Runtime) Version 6.0.8.14.1 (Production)

PL/SQL Version 8.0.6.3.0 (Production)

Oracle Virtual Graphics System Version 6.0.5.37.0 (Production)

Oracle Multimedia Version 6.0.5.34.0 (Production)

Oracle Tools Integration Version 6.0.8.13.0 (Production)

Oracle Tools Common Area Version 6.0.5.32.1

Oracle CORE Version 4.0.6.0.0 - Production

TSE Startup Time : 43574343

TSE Handshake Duration  : 43574343

##### CTIME STARTS HERE

# C

WINDOW NONAME START START

Opened file: d:\hri1\forms\F_main.fmx

TSE FSERVER_START 0 0 43574828

TSE DBLOGON_START 0 0 43574828

TSE DBLOGON_END 0 0  43575093

Opened file: d:\hri1\forms\F_LOGIN.fmx

TSE FSERVER_END -1  0 43576343

IXPROP_VIEW_OUTERSIZE

IXPROP_VIEW_OUTERSIZE

# 1 - F_LOGIN:DISCLAIMER.DISAGREE_BTN.43577156

WINDOW F_LOGIN DISCLAIMER_WINDOW ACTIVATE 3

TSE FSERVER_START -1 1953 43577156

TSE FSERVER_END -1  0 43577156

##### CTIME STARTS HERE

TSE FSERVER_START -1 1182 43578359

TSE FSERVER_END -1  0 43578359

# 2 - F_LOGIN:DISCLAIMER.DISAGREE_BTN.43578500

CLICK F_LOGIN DISCLAIMER AGREE_BTN 1 MOUSE

IXPROP_VIEW_OUTERSIZE

# 3 - F_LOGIN:CNTL.USER_LOGIN.43578515

WINDOW F_LOGIN DISCLAIMER_WINDOW DEACTIVATE 3

# 4 - F_LOGIN:CNTL.USER_LOGIN.43578515

WINDOW F_LOGIN LOGWINDOW ACTIVATE 3

TSE FSERVER_START -1 140 43578500

TSE FSERVER_END -1  0 43578515

##### CTIME STARTS HERE

TSE FSERVER_START -1 390 43578921

TSE FSERVER_END -1  0 43578921

TSE FSERVER_START -1 591 43579531

TSE FSERVER_END -1  0 43579531

TSE FSERVER_START -1 1362 43580890

TSE FSERVER_END -1  0 43580890

# 5 - F_LOGIN:CNTL.USER_LOGIN.43582031

VALUE F_LOGIN CNTL USER_LOGIN 1 PLK

# 6 - F_LOGIN:CNTL.USER_LOGIN.43582031

KEY Next_item

##### CTIME STARTS HERE

TSE FSERVER_START -1 1112 43582031

TSE FSERVER_END -1  0 43582031

TSE FSERVER_START -1 981 43583031

TSE FSERVER_END -1  0 43583031

##### CTIME STARTS HERE

TSE FSERVER_START -1 6750 43589796

TSE FSERVER_END -1  0 43589796

# 7 - F_LOGIN:CNTL.USER_PASSWORD.43589890

VALUE F_LOGIN CNTL USER_PASSWORD 1 BLESSED

# 8 - F_LOGIN:CNTL.USER_PASSWORD.43589890

CLICK F_LOGIN CNTL PB_LOGIN 1 MOUSE

Opened file: d:\hri1\forms\F_DIARY.fmx

TSE FSERVER_START -1 80 43589890

TSE DB_START 0 0 43589968

TSE DB_END 0 0  43589968

TSE DB_START 0 0 43590046

TSE DB_END 0 0  43590250

TSE FSERVER_END -1  0 43591031

Listing 10.2: A sample from the Oracle9iAS Forms Server log

As we can see, the Oracle9iAS Forms Server log produces transaction-level response time details for all transactions.  Hence, this log file can get very large on highly active systems.  Once your program gathers the data and stores the summaries into an Oracle tables, many Oracle9iAS administrators will delete the Forms log to keep the disk from becoming full. While the programmatic details are beyond the scope of this text, you can write a summarization program using C, Perl or Java.  As we can see, the Oracle9iAS Forms Server performance log produces transaction-level response time details for all transactions.   The Forms Server creates a log for each connection and logs timing marks at the transaction enters and exits the Forms Server.   Hence, these log files can be numerous and quite large on highly active systems. 

As we noted, Oracle Forms Server 6i contains a Perl script to analyze a single performance log which is very useful during development; however it does not scale to provide performance statistics for a production system.  By automating the analysis for the entire performance log over an extended period of time you can produce extensive statistics of actual system performance. 

Form Server tier metric measures the time spend inside the Forms Server itself and the Database tier is the time spent in the database.  All times are recorded as the program transits to and from the Form Server so network latency is included in the Database tier time for the connection to the database, and is included in the Network/Client Tier's time for the connection between the client and the Forms Server.  The program should store the sums as each event occurs and with the transition between different Forms.   Other statistics can also be collected such as number of database calls per form or event.  This will provide that ability to determine which forms and which events within each form are causing performance problems and can be used to focus performance tuning efforts.

Note: The writing of custom log analysis programs is a critical part of Oracle9iAS tuning because the format of the Oracle9iAS logs do not lend themselves to easy time-based analysis.

Once you write a simple program to read this information you can collect the response time in the database, Forms Server client and network.

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