Implicit conversion and DBMS_SQLTUNE

No wonder implicit conversion is one of the most reasons of poor query performance – it makes existing indexes useless. Often developers have to spend time to identify what is the reason of poor performance and what would be the correction of the wrong plan.

For example:

SQL> create table t (x date);

Table created.

SQL> create index t_x on t(x);

Index created.

SQL> insert /*+ append */ into t select sysdate-rownum from dual
  2  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> user, tabname=>'T', cascade=>true)

PL/SQL procedure successfully completed.

SQL> DECLARE
  2   l_ts_variable TIMESTAMP := SYSTIMESTAMP;
  3  BEGIN
  4   FOR c IN (SELECT * FROM t ts_test WHERE x = l_ts_variable) LOOP
  5      NULL;
  6   END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> select sql_id from v$sqlarea
  2  where sql_fulltext like 'SELECT%TS_TEST%';

SQL_ID
-------------
2mpd2hva20868

SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor('2mpd2hva20868'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  2mpd2hva20868, child number 0
-------------------------------------
SELECT * FROM T TS_TEST WHERE X = :B1

Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    55 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |  1000 |  8000 |    55   (8)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("X")=:B1)
18 rows selected.

As we can see the problem is bind variable is declared as TIMESTAMP while column type is DATA – often situation with Java coders, isn’t it ? In reality the problem is we need to go to dictionary to identify what is bind variable type is.

Oracle suggests DBMS_SQLTUNE package as an automation tool to make the performance tuning easier. It’s interesting – is this package able to identify this problem and advise something to correct ? Let’s make a test.

SQL> DECLARE
  2  my_task_name VARCHAR2 ( 30 );
  3  BEGIN
  4  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
  5    sql_id    => '2mpd2hva20868', task_name => 'easy_task'
  6  );
  7  DBMS_SQLTUNE.EXECUTE_TUNING_TASK ( my_task_name );
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL> select DBMS_SQLTUNE.REPORT_TUNING_TASK(
  2  'easy_task' ) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('EASY_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : easy_task
Tuning Task Owner  : DBA_GEN
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 11/09/2010 17:45:40
Completed at       : 11/09/2010 17:45:41


DBMS_SQLTUNE.REPORT_TUNING_TASK('EASY_TASK')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: DBA_GEN
SQL ID     : 2mpd2hva20868
SQL Text   : SELECT * FROM T TS_TEST WHERE X = :B1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('EASY_TASK')
--------------------------------------------------------------------------------
  The predicate "TS_TEST"."X"=:B1 used at line ID 1 of the execution plan
  contains an implicit data type conversion on indexed column "X". This
  implicit data type conversion prevents the optimizer from selecting indices
  on table "DBA_GEN"."T".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. 

-------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('EASY_TASK')
--------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |  8000 |    55   (8)| 00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('EASY_TASK')
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T    |  1000 |  8000 |    55   (8)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(INTERNAL_FUNCTION("X")=:B1)

-------------------------------------------------------------------------------

As we can see Oracle identifies the root cause – implicit conversion.

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s