Oracle CPU Costing

原文連線：

http://itspace.javaeye.com/blog/644780

[@more@]

Oracle CPU Costing Oracle Tips by Burleson Consulting

Turning on CPU Costing

The default setting for the optimizer cost model is CHOOSE, meaning that the presence of CBO statistics will influence whether or not CPU costs are considered. According to the documentation, CPU costs are considered when SQL optimizer schema statistics are gathered with the dbms_stat.gather_system_stats package, which is the default behavior in Oracle10g, and CPU costs will be considered in all SQL optimization.

It gets tricky because of Bug 2820066 where CPU cost is computed whenever optimizer_index_cost_adj is set to a non-default value. Unless the 9.2.0.6 server patch set has been applied, the Oracle9i database may be generating CPU statistics, regardless of the CBO stats collection method.

To ensure that CPU costing is in use:

§ In Oracle9i, use dbms_stats.gather_system_stats to collect statistics

§ Set the undocumented parameter _optimizer_cost_model=cpu;

Turning on I/O Costing

I/O-bound databases, especially 32-bit databases, may want to utilize I/O-based SQL costing. The default optimizer costing in Oracle10g is CPU, and it can be changed to IO costing by using these techniques:

§ Ensure that optimizer_index_cost_adj is set to the default value (Oracle9i bug 2820066)

§ Add a no_cpu_costing hint in the SQL

§ alter session set “_optimizer_cost_model=io;

§ Set init.ora hidden parameter _optimizer_cost_model =io

Notes on Bug 2820066:

CPU cost is computed when optimizer_index_cost_adj is set to a non-default value. If optimizer_index_cost_adj is set to a non-default value, CPU costs are calculated regardless of the optimizer cost model used. If optimizer_index_cost_adj is set and the optimizer CPU cost model is not in use, but the explain plan shows that for queries not using domain indexes CPU costs are being calculated, this bug is likely in play.

In sum, CPU cost is always computed regardless of optimizer mode when optimizer_index_cost_adj is set in unpatched Oracle versions less than 10.1.0.2.

The following section shows how to change from CPU-based to I/O-based SQL optimization when the processing characteristics of the database change on a regular basis.

--=================================

Hi all

I have an explain plan like below. and I doubt about the COST.
How is the COST be calculated? I mean what does that mean? (CPU
rate?)

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1764098166

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7824 (1)|
00:01:34 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | INDEX FAST FULL SCAN| BIG_TABLE_PK | 6812K| 7824 (1)|
00:01:34 |
------------------------------------------------------------------------------

The cost reported by the optimizer has always been a time estimate. It
just uses an odd unit which is single block reads. In your particular
case you see the overall cost is 7824 single block reads. The TIME
column tells you this cost turned into time by simply multiplying the
cost with the single block read time which is available if you have
System Statistics enabled (default from 10g on). We can deduce from
both the 94 seconds and the cost of 7824 that your average single
block read time according to your system statistics seems to be 12 ms
(94,000 ms / 7824 is quite close to 12 ms).

You can check your SYS.AUX_STATS$ table for the SREADTIM value - if it
is blank then you are running with (default) NOWORKLOAD System
Statistics and the single block read time is derived from IOSEEKTIM,
IOTFRSPEED and your default DB_BLOCK_SIZE.

The 12 ms are the default single block read time with default
NOWORKLOAD System Statistics and a default 8 KB default block size.

The % CPU is the estimated percentage of CPU cost of the calculated
cost, since with System Statistics the optimizer also includes an
estimation of the CPU cost required to execute the statement.

For more information, see e.g.



Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:


30226684