SQL Tuning

Using dbms_sqltune.import.sql_profile to adjust explain plans

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 07 April 2011 15:50; Last Updated on Wednesday, 01 August 2012 15:35; Published on Thursday, 07 April 2011 15:50; Written by Guy Lambregts; Hits: 8225

Using dbms_sqltune.import.sql_profile to adjust explain plans

Query transformation was based on heuristic rules in 9i. From 10G R1 however by default query transformations are cost based since "_optimizer_cost_based_transformation" defaults to linear. There are bugs related to cost based query transformation causing huge performance degradation. For some databases we set cost based query transformation to off with the below sql.

SQL> alter system set "_optimizer_cost_based_transformation"=off scope=both; System altered.

However we have some databases which highly benefit from cost based query transformation, only there are a very few statements which run extremely bad. The optimizer is just wrong for that particular statement. We can revert to cost based query transformation with the below sql

SQL> alter system set "_optimizer_cost_based_transformation"=linear scope=memory; System altered. SQL> alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*'; System altered.

When we use the sql tuning advisor a possible better explain plan is found and we are invited to accept the suggested sql profile. Once accepted we are disappointed (sometimes) the plan is even worse. Why is that ? The optimizer decision is based on some algorithms, if we use the sql tuning advisor we use the optimizer in tuning mode but Oracle still uses some built in algorithms. And those algorithms are not yet 100% bug free.

Prior to Oracle 10G we could use stored outlines to fix and to freeze explain plans for statements. But what when literals were used in the predicates, for every statement an outline ?

Oracle 10G comes with the dbms_sqltune package and my eye fell on the procedure import_sql_profile. I found it hard to find some documentation about it, but I used the import_sql_profile procedure succesfully to get my own profiles using the execution context I preferred to get the right explain plan for statements I could not use nor hint ( since it was thirth party code )

Here is an example how I reverted to heuristc query transformations for 1 particular statement. ( to get complex view merging done )

DECLARE l_sql VARCHAR2(2000); BEGIN l_sql := q'!SELECT a,b,c from T1 T1A WHERE d=:B2 AND e=:B1 AND f = (SELECT MAX(f) FROM T1 T1BIS WHERE T1BIS.d = T1A.d AND T1BIS.g = T1A.g AND T1BIS.h = T1A.h AND T1BIS.e = T1A.e )!'; dbms_sqltune.import_sql_profile( sql_text => l_sql, name => 'TUNING_TASK_01',profile => sqlprof_attr(q'!optimizer_features_enable('9.2.0')!'), force_match => true ); end; /

The sqlprof_attr is the parameter we use to set our execution context. In the above example I thus reverted to heuristc query transformations setting optimizer_features_enable back to 9.2.0 for that particular statement. If we would have been able to change to code ourself

SELECT /*+ OPT_PARAM('optimizer_features_enable','9.2.0') */ a,b,c from T1 T1A WHERE d=:B2 AND e=:B1 AND f = (SELECT MAX(f) FROM T1 T1BIS WHERE T1BIS.d = T1A.d AND T1BIS.g = T1A.g AND T1BIS.h = T1A.h AND T1BIS.e = T1A.e )

In which views is the sql profile information stored ?

In Oracle 10G there are the views : sys.sqlprof$ and sys.sqlprof$attr

in Oracle 11G there are the views : sys.sqlobj$ and sys.sqlobj$data