Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Monday, September 26, 2011

Flushing Cursor SQL Plan Out of Library Cache

I can never remember how to do this.

I wanted to flush a specific SQL execution plan out of the Oracle 11g SQL Library Cache so that I could try and compare a before and after SQL execution scenario using SQL trace and then tkprof'ing it.
Here's the link to the blog that helped me remember again:


Friday, September 23, 2011

Enable SAP* in Netweaver 7.0

If you've lost the Administrator password or you want to log in as SAP* in your pure Java stack environment, this can be a little tricky as the way to set the password for the SAP* user in configtool is not as user friendly as you might think.

Log into configtool by opening an X-Windows session (on UNIX), and then execute: /usr/sap/<SID>/JC<SYS#>/j2ee/configtool/ (.bat on Windows).

NOTE: Once you enable the SAP* user, you will not be able to log into the system as any other users e.g. Administrator or J2EE_ADMIN.

In the configtool screen, expand the “Global server configuration -> services” branches:

Click the “” item:

On the right hand side, scroll down to the ume.superadmin.activated option:

Set the “Value:” field to “TRUE”:

Now single click the “ume.superadmin.password” item:

You can’t see the password and the “Value:” field looks like it doesn’t accept input, but it does.
Type the new password in to the “Value:” field at the bottom (even though the cursor doesn’t move):

Now click “Set” on the right:

You will be prompted to re-enter the password:

Click Save:

You should restart the J2EE stack before trying to log in as SAP*.

Monday, September 19, 2011


There are various methods of tuning an SAP system, but sometimes the operating system vendor will permit specific tuning so that the SAP system can take better advantage of the available resources.
Usually, you can find whitpapers on the O/S or hardware vendor's web site, but also SAP will themselves develop a range of SAP notes that will offer avice and guidance when running on certain approved hardware partners.
If you're using HP-UX 11iv3 (11.31) and you are running SAP, then here are some gems direct from SAP themselves:

172747 HP-UX OS Parameter Recommendations
798194 HP-UX async IO trace files
837670 HP-UX OS Patch Requirements
918279 HP-UX SAPOSCOL Versions
1077887 HP-UX filesystem options
1329878 Using non-default pagesize on HP-UX
1351051 Support of Oracle on HPVM
1457063 HP-UX Consolidating SAP Systems
1575609 Future HP-UX support for SAP on Oracle

I would highly recommend the OS Parameters note.
If you're suffering I/O performance issues, make sure that you have set the correct block size as per 1077887.
A huge (>10%) reduction in memory usage can be obtained following 1329878, but only if you are using Oracle 11g.

Using the direct I/O option alone on a VxFS 5.0 environment decreased Data Protector backup times by over 1 quarter e.g. a 1.5 hour backup reduced to ~1 hour.

I haven't investigated the HPUX_SCHED_NOAGE O/S parameter option yet (I need the audacity to recommend it to the server team!), but according to this excellent blog post by Christian Bilien, it should help your environment significantly if you're running more than one Oracle database on a SMP system.
My reasoning is that there will be more than one CPU hungry Oracle thread and they could each be battling against each other (ageing each other out) even more so in a well tuned system with less I/O (large SGA/PGA and well tuned SQL) meaning less voluntary context switches and more forced ones. It’s possible that in this situation, without HPUX_SCHED_NOAGE, you could start to see CPU bottlenecks.

HP also produce their own performance recommendations for running Oracle.
Here's one on the HP recommended filesystem IO options.
Notice that it says if you're using VxFS v5.0, you don't need to worry about 1KB block sizes on Redo and Archive Log partitions.

WARNING: Take heed in the warning about Progress databases on file system partitions with directio enabled on the mount options.  I have personally experienced issues where Progress applications have had a 20x decrease in performance!  It's a fact, Progress is not very good without the file system cache.

This one is specific to tuning HP-UX TCP/IP.  Very useful if you're seeing networking bandwidth problems in you environment.

Finally, the HP-UX Performance Cookbook provides an excellent source of information.
I have noticed that some of the SAP Oracle recommended parameters (830576 "Parameter Recommendations for Oracle 10g") are specific to HP-UX, I have found that a good set of descriptions for some of these are covered in this HP Oracle paper: The Oracle Database on HP Integrity servers.

It's really a trade off between what SAP say and what Oracle say.
Obviously Oracle know there own database system, so anything Oracle says can also be included.
I generally follow the top down rule, where SAP overrides anything that is contradictory from HP or Oracle, Oracle overrides anything contradictory from HP, and I only implement any parameters from HP if neither Oracle or SAP have anything to say on the matter:
- HP (OS)
 - Oracle (RDBMS)
  - SAP

Saturday, September 10, 2011

SAP User Groups

Apart from the roles, profiles and authorisation objects involved in SAP security controls, there is also an additional level.  User groups.
The user groups in an SAP system can be used to control access to certain authorisation objects (i.e as a restriction in a profile), or used as a method of tagging different types of users to permit certain types of administrative delegation.
Therefore permitting a super set of users to administer passwords for a smaller sub-set of users of a certain user group.

So how do you create user groups?  Use transaction SUGR to define the groups, then assign the users in SU01 or SU10.
Take a look at authorisation object S_USER_GRP.

Monday, September 05, 2011

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
- DML time (INSERT, UPDATE etc).
- Stats generation time.
- Segment space (hard disk).
- Structure validation time.
- Recovery time.

Removing the index may not be an option.  But what if it's not actually used!!

How can we tell if it's used?
Well you could monitor all SQL in the shared pool using the AWR capture script, or you could use Jonathan's script to see if the index segment(s) has been scanned (full scan).  But these don't comprehensively give you a definitive answer.
There could be holes in your monitoring.

Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here:

Turn on:
alter index <INDEX> monitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;

Turn off:
alter index <INDEX> nomonitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;

Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;

Or use query below to see all monitored objects:

SELECT owner , index_name , table_name ,
DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring ,
DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used ,
ou.start_monitoring start_monitoring ,
ou.end_monitoring END_MONITORING
sys.user$ u ,
sys.obj$ io ,
sys.obj$ t ,
sys.ind$ i ,
sys.object_usage ou
i.obj# = ou.obj#
AND io.obj# = ou.obj#
AND t.obj# =
AND u.user# = io.owner#;

Thursday, September 01, 2011

Basic Performance Tuning Guide - SAP NetWeaver 7.0 - Part IV - ABAP Runtime Analysis

This is the final part of my SAP NetWeaver 7.0, Basic Performance Tuning Guide.
You may wish to review Part I, Part II or Part III.
In this part I will focus on the use of ABAP Runtime Analysis to check performance of ABAP programs/reports.  This will help you locate areas of ABAP that are eating up valuable response time.

Execute transaction SE30.
Enter the transaction (or program or function module) that you wish to analyse:

Edit the "Measurement Restrictions" variant to either create your own, or simply adjust the DEFAULT.
This will allow us to display additional information which can be very useful when trying to determine performance problems.

Tick the "DB-level ops" option on the Statements tab:

On the Duration/Type tab, select "None" for Aggregation and tick "With memory use":

The transaction or program you entered will be run, but you should see a prompt at the bottom of the screen telling you that the measurement process has started:

Perform the actions you need in the transaction/program.
Once you have finished, simply click Back (exit from the transaction) to the SE30 main screen:

The SE30 screen will display a prompt showing the analysis has finished:

The trace data is stored in a file on the SAP instance server.
This can be seen by clicking the "File Info..." button:

You can see the file name and path:

If you need to load the trace data again for analysis, you can simply click the "Other File..." button on the main screen.
It's also possible to transfer the analysis files via RFC to another SAP system from the "Other File..." sub-screens.
This is very useful if you want to compare performance between two systems.
NOTE: The performance data files are only retained on the server for eight days.  I haven't found a way of extending this.

Let's analyse the stats.
Click the Evaluate button:

You will see a basic breakdown of the transaction runtime performance analysis in histogram form.
The three different areas are shown (ABAP, Database and System) against time (as a percentage):

More details on this screen can be found here:

The values shown correlate to the response times measured in the single record statistics plus the time required for analysis.
Note that DATABASE time above, also includes the time the system takes to open and close cursors etc, not just the SQL runtime.
For reference, the STAD output for the same operation (notice the slightly lower DB req time):

SAP STAD output DB request time

The most interesting buttons at the top of the SE30 screen are "Hit List", "Table Hit List" and "Call hierarchy".
 Click the Hit List button:

STAD output hit list

The Hit List will be displayed in descending order (gross time):

STAD output hit list output

The columns are defined as follows:

"No." - Shows the number of times a call was made.
If a "SELECT" is performed in a loop, then this will show the number of times the call was made in total in the loop.

"Gross" - Total execution time in microseconds (running bottom to top of the hit list).
"Net" - The time in microseconds for the specific call(s)
"Call" - The program component that was being executed.
"In program" - The main program of the component.
"Type" - The type of call (DB, System, Non-system).

SAP says here
"Gross time
The total time required for a call. This includes the runtime of all modularization units and ABAP statements called by the subject.

Net time
The net time is the gross time less the time required for modularization units (MODULE, PERFORM, CALL FUNCTION, CALL SCREEN, CALL TRANSACTION, CALL METHOD, CALL DIALOG, SUBMIT), and for the ABAP statements listed separately. For statements such as APPEND, the gross time is the same as the net time.

In this view, you are looking for records in the list with the larger "Net" value.
This indicates that a large amount of overall time was spent in the call/program listed.

If the Gross and Net time fields do not hold the same value for a record, then double clicking the record will display the sub-components and switch to the hierarchy view:

STAD output hit list sorting

At any time, you can single click a record, then jump to the ABAP source by clicking the "Display Source Code" button:

STAD output hit list call point in ABAP

STAD output hit list call point in ABAP

NOTE: I always recommend you enable the "New" ABAP editor.  It gives you visibility of line numbers and highlights the results of text "Find" operations so you can see what's been found.

Back on the main analysis screen, select the "Table Hit List" button:

The list of tables used in the execution of the transaction/program are displayed in descending order of runtime:

STAD output hit list tables used

The #Acces column, displays the number of times the table was accessed (read/write) and corresponds to any loops you may have seen in the "Hit List" screen No. column.

The Class column shows TRANSP (Transparent Table), VIEW or POOL (cluster table).

Double click a table to display the call points, from which you can jump to the ABAP source.

Finally, from the main analysis screen, click the "Call hierarchy" button:

The Hit List that you have already seen, will now be displayed in a hierarchy with sub-calls indented under the parent call:

STAD output hit list call hierarchy

The only real difference between this view and the Hit List table view, is the indenting.
You can still double click to display the execution time overview for that sub-call, or use the "Display Source Code" button to jump to the ABAP source.

In my example screen shots, you will have seen that the PMSDO fetch is taking a large amount of the response time.
I was able to find that this operation was inside the GET_DATA function and check the ABAP code.

The main thing you are looking for in poor performing ABAP code, is where does the execution time get eaten up.  As a side note, you may also check the amount of memory used if you are looking to resolve TSV PAGE ALLOC errors.

Try sorting the Hit List by "Net Time" to find the individual components with the highest net execution time.
Check if the operation is a database related call or not.  You can then use Part III to trace this further with an SQL trace.
Check the number of loop iterations (The "No." column) in the "Hit List". Are you performing an expensive routine too many times in a loop?

Validate that the code is not doing too much work using iTabs, when some of the work could be done in the database.
The database is the ultimate place to do sorting, grouping and record elimination.
Don't pull back more records than you need, only to loop through an iTab in ABAP and remove them, it's more expensive.

That's it for now.  Thanks for reading.