Notes on “The Evils of Implicit Conversions and other SQL Skullduggery” by #TomKyte via #Red-Gate


Notes on Webinar called The Evils of Implicit Conversions and other SQL Skullduggery from Red Gate Software by Tom Kyte.

Trigger Trickery

Be aware of the four (4) types of triggers (the compound trigger is just a combination of these):

  1. Before Statement
  2. Before Each Row
  3. After Each Row
  4. After Statement

Tom Kyte demonstrated that triggers can fire multiple times due to row locking. He demonstrated multiple firings occurring when a statement is blocked and then rolled back to begin again—internally by the Oracle RDBMS engine in order to avoid the ‘lost update’ problem.

He says that:

  • Never do anything non-transactional in a trigger
  • Pragma autonomous_transaction is a really bad idea in general
  • UTL_packages – really bad idea. They do not roll back (utl_file, utl_http, utl_smtp, etc etc etc)
  • Even just setting global variables in a trigger can be tricky

This has huge implications for sites that use triggers for auditing purposes as:

  • Rolled back changes are recorded usually because of PRAGMA AUTONOMOUS_TRANSACTION
  • Multiple instances of the same change are recorded
  • Recording a change that never happened—the change could see multiple consistent read images until the final one is read

Triggers are not called during direct path loads via SQL Loader. The presence of a trigger also silently stops direct path loads in SQL.

NULLS and Indexes and Cardinality

Tom Kyte’s mantra is:

Wrong cardinality = Wrong Plan

He demonstrated that replacing NULLs by a ‘fake’ value resulted in an incorrect plan because the column histogram became distorted.

He says that the following myths are not true:

  • There is a pervasive myth that indexes and NULLs are like matter and anti-matter
  • There is the thought that “where column is null” cannot use an index
  • There is a thought that NULLs are not indexed

He demonstrated how to index null values of a column through the use of a concatenated index with the leading column as nullable and the next column was not.

CREATE INDEX t_null_col ON t(null_col, 0);

This I did not know. He used a constant as part of the index key. Because a constant is never NULL, the index will point to all the rows in the table. So, the predicate, NULL_COL IS NULL, will use an INDEX RANGE SCAN, if the CBO thinks so.

In B*Tree indexes, entirely NULL entries are excluded.

In a bit mapped index, NULLs are indexed.

Implicit Conversions are Evil

Tom Kyte says that [i]mplicit conversions…are probably the #2 cause of bugs…. NLS_DATE default settings are a problem waiting to happen. Also, changing the NLS_DATE through the use of constants enables SQL injection.

He says that implicit conversions causes the following performance issues:

  • Repeated conversions
  • Access path reductions
  • Partition elimination eliminated

Useful SQL*Plus commands for compiling PL/SQL code:

ALTER SESSION SET plsql_warnings = 'enable:all';
CREATE OR REPLACE PROCEDURE P ...
SHOW ERRORS PROCEDURE P

Implicit conversions wrap the column into a function call which precludes index lookup and partition elimination.

To prevent these problems, Tom Kyte recommends the following setting:

ALTER SESSION SET plsql_warnings = 'error:all';

This elevates all warnings to errors so preventing compilation. He suggests a conversion over several years.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s