Mismatch between recordcount and actual number of records in the buffer

[E. van der Poll]

I am trying to get some Oracle query statistics for the queries we are using. I found that the number of records read from the server does not match the number of records fetched by the program.

I use a TOracleDataset, in combination with a DBgrid. Normally the ReadBuffer is set to 25. If the grid can show 13 records, there will still be 25 records read from the server. I can see that in the SQL monitor. If I display RecordCount as a means of comparing the statistics I get the wrong values.

Can I access the actual number of records read from the server (other than setting ReadBuffer to 1)?

 

[Marco Kalter]

The ReadBuffer controls how many records are fetched from the server in one network roundtrip (one "fetch" operation). This does not affect application logic. The RecordCount property indicates how many records are in the dataset's resultset. If QueryAllRecords is set to True, this will always be equal to the total result set size. Set it to False to force the dataset to only get records into the result set when the application requests it.

 

[E. van der Poll]

We do not want QueryAllRecords to be true, because activating a query would sometimes be too slow.

For similar reasons we do not want ReadBuffer set to 1, because we want to minimize network traffic.

So if we want to get statistical information from v$mystat we need to know the actual number of records read from the server into the read buffer. This may be larger than the number of records actually fetched into the dataset resultset (which is what RecordCount gives us).

So to correlate the statistical information with the actual number of records read from the server we would need to known how many times a full buffer was read, plus the number of records still present in the readbuffer but not fetched into the resultset.

In numbers: if the grid can show 13 records, but the readbuffer is 25 records, and the total query would return only 20 record, we get the statistics for 20 records, while RecordCount returns 13 and ReadBuffer is 25. We need that number 20 to be able to correctly interpret the oracle statistics.

 

[E. van der Poll]

What we actually are doing is:
(1) execute select from v$mystat
(2) execute our own query
(3) execute select from v$mystat
(4) execute select from v$mystat

The statistical results are the difference between (1) and (3), corrected for the statistics of v$mystat (being the difference between (3) and (4). So for (2) we need the actual number of records read from the server, and not RecordCount.

 

[E. van der Poll]

The question has not yet been answered: How can I get the number of records read from the server, as opposed to the number of records actually fetchedinto the dataset.

 

[Marco Kalter]

The records fetched from the server is returned by the TOracleQuery.RowsProcessed property.

For a TOracleDataSet you cannot directly get the RowsProcessed property.

The work around is to derive your own class from TOracleDataSet and access the InternalQuery function. This returns the TOracleQuery instance that is used when you call ExecSQL. It is a protected function, so only descendants can access it:

TmpOracleDataSet = class(TOracleDataSet)
  end;

After that, you can typecast a TOracleDataSet to get access to InternalQuery:

OracleDataSet.Active := True;
  Rows := TmpOracleDataSet(OracleDataSet).InternalQuery.RowsProcessed;

 

[E. van der Poll]

Thanks.
Since we already used a derived class it was easy to add the rowprocessed function as described above. This gave us the correct values.