RE: SAP Service Report / EarlyWatch Check usefulness?

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Wed, 11 Feb 2004 08:04:00 -0500
Message-ID: <358728A276824E419580403633AABFD001B7B583@INDYSMAIL03.am.thmulti.com>

Thanks for the reply Kip.

Finally getting access to the SQL. Turns out the developers did not have any Oracle IDs, they had to go thru the SAP tool, therefore they never did explains or traces, consequently, their solution to everything was to throw another
index in there.

-----Original Message-----
From: Kip.Bryant_at_Vishay.com [mailto:Kip.Bryant_at_Vishay.com] Sent: Tuesday, February 10, 2004 4:55 PM To: oracle-l_at_freelists.org
Subject: RE: SAP Service Report / EarlyWatch Check usefulness?

Jeff,

Is this coming from one of these reports? If you want to pass questions of this sort to SAP types, you can go to http://www.sapfaq.com and register for

BASIS listserv...or maybe they'll give you an "OSS" er, "SAPNET" account and you can't question SAP directly. Some minor comments inline below...

|Well, I'm having fun. The database is remote, so I cannot dig in to the
|degree I'd like.
|Not being familiar with SAP, some observations:

|1) The SAP admin insists that hash_join_enabled must be set to FALSE. The
|major complaint is that the batch/large reports are slow. Still trying
to
|get WHICH batch and WHICH reports are slow to look in detail/trace, but
|it
|seems to me they are shooting themselves in the foot not allowing hash
joins.
|Is this some peculiar trait of the SAP architecture or some left-over
|paradigm with respect to hash joins? The database is on 9.2.0.3.

They need to give you an account so you can run one of these things with trace on.

hash_join_enabled recommendation seems to vary depending on type of SAP system. There is an un-explained recommendation for 4.x sap 8.x oracle to set
hash_join_enabled to FALSE *but* it doesn't seem to apply to all flavors of SAP. Only 9.2 system we have so far is an out-of-the-box, un-tweaked BW (SAP
data warehouse) system which uses hash_join_enabled set to TRUE. I do have another SAP variant on 8.1.7 that has hash_join_enabled set to TRUE.

|2) Session_cached_cursors is set to 0. Looks like to me the middle-tier
|generates all
| SQL, and there's a lot of duplicate SQL.

|STATISTIC# NAME CLASS
|VALUE
|---------- ------------------------------------------------- ----------
|----------
| 205 session cursor cache hits 64
0
| 206 session cursor cache count 64
0

| Would setting session_cached_cursors benefit the SAP environment?

I actually have a reference that says session_cached_cursors is obsolete starting with 7.3 (hmmm) but it still seems to be a valid parameter. Not sure this would help. I have the same setting.

|3) All indexes are set with degree=4. Look at all the downgrades. Since
|the database
| is primarily transactional, what is he benefit to SAP of setting
|degrees=4?

Can't comment on this one.

|STATISTIC# NAME
|CLASS VALUE
|----------
|----------------------------------------------------------------
|---------- ----------
| 212 Parallel operations not downgraded
|32 308528
| 213 Parallel operations downgraded to serial
|32 2182286
| 214 Parallel operations downgraded 75 to 99 pct
|32 0
| 215 Parallel operations downgraded 50 to 75 pct
|32 0
| 216 Parallel operations downgraded 25 to 50 pct
|32 8725
| 217 Parallel operations downgraded 1 to 25 pct
|32 534

|4) Looking at waits, appears to be a lot of db sequential file/buffer busy
|waits. Is this
| also typical of SAP?

Maybe yes for db sequential file but maybe not for buffer busy. But what is

"a lot"? The only thing they've ever commented to me about was the average wait time for these. They have a "rule of thumb" for the averages (one for SAN and one for non-SAN). Test your SAP admin ;-). And then bad sql...it can be tough to connect bad sql (high disk reads or high buffer gets) if you don't have access to SAP where it will connect offending sql to the problem program...

Kip

|Well...that depends. They are NOT a substitute for the tuning
|knowledge of an experienced DBA but they can be a fair starting point
|when one is either not familiar with the application (SAP) or is
|relatively inexperienced with Oracle. Plus even SAP says you should
|test these recommendations and not just fling them at your production
|system. Personally, I'm at the point where I'd rather not have them
|taking up my time but it should be said that they do look at
|more than Oracle tuning issues and may be invaluable as part of the more
|major
|SAP application upgrades (2.2 to 3.0 was major and so was 3.1 to 4.6).

|I know that Jared has said this should go to sapfaq listserv but this
|may be a safer forum for a critical discussion. I've had SAP call me
|after asking "sensitive" questions.

|Kip Bryant
|SAP'd in '93 with version 2.0D

||They are trash in what regards Oracle tuning.
||Total and unadulterated.

||Cheers
||Nuno Souto
||in sunny Sydney, Australia
||dbvision_at_optusnet.com.au
||----- Original Message -----
||From: "Thomas Jeff" <jeff.thomas_at_thomson.net>
||To: <oracle-l_at_freelists.org>
||Sent: Tuesday, February 10, 2004 2:14 AM
||Subject: SAP Service Report / EarlyWatch Check usefulness?

||>
||> Anyone familiar with these reports? If so, what is your experience
||> as to it's relevance?
||>

||----------------------------------------------------------------
||Please see the official ORACLE-L FAQ: http://www.orafaq.com
||----------------------------------------------------------------
||To unsubscribe send email to: oracle-l-request_at_freelists.org put
||'unsubscribe' in the subject line.
||--
||Archives are at http://www.freelists.org/archives/oracle-l/
||FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
||-----------------------------------------------------------------
|----------------------------------------------------------------
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|----------------------------------------------------------------
|To unsubscribe send email to: oracle-l-request_at_freelists.org put
|'unsubscribe' in the subject line.
|--
|Archives are at http://www.freelists.org/archives/oracle-l/
|FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
|-----------------------------------------------------------------
|----------------------------------------------------------------
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|----------------------------------------------------------------
|To unsubscribe send email to: oracle-l-request_at_freelists.org put
|'unsubscribe' in the subject line.
|--
|Archives are at http://www.freelists.org/archives/oracle-l/
|FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
|-----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Feb 11 2004 - 07:04:00 CST