# Expert Oracle Practices # PL/SQL and the CBO

I am reading Expert Oracle Practices by Oracle Database Administration from the Oak Table. I’d like to summary and share what I learned.

This chapter PL/SQL and the CBO by Jože Senegačnik is pretty interesting. It tells how to use oracle extensible optimizer to set the selectivity and the cost of PL/SQL functions. This will help oracle optimizer know to choose a better plan when PL/SQL function is used in a SQL where cause.

Here is an example:

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS func1 DEFAULT SELECTIVITY 12, DEFAULT COST (312722, 5, 0);
--This association defines in this particular case default selectivity of 12 percent (0.12), a default CPU cost of 312,722, an I/O cost of 5, and a network cost of 0.

It uses the SQL syntax ASSOCIATE STATISTICS. This is another case to prove that DBA need to review new oracle document even you know more than 90% of the contents. The left 10% or less will help you in some cases. I admin that I didn’t aware this before.

Also the author told us how to estimate the COST by execution time.

In the following case, we will calculate the CPU cost for a function that always executes in 0.002 seconds:
SQL> variable a number
SQL> begin :a := 1000*DBMS_ODCI.ESTIMATE_CPU_UNITS(0.002);end;
2 /
PL/SQL procedure successfully completed.
SQL> print a;
A
----------
312722,323

At last, if you want to remove the given statistics, using DISASSOCIATE STATISTICS syntax

SQL> DISASSOCIATE STATISTICS FROM FUNCTIONS Demo_Func1;
Statistics disassociated