November 26, 2013

Finding Plan Predicates in Oracle

An application was suffering dreadful performance, with sky-high buffer reads caused by full table scans from trivially simple queries that looked something like this:

UPDATE scormdata SET foo = :1 WHERE registrationid =:2

The obvious solution, of course, is to build an index on registrationid, watch the load drop, and enjoy the satisfying feeling of having Turned the Client's Frown Upside-Down.

Alas, in this case, there already was an index on registrationid. The scormdata table was large -- too large for Oracle to think that bypassing the index is a naturally good idea. Table statistics are correct. What's more, the poor performance can't be replicated from the command line: running an update in SQL*Plus uses the index properly. What's going on?

The best way to see is to look at what the query is really doing -- both the plan and the predicates. To do that, you'd think we can use dbms_xplan.display_awr to look at the historical plan information. Alas, you'd be wrong. Predicate information is blank. This is apparently a long-standing bug in 10 & 11. Random people on the internet say that it is planned to be fixed in Oracle 12g but we're not even close to thinking about installing that version of the software yet!

Assuming the information is still available, we can instead use dbms_xplan.display_cursor to pull the predicates and plans out of the existing cursors.

In the meantime, it is possible to pull some of the information out of the currently existing cursors, however this is sometimes a bit peculiar (we're dealing with a very dynamic area).

SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE s.sql_id='dp0tkwzmw3qab'
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |               |       |       |  1439 (100)|          |
|   1 |  UPDATE            | SCORMDATA     |       |       |            |          |
|   2 |   TABLE ACCESS FULL| SCORMDATA     |  1900 |   322K|  1439   (1)| 00:00:18 |
------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter SYS_OP_C2C("REGISTRATIONID")=:2


The key to the problem is above -- the SYS_OP_C2C() function. This is used internally by Oracle for character to character conversions. REGISTRATIONID in the table is a VARCHAR2, but the data in the parameter being passed is an NVARCHAR. Because of the type mismatch, Oracle is unable to use the index and will laborously convert the data one at a time, row-by-row.

There are two solutions. The longer-term one is to fix the application (or the database) so that the data types match. We need to either turn the table column into NVARCHAR or change the application to use VARCHAR2. The short-term fix, however, is just to make a new functional index using the SYS_OP_C2C function.

CREATE INDEX scormdata_idx1 ON scormdata(SYS_OP_C2C(registrationid)) ;

Once created, the database will start using the new index and all will be well. At least for at least the next twenty-seven minutes before someone doing datacenter maintenance spills his coffee all over the filer.

Posted by jeffreyb at 10:45 PM | Comments (0)