MDA: monOpenObjectActivity – Handy Stats…

There are a lot of handy MDAs around in ASE.  This one is real fun.  It allows you have a sneak view into the guts of your ASE workload, on per-object resolution.

I’d like to share some insights and challenge you to fill the gaps.

First, let’s take a look at a sample data (15.7 SP134 – trimmed to counters I want to discuss):

Index ID Object Name Logical Reads Physical Reads APF Reads Pages Read Phys ical Writes Pag es Writt en Rows Insert ed Rows Delet ed Rows Updat ed Operations Lock Requests Lock Waits Opt Select Count Used Count SNAPID
0 TableA 4,796,925 1,045 365,957 8,164 0 0 0 0 0 0 58,245 0 0 0 32
2 pk_TableA 16,484,091 15,498 0 15,498 0 0 0 0 0 43,690,210 NULL NULL 1,048 4,150,507 32
3 ak2_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
4 ak3_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
5 ak4_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
6 ak1_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 5 0 32
7 ak5_TableA 7,842 28 5,201 224 0 0 0 0 0 1,189,633 NULL NULL 28 20 32
8 ak6_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
9 ak7_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
10 ak8_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 40 0 32
11 ak9_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
12 ak10_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
13 ak11_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 32
0 TableA 5,700,429 1,093 365,957 8,212 0 0 0 0 0 0 58,921 0 0 0 33
2 pk_TableA 18,696,548 15,533 0 15,533 0 0 0 0 0 1,069,470,126 NULL NULL 1,083 5,054,272 33
3 ak2_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
4 ak3_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
5 ak4_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
6 ak1_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 5 0 33
7 ak5_TableA 7,842 28 5,201 224 0 0 0 0 0 1,189,633 NULL NULL 28 20 33
8 ak6_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
9 ak7_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
10 ak8_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 40 0 33
11 ak9_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
12 ak10_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33
13 ak11_TableA 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 33

What can we see from these two consecutive snapshots of data?

TableA has been:

accessed about 1k times (UsedCount = Total number of DMLs + Scans on the Object).  Here it is probably Scans only, no DML.

access has been done by the optimizer ~50 times exclusively through the table primary key ( OptSelectCount = Object Selected for Plan by Optimizer)

these 1k accesses resulted in close to 1m total operations on the table (Operations = Object Accesses).

it has resulted in LogicalReads only – mostly retrieved through PK, with a small portion descending to data pages level (IxID 2 + 0).

pretty few lock requests (around 800).

So far so good.  So good?  Hm.  If the table has been accessed for ~1000 DMLs/SCANs – how can it be that optimizer selected it only 50 times?  Plan reuse for SPs?  Possible.  If the table has been accessed about 1k times, generating about 1k lock requests, what are those 1m operations that run against the table?  I found it confusing…  Any help?

What about another table:

Index ID Object Name Logical Reads Physical Reads APF Reads Pages Read Phys ical Writes Pages Written Rows Insert ed Rows Delet ed Rows Upda ted Operations Lock Requests Lock Waits Opt Select Count Used Count SNAP ID
0 TableB 4,665,058 120,691 0 120,697 0 0 0 0 0 1 143,554 0 0 0 42
2 ak9_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 42
3 ak1_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 42
4 ak2_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 42
5 ak3_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 42
6 ak4_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 42
7 ak7_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 2 0 42
8 ak5_TableB 17,050 8 14,877 64 0 0 0 0 0 1 NULL NULL 25 1 42
9 ak6_TableB 63 6 0 27 0 0 0 0 0 41 NULL NULL 88 21 42
10 ak8_TableB 7 5 0 19 0 0 0 0 0 6 NULL NULL 3 2 42
11 pk_TableB 14,134,395 6,931 0 6,931 0 0 0 0 0 12,770,294 NULL NULL 732 4,769,017 42
0 TableB 4,860,552 125,398 86,646 145,018 0 0 0 0 0 1 151,416 0 40 0 43
2 ak9_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 43
3 ak1_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 43
4 ak2_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 43
5 ak3_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 43
6 ak4_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 0 0 43
7 ak7_TableB 0 0 0 0 0 0 0 0 0 0 NULL NULL 2 0 43
8 ak5_TableB 31,514 294 33,884 2,002 0 0 0 0 0 184,874 NULL NULL 65 110 43
9 ak6_TableB 63 6 0 27 0 0 0 0 0 41 NULL NULL 88 21 43
10 ak8_TableB 7 5 0 19 0 0 0 0 0 6 NULL NULL 3 2 43
11 pk_TableB 14,448,611 9,663 0 9,663 0 0 0 0 0 12,884,606 NULL NULL 1,580 4,873,835 43

TableB has been:

accessed about 100k times (UsedCount = Total number of DMLs + Scans on the Object).  Here it is probably Scans only, no DML.

access has been done by the optimizer ~1k times through several table keys and possible table scans ( OptSelectCount = Object Selected for Plan by Optimizer)

these 100k accesses resulted in 300 k total operations on the table (Operations = Object Accesses).

it has resulted in LogicalReads & PhysicalReads – retrieved through PK and one of non-clustered indices (AKx), with a small portion descending to data pages level (IxID 8, 11 + 0).

pretty few lock requests (around 8k).

These values are more easy to interpret:

The table has been accessed in read operations, mostly through its indices, but also table scanned (OptSelect>0, indexID=0,APF>0).

Still, Operations vs UsedCount is moot and APFReads vs PagesRead is moot.  Any explanation?

Index ID Object Name Logical Reads Physical Reads APF Reads Pages Read Physical Writes Pages Written Rows Inserted Rows Deleted Rows Updated Operations Lock Requests Lock Waits Opt Select Count Used Count SNAP ID
0 TableC 96,091 40 0 40 287 287 446 0 446 892 124,515 0 380 445 36
2 pk_TableC 4,495 2 0 2 25 25 446 0 0 445 NULL NULL 16 16 36
3 ak1_TableC 3,708 2 0 2 45 45 446 0 0 0 NULL NULL 20 0 36
4 ak2_TableC 6,303 2 0 2 42 42 446 0 0 2,992 NULL NULL 84 40 36
5 ak3_TableC 4,562 2 0 2 50 50 446 0 0 1,706 NULL NULL 32 16 36
6 ak4_TableC 3,652 2 0 2 46 46 446 0 0 0 NULL NULL 2 0 36
0 TableC 3,364,491 60 998,295 60 186,493 186,493 1,147,948 0 248,706 249,355 2,611,593 15 1,180 634 37
2 pk_TableC 4,366,690 4 0 4 10,215 10,215 1,147,952 0 0 248,080 NULL NULL 63 62 37
3 ak1_TableC 4,673,030 4 0 4 33,994 33,994 1,147,841 0 0 0 NULL NULL 44 0 37
4 ak2_TableC 4,377,284 4 0 4 14,814 14,814 1,147,588 0 0 37,882 NULL NULL 176 81 37
5 ak3_TableC 4,564,749 4 0 4 15,828 15,828 1,147,436 0 0 391,707 NULL NULL 119 63 37
6 ak4_TableC 3,800,181 4 0 4 13,806 13,806 1,147,045 0 0 0 NULL NULL 10 0 37

TableC has been:

accessed about 200 times by DMLs (update + insert, may be some scans too) (UsedCount = Total number of DMLs + Scans on the Object). 

access has been done by the optimizer ~1k times involving all table keys and table data ( OptSelectCount = Object Selected for Plan by Optimizer)

these DMLs resulted in 1m total operations on the table – distributed across various table keys and data pages (Operations = Object Accesses).

it has resulted in inserts/updates across all of the indices, generated locks and lock contention.

Here values are too tempting.  Again, probably some table scans resulting in physical io to get to the table – may be as a part of update dml itself.  DML resulting in each table index being updated.  Operations for the same access (DML) multiplied by the changes to each index (makes sense).  But – OptSelectCount higher than UsedCount?  For SP/LWP call, UsedCount may be higher than OptSelectCount (plan reuse).  When it is vice versa?  1K APF Reads with puny 30 PagesRead?  Confusing again.

Now, it is true that the table may be used to get a general idea as to which object is used in what way in your ASE – if there are table scans, physcial read, excessive logical reads.  To create a object heat map for the server, so to say.  But the data in the table on close inspection becomes very confusing.

I’ve done a test (on ASE 16 SP01) to see if going slowly rather than plunging into a busy ASE makes it easier to understand what’s going on in this table.

Here is the test:

