Apr 102013
 

This post is also available in: Bulgarian

I really enjoy when I see some small detail in the huge and complex Oracle Database. I want to tell you about one such case.

It started with an urgent email from a Development manager. The new functionality ХYZ works slow (12-13 sec), and they have identified the problem to be in one specific query:

select .... from SOME_TABLE where IP = :1 and ACTIVE_DATE > current_timestamp - :2;

I needed only a few seconds to see that it uses full table scan on a multi-million-row table. There is no index neither on IP, nor on ACTIVE_DATE. Of course the whole table is in memory, after such heavy usage. But even LIOs, when you use millions of them, take time.

I asked if they could change the app to use some of the existing indexes. Well, no, we can’t.

OK, the we’ll make an index on (IP, ACTIVE_DATE). I have created the index on one of the DEV databases and I asked the QAs to test is. There was not effect. Blah!

I decided to prod Oracle to take it more seriously. What a moron! I’ve create the perfect index for the query, and it didn’t even notice! I have started the Тuning advisor to fix this. An then – surprise, surprise – Tuning advisor could not use the index too! But it gave me a hint where the problem is:

The predicate TO_NUMBER(“IP”)=:B1 used at line ID 1 of the execution plan contains an implicit data type conversion on indexed column “IP”. This implicit data type conversion prevents the optimizer from selecting indices on table “SOME_TABLE”

That’s a good tool! As a former developer I instantly understood what happens. Isn’t it a pity – after so many years with newer and better IDEs and frameworks, the errors are the same.

The problem is that the column in the table is VARCHAR2. However, the application binds a NUMBER. If we did not use binds, this should have been visible with a naked eye. But in that case I had to take a look at V$SQL_BIND_CAPTURE. Or even better, ask some developer to check – and fix – the java code

Now I want to share one thought about OEM CC. It s very good, very convenient and useful product, but at that case I’ve waisted some time with it. I could have seen this information at the beginning, if I wasn’t too lazy to use dbms_xplan.display_cursor. Here’s what it shows (check the filter information):

SQL_ID  436wr79chvhqx, child number 0
-------------------------------------
select ...
  from SOME_TABLE 
 where IP = :1
   and ACTIVE_DATE > current_timestamp - :2
 
Plan hash value: 2330990216
 
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |       |       | 12528 (100)|          |
|*  1 |  TABLE ACCESS FULL| SOME_TABLE         |     1 |    87 | 12528   (2)| 00:02:31 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter((TO_NUMBER("IP")=:1 AND 
              "ACTIVE_DATE">CURRENT_TIMESTAMP(6)-:2))

And here’s the tiny piece of Oracle which made my day. The developers fixed the code, binded the IP as a string. The DB started using the index. Now look at the following:

SQL_ID  4ktc94z4ypdpu, child number 0
-------------------------------------
select ...
  from SOME_TABLE 
 where IP = :1
   and ACTIVE_DATE > current_timestamp - :2
 
Plan hash value: 3569887601
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| SOME_TABLE            |     1 |    87 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_SOME_TABLE_IP_TIME  |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("IP"=:1 AND "ACTIVE_DATE">CURRENT_TIMESTAMP(6)-:2 AND 
              "ACTIVE_DATE" IS NOT NULL)

Did you notice the new predicate that appeared from nowhere: AND "ACTIVE_DATE" IS NOT NULL? The optimizer did this!

Here’s what happens. The b-tree indexes do not store null values. If I want to use an index, I have to be sure that the user do not need the rows with null in the column. In other words, Oracle needs to know, that the user issuing the query did not request this data. On the other hand the optimizer knows the no date is lower (or higher) than null. That’s why it dares to add to the where clause one predicate, which lets him use and indexed access.

I like it!

 Posted by at 7:21

Sorry, the comment form is closed at this time.