If more than several hints should be added to the issue SQL, the sql patch is not suitable due to line size limitation (241).
Also if the sql has no ideal plan in the memory nor AWR, direct updating hint set in SQL Profile will be one of options.
#1. connect the db with sysdba user and run the coe_xfr_sql_profile.sql with the following method.
i.e.
$sqlplus / as sysdba
sql>@coe_xfr_sql_profile.sql 5xppvgwhp5rdr 482745184
Then it generates the following file.
coe_xfr_sql_profile_5xppvgwhp5rdr_482745184.sql
Open this file with some editor. And go to outline data section.
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
...
q'[LEADING(@"SEL$5428C7F1" "PAY_PAC"@"SEL$1" "POG"@"SEL$1" "PAP"@"SEL$1" "PR"@"SEL$2" "PRE"@"SEL$1" "PAY_REL_GROUPS_DN"@"SEL$3")]',
q'[USE_NL(@"SEL$5428C7F1" "POG"@"SEL$1")]',
q'[USE_NL(@"SEL$5428C7F1" "PAP"@"SEL$1")]',
q'[USE_NL(@"SEL$5428C7F1" "PR"@"SEL$2")]',
q'[USE_NL(@"SEL$5428C7F1" "PRE"@"SEL$1")]',
q'[USE_NL(@"SEL$5428C7F1" "PAY_REL_GROUPS_DN"@"SEL$3")]',
....
q'[END_OUTLINE_DATA]');
If the query needs new join ( PR beforE PAP) and hash join on PAP, PR, PRE and PAY_REL_GROUPS_DN, the following will be worked..
q'[LEADING(@"SEL$5428C7F1" "PAY_PAC"@"SEL$1" "POG"@"SEL$1" "PR"@"SEL$2" "PAP"@"SEL$1" "PRE"@"SEL$1" "PAY_REL_GROUPS_DN"@"SEL$3")]',
q'[USE_NL(@"SEL$5428C7F1" "POG"@"SEL$1")]',
q'[USE_HASH(@"SEL$5428C7F1" "PAP"@"SEL$1")]',
q'[USE_HASH(@"SEL$5428C7F1" "PR"@"SEL$2")]',
q'[USE_HASH(@"SEL$5428C7F1" "PRE"@"SEL$1")]',
q'[USE_HASH(@"SEL$5428C7F1" "PAY_REL_GROUPS_DN"@"SEL$3")]',
Once modification is finished, the file(coe_xfr_sql_profile_5xppvgwhp5rdr_482745184.sql) should be saved to apply the environment.
Note. The above outline is made for example. not real one.