Collection Costs

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:

Quiz Night

Apart from the fact that the “Rows” figure for the FILTER operation at line 6 is blank, what’s the obvious error in this extract from an execution plan:

Surprising Error

Here’s an unexpected error that appeared recently while I was doing a test  on a database running 11.1.0.6.  (The fact that I got an error didn’t surprise me, it was Oracle’s choice of error for the mistake I’d made.)

Cut-n-Paste from a SQL*Plus session:

SQL> alter system set db_cache_size = 256m scope = memory;
alter system set db_cache_size = 256m scope = memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00001: unique constraint (.) violated

If you’re wondering, I was trying to reduce the db_cache_size from 384MB and the value had been set by the startup parameter file – so the ORA-02097 is probably trying to tell me that I can’t reduce the dynamic value below the initial minimum. (I didn’t check this, I just bounced the database with a change of parameter file, but it’s probably a reasonable guess.)

BCT

That’s “Block Change Tracking” if you’re not familiar with the acronym – a feature that appeared in the 10g with its own background process (CTWR – change tracking writer) to help rman do faster incremental backups on very large datafiles.

It’s very useful if you’re using large tablespaces (i.e. the “one file per tablespace”) option – but, as with many things Oracle produces, when you push the limits or move into combinations of features odd errors start to appear.

Saibabu Devabhaktuni has written a blog note about using BCT with physical standby databases – a relatively new enhancement to BCT, but possibly one you need to be cautious about for reasons he explains.