# 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
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: