Introduction

Parts 1 and 2 of this series introduced the IDS OpenAdmin Tool (OAT), a Web-based interface for administering Informix Dynamic Server, and showed how to migrate from the Informix Server Administrator. OAT offers many advantages:

• A single installation of OAT can administer multiple data servers
• No additional software is required on the data server, and OAT can be run on any Web browser
• Since OAT is open source, it is easy to edit and modify the tool to meet your needs

Before getting into the details of what the OAT can do with respect to performance analysis, let's look into the parameters that determine the performance of a database server. The following measures describe the performance of an Online Transaction Processing (OLTP) System:

• Throughput
• Response time
• Cost per transaction
• Resource utilization

OAT helps to measure each of the above parameters with its "Performance Analysis" toolkit, composed of the following functions:

• SQL Explorer
• Performance History
• System Reports
• Session Explorer

This article examines each of these functions.

Back to top

SQL Explorer

The SQL Explorer page displays a summary of all SQL statement types that have been executed on the current database server. Using the information on this page, you can determine how many times a type of statement has been run, along with the average response time and memory usage, maximum response time, and number of rows processed.

Click on the statement type to see further detail about that statement type, such as the number of times a particular statement has been run at the server. For example, click on SELECT to view statistics about SELECT statements that have been captured with SQL trace. You can then drill down on the saved SELECT queries. You can also see which statements have been run the most with a pie chart (see Figure 1) that breaks out each statement type by percentage of statements that have been run. Move the pointer over each section of the pie chart to view the percentage for the statement type.

Select Data to view this information in table format:

Before you start using the various features offered by this component, you need to enable the SQLTRACING option. Trying to activate the SQL Explorer without enabling it will display the following message:

An option to activate SQLTRACING is provided as follows:

Select the Mode to On. You can also set the "Number of traces", which is the number of SQL statements to trace before reusing the resources. The range is 500 to 2147483647. The "Trace Size" refers to the maximum size of variable length data to be stored. The range is 1KB to 100KB. If this buffer size is exceeded, the database server discards saved data.

The trace level can be one of the following:

• Low: This tracing level, which is enabled by default, captures statement statistics, statement text, and statement iterators.
• Medium: This tracing level captures all of the information included in low-level tracing, plus table names, the database name, and stored procedure stacks.
• High: This tracing level captures all of the information included in medium - level tracing, plus host variables.

Alternately, disable the SQL Tracing option by setting the Mode to Off.

Note: By default, the SQL Tracing option is disabled.

The SQL Explorer page is similar to the server side onstat -g his output. By default, only the DBSA can view onstat -g his syssqltrace information. However, when UNSECURE_ONSTAT = 1, all users can view this information. The steps below illustrate how to work with the Query Drill-Down feature. The SQL statement being drilled-down is the CREATE TABLE statement.

1. Enable the SQLTRACE feature.
2. Execute the CREATE TABLE command either using dbaccess or through the OAT SQL Editor component.
3. Select the SQL Explorer component under the Performance Analysis tab.
4. Click on the CREATE TABLE SQL statement, listed in the SQL Statement Summary:
   
   Figure 5. SQL Statement Summary
   
   
   
5. The resulting page displays performance information about an SQL statement, such as how many times the statement has been executed at the server (Count) and average run time.
   
   To view the details of each execution of the SQL statement, click on Drill Down:
   
   Figure 6. SQL Frequency Summary
   
   
   
6. The resulting page provides details of an SQL statement that has been selected for drill down. The SQL statement is displayed, as well as the following statistics:
   • Session ID: Database session ID of the user running the SQL statement
   • User ID: User ID of the statement running the SQL statement
   • Response Time: The length of time required to process the SQL statement
   • Rows/Second: The average number of rows produced per second
   • Rows Processed: The number of rows processed for the SQL statement
   • Lock Wait Time: Time the system waited for locks during SQL statement
   • Wait IO Time: The number of times an IO operation had to wait
   
   
   Click on Drill Down to view detailed statistics for the SQL statement:
   
   Figure 7. Drill Down screen
   
   
   
