RE: SAP Service Report / EarlyWatch Check usefulness?

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Tue, 10 Feb 2004 14:01:37 -0500
Message-ID: <358728A276824E419580403633AABFD0021F6082@INDYSMAIL03.am.thmulti.com>

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:

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.
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?

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?

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?

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

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.

|>
|> 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
-----------------------------------------------------------------

Received on Tue Feb 10 2004 - 13:01:37 CST