Unused Index Search
A user on 11.70.FC2 asked why 'onstat -g ppf' shows indexes with zero Reads (isrd) but thousands/millions of Buffer Reads, assuming buffer reads were a subset of reads. Art Kagel suggested stats may have been zeroed (onstat -z) after the pages were cached; Fernando Nunes clarified the columns are unrelated — isrd counts ISAM read operations (not a 1:1 map to SQL statements), while bufreads/pagreads count page access — and that an index with buffer reads but no ISAM reads is likely being maintained by UPDATEs rather than read by SELECTs. He also gave a sysmaster query (sysshmvals, sh_pfclrtime/sh_clrtime via dbinfo utc_to_datetime) to find the last stats reset, which the poster used to rule out a reset. No firm conclusion on whether those indexes are truly in use.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm looking at index usage and have a question about onstat -g ppf output.
It has both a "Reads" and "Buffer Reads" column. I have some indexes with zero
"Reads" but, in a few cases, many thousands (and even millions) of "Buffer
Reads".
This doesn't make much sense to me if I assume "Buffer Reads" are a subset of
"Reads", which I expect to be the sum of disk+buffer)....so I have to assume
this logic isn't right.
So, the question is, why are Buffer Reads > 0 when Reads are = 0, and are
these really in use.
This is 11.70.FC2.
If the stat were zerod (onstat -z) since the last time that index pages
were read from disk (so your cache is running at steady state now) then the
disk reads will show zero while the buffered reads will read the activity
since the reset.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Dec 13, 2012 at 3:14 PM, <> wrote:
> I'm looking at index usage and have a question about onstat -g ppf output.
>
> It has both a "Reads" and "Buffer Reads" column. I have some indexes with
> zero
> "Reads" but, in a few cases, many thousands (and even millions) of "Buffer
> Reads".
>
> This doesn't make much sense to me if I assume "Buffer Reads" are a subset
> of
> "Reads", which I expect to be the sum of disk+buffer)....so I have to
> assume
> this logic isn't right.
>
> So, the question is, why are Buffer Reads > 0 when Reads are = 0, and are
> these really in use.
>
> This is 11.70.FC2.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f23450780def504d0c2b178
Not an easy subject... I'm not sure what you're calling "reads". What I see
is:
Partition profiles
partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt
seqsc rhitratio
0x100001 0 0 0 0 0 0 0 0 558 0
0 100
0x100002 12 0 0 0 3 0 0 0 15 1
0 87
0x100004 9 0 0 0 3 0 0 0 12 0
0 75
0x200001 0 0 0 0 0 0 0 0 0 0
0 0
0x300001 0 0 0 0 0 0 0 0 0 0
0 0
0x400001 0 0 0 0 0 0 0 0 0 0
0 0
Lock Requests # Lock Waits # Dead locks # Lock Time Outs # ISAM Reads #
ISAM Writes # ISAM ReWrites # ISAM Deletes # Buffer Reads # Buffer Writes #
Sequential Scans # Read hit ratio
So the "reads - ISAM Reads" have nothing to do with the page reads you're
considering.
By the way, don't try to map ISAM Read to "SELECTs", ISAM Writes to
"INSERTs", ISAM ReWrites to "UPDATEs" nor ISAM Deletes to "DELETES"
although a select will trigger ISAM reads and so on. But it's not a one to
one mapping between ISAM* and SQL operations or rows. This is a common
mistake.
The only thing you can consider is that more ISAM operations mean more work.
Getting back to the question of page reads vs buffer reads vs "reads" you
can try this query:
SELECT
$FIRST_CLAUSE
pf.dbsname, pf.tabname, pf.partnum, lower(hex(pf.partnum)),
pf.lockreqs, pf.lockwts, pf.deadlks, pf.lktouts,
pf.isreads, pf.iswrites, pf.isrewrites, pf.isdeletes,
pf.bufreads, pf.bufwrites,
pf.pagreads, pf.pagwrites,
pf.seqscans, t.tabname,
case
when pt.nrows = 0 and pt.nkeys != 0
then
"INDEX"
else
"PARTITION"
end
FROM
sysptprof pf, sysptnhdr pt, systabnames t
WHERE
(
pf.lockreqs != 0 OR pf.lockwts != 0 OR pf.deadlks != 0 OR
pf.lktouts != 0 OR
pf.isreads != 0 OR pf.iswrites != 0 OR pf.isrewrites != 0 OR
pf.isdeletes != 0 OR
pf.bufreads != 0 OR pf.bufwrites != 0 OR pf.pagreads != 0 OR
pf.pagwrites != 0 OR pf.seqscans != 0
) AND
pt.partnum = pf.partnum AND
t.partnum = pt.lockid
${DB_LIST_CLAUSE}
ORDER BY ${PARTITION_ORDER_BY} DESC
Sorry for the variables but I took it directly from a SHELL script.
The point is that it contains "pagreads", "pagwrites", "bufreads" and
"bufwrites". Hopefully these will match your points. And You can comparte
those values with the values from rhitratio given by "onstat -g ppf"
I may be missing something, but an index partition with many buf reads and
no ISAM reads is probably one that is not being used for SELECTs, but that
is heavily UPDATEd.. It would be interesting to see some partial outputs
Regards.
On Thu, Dec 13, 2012 at 9:14 PM, <> wrote:
> I'm looking at index usage and have a question about onstat -g ppf output.
>
> It has both a "Reads" and "Buffer Reads" column. I have some indexes with
> zero
> "Reads" but, in a few cases, many thousands (and even millions) of "Buffer
> Reads".
>
> This doesn't make much sense to me if I assume "Buffer Reads" are a subset
> of
> "Reads", which I expect to be the sum of disk+buffer)....so I have to
> assume
> this logic isn't right.
>
> So, the question is, why are Buffer Reads > 0 when Reads are = 0, and are
> these really in use.
>
> This is 11.70.FC2.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7bdc06f0ed0a1504d0c2d4e7
I'm calling a "Read" the column "isrd" which the documentation (at least the 11.1 doc...the 11.7 doc site is broken this week for some reason) specifically calls out as a "Read" but does not go into any further detail.
Art, that's entirely plausible. Is there somewhere that stores the stat reset date in the db?
Hmmm... the site is working: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/id s_adr_0559.htm If it just shows a blank page, try closing your browser. I have that problem often and couldn't understand it ... I suppose is has to do with cookies, but I'm not sure. Quote: isrd (decimal)The number of read operations for a partition Yes... not very detailed But "operations" are just internal calls to functions. On Thu, Dec 13, 2012 at 9:55 PM, <> wrote: > I'm calling a "Read" the column "isrd" which the documentation (at least > the > 11.1 doc...the 11.7 doc site is broken this week for some reason) > specifically > calls out as a "Read" but does not go into any further detail. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf302ef990573f3004d0c324af
DATABASE sysmaster;SELECT
DBINFO('utc_to_datetime',sh_boottime) boot_time,
DBINFO('utc_to_datetime',sh_pfclrtime) reset_time
FROM
sysshmvals
Regards
On Thu, Dec 13, 2012 at 9:57 PM, <> wrote:
> Art,
>
> that's entirely plausible. Is there somewhere that stores the stat reset
> date
> in the db?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf303b412149371004d0c33228
Thanks, that proves a reset isn't the culprit....
Neither the 11.5 or 11.7 sites are working on multiple machines with multiple browsers on different internet connections for me this week
....and now, it works just fine, but wasn't 5 minutes ago.
Interesting... what happens? Errors or blank pages? I have no problems accessing it... both directly and using a company connection that routes through another country. Regards. On Thu, Dec 13, 2012 at 10:14 PM, <> wrote: > Neither the 11.5 or 11.7 sites are working on multiple machines with > multiple > browsers on different internet connections for me this week > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00504501654a0a89aa04d0c35637
select sh_clrtime from sysmaster:sysshmvals;
It's a UNIX epoch, to see it as a datetime:
select dbinfo('utc_to_datetime', sh_clrtime) from sysmaster:sysshmvals;
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Dec 13, 2012 at 3:57 PM, <> wrote:
> Art,
>
> that's entirely plausible. Is there somewhere that stores the stat reset
> date
> in the db?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340befe8a7a404d0c35eb9
Weird... anyway...I like to keep the PDF set on my laptops... After 20
years I tend to know where the info is located... :)
But if I really need searching or the release/machine notes I prefer the
site.
As a side note, for onstat reference you can also look at
http://www.oninit.com (Reference -> onstat guide).
Many times it contains extra info, usually very useful.
Regards
On Thu, Dec 13, 2012 at 10:21 PM, <> wrote:
> .....and now, it works just fine, but wasn't 5 minutes ago.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--047d7bdc15c4a72bec04d0c36aa3
I also have the PDFs everywhere, even on my smartphone.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Dec 13, 2012 at 4:27 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> Weird... anyway...I like to keep the PDF set on my laptops... After 20
> years I tend to know where the info is located... :)
> But if I really need searching or the release/machine notes I prefer the
> site.
>
> As a side note, for onstat reference you can also look at
> http://www.oninit.com (Reference -> onstat guide).
> Many times it contains extra info, usually very useful.
>
> Regards
>
> On Thu, Dec 13, 2012 at 10:21 PM, <> wrote:
>
> > .....and now, it works just fine, but wasn't 5 minutes ago.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --047d7bdc15c4a72bec04d0c36aa3
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf30223999e30f8204d0c37afa
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g