Oracle SGA

1. SGA components
- db buffer = DB_CACHE_SIZE
- shared pool = SHARED_POOL_SIZE
- redo log buffer = LOG_BUFFER
- large pool = LARGE_POOL_SIZE
- jva_poo – JAVA_POOL_SIZE

2. New view in new ST04 under Peformance > Stats Info > SGA

3. Dynamic SGA
- allows resezing SGA components ( buffer, shared pool and large pool)  without restarting at runtime. The others SGA components need to be restarted.
- sga_max_size = maximum size for total SGA. If not defined, the value is the total sga size.
- large sga will cause swapping.

4. SGA info
- V$SGA_CURRENT_RESIZE_OPS  = Current SGA size change operation(s)
- V$SGA_RESIZE_OPS = List of the last 100 SGA size change operations carried out
- V$SGA_DYNAMIC_COMPONENTS = Display detail info and history
- V$SGA_DYNAMIC_FREE_MEMORY = Display of the memory available for future SGA enhancements

5. Tuning DB buffer
- Cache size advice based on data buffer quality/hit ration and V$DB_CACHE_ADVISE (parameter DB_CACHE_ADVICE = ON or STATISTIC_LEVEL = typical ).
- SQL>ALTER SYSTEM SET STATISTICS_LEVEL=’TYPICAL’;
- high hit ration doesnt mean good sizing, as it could be due to expensive SQL. if ration buffer get : user call  > 15, hit ratio is   not a good indicator. Check for log read/user call value.ST04 and ST04OLD use diff method to collection of hit ratio.
- use ST04, cache advisory tab, the check the values of physical read for a range of cache size. Objective is to have less phy     read or until the phy read is constant after adding more cache size.

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/memory.htm

6. Tuning Shared pool
- If the shared pool is too small, then extra resources are used to manage the limited amount of available space.  This consumes CPU and latching resources, and causes contention and possibly ORA-4031
- Use v$shared_pool_advice
- Check the freepool in v$sgastat to see free memory

Oracle PGA

PGA
1. Memory privately assigned Oracle server process / shadow process.
2. Available PGA is “work area”, ensure sufficient amount available.
3. If insufficient, db used temp tbs – resulting in slow perf due to I/o – one pass or multi pass.
4. Manual PGA parameters: sort_area_size, hash_area_size, bitmap_merge_size, create_bitmap_area_size
5. Auto PGA – by setting WORKAREA_SIZE_POLICY= AUTO | MANUAL. Pros – simple admin, dynamic allocation, freeing, efficient mem.
6. PGA_AGGREGATE_TARGET specify max PGA thru auto allocation.
e.g SQL>alter system set pga_aggregate_target=1024M;
7. V$PGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE_HISTOGRAM
8. Estimation – for oltp PGA_AGGREGATE_TARGET = <Total physical memory> * 20%.
9. V$PGASTAT – info on max,current pga info.
- ‘aggregate PGA target parameter’ = max auto pga value set.
- ‘over allocation count’ = # of times pga agg targer is exceeded. If >0 , mean pga too small.
- ‘cache hit percentage’= efficiently of pga. should be near 99%.

Tuning PGA
1. Min PGA aggr value:
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;

2. v$pga_target_advice; = ESTD_OVERALLOC_COUNT must be zero

3. multipass, onepass or optimal can be monitored from ST04 PGA monitor- status – workarea executions.

Memory Management 101 – WebAs ABAP

1. SAP memory area
- em/initial_size_MB = overall em, set large enough.
- ztta/roll_extention = overall em limit per workprocess.
- abap/heap_area_dia & abap/heap_area_nondia = limits for heap usage for dia/non dia. If set higher, OS swap be used.
- abap/heap_area_total = limits for all dia and non-dia heap usage
- abap/heaplimit = to allow wp restart AFTER it completed execution to free memory, usually small.
- rdisp/PG_SHM & rdisp/PG_MAXFS = shm 10% or maxfs
- rdisp/ROLL_SHM & rdisp/ROLL_MAXFS = maxfs can be set to big.
- SAP buffers as seen in ST02

Note:
- Both em and heap uses swap space, ensure swap file is increased correspondingly.
- For unix ensure shm pools (e.g HP-ux pool 10 and 40) are enough.
- Note 425207 SAP memory management, current parameter ranges

2. ST02 storage report
- Storage shared between wp = total usage for sap buffers. they take hp-ux pool.
- user storage for each wp = mem of each wp x total number of wp
- size of ext = em/initial_size_MB set.
- virtual mem allocated = mem req for sap.

3. Allocation sequence
- DIA = roll area > em > heap
- NONDIA = roll area >heap > em
- Use RSMEMORY to redefine seq. (0 – Roll area, 1 – Ext Memory, 2 -  Heap)
- SAP Note 177226 Documentation rsmemory

Configure Oracle 10g EM / DBconsole

1. emca -repos create ( create sysman schema )
2. emca -config dbcontrol db
3. To start or stop : emctl start | stop | status dbconsole
4. Default path is http://hostname:1158/em/dbconsole

Resource
1. http://www.oracle.com/technology/obe/2day_dba/gettingstarted/gettingstarted.htm
2. 355770 – Oracle Enterprise Manager Database Control
3. http://technopark02.blogspot.com/2008/02/oracle-10g-setting-up-oracle-enterprise.html

Oracle Consistency Check

SAP Notes

a. 540463 – FAQ: Consistency Checks + Block Corruptions
b. 23345 – Consistency check of ORACLE database

1. Analyze table validate structure cascade
Runtime: long – locks the table examined currently against change accesses
Only feasible in times of minimal work load or – even better – when the SAP system has been stopped
starting with Oracle 9 the extension ‘analyze table validate structure cascade ONLINE’; does not lock the table anymore checks tables and indexes if an analyze (online or not online) is running on a table and you try to alter storage parameters, parallel degree etc from this table then all further sessions accesing the table with write or read access are locked until the analyze is finished. Further details can be found in note 619188. reads the blocks to be checked in the SGA, causing possible temporary deterioration in the buffer quality of the DB block buffer does NOT check data stored in lob columns

2.Export
Runtime: medium – feasible in a running system. Performance loss as a result of the export processes. If you access extensive tables in write mode during export, there is a danger of ORA-1555 “snapshot too old”. However, you can prevent this by sufficiently dimensioned rollback segments only checks tables certain kinds of corruptions are exported without an error occuring. If you later try to reorganize this type of table (export/import), you will have problems during import. reads the blocks to be checked into the SGA which is why the buffer quality of the DB block buffer is adversely affected for a short while.

3. dbverify
Runtime: short – feasible in running operation (UNIX: all oracle releases; Windows Oracle9 or newer)
not only checks tables and indexes but also blank DB blocks does not check cross references between tables and indexes . Reads the blocks WITHOUT loading them in the SGA, which is why the buffer quality of the DB block buffer is not affected the only procedure that is also possible on data files restored from a backup (without these files having to belong to a DB) does together with blockchecksums according note 923919 an extended check on blocks and can then find special kinds of cross reference corruptions because those may cause wrong checksums. Wrong cross references caused by lost writes do NOT lead to wrong checksums and therefore cannot be detected.
Also cross reference failures already occuring in main memory BEFORE the checksum for the table and index blocks are calculated cannot be detected because the checksums base already on bad data.
Anyway dbverify together with blockchecksums are a considerable alternative if an analyze table validate structure cascade on the complete DB lasts to long.

BlueBanc issue

1. Use “cleanipc <SysNr> remove” as root after shutdown.
2. Problem due to “ipc/shm_psize_30″ is set, only pool 10 and 40 can be set.

Oracle Memory 101

1. SGA consists of
a. Buffer Pool: DB_BLOCK_BUFFERS (unit: Blocks) or DB_CACHE_SIZE when you use the dynamic SGA.
b. Shared Pool: SHARED_POOL_SIZE ( refer SAPnote 618868/690241)
c. Java Pool: JAVA_POOL_SIZE.
e. Large Pool: LARGE_POOL_SIZE.
f.  Redo Buffer: LOG_BUFFER

2.  The PGA allocation is dynamic and can be affected by the parameters SORT_AREA_SIZE, HASH_AREA_SIZE,
BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE or PGA_AGGREGATE_TARGET when you use the automatic PGA administration

3. V$DB_CACHE_ADVICE – size advisor

4. SGA_MAX_SIZE – dynamic SGA. Key SGA components can be altered dynamically – buffer cache, shared pool and large pool.

ST04 Rules of Thumbs

1. Data buffer quality  >95% – meaning less physical read from disk.
2. User/Recursive Call > 2. Recursive call will get more over time.
c. Read/User Call < 30. If > 30 means expensive SQL statements.
d. Time/User Call < 15ms.
e. Busy time & CPU time ratio 60:40 ratio. Higher ratio means tuning required.

Java engine Memory Management

Do not set exceptionally high max heap value for Java server process !

The reason why big heaps (4g for example) are not recommended is that the bigger the heap the longer it takes for the garbage collector to run through it. So there is a trade off between less gc-s in the beginning when the JVM is started and much longer ones, when the heap is for example half full. Therefore, larger max heap doesnt related to better performance. If you have huge resource, you add more server nodes you can scale more easily and serve a larger number of users.

a. 723909 – Java VM settings for J2EE 6.40/7.0
b. 876722 – Java heap size doesn’t update or appears twice in cofig tool

ST06 Rules of Thumb

1. CPU usage ideally < 80%. Check for peak hour usage. Use ST06N for historical figures
2. Swapping ( UNIX – swap out < 20% RAM size/hour, Windows page in < 25% RAM size/hour)
3. ST03N – processing time > 2x CPU time indicated CPU bottleneck

WebAS ABAP Performance Monitoring

RESPONSE TIME = roll in + wait + load/gen + database + processing + roll wait + enque + etc 

 a. Wait time = queue time to wait for free work process (DIA) in SM50. Should be < 50ms, else it is an indication of not enough DIA wp in SM50 or all DIA tied to long running programs. Ensure the extended memory full is sufficient before adding more WPs.

