It not every time you get to improve application performance from 6 hours to 2 mins execution time, so sharing 😊.
Brief note:
Avoid deprecated hints ORDERED. See https://jonathanlewis.wordpress.com/2021/09/03/ordered-hint/. ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If it is an application without the possibility of updating the code, you can use Oracle SQL Patch.
DECLARE test_patch_name VARCHAR2(32767);
Begin
test_patch_name:=dbms_sqldiag.create_sql_patch(sql_id=>'5tqnung5sgjs6',hint_text=>'IGNORE_OPTIM_EMBEDDED_HINTS');
END;
/
To confirm optimizer using SQl Patch and details of explain plan:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_cursor(sql_id=>'5tqnung5sgjs6',format=>'Advanced'));
