RE: SAP Service Report / EarlyWatch Check usefulness?
- From: Kip.Bryant@xxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 11 Feb 2004 09:41:24 -0800
Hi Jeff,
There is an explain plan built into SAP and also trace as well as more general
runtime and sql analysis tool. Maybe they don't know about these items. SAP
developers usually don't. Since these are "proprietary" bits I'm not sure I
can tell you where they are on this list. As many people know, there has been
a long standing "problem" between these two vendors... Your "basis" person
really should know these things...
Kip
|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@xxxxxxxxxx [mailto:Kip.Bryant@xxxxxxxxxx]
|Sent: Tuesday, February 10, 2004 4:55 PM
|To: oracle-l@xxxxxxxxxxxxx
|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 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
||-----Original Message-----
||From: Kip.Bryant@xxxxxxxxxx [mailto:Kip.Bryant@xxxxxxxxxx]
||Sent: Tuesday, February 10, 2004 12:15 PM
||To: oracle-l@xxxxxxxxxxxxx
||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.
|||Cheers
|||Nuno Souto
|||in sunny Sydney, Australia
|||dbvision@xxxxxxxxxxxxxxx
|||----- Original Message -----
|||From: "Thomas Jeff" <jeff.thomas@xxxxxxxxxxx>
|||To: <oracle-l@xxxxxxxxxxxxx>
|||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@xxxxxxxxxxxxx put
|||'unsubscribe' in the subject line.
|||--
|||Archives are at //www.freelists.org/archives/oracle-l/
|||FAQ is at //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@xxxxxxxxxxxxx put
||'unsubscribe' in the subject line.
||--
||Archives are at //www.freelists.org/archives/oracle-l/
||FAQ is at //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@xxxxxxxxxxxxx put
||'unsubscribe' in the subject line.
||--
||Archives are at //www.freelists.org/archives/oracle-l/
||FAQ is at //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@xxxxxxxxxxxxx put
|'unsubscribe' in the subject line.
|--
|Archives are at //www.freelists.org/archives/oracle-l/
|FAQ is at //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@xxxxxxxxxxxxx
|put 'unsubscribe' in the subject line.
|--
|Archives are at //www.freelists.org/archives/oracle-l/
|FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
