Oracle Magazine - September/October 2010

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

performance of your query will be negatively affected.

In Oracle Database 11 g, 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.

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

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