Thursday Apr 25, 2013

'IS NULL' SQL Tuning

NULLFrequently at client sites some very slow queries contain the SQL involving the ‘IS NULL’ criteria in the where clause. It is commonly known Oracle does not store NULL values in indexes so even though an index may exist for the column it is NOT usable in this case. However there is a solution to this situation and FULL table scans can be avoided!

Example: SQL> Select name from emp where dept_id is null;

So in the above example even if the dept_id is indexed on the table emp, due to the ‘IS NULL’ criteria it will NOT be used.

Many developers believe that this type of statement cannot be tuned and that a FULL table scan is the only option. However here is a solution to this issue, simply create an appropriate index but add in an additional constant value to the end of the index so that now NULL values are actually stored!

Example: SQL> create index E1 on EMP (DEPT_ID, -1);

Running an explain plan and executing the SQL it can be seen that the new index will now be used as NULL values are now indexed.

Real life example: At a particular customer site there was a situation where a table had over 11 million rows. A SQL statement had a lot of table joins but only 2 constraints in the where clause as below.



From analyzing the table almost every row had the PRIV_FLG = ‘N’ so that was not going to help, however looking at the integration_id from the 11 million rows, only 51,167 were null values. The field 'integration_id' was already indexed however this would not be used due to only wanting NULL values. By indexing as in the above example the new index helped optimize the query significantly as the number of rows in the driving table had been significantly reduced without any full table scans!

Thursday Apr 18, 2013

Changing the Explain plan for one SQL statement to another with NO code changes or Hints

CheetahOn a client site and had an issue with a 3rd party product to do with an inbuilt query executing from an application. A specific query was taking forever to run and the execution plan was not ideal.  Nothing obvious had shown anything useful such as hints or indexes etc. 

Obviously as the code is submitted from a product itself therefore it could not be changed!  Table and Index statistics were up to date and the query had always been pretty poor in performance when examining the AWR history.

However during an initial analysis of the code a much better performing execution plan was located, the difference between the 2 pieces of code were simply that one was using a bind variable and the second was a hard coded value given as a test case.  There are documented examples of how to simulate code in PLSQL with bind variables so that this issue does not actually occur, however the interesting thing was that without the bind variable the plan was just what the customer wanted.

 The real answer to the above question would be to look at dynamic sampling / statistics gathering etc to ensure nothing is being influenced due to the bind variables (such as bind variable peeking or histograms etc), however a really simple solution was how to get the explain plan of SQL A over to SQL B with no actual code change.

There is a blog entry already showing how to introduce hints and also parallelism when a code change is not possible:

However this is a test case where we simply want to link the execution plan of SQL A to that of SQL B.

Please Note these scripts were written only for demonstration purposes. They are not optimized and they have almost no error checking, so be careful!

  1. The first thing required is to know the SQL_ID for the SQL that you wish to change the explain plan. (poor performer)
  2. The Second thing required to know is the SQL_ID and Plan_Hash_Value for the SQL that has the desired execution plan.

 Then it is simply a matter of running the following PL/SQL block:

The above once executed created a baseline entry in dba_sql_baselines.  By executing the following query this could be seen :

SQLPLUS > select fixed,accepted,enabled,created,sql_text,sql_handle from dba_sql_plan_baselines;

Other useful baseline queries are:

SQLPLUS> select * from table(dbms_xplan.display_cursor(‘&1’,&2));

NOTE:  above needs &1 set to the sql_id and &2 the child number.  At the end of the explain plan you can see if the baseline has been used or not.

SQLPLUS> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>’xxxxxx’, format=>’basic’));

NOTE:   xxxxxx is the sql_handle from dba_sql_plan_baselines.

As it could be seen the baseline was taken and being used so the final step was also to fix the baseline:

If the baseline is not applicable anymore or did not work then it is easy to tidy up and remove it as below:

Conclusion: Although the long term solution is still to review the code and database to see really why the bind variable execution was giving a considerably poorer execution plan to that of the hard coded SQL. The issue was at least resolved with a nice work around due to being able to use a baseline and influence the explain plan as desired.

Wednesday Apr 17, 2013

11gR2 Flashback Guaranteed Restore Point without enabling Flashback logging

Flashback PicturePlenty has been documented and published regarding flashback database since the first implementation in Oracle 10g. However little is documented regarding using flashback restore points in Oracle when the flashback logging (flashback off) is not enabled.

At a client site flashback logging was turned off as a feature due to performance implications, in 11gR2 it is a dynamic online feature that you can now turn flashback on and off without a database bounce. A situation arose and questions were asked regarding the use of guaranteed restore points and flashback logging for  testing purposes.

For reference this paper gives a short summary and example of the Oracle documentation:

Download The Full PDF here: 11gR2_Flashback_Guaranteed_Restore_Point_without_enabling_Flashback_logging.pdf


About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.


« April 2013 »