On Popularity, Learning, and Unlearning

Our technologist revisits a classic, recounts Cardinality Feedback, and remembers SQL*Plus.

By Tom Kyte

September/October 2010

From time to time, I get e-mails from Oracle Magazine readers in which they ask follow-up questions or point out a mistake (or two) in my columns. Recently I received a follow-up question (not a mistake report!) regarding the most popular question and answer on Ask Tom (and in Oracle Magazine at oracle.com/technology/oramag/oracle/09-jul/o49asktom.html). To answer that follow-up question, I’ll begin by sharing the original Q&A. This will be the third time I’ve written about this now—it is the idea that will not go away.

First, to refresh you on the topic of that most popular Q&A, the original problem statement was

I want to declare multiple cursors based on the values passed through a procedure, and only the WHERE conditions of the cursors will change. The body of the procedure is the same for all the cursors otherwise.

Basically, the answer was to use native dynamic SQL and dynamically construct the WHERE clause based on the inputs. My suggestion was to incorporate code that resembled this:

if (some_parameter is not NULL)
then
  the_query := the_query ||
  ' and some_column = :some_parameter ';
else
  the_query := the_query ||
  ' and (1=1 or :some_parameter
is null) ';
end if;
and, later in the code, open a ref cursor:
open the_cursor
  for the_query
using some_parameter <and any other parameters...>;

That way you could have a subroutine with many input parameters—as many as you wanted—and build a query that could always be opened with a constant number of bind inputs (a requirement of native dynamic SQL). I encourage you to read the earlier column to review this technique and my reasons for originally suggesting this approach.

The follow-up Oracle Magazine reader question I received was, in short, “How do I use this approach with an UPDATE statement?” In full, the question was

Great article. I would LOVE to know how to apply this to dynamic update statements. What can I use in place of “??????” in the following:

create or replace
procedure my_new_procedure
(p_hiredate  in date default NULL)
as
   l_query  varchar2(512);
BEGIN
  l_query := 'UPDATE DETAIL SET
ACOLUMN = 1';
  If ( p_date is NOT NULL )
  then
    l_query := l_query ||
     ' ,BCOLUMN = :p_hiredate ';
  else
    l_query := l_query ||
     ' ?????? ';
  end if;
... ccolumn and others here ...
EXECUTE IMMEDIATE V_SQL
USING p_hiredate;
  COMMIT;
END;

So, the follow-up question was a variation on a theme. Instead of selectively adding criteria to a predicate, which is what my original column was about, this reader would like to selectively modify columns if some input is passed along.

There are two cases to consider here: one is that there is either no WHERE clause to be added or the WHERE clause is static (constant). The UPDATE statement would either update all rows in the table (no WHERE clause) or a set of rows based on a constant criterion. In this case, the answer is easy: do not use dynamic SQL at all. Just code.

BEGIN
  UPDATE detail
  SET acolumn = 1,
  bcolumn = nvl(p_hiredate, bcolumn),
  <ccolumn and others here> ...
  <WHERE clause if provided>
END;

And that’s it. If P_HIREDATE is null, then the UPDATE will effectively set BCOLUMN to itself. If P_HIREDATE is not null, then P_HIREDATE will be used to update BCOLUMN. It is true that this would generate a bit more redo and undo, because every column would be modified every time, but because the indexes would not be maintained for any indexed column whose value did not actually change, this would be minimal. For the first case, then, the solution is to use static SQL and NVL().

The second case is when there is also a dynamically constructed WHERE clause. In this case, the answer is still to use NVL()—so the SET portion of the UPDATE will be constant—and then use the technique outlined in the original column for the WHERE clause. That is, the query construction would look like this:

begin
  L_query :=
  'update t set x=1,
   c1 = nvl(:c1,c1),
   c2 = nvl(:c2,c2), ...
   where 1=1'
  if (p1 is not null)
  then
     l_query := l_query ||
     ' and p1 = :p1 ';
  else
     l_query := l_query ||
     ' and (1=1 or :p1 is null);
  end if;
  ... other parameters ...
  execute immediate l_query using
  c1, c2, ..., p1, p2, ...;

As in the static SQL example, I would set all the column values every time—either to the new values in the bind variables or to themselves if the bind variables were NULL.

As an aside, and as I mentioned earlier, I get e-mails from time to time regarding the content of my column—as do other Oracle Magazine authors. Anytime you see something confusing, would like a clarification, or have a question on the content of the magazine, feel free to drop the editors an e-mail at opubedit_us@oracle.com. Include the URL to the online version of the column or the article title and issue date of the magazine you are writing about, and the editors will forward the question to the person who wrote the article. Who knows, you might appear in the front of the magazine one issue in the letters to the editor (From Our Readers).

