Intermediate SQL Color Coded SQL, UNIX and Database Essays


What are SQL Profiles and why do we need them ?

If you use DBMS_XPLAN package to analyze execution plans for your SQL statements (and you really should these days), you might have noticed that at times the following line might be displayed along with your execution plan:

   - SQL profile "SYS_SQLPROF_012ad8267d9c0000" used FOR this statement

This seems to mean that:

  1. An external ‘helper’ object was used during evaluation of your SQL statement
  2. This ‘helper’ object changed (or, at least, influenced) its execution plan

While having some extra help is nice, one has to wonder: what exactly is this “profile” object ? What does it do ? And, in a bigger picture, why do we need “external” help evaluating SQL statements at all ?