Ops Archives » Page 12 of 16 » Musings of an IT Implementor

Use Oracle 10g Segment Advisor Usage

Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It’s useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor

Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space

An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

— Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,’DD-MM-YY HH24:MI:SS’) start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner=’SYS’
AND job_name = ‘AUTO_SPACE_ADVISOR_JOB’
ORDER BY actual_start_date;

Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

— Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

— Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK(‘Segment Advisor’, :TASK_ID, :TASK_NAME);

— Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘TABLE’, ‘<SCHEMA>’, ‘<TABLE NAME>’, NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘INDEX’, ‘<SCHEMA>’, ‘<INDEX NAME>’, NULL, NULL, :OBJECT_ID);

— Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

— Check the results in two ways:
— Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

— or use DBMS_SPACE package (recommended).

SELECT
RECOMMENDATIONS RECOMMENDATION,
C1 ACTION1,
C2 ACTION2,
C3 ACTION3
FROM
TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
TASK_ID = :TASK_ID;

— Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);

Disabling Change or Deactivation of SAP Audit Logging SM19 – SAL

To disable changing or deactivation of the SAP Audit Logging (SAL) settings via transaction SM19, you can remove the authorisation object S_ADMI_FCD activity AUDA.
(see https://wiki.sdn.sap.com/wiki/display/SMAUTH/S_ADMI_FCD).

This would prevent “normal” BASIS administrators from being capable of changing or disabling audit logging, but permit you to provide an emergency user with this capability (you will need to change SAL settings in SM19 at some point!).

Populate User Groups into SU01 Authorization Group

If you have followed my previous post on how to create SAP user groups en-mass, then you may wish to know how you can assign one of these groups as the security group (as per the security group box in SU01).

Simply setup your groups as per my previous post, then use the following SQL at the database level to perform the assignment to the user account in SU01.

NOTE: The script will apply each group it finds to the user accounts. If more than one group is assigned to a user, then only one of those groups will be used. The order will depend on the order of records returned in the inline cursor.
set serveroutput on size 1000;


DECLARE
   CURSOR c_ug IS SELECT BNAME,USERGROUP,MANDT FROM USGRP_USER;
BEGIN
FOR ug IN c_ug LOOP
UPDATE USR02 SET CLASS=ug.USERGROUP WHERE BNAME=ug.BNAME and MANDT=ug.MANDT;
DBMS_OUTPUT.PUT_LINE ('User: '||ug.BNAME || ' Group: '||ug.USERGROUP);
END LOOP;

END;

SAP note 830576 – PGA_AGGREGATE_TARGET on Oracle 10gR2

SAP note 830576 “Parameter Recommendations for Oracle 10g” is quite a popular one for me.
It lists all the SAP recommended Oracle 10g parameter settings for Oracle 10.2.0.4 and 10.2.0.5.
It’s a good point of reference and I’d recommend you implement it as a baseline before tuning the system further.
It has a buddy note, 1289199 “Information About Oracle Parameters” which describes some of the parameters in more detail.

Unfortunately, there is a major flaw on note 830576. When setting PGA_AGGREGATE_TARGET the SAP note says 20% of available memory. It fails to mention that this should be 20% of the SGA size, not O/S memory.
The Oracle docs (see MYOS note 153367.1) say that the value should be:

Syntax PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value 10 MB or 20% of the size of the SGA, whichever is greater
Modifiable ALTER SYSTEM
Range of values Minimum: 10 MB
Maximum: 4096 GB – 1

The Oracle note goes on to say that when sizing the Oracle database memory areas, you should consider the SGA size first, then assign any spare memory to PGA.
Now in an SAP landscape with a single Central Instance + Dialog Instance on the same server as the database, you may wish to use the SAP 70/30 rule (70% to SAP, 30% to Oracle).

My order of sizing would look something like this:
1, Determine number of users of SAP system.
2, Determine number of DIALOG work processes + Background work processes + Update processes (~= Oracle “processes”).
3, Determine leftover memory for Oracle SGA (split between pools, SAP doesn’t support automatic memory management).
4, Determine leftover memory for PGA + overheads.

If you get to step 4 and you have diddly squat RAM left (hardly any), then consider adding more RAM to your server. Remember, we don’t like pageing.

SAP note 789011 “FAQ: Oracle Memory Areas”, provides a range of SQL statements for checking the actual size of the PGA. Since PGA_AGGREGATE_TARGET is only telling Oracle what you would like the maximum PGA allocation to be.

When you set PGA_AGGREGATE_TARGET, you also allow Oracle to release PGA memory back to the O/S. Using the *_AREA_SIZE parameters and setting PGA_AGGREGATE_TARGET to 0, forces a specific size of PGA which does not release the memory to the O/S.
/* Actual PGA consumption */ SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

/* Chronological PGA allocation (needs AWR license) */ SELECT SUBSTR(S.END_INTERVAL_TIME, 1, 40) TIME, P.VALUE PGA_ALLOCATION FROM DBA_HIST_SNAPSHOT S, DBA_HIST_PGASTAT P WHERE P.NAME = 'total PGA allocated' AND S.SNAP_ID = P.SNAP_ID ORDER BY P.SNAP_ID;

Oracle states:
Memory Area Dedicated Server Shared Server
Nature of session memory Private Shared
Location of the persistent area PGA SGA
Location of part of the runtime area for SELECT statements PGA PGA
Location of the runtime area for DML/DDL statements PGA PGA

When installing Oracle for SAP, by default it uses DEDICATED server mode (see note 70197).

Transporting SAP User Groups

I’ve blogged about SAP user groups before: https://www.it-implementor.co.uk/2011/09/sap-user-groups.html
Let’s say you’ve set them up in your DEV system and now you’re expecing to just transport them. Well, as with all things SAP, it’s never quite that easy.

Create a new workbench transport request in SE01, SE09 or SE10 (doesn’t matter which).
Then open the request at the request level by double clicking it.
Switch to change mode by clicking the pencil button:

Now manually type in the program Id, object type and object names as follows:

R3TR TABU USGRP
R3TR TABU USGRPT

Click Yes at the prompt:

On each item, click the Function button (key symbol):

Enter * in the Table Keys field:

Save the request again.

That’s it!

Release the transport and import into the next system.
This is a farily generic process that can b used to transport any table values.
NOTE: You should be aware that the “*” in the table key, means all items specific to the current client.