SQL> create table t1 ( x int );
Table created.
SQL> create table t2 ( x int )
  2  segment creation immediate;
Table created.
SQL> select segment_name,
  2   extent_id, bytes, blocks
  3    from user_extents
  4   where segment_name
  5         in ( 'T1', 'T2' )
  6   order by 1,2;
SEG EXTENT_ID       BYTES BLOCKS
--- ---------   --------- ------
T2          0       65536      8
SQL> insert into t1(x) values (1);
1 row created.
SQL> select segment_name,
  2   extent_id, bytes, blocks
  3    from user_extents
  4   where segment_name
  5         in ( 'T1', 'T2' )
  6   order by 1,2;
SEG EXTENT_ID       BYTES BLOCKS
--- ---------   --------- ------
T1          0       65536      8
T2          0       65536      8

I had to add SEGMENT CREATION IMMEDIATE to many examples in the book in order to make things work as they used to. The gist of this is to point out that I myself learn something new about Oracle Database all the time, and I thought I would share a few of those things with you here.

About estimated cardinalities. The first thing I recently learned has to do with estimated cardinalities. The cost-based optimizer works mainly by trying to guess how many rows will be returned by various parts of your query. For example, if you have a query with the WHERE clause WHERE x = ? and y = ?, and it turns out that x and y each have an index, the optimizer will pick the index it wants to use by trying to guess how many rows WHERE x = ? will return and how many rows WHERE y = ? will return. The condition expected to return the fewest records will drive the optimizer’s selection of the corresponding index. If the optimizer guesses wrong, for whatever reason, the performance of your query will be negatively affected.

In Oracle Database 11g, the optimizer has changed to learn from its mistakes. So if the optimizer guessed wrong in this example, it would make that discovery as it executed the query and saw what the actual answer was. In the past, the optimizer would not use this newly gained information at runtime to change its mind and develop a new query plan. Now, it has that ability via a feature called Cardinality Feedback. I’ll demonstrate with an example that uses a PL/SQL function.

Typically, when you have a pipelined function, the estimated cardinality is computed according to your database block size—that is, the default number of rows that will result from the function is based on your block size. I have an 8 KB block size, so my estimated cardinality will be close to 8192, as shown in Listing 1.

Code Listing 1: Estimated cardinality from the explain plan

SQL> create or replace type str2tblType
                  as table of varchar2(30);
2  /
Type created.
SQL> create or replace
2    function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
3    return str2tblType
4    PIPELINED
5    as
6        l_str      long default p_str || p_delim;
7        l_n        number;
8    begin
9        loop
10            l_n := instr( l_str, p_delim );
11            exit when (nvl(l_n,0) = 0);
12            pipe row ( ltrim(rtrim(substr(l_str,1,l_n-1))) );
13            l_str := substr( l_str, l_n+1 );
14        end loop;
15        return;
16    end;
17    /
Function created.
SQL> column plan_table_output format a80 truncate
SQL> variable in_list varchar2(255)
SQL> exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;
Execution Plan
-----------------------------------------
Plan hash value: 2407808827
-------------------------------------------------------------------------------
|Id  |Operation                         |Name    |Rows  |Bytes |Cost (%CPU)
-------------------------------------------------------------------------------
|  0 |SELECT STATEMENT                  |        | 8168 |16336 |  29    (0)
|  1 | COLLECTION ITERATOR PICKLER FETCH|STR2TBL | 8168 |16336 |  29    (0)

The optimizer guesses 8,168 rows. In all probability, the real number of rows is not anywhere near 8,168, however. So if I use this estimated row count in a bigger query, I’ll probably end up with the wrong plan (I like to say “wrong card = wrong plan, right card = right plan,” where card is cardinality).

Now, when I run the query and ask Oracle Database, “Which plan did you use?” I get to see “reality”—not an explain plan (explain plans do not always reflect reality)—and Listing 2 shows reality.

Code Listing 2: Viewing the “reality” of query execution (not the explain plan)

SQL> set autotrace off
SQL> set serveroutput off
SQL> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;
COLUMN_VALUE
--------------------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE
SQL> select * from table(dbms_xplan.display_cursor);
...
-----------------------------------------------------------------------------
|Id  |Operation                         |Name    |Rows  |Bytes |Cost (%CPU)
-----------------------------------------------------------------------------
|  0 |SELECT STATEMENT                  |        |      |      |  29  (100)
|  1 | COLLECTION ITERATOR PICKLER FETCH|STR2TBL | 8168 |16336 |  29    (0)

The reality in Listing 2 is not any different from the explain plan in Listing 1 at this point. Reality says, “I came up with a plan based on an estimated cardinality of 8,168 rows.” However, because the database actually ran the query in Listing 2, it has learned from its mistake. I run the query again in Listing 3, and you can see what the database learned.

