Question: When do
I use the no_cpu_costing hint on a SQL statement. I am
on 12c and I understand that the no_cpu_costing hint can
improve SQL performance. Can you explain how the
no_cpu_costing hint works?
Answer: The no_cpu_costing hint
was introduced to turn off
the including of CPU costs in the
decision tree for choosing the "best" execution plan.
This hint turns off CPU costing for the SQL
statement. CBO then uses the IO cost model, which measures
everything in single-block reads and ignores CPU cost.
Make sure to read these important notes on
the optimizer cost model.
Oracle introduced CPU costing as the default
_optimizer_cost_model=cpu because most Oracle database are
CPU-bound and you want to optimizer SQL to minimize CPU
costs. However, there are cases where you want to use
I/O costs when optimizing a SQL statement (this can be done
system-wide by setting _optimizer_cost_model=io).
I/O-bound databases, (especially older 32-bit databases),
may want to utilize I/O-based SQL costing. The default
optimizer costing in Oracle10g and beyond is CPU, and it can
be changed to IO costing by using these techniques:
SQL> alter system set
optimizer_index_cost_adj=100 scope=both;
SQL> alter session set
“_optimizer_cost_model=io;
Set init.ora
hidden parameter for a permanent change:
SQL> alter system set
"_optimizer_cost_model"=io scope=both;
There are many documented cases in Oracle 12c and beyond
where you want to improve the speed of SQL statements by
turning-off CPU costing at the statement level:
select /*+ no_cpu_costing */ col1
from mytab;
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
