Adivece for Ora tuning

[Ganimede-Dignan]

Hi forum,

this is an old 4.6C system with 4 GB ram and 4 Intel/CPU

-

---

ALWAYS_ANTI_JOIN	hash
BACKGROUND_DUMP_DEST	f:\oracle\SID\saptrace\background
BITMAP_MERGE_AREA_SIZE	33554432
COMPATIBLE	8.1.7.4.12
CONTROL_FILES	( g:\oracle\SID\sapdata1\cntrl\cntrlSID.dbf,E:\oracle\SID\c
CONTROL_FILE_RECORD_KEEP_TIME	30
CREATE_BITMAP_AREA_SIZE	33554432
DB_BLOCK_BUFFERS	71680
DB_BLOCK_LRU_LATCHES	8
DB_BLOCK_SIZE	8192
DB_FILES	254
DB_FILE_MULTIBLOCK_READ_COUNT	8
DB_NAME	SID
EVENT	"10183 trace name context forever, level 1"
HASH_AREA_SIZE	4194304
HASH_JOIN_ENABLED	false
HASH_MULTIBLOCK_IO_COUNT	8
LOCAL_LISTENER	"(ADDRESS=(PROTOCOL=TCP)(HOST=128.104.1.15)(PORT=1527)(COMMU
LOG_ARCHIVE_DEST	h:\oracle\SID\saparch\SIDarch
LOG_ARCHIVE_FORMAT	ARC%s.%T
LOG_ARCHIVE_START	true
LOG_BUFFER	1048576
LOG_CHECKPOINTS_TO_ALERT	true
LOG_CHECKPOINT_INTERVAL	0
MAX_DUMP_FILE_SIZE	20000
MAX_ROLLBACK_SEGMENTS	400
OPEN_CURSORS	800
OPTIMIZER_INDEX_COST_ADJ	10
OPTIMIZER_MODE	choose
PARALLEL_BROADCAST_ENABLED	true
PARALLEL_EXECUTION_MESSAGE_SIZE	8192
PARALLEL_MAX_SERVERS	4
PROCESSES	100
ROLLBACK_SEGMENTS	(PRS_0,PRS_1,PRS_2,PRS_3,PRS_4,PRS_5,PRS_6,PRS_7,PRS_8,PRS_
ROW_LOCKING	always
SESSIONS	110
SHARED_POOL_RESERVED_SIZE	10000000
SHARED_POOL_SIZE	357019156
SORT_AREA_RETAINED_SIZE	0
SORT_AREA_SIZE	2097152
STAR_TRANSFORMATION_ENABLED	true
TIMED_STATISTICS	true
TRANSACTION_AUDITING	false
USER_DUMP_DEST	f:\oracle\SID\saptrace\usertrace

-

---

Regards.

Ganimede Dignan.

[lbreddemann · Accepted solution]

Hi Ganimede,

well, first: please upgrade to at least Oracle 9i or better Oracle 10g since the maintenance for Oracle 8i is long gone...

And yes: with a 4.6D_EXT Kernel Oracle 9i will work with that system.

The other thing is: the parameters are not only not what SAP recommends them to be, they're pretty contradictionary.

For example: STAR_TRANSFORMATION_ENABLED=TRUE should support large data volume operations in BW-Systems. To speed up I/O there the db_file_multiblock_read_count is there usually set to 32. In your system it's set to the OLTP standard value of 8. The same applies for the optimizer_index_cost_adj which is set to 10 in OLTP systems in order to favor index access over full table scans.

So, yes: please set the parameters according to the SAP recommendation.

After that you should have a closer look into the shared cursor cache to find the statements that produce most of your physical I/O and take the longest time to get finished.

I know these are pretty general advices, but a) they help often and b) performance tuning is not too trivial.

KR Lars

[former_member204746]

You failed to supply your Oracle version. From the COMPATIBLE parameter, I guess you use 8.1.7.4 with patch 12.

So, if this is the case, read carefully SAP not 124361 - Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x)

This note will help you decide if all your parameters are set properly.

What I am sure is that you are missing a lot of EVENTS:

check "10027 trace name context forever, level 1" (8.1.7 or higher)

-> Note 596420

"10028 trace name context forever, level 1" (8.1.7 or higher)

-> Note 596420

"10181 trace name context forever, level 10" (8.0.4.x where x is 2 or higher)

-> Note 114716

"10181 trace name context forever, level 1" (9.2.0.x where x is 2 or lower)

-> Note 594513

"10183 trace name context forever, level 1" (8.0.5.2 or higher)

-> Note 128648

"10191 trace name context forever, level 1" (8.0.5.2 or higher)

-> Note 128221

"38043 trace name context forever, level 1" (9.2.0.6 or higher)

-> Note 960633

"38068 trace name context forever, level 100" (9.2.0.7 or higher)

-> Note 176754