1) it is the percentage of the cost that is associated with CPU, as opposed to IO. Originally, the optimizer used IO costing only. Starting in 9i, CPU costing was added (so the things you do in the where clause, the functions you invoke - add to the cost) as an option and in 10g, it (CPU+IO costing) became the way it worked.
for example:
ops$tkyte%ORA11GR2> create or replace function f( x in number ) return number
2 as
3 begin
4 return x;
5 end;
6 /
ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 292 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 73141 | 292 (1)| 00:00:04 |
-------------------------------------------------------------------
<b>the cost of the full scan, a simple full scan, it 292. Of that - virtually nothing is CPU cost - very very tiny bit. Changing the query a bit:</b>
ops$tkyte%ORA11GR2> select count(*) from t where upper( ltrim( rtrim( substr( object_name, 1, 20 ) ))) = rpad( 'x', 20,'x') ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 294 (2)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 25 | | |
|* 2 | TABLE ACCESS FULL| T | 731 | 18275 | 294 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(UPPER(LTRIM(RTRIM(SUBSTR("OBJECT_NAME",1,20))))='xxxxxxxxx
xxxxxxxxxxx')
<b>the cost of the query went up a bit, and the CPU cost percentage when up as well. that was due to the extra work we are expecting to have to do to evaluate that predicate</b>
ops$tkyte%ORA11GR2> select count(*) from t where f(object_id) = 42;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 301 (4)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| T | 731 | 3655 | 301 (4)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("F"("OBJECT_ID")=42)
<b>using a plsql function increased the cpu cost contribution even more... </b>
2) see above, the more cpu intensive things you do, the higher the percentage of the total cost that can be attributed to cpu costing....
3) it is an estimate based on the cpu and IO costs. it is just a function that takes the costing information as input, looks at various metrics and assigns an estimated time to it.
the estimated time is typically very very very much an ESTIMATE. It would be unlikely for a query to exactly hit that estimate.