SELECT on database table containing strings takes much too much time (Oracle)

[ennowulff]

Hi there!

I have created a database table with two index fields (key1, key2) of type CHAR2 and a value field of type STRING.

the table contains 3700 entries. The value field contains only short strings (max 30).

The SELECT statement takes about 3 seconds...

Selecting only the key fields is very fast but with string field 3 seconds.

I did an SQL trace but there is only the SELECT statement with this enormous run time.

I know that storing and selecting STRINGS in a database is not the best way, but I see no reason why it takes that much time.

this is from performance trace SAT:

Do you have any hints for me?

Thanks in advance

Enno

[Jelena]

Can you see the specific execution path for SELECT? It's available in ST05 (not sure about SAT).

And is this happening with just this specific table? Some DB issue with a specific table is more likely than general problem with string fields.

I've worked with an ECC system on Oracle before and don't recall any issues like that.

[Sandra_Rossi]

RAWSTRING is not used so often in standard tables. I recommend that you look at ORACLE forums and in SAP notes. It may depend on your Oracle database version: there is the note 563359 (Performance optimization for tables with LOB columns) which says that Oracle 11.2 and above is okay, but not before (script provided in the note).

[pjl]

Hi Enno,

Can you give some details about what exactly the database translated this string to? I guess it's not a varchar but something else.

Cheers,

Petet

[ennowulff]

Additional info:

the field is of type STRINGVAL what is datatype "STRING - Character String (CLOB)"

Oracle Version is 12.1.0.2.0

[ennowulff]

It seems that every single entry will be fetched with SELECT * ... INTO TABLE:

The same select for T005T (which I used as template for my test-table) looks "more efficient":

I haven't found any explanation about "DB: Exec Static"...

tomas.buryanek

[Tomas_Buryanek]

enno.wulff T005T table does not have STRING column. I tried run SAT on SELECT * FROM Ztable (with STRING column) and do not see "DB: Exec Static" on our system...

[Tomas_Buryanek]

Hello,
I was just solving this issue yesterday 🙂
We had a (standard abap / open sql) SELECT from Z* DB table where one of the columns is defined as STRING (CLOB in Oracle). Simple SELECT * FROM ztable of about 40k records took ~ 60 seconds!

I was investigating what could be the problem and pin-poined it also on STRING column. If I run same SELECT but just without selecting the STRING column - it took ~ 1 second (40k records, just without that one string column).

Could not find any other solution. But I tried "native SQL" from Oracle using "EXEC SQL" in ABAP and selecting same amount of data took now only ~ 10 seconds (FETCH in DO. ENDDO. cycle, different way might be even faster? comment please). So I ended up with using this "workaround" because it is the part of code where we need as much performance as we can. BUT! read below!!!

If you use this workaround with native SQL - then I strongly recommend to do a detailed testing and comparison of the data you are getting (OPEN SQL vs. Native SQL SELECTs). In my case data were ALMOST identical. Difference was that string data of native SQL select were missing all 'EN SPACE' (U+2002) trailing characters! (no problem for our implementation)

Please comment / share other ideas if you have any...

[ennowulff]

Hey Tomas!

Yes this is exactly what I experienced. Thanks for sharing your workaround!

We solved this topic as we replaced STRINGVAL by CHAR1024. That's space enough for what we need. But I got y little bit scared because using a STRING in database table heas become common procedure. I know, it's not the fastest way but it should not be THAT slow!

I had a look at the oss note Sandra mentioned. I haven't tried yet because Sandra said it should be obsolete from oracle version 11 and above. I still hope that there is a switch or parameter that must be set to make STRING selection faster...

[Sandra_Rossi]

There are a few discussions in Stack Overflow about the topic (maybe this one has some interesting insights).

There are also Oracle LOB performance guidelines and LOB storage parameters, and this discussion.

All discussions seem to focus on LOB "in row" and "out of row" storages, and storage parameters.

[Tomas_Buryanek]

enno.wulff Sadly STRING is sometimes really needed and can not be replaced with CHAR data type. I checked that NOTE and also do no think it would help in our case since we have versions which already have this...

sandra.rossi Thank you for the links. But all of these are relevant only from the Oracle point of view. But Oracle selects alone are OK-ish in terms of performance. The problem is somewhere in SAP (Open SQL select and its interpretation + implementation):

SAP select ~60 seconds

Oracle select ~10 seconds

[ennowulff]

Thanks, sandra.rossi ! The links look very promising!

This is a very interesting statement:

A LOB is stored inline when:

(
  The size is lower or equal than 3964 AND
  ENABLE STORAGE INROW has been defined in the LOB storage clause
)OR(
  The value isNULL)