TS ObjectName IndexID Used Count Scans Updates Inserts Deletes Operations Rows Inserted Rows Deleted Rows Updated Lock Requests Opt Select Count Operation
12:39:04 MDA_TESTS2 0 0 0 0 0 0 0 0 0 0 6 0 create table (pk = unique clustered on a
12:39:04 MDA_TESTS2_pk 1 0 0 0 0 0 0 0 0 0 NULL 0 NULL
12:39:04 MDA_TESTS2_ak 2 0 0 0 0 0 0 0 0 0 NULL 0 NULL
12:39:56 MDA_TESTS2 0 1000 0 0 1000 0 1000 1000 0 0 2043 0 insert MDA_TESTS2 (b
12:39:56 MDA_TESTS2_pk 1 0 0 0 0 0 1000 1000 0 0 NULL 0 NULL
12:39:56 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:40:42 MDA_TESTS2 0 1000 0 0 1000 0 1001 1000 0 0 2049 0 select count(*) from MDA_TESTS2 -> showplan = Table Scan.
12:40:42 MDA_TESTS2_pk 1 1 1 0 0 0 1001 1000 0 0 NULL 1 NULL
12:40:42 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:41:49 MDA_TESTS2 0 1000 0 0 1000 0 1002 1000 0 0 2054 0 select * from MDA_TESTS2 -> showplan = Table Scan.
12:41:49 MDA_TESTS2_pk 1 2 2 0 0 0 1002 1000 0 0 NULL 2 NULL
12:41:49 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:43:51 MDA_TESTS2 0 1000 0 0 1000 0 1002 1000 0 0 2054 0 NULL
12:43:51 MDA_TESTS2_pk 1 2 2 0 0 0 1002 1000 0 0 NULL 2 NULL
12:43:51 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:44:39 MDA_TESTS2 0 1000 0 0 1000 0 1002 1000 0 0 2054 0 NULL
12:44:39 MDA_TESTS2_pk 1 2 2 0 0 0 1002 1000 0 0 NULL 2 NULL
12:44:39 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:44:51 MDA_TESTS2 0 1001 0 1 1000 0 1004 1000 0 99 2352 0 update MDA_TESTS1 set b = 2 where a< 100 – showplan pk
12:44:51 MDA_TESTS2_pk 1 3 3 0 0 0 1004 1000 0 99 NULL 3 NULL
12:44:51 MDA_TESTS2_ak 2 0 0 0 0 0 0 1000 0 0 NULL 0 NULL
12:46:09 MDA_TESTS2 0 1002 0 2 1000 0 1005 1000 0 198 2454 0 update MDA_TESTS2 set c = 3 where b = 2 – showplan ak
12:46:09 MDA_TESTS2_pk 1 3 3 0 0 0 1005 1000 0 198 NULL 3 NULL
12:46:09 MDA_TESTS2_ak 2 1 1 0 0 0 1 1000 0 0 NULL 1 NULL
12:48:09 MDA_TESTS2 0 1003 0 2 1000 1 1007 1000 11 198 2477 0 delete MDA_TESTS2 where a between 10 and 20 – showplan pk
12:48:09 MDA_TESTS2_pk 1 4 4 0 0 0 1007 1000 11 198 NULL 4 NULL
12:48:09 MDA_TESTS2_ak 2 1 1 0 0 0 1 1000 11 0 NULL 1 NULL
12:51:50 MDA_TESTS2 0 1004 0 3 1000 1 1009 1000 11 1099 5222 0 update MDA_TESTS2 set b = 4 where c = 1 – showplan Table Scan
12:51:50 MDA_TESTS2_pk 1 5 5 0 0 0 1009 1000 11 1099 NULL 5 NULL
12:51:50 MDA_TESTS2_ak 2 1 1 0 0 0 1 1000 11 0 NULL 1 NULL
12:56:20 MDA_TESTS2 0 1005 0 4 1000 1 1011 1000 11 1100 5227 0 update MDA_TESTS2 set b = 4 where a = 1 – via pk
12:56:20 MDA_TESTS2_pk 1 6 6 0 0 0 1011 1000 11 1100 NULL 6 NULL
12:56:20 MDA_TESTS2_ak 2 1 1 0 0 0 1 1000 11 0 NULL 1 NULL
12:57:01 MDA_TESTS2 0 1006 0 5 1000 1 1012 1000 11 1100 5229 0 update MDA_TESTS2 set c = 4 where b = 1 – via ak
12:57:01 MDA_TESTS2_pk 1 6 6 0 0 0 1012 1000 11 1100 NULL 6 NULL
12:57:01 MDA_TESTS2_ak 2 2 2 0 0 0 2 1000 11 0 NULL 2 NULL
13:19:19 MDA_TESTS2 0 1007 0 6 1000 1 1013 1000 11 1187 5318 0 update MDA_TESTS2 set c = 4 where b = 2 – via ak
13:19:19 MDA_TESTS2_pk 1 6 6 0 0 0 1013 1000 11 1187 NULL 6 NULL
13:19:19 MDA_TESTS2_ak 2 3 3 0 0 0 3 1000 11 0 NULL 3 NULL
15:00:43 MDA_TESTS2 0 1007 0 6 1000 1 1013 1000 11 1187 5318 0 create proc SP_MDA_TESTS2_SELECT @a int
15:00:43 MDA_TESTS2_pk 1 6 6 0 0 0 1013 1000 11 1187 NULL 6 NULL
15:00:43 MDA_TESTS2_ak 2 3 3 0 0 0 3 1000 11 0 NULL 3 NULL
15:01:18 MDA_TESTS2 0 1007 0 6 1000 1 1015 1000 11 1187 5321 0 exec SP_MDA_TESTS2_SELECT @a = 10
15:01:18 MDA_TESTS2_pk 1 8 8 0 0 0 1015 1000 11 1187 NULL 8 NULL
15:01:18 MDA_TESTS2_ak 2 4 4 0 0 0 4 1000 11 0 NULL 4 NULL
15:02:33 MDA_TESTS2 0 1008 0 6 1000 2 1017 1000 811 1187 6948 0 delete MDA_TESTS2 where a > 200
15:02:33 MDA_TESTS2_pk 1 9 9 0 0 0 1017 1000 811 1187 NULL 9 NULL
15:02:33 MDA_TESTS2_ak 2 4 4 0 0 0 4 1000 811 0 NULL 4 NULL
15:03:28 MDA_TESTS2 0 1009 0 7 1000 2 1019 1000 811 1187 6950 0 SP_MDA_TESTS2_UPDATE @a = 10
15:03:28 MDA_TESTS2_pk 1 10 10 0 0 0 1019 1000 811 1187 NULL 10 NULL
15:03:28 MDA_TESTS2_ak 2 4 4 0 0 0 4 1000 811 0 NULL 4 NULL
15:03:43 MDA_TESTS2 0 1010 0 8 1000 2 1021 1000 811 1187 6951 0 SP_MDA_TESTS2_UPDATE @a = 10
15:03:43 MDA_TESTS2_pk 1 11 11 0 0 0 1021 1000 811 1187 NULL 10 NULL
15:03:43 MDA_TESTS2_ak 2 4 4 0 0 0 4 1000 811 0 NULL 4 NULL
15:04:01 MDA_TESTS2 0 1011 0 9 1000 2 1023 1000 811 1187 6952 0 SP_MDA_TESTS2_UPDATE @a = 10
15:04:01 MDA_TESTS2_pk 1 12 12 0 0 0 1023 1000 811 1187 NULL 10 NULL
15:04:01 MDA_TESTS2_ak 2 4 4 0 0 0 4 1000 811 0 NULL 4 NULL
15:04:32 MDA_TESTS2 0 1011 0 9 1000 2 1026 1000 811 1187 6958 0 SP_MDA_TESTS2_SELECT @a = 10
15:04:32 MDA_TESTS2_pk 1 15 15 0 0 0 1026 1000 811 1187 NULL 10 NULL
15:04:32 MDA_TESTS2_ak 2 6 6 0 0 0 6 1000 811 0 NULL 4 NULL
15:05:11 MDA_TESTS2 0 1011 0 9 1000 2 1028 1000 811 1187 6962 0 SP_MDA_TESTS2_SELECT @a = 10
15:05:11 MDA_TESTS2_pk 1 17 17 0 0 0 1028 1000 811 1187 NULL 10 NULL
15:05:11 MDA_TESTS2_ak 2 7 7 0 0 0 7 1000 811 0 NULL 4 NULL
15:05:28 MDA_TESTS2 0 1011 0 9 1000 2 1029 1000 811 1187 6964 0 SP_MDA_TESTS2_SELECT @a = 10
15:05:28 MDA_TESTS2_pk 1 18 18 0 0 0 1029 1000 811 1187 NULL 10 NULL
15:05:28 MDA_TESTS2_ak 2 8 8 0 0 0 8 1000 811 0 NULL 4 NULL

I won’t walk through all steps, but you may easily see that here (ASE16) too are things that start to confuse:

Operations on PK and DATA seem to be repeated (non found in real life – not  in 15.7)

OptSelectCount stays zero for operations that result in table scan (based on showplan).

Insert operation populating the table which definitely affect all its indices does not change the Operation counter for all.

Did anyone tried to read this table in detail?  How safe-proof are the assumption made base on the data displayed in it?  I wish SAP would produce more documentation on MDAs.  Numbers in it are really cute…

Would appreciate some insights.

Thank you,

Andrew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: