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) |
| With TO_CHAR | Predicate Information (identified by operation id): ————————————————— 2 – filter(“COL1″=131) |
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
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]
ORAganism 