Routine maintenance script of Informix Database

Check the space usage of each dbspaces: select name dbspace, sum (chksize) allocated, sum (nfree) free, round (sum (chksize)-sum (nfree )) /sum (chksize) * 100) | "%" pcused form sysdbspaces d, syschunks c where d. dbsnum = c. dbsnum group by name order by 4 desc the remaining space must be larger and better. display the I/O status of each dbspaces: select d. name, fname path_name, sum (pagesread) diskreads, sum (pageswritten) diskwrites from syschkio c, syschunks k, sysdbspaces d where d. dbsnu M = k. dbsnum and k. chknum = c. chunknum group by 1, 2 order by 3 desc adjusts the table layout based on the I/O of each dbspaces, enabling the entire system I/O to evenly check which table has the most disk I/0: select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites) diskwrites from sysptprof order by 3 desc, 4 desc same as above. Check the extent distribution of the Table: select t. tabname, count (*) num_ext from sysextents e, npmdb: tables Ables t where e. tabname = t. tabname and dbsname = "npmdb" and t. tabname not like "sys %" group by 1 having count (*)> 1 order by 2 desc table extent is recommended to have no more than 30. If it is too large, you need to recreate the table and modify the extent size so as to modify the number of indexes in the extent checklist (the fewer the better): select idxname, levels from sysindexes order by 2 desc check index with low hit rate (the closer nrows and unique are, the better): select tabname, idxname, Nrows, nunique from tables Ables t, sysindexes I where t. tabid = I. tabid and t. tabid> 99 and nrows> 0 and nunique> 0 when the indexing efficiency is low, you need to modify the indexes according to the actual situation and check that the tables in the database have more fragments (smaller fragments are better) select dbsname, tabname, count (*), sum (size) from sysextents group by 1, 2 order by 3 desc; read/write status of tables and indexes (check that the database has many read/write operations) select dbsname, tabname, (isreads + pagreads) diskreads, (iswrites + pagwrites) diskwrites from sysptprof order by 3 Desc, 4 desc those tables have fierce lock competition (the smaller the better) select. tabname, nrows, lockwts, deadlks from sysmaster: sysptprof a, Ables B where. tabname = B. tabname and lockwts> 0 and. dbsname = name and B. tabid> = 100 order by tabname; number of sequential scans of a table (the smaller the number of sequential scans of a large table, the better) select. tabname, nrows, seqscans from sysmaster: sysptprof a, Ables B where. tabname = B. tabname and seqscans> 0 and. dbsname = 'database name' and B. tabid> = 100 order by ta Bname; view the space occupied by the informix database table: query the tables systabnames and sysptnext in the sysmaster database. The parameter abnames. partnum = sysptnext. pe_partnum. run the following SQL statement: select tabname, sum (pe_size) from tables abnames, sysptnext where partnum = pe_partnum and tabname = 'table _ name' -- (table_name indicates the name of the table to be queried) group by tabname (Note: The pe_size value in the query results, in IBM is 4 k, the HP-UX is 2 k) with onstat check that the thread is doing, and then analyze. I used onstat-u to view the number of locks. Use onstat-g ses to find the session id and use onstat-g SQL session id to view what it is doing. Update statistics for table temp_user;