Monitoring SQL Statements

Onstat -g sql Print SQL Information

This command is one of the most useful features found in onstat in IDS engines. OnLine systems provided no way for you to see exactly which SQL statements were being executed at any particular time. This left the DBA in the dark when trying to see exactly which SQL statements may be hogging system resources and bringing the system to its knees.

Users who are running ad hoc queries in OLTP systems are going to do bad SELECTS no matter how well you train them. A bad SELECT is defined as any SELECT statement that hogs a lot of the system resources. These can be such things as non-indexed reads against large tables resulting in sequential scans, disjoint queries that return a Cartesian product of the tuples, generating a million lines of output, or queries so constructed that they use bad optimizer paths. You'll even generate bad queries occasionally yourself. This is forgivable.

What is totally unforgivable in a system is to imbed system-hog queries into the code of a production system. During the development process, every SQL statement and every possible combination of dynamic SQL statements that may be created on the fly in response to user inputs should be tested and should have a SET EXPLAIN run against them. Dynamic SQL statements in particular are problematic, since by nature, the statements change in response to user inputs. Particular care should be given to front-end applications that generate dynamic SQL statements to make sure that they will not allow, or at least warn the user about, SQL statements that will hog up system resources.

Onstat -g sql gives you a tool to trace down these pathological queries and identify where the queries are coming from. I'd estimate that half of your production problems can be solved by eliminating these system hog queries.

What usually happens is that you'll receive reports or notice yourself that system performance suddenly goes into the toilet. This is the time to run an onstat -g sql.

Onstat -g sql can be run either globally or against a particular session. Here is a global run:

$ onstat -g sql
Informix Dynamic Server Version 7.30.TC3   -- On-Line -- Up 00:29:36 -- 9536 Kbytes

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
583   SELECT         sysmaster          CR  Not Wait   0    0    7.30

This invocation gives you a simple list of the session ID, type of SQL statement (SELECT, UPDATE, INSERT, DELETE), the database used, isolation level, lock mode, SQL error code and/or ISAM error code, and front-end program version of all the sessions currently running. You can get some useful data from this. If you have a system that runs multiple types of clients, such as ESQL/C programs, dbaccess, Powerbuilder, and the like, the final field, F.E. Version, can often give you a hint as to what kind of job is running. You might have started your witch hunt by running onstat -u, looking for sessions that were doing a lot of reads, which often indicates a sequential scan. You could have used the SMI tables to discover sequential scans. Anyway, somehow you have gotten interested in finding out what a particular user thread is doing. In this case, we're interested in session ID #583:

$ onstat -g sql 583
Informix Dynamic Server Version 7.30.TC3   -- On-Line -- Up 00:30:56 -- 9536 Kbytes

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers
583   SELECT         sysmaster          CR Not Wait    0    0    7.30

Current statement name : slctcur
Current SQL statement :
select * from systables
Last parsed SQL statement :
select * from systables

Running onstat -g sql with a session id after it gives you the same information about the thread that running without the session id did, plus it gives you the paydirt at the end of the report. This paydirt is the exact SQL code that is being run by the session, as well as the last SQL code that was parsed by the statement. Just seeing the SQL may tip you off that this is you resource hog. If you see someone running a query against a 100-million-row table with a where clause referencing an unindexed column, you know you've found the culprit.

Often though you won't be sure that this is the job that's causing the problem. The thing to do now is cut and paste the query text into your own query tool, slap a SET EXPLAIN ON at the beginning of the statement, run it long enough to generate a query plan, and then interrupt the query. Of course, before running it, be sure that running the query won't lock a system table or something similar or otherwise screw up what's running in production. Then take a look at the query plan and see exactly what the query is doing. If this provides the smoking gun, it's time to break out the baseball bat and go visit either the user who's running the ad hoc query or the developer who let such a stupid piece of code get into a production system. This is much more fun than doing what you really should have done, which is use the baseball bat on yourself. After all, you do have a policy that all production SQL code has to be approved by the DBA, don't you?

A final point for the onstat -g sql <sessionid> command is the current statement name. This is the cursor or prepared statement name for the currently running SQL statement. If you are writing code that prepares and executes SQL statements, be sure that the names chosen for the cursors are meaningful. If they are, you can identify the running SQL more easily. For example, the cursor name should be coded to identify both the purpose of the cursor and the name of the program running it.

Onstat -g pos Print DBSERVERNAME File

The DBSERVERNAME file is actually the $INFORMIXDIR/etc/.infos.servername file. This file is a temporary binary file that is created whenever you initialize shared memory for an instance and is deleted when you take the server off-line. The "servername" part of the filename is the DBSERVERNAME parameter from the ONCONFIG file. The IDS engine and its utilities refer to this file in order to address the server. The only way to get a human-readable view of this file is to use onstat -g pos. Do not delete this . infos file from the $INFORMIX/etc directory, as it is needed by the utilities. If you delete the file by accident (it's hard because the filename begins with a period), you can re-create it by restarting the engine.

$ onstat -g pos
Informix Dynamic Server Version 7.30.UC3   -- On-Line -- Up 2 days 22:16:22 -- 18464 Kbytes
  1   7   0 infos ver/size 2 136
  2   1   0 snum 1 52574801 00000000 inf_rockl
  3   4   0 onconfig path /informix/etc/onconfig.rockl
  4   5   0 host rock
  5   6   0 oninit ver Informix Dynamic Server Version 7.30.UC3
  6   8   0 del

Onstat -g lap Print Light Append Information

Light appends are append operations to a page which bypasses the normal buffer pool. They are used in the High Performance Loader (HPL) in certain instances in the express mode of operation. The onstat -g lap command is the primary means of looking at these light appends.

$ onstat -g lap
Informix Dynamic Server Version 7.30.UC3   -- On-Line -- Up 2 days 22:16:37 -- 18464 Kbytes
Light Append Info
session id  address   cur_ppage   la_npused   la_ndata   la_nrows     bufcnt