That means to me, that my demo table should mostly behave like a "normal" db table.

If "IN ROW enabled" means "Row store" in database table technical settings and "IN ROW disabled" means "Column Store", then I am pretty sure that I tried both settings without any change in the performance...

[Sandra_Rossi]

As explained by Tomas Buryanek, it seems to be a kernel (DBI/dbsl) issue, so you should open a ticket/customer message at SAP support, if there's no other SAP note with a correction. If it's not an issue, then no doubt that SAP will give you some hints about what's going on.

[ennowulff]

This is an answer test.

Or a Test answer.

From mobile

[ennowulff]

Hi sandra.rossi I think there is something broken with the answers functionality...?!

I cannot react directly on an answer/ comment...

I only see "Like", "Share" and "Alert Moderator" beneath an answer. ;(

oliver is it because the "answers" are "comments" only??

[Sandra_Rossi]

Mobile test

[Sandra_Rossi]

Thank you for the info & action (you're the first person who is explaining to me why he/she has a problem and is attempting to find a solution) - I use my laptop only so I can't say why there could be a problem. I just used my mobile to post both a comment on the question and a comment on this answer, it didn't seem to be different, UI from the laptop and the mobile looked identical.

[ennowulff]

ok.

• there are ANSWERs and COMMENTs.
• Jelena and Peter COMMENTED instead of ANSWERING; so I am not the only one who is confused about it... 😉
• When clicking on "View full comment" in the notification mail, I see all comments/ answers, but the memo field is for "YOUR ANSWER" which will cause a new answer instead of commenting the former comment.
• For reacting to a comment I need to click "COMMENT" of the origin comment. This COMMENT then will be placed at the end of the previous comments. That was not obvious for me.
• Mobile and Desktop version is the same (as you also just said). I just was confused about the doing

Thanks sandra.rossi for clarifying and sorry for mixing the main topic with this UI issue...!

Cheers
Enno

[Sandra_Rossi]

Jelena and Peter have chosen the right option, because their comment was more questions than answers. An "answer" is a final solution.

Yes, I think the SCN team did not choose the right option, some people proposed to hide that "your answer" box, and they have chosen to just add a text that nobody reads 🙂

[BaerbelWinkler]

enno.wulff sandra.rossi

Hi Enno, hi Sandra,

just to add to this off-topic thread: another issue with having to respond to an "Answer" when actually responding to one of the already exisiting comments is to remember to use the @-mention to make the intended community member(s) aware of your reaction/response. Otherwise they'll not get a notification and others reading the thread may not really know who is being addressed.

[ennowulff]

sandra.rossi This is really some kind of weird. I searched minutes for your comment (because I got a notification email) but did not see your comment, because it was hidden behind "show more"... 😕

[Sandra_Rossi]

Maybe that would have cost less to deploy an existing "forum" platform (like Stack Exchange) than developing it from scratch... 🙂

[former_member350857]

Hi guys,

I am having the same problem. I did a test where I did a select on the same table with the field containing the string and one without. The difference is crazy (5 seconds to 12 milliseconds). Execution plan is the same for both (5 cost).

I cannot find any info on this issue in any official document (notes, kernel release notes).

Do you have any info for me whether this was fixed in a new kernel. Currently running 753 PL400.

Oracle Release: 12.1.0.2.0

Looking forward for your answers. Happy Easter everyone!

BR
Fabian

[pjl]

Hi Enno,

if the string is implemented as a CLOB or even BLOB on Oracles side, that would explain some delay. I don't understand this huge delay though.

But the gerenal problem with [BC]LOBs is, that you need an extra step in the OCI interface to retreive the data.I havent programmed with Oracle OCI since Oracle 10 though, so my knowledge is somehow outdated. In any case Strings are just simply there when you retrieve a row from the data base but for CLOBs you need to open a stream for every selected row and 'String' field in order to retrieve that data. I guess thats what the SAP data base driver does under the hood.

But I see this is an interesting issue. Especially when it comes to compare to HANA behaviour, so I will have a look myself soon.

Cheers,
Peter

[ennowulff]

Thanks Peter! Yes, that matches with my information. As STRINGS seem to be stored outside the database, the access is a little bit more complicated. But, again, it should not be THAT slow... Still searching for some switches or parameters to make this "extra trip" faster.

Cheers
Enno

[ennowulff]

Hi Helena! There is no specific Trace information for this select.

I created a second similar table to reproduce this issue and it's the same problem. Replacing STRING by CHAR1024 makes the select fast as ever.

I also don't remember any problems with string fields in the database

[Sandra_Rossi]

Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area: "Before answering You should only submit an answer when you are proposing a solution to the poster's problem"