oracle parameter hash_join_enabled is setvalueto false(recmd value is true)

[Former Member]

Hi,

Iam getting error it showing red mark in 2 lines

1)oracle:parameter hash_join_enabled is set value to false(recommended value is true).

2)oracle:parameter star_transformation_enabled is set to value false(recommended value is )

Please guide me for the same.

Thanku

[former_member204746 · Accepted solution]

1. you do not tell us what is your Oracle version

2. you do not tell us which SAP products you are using

3. you do not tell us where you waw this message

if using Oracle 9i, check note 124361

if using Oracle 10g, check note 830576

when these are setup properly, adapt transaction DB17 to reflect the SAP note for your Oracle version.

[Former Member]

Using the relevant parameter notes for the affected system (Oracle 9i and lower: Note 124361 and 180605 (BW); Oracle 10g and higher: 830576), check whether the settings (<curr_val>) you are using are actually correct. If so, use transaction DB17 to change the <check_val> test values for the affected parameters to the correct <curr_val> value, to avoid future unjustified warnings. Alternatively, you can also deactivate or delete the checks in DB17 (Active = "N").

If warnings are issued correctly, you must adjust the parameter settings in init<sid>.ora (and, if necessary, the other configuration files included there using IFILE).

Ref NOte 192055

[Former Member]

Oracle can only perform a hash join for an equijoin. Hash join is not available with the RBO. You

must enable hash join optimization, using the initialization parameter HASH_JOIN_ENABLED (which can

be set with the ALTER SESSION statement) or

the USE_HASH hint.

To perform a hash join, Oracle performs the following steps:

1. Oracle performs a full table scan on each of the tables and splits each into as many partitions

as possible based on the available memory.

2. Oracle builds a hash table from one of the partitions (if possible, Oracle selects a partition

that fits into available memory). Oracle then uses the corresponding partition in the other table

to probe the hash table. All partition pairs that do not fit into memory are placed onto disk.

3. For each pair of partitions (one from each table), Oracle uses the smaller one to build a hash

table and the larger one to probe the hash table.