How to determine whether index is actually being used? / Drop PRI INDX ??

[former_member69568]

Hi... Experts....

We see in our system Table FAGLFLEXA is havind 5 INDEXES and have huge size around 20 GB+.

We would like wether they are getting used or not. if they are not getting used, we would like to drop indexes which are not used.

Requirement -1

How to identify the INDEXES are getting used or not ?

SEGMENT SEG TYPE SIZE

FAGLFLEXA~0 INDEX 26.24 in GB

FAGLFLEXA~2 INDEX 22.61 in GB

FAGLFLEXA~3 INDEX 23.30 in GB

FAGLFLEXA~4 INDEX 19.82 in GB

FAGLFLEXA~6 INDEX 19.29 in GB

Requirement - 2 :

For space constraint reasons we want to gain space in Database

Concern for Requirement -2 :

Do you (experts) suggest dropping PRIMARY INDEXES in SAP System if they are not getting used ?

Rgds

[former_member69568]

Requirement -1 is fixed

SAP Note 912620 - FAQ Oracle indexes (((Point / Question Numner 14)))

connect to DB (SQL Prompt) as SCHEMA user PSAP<SCHEMA ID> ( Ex : PSAPSR3).

TO ENABLE MONITORING ON INDEXES

SQL>ALTER INDEX "FAGLFLEXA~0" MONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~2" MONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~3" MONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~4" MONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~6" MONITORING USAGE;

>>> AFTER FEW BUSINESS HOURS (or) BUSINESS DAY <<<

connect to DB (SQL Prompt) as SCHEMA user PSAP<SCHEMA ID> ( Ex : PSAPSR3).

TO KNOW  WHETHER USED OR  NOT 

SQL> select * from V$OBJECT_USAGE;

TO DISABLE MONITORING ON INDEXES

SQL>ALTER INDEX "FAGLFLEXA~0" NOMONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~2" NOMONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~3" NOMONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~4" NOMONITORING USAGE;

SQL>ALTER INDEX "FAGLFLEXA~6" NOMONITORING USAGE;

-

---

EXPERTS PL HELP / SUGGESSIONS ON MY REQUIREMENT 2

Requirement - 2 :

For space constraint reasons we want to gain space in Database

Concern for Requirement -2 :

Do you (experts) suggest dropping PRIMARY INDEXES in SAP System if they are not getting used ?

Regards

[stefan_koehler]

Hello,

> Do you (experts) suggest dropping PRIMARY INDEXES in SAP System if they are not getting used ?

No, never drop primary indexes. Now you maybe ask you why?

So just let's take a closer look how a primary key defined in the SAP DDIC is implemented on oracle database level.

I will just take the primary key index of the table MSEG in our system

SQL> select index_name, index_type, UNIQUENESS from dba_indexes where table_name = 'MSEG' order by index_name;
INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
MSEG~0                         NORMAL                      UNIQUE
....
....

SQL> select constraint_name, constraint_type, search_condition from dba_constraints where table_name = 'MSEG';
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------------------------------------------------------------------
SYS_C00258815                  C "MANDT" IS NOT NULL
SYS_C00258816                  C "MBLNR" IS NOT NULL
SYS_C00258817                  C "MJAHR" IS NOT NULL
...
...

So now let's check the definition of a unique and primary key:

Unique Key
Column(s) value(s) must be unique in table or null (see note below)
Primary Key
UK + Not Null which equates to every column in the key must have a value and this value is unique so the PK uniquely identifies each and every row in the table

So as you can see SAP is using an unique index and NOT NULL constraints on database level to guarantee the uniqueness of every value combination for the defined columns. Until now i haven't found the reason why SAP isn't just using a primary key constraint, but this is another topic.

So if you delete the primary key index you have no guarantee that the value combination of the corresponding columns is unique (which is really necessary in some business cases).

Regards

Stefan

[former_member69568]

>>> STEFAN ..................T H A N K---Y O U- V E R Y--- M U C H

Last Doubt // In this corner can we dare to delete SAP Delivered "NONUNIQUE" Indexes,((if we discover they are not getting used.)

Do they fall under SPDD when we do next Support Package Import ??

Rgds

[stefan_koehler]

Hello,

> In this corner can we dare to delete SAP Delivered "NONUNIQUE" Indexes,((if we discover they are not getting used.)

Just delete it on the database if you want to, but to be honest i wouldn't do that. The SAP delivered indexes have their reason in some business cases.

> Do they fall under SPDD when we do next Support Package Import ??

If you don't modify the SAP DDIC object.. no. You just will get some entries by the database check, but this has nothing to do with the SPDD.

Regards

Stefan

[lbreddemann]

>

> So as you can see SAP is using an unique index and NOT NULL constraints on database level to guarantee the uniqueness of every value combination for the defined columns. Until now i haven't found the reason why SAP isn't just using a primary key constraint, but this is another topic.

Hi Stefan,

in earlier Oracle releases there had been problems with primary key indexes.

Also the feature to create a primary key constrained with a chosen index name had been around only since 8.0.4 (or so).

Therefore it had been decided to leave out the primary key constraint definition on Oracle (e.g. on MaxDB it is used).

Since the primary key constraint does only provide an additional meaning to the data model but no changed functionality (ok - let's skip DBMS_REDEFINITION for now...), it does no harm to the usability of the database.

best regards,

Lars

[Former Member]

Hi Lars,

well, Oracle 8.0.4 was in the last century - in the meantime they improved this a little bit

But I guess it's the same reason as ever:

• keep most of the data validation in the application server and outside of the database (SAP will be the only application that will access the tables - fingers crossed !)

• Hide information as much as you can - we don't need those sophisticated optimizers taken into accout such boring semantic information like PK's!)

• This database is a black box - why care about it?

bye

yk

[lbreddemann]

> well, Oracle 8.0.4 was in the last century - in the meantime they improved this a little bit

So what?

Should SAP redesign the DDIC-DB mapping everytime a bug is fixed?

And what would have been the advantage of this for the SAP users?

> But I guess it's the same reason as ever:

>

> * keep most of the data validation in the application server and outside of the database (SAP will be the only application that will access the tables - fingers crossed !)

We don't need to cross fingers.

For SAP customers it's simply not supported to access the data directly via DB access.

And to be able to map a business transaction to several database transactions you need to defer database access. This would mean defered constraint checking on db level - something you won't want in your database.

> * Hide information as much as you can - we don't need those sophisticated optimizers taken into accout such boring semantic information like PK's!)

Ok, show us the super clever use of primary key constraints (or other more business like constraints) that Oracle currently allows.

There are no.

The combination of unique constraint/unique index and NOT NULL constraint is handled equally as the primary key constraint by the optimizer.

> * This database is a black box - why care about it?

Sorry, but that's pretty much nonsense.

SAP uses each database platform intensively to make the best out of it, without taking the option to change to another platform from the customer.

Does this mean that we support every single feature there is for our products?

Of couse not.

All of the features that the DBMS offer are checked and evaluated by SAP together with Oracle.

And many of the Oracle features are turned down or changed because of the Oracle-guys at SAP.

One example is the optimizer parameter optimize_index_cost_adj which had been introduced for SAP to enable the actual use of the CBO in OLTP setups.

Another good example is the 'evolution' of the parameter recommendations note for Oracle 10g.

When you check the earlier versions of it, you'll find many features enabled or left to default.

With the experience of the many SAP installations it occured that these defaults need to be changed and other features weren't so mature.

It's true, SAP follows a platform strategy that is not fixed to a single DBMS (as Oracle naturally wants it).

But this is something very different from beeing database agnostic.

regards,

Lars

[Former Member]

        Should SAP redesign the DDIC-DB mapping everytime a bug is fixed? 

not every time - at least once per century.

(you already adimit that using a PK in online redefinition is mandatory for performance or in special cases where ROWID method

can't be used). Since (most) but not all tables have a unique key with NOT NULLs this is applicable.

When you create it in online redefinition why not in the DB itself?)

       We don't need to cross fingers.
       For SAP customers it's simply not supported to access the data directly via DB access.      

reason is: non existing database constraints. When I know my custom data model why do you block someone from using it from outside and to enforce constraints on DB level ?

   And to be able to map a business transaction to several database transactions you need to defer database access. This would mean defered constraint checking on db level - something you won't want in your database.      

deferred PK means it's supported by an non-unique index: do you have non-unique indexes in place to allow for deferred PK's? I guess, not. You allow for duplicates temporarily- I guess you can't due to the unique index/ not null.

Why they introduced the deferring option for PKs? For special cases i.e. refreshing snapshots it makes sense to defer a PK constraint.

      Ok, show us the super clever use of primary key constraints (or other more business like constraints) that Oracle currently allows.       
       There are no. 
       The combination of unique constraint/unique index and NOT NULL constraint is handled equally as the primary key constraint by the optimizer.      

here we go: they add information about the data into the data dictionary used in query rewrites (correlated queries, MV's, PK/FK joins ). It engraves " I'm the key you pointing back your foreign key, I'm immutable, use me to select 1 row" inside the DB.

Optimizers had evolved and will so in the future - not in a way taken less BUT more information to generate good access plans.

I can understand that you can't know the business rules when you roll out the SAP app to a customer - so you

will leave out all the FK constraints. For the PK -you have the supporting unique indexes.

For own SAP custom applications WE know the business rules and may want to enforce a PK constraint

(oops, it may that we use the table from outside...)

bye

yk

[lbreddemann]

> When you create it in online redefinition why not in the DB itself?)

The BRTOOLS create the PK constraint automatically - so there is no disadvantage for the user here.

On the other hand such a change would not deliver any additional value for SAP users.

> For SAP customers it's simply not supported to access the data directly via DB access.

> reason is: non existing database constraints. When I know my custom data model why do you block someone from using it from outside and to enforce constraints on DB level ?

Nope - the reasons why this is forbidden are:

- Oracle(!) license agreements concerning how SAP customers are allowed to use the DB when they purchased the license from SAP

- Complience regulations that forbid every unautherized data access/change

- the fact that the whole, quite complex permission management is done on the application layer

- the fact that separate business units, businesses etc. are partitioned via the client key and that the access is regulated by the application layer

SAP is a complex application and a data access API is necessary for

that.

When you by-pass its like you're doing the same as modifying rows in the

database with a Hex-Editor.

> deferred PK means it's supported by an non-unique index: do you have non-unique indexes in place to allow for deferred PK's? I guess, not. You allow for duplicates temporarily

No, even temporarily duplicate keys are not allowed.

You may want to learn more about the UPDATER concept of the ABAP layer...

> Why they introduced the deferring option for PKs?

There is no deferring of PKs - you got that wrong.

> here we go: they add information about the data into the data dictionary used in query rewrites (correlated queries, MV's, PK/FK joins ). It engraves " I'm the key you pointing back your foreign key, I'm immutable, use me to select 1 row" inside the DB.

> Optimizers had evolved and will so in the future - not in a way taken less BUT more information to generate good access plans.

So far the nice theory.

And now go ahead.

Pull your Oracle 10g or 11g database and provide an example where the CBO makes up a more efficient plan when there is a PK instead of UNIQUE Index + NOT NULL constraints.

I know that you cannot do that.

Proove me wrong!

> (oops, it may that we use the table from outside...)

Oops - you're violating license agreements and possibly financial laws...

regards,

Lars