number of pages read during sequential scans

Hello, is it possible to get the number of pages that have been read during sequential scans on the database tables? In other words, I would like to know not only how many sequential scans have been performed on a table, but also how many pages have been read during those scans. The Informix version is IDS 9.4. Thanks in advance for any help. Greetings, Tomek.

In message <1142006554.183058.255500@e56g2000cwe.googlegroups.com>, stokrota@gmail.com writes >Hello, > >is it possible to get the number of pages that have been read >during sequential scans on the database tables? > >In other words, I would like to know not only how many sequential >scans have been performed on a table, but also how many pages >have been read during those scans. > >The Informix version is IDS 9.4. > >Thanks in advance for any help. I don't know how to do what you are want exactly, but your query getting the #scans can work out the data pages in the table as you have nrows and rowsize available. When I have been looking for sequential scans I've always excluded tables with small numbers of rows as they are not going to impact on performance - that might be the best way to do the scan regardless of the indexes. -- Surfer! Email to: ramwater at uk2 dot net

stokrota@gmail.com wrote: > Hello, > > is it possible to get the number of pages that have been read > during sequential scans on the database tables? > > In other words, I would like to know not only how many sequential > scans have been performed on a table, but also how many pages > have been read during those scans. > > The Informix version is IDS 9.4. > > Thanks in advance for any help. > > Greetings, > Tomek. > A sequential scan reads the whole table, so the number of pages read is equal to the number of data pages in the table. You can retrieve the number of data pages from sysmaster:systabinfo.ti_npdata Claus

Claus Samuelsen said: > stokrota@gmail.com wrote: >> Hello, >> >> is it possible to get the number of pages that have been read >> during sequential scans on the database tables? >> >> In other words, I would like to know not only how many sequential >> scans have been performed on a table, but also how many pages >> have been read during those scans. >> >> The Informix version is IDS 9.4. >> >> Thanks in advance for any help. > > A sequential scan reads the whole table, so the number of pages read is > equal to the number of data pages in the table. You can retrieve the > number of data pages from sysmaster:systabinfo.ti_npdata Does a sequential scan always read the entire table or the entire index? -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo

Obnoxio The Clown wrote: > > Does a sequential scan always read the entire table or the entire index? > The phrase 'full table scan' is often used, but I don't think we have anything like half or partial table scan, when doing sequential scans. Just for curiousity: The Red Brick dbms has an interesting 'elevator' feature, where a new sequential scan process can attach to an already running scan process. When the first process ends, the second process just have to reread the pages the process 1 had read when process 2 attached. In this way the total number of page reads is reduced dramatically. Claus

In message <mailman.60.1142068412.18205.informix-list@iiug.org>, Obnoxio The Clown <obnoxio@serendipita.com> writes > >Claus Samuelsen said: >> stokrota@gmail.com wrote: >>> Hello, >>> >>> is it possible to get the number of pages that have been read >>> during sequential scans on the database tables? >>> >>> In other words, I would like to know not only how many sequential >>> scans have been performed on a table, but also how many pages >>> have been read during those scans. >>> >>> The Informix version is IDS 9.4. >>> >>> Thanks in advance for any help. >> >> A sequential scan reads the whole table, so the number of pages read is >> equal to the number of data pages in the table. You can retrieve the >> number of data pages from sysmaster:systabinfo.ti_npdata > >Does a sequential scan always read the entire table or the entire index? > Surely that depends on if the information is in an index or not? -- Surfer! Email to: ramwater at uk2 dot net

Surfer! wrote: >> >> Does a sequential scan always read the entire table or the entire index? >> > Surely that depends on if the information is in an index or not? > Generally we use the phrase 'sequential scan' for a scan on the data part of the table. If it's scan on an index, the word 'index' is used. The explain feature in IDS might not be very accurate on this, but the meaning is obvious in the context: QUERY: ------ select * from customer Estimated Cost: 2 Estimated # of Rows Returned: 28 1) csa.customer: SEQUENTIAL SCAN QUERY: ------ select count(*) from customer where customer_num > 0 Estimated Cost: 2 Estimated # of Rows Returned: 1 1) csa.customer: INDEX PATH

Good question! Assume I have an application which browses the customer data with a cursor. There is no index on the customer data, so sequential scan is necessary. Now, if the cursor is closed before it gets to the last record, it is possible that not all of the data has been read sequentially. Or am I wrong? Thanks to all of you for your answers. Greetings, Tomek.

On 11 Mar 2006 05:01:01 -0800, stokrota@gmail.com <stokrota@gmail.com> wrote: > Good question! Assume I have an application which browses the customer > data with a cursor. There is no index on the customer data, so sequential > scan is necessary. Now, if the cursor is closed before it gets to the last record, > it is possible that not all of the data has been read sequentially. > Or am I wrong? It depends on the SELECT statement that the cursor applies to. If it has an ORDER BY clause (and no filter conditions), then you are guaranteed that the whole table has been read, because it cannot start returning data until it has read it all and sorted it. OTOH, if you have no ORDER BY clause and you have read, say, 70% of a 2GB table when you abandon the operation, it is highly unlikely that all the remaining 600MB of data has been read into your server's buffer pool as a result of your activity - someone else might have put it there, of course. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

In the second case, is it possible to retrieve from the database engine information of exactly how many pages have been read during sequential scans? Is Informix gathering such a data somewhere? (I can't analyze each query, what I am looking for is statistics about seq scans & pages read for each table) Tomek.

I think the following could work: * Establish two sessions in a special test program to the IDS: One is the test session which executes a query, fetches data etc., the other session watches the data for the test session in sysmaster:syssesprof. * The test session finds out its session id (select dbinfo('sessionid') from table(set{1})). * The other session queries (and stores) sysmaster:syssesprof (with the sid from above) for the data of the test session (one row). Interesting are for example the columns: bufreads, pagreads etc.(http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.adref.doc/adref216.htm) * The test session now does it's work. Query with sequential scan etc. etc. * The other session now queries sysmaster:syssesprof again, and you can calculate the difference in bufreads, pagreads etc.

On 11 Mar 2006 06:22:59 -0800, stokrota@gmail.com <stokrota@gmail.com> wrote: > In the second case, is it possible to retrieve from the database engine > information of exactly how many pages have been read during sequential > scans? > > Is Informix gathering such a data somewhere? > > (I can't analyze each query, what I am looking for is statistics about > seq scans & pages read for each table) Please read the FAQ on how to respond to a posting in this (and any other) news group from Google accounts - it is not the obvious reply button. You owe it to people answering your questions to include enough of the previous posting that your posting makes sense. There is no way to get the information for a single session without any possibility of interference from concurrent sessions - IDS does not record the per-session statistics. Doubly so, it does not record the statistics for individual tables. IDS does record the overall statistics for the server - 'onstat -p' profiling or select from sysmaster:sysprofile table (check spelling). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

stokrota@gmail.com wrote: > In the second case, is it possible to retrieve from the database engine > > information of exactly how many pages have been read during sequential > scans? > > Is Informix gathering such a data somewhere? > > (I can't analyze each query, what I am looking for is statistics about > seq > scans & pages read for each table) > > Tomek. > You can get the number of sequential scans per table from sysmaster:sysptprof Claus