Fragmentation in Oracle LMTS tablespace!

Recently two customers reported independent from each other that their LMTS/ASSM tablespace shows much “fragmentation”. What they meant was, that the repeatedly got errors like:

ORA-1688: unable to extend table SAPWBP./BIC/B0000256000 
partition /BIC/B00002560000000000092 by 128 in
tablespace PSAPBWP

or

ORA-1654: unable to extend index SAPWBP.BALHDR~2 by 128 in
tablespace PSAPBWP

The errors occurred although there had been much (>3GB) free space in the tablespace.
It turned out that the tablespaces (in this case PSAPBWP) where configured with the default option, that is:
locally managed autoallocate segment space management auto

This means:

  • the tablespace does the free space management in tablespace-local bitmap-structure,
  • the extent sizes are determined by an oracle internal algorithm (that basically uses larger extents the larger an segment grows)
  • the freeblock-management is automated as well.

These automatic features disburden the DBA from former regular tasks (like CHECK NEXT EXTENT) to optimize the storage settings for the database objects. One prominent effect was advertised to lead to lower fragmentation issues. And now the customers get the errors above. So what happened here?

As most of you know, extents are allocated in one chunk, so the free space block that should be used for a new extent has to be at least the size of the extent that should be created. In the customers case the free space should have been 128 blocks large (or larger). Obviously there was no such large free space block available at this moment in time, so the error message was thrown out.

But how could this have happened at all?

Why did all the automatic storage management features fail here?
Well, they didn’t.

Let’s see how the situation was created:
In my example I’ve just one small tablespace and four segments (a, b, c and d). As you may notice, this tablespace is also a default tablespace, so the extent sizes are chosen by the system.


(free space: 2 blocks, # of additional extents for segment a: 0)

Currently there are 2 free blocks available, the largest freeblock chunk is also 2 blocks and there is no freeblock available that would fit the next extent of segment a, since it already has a extent size of 8 blocks.

Anyhow, in this tablespace is not enough free space anyhow, so nobody would say: this is a mistake. Therefore, let’s free some space and drop segment d.


(free space: 18 blocks, # of additional extents for segment a: 0)

Now there are 18 blocks free, enough to store even two times the next extent size of segment a. But as the largest free chunk is only the size of 4 blocks, we would still get the ORA-1688 (if segment a would be a table).

So what can we do about this? Everything worked as it should and still there are many small bits of free space that are mostly unusable for the big segments in our tablespace. To make the free space useable again we would have to reorganize the tablespace – not so nice, isn’t it?

Shouldn’t LMTS prevent us from this?

No, it shouldn’t! LMTS with the auto allocate feature is the general purpose approach to extent storage in tablespaces. It’s a one size fits all approach that is – that cannot be optimal for every appliance.

To avoid situations like this there is another option available for locally managed tablespaces: EXTENT MANAGEMENT UNIFORM.

With this setting each and every extent in this tablespace will have the same size. Of course this is not what we want in case we have many small tables AND many large tables mixed together in one tablespace. In that case even for 1-row-tables a complete extent would be allocated and thus much space wasted,

But if we know in advance that there will be many very big segments to be stored (like it’s often the case for BW objects) than it makes sense to move these segments to a tablespace with UNIFORM extent size.

In my example I set the extent size to 8 blocks – let’s see how much free space is available then:


(free space: 2 blocks, # of additional extents for segment a: 0)

As you see now there are much fewer extents in the tablespace but all of them allocate the same number of blocks. Currently only three blocks are free so no segment in this tablespace could allocate an additional extent.

Let’s drop segment d again:


(free space: 19 blocks, # of additional extents for segment a: 2)

Now there are 19 (3 + 2*8) Blocks free and there are two freeblock chunks of a 8-block size. So not only one but two additional extents would be able to fit in this tablespace.

Due to the fixed extent size there is no way to “produce” unusable “holes” in your free space. Everything that got freed is useable again! BTW: this is exactly the same way MaxDB archieves reorganization-free operation. It allocates space always by single pages – so there are never free space areas that are too small.

Of course it would be a good idea to make the tablespace a multiple of the extents size to also get use of the 3 empty blocks at the end of the tablespace…

There are of course information about this in some SAP notes as well as in the documentation:

821687 – FAQ: Space utilization and fragmentation in Oracle
599694 – LMTS autoallocate: Extent allocation
214995 – Oracle locally-managed tablespaces in the SAP environment

KR Lars

‘Mind the gap …’

More and more customers decide to use MaxDB and with that, the number of customers asking for a comprehensive list of differences or a comparison increases as well.

In my mini-series “Mind the gap” I will try to shed some light on where the little or big differences between MaxDB and Oracle databases are and what to keep in mind when working with them.

Today I will start with something very basic nevertheless important:
logon mechanisms and user types.

So let’s get started – about database logon mechanisms in SAP systems

One quite common problem is lost connectivity to the database. There are not too many SAP Basis administrators around that never got across a situation where the workprocesses were not able to connect to the database anymore.

In such a situation it is good to know how the workproceses actually perform the connect to the database.

How it works with Oracle…

For Oracle systems the SAP workprocesses make use of an operation-system (OS) authentication called OPS$-mechanism.
One creates a user in the database and sets it up in a way with the flag “identified externally“.
Now whenever this OS-user tries to connect without specifying username and password (e.g. “sqlplus /”) the database asks back to the OS if this user is known.
If so, the access is granted.
Once the workprocesses have done this successfully, they have access to just one table called SAPUSER that contains the password for the SAP-schema user.
With that password the workprocesses then perform the final connect as the SAP-user against the database – this time with a username and a password.

That approach allows storing the logon data for the actual SAP-schema user inside the database and relies (obviously) on the authentication of the users by the OS.
Of course, one has to know for sure, which OS-users run the SAP workprocesses in this case.

Anyhow, to make this mechanism work correctly the sqlnet configuration has to be done correctly (sqlnet.ora, tnsnames.ora, ORACLE_SID environment etc.) and of course, the OS-based authentication has to work properly (which might be difficult in some Windows Active Directory setups).

… and with MaxDB

MaxDB uses stored logon data as well, but it does not store them in the database.
Instead it is stored them on OS-level for every user on every server separately and encrypted.
The facility that MaxDB uses for this is called XUSER.
Therefore, for the OS-users that run the workprocesses (<sid>adm on UNIX/LINUX and SAPSERVICE<SID> on MS Windows) it is necessary to setup and store the logon data.

A XUSER-entry consists of all information needed to connect to the database, that is username, password, database SID, hostname of the database and some other “special” settings.
Once the data has been setup correctly, the workprocesses (or any other process like e.g. sqlcli or dbmcli) can logon to the database just by specifying the KEY under which the logon data is stored.
For the SAP schema user the key is usually ‘SAP<SID>’, so if your SAP System Identifier is ‘SDB’ it would be possible to connect just via:

          sqlcli -U SAPSDB

No need to specify the database or the host on which it runs, just the XUSER-key, that’s it.

*) ok, in the area of CCMS there we have something called “central authorization” where the logon data is stored within the database. However, this is just used by the CCMS.

Checking the Connectivity

Another important thing to know is how to check if the logon mechanism was configured correctly.
Since it is time consuming to try the “startsap”-option repeatedly, a shortcut is using R3trans.
It uses the same logon mechanisms as the workprocesses do, but has a much smaller startup overhead (just one process, no memory initialization etc.).
Use

          R3trans -d 

to try a connect to the database including the read access to some internal tables.
If it works – great, chances are that the workprocesses will also be able to perform the connect.
If not it will have generated a trace file named “trans.log” in the current directory containing detailed information about what went wrong with the logon.
Since R3trans is database-dependent is knows how to use the OPS$-mechanism for Oracle and how to use the XUSER-mechanism with MaxDB.

ATTENTION
: mind the user context you are running R3trans in!
It might well be that you test it on e.g. Windows as the <sid>adm user and it works and still the workprocesses fail to connect as they are running in the SAPSERVICE<SID> context (VERY popular mistake!).

User concepts

The second part of my blog is about a drier topic: user concepts.
Both database systems know different kinds of users that are required in different contexts.

SQL-users

Let’s start with the easy to understand SQL-user first.
This database user is used most.
It can logon to the database, create and drop database objects and  query and update data.
Just what a database user is all about concerning working with the database itself. The workprocesses use this type of user to connect to the databases.

What these users usually are not able to do are actions like backup, recovery, shutdown and (of course not) startup the database.
Since SQL-users only exist within the database, it is necessary that the database is up and running to use them. Therefore, these users are just not there to start the database instance…

DBA-users …

The second kind of user is the administrative (dba) user. This user type is focused on performing dba tasks like backup, recovery, shutdown and startup ❗ the database. It is the “magic database god” straight away as it can create the database instance.

This distinction between the both user types is present in both MaxDB and Oracle. Nevertheless, there are again important differences.

… in Oracle

With Oracle there is the SYSDBA user. This user (called SYS) can do anything – really anything in the database.
Permission checking just does not exist for this user. Beside any DBA actions it can also access any data in the database. It is really the “almighty” on Oracle instances.
This comes in very handy, as this user is also os-authenticated.
Just put the os-level user you are starting sqlplus with into the dba-group and all you have to type in is

          sqlplus "/as sysdba"

to connect to the database instance.
With every light there is a shadow: this approach does not protect you from any mistakes. If you are about to mess things up – this user makes possible the worst. (Amazing how quick you can drop a multi-GB-tablespace… just one command…)

… and in MaxDB

MaxDB on the other side strictly separates SQL-users and DBAs (called Database Manager Operators in MaxDB-speech).

As such a DBM user (usually named CONTROL) you can also do anything you like with the database instance – except accessing the data in the database. (I here praises of SOX-consultants rising in the background…).
Nevertheless there are also exceptions to this separation – the DBM users are allowed to access the database catalog and some internal tables to get information about the database instance when it is up and running.
To enable this “double-life” the user-logon information are stored twice: in a special file (user profile container or upc-file) and in the database (table users).
In former versions of MaxDB it was possible to change the password of a user in just one of both location and thus create an inconsistency. Nowadays this is past – so a change of the password for e.g. CONTROL or SUPERDBA in one of the locations automatically updates the other.

My view to it

To me this strict distinction has been one of the major obstacles when I first got hands on MaxDB after developing on Oracle for quite some time.
However, I have to say, “I like that!”
Like most people, I tend to get lazy and sometimes thoughtless in the hurry of the day and then it is easy to issue just that catastrophic command that kills your database.
Concerning that Oracle pushes the “learn through pain“-approach where MaxDB just do not do anything until you do it right (sometimes it is even too quiet about what exactly was not right…)

When it comes to flexibility with the setup of logon mechanisms Oracle is clearly in the lead – so many options, so many parameters than can be set.
However, on the few thousand Oracle installations of SAP systems that I have seen in the last years most often just the standard setup was used. Sometimes not even the recommendations had been followed.
Viewed from that angle MaxDBs logon mechanism has everything required for a SAP installation and is much easier to configure.

The cheat sheet ————- 8< ———— cut here ———— 8< ————

To reward you for your endurance and reading this so far, here’s a short cheat sheet that provides the quick overview over the differences and the similarities between the both DBMS.

Oracle

MaxDB

superuser called SYS superuser called CONTROL
workprocesses use OPS$– and
username/password to logon to the database
workprocesses use the XUSER data to logon to the database
SYS can do anything, including accessing SAP-data CONTROL can only access the database instance, but not the SAP-data
slight distinction between DBA and SQL-user (SYS is both) strict distinction between DBA and SQL-User
notes to know:

562863 – FAQ: Logon mechanisms

notes to know:

39439 – XUSER entries for SAP DB 6.x and 7.x

25591 – Changing the DBM, SYSDBA and DBA user passwords

———————- 8< ———— cut here ———— 8< ——————–

In the next issues of “Mind the gap” I am going to talk about topics like data storage, query optimizers, database software installations … let me know if you’re interested in a specific topic and “stay tuned!”

Best regards,
Lars