I came across with a SQL statement from a user as a testing case to our product, the SQL is joining 4 tables with data retrieved from system table dba_objects. create table tu as select * from dba_objects; create table tv as select * from dba_objects; create table tw as select * from dba_objects; create table tt as select * from dba_objects; The user try to use /*+ use_hash(w u v t) */ optimization Hints to force the SQL to process with 4 tables Hash Join: SELECT /*+ use_hash(w u v t) */ COUNT(*) FROM tv v, tu u, tw w, tt t WHERE t.object_id = v.object_id AND t.object_name = upper(v.object_name) AND w.object_id = u.object_id AND v.created BETWEEN t.created AND t.last_ddl_time AND v.created BETWEEN u.created AND u.last_ddl_time AND t.object_id = w.object_id AND w.created = v.created； Let’s see how this hint affects the execution plan of the SQL statement. Basically, this hint failed to achieve what the user wanted to do, and the following screen shows the execution plan is the same as that of the SQL without any hint applied. The SQL takes more than 17 minutes to finish with or without the user’s hint added. Let me use the automatic SQL tuning function in our product to tune this SQL, a much better SQL with a new hint injected with execution time 0.31 second only. It is more than 3000 times faster than the original SQL statement. The following screen shows the new hint /*+ NO_USE_NL(@SEL$1 U) */ is used to pin point and solve the problem right away, there are no clumsy hints instructions, but simply tells Oracle not to use nested loop to process table TU with alias U. With this new hint, Oracle SQL optimizer avoids using nested loop to retrieve table TU, the entire execution plan finally meets user’s expectation that using Hash join to process all tables. So, even for an experienced user with in-depth knowledge in SQL tuning, it does not mean he can easily control how Oracle SQL optimizer to optimize his SQL without a right tool like this : https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/
