cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle 10G performance problems

Former Member
0 Kudos

Hello,

we have a lot of performance problems with oracle 10G. Especially tables scan on DRAW or AEN1 have long response times. It seems that the CBO uses the wrong strategy. The latest merge fix is already installed. Any idea to solve the problem is welcome.

Best regards

Juergen Remmert

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Juren Remmert..

We also had a lot of performance issues.We had upgraded 6 systems from 9.2.0.7 to 10.2.0.2.

As others have rightly pointed out u need to set all the parameters of Oracle in accordance with the note 830576.

This note should set right everything for u.

The <b>_(underscore)</b> parameters are very important in that.

Regards

Bharathwaj V

Former Member
0 Kudos

Hello,

it is also important to apply some optimizer and merge fixes. Especially the patches 6321245 and 6033289 are relevant.

See please the SAP Notes.

Oracle Database 10g: Patch sets/patches for 10.2.0

Optimizer merge fix for Oracle 10.2.0.2

Best regards

K.Baran

Former Member
0 Kudos

Typical reasons beside statistics are:

- Parameters (-> note 830576 has to be applied carefully)

- Changes of CBO behavior with 10g (-> note 176754)

If this doesn't solve the problem and you need a more detailed advice, please provide me with details (execution plan, SQL statement, statistics, ...).

Former Member
0 Kudos

We had similar performance issues in our environment, once we upgraded from 9.2.0.2 to 10.2.0.2.

Oracle: 10.2.0.2

SAP: 4.7x110

OS: SOLARIS 9 64bit

The above mentioned notes were very helpful. We had to install an oracle patch as well (found on marketplace) -- 6321245

and make the following oracle parameters changes:

pga_aggregate_target - 144MB (default = 25MB)

*.event="10027 trace name context forever, level 1"

*.event="10028 trace name context forever, level 1"

*.event="10162 trace name context forever, level 1"

*.event="10183 trace name context forever, level 1"

*.event="10191 trace name context forever, level 1"

*.event="10629 trace name context forever, level 32"

*.event="38068 trace name context forever, level 100"

*.event="38043 trace name context forever, level 1"

*.optimizer_index_caching=50

*.optimizer_index_cost_adj=20

*.parallel_execution_message_size=16384

*._b_tree_bitmap_plans=FALSE

*._index_join_enabled=FALSE

*._optim_peek_user_binds=FALSE

*._optimizer_mjc_enabled=FALSE

*._sort_elimination_cost_ratio=10

Remove

*.optimizer_features_enable='9.2.0'

HTH

former_member204746
Active Contributor
0 Kudos

try:

brconnect -c -u / -f stats -t DRAW,AEN1 -f collect