Here's what they look like for a 16.0.0.2052 database busy doing SELECT and UPDATE operations, no INSERTs...

SELECT DB_PROPERTY ( 'IndAdd' ), DB_PROPERTY ( 'IndLookup' );

DB_PROPERTY('IndAdd'),DB_PROPERTY('IndLookup')
'401941','150'

SELECT Number, PropName, Value 
  FROM sa_conn_properties() 
 WHERE PropName IN ( 'IndAdd', 'IndLookup' )
 ORDER BY Number, PropName;

Number,PropName,Value
3,'IndAdd',80
3,'IndLookup',0
4,'IndAdd',9990
4,'IndLookup',0
5,'IndAdd',9990
5,'IndLookup',0
6,'IndAdd',9990
6,'IndLookup',0
7,'IndAdd',9990
7,'IndLookup',0
8,'IndAdd',9990
8,'IndLookup',0
9,'IndAdd',9990
9,'IndLookup',0
10,'IndAdd',9990
10,'IndLookup',0
...
CREATE TABLE DBA.inventory ( -- 1,000,000 rows, 28M total = 27M table + 40k ext + 960k index, 30 bytes per row
   item_id         /* PK        */ INTEGER NOT NULL DEFAULT autoincrement,
   item_count                      INTEGER NOT NULL,
   item_name                       VARCHAR ( 1024 ) NOT NULL,
   updated_at                      TIMESTAMP NOT NULL DEFAULT timestamp,
   last_modified   /*         X */ TIMESTAMP NOT NULL DEFAULT timestamp,
   CONSTRAINT ASA78 PRIMARY KEY ( -- 356k
      item_id )
 );
-- Parents of DBA.inventory
-- none --
-- Children
-- none --
CREATE INDEX inventory_ml ON DBA.inventory ( -- 604k
   last_modified );
  DO WHILE lb_continuing

   ll_pkey = Rand ( 100 ) * 10000 + il_thread_index;

   ls_sql = "UPDATE inventory SET item_count = item_count + 1 WHERE item_id = " + String ( ll_pkey )

   EXECUTE IMMEDIATE :ls_sql USING itr_sqlca;

   IF SQLCA.SQLCODE <> 0 THEN

    UPDATE thread_status 
     SET thread_status.status = 'error',
       SQLDBCode = :SQLCA.SQLDBCode,
       SQLErrText = :SQLCA.SQLErrText
     WHERE thread_status.thread_index = :il_thread_index
     USING itr_sqlca;

    COMMIT USING itr_sqlca;

    lb_continuing = FALSE

   END IF   

asked 18 Mar '15, 16:24

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826
accept rate: 20%

edited 19 Mar '15, 13:09

I am seeing index lookups and adds getting counted in versions 12.0.1 and 16 when I query GroupO.Products in the sample databases that are shipped with each version. And they seem to be doing so appropriately for me.

Index lookups can fail to count depending upon what plans are getting executed ... is that a possibility in your case? [table scans for everyone ... anyone?] FWIW I can suggest that your updates are likely not adding to the lookup count much if that is your expectation; I can run inserts and updates here without any.

It may help if we know the schemas and queries you are counting here.

(19 Mar '15, 11:31) Nick Elson S...
Replies hidden

The application is an artificial benchmark which is executing no (0, zero, nada) INSERT operations, just UPDATE. All the connections are doing the same thing so the counts are all the same: IndAdd 9990. The "9990" makes sense, but "IndAdd" does not... how do UPDATE operations contribute to IndAdd?

The UPDATE operations all use the primary key, so IndLookup 0 also makes no sense... IndLookup 9990 would make sense.

See the question for the schema and a snippet of code that is heavily executed.

(19 Mar '15, 13:04) Breck Carter

...and now I think I see it.

Somehow a stupid extra redundant bogus last_modified column with DEFAULT TIMESTAMP and an index has been added to the table... which means every steenking UPDATE is going to delete and create an index entry.

Does that make sense? The effect, not the existence, of the column with index.


After dropping the index (not the column) the rate of IndAdd operations dropped from over 16,000 per second down to about 100 per second.

...the moral of the story being, unexpectedly large IndAdd values may indicate UPDATE operations that change indexed columns, something that may not be desirable from a performance point of view.

(19 Mar '15, 13:14) Breck Carter

That's pretty consistent with my tests too.

As to "...how do UPDATE operations contribute to IndAdd?" they should do so only if the update changed a value in an indexed column. Of course trigger actions (,etc) could also be contributing.

If you are contributing to the IndAdd counter that way you don't also contribute to the IndLookup. That seems to be the current implementation. {I haven't found a case where any insert/update or delete contributes to that counter and the code seems to support that partitioning.}

It might make more sense if the intent of these counters were better documented. I take the Add to be a measure of index maintenance and the Lookup to be mostly for aiding in the analysis of index utilization for query performance purposes; which would map to a measure of cost vs benefit from indexing.

(19 Mar '15, 14:45) Nick Elson S...
Replies hidden

If memory serves, both numbers are almost always vanishingly small even under heavy workloads, EXCEPT in unexplained cases where IndAdd is astonishingly high.

Thinking back, it is quite possible all of those unexplained cases could have been UPDATE statements that affect indexed columns, something one might want to avoid if one is interested in Maybach-style performance.

alt text

FWIW the following has been added to the Foxhound Help for "Index Adds": "Large values may be caused by updates that are changing index columns."

(19 Mar '15, 14:54) Breck Carter

Just to clarify: So "IndAdd" would also count dropped entries? Would an UPDATE of an index entry count twice?

Your take seems to make a good enhancement to the docs, particularly as that refers to the usage ("What does it tell?") and not only the technical source.

(20 Mar '15, 05:28) Volker Barth

It would be nice to have better descriptions of many performance-related statistics. In the meantime, yes, since there is no "IndDel" statistic it may be safe to assume that "IndAdd" really means "index modification"... said the blindfolded man about the elephant :)

(20 Mar '15, 08:08) Breck Carter
showing 2 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×20
×7

question asked: 18 Mar '15, 16:24

question was seen: 2,249 times

last updated: 20 Mar '15, 08:08