Thursday, December 21, 2006

The Optimizer, an update

My last post involving the optimizer was quite perplexing until Jonathan Lewis commented on it. Indeed, the check constraint was the problem in this particular case.

I was so frustrated, I went down the path of upgrading to the latest version since I knew Oracle wouldn't be submitting a bug on 9.2.0.5. I even changed platform just for kicks:


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.2.0 - Production
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Alas, I still had the problem before Jonathan suggested the constraints.

SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.27
SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.27

If I had only chased down the filter predicate using the new dbms_xplan.display method, I might have had some more clues:

SQL> explain plan for select count(*) from activity_rtab where date_matched = '31-dec-4712';

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 3790338984

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 269 (3)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IX_ACTIVITY_DATE_MATCHED | 3801 | 30408 | 269 (3)| 00:00:04 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("DATE_MATCHED"=TO_DATE('4712-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(INTERNAL_FUNCTION("DATE_MATCHED"))=TO_DATE('4712-12-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))

16 rows selected.

I dropped the constraint and sure enough, the performance problem went away:

SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';

COUNT(*)
----------
370319

Elapsed: 00:00:00.06
SQL> explain plan for select count(*) from activity_rtab where date_matched = '31-dec-4712';

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3790338984

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 264 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IX_ACTIVITY_DATE_MATCHED | 380K| 2969K| 264 (1)| 00:00:04 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("DATE_MATCHED"=TO_DATE('4712-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

14 rows selected.

Now we have to decide; use the constraint to ensure data integrity or suffer the performance penalty for the sake of the data. A classic trade-off.

2 comments:

Anonymous said...

Whenever I end up cloning one table into another there are some checks that are relevant to help ensure that you end up with an apples to apples comparison. Those considerations include not only column numbers and data types but also statistics, constraints, numbers of indexes, etc.

Tools like Toad have these types of utilities built in already but you can of course also build your own script that does such a comparison. They come in very handy during situations like you described and might have helped you see the difference in the table definitions earlier.

Anonymous said...

You can continue to use the SQL*Plus AUTOTRACE command in 10.2. It now uses dbms_xplan.