Even the most experienced SQL tuning developer cannot do it?

Discussion in 'Oracle' started by Richard To, Oct 2, 2019.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    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/

    shabbir likes this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice