Monitoring Index usage

[Former Member]

Hi,

I tried to execute the index usage monitoring based on SAP note 912620 - FAQ: Oracle indexes.

However, I'm not sure how to interpret the result.

 

SELECT SUBSTR(OBJECT_NAME, 1, 40) SEGMENT_NAME,SUBSTR(STATISTIC_NAME, 1, 20) STATISTIC_NAME,VALUE "NUMBER"
FROM V$SEGMENT_STATISTICS
WHERE OBJECT_NAME LIKE 'MSEG%' AND STATISTIC_NAME IN ('logical reads', 'physical reads') 
order by value;


SEGMENT_NAME	STATISTIC_NAME NUMBER
--------------------------------------------
MSEG~S		        physicalreads	619
MSEG~R		        physicalreads	675
MSEG~Z4		physicalreads	1140
MSEG~Z1		physicalreads	17080
MSEG~0		        physicalreads	27606
MSEG~Z3		physicalreads	38665
MSEG~R		        logicalreads	47600
MSEG~Z4		logicalreads	48960
MSEG~Z1		logicalreads	63696
MSEG~S		        logicalreads	66080
MSEG~Z2		physicalreads	96416
MSEG~M		        physicalreads	155965
MSEG~Z3		logicalreads	247152
MSEG~M		        logicalreads	393104
MSEG~0		        logicalreads	643392
MSEG		        physicalreads	22369182
MSEG		        logicalreads	23181664
MSEG~Z2		logicalreads	37081296

Can I know what the result means?

BR,

Eida

[fidel_vales]

Hi,

you are checking the view "V$SEGMENT_STATISTICS"

This view has noting to do with index monitoring. For it you will need to query "V$OBJECT_USAGE".

So, the first thing to know would be what is what you want to achieve

Note that index monitoring has a bug (6798910) and the fix is provided with the SBP from October.You should install it if you want to use Index Monitoring.

Regarding your query, it indicates how many "buffer gets" and "disk reads" were done for each of the objects since the database was started.

For example, 619 blocks were read from disk that belong to MSEG~S and 66080 blocks were read from the cache for the same index.

[Former Member]

Fidel Vales,

Thanks for the reply.

Can I conclude that only index MSEG~Z2 has been used regularly?

Also from the list earlier, most of the query is accessing directly from table and not from the index. Is this statement is true based on the output of the query?

BR,

Eida

[lbreddemann]

> Can I conclude that only index MSEG~Z2 has been used regularly?

No you cannot conclude that.

And even more important, you cannot know, whether or not the index is important to any of the SQL queries.

> Also from the list earlier, most of the query is accessing directly from table and not from the index. Is this statement is true based on the output of the query?

No, again!

The data in V$SEGMENT_STATISTICS is reset when the instance gets restarted.

One possible approach for figuring out, whether the index may be irrelevant and could be dropped without impacting the system performance is to use the "Space_PotentiallyUnnecessaryIndexes" script from SAP Note 1438410 SQL: Script collection for Oracle.

The idea behind this script is to check

- whether or not blocks of this index are present in the buffer cache,

- whether the indexed columns are a subset of other indexes,

- whether there are execution plans in the cache that include operations on this index and

- to check whether the index segment had been used at all (this is only available, when index usage monitoring had been enabled before ....)

Hope this is what you wanted to figure out...

regards,

Lars

[Former Member]

Hi Fidel,

Now I'm trying to use the index monitoring as per your recommendation.

ALTER INDEX "SAPPDF"."MSEG~Z1" MONITORING USAGE;

However, when I view the record (v$object_usage), I got an empty rows.

SQL> SELECT table_name,

2 index_name,

3 used,

4 start_monitoring,

5 end_monitoring

6 FROM v$object_usage;

no rows selected

Questions?

- Why is it showing an empty record? I've google and find out that I need to activate the oracle job (GATHER_STATS_JOB). Can anyone confirm this?

- Recommendation from SAP is to disable this internal job during Oracle 10g upgrade. Can I know if there is any impact on performance if I enable the job?

Thanks in advance.

Eida

[Former Member]

Hi Lars,

I have executed the query you'd mentioned. Can I know how can I interpret the result:

OWNER                          TABLE_NAME                     INDEX_NAME                     SIZE_MB     P O S B                                                                            
------------------------------ ------------------------------ ------------------------------ ----------- - - - -                                                                            
30.11.2010                     10:18:57                                                                                
SAPPDF                         GLPCA                          GLPCA~1                           16917.00     X                                                                              
SAPPDF                         GLPCA                          GLPCA~7                           14152.00     X                                                                              
SAPPDF                         COEP                           COEP~Z01                           7536.00     X                                                                              
SAPPDF                         VBOX                           VBOX~A                             6476.00     X X                                                                            
SAPPDF                         J_1GVL_ML                      J_1GVL_ML~VAB                      4178.00     X                                                                              
SAPPDF                         J_1GVL_ML                      J_1GVL_ML~Z02                      3552.00     X                                                                              
SAPPDF                         CE1FAPG                        CE1FAPG~3                          3482.00     X                                                                              
SAPPDF                         CE1FAPG                        CE1FAPG~4                          3394.00     X                                                                              
SAPPDF                         J_1GVL_ML                      J_1GVL_ML~Z01                      333

Thanks in advance

Eida

[lbreddemann]

Hi there,

did you already use the index actually?

If not, then there won't be any entries in the table.

Also, you don't need to run the Oracle internal statistics jobs to make the entries visible in the V$OBJECT_USAGE.

The standard brconnect run does the trick as well.

regards,

Lars

[lbreddemann]

> I have executed the query you'd mentioned. Can I know how can I interpret the result:

Sure you can.

Why don't you read the the script?

The last four columns (containing either X or nothing) represent the four different criteria that are used to indicate that an index maybe can be omitted without impacting system performance.

The columns are (in this order):

- PREFIX, meaning, there is another index containing the same columns at the start of it's column list. A range scan could then also be done via the other index.

- OBJECT_USAGE, meaning, there is an entry for this index in V$OBJECT_USAGE

- SQL_PLANS, meaning there are execution plans in V$SQL_PLAN that contain the index

- BUFFER_POOL, meaning that there are blocks of the index found in the buffer pool

Based on these indications you can review the indexes one by one and decide whether or not to drop them.

If the indexes are standard indexes and you never came across an issue with them (e.g. you never heard of them at all): leave them as they are.

If the indexes are customre indexes (~Z...) and you don't know them, make sure to figure out who created them and what for.

regards,

Lars

[Former Member]

Hi Lars,

The index for MKPF~Z1 is with the following field

MANDT

VGART

BLART

CPUDT

XBLNR

BKTXT

TCODE2

Below is the steps that I executed from SQL:

ALTER INDEX "SAPPDF"."MKPF~Z1" MONITORING USAGE;

ALTER INDEX "SAPPDF"."MKPF~0" MONITORING USAGE;

ALTER INDEX "SAPPDF"."MKPF~BUD" MONITORING USAGE;

SQL> select *

2 from SAPPDF.MKPF

3 where

4 MANDT='550'and

5 VGART='WA' and

6 BLART='WA' and

7 CPUDT='31.12.2010' and

8 XBLNR=' ' and

9 BKTXT=' ' and

10 TCODE2='MB11';

no rows selected

SQL> select * from V$object_usage ;

no rows selected

Regards,

Eida

[lbreddemann]

So what?

Have the indexes been used for that query?

Have you checked the execution plan for it?

And have you run the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure afterwards (which is done automatically when you gather statistics with brconnect)?

regards,

Lars

[Former Member]

Finally, I know the reason the v$object_usage is empty. This was because I use sysdba to login to Oracle.

The fix is to login as owner of the table which you have activated.

[lbreddemann]

Dang!

You're right - and it is even documented (e.g. check note #912620 - FAQ: Oracle indexes).

If I'm not totally mistaken it was in fact me who asked Martin back then to include this...

Anyway, working as SYSDBA is as common as it is bad practise.

Nevertheless there exist workarounds for this, e.g. this one http://www.oracloid.com/2006/05/vobject_usage-empty/ .

I'd really be interested to learn about how much you gained by putting in all this effort into finding unused indexes.

My gut-feeling for this would be: not too much and maybe you'd be better off by just using 11g features like compression and deferred segment creation.

regards,

Lars