7. The resulting page displays a detailed profile of an SQL statement execution, selected through drill down. Use the information on this page to analyze the performance of each SQL statement:
   
   Figure 8. Statement Statistics
   
   
   

   Statement Statistics output description:

   • Page Reads: Number of pages that have been read from disk
   • Buffer Reads: Number of times a page has been read from the buffer pool and not read from disk
   • Read Cache: Percentage of times the page should be read from the buffer pool
   • Index Buffer Read: Number of buffer reads for index pages
   • Page Write: Number of pages written to disk
   • Buffer Write: Number of pages modified and sent back to the buffer pool
   • Writes Cache: Parentage of time that a page was written to the buffer pool but not to disk
   • Lock Requests: Total number of locks required by this statement
   • Lock Waits: Number of times this SQL statement waited on locks
   • Lock Wait Time: Time spent waiting for locks during this SQL statement in seconds
   • Disk Sorts: Number of sorts for this SQL statement that were executed on disk
   • Memory Sorts: Total Executions - Total number of times this statement has been executed or the number of times this cursor has been re-used
   • Total Time: Total time executing this statement in seconds
   • Average Time: Average time this state takes to execute in seconds
   • Max Time: Total time to run the SQL statement in seconds, excluding any time taken by the application
   • Lock Wait Time: Amount of time the statement waited for application locks
   • Average IO Wait: Amount of time the statement waited for I/O, excluding any asynchronous I/O
   • Average Rows/Second: Average number of rows a second produced by this statement
   • Estimated Cost: Cost associated with the SQL statement
   • Estimated Rows: Estimated number of rows returned, as estimated by the optimizer for the statement
   • Actual Rows: Number of rows returned for this statement
   • SQL Error: The SQL error number
   • ISAM Error: The RSAM/ISAM error number
   • Isolation Level: Isolation level this statement was run with
   • SQL Memory: Number of bytes this SQL statement requires

Performance History

Performance history through OAT allows you to monitor critical aspects of performance. Through this tool, you can monitor the historical data captured by the IDS Scheduler DBCron. OAT provides five profiled fields, as shown in Figure 9 below, to view the performance:

You can click on the relevant profiled field to see the performance of IDS in that field. To view performance data for the current database server, expand the list and select a type of performance data. A graph is generated, displaying the requested information. You can zoom in and out of a data range, and the graph will adjust accordingly.

Now, let's look into each of the Profiled Fields in detail.

Auto Checkpoints

Auto Checkpoints allows the server to trigger checkpoints more frequently to avoid transaction blocking. This menu provides many checkpoint parameters, shown in the drop down box in Figure 10 below:

To view the history of the maximum disk flush time, click on acp_longest_dskF. Figure 11 shows a snapshot of the output. The graph displays the value with respect to an instant of time. Also available with the Profiled Fields menu is a Legend option to help you easily interpret the graph. To make the graph more readable, a zoom-in and a zoom-out option have been provided, which looks similar to the following:

For example, in the graph shown in Figure 11 below, the Maximum Disk Flush time is "5" on 03-June-2008, 2:01 AM. Zooming-in further also specifies the value at that particular instant of time.

To view this in text format, click on the Data button at the bottom left on the frame:

Disk

This component provides performance history details of the database server in terms of the various disk operations. When you click on the "Disk" drop-down menu, it displays the following submenus, as shown in Figure 13:

• Disk Reads: Total number of read operations from disk
• Disk Writes: Total number of write operations to disk
• Disk Flushes: Total number of writes from a buffer to disk
• Chunk Writes: Total number of chunk writes (chunk writes, performed as sorted writes, are the most efficient writes available to the database server)
• Foreground Writes: Number of foreground writes at a given instant of time.

Selecting each of the above submenus expands the list, providing the option for you to choose disk operations for different page sizes, as shown in Figure 14 below:

Buffer

This component provides the shared memory buffer-related information required to analyze the database server performance. The submenus are similar to that of the "Disk" component.

General Profile

This component provides performance history data like checkpoint information, deadlocks, rollbacks, commits, updates, and deletes, as shown in Figure 15 below:

B-Tree Scanner

The B-Tree Scanner improves transaction processing for logged databases when rows are deleted from a table with indexes. The B-Tree Scanner threads remove deleted index entries and rebalance the index nodes. The B-Tree Scanner automatically determines which index items are to be deleted. After all indexes above the threshold are cleaned, indexes below the threshold are added to the hot list. The default threshold is 500. The B-Tree Scanner component expands to display list items like bts_rangesize, bts_threads, bts_flags, and so on.

Back to top

System Reports

This component of the OAT is extremely attractive from the database administrator's point of view. The OAT provides the much needed information required by the DBA like the disk space usage, log files, SQL statements occupying the most IO time, the slowest SQL statements, and so on. A DBA can use this report and tune the performance of the IDS if required. Therefore, the "System Reports" component acts like a yard stick to measure the current server performance through various parameters. The "System Reports" page displays a variety of reports that you can view individually, or you can also build a report from many smaller reports:

To display a report, click on the link with the name of the report. The report appears with current information. For example, clicking on the Server Configuration, as shown in the red circle in Figure 16 above, takes you to the current configuration of the server. Here, you can cater to the "Recommendations" provided by the OAT for certain configuration parameters or set the configuration parameter to a value of your choice. However, only some configuration parameters are dynamic and can be set from the OAT.

An alternate way to build a report is through the check-box option, also shown in Figure 16 above. To build a report from multiple smaller reports, select the check boxes for the chosen reports, and click on Create Report. The reports are concatenated for displaying current information. In this method of viewing the reports, a separate browser window is opened, and the report is displayed in this new window. The report specifies the time at which the report was created, the "Server uptime", and the actual data. The report begins with the list of all the small reports it is made up of. You can select each report and directly view the smaller report, then navigate back to the top by clicking the Top link provided, as shown in Figure 17 below:

To filter the list of reports, click on the Report Type selection list. Select the type of report you want. The check boxes for each of these reports are selected. Click Create Report to view the report.

Back to top

Session Explorer

This page displays a list of the user sessions currently running at the server. Click on a value in the SID (Session ID) column to open detailed information about the session. The session details can be obtained on the server side using the onstat -g ses utility. It is also possible for you to end a session by clicking on the thunder button:

Note: You cannot kill system-initiated sessions.

Output description:

• SID: Session ID
• Username: Name of the user connect in that session
• PID: Process ID
• Hostname: Displays the machine name in which the server instance is running
• Memtotal: Total Memory used in that session
• IOWaittime: The time elapsed in waiting for the IO
• CPUTime: The amount of CPU time utilized by the session

Clicking on the SID number displays information about the selected user session. The Session Info section displays a report for the session, as shown in Figure 20 below:

Output description:

• Connected: Displays the time when the session is started
• Open Tables: Displays the number of the tables opened in that session
• Current Statement: Displays the Current Executed SQL query in that session

The SQL tab

The SQL tab displays a list of statements that have been run by the selected session.

Output description:

• ID: The ID of the SQL statement executed
• Type: The type of the SQL operation performed
• Statement: Displays the actual query executed

The Locks tab

The Locks tab displays a list of the table and page locks for this user session.

Output description:

• Table Name: Name of the table on which the locke existed
• Lock Type: Type of the lock
• Lock Duration: The duration which the lock existed
• Row ID: The ID of the row which is locked
• Index #: The Index number of the locked row
• Waiters: Others waiting for the lock
• Key Item Locked: The key pointing to the locked row

The Threads tab

The Threads tab displays details about the threads used in this session.

Output description:

• Name: Name of the thread
• Num_Scheduled: The total number of times the thread has been scheduled
• Thread_ID: ID of the thread
• Thread_Priority: Priority of the thread
• Time_Slice: Fixed amount of execution time given to thread
• Total_Time: Total time for thread execution
• VPID: Virtual process ID
• Wait_Reason: Reason the thread is waiting

The Memory tab

The Memory tab displays the used and unused memory for the selected session.

Output description:

• Name: Session ID
• Used: Amount of memory used
• Free: Amount of memory free

The Network tab

The Network tab displays performance details about the network connectivity, IO, and so on for the selected session.

Output description:

• Average_Recv: Amount of data received in that session per network read
• Average_Send: Amount of data sent in that session per network write
• Connect_Duration: Time since the connect is established for that session
• Last_Read: Time of the last read from the network
• Last_Write: Time of the last write from the network
• Received_Data: Amount of data received in that session
• Send_Data: Amount of data sent in that session
• Session_Start_Time: Time this session is connected
• Thread_Name: Name of the thread

The Environment tab

The Environment tab displays the settings of the IDS environment variables for the selected session.

The Profile tab

The Profile tab displays a report of all of the combined statistics for the user session.

Back to top

Conclusion

This article has explained how OAT can be used to analyze the performance of IDS. The OAT provides a simple, easy-to-use GUI for the DBA to obtain the current performance details of IDS. These details are provided through raw data and pie-charts to comprehend the information more quickly. The Informix Dynamic Server is a powerful database engine. OAT provides an easy-to-use GUI to monitor IDS and makes the job of a DBA very easy.

Resources

Learn

• Manage your Informix database with the IDS OpenAdmin Tool series:
  • Manage your Informix database with the IDS OpenAdmin Tool, Part 1: Configuring and using the OpenAdmin Tool with IDS: See how the OpenAdmin Tool in Informix Dynamic Server makes server administration more user friendly. This article highlights some of the most useful features of OAT and helps you get started.
  • Manage your Informix database with the IDS OpenAdmin Tool, Part 2: Migrate from Informix Server Administrator to the IDS OpenAdmin Tool: Not only migrate from the ISA to the OAT, but also learn about the basic capabilities of ISA that are made available on OAT, along with enhanced functionalities and greater ease-of-use.
  
  
• developerWorks Informix page: Find articles and tutorials, and connect to other resources to expand your Informix skills.
  
  
• "Using IBM Informix Dynamic Server on Microsoft Windows, Part 2" (developerWorks, December 2007): See how to install an Apache HTTP Server, install and configure PHP, and install the OpenAdmin Tool (demo).
  
  
• "Use the Informix Dynamic Server scheduler and SQL API" (developerWorks, September 2007): Learn how the IDS 11 scheduler and SQL API make a DBA's daily tasks easier.
  
  
• IBM Informix Dynamic Server Performance Guide, V11.1 (G229-6385-01): Search the IBM Publications Center for the IBM Informix Dynamic Server Performance Guide, V11.1, and get more information on how to configure and operate IDS to achieve optimum performance.
  
  
• "Informix Unleashed" (Macmillan Computer Publishing): Get up to speed with the knowledge and information you need to use Informix products successfully.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Technology bookstore: Browse for books on these and other technical topics.
  
  

Get products and technologies

• IDS OpenAdmin Tool: Download the IDS OpenAdmin Tool.
  
  
• Informix Dynamic Server: Download a free trial version of IDS..
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Bharath Sriram is an Associate Software Engineer at India Software Labs, IBM. He works for the IDS Integration Team across various IDS Products and also works on the OpenAdmin Tool for IDS 11.

Vishnu Saran is an Associate Software Engineer at India Software Labs, IBM. He works for the IDS Integration Team across various IDS Products and also works on the OpenAdmin Tool for IDS 11. He is certified in System Administration for IBM Informix Dynamic Server V11.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• SQL Explorer
• Performance History
• System Reports
• Session Explorer
• Conclusion
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Informix Ultimate Edition
• Buy: Informix Ultimate Edition

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers