MaxDB ST05-trace fallacy – when sometimes the trace is wrong…

One of the most important analytical tools used to investigate slow running SQL statements is the well-known ST05 – SQL trace.

The idea of it is that the ABAP database interface notes down what SQL statement it sends to the database, how long it took to get the result, what selection criteria were used and so on.

Obviously, a key point here is that the trace contains exactly the SQL that was actually sent to the database.

Recently I came across a speciality that must be considered when using the ST05 trace results on MaxDB databases. Otherwise one will end up with totally wrong execution paths and thus at wrong conclusions for how to improve performance.

Let’s look at the following SQL Explain plan:

We see that the estimated costs are quite high, but this is not what I want to point out today here. Instead, keep an eye on the execution path – it’s a RANGE CONDITION FOR KEY that uses four key columns (MANDT, BZOBJ, KALNR, KALKA).

Next, also a common step in performance analysis, we take the statement, have the ST05 fill in the ?’s with the real variable values and use the ST05 or the SQL Studio to explain the statement again (and modify it eventually):

Now we still see a RANGE CONDITION FOR KEY, but only two columns are used, making the plan even less efficient.

The values had been taken directly from the ST05 trace. Let’s double check this:

Sure enough, the bind values are there.
Notable, however, is the fact that the values for KALNR and KADKY (now missing in the used KEY COLUMN list) are NUMBER type values.

The leading zeroes in A2/A3 and the date-like information in A5/A6 might give an indication that this might not be totally correct.
Let’s check the table column definition:

Surprisingly we find both columns to be CHARACTER types.
Well, not exactly surprisingly – many number-datatypes from the ABAP world are mapped to character columns.

For the application and the database interface this is rather transparent, but for the database query optimizer, this is an issue as it cannot use non-matching datatypes for KEY or INDEX accesses. If we want to run or explain the statement manually, we have to take care of this.

Therefore, we have to enclose the numbers into apostrophes to mark them as character strings:

And here we are, back with the original execution plan.

Now the real performance analysis can begin!

P.S. although the KADKY column is not used for the execution plan it’s still important to get the data type correctly – otherwise the optimizer has no chance to estimate the selectivity of the conditions correctly.

ORA-01007 in ABAP stack and what to do about it.

Recently a colleague asked me about how to analyze the following short dump:

 Database error text........: "ORA-01007: variable not in select list"
 Database error code........: 1007
 Triggering SQL statement...: "FETCH NEXT "
 Internal call code.........: "[DBDS/NEW DSQL]"

The error was raised within a standard SAP report with the following statement:

>>>>>       select tablespace_name,
 1435          initial_extent / 1024,
 1436          next_extent / 1024,
 1437          min_extents,
 1438          max_extents,
 1439          pct_increase,
 1440          pct_free,
 1441          pct_used,
 1442          ini_trans,
 1443          freelists,
 1444          freelist_groups,
 1445          iot_type,
 1446          partitioned
 1447       from user_tables into :ora_para
 1448       where table_name = :tabname
 1449     ENDEXEC.
 1450   endif.

Does not look too suspicious, so the next thing to look for is whether the table queried would match the SQL statement.
The USER_TABLES view is again a standard view for Oracle databases and looks like this in 10g (for easy comparison I marked the columns used in our statement with a ‘->’)

desc user_tables
      Name                         Null?    Type
      ---------------------------- -------- ---------------
   -> TABLE_NAME                   NOT NULL VARCHAR2(30)
   -> TABLESPACE_NAME                       VARCHAR2(30)
      CLUSTER_NAME                          VARCHAR2(30)
      IOT_NAME                              VARCHAR2(30)
      STATUS                                VARCHAR2(8)
   -> PCT_FREE                              NUMBER
   -> PCT_USED                              NUMBER
   -> INI_TRANS                             NUMBER
      MAX_TRANS                             NUMBER
   -> INITIAL_EXTENT                        NUMBER
   -> NEXT_EXTENT                           NUMBER
   -> MIN_EXTENTS                           NUMBER
   -> MAX_EXTENTS                           NUMBER
      PCT_INCREASE                          NUMBER
   -> FREELISTS                             NUMBER
   -> FREELIST_GROUPS                       NUMBER
      LOGGING                               VARCHAR2(3)
      BACKED_UP                             VARCHAR2(1)
      NUM_ROWS                              NUMBER
      BLOCKS                                NUMBER
      EMPTY_BLOCKS                          NUMBER
      AVG_SPACE                             NUMBER
      CHAIN_CNT                             NUMBER
      AVG_ROW_LEN                           NUMBER
      NUM_FREELIST_BLOCKS                   NUMBER
      DEGREE                                VARCHAR2(10)
      INSTANCES                             VARCHAR2(10)
      CACHE                                 VARCHAR2(5)
      TABLE_LOCK                            VARCHAR2(8)
      SAMPLE_SIZE                           NUMBER
      LAST_ANALYZED                         DATE
   -> PARTITIONED                           VARCHAR2(3)
   -> IOT_TYPE                              VARCHAR2(12)
      TEMPORARY                             VARCHAR2(1)
      SECONDARY                             VARCHAR2(1)
      NESTED                                VARCHAR2(3)
      BUFFER_POOL                           VARCHAR2(7)
      ROW_MOVEMENT                          VARCHAR2(8)
      GLOBAL_STATS                          VARCHAR2(3)
      USER_STATS                            VARCHAR2(3)
      DURATION                              VARCHAR2(15)
      SKIP_CORRUPT                          VARCHAR2(8)
      MONITORING                            VARCHAR2(3)
      CLUSTER_OWNER                         VARCHAR2(30)
      DEPENDENCIES                          VARCHAR2(8)
      COMPRESSION                           VARCHAR2(8)
      DROPPED                               VARCHAR2(3)

Still nothing too suspicious here – all columns used in our statement are present in the view.
Maybe explaining the statement will shed some light on this issue?

explain plan for
select tablespace_name, initial_extent / 1024, next_extent / 1024,
min_extents, max_extents, pct_increase, pct_free, pct_used, ini_trans,
freelists, freelist_groups, iot_type, partitioned
from user_tables
where table_name = :A0 ;


select * from table(dbms_xplan.display);

Plan hash value: 19987672

| Id  | Operation                          | Name           | Rows  | Cost  |
|   0 | SELECT STATEMENT                   |                |     1 |     3 |
|   1 |  NESTED LOOPS OUTER                |                |     1 |     2 |
|   2 |   NESTED LOOPS OUTER               |                |     1 |     2 |
|   3 |    NESTED LOOPS OUTER              |                |     1 |     2 |
|   4 |     NESTED LOOPS OUTER             |                |     1 |     2 |
|   5 |      NESTED LOOPS                  |                |     1 |     2 |
|   6 |       NESTED LOOPS                 |                |     1 |     1 |
|   7 |        NESTED LOOPS                |                |     1 |     1 |
|*  8 |         HASH JOIN                  |                |     1 |     1 |
|*  9 |          FIXED TABLE FULL          | X$KSPPI        |     1 |     0 |
|  10 |          FIXED TABLE FULL          | X$KSPPCV       |  1491 |     0 |
|* 11 |         TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |     0 |
|* 12 |          INDEX RANGE SCAN          | I_OBJ2         |     1 |     0 |
|* 13 |        TABLE ACCESS CLUSTER        | TAB$           |     1 |     0 |
|* 14 |         INDEX UNIQUE SCAN          | I_OBJ#         |     1 |     0 |
|  15 |       TABLE ACCESS CLUSTER         | TS$            |     1 |     0 |
|* 16 |        INDEX UNIQUE SCAN           | I_TS#          |     1 |     0 |
|  17 |      TABLE ACCESS CLUSTER          | SEG$           |     1 |     0 |
|* 18 |       INDEX UNIQUE SCAN            | I_FILE#_BLOCK# |     1 |     0 |
|* 19 |     INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     0 |
|  20 |    TABLE ACCESS BY INDEX ROWID     | OBJ$           |     1 |     0 |
|* 21 |     INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     0 |
|  22 |   TABLE ACCESS CLUSTER             | USER$          |     1 |     0 |
|* 23 |    INDEX UNIQUE SCAN               | I_USER#        |     1 |     0 |

Also, the EXPLAIN works, so what is this about?
The first important hint is already present in the short dump:

   Triggering SQL statement...: "FETCH NEXT "

This tells us that the error did not occur during parsing or opening (that is executing) the query.
Instead, the error occurred when our work process, the client for the database, tried to fetch the result set from the server process to the client process.

In fact, this is already the major part of understanding this issue.
The error message “ORA-01007: variable not in select list” tells us that the client wanted to fetch a value from the server that was not selected in the statement before.
Now how can that be?
We’ve seen that all columns from the SQL statement were present in the table!

There’s one thing we didn’t pay attention to – until now:

>>>>>       select tablespace_name,
 1435          initial_extent / 1024,
 1447       from user_tables into :ora_para              <<<<
 1448       where table_name = :tabname

This is not just a simple SELECT statement, but a SELECT … INTO statement.
With the INTO clause, we tell the Oracle client to accept whatever we give it as the memory structure to put the result data into.

Knowing that it’s time to check what exactly we gave the client here.
The workspace variable :ora_para is defined in the DATA section of the ABAP report.
Using transaction ST22 (where we already checked the short dump itself) we simply use the “ABAP EDITOR” button to navigate to the source code and sure enough we find the definition of :ora_para in the DATA section:

  data: ora_para like oratabsti,
        db_index like dd12l-dbindex.

Our ora_para is defined to look like a table or structure named ORATABSTI.
By double-clicking on the name in the ABAP editor or by using transaction SE11 we can check the layout of this DDIC object.

ORATABSTI looks like this:

  Fields of active runtime object
  | Position |Field Name  |
  |   1      |TABSPACE    |
  |   2      |INIT        |
  |   3      |NEXT        |
  |   4      |MINEXT      |
  |   5      |MAXEXT      |
  |   6      |PCTINC      |
  |   7      |OPCTFREE    |
  |   8      |OPCTUSED    |
  |   9      |INITRANS    |
  |  10      |OFREELIST   |
  |  11      |OFREEGROUP  |
  |  12      |INDEXORG    |
  |  13      |PARTY       |
  |  14      |COMPRESSION |
  |  15      |COMPRESS_FOR|

Do you spot it?
There are two additional fields in this structure!

  |  14      |COMPRESSION |
  |  15      |COMPRESS_FOR|

These columns don’t occur in our statement, although they are present in the USER_TABLES view.

Now it’s pretty clear what happened:
The SAP work process successfully prepared and executed the SELECT statement.
When it came to fetch the data from the Oracle server it told the Oracle client software to get the values for column TABSPACE, for column INIT, for column NEXT etc.
This went without problems until the Oracle client tried to get the values for column COMPRESSION.
Since this column was not part of the result set, the Oracle client software correctly raised the error:

"ORA-01007: variable not in select list"

As usual, once you know the cause of a problem, resolving it is most often not too difficult.
By either changing the ORATABASTI or the report, the problem can easily be solved.

Hope this error is not that confusing anymore from now on 🙂


How to run a SAP landscape (or so)

It’s been quite a while since my last blog post and we’ve been pretty busy in the primary support department.
A major change for us had been the introduction of “Enterprise Support” (ES) and the offer to customers to not only get support with technical issues but also to get free consulting to a certain degree.

I don’t want to go into the discussion of the pros and cons of ES – to me, it’s a fact that I accept.

So do many of SAPs customers.
In fact, many of them are e.g. big IT outsourcing companies that require having the level of support offered by ES.

The idea here is, of course, to provide customers with top IT-know-how and top operation procedures a support that fits this.
The reality (at least at our end of the support chain) is somewhat less “top”.

In the last months, I’ve seen more and more customer messages that had not been opened because the software did not work as it should.
Instead, these messages were opened to clarify questions like:

  • “How should I setup my standby server?”
  • “How often do I need to take backups?”
  • “I’m going to install a new system, Should I use Oracle 9 or 10 for that?”
  • “Our storage system is full – what should I do?”

Obviously, these questions are a bit awkward when found in support messages of high-level IT-companies.
More frightening then the questions itself is the timing with which these are sent to SAP support:
All of them needed to be clarified as soon as possible because the decision/information was on the critical path of a super important and already late implementation project or GoLive due data.

Let put this a bit clearer:
These customers began to worry about the “WHAT” and “HOW” of their database backup strategy two days before they wanted to start the productive use of their system.

The best thing to do here is usually to postpone the GoLive and go back into the planning phase.

But what other options are there for the system integrator to get the answers to their questions?

Answering “Read the f***g documentation!” to this request would be just cynical.

Instead, SAP offers a much better alternative: “Run SAP Best Practices” (available in the SAP service marketplace at this link )

There you’ll find lots of documents that tell you what to do and how to do it.
And even better: what NOT to do.

One of my favourites of this collection is document “012 – Backup and Restore for SAP System Landscapes“.
In its current revised version, a complete overview of the Do’s and Dont’s of system landscape backup and restore is given.
A big part of the guide is dedicated to highlighting the critical dependencies that come with point-in-time recoveries.

This is one of the documents that everyone involved in technical operations of SAP landscapes should now rather well!

Ok – that’s it for today.
Now, go and get your copy of this Run SAP Best Practice!