As we know, in database application system, the performance of SQL statement is critical. Poor SQL statement performance can result in poor performance of the entire database application system. So, how to monitor the performance of SQL statements in the database system, what is the cause of the poor performance of SQL statements? How are system resources used during SQL statement execution? What are the system resource bottlenecks? In Informix 11.5, two main tools are provided to solve these problems. One is the set Explain command, which allows us to analyze the causes of poor SQL statement performance by viewing the query plan of the database and adjust accordingly, and the other is the SQL drill-down query feature, through which, We can analyze the slow execution of SQL statements in the system, the time of SQL statement execution, the occupation of resources during SQL statement execution and what the bottleneck of the system is and make corresponding adjustments in time. Below, we look at the specific use of these two monitoring tools, I hope to help you.
The SET EXPLAIN command
When we find that one or a group of SQL statements in the system are performing poorly, we often use the set Explain command to look at the query plan of the SQL statement, see what is causing the poor performance of the SQL statement and make adjustments accordingly. In Informix, query plans mainly include access plans and join plans. The access plan refers to the method by which the Informix database reads data from disk. Generally speaking, Informix provides the following access plans:
- Sequential scan: The database server reads all the records in a table in physical order.
- Index Scan: The database server reads the Index page and reads the associated records through the corresponding ROWID.
- Key-only index scan: If the read data is contained in the index node, the database server only needs to read the index and does not need to read the corresponding data page.
- Key-first Index scan is an index scan that first uses an index Key filter to reduce the amount of data read by a query.
- Auto-index scan: The auto-index scan feature allows the database server to automatically create a temporary index on one or more fields. The database server reads the corresponding data through this temporary index. This temporary index only takes effect during the query. This feature is particularly useful in some OLTP batch operations where indexes are utilized without the overhead of index maintenance.
Informix provides two main types of table join plans:
- Nested-loop join: In a nested loop join, the first (or external) table is scanned for rows that meet the query rules. For each row found in the external table, the database server searches for its corresponding row in the second (or internal) table. Whether an external table is accessed through an index scan or a table scan depends on the table. If there are filters, the database server applies them first. If the internal table does not have an index, the database server will compare the cost of building an index on the table to the cost of a continuous scan and choose the least expensive method. The total cost depends on whether there is an index on the join column. If there is an index on the join column, the cost is quite low; Otherwise, the database server must perform a table scan on all tables (external and internal).
- Hash Join: This method is used when there are no indexes on one or more join tables, or when the database server must read a large number of rows from all join tables. In this method, you scan one of the tables, usually the smaller one, and use it to create a hash table in memory. With a hash function, rows with the same hash value are placed in a bucket. After scanning the first table and placing it in the hash table, the second table is scanned and each row in the table is looked up in the hash table to see if it can be joined. If there are more tables in the join, the database server performs the same operation for each join table.
Hash joins consist of two actions: build the hash (or what we call the build phase) and probe the hash table (or probe phase). During the build phase, the database server reads a table and creates a hash table in memory after applying all existing filters. A hash table can be conceptually thought of as a series of buckets, each of which has an address that is derived from a key value by applying a hash function. During the probe phase, the database server reads the other tables in the join and applies filters if they exist. On each row that satisfies the filter criteria, the database server applies a hash function to the key and probes the hash table for matching key values. If the read related data is contained in the index node, the database server only needs to read the index and does not need to read the corresponding data page.
How do I get a query plan
In Informix, we can use a SET EXPLAIN statement or EXPLAIN pseudodirective to display the query plan selected by the optimizer.
Use the SET EXPLAIN
The output of the SET EXPLAIN statement shows the decision made by the query optimizer. It shows whether parallel scanning has been used, the maximum number of threads required to respond to the query, and the type of connection used for the query. You can use SET EXPLAIN to view the query plan for your application.
Basic syntax for SET EXPLAIN statements:
>>-SET EXPLAIN-------------------------------------------------->
>--+-+-OFF-------------------+-------------------------+-------><
| '-ON--+---------------+-' |
| '-AVOID_EXECUTE-' |
'-FILE TO--+-'filename '--+--+--------------------+-'Copy the codeAmong them:
- ON: Generates an estimate for each subsequent query and writes the results to an output file in the current directory. If the file already exists, the new output is appended to the existing file.
- AVOID_EXECUTE: Prevents SELECT, INSERT, UPDATE, or DELETE statements from being executed when the database server prints the query plan into the output file.
- OFF: Terminates the activity of the SET EXPLAIN statement so that estimates are no longer generated for subsequent queries or written to output files.
- FILE TO: Generates estimates for each subsequent query and enables you TO specify the location of the description output FILE.
Output from the SET EXPLAIN ON statement is directed to the appropriate file before the SET EXPLAIN OFF statement is issued or the program ends. If no SET EXPLAIN statement is entered, the default behavior is OFF and the database server does not generate estimates for the query.
The SET EXPLAIN statement is executed during the database server tuning phase, which begins when you start the query. For cursor related queries, if the query is ready and there is no host variable, optimization occurs while you are preparing. Otherwise, optimization occurs when you open the cursor.
If the user wants to know the query plan for the following SQL statement and execute the following SQL statement, we can execute it:
SET EXPLAIN ON;
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.lname = "Higgins";Copy the codeIf the user wants to know the query plan for the following SQL statement but does not want to execute the following SQL statement, we can execute it:
SET EXPLAIN ON AVOID_EXECUTE;
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.lname = "Higgins";Copy the codeIf the user wants to know the query plan of the following SQL statement and output the result to the specified location, we can execute:
SET EXPLAIN ON AVOID_EXECUTE;
SET EXPLAIN FILE TO '/tmp/explain.out'
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.lname = "Higgins";Copy the codeIf the user no longer wants to know the query plan for the following SQL statement, we can execute it:
SET EXPLAIN OFF;Copy the codeWhen the SPL routine is executed, it is optimized. To display the query plan for each SQL statement contained in the SPL routine, execute the SET EXPLAIN ON statement before executing any of the following SQL statements, which always try to optimize the SPL routine:
CREATE PROCEDURE
UPDATE STATISTICS FOR PROCEDURECopy the codeFor example, use the following statement to display the query plan for the SPL routine:
SET EXPLAIN ON;
UPDATE STATISTICS FOR PROCEDURE procname;Copy the codeUse the AVOID_EXECUTE option
The SET EXPLAIN ON AVOID_EXECUTE statement activates the Avoid Execute option for the session, or until the next SET EXPLAIN OFF (or ON) without AVOID_EXECUTE is executed. The AVOID_EXECUTE keyword will prevent THE DML statement from executing; The database server prints the query plan to the output file. If AVOID_EXECUTE is enabled for queries that contain remote tables, the query will not be executed at the local or remote site.
If AVOID_EXECUTE is set, the database server sends a warning message. If you are using DB-Access, it displays text messages for all select, delete, update, or insert query operations:
Warning! avoid_execute has been setCopy the codeSqlwarn. Sqlwarn7 character is set to “W” according to ESQL.
Use the SET EXPLAIN ON or SET EXPLAIN OFF statement to turn OFF the AVOID_EXECUTE option. The SET EXPLAIN ON statement turns off the AVOID_EXECUTE option, but continues to generate the query plan and write the results to the output file.
If SET EXPLAIN ON AVOID_EXECUTE statement is issued within the SPL routine, then the SPL routine and all DDL statements will still execute, but the DML statements within that SPL routine will not. The database server prints the query plan for the SPL routine to the output file. To turn this option OFF, a SET EXPLAIN ON or SET EXPLAIN OFF statement must be executed outside the SPL routine. If the SET EXPLAIN ON AVOID_EXECUTE statement is executed before the SPL routine is executed, the DML statements within the SPL routine will not be executed, and the database server will not print the query plan for the SPL routine into the output file.
When AVOID_EXECUTE takes effect, the constant functions in the query are still evaluated because the database server evaluates them before tuning.
For example, the func() function is evaluated even though the following SELECT statement is not executed:
SELECT * FROM orders WHERE func(10) > 5;Copy the codeIf the SET EXPLAIN ON AVOID_EXECUTE statement is executed before opening the cursor in the ESQL/C program, each FETCH operation will return a message that the row could not be found. However, if SET EXPLAIN ON AVOID_EXECUTE is executed after the ESQL/C program opens the cursor, this statement does not affect the cursor and will continue to return rows.
Sqexplain.out file
ON UNIX systems, the database server writes the output of the SET EXPLAIN ON statement or EXPLAIN pseudoinstructions to the sqExplain. Out file.
If the client application and the database server are on the same computer, the SQexplain.out file is stored in the current directory. If you are using a client application with version 5.x or earlier and the sqexplain.out file does not appear in the current directory, check your home directory for it.
When the current database is on another computer, the SQexplain.out file is stored in the home directory on the remote host.
In Windows, the database server writes the output of the SET EXPLAIN ON statement or EXPLAIN pseudodirective to the file %InformixDIR%\ SQEXPLn \username.
Sqexplain. out File contents.
The sqexplain.out file is divided into three parts:
Part 1:
Part 1 contains the following, in the same order as listed below:
- SQL statements used for queries.
- An estimate of query cost expressed in units that the optimizer uses to compare plans. These units represent the relative time of query execution, and each unit is approximately equivalent to a typical disk access time. The optimizer chose a particular query plan because the estimated cost of executing it was the lowest of all the evaluated plans.
- Estimate of the number of rows expected to be generated by the query.
- The maximum number of threads derived from executing the query, if PDQPRIORITY is set.
- Temporary files to execute ORDER BY and/or GROUP BY (if required) are optional.
Part 2:
The tables are listed here in the order in which they are accessed. For each table, the filters applied are listed.
- The name of the accessed table
- The access plan taken by the database server to read the tables – sequential scan, index path, and automatic indexing. Also, if the table is segmented, the active segments to be accessed for this particular query are listed here.
- Lists the join plan for each pair of tables: nested circular join or dynamic hash join join. For dynamic hash join connections it also lists the filters used to perform hash join connections.
Part 3:
The content of this section will vary depending on the access plan selected. For each table in Part 2, this section occurs once.
For sequential scans: This section contains the filters to apply, if any. If the subquery is part of the filter, it will be extended here, and it will include all parts just like the main query.
For index and automatic index scans: For index and automatic index scans, this section contains the following information:
- Some index keys, to which filters will be applied, followed by one or all of the following:
- Key item only, if it is only used for key index scan.
- Aggregate items if the query aggregates index keys.
- Key priority if a key priority filter is applied to the index key.
- Lower bound index filters, if any. If the subquery is part of the filter, it will be extended here, and it will include all parts just like the main query.
- Upper index filter (if any). If the subquery is part of the filter, it will be extended here, and it will include all parts just like the main query.
- The key-first filter to apply, if any. If the subquery is part of the filter, it will be extended here, and it will include all parts just like the main query.
The EXPLAIN directive
If you want to display query plans for only one SQL statement, you can use EXPLAIN pseudo-directives instead of SET EXPLAIN ON or SET EXPLAIN ON AVOID_Execute statements.
Use EXPLAIN pseudo-directives to display query plans as follows:
- EXPLAIN: Displays the query plan selected by the optimizer.
- EXPLAIN AVOID_EXECUTE: Shows the query plan selected by the optimizer, but does not execute the query.
In Informix, pseudo-directives can be represented as follows:
--+directive text
{+directive text}
/*+directive text */Copy the codeThe following example shows a query statement using EXPLAIN directives:
select --+ explain
l.customer_num, l.lname, l.company,l.phone, r.call_dtime, r.call_descr
from customer l, cust_calls r where l.customer_num = r.customer_numCopy the codeThe following example shows a query statement using the EXPLAIN AVOID_Execute directive:
select --+ explain,avoid_execute
l.customer_num, l.lname, l.company,l.phone, r.call_dtime, r.call_descr
from customer l, cust_calls r where l.customer_num = r.customer_numCopy the codeHere is a sample output of the SQexplain.out file for a query using EXPLAIN AVOID_EXECUTE pseudo-directive:
QUERY:
------
select --+ explain ，avoid_execute
l.customer_num, l.lname, l.company,l.phone, r.call_dtime, r.call_descr
from customer l, cust_calls r where l.customer_num = r.customer_num
DIRECTIVES FOLLOWED:
EXPLAIN
AVOID_EXECUTE
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 7
Estimated # of Rows Returned: 7
1) Informix.r: SEQUENTIAL SCAN
2) Informix.l: INDEX PATH
(1) Index Keys: customer_num (Serial, fragments: ALL)
Lower Index Filter: Informix.l.customer_num = Informix.r.customer_num
NESTED LOOP JOINCopy the codeThe following table describes the relevant output rows in the above sqexplain.out file that describe the selected query plan.
| Output line | Description of the selected query plan |
|---|---|
| DIRECTIVES FOLLOWED: EXPLAIN AVOID_EXECUTE | Use the EXPLAIN and AVOID_EXECUTE directives to show the query plan instead of executing the query. |
| Estimated # of Rows Returned: 7 | It is estimated that the query returns 7 rows. |
| Estimated Cost: 7 | This estimated cost value is 7, which the optimizer uses to compare different query plans and select the least costly one. |
| 1) Informix.r: SEQUENTIAL SCAN | The CUST_Calls R table is used as the facade and scanned for each row. |
| 2) Informix.l: INDEX PATH | For each row in the appearance, use the index to get the matching row in the inner table Customer L. |
| (1) Index Keys: customer_num (Serial, fragments: ALL) | Using the index of the Customer_num column, scan it consecutively and scan all segments (the Customer L table consists of only one segment). |
| Lower Index Filter: Informix.l.customer_num = Informix.r.customer_num | The index scan starts with the customer_num value on the outside. |
| NESTED LOOP JOIN | The nested loop connection is adopted |
Onmode -y: Dynamically change SET EXPLAIN
If the user does not have access to the SQL source code, the database administrator can dynamically SET EXPLAIN by using the onmode -y command that runs the SQL code. For individual sessions, we can also dynamically change the value of the SET EXPLAIN statement using the onmode -y command.
Syntax of the onmode -y command:
| call | explain |
|---|---|
| onmode -Y sessionid 2 | Open SET EXPLAIN for sessionIDS and show only query plans |
| onmode -Y sessionid 1 | Open SET EXPLAIN for sessionids |
| onmode -Y sessionid 0 | Close SET EXPLAIN for sessionIDS |
When SET EXPLAIN is opened using the onmode -y command, the output is shown in the sqexplain.out.sessionID file.
If you want to turn ON SET EXPLAIN ON AVOID_Execute ON session 30 dynamically, you can run:
Onmode -- Y 30 2Copy the codeIf you want to dynamically enable the SET EXPLAIN ON feature for Session 30, you can run:
Onmode -- Y 30 1Copy the codeIf you want to dynamically enable the SET EXPLAIN OFF feature for Session 30, you can run:
Onmode -y 30 0Copy the codeDisplay query statistics
The EXPLAIN_STAT configuration parameter in the Informix ONCONFIG configuration file is used to control whether to include “Query statistics” in the sqexplain.out file. If the EXPLAIN_STAT configuration parameter is enabled, the query statistics section will appear in the SQExplain.out file. The “Query Statistics” section of the sqexplain.out file shows the estimated number of rows expected to be returned by the query plan, the actual number of rows returned, and other information about the query. You can use this information, which shows the overall traffic of the query plan and the number of rows that pass through each phase of the query, to debug performance issues.
EXPLAIN_STAT configuration parameters can be set to the following values:
| value | describe |
|---|---|
| 0 | The display of query statistics is disabled |
| 1 | Enable the display of query statistics |
We can also change this value by modifying the onconfig file, or we can set it dynamically by using the onmode -wf and onmode -wm commands.
Onmode -- wf EXPLAIN_STAT= 1 onmode -- wm EXPLAIN_STAT= 1Copy the codeQuery statistics can only be generated after the SQL statement is executed. When set Explain on and onmode — Y session_id 1 are executed, the sqexplain. Out file contains “Query statistics”.
The following example shows query statistics in the SET EXPLAIN output. If the estimated number of rows scanned or joined differs significantly from the actual number of rows, the statistics on these tables may be old and should be updated.
select * from tab1, tab2 where tab1.c1 = tab2.c1 and tab1.c3 between 0 and 15
Estimated Cost: 104
Estimated # of Rows Returned: 69
1) zelaine.tab2: SEQUENTIAL SCAN
2) zelaine.tab1: INDEX PATH
(1) Index Keys: c1 c3 (Serial, fragments: ALL)
Lower Index Filter: (zelaine.tab1.c1 = zelaine.tab2.c1
AND zelaine.tab1.c3 >= 0 )
Upper Index Filter: zelaine.tab1.c3 <= 15
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 tab2
t2 tab1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 50 50 50 00:00:00 4
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 67 69 4 00:00:00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 67 70 00:00:00 104Copy the codeQuery plan example analysis
Here we give some examples of query plans, hoping to have a more comprehensive understanding of the query plan.
Single table query
Here is the SET EXPLAIN output for the complex query on the Customer table:
QUERY: ------ SELECT fname, lname, company FROM customer WHERE company MATCHES 'Sport*' AND customer_num BETWEEN 110 AND 115 ORDER BY lname Estimated Cost: 1 Estimated # of Rows Returned: 1 Temporary Files Required For: Order By 1) virginia.customer: INDEX PATH Filters: virginia.customer.company MATCHES 'Sport*' (1) Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: virginia.customer.customer_num >= 110 Upper Index Filter: virginia.customer.customer_num <= 115Copy the codeThe following output line shows the range of index scans for this query:
- Start index scan with index key value 110:
Lower Index Filter: virginia.customer.customer_num >= 110
- Stop index scan with index key value 115:
Upper Index Filter: virginia.customer.customer_num <= 115
Multi-table query
SET EXPLAIN output for multi-table queries is shown below:
QUERY:
------
SELECT C.customer_num, O.order_num, SUM (I.total_price)
FROM customer C, orders O, items I
WHERE C.customer_num = O.customer_num
AND O.order_num = I.order_num
GROUP BY C.customer_num, O.order_num
Estimated Cost: 78
Estimated # of Rows Returned: 1
Temporary Files Required For: Group By
1) virginia.o: SEQUENTIAL SCAN
2) virginia.c: INDEX PATH
(1) Index Keys: customer_num (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: virginia.c.customer_num = virginia.o.customer_num
NESTED LOOP JOIN
3) virginia.i: INDEX PATH
(1) Index Keys: order_num (Serial, fragments: ALL)
Lower Index Filter: virginia.o.order_num = virginia.i.order_num
NESTED LOOP JOINCopy the codeThe SET EXPLAIN output lists the order in which the database server accesses the tables and the access plan for reading each table. The plan output above indicates that the database server will do the following:
- The database server will read it firstordersTable.
Because there are no filters on the Orders table, the database server must read all rows. Reading the table in physical order is the least expensive method.
- forordersFor each row, the database server searches the Customer table for a matching row.
Search for indexes using customer_num. The key-only flag means that only the index needs to be read for the Customer table because only the C. customer_num column is used in the join and output and is the index key.
- For each row in Orders that has a customer_num match, the database server uses the index of Order_num to search the items table for a match row.
Key first scan
A key-first scan is an index scan that uses keys that are not listed in low – and high-index filters. Sample queries using key-first scan are shown below:
select * from tab1 where (c1 > 0) and ( (c2 = 1) or (c2 = 2))
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) pubs.tab1: INDEX PATH
(1) Index Keys: c1 c2 (Key-First) (Serial, fragments: ALL)
Lower Index Filter: pubs.tab1.c1 > 0
Index Key Filters: (pubs.tab1.c2 = 1 OR pubs.tab1.c2 = 2)Copy the codeIn this example the database server will first try to reduce the number of possible rows by applying additional key filters. The database server uses the index to apply additional filters c2 = 1 OR c2 = 2 before reading the row data.
Query plan for subqueries
If the cost of joining is low, the optimizer can automatically change the subquery to join. The sample output from the SET EXPLAIN ON statement in the following example shows the optimizer changing the table in the subquery to the inner table in the join:
QUERY:
------
SELECT company, fname, lname, phone
FROM customer c
WHERE EXISTS(
SELECT customer_num FROM cust_calls u
WHERE c.customer_num = u.customer_num)
Estimated Cost: 6
Estimated # of Rows Returned: 7
1) virginia.c: SEQUENTIAL SCAN
2) virginia.u: INDEX PATH (First Row)
(1) Index Keys: customer_num call_dtime (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: virginia.c.customer_num = virginia.u.customer_num
NESTED LOOP JOIN (Semi Join)Copy the codeWhen the optimizer changes a subquery to a join, it can use several variants of the access plan and join plan:
- The first line scan
A front-row scan is a variant of a table scan. When the database server finds a match, the table scan stops.
- Ignore the copy index scan
Ignoring copy index scan is a variant of index scan. The database server does not scan copies.
- Half a connection
A semi-join is a variant of a nested loop join. When the first match is found, the database server stops the inner table scan.
SQL statement optimization
Through the analysis of SQL statement query plan, we can know what kind of access method is used in the execution process of SQL statement, is sequential scan or index scan; What kind of method is used to join tables, nested loop join or hash join; What is the order of access between tables; Whether temporary tables are generated; What is the cost of the query. In order to improve the performance of SQL statements, we can consider whether to create appropriate indexes, whether to adjust the join order between tables, whether to change the way SQL statements are written, and so on. Usually, when we adjust, we can compare the query cost before and after the change to ensure a significant reduction in the query cost. Alternatively, we can specify the data access method by setting the OPTCOMPIND parameter; Through the access plan indication, join order indication, join plan indication, target indication to specify data access method, table join order, table join method and data return result set; Improve SQL statement performance by executing update statistics statements. For details on how to use the OPTCOMPIND parameter and query instructions, refer to the Informix Information Center.
Back to the first page
SQL drill down query feature
When monitoring SQL statement performance, we often need to know how long the SQL statement has been executed; How many system resources are occupied by SQL statements, such as CPU usage, memory usage, disk I/O read and write status; The time and number of times the SQL statement waits for system resources such as disk I/O and locks. Based on the usage and waiting of SQL statements, we can learn about the bottleneck of SQL statements and adjust system resource configuration or users’ applications in a timely manner. The set Explain method we introduced above can help us understand some SQL statement performance problems, but when we enable the set Explain function, SQL statement performance problems may already occur. To enable DBAs to have a more timely and detailed view of the resource usage of SQL statements and make adjustments accordingly, SQL drill-down queries are provided in Informix to meet the above capabilities.
The SQL drill-down query feature collects statistics about every SQL statement executed on the system and analyzes statement history. It helps you answer the following questions:
- How long the SQL statement takes
- How many resources does each statement use?
- How long does it take to wait for each resource?
- What is the query plan?
Statistics are stored in a circular buffer (a pseudo-table in memory named SYSSQLTRACE), that is, in the SYSMaster database. You can dynamically resize the circular buffer.
This feature is turned off by default, but you can turn it on for all users or for a specific set of users. When this feature is enabled with default configuration, the database server keeps track of the last 1000 SQL statements running and their summary statistics, with each SQL statement occupying 1K of space.
This feature requires a lot of memory if you want to keep a lot of historical information. The default amount of space required for SQL history tracing is 1 Megabyte. You can increase or decrease storage as required. If you don’t want to use memory for this, you can disable SQL history tracing.
Use the SQLTRACE configuration parameter to specify the start SQL trace information
We can control the default trace behavior when the database server starts by modifying the SQLTRACE configuration parameter in the $InformixDIR/etc/$ONCONFIG file. The information set includes the number of SQL statements to trace and the tracing method.
SQLTRACE configuration parameter syntax:
SQLTRACE [Level=off|low|med|high],
[Ntraces=number of traces],
[Size=size of each trace buffer],[Mode=global|user]Copy the codeAmong them:
The level field can specify one of the following values:
- Low: It is used to capture statement statistics, statement text, and statement iterators. It is the default trace level when SQL tracing is enabled.
- Medium: This trace level captures all the information contained in the low-level trace, plus the table name, database name, and stored procedure stack.
- High: This trace level captures all the information contained in the intermediate trace, plus the main variables.
- Off: This does not specify SQL tracing. The default value is OFF
The ntraces field specifies the number of SQL statements to trace, ranging from 500 to 2147483647.
Size field, which specifies the number of kilobytes of trace buffer size. Trace information for each SQL statement uses a trace buffer, and if this buffer size is exceeded, the database server discards the saved data. The range is 1K-100K.
The mode field, specifying any of the following:
- Global: Tracks all users on the system, which is the default.
- User: Tracks the specified User (specify this if you want to get a sample of THE SQL being run by a small group of users).
When setting the SQLTRACE parameter, we need to consider the following:
- SQLTRACE buffer
The collected statistics are stored in a memory buffer of =Ntraces*Size. The more statements you collect and the more detailed information you collect, the more buffers you need and the more memory resources you use. Therefore, during configuration, you must consider your own actual situation. In addition, this buffer is a circular buffer that will discard old information when the buffer size is insufficient, so consider its size if you need to save SQL statement tracing history. When we turn off SQL trace, the information stored in the SQLTRACE buffer is also lost. If you need to save the information, save it to a table or file before turning off SQL trace to prevent loss.
- About the size parameter
The size parameter is determined by the size and Level of the SQL statement. Different levels collect different amounts of information. Medium collects more information than Low, and High collects more information than Medium. Therefore, when choosing, we should give full consideration to the purposes for which we collect statistical information. Generally, Low Level is suitable for error diagnosis and performance tuning, while High Level is used for system load reproduction. Medium levels are also used in error diagnosis situations.
- About Mode parameters
Global mode for all users of statistical information on the tracking system, therefore, when in a relatively busy system, may soon be collected a lot of information, at the same time, all users of information are included in the together, analysis is more tedious, usually, select global mode, mainly used for all sessions on comparison system resource usage, Or we don’t know which specific user’s resource usage to analyze. In general, we will use user mode, so that we can analyze more clearly, at the same time, does not consume too many system resources.
The following statement specifies that the database server will collect low-level information about up to 2000 SQL statements executed by all users on the system and will allocate approximately 4 megabytes of memory (2000 x 2 kilobytes).
SQLTRACE level=LOW,ntraces=2000,size=2,mode=globalCopy the codeThe following statement specifies that the database server will collect high-level information about up to 2000 SQL statements executed by all users on the system and will allocate approximately 4 megabytes of memory (2000 x 2 kilobytes).
SQLTRACE level=high,ntraces=2000,size=2,mode=globalCopy the codeUsing SQLTRACE configuration parameters is suitable for setting some default configurations. If frequent changes are required, using ADMIN API commands is more convenient.
Use the ADMIN API command to specify enabling SQL trace information
If you do not want to set the SQLTRACE configuration parameter to restart the server, you can execute the following ADMIN API command, which provides the same functionality as setting SQLTRACE. Using the ADMIN API’s Task () and ADMIN () functions, you can dynamically change the setting of SQLTRACE without restarting the server. Only Informix users have the authority to execute ADMIN API commands. SQLTRACE Settings modified by the ADMIN API command are not saved to SQLTRACE configuration parameters, so the SQLTRACE configuration parameter values will take effect when the server is restarted.
When the SQLTRACE configuration parameter is OFF, the system uses the default setting when we start the SQL trace with the following ADMIN API command. That is:
SQLTRACE level=low,ntraces=1000,size=1,mode=global
execute function task("set sql tracing on");Copy the codeWe can also specify our own values. The following statement specifies that the database server will collect low-level information about up to 2000 SQL statements executed by all users on the system and will allocate approximately 4 megabytes of memory (2000 x 2 kilobytes).
execute function task("set sql tracing on", 2000,"2k","low","global");Copy the codeTo stop collecting SQL statement information, we can execute:
execute function task("SET SQL TRACING OFF");Copy the codeAfter the preceding command is executed, the SQL trace function is disabled and the trace buffer is lost.
Enable SQL history tracing for a specific user
It is generally recommended to enable SQL history tracing for specific users, which not only saves system memory but also makes analysis clearer. After you enable the SQL trace system as a user, you can enable tracing for a specific user. After you specify user as the mode in the SQLTRACE configuration parameter, you must execute the administration API Task () or admin() function to turn on SQL history tracing for a particular user.
If global SQL tracing is disabled, the administration API Task () or admin() functions can be executed to enable SQL tracing for a specific user.
To enable SQL history tracing for a particular user, you can execute the task or admin() function and specify set SQL Tracing on and define the user’s information.
If we need to enable SQL statement tracing for Session 30, we can execute:
execute function task("set sql tracing on", 1000, 1,"low","user");
execute function task("set sql user tracing on",30)Copy the codeIf we need to enable SQL statement tracing for users currently connected to the system (as long as they are not logged in as user root or Informix), we can execute:
dbaccess sysadmin -<<END
execute function task("set sql tracing on", 1000, 1,"low","user");
select task("set sql user tracing on", sid)
FROM sysmaster:syssessions WHERE username not in ("root","Informix");
ENDCopy the codeIf we need to stop tracking session 30, we can do:
Execute function task(" Set SQL user tracing off",30);Copy the codeSQL tracking information display and analysis
SQL drill-down query information can be obtained by executing the onstat -g his command or by querying the SYSSQLTRACE pseudo-table in the sysmaster database.
Onstat -g his command
We can display the SQL drilldown query information by executing onstat -g his.
Grammar:
>>-onstat-- -g--his--------------------------------------------><Copy the codeThe onstat -g his option displays the information collected by SQLTRACE and formats the output. By default, only DBSA can view onstat -g his syssQLtrace information. However, when UNSECURE_ONSTAT = 1, all users can view this information. The onstat -g his option displays all of the collected information, not just for a particular user session or SQL statement. Therefore, it is more suitable for small amount of data display, its advantage is more convenient.
The output of the onstat -g his option consists of trace profile, statement text, and statement statistics.
Trace profile: This is the first few lines of the output of the onstat -g his command that describe the level of the Trace, the Trace mode, the number of SQL statements being traced, the size of the Trace buffer, and how long the Trace buffer is held. As follows:
Example of the output of onstat -g his (Trace profile section) :
Statement history: Trace Level High Trace Mode User Number of traces 50000 Current Stmt ID 3 Trace Buffer size 12264 Duration of buffer 37 Seconds Trace Flags 0x00007F21 Control Block 0x4b8cd018 ... .Copy the codeStatement text and iterators: the next few lines of the onstat -g his command output describe the SQL statements being traced and the iterators and query plan information used in the query. The content of the SQL statement section varies depending on the trace level. If the trace level is LOW, only the hexadecimal description of the SQL statement being traced and the database being used is displayed. If the trace level is Medium, the database name, SQL statement, table name used in SQL statement, and stored procedure call stack information are displayed. If the trace level is high, in addition to medium level information, host variable information used in the SQL statement is displayed. As follows:
Onstat -g his example output (Statement text and iterators: part) :
. . Statement # 3: @ 0x4b907018 Database: sysmaster Statement text: Select first 2 substr(tabname,1,20) as table, isreads as reads from sysptprof where isreads > 10 order by isreads desc SELECT using tables [ systabnames sysptntab ] Iterator/Explain ================ ID Left Right Est Cost Est Rows Num Rows Partnum Type 3 0 0 9 33 40 20 Seq Scan 4 0 0 1 100 1 15 Index Scan 2 3 4 28 33 40 0 Nested Join 1 2 0 1 33 2 0 Sort 4 0 0 18 92 92 Disk Scan 2 3 4 287 1380 5060 Nested Join 1 2 0 1 1 5060 Insert ... .Copy the codeStatement Information and Statistics: The following section contains SQL statements and performance statistics, and is the most important part of our monitoring. It allows you to find information about SQL statements such as memory usage, disk I/O, lock usage and contention, CPU usage, sorting and index usage. Accordingly, we can adjust accordingly. We can break it down into the following three parts:
Statement information:
- User ID of the user running the command
- Database session id
- Database name
- Type of SQL statement
- Duration of SQL statement execution
- The time at which the statement completes
- A list of SQL statement text or function calls (also called a stack trace) with statement types, such as: Procedure1 () Calls Procedure2 () Calls Procedure3 ()
RSAM statistics: Describes the following information:
- The number of buffer reads and writes
- The number of page reads and writes
- The number of sorts and disk sorts
- Number of lock requests and waits
- Number of logical log records
SQL statistics: Describe the following information:
- Estimated number of rows
- Optimizer estimates cost
- The number of rows returned
- SQL/ISAM error
- Database isolation level
- Memory usage of SQL statements.
Onstat -g example of his output (Statement Information and statistics section) :
. . Statement information: Sess_id User_id Stmt Type Finish Time Run Time 26 501 SELECT 23:31:01 0.0054 Statement Statistics: Page Buffer Read Buffer Page Buffer Write Read Read % Cache IDX Read Write Write % Cache 0 410 100.00 00 0.00 Lock Lock LK Wait Log Num Disk Memory Requests Waits Time (S) 00 0.0000 0.000 B 1 0 1 Total Total Avg Max Avg I/O Wait Avg Executions Time (S) Time (S) Time (S) IO Wait Time (S) Per Sec 1 0.000000 0.000000 370.1291 Estimated Actual SQL ISAM Isolation SQL Cost Rows Rows Error Error Level Memory 28 33 2 0 0 CR 34176Copy the codeOutput description:
- Page Read: Number of pages that have been Read from the disk
- Buffer Reads: The number of times a page is read from the Buffer pool rather than from disk
- Read % Cache: The percentage of times the page was Read from the buffer pool
- Buffer IDX Read: The number of Buffer reads for the index page
- Page Write: indicates the number of pages written to the disk
- Buffer Write: Modifies the number of pages that are sent back to the Buffer pool
- Write % Cache: The percentage of times the page was written to the buffer pool instead of to disk
- Lock Requests: The total number of locks required for this statement
- Lock Waits: Number of times the SQL statement Waits for a Lock
- LK Wait Time: The Time (in seconds) used to Wait for a lock during the execution of this SQL statement
- The Log Space:
- Num Sorts: The total number of Sorts used to execute the statement
- Disk Sorts: The number of Sorts performed on the Disk for the SQL statement
- Memory Sorts
- Total Executions: The Total number of times the statement was executed, or the number of times the cursor was reused
- Total Time: The Total Time (in seconds) for executing the statement
- Avg Time: The average Time (in seconds) for executing the statement
- Max Time: The total Time (in seconds) in which the SQL statement was run, excluding any Time used by the application
- LK Wait Time: The amount of Time a statement waits for an application lock
- Avg IO Wait: The amount of time for a statement to Wait for I/ OS, excluding any asynchronous I/ OS.
- Avg Rows Per Sec: The average number of Rows Per second generated by this statement
- Estimated Cost: indicates the Cost associated with SQL statements
- Estimated Rows: The Estimated number of Rows returned, Estimated by the statement optimizer
- Actual Rows: The number of Rows returned for this statement
- SQL Error: INDICATES the SQL Error number
- ISAM Error: INDICATES the RSAM/ISAM Error number
- Isolation Level: The Isolation Level used when the statement is run
- SQL Memory: The number of bytes required by the SQL statement
Syssqltrace pseudo table
In Informix, three in-memory pseudo tables are provided to hold SQL trace information. The SYSSQLTRACE table is used to provide detailed trace information for each SQL statement being traced. The size is determined by ntraces*size. We can dynamically resize it. The three in-memory pseudo tables can be accessed through the sysmaster database.
The output of the syssQLtrace table is similar to the Statement information and statistics output of the onstat -g his command.
Since SQL trace information can be accessed using SQL statements, this approach is suitable for querying detailed trace information about a single SQL statement or a group of SQL statements.
The basic structure of the SYSSQLTRACE table is as follows:
| column | type | describe |
|---|---|---|
| sql_id | int8 | Unique SQL execution identifier |
| sql_address | int8 | Address of a statement in a code block |
| sql_sid | int | The database session id of the user running the SQL statement |
| sql_uid | int | The user id of the statement that runs the SQL |
| sql_stmttype | int | Statement type |
| sql_stmtname | char(40) | The statement type displayed as a word |
| sql_finishtime | int | Completion time of this statement (UNIX) |
| sql_begintxtime | int | The start time of this transaction |
| sql_runtime | float | Statement execution time |
| sql_pgreads | int | The number of disk reads for this SQL statement |
| sql_bfreads | int | The number of buffer reads for this SQL statement |
| sql_rdcache | float | Percentage of time a page was read from the buffer pool |
| sql_bfidxreads | int | Number of index page buffer reads |
| sql_pgwrites | int | Number of pages written to disk |
| sql_bfwrites | int | Number of pages that have been modified and returned to the buffer pool |
| sql_wrcache | float | Percentage of times a page was written to the buffer pool, but not to disk |
| sql_lockreq | int | The total number of locks required for this SQL statement |
| sql_lockwaits | int | The number of times the SQL statement waited for a lock |
| sql_lockwttime | float | The time that the system waits for a lock during an SQL statement |
| sql_logspace | int | The amount of space used by SQL statements in logical logs |
| sql_sorttotal | int | The number of sorts for the statement to run |
| sql_sortdisk | int | The number of sorts running on disk |
| sql_sortmem | int | The number of sorts running in memory |
| sql_executions | int | The number of times the SQL statement was run |
| sql_totaltime | float | Total time spent running a statement |
| sql_avgtime | float | The average amount of time taken to run a statement |
| sql_maxtime | float | The maximum amount of time to execute an SQL statement |
| sql_numiowaits | int | Number of times an I/O operation must wait |
| sql_avgiowaits | float | The average amount of time an SQL statement must wait |
| sql_totaliowaits | float | The total amount of time the SQL statement must wait for I/O. This does not include any asynchronous I/O. |
| sql_rowspersec | float | Average number of rows generated (per second) |
| sql_estcost | int | Costs associated with SQL statements |
| sql_estrows | int | The estimated number of rows returned for the SQL statement as predicted by the optimizer |
| sql_actualrows | int | Is the number of rows returned by the SQL statement |
| sql_sqlerror | int | SQL error no. |
| sql_isamerror | int | RSAM/ISAM error number |
| sql_isollevel | int | Isolation level of SQL statements. |
| sql_sqlmemory | int | Number of bytes required to execute SQL statement |
| sql_numiterators | int | Number of iterators used by the statement |
| sql_database | char(128) | The database name |
| sql_numtables | int | Number of tables used in executing SQL statements |
| sql_tablelist | char(4096) | Table names directly referenced in the SQL statement. If the SQL statement fires a trigger that executes a statement on another table, these other tables are not listed. |
| sql_statement | char(1600) | SQL statement that has been run |
If we want to view the SQL trace for session 30, we can execute:
select * from syssqltrace where sql_id =30;Copy the codeSyssqltrace_info pseudo table
The sySSQLTrace_INFO pseudo table is also a memory table that holds tracing profile information. We can access the in-memory pseudo table through the sysmaster database. Tracing profile information is mainly used to describe the tracing level, tracing mode, number of SQL statements to trace, size of trace buffer, and duration of trace buffer.
The output of the syssQLtrace_info pseudo table is similar to the output of the tracing profile section from the onstat -g his command.
The basic structure of the SYSSQLTRACe_INFO table is as follows:
| column | type | describe |
|---|---|---|
| flags | integer | SQL trace flag |
| ntraces | integer | Number of items to track |
| tracesize | integer | The size of the text stored for each SQL trace entry |
| duration | integer | Trace buffer (in seconds) |
| sqlseen | int8 | Number of SQL items tracked since startup or resizing |
| starttime | integer | When tracing was enabled |
| memoryused | int8 | SQL tracks the number of bytes of memory used |
Syssqltrace_iter pseudo table
Syssqltrace_iter Pseudo table is also a memory table that stores Statement text and iterators information. We can access the in-memory pseudo table through the sysmaster database. Statement text and iterators describes the SQL statements to be traced, iterators used in queries, and query plans. This table is often used to query query plan information for a particular SQL statement.
The output of the syssQLtrace_iter pseudo-table is similar to that of Statement text and iterators displayed after you run the onstat -g his command.
The basic structure of the SYSSQLTRACe_iter table is as follows:
| column | type | describe |
|---|---|---|
| sql_id | int8 | SQL execution identity |
| sql_address | int8 | Address of the SQL statement block |
| sql_itr_address | int8 | The address of the iterator |
| sql_itr_id | int | Iterator identifier |
| sql_itr_left | int | Iterator identifier to the left |
| sql_itr_right | int | The iterator identifier to the right |
| sql_itr_cost | int | Iterator cost |
| sql_itr_estrows | int | The iterator estimates the number of lines |
| sql_itr_numrows | int | The number of lines actually processed by the iterator |
| sql_itr_type | int | Iterator type |
| sql_itr_misc | int | Iterator miscellaneous flag |
| sql_it_info | char(256) | Iterator miscellaneous flag displayed as text |
If we want to see the iterator and type used for a query with sql_id=15, we can execute:
Select sql_itr_type, substr(sql_itr_info,1,20) as iterator_info, sql_itr_numrows from syssqltrace_iter where sql_id = 14;Copy the codeBy executing the onstat -g his command or querying the SYSSQLTRACE pseudo-table in the sysmaster database, We can look at the SQL statements running on the system, the resources used to execute the SQL, the time taken to run the SQL, the number of disk/page/buffer reads and writes, the number of locks used, the number of sorts used, and the amount of memory used. Also, you can view the estimated time taken by the Informix optimizer to run the SQL. In this way, we can learn about the resource usage of SQL statements and existing resource bottlenecks, and adjust accordingly. In addition, we can compare the estimated number of returned rows by the Informix optimizer with the actual number of returned rows (SQL_ESTROWS and SQL_ACTUalRows). If these two values are significantly different, the Informix optimizer does not have correct statistics about the number of rows and indexes in the table. This means that you need to run Update Statistics to provide the optimizer with the correct data.
In addition, by querying the SYSSQLTRACE table, we can also find the longest running SQL queries in the system, queries with too many table join operations, unwanted join types, queries with too many return records, and queries with lock waits, so that we can timely adjust and improve the performance of SQL statements.
If we wanted to find the longest running SQL query, we could execute:
Select first 5 substr(sql_statement,1,50) as statement, sql_avgtime as Average_Time, Sql_executions as Number_of_times FROM syssQLTRACE ORDER by SQL_AVgtime desc; sql_executions as Number_of_times from syssQLtrace ORDER by SQL_avgtime desc;Copy the codeIf we want to find a query that returns too many records, we can do:
select first 5 sql_estrows as est_rows , sql_actualrows as actual_rows , Substr (sql_statement,1,30) as statement from syssqltrace order by sql_actualrows desc;Copy the codeconclusion
This article mainly introduces the Informix database for SQL statement performance monitoring two basic methods and use, for SQL statement tuning methods and suggestions, you can refer to the Informix information center performance tuning section.