# Expert Oracle Practices # PL/SQL and the CBO | Alex Zeng's Blog
← A script to delete folder later than X days
A script to check cacti →
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.
SQL&gt; 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&gt; begin :a := 1000*DBMS_ODCI.ESTIMATE_CPU_UNITS(0.002);end;
SQL&gt; print a;
312722,323
At last, if you want to remove the given statistics, using DISASSOCIATE STATISTICS syntax
SQL&gt; DISASSOCIATE STATISTICS FROM FUNCTIONS Demo_Func1;
Statistics disassociated
Tagged with Expert Oracle Practices, Oracle CBO, pl/sql	About Alex ZengI 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.
# Expert Oracle Practices # PL/SQL and the CBO | Alex Zeng's Blog
← A script to delete folder later than X days
A script to check cacti →
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.
SQL&gt; 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 STATISTIC
S. 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&gt; begin :a := 1000*DBMS_ODCI.ESTIMATE_CPU_UNITS(0.002);end;
SQL&gt; print a;
312722,323
At last, if you want to remove the given statistics, using DISASSOCIATE STATISTICS syntax
SQL&gt; DISASSOCIATE STATISTICS FROM FUNCTIONS Demo_Func1;
Statistics disassociated
Tagged with Expert Oracle Practices, Oracle CBO, pl/sql	About Alex ZengI 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.