Last Resort

Sometimes one has to come up with a fast solution to a tricky problem. In this case, I had a SQL query with a massive amount of joins due to a poorly designed database model in the source. The query was executed on Oracle 19c Standard Edition 2, connecting to Oracle 11g Standard Edition 2, over a database link. It took about 4-5 minutes.

One of the first things that was discovered was that SQL actually performed quite well if one used hardcoded literals instead of bind variables.

Literals -explicit values that are directly included in the SQL statement

SELECT * FROM employees WHERE department = 'Sales';

Bind variables – placeholders, the actual values are provided at the time the query is executed

SELECT * FROM employees WHERE department = :dept;

The common rule is: Always use bind variables, or your performance will suffer. But using binds in this case makes the performance much worse. Why?

If you don’t upgrade the Oracle database, you will hit bugs. In this case, the source is on an unsupported version, 11g. There are plenty of bugs related to statistics, histograms, bind variable peeking, and so forth. On top of that, the query uses a database link.

The correct thing would, of course, be to upgrade, get those statistics working properly, and so on. But that is not an option in this situation. Rewriting the SQL from the bottom to the top on a non-normalized database would probably take some time, without a guarantee of not hitting the same bugs.

Parallelization, materialized views, partitioning, SQL Plan Management, AWR, etc., are all enterprise features.

So, lacking the luxury of the Oracle Enterprise Edition did not make it any simpler. But I knew that the SQL with the hardcoded literals performed well. So, if one could use the same execution plan for my slow queries (using binds).

In 11g, there is a deprecated functionality called STORED OUTLINES, which basically makes it possible to make my slow query use my fast execution plan. That solution works great locally on the same instance, but it does not work with a distributed query, a.k.a. database link.

So the only thing I could come up with for a fast solution was to use HINTS. Basically instructing the optimizer to choose a specific path.

SELECT /*+ INDEX(emp emp_dept_idx) */ employee_name, department_name
FROM employees emp
JOIN departments dept ON emp.department_id = dept.department_id;

In this example, the hint /*+ INDEX(emp emp_dept_idx) */ instructs the optimizer to use the index emp_dept_idx on the employees table.

The execution plans for both SQL statements were very complex. Comparing them step by step would take a significant amount of time… time that I don’t really have.

Last resort, the last trick in the book for a fast solution to the problems would be to use the outline hint from the fast SQL and apply that to the slow SQL.

Outline hint?

Let’s add some tables and an index

-- Create departments table
CREATE TABLE departments (
  department_id NUMBER GENERATED ALWAYS AS IDENTITY,
  department_name VARCHAR2(100),
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

-- Create employees table
CREATE TABLE employees (
  employee_id NUMBER GENERATED ALWAYS AS IDENTITY,
  employee_name VARCHAR2(100),
  department_id NUMBER,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id),
  CONSTRAINT employees_fk_dept FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

-- Create index on employees table
CREATE INDEX emp_dept_idx ON employees (department_id);

Now, lets execute a query using the emp_dept_idx, and have a look at output of the section below “Outline Data”(I have cut the rest of the output).

SELECT /*+ INDEX(emp emp_dept_idx) */ employee_name, department_name
FROM employees emp
JOIN departments dept ON emp.department_id = dept.department_id;
no rows selected

SELECT * FROM TABLE ( dbms_xplan.display_cursor( format => 'ADVANCED') );

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$58A6D7F6")
      MERGE(@"SEL$1" >"SEL$2")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      FULL(@"SEL$58A6D7F6" "DEPT"@"SEL$1")
      INDEX(@"SEL$58A6D7F6" "EMP"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
      LEADING(@"SEL$58A6D7F6" "DEPT"@"SEL$1" "EMP"@"SEL$1")
      USE_NL(@"SEL$58A6D7F6" "EMP"@"SEL$1")
      NLJ_BATCHING(@"SEL$58A6D7F6" "EMP"@"SEL$1")
      END_OUTLINE_DATA
  */

I used the same technique for my much more complex but faster query, and simply copied and pasted the outline data to my slow query. Now it takes about 10 seconds to execute. For me this is the last resort, and only a temporary solution, that’s needs to be closely watched all the time.

Upgrade your database, regular database maintenance, use the features the customer paid for, and DO A PROPER DATABASE MODEL.

Over&Out