b. Roll in/out time = time copy user context to/from (information related to a transaction like internal table, parameters, screen list) the work process memory. Should be <20ms, else tuning the roll memory and extended memory.

c. Load time = time to load and generate program and screens from the SAP buffers. Should be <50s, else is an indication of too small SAP program buffer.

d. Database time = Time to read or insert to the database. DB time should be <40% of total response time. High DB time indicates incorrectly tuned database, I/O disk problems in db or most likely slow/expensive SQL statements. If it is caused by customized report, you should optimize the ABAP codes (ST05 to execute ABAP SQL trace).

e. Processing time. If processing >>> 2x CPU time, is an indication of CPU bottleneck.
CPU time is not part of total response time !

f. Roll wait time = time to wait for reply from RFC connections. Roll wait time is more or less equals to GUI time, as SAPgui is considered an RFC from SAP apps server point of view. Roll wait time > 200s indicates slow network or PC.

g. GUI time = time used for data transfer from SAP server to SAPgui workstation. It includes the time in the LAN/WAN. There could be several roundtrips to complete for transaction. Long GUI time can indicate network performance (WAN/VPN) or slow processing PC. I’m still trying to get a good definition of the diff between GUI time ang Network FE time.

h. Enqueue time = time for wp to set an enque (lock row). Should be minimal.

LiveCache 101

1. Livecache users :
    a. control (db level, DBM user/operator) – admin
    b. superdba (db level, SYSDBA user) – used in sqlstudio
    c. sap<lcsid> (db level, schema livecache owner)
    d. sdb (os level, group sdba)
    e. lcdadm ( os level, group sdba)

2. LVC contains OMS for LCA routines. Stored in memory i.e data cache. During checkpoint/stop, cache written to disk. LCA routines is store procedures. No I/O

3. ONLY LC10 MUST be used to stop and start Livecache

4. Log files :
    a. knldiag, knldiag.old and knldiag.err (system msg)
    b. lcinit.log (start/stop)

5. Directory structure consists of database independent and dependent
    – Dependent path – /sapdb/SID/db
    – Independent data path – /sapdb/SID/ (dbmcli dbm_getpath IndepDataPath)
    – Independent program path – /sapdb/program (dbmcli dbm_getpath IndepProgPath)
    – Run directory -  /sapdb/data/wrk/SID
   – Data/log volume – /sapdb/LCD/data or log

6. Three connections LCA for APO, LDA for ATP & LEA

7. Memory Areas:
   a. CACHE_SIZE – persistent allocated when startup. Swap to data file if cache size is too small
   b. OMS_HEAP_LIMIT – local memory

Heap usage monitoring /SAPAPO/OM_LC_MEM_MEMORY.

MaxDb dbmcli commands

a. dbmcli -d <SID> -u control,<pwd> – Login
b. dbmcli -d <SID> -u control,<pwd> db_offline|db_state|db_online – Shutdown
c. dbmcli db_enum & sdbuninst -l – Identify instance
d. dbmcli dbm_getpath IndepProgPath
e. sdbverfiy – Verify installation

Oracle Recovery Scenarios

1. Recovery commands: 

recover database Complete recovery Require full set of archived logs generated during the online backup

recover database until cancel Incomplete recovery. Need not apply full set of archived logs, and thus will cause some data loss. Must “alter database open resetlogs” to open the database.

recover database using backup controlfile / recover database until cancel using backup controlfile Use this if all the current control files are missing or the control file version is different from the datafile and online redo logs version. If there is no copy of backup controlfile, it must be manually created. Require full set of archived logs generated during the online backup Can be complete / incomplete recovery – depending on “until cancel” statement. Must “alter database open resetlogs” to open the database.

 2. “resetlogs” and “noresetlogs” options only apply when recover statement has “until cancel” or “using backup controlfile”.

3. Effects of “alter open database resetlogs”

• Erase content of online redo logs (orig and mirr)
• Reset log sequence number – new generation of database starting from loq sequence 0
• Reinitializes the control file data about online redo logs and redo threads.
• Creates the online redo log files if they do not currently exist

LiveCache Admin Tcodes

1. DB59 – check all LCA,LDA,LEA connections
2. LCA03 – consistency check
3. /SAPAPO/OM13 – analysis and check
4. /SAPAPO/OM16 – view Livecache content
5. LC03
6. more to come….

LiveCache 7.6 Patch Strategy

LiveCache consists of 3 key components:

• LCAPPS – this is the part of the ABAP stack software components
• LCABUILT & LiveCache kernel – they come together in a patch level. The LC kernel is identified by 7.6.xx Built XX and will have a corresponding LCA Built, for example 50.xx PL xx.

Higher LCA Built is possible for lower LCAPPS, not conversely. Check from https://www.sdn.sap.com/irj/sdn/livecache for dependencies

Installation is pretty straighfoward, steps are similar to a new installations. You will need to stop both Livecache (and X_server) and SAP SCM first. Execute LCA03 after patching for consistency checks.

a. 824489 – LCAPPS patch strategy
b. 875662 – Importing liveCache version 7.6