Dynamic hints in Oracle

Question

I am researching whether it is possible to use dynamic hints in Oracle. We have system for a lot of customers with different volumes of data, so we want to personalize queries for each of them.

I would like to do this:

PROCEDURE PROCESS_STEP_1 (p_cust_id NUMBER)
IS
  v_hint_value NUMBER;
BEGIN
  -- Select correct value from param table
  SELECT value INTO v_hint_value
    FROM param_table
    WHERE cust_id = p_cust_id
      AND process_name = 'PROCESS_STEP_1';

  INSERT INTO result_table  
  SELECT /*+ PARALLEL v_hint_value */ * FROM etc.

END;

I know that it's possible to do this is via dynamic queries. We had a lot of them and had to abandon them due to bad readability and other issues (those SQLs are rather complex).

Do you have any idea how something like this could be implemented? Thanks!

Answer 1

OK, I will start with a complete disavowal of this approach. I write a lot of high-performing SQL applications and very rarely use hints. The fact that you seem to be talking about a massively hinted application where the hints vary by installation is a big red flag. I recommend you try to remove the hints and address what may be underlying application design issues.

That all said, Oracle has a SQL translation feature. It is intended to let you translate SQLs from applications written for non-Oracle databases, but it can serve your purpose too. What you want to do is use this feature to "translate" hints in any inbound SQL statement before Oracle parses it.

Here is sample code to illustrate the concept and get you started. Again -- I am not recommending this, but SO is a place for answers, not lectures, so here it is...

Step 1 -- Make sure you have the privileges to do this

-- GRANT CREATE ANY SQL TRANSLATION PROFILE TO yourapplication
-- GRANT TRANSLATE ANY SQL TO yourapplication
-- GRANT USE ANY SQL TRANSLATION PROFLE TO yourapplication
-- GRANT EXECUTE ON SYS.DBMS_SQL_TRANSLATOR TO yourapplication

Create a table to store hint translations

The idea here is that you embed hints in your application like "/+HINT12345/ and that you would populate this table differently at each customer site. You can obviously get more sophisticated than this.

CREATE TABLE myapp_hint_translations ( hint_id VARCHAR2(80), hint_text VARCHAR2(800) );

INSERT INTO myapp_hint_translations VALUES ( '/*+HINT12345*/','/*+PARALLEL(4)*/');

COMMIT;

Create a PL/SQL Package to perform the hint translation

You must use the procedure names and parameters as given. They are required by the DBMS_SQL_TRANSLATOR package we'll be using.

CREATE OR REPLACE PACKAGE myapp_hint_translator IS
  PROCEDURE translate_sql( sql_text IN CLOB, 
                           translated_text OUT CLOB );

  PROCEDURE translate_error( error_code IN BINARY_INTEGER,
                             translated_code OUT BINARY_INTEGER,
                             translated_sqlstate OUT VARCHAR2 );

END myapp_hint_translator;
/

CREATE OR REPLACE PACKAGE BODY myapp_hint_translator IS
  PROCEDURE translate_sql( sql_text IN CLOB, 
                           translated_text OUT CLOB ) IS
  BEGIN
    <<hint_search>>
    FOR r IN ( SELECT hint_id, hint_text FROM myapp_hint_translations ) LOOP
      IF INSTR(sql_text, r.hint_id ) > 0 THEN
        translated_text := replace(sql_text,r.hint_id,r.hint_text);
        RETURN;
      END IF;
    END LOOP;
    -- No translation made
    translated_text := sql_text;
  END translate_sql;

  PROCEDURE translate_error( error_code IN BINARY_INTEGER,
                             translated_code OUT BINARY_INTEGER,
                             translated_sqlstate OUT VARCHAR2 ) IS
  BEGIN
    -- We are not using this feature
    NULL;
  END;

END myapp_hint_translator;

Tell Oracle to start using our translator

BEGIN
  dbms_sql_translator.create_profile(profile_name => 'MYAPP');
  dbms_sql_translator.set_attribute(profile_name => 'MYAPP',
                                    attribute_name => dbms_sql_translator.attr_translator,
                                    attribute_value => 'myappschema.myapp_hint_translator');
END;

ALTER SESSION SET SQL_TRANSLATION_PROFILE=MYAPP;

ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

-- Test some of your application and verify hints are being translated