A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
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.
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.
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,
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... */
DBMS_SQLTUNE.DROP_TUNING_TASK( task_name => 'sql_tuning_task' );
WHEN OTHERS THEN NULL;
/* 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... */
Step 2 - Run the tuning task
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
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.