SAP Tools vs. SQLTrace and TKPROF

[Former Member]

Experts,

First let me say that I am NOT an oracle expert, I am a basis person. Such is the reason I come seeking wisdom.

We are undergoing somewhat of a knee-jerk performance analysis, based on some user complaints. Our new-to-the-organization dba (who has no experience in SAP) is wanting to set up a small performance tablespace within our Production ECC DB and use sql trace and tkprof to analyze what ever it is that they are wanting to look at.

In the past we have been slightly more methodical about tracking down the problem, and then analyzing it based on traces and information within the ST* and DB* tools of SAP.

my 2 questions are:

- Is there any concern with setting up perf gathering tablespace within the production DB

- Does SQLTrace and TKProf give information not readily available within SAPs performance and administration txn's? (DBACockpit, ST* tools, Etc.)

(edit: environment is Solaris 10 64-bit/Oracle 10.2.0.2 64-bit e2900 64/GB Mem)

Thank you in advance,

Phillip

Edited by: Phil May on Oct 9, 2009 1:47 PM

[fidel_vales · Accepted solution]

Hi,

More or less, what your DBA wants to do is explained on the SAP Note 613872 "Oracle traces with ORADEBUG" and 654176 "Analyzing Oracle trace files with TKPROF"

Looking at them you will understand what he is trying to do. Probably they want to use something similar to oradebug as there are other options to activate the traces, but I recommend to take a look at the note as it is very important to be able to "lock" the SAP workprocess in order to do the trace on the correct session.

what I do not know is why they need a extra tablespace.

regarding your questions:

> - Is there any concern with setting up perf gathering tablespace within the production DB

as mentioned, I do not know why they need this tablespace, perhaps they can elaborate a little about it.

Nevertheless, I do not think it is an issue. In oracle <= 9i you set up a tablespace for statspack information (if you install it) and in 10g that information is gathered automagically in the SYSAUX tablespace

> - Does SQLTrace and TKProf give information not readily available within SAPs performance and administration txn's? (DBACockpit, ST* tools, Etc.)

Yes. But, before I use SQLTRACE/ORADEBUG i try to analyze the available information from ASH or AWR and "standard" traces.

my 5cts

Edited by: Fidel Vales on Oct 9, 2009 10:08 PM

Perhaps would be interesting also to take a look at the note 853576 "Oracle 10g: Performance analysis w/ ASH and Oracle Advisors" they can provide an initial base for the analysis without the need of setting up anything. They can be executed from DBACockpit but take a look at the requirements: note 1028068 "Required Oracle options for the DBA Cockpit"

I mention all this because my job is to analyze performance issues in SAP systems and usually I have to deal with them from inside SAP. In few ocassions I had to use an oracle trace. May be this is one

[Former Member]

I would say let the DBA do his job the way he knows how. He will be most effective that way, and he will only get frustrated at the lack of what he's expecting to see, both in content and in format, if you try to teach him "the SAP way." In the end, you are asking for his assistance to diagnose database issues. If you try to tell him also how to do it, he will probably just leave you to do it for yourself.

[stefan_koehler]

Hello Phillip,

just let me tell you some parts from my personal performance tuning experience.

At first i think, that the analyze possibilities inside SAP for performance issues are one of the best i have ever seen. So in my opinion you can mostly identify and track down the performance issues within SAP itself (ST05, ST04, STAD, etc.). The other good part is that you can get the content of the binds very easily in SAP, if you use a raw sql trace, it is a little bit more difficult to get the binds (especially in a productive environment - hard parses).

As you told your oracle dba has no experience in SAP, i would show him the SAP tracing possibilities first. If he needs to do further sql analyzes he can do this with SQL*Plus (for example with autotrace, etc.).

> Is there any concern with setting up perf gathering tablespace within the production DB

Don't know what this should be for. Maybe he wants to copy the productive tables into an own schema .. but you don't need a seperate tablespace for that.

> Does SQLTrace and TKProf give information not readily available within SAPs performance and administration txn's?

Of course .. the raw SQL trace is much more detailed (wait events, etc.), but to be honest .. mostly you don't need it in a SAP environment.

Regards

Stefan

[lbreddemann]

Hi Stefan,

> At first i think, that the analyze possibilities inside SAP for performance issues are one of the best i have ever seen.

True and sad at the same time.

There would be so much more necessary to really have an end-to-end performance view of data processing processes in heterogeneous and distributed systems like SAP landscapes are.

>So in my opinion you can mostly identify and track down the performance issues within SAP itself (ST05, ST04, STAD, etc.).

> > Does SQLTrace and TKProf give information not readily available within SAPs performance and administration txn's?

> Of course .. the raw SQL trace is much more detailed (wait events, etc.), but to be honest .. mostly you don't need it in a SAP environment.

Hmm... I think this (being able to solve most problems with the currently available tool set) only partly comes from the completeness of the tool set.

At least for a part of it, I think it's something like 'When-all-you-have-got-i-a-hammer-everything-look-like-a-nail"-effect.

Usually the "Wait Event Evangelists" (like Cary Milsap) will tell you that the extended SQL trace is all you'll ever need to track down all of your performance issues.

Then there is a whole group of people that try to catch everything by staring at execution plans and theorizing about what execution step would be the problem cause (e.g. the bad hash join or the bad bad bad full table scan).

The DB-agnostic counterpart will say that the NetWeaver platform already covers everything necessary and that look out for additional indexes or ABAP parameters.

I think the truth lies (as usual) somewhere in between.

All in all I think the key point is to get an angle to the problems at all.

As soon as one can describe the problems in a structured way, (s)he has at least a chance to figure out what is happening when

the systems becomes slow.

Given that I'd also vote for the most convenient approach - and that is to use the GUI tools provided by NetWeaver.

And the more experienced Oracle tuning-super-heroes can always take a step further and emply the sql_id-data-collection script or one of the endless number of special scripts that are available through report RSORASTT nowadays.

Just my 2 pence...

regards,

Lars

Edited by: Lars Breddemann on Oct 10, 2009 10:31 AM

[stefan_koehler]

Hey Lars,

> There would be so much more necessary to really have an end-to-end performance view of data processing processes in heterogeneous and distributed systems like SAP landscapes are.

If you mean something like RFC connectons, BI-Load or from the portal view you are right. But the most of the problems that i have seen could be analyzed with the detailed information in STAD and ST05 .. so in more complex cases you need to combine different tools and systems to get a whole overview .. but anything you need already exists.

> I think this (being able to solve most problems with the currently available tool set) only partly comes from the completeness of the tool set.

I think about another very important part. The SAP tools are mostly enough, because of the SAP architecture strips down many oracle functions (limited column types, no implicit/explicit conversions, no triggers, no referential integrity, no special kinds of index like function based ones, no parallel query/dml, etc.). If i am analyzing non-SAP oracle databases, i also have to take a look at all this stuff and so a SQL trace would make much more sense in such cases )

> and theorizing about what execution step would be the problem cause (e.g. the bad hash join or the bad bad bad full table scan).

Yeah, that is one of my favorite ones .. the bad bad bad full table scan )

Regards

Stefan