1

Let's assume there is a C++ application executing a specific SQL query on Oracle database.

This query was working fine for last couple of years in production at a customer's environment, but suddenly one fine day the query started taking around 10x more time to execute. (Assume there is constant addition of data in the tables on which this query works ).

While doing the analysis the experts found that Oracle's optimizer is not generating an optimal plan because of may reasons related to DB statistics/data skew/all the other parameters which can influence optimizer to generate sub optimal plan.

Forcing the optimizer by placing hints in the query to generate a good execution plan works perfectly.

Application development team is now being pressurised to change the application code and inject the hint in the query when it's being constructed.

Application development team doesn't want to change application code because they have hundreds of other customers who are not complaining about this specific query performance. Changing application code also means more maintenance cost as they will need mechanism in place to disable the hint it the hint is no longer needed when customer upgrades database to newer release.

The customer in question is not willing to hire a DBA who can execute SQL command to tune the query using plan baseline feature.

What are the options for application development team in this case?

Jackie
  • 11
  • 1
  • If I were you, I would add a condition for using the hint in the query in the application code, the condition can be db version or rules as such. It is not unheard of. Ofc, I am assuming the RCA is out of question here. – Mistu4u May 27 '20 at 03:32
  • What is your 4 digits Oracle version ? Are you using Entreprise Edition ? Do you have Tuning Pack and/or Diagnostic Pack licences ? – pifor May 27 '20 at 07:44

1 Answers1

0

Create a manual SQL profile to inject the hints without changing the application SQL. While this is still a "DBA" task, it only requires running a single command and is much simpler than SQL Plan Baselines.

Use the below PL/SQL block. Replace the SQL_ID, name, description, and list of hints, and then send the customer the full command.

--Large SQL statements are better loaded from SQL_ID.
declare
    v_sql_id constant varchar2(128) := '2z0udr4rc402m';  --CHANGE ME
    v_sql    clob;
begin
    --Find the SQL, it should be in one of these.
    begin
        select sql_fulltext into v_sql from gv$sql where sql_id = v_sql_id and rownum = 1;
    exception when no_data_found then null;
    end;

    if v_sql is null then
        begin
            select sql_text into v_sql from dba_hist_sqltext where sql_id = v_sql_id and rownum = 1;
        exception when no_data_found then
            raise_application_error(-20000, 'Could not find this SQL_ID in GV$SQL or DBA_HIST_SQLTEXT.');
        end;
    end if;

    --Create profile.
    dbms_sqltune.import_sql_profile
    (
        sql_text    => v_sql,
        name        => 'Some_Meaningful_Name',  --CHANGE ME
        description => 'Add useful description here.',  --CHANGE ME
        force_match => true,
        profile     => sqlprof_attr('full(a)', 'parallel(8)')  --CHANGE ME
    );
end;
/

The "best" solution would be to dig into the customer's system and find out why they are different than everyone else. Do they have some weird optimizer parameter, did they disable the stats job, etc. But if this is the only problem the customer has, then I'd just use the SQL Profile and call it done.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132