​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama

An quick introduction to tuning SQL and using hints 


The number one reason why SQL is non-performant has to be due to missing indexation on the key tables involved in the query. So the first thing you should do when looking at tuning is to ensure that your tables are indexed correctly.

In general you should be using COST based optimisation in Oracle rather than RULE based as it’s possible that the latter will be phased out in future versions. To enable COST based optimisation ensure the OPTIMIZER_MODE initialisation parameter is set to its default value of CHOOSE or for a session use the ALTER SESSION command with OPTIMIZER_GOAL set to ALL or FIRST_ROWS. For an individual SQL statement use the ALL_ROWS or FIRST_ROWS hint (more about hints later). COST based optimisation relies on statistics so it’s essential to gather these on all tables and indexes referenced in SQL that needs tuned. You can do this using the ANALYZE command. 

​e.g 

ANALYZE TABLE emp  COMPUTE STATISTICS
for table for all indexes for all indexed columns;
 

In my experience I've found sometimes that using ESTIMATE can actually slow down some queries therefore I strongly suggest using COMPUTE to get a fully accurate set of statistics. It may take a lot longer to gather the data but it's well worth it. Hints are suggestions that you can give to the optimiser for ensuring your SQL runs as efficiently as possible. Hints are put inside 'C'-like comment blocks and immediately after the SELECT, UPDATE or INSERT statement.

Here are a few examples. 

SELECT /*+ ALL_ROWS */ name,dept FROM manager WHERE sal > 10000 

The ALL_ROWS hint tells the optimizer to choose COST based optimisation with the aim of best throughput. 

SELECT /*+ FIRST_ROWS */ name,dept FROM manager WHERE sal > 10000 

The FIRST_ROWS hint tells the optimizer to choose COST based optimisation with the aim of best response time. 

SELECT /*+ CHOOSE */ name,dept FROM manager WHERE sal > 10000 

If stats are present use COST based optimisation otherwise use RULE based. 

SELECT /*+ RULE*/ name,dept FROM manager WHERE sal > 10000 

Explicitly use RULE based optimisation 

On a more general note it is often possible to improve the speed of NOT IN type queries by re-writing them as NOT EXIST. For instance if your original query looks like:- 

SELECT name,deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp) 

Try re-writing it as:- 

SELECT name,deptno FROM dept 
WHERE NOT EXISTS ( SELECT deptno FROM emp WHERE dept.deptno = emp.deptno)

Be aware though that when the sub-select can retrieve null values NOT IN and NOT EXISTS MAY NOT be equivalent.


​DBMS_SQLTUNE

Oracle 10 introduced a new way of tuning SQL using  dbms_sqltune 
See below for an example of how to use it.

Step 1 - Create the tuning task
=======================

CREATE OR REPLACE PROCEDURE sql_tuning_demo
(                     p1 IN VARCHAR2 DEFAULT NULL,                     p2 IN VARCHAR2 DEFAULT NULL                     ) AS       v_task VARCHAR2(30);       v_sql  CLOB;   
BEGIN      /* Assign the SQL we want to tune into a wariable */     

v_sql := ' select substr(d.ric,1,10) ric,     
 d.sedol,d.s_mnem,d.s_lprdate,d.UNIVERSE_ID,d.LAST_UPDATE,       
s.s_extmkr,s.s_extdate,s.s_lprdate  from ds_static d,stock s 
where d.s_mnem = s.s_mnem   and d.primary = ' || chr(39) || 'Y' || chr(39) ||   
' and exists (select ' || chr(39) || 'x' || chr(39) ||               
' from ds_static                where universe_id = d.universe_id                 
and trunc(last_update) > trunc(d.last_update)               )   
and d.last_update is not null   and d.universe_id != ' || chr(39) || 'X' || chr(39) ;     

/* Drop the task in case we are re-running... */     
​ BEGIN         
DBMS_SQLTUNE.DROP_TUNING_TASK(            task_name => 'sql_tuning_task'            );   
 EXCEPTION         
WHEN OTHERS THEN         NULL;     
END;     

/* Create a SQL Tuning task for our SQL... */     

v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(                   
sql_text    => v_sql,                   
time_limit  => 1,                   
scope       => 'COMPREHENSIVE',                   
task_name   => 'sql_tuning_task',                   
description => 'Demo of DBMS_SQLTUNE'                   
);     

/* Execute the task... */     

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(         task_name => 'sql_tuning_task'         );     

/* We want to run this again... */     

ROLLBACK;   
END sql_tuning_demo;


Step 2 - Run the tuning task
======================

exec sql_tuning_demo;

Step 3 - Review the output
====================
set long 80000col recs format a90 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS recsFROM   dual;


Step 4 - Clean up if required
======================
exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');

The output of step 3 above will be a huge screed of data. In amongst this there may be one or more suggestions for speeding up the query. Normally these will involve creating one or more indexes on one or more of the tables involved in the query. Make the suggested changes and retry the query.