Estimating table access?

Geoff Wilson <gmwils@eisa.net.au> wrote in message news:8dh7dr$2eel$1@mired.zark.thedonkeys.org... > Is it possible to figure out how much particular tables are being accessed > under Informix 7.3? The problem is that I have a disk performance problem on a > server I have just started admining for. The database is pretty much all > stored in one partition on the machine and one database segement. The database > is accessed through a client program to which there is no source. I would like > to be able to work out which tables are taking the most hits in order to split > them off from the rest of the data. onstat -g ppf > > It would also be useful to be able to estimate data access paths so as to > reallocate resources to indexes. Ideally I would like to be able to find out > the most expensive SQL queries being run by the server (I remember being able > to do this under Oracle, but can't figure it out under Informix, maybe due to > lack of support?) In DB-Access : set explain on SELECT ... Then go to the $INFORMIXDIR/etc/sqexplain.out and you'll see path for this select. If you don't have sources, then locate sessions, running huge statements, then run 'onstat -g sql <session_id>' - you'll see current SQL statement. HTH. -- ------------------------------------------------- With best regards, Yuri Dovgart SAP R/3, Informix technical consultant, Informix Certified Professional, Senior System Consultant System Architecture and High Availability Systems, 'Telecominvest' company Email y_dovgart@tci.ukrtel.net ICQ 39284285

Is it possible to figure out how much particular tables are being accessed under Informix 7.3? The problem is that I have a disk performance problem on a server I have just started admining for. The database is pretty much all stored in one partition on the machine and one database segement. The database is accessed through a client program to which there is no source. I would like to be able to work out which tables are taking the most hits in order to split them off from the rest of the data. It would also be useful to be able to estimate data access paths so as to reallocate resources to indexes. Ideally I would like to be able to find out the most expensive SQL queries being run by the server (I remember being able to do this under Oracle, but can't figure it out under Informix, maybe due to lack of support?) Any help or pointers to information about this would be greatly appreciated. The performance tuning manual just doesn't seem to cut it for this. Geoff -- a paranoid is just someone with all of the facts at his disposal - william seaward burroughs 1914-1997 Geoff M. Wilson <gmwils@zark.thedonkeys.org> www.zark.thedonkeys.org/~gmwils

Geoff Wilson wrote: > Is it possible to figure out how much particular tables are being accessed > under Informix 7.3? The problem is that I have a disk performance problem on a Examine sysmaster:sysptprof for table and detached index information - there's really good stuff in there. (Initialize those stats using "onstat -z") > > server I have just started admining for. The database is pretty much all > stored in one partition on the machine and one database segement. The database > is accessed through a client program to which there is no source. I would like > to be able to work out which tables are taking the most hits in order to split > them off from the rest of the data. > > It would also be useful to be able to estimate data access paths so as to > reallocate resources to indexes. Ideally I would like to be able to find out > the most expensive SQL queries being run by the server (I remember being able The Oracle approach, as far as I can remember, is static, in that you pick an SQL and then run it to examine costs. Under Informix, you can follow a similar approach by using the SET EXPLAIN ON sql command, but it gives you estimates. Even with a freshly run 'UPDATE STATISTICS <whatever>' under your belt, these can be wrong at times, especially when statements are complex. To get dynamic information about which SQL statements need to be tuned, you could query the instance periodically to get a sampling of the "active" SQL (I generally do it once every 5 minutes). Over time, you could get a glimmering of an "SQL Profile" on your database. Rudy

Rudy Fernandes <rferdy@americasm01.nt.com> wrote on the Tue, 18 Apr 2000 10:18:59 -0400: > Geoff Wilson wrote: >> Is it possible to figure out how much particular tables are being accessed >> under Informix 7.3? The problem is that I have a disk performance problem on a > Examine sysmaster:sysptprof for table and detached index information - there's > really good stuff in there. (Initialize those stats using "onstat -z") Thanks > The Oracle approach, as far as I can remember, is static, in that you pick an SQL > and then run it to examine costs. > Under Informix, you can follow a similar approach by using the SET EXPLAIN ON > sql command, but it gives you estimates. Even with a freshly run 'UPDATE > STATISTICS <whatever>' under your belt, these can be wrong at times, especially > when statements are complex. Oracle stores the SQL text strings of queries that are run on the database. It also stores information attached to them about how many times they were run. The idea is so that you can monitor how much work the parser is doing, but it is also useful as it provides an idea as to what sort of queries are being run on the database server. > To get dynamic information about which SQL statements need to be tuned, you could > query the instance periodically to get a sampling of the "active" SQL (I > generally do it once every 5 minutes). Over time, you could get a glimmering of > an "SQL Profile" on your database. Where abouts is this information stored? Geoff -- a paranoid is just someone with all of the facts at his disposal - william seaward burroughs 1914-1997 Geoff M. Wilson <gmwils@zark.thedonkeys.org> www.zark.thedonkeys.org/~gmwils

In article <8dh7dr$2eel$1@mired.zark.thedonkeys.org>, Geoff Wilson <gmwils@eisa.net.au> wrote: > Is it possible to figure out how much particular tables are being accessed > under Informix 7.3? This scripts give you the number of writes and read for each table in a database. #!/bin/ksh -f # ########## # SCRIPT NAME : hits.sh # # PURPOSE : To determine the number of read / write hits are going # against a database # # NOTES : Expects the database name to be passes as a parameter # ########## db=$1 if [ -z "$1" ] then echo "\\nusage : hits.sh <dbname>" exit fi dbaccess sysmaster <<EOF select dbsname[1,12], tabname[1,15], pf_dskreads diskreads, pf_bfcread cache_reads, pf_dskwrites diskwrites, pf_bfcwrite cache_writes from sysptntab, systabnames where sysptntab.partnum=systabnames.partnum and tabname[1,3] != "sys" and tabname[1,2] != "mb" and tabname[1,3] != "ep_" and tabname[1,3] != "pbc" and dbsname not in ("sysmaster","rootdbs","sysutils") and tabname != "TBLSpace" and dbsname = '$db' order by 1, 3 asc,4 asc,5 asc,6 asc, 2 asc EOF Sent via Deja.com http://www.deja.com/ Before you buy.

Geoff Wilson wrote: > Oracle stores the SQL text strings of queries that are run on the database. It > also stores information attached to them about how many times they were run. > The idea is so that you can monitor how much work the parser is doing, but > it is also useful as it provides an idea as to what sort of queries are being > run on the database server. Reuse of SQL across sessions is one feature of Oracle that I really like. I didn't know about the stats it kept alongside - makes it even better. > > To get dynamic information about which SQL statements need to be tuned, you could > > query the instance periodically to get a sampling of the "active" SQL (I > > generally do it once every 5 minutes). Over time, you could get a glimmering of > > an "SQL Profile" on your database. > > Where abouts is this information stored? Unfortunately, this isn't stored. What one could do is use the "onstat -u" command to determine "active" sessions (these are sessions that are non-admin sessions that are not in the "wait on sm_read/netnorm condition"). Then, run an "onstat -g sql" against each of these sessions to determine the SQL they are running. There would be some parsing of the output of both commands before the result is appended to a date-specific file with a date-time stamp. The above commands would be packaged into a shell script that would be run periodically by cron. Eye-balling the resulting file can provide immediate clues to your instance's SQL profile. One could build a parser to make it easier. Obviously, I've built a set of scripts to do this. If you're interested, I could mail these to you privately. Rudy

Information on sql's running on the system are available in the syssqexplain and syssessions tables in the sysmaster database. You can join these tables by syssessions.sid and syssqexplain.sqx_sessionid to get information on sql's being run by current sessions. There are many columns in the syssqexplain table that can be very helpful. The following is a list of useful columns: sqx_sessionid - session id sqx_sdbno sqx_iscurrent sqx_executions sqx_cumtime sqx_bufreads sqx_pagereads sqx_bufwrites sqx_pagewrites sqx_totsorts sqx_dsksorts sqx_sortspmax sqx_conbno sqx_ismain sqx_sqlstatement - actual sql statement If you have any questions...let me know. Kirk In article <8djs5e$u6f$1@nnrp1.deja.com>, mgimbert8769@my-deja.com wrote: > In article <8dh7dr$2eel$1@mired.zark.thedonkeys.org>, > Geoff Wilson <gmwils@eisa.net.au> wrote: > > Is it possible to figure out how much particular tables are being > accessed > > under Informix 7.3? > > This scripts give you the number of writes and read for each table in a > database. > #!/bin/ksh -f > # ########## > # SCRIPT NAME : hits.sh > # > # PURPOSE : To determine the number of read / write hits are going > # against a database > # > # NOTES : Expects the database name to be passes as a parameter > # ########## > db=$1 > if [ -z "$1" ] > then > echo "\\nusage : hits.sh <dbname>" > exit > fi > dbaccess sysmaster <<EOF > select > dbsname[1,12], > tabname[1,15], > pf_dskreads diskreads, > pf_bfcread cache_reads, > pf_dskwrites diskwrites, > pf_bfcwrite cache_writes > from > sysptntab, > systabnames > where > sysptntab.partnum=systabnames.partnum and > tabname[1,3] != "sys" and > tabname[1,2] != "mb" and > tabname[1,3] != "ep_" and > tabname[1,3] != "pbc" and > dbsname not in ("sysmaster","rootdbs","sysutils") and > tabname != "TBLSpace" and > dbsname = '$db' > order by 1, 3 asc,4 asc,5 asc,6 asc, 2 asc > EOF > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.