The database hard-parsed that query, because the actual observed row counts were so far removed from what it guessed—it did not reuse the plan. In Listing 3, you can see that the estimated cardinality in the plan is different—the row count (6) is much lower (than 8,168). This can have a dramatic effect on the overall query plan used by the optimizer.

Code Listing 3: Optimizer learning via Cardinality Feedback

SQL> select *
2    from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t;
COLUMN_VALUE
--------------------------------------------
DBMS_PIPE
DBMS_OUTPUT
UTL_FILE
SQL> select * from table(dbms_xplan.display_cursor);
...
----------------------------------------------------------------------------
|Id  |Operation                         |Name    |Rows  |Bytes |Cost (%CPU)
----------------------------------------------------------------------------
|  0 |SELECT STATEMENT                  |        |      |      |  29  (100)
|  1 | COLLECTION ITERATOR PICKLER FETCH|STR2TBL |    6 |   12 |  29    (0)
----------------------------------------------------------------------------
Note
------
- cardinality feedback used for this statement

A new thing about an old tool. The next new thing I learned is about a new feature in the venerable old SQL*Plus tool. The learning began when I was asked on Ask Tom about trapping the SP2 errors in SQL*Plus, those you get when you have a bad SQL*Plus command such as this one: SQL> selct * from dual;

SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.

In this case, neither of the built-in SQL*Plus error handling capabilities—OSERROR or SQLERROR—will help you. The error is not an OS error such as “unable to open spool file,” and it is not a SQL error, because “selct” is not SQL, so the statement never got to the SQL layer. That SP2 error is uncatchable, and I wrote as much in my answer.

As I mentioned earlier, things change. So although my answer is dead-on correct for Oracle Database 10g Release 2 and earlier releases, it is not technically true in Oracle Database 11g and above.

SQL*Plus in Oracle Database 11g added an error logging facility. So a session can now issue

SQL> set errorlogging on

and have any SQL, OS, or SP2 errors logged into a logging table, much as with DML error logging. Additionally, you can have your errors tagged with an identifier, making it easy to find your error records. So you can now check (using SQL) at various times to see if you’ve hit an error, and your program that runs SQL*Plus to run a script can easily check to see if any errors have occurred in your session.

(Thanks to Enrique Aviles [bit.ly/beXbde] for pointing it out, and thanks to Arup Nanda for writing it up [see “SQL*Plus Error Logging”].)

Note that you need Oracle Database 11g SQL*Plus, not just Oracle Database 11g with an old version of SQL*Plus connected to it. This is a feature of SQL*Plus.

On the flip side, though, this means that the new SQL*Plus error logging is available for older database releases! You can connect to Oracle9i Database with Oracle Database 11g SQL*Plus by using the code in Listing 4.

Code Listing 4: Using SQL*Plus error logging with Oracle9i Database

$ sqlplus scott/tiger@ora9ir2
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 23 15:36:51 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
scott%ORA9IR2> set errorlogging on
scott%ORA9IR2> selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
scott%ORA9IR2> select statement, message
 2   from sperrorlog;
STATEMENT    MESSAGE
---------    -------
selct *      SP2-0734: unknown command beginning
from dual;   "selct * fr..." - rest of line ignored.

Once upon a time—and I remember the day I learned this, during a benchmark in 1993—I learned that unindexed foreign keys had some locking implications. Specifically, if you

• Update the parent table primary key (which does happen, because some “frameworks” update every column even if the value has not changed) or
• Delete from the parent table

then you should probably index the foreign key in the child table, or a full table lock will be placed on the child table for the duration of the transaction.

Sometime during my use of Oracle9i Database, I learned yet another modification to the “unindexed foreign keys and locking implications” rule above. The rule in Oracle9i Database has to include

• If you merge into the parent table

in addition to update and delete. As I was getting ready to add that to the second edition of Expert Oracle Database Architecture, I learned something new: the rule has changed again. The MERGE doesn’t always lock the table in Oracle Database 11g Release 1 and above. If you are interested in the full test case demonstrating this, I encourage you to read http://tkyte.blogspot.com/2010/04/something-i-recently-unlearned.html, where I first wrote this up (the example is too large to fit here).

So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

• Update the parent table primary key
• Delete from the parent table
• Merge into the parent table

And the rule in Oracle Database 11g Release 1 and later is that you will want an index on the foreign key of the child table if you do any of the following:

• Update the parent table primary key
• Delete from the parent table
• Use a merge that either updates the parent table primary key or deletes from the parent table

See http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html for more information on unindexed foreign keys, including a script for detecting whether you have any!