TO_CHAR or NOT TO_CHAR

Another post where I probably show trivial things. This issue is very easy to diagnose and fix but quite complicate to prevent.
I’ll start from my personal statistics of performance analyses during development. From my point of view there are 5 levels of SQL tuning that are using in code development. I try to present percent of queries that tuned by each of them.

N Method % of statement tested
1 No test. Rely on optimizer and experience. 50
2 Execute on developer database. 25
3 Execute on a database with real size data. 15
4 Execute on database with real size data and analyse the execution plan. 10
5 Execute on database with real size data and analyse the execution plan and predicates. 0.0000001

It shows that usually we usually start intensively tune queries only when it does not work on a real production or preproduction stage. It have sense but some hidden rocks can and should be diagnosed during using the first level of tuning. Based on my own experience the own expertise can be easily overestimated. Before I start this post I query some my friends and no one was aware this oracle trick, thus I believe that it would be useful for general audience too.
Will you check the predicates if the access method is expected and use the correct index. Probably not but oracle loves to make surprises. In our case it was surprise with concatenated indexes and implicit conversion.
I’ll try to show the case using the example

--Create sample table
CREATE TABLE eterpani.TESTTABLE
   (    COL1 NUMBER(10,0) DEFAULT (0) NOT NULL ENABLE,
        ID NUMBER(10,0) NOT NULL ENABLE,
        PARTITIONKEY CHAR(1) NOT NULL ENABLE,
        COL2 NUMBER(10,0),
        COL3 VARCHAR2(6 CHAR) NOT NULL ENABLE,
        COL4 DATE NOT NULL ENABLE,
        COL5 VARCHAR2(3 CHAR) NOT NULL ENABLE,
        AMOUNT NUMBER(16,3) DEFAULT (0) NOT NULL ENABLE,
         CONSTRAINT TESTTABLE_PK PRIMARY KEY (ID,PARTITIONKEY) USING INDEX )
/
--Put concatenated index on it where col2 column is in the middle
CREATE INDEX eterpani.TESTTABLE_IDX1 ON eterpani.TESTTABLE 
   (PARTITIONKEY,  COL4, COL5, COL2, COL3)
/
--Populate table by some data
INSERT INTO eterpani.TESTTABLE
SELECT COL1, ID, PARTITIONKEY, COL2, COL3, COL4,COL5,AMOUNT FROM dual
   MODEL DIMENSION by (65 i)
   MEASURES (0 COL1,
             0 ID,
             CAST(' ' as CHAR(1)) PARTITIONKEY,
             0 COL2,
             CAST(' ' as CHAR(25)) COL3,
             sysdate COL4,
             CAST(' ' as CHAR(3)) COL5,
             0 AMOUNT)
     (col1[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(100,150)),
      ID[for i from 1 to 100000 increment 1] = cv(i),
      PARTITIONKEY[for i from 1 to 100000 increment 1] = CHR(65+MOD(cv(i),26)),
      col2[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(10,12)),
      col3[for i from 1 to 100000 increment 1] = PARTITIONKEY[cv(i)]||DBMS_RANDOM.STRING('U',5),
      col4[for i from 1 to 100000 increment 1] = TRUNC(SYSDATE)+ROUND(DBMS_RANDOM.VALUE(-5,5)),
      col5[for i from 1 to 100000 increment 1] = TO_CHAR(ROUND(DBMS_RANDOM.VALUE(10,999))),
      AMOUNT[for i from 1 to 100000 increment 1] = ROUND(DBMS_RANDOM.VALUE(100,1000)))
/
COMMIT
/

The preparation steps are over. Lets run the query.

Set autotrace on
SELECT PARTITIONKEY, COL4, COL3, COL2, COL5, SUM(AMOUNT) SUMAMOUNT
FROM eterpani.TESTTABLE
WHERE PARTITIONKEY = 'C' AND COL3='COWRTE'
     AND COL4 = TRUNC(SYSDATE) AND COL2 = 11
     AND COL5 = 901 AND COL1=131
GROUP BY PARTITIONKEY,  COL4, COL5, COL2, COL3;

Let’s look on execution plan

Execution Plan
----------------------------------------------------------
Plan hash value: 3717891987

-------------------------------------------------------------------------------------------
|Id| Operation                   | Name           | Rows  | Bytes | Cost (%CPU)|  Time    |
-------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT             |                |   1   |  59   |  4   (0)   | 00:00:01 |
| 1| SORT GROUP BY NOSORT        |                |   1   |  59   |  4   (0)   | 00:00:01 |
|*2|  TABLE ACCESS BY INDEX ROWID| TESTTABLE      |   1   |  59   |  4   (0)   | 00:00:01 |
|*3|   INDEX RANGE SCAN          | TESTTABLE_IDX1 |   1   |       |  4   (0)   | 00:00:01 |

If someone had showed me this execution plan few months ago I would have said that it is the good one without any doubts. But let me make a small changes in a query – put explicit conversion to COL5 predicate.

Set autotrace on
SELECT PARTITIONKEY, MAX(COL1), COL4, COL3, COL2, COL5, SUM(AMOUNT) SUMAMOUNT
FROM eterpani.TESTTABLE
WHERE PARTITIONKEY = 'C' AND COL3='COWRTE'
     AND COL4 = TRUNC(SYSDATE)  AND COL2 = 11
     AND COL5 = TO_CHAR(901) AND COL1=131
GROUP BY PARTITIONKEY,  COL4, COL5, COL2, COL3;

As result we have the same execution plan. Even the “Plan hash value” is the same. At this stage you can say no difference no reasons to bother. But difference exists. Firstly pay attention to the bottom part of the autotrace output

Without TO_CHAR Statistics
———————————————————-
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
675 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
With TO_CHAR Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
746 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Does the triple number of read blocks confuse you? The answer on this confusion is in the middle part of autotrace output

Without TO_CHAR Predicate Information (identified by operation id):
—————————————————

2 – filter(“COL1″=131)
3 – access(“PARTITIONKEY”=’C’ AND “COL4″=TRUNC(SYSDATE@!) AND “COL2″=11 AND “COL3″=’COWRTE’)
filter(“COL3″=’COWRTE’ AND “COL2″=11 AND TO_NUMBER(“COL5″)=901)

With TO_CHAR Predicate Information (identified by operation id):
—————————————————

2 – filter(“COL1″=131)
3 – access(“PARTITIONKEY”=’C’ AND “COL4″=TRUNC(SYSDATE@!) AND “COL5″=’901′ AND “COL2″=11 AND “COL3″=’COWRTE’)

As you understand the most selective predicate in this query is “COL3=’COW131’”. This predicate use indexed column and mentioned in corresponding section thus everything should be easy and strait forward. But there is one small difference. Oracle implicitly convert all values in COL5 to number. Why Oracle is doing it gods know. But it prevent usage of COL5 in access path. Even without explicit conversion we still use the index but read more blocks.
I have tried to describe this difference in reading on a picture
index block reading schema
All index blocks with the same PARTITIONKEY and COL4 values would be reviewed in a process of searching the relevant records. It is just index blocks reading but it is additional read operation that can dramatically increase the time query depending on cardinality of COL5. Moreover we have extra CPU waste. Instead of one conversion operation from constant to char we convert each single COL5 value to number. Believe me it is not we all expected on our databases.
There are two conclusions on above information:
firstly – be careful when looking on execution plan, check not only plan but predicates and other bits and pieces
Secondly – implicit thing have tendency to work unpredictably. Convert explicitly where you can.
Usefull doc: ORA-01722: Invalid Number; Implicit Conversion Not Working Correctly [ID 743595.1]

When to Gather Fixed Object Optimiser Statistics

Lets start with some advice from Metalink note 272479.1: Gathering Statistics For All fixed Objects In The Data Dictionary.

With Oracle Database 10G it is now recommended that you analyze the data dictionary.
The data dictionary has many fixed tables, such as X$ tables and collecting statistics
for these objects is suggested by Oracle. The GATHER_FIXED_OBJECTS_STATS
procedure gathers statistics for all fixed objects (dynamic performance tables) in the
data dictionary.

So we know we should do it – but when? Advice from the Oracle documentation this time.

Statistics on fixed objects, such as the dynamic performance tables, need to be
manually collected using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record
current database activity; statistics gathering should be done when database has
representative activity.

I have an example below of how important the timing of this “representative activity” can be. Here is a simple query – the likes of which are often run by monitoring tools such as Oracle Grid Control – executed on a database that has had statistics for all fixed objects gathered previously.

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:01:43.39

Nearly 2 minutes for a 23 row result set. The execution plan is a bit long to post here but below is a snippet that set some gentle alarm bells ringing.

------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | 
------------------------------------------------------------------------------
... snip ...
|* 11 |            HASH JOIN OUTER          |                        |     1 | 
|  12 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  13 |              MERGE JOIN CARTESIAN   |                        |     1 |
|  14 |               FIXED TABLE FULL      | X$KCCRSR               |     1 |
... snip ...
|* 36 |           HASH JOIN OUTER           |                        |     1 |
|  37 |            MERGE JOIN CARTESIAN     |                        |     1 |
|  38 |             MERGE JOIN CARTESIAN    |                        |     1 |
|  39 |              FIXED TABLE FULL       | X$KCCRSR               |     1 |
... snip ...
------------------------------------------------------------------------------

A quick glance at the optimiser statistics for the X$KCCRSR table tells us some interesting information.

select rowcnt, blkcnt, analyzetime, samplesize
from
 tab_stats$ where obj#=(select OBJECT_ID from V$FIXED_TABLE where name = 'X$KCCRSR');

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
         0          0 26-APR-09          0

Zero rows. And after gathering fixed object statistics…

exec dbms_stats.gather_fixed_objects_stats;

    ROWCNT     BLKCNT ANALYZETI SAMPLESIZE
---------- ---------- --------- ----------
       225          0 21-JUL-09        225

select count(*) from v$rman_backup_job_details;

  COUNT(*)
----------
        23

Elapsed: 00:00:00.19

The optimiser now knows there are 225 rows in X$KCCRSR and returns it’s result set in under a second. Now I’m not suggesting anyone regularly schedule a call to GATHER_FIXED_OBJECTS_STATS just consider the timing of this call a bit. Perhaps after your first couple of RMAN backups may do the trick :)

As an interesting aside I stumbled across the following method to view when statistics have been gathered.

select operation,start_time from DBA_OPTSTAT_OPERATIONS order by start_time;

OPERATION                             START_TIME
------------------------------------- -------------------------------------
gather_database_stats(auto)           17-JUL-09 10.00.01.560328 PM -03:00
gather_database_stats(auto)           18-JUL-09 06.00.03.189531 AM -03:00
gather_database_stats(auto)           20-JUL-09 10.00.01.363156 PM -03:00
gather_fixed_objects_stats            21-JUL-09 09.52.59.646815 AM -03:00

I’ve just noticed there are no system statistics in there – that’s an investigation for another day.