What is meaning of COST in execution plan in Oracle:
COST in execution plan is a relative factor which tells how much single block read,multi block reads, temporay space operations will take place along with , CPU cycles (cpu cycle can differe in various operations). If two costs are closer than it does not mean their execution time will also be closer. Generally index scan needs more CPU cycles than FTS. In same way Nested loop join involves more CPU than Hash Join and Sort Merge join., while hash and sort-merge join needs temporay work area UGA(part of PGA)(as well temp space depends) which is not needed in Nested Loop join.
You should gather system statistics at representative workload so that oracle knows what is single block read time, what is multiblock read time and what is CPU speed etc. This way cost calclulated is absolutely best provided the table,index,columns statistics(histograms, if needed) are accurate.
e.g. Consider below two queries for which cost are closer but execution time is differing a lot
1. select count(*) from (select * from mytab union all select * from mytab); and
2 select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
Their cost in execution plan is quite close but execution time of second query is less than half of first query. Below uses dynamic sampling but it does not make difference.
SQL> conn scott/tiger
Connected.
SQL> create table mytab as select * from all_objects;
Table created.
SQL> explain plan for select count(*) from (select * from mytab union all select * from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1229214271
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 313 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 115K| 313 (1)| 00:00:04 |
| 3 | UNION-ALL PARTITION| | | | |
| 4 | TABLE ACCESS FULL | MYTAB | 46942 | 159 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL | MYTAB| 46942 | 159 (2)| 00:00:02 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
16 rows selected.
SQL> explain plan for select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3291583229
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 2 | 26 | 316 (1)| 00:00:04 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT AGGREGATE | | 1 | | | |
| 5 | TABLE ACCESS FULL| MYTAB| 46942 | | 158 (1)| 00:00:02 |
| 6 | SORT AGGREGATE | | 1 | | | |
| 7 | TABLE ACCESS FULL| MYTAB | 46942 | | 158 (1)| 00:00:02 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> --now alter system flsuh buffer_cache from sys session
SQL> alter session set statistics_level=all;
Session altered.
You notice COST of both queries are very close. Now check actual execution time
SQL> set timing on
SQL> select count(*) from (select * from mytab union all select * from mytab);
COUNT(*)
----------
100192
Elapsed: 00:00:01.77
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 5wn0myv3kcsvs, child number 0
-------------------------------------
select count(*) from (select * from mytab union all select * from mytab)
Plan hash value: 1229214271
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.77 | 1386 |
| 2 | VIEW | | 1 | 115K| 100K|00:00:01.60 | 1386 |
| 3 | UNION-ALL PARTITION| | 1 | | 100K|00:00:01.10 | 1386 |
| 4 | TABLE ACCESS FULL | MYTAB | 1 | 46942 | 50096 |00:00:00.15 | 693 |
| 5 | TABLE ACCESS FULL | MYTAB| 1 | 46942 | 50096 |00:00:00.10 | 693 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.09
SQL> --now alter system flsuh buffer_cache from sys session
SQL> select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
SUM(C)
----------
100192
Elapsed: 00:00:00.61
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gq7ca01amc88s, child number 0
-------------------------------------
select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab)
Plan hash value: 3291583229
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.60 | 1386 | 689 |
| 2 | VIEW | | 1 | 2 | 2 |00:00:00.60 | 1386 | 689 |
| 3 | UNION-ALL | | 1 | | 2 |00:00:00.60 | 1386 | 689 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.35 | 693 | 689 |
| 5 | TABLE ACCESS FULL| MYTAB | 1 | 46942 | 50096 |00:00:00.27 | 693 | 689 |
| 6 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.25 | 693 | 0 |
| 7 | TABLE ACCESS FULL| MYTAB | 1 | 46942 | 50096 |00:00:00.15 | 693 | 0 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
22 rows selected.
Elapsed: 00:00:00.10
SQL> spool off
You see second query is more than twice faster than first query
COST in execution plan is a relative factor which tells how much single block read,multi block reads, temporay space operations will take place along with , CPU cycles (cpu cycle can differe in various operations). If two costs are closer than it does not mean their execution time will also be closer. Generally index scan needs more CPU cycles than FTS. In same way Nested loop join involves more CPU than Hash Join and Sort Merge join., while hash and sort-merge join needs temporay work area UGA(part of PGA)(as well temp space depends) which is not needed in Nested Loop join.
You should gather system statistics at representative workload so that oracle knows what is single block read time, what is multiblock read time and what is CPU speed etc. This way cost calclulated is absolutely best provided the table,index,columns statistics(histograms, if needed) are accurate.
e.g. Consider below two queries for which cost are closer but execution time is differing a lot
1. select count(*) from (select * from mytab union all select * from mytab); and
2 select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
Their cost in execution plan is quite close but execution time of second query is less than half of first query. Below uses dynamic sampling but it does not make difference.
SQL> conn scott/tiger
Connected.
SQL> create table mytab as select * from all_objects;
Table created.
SQL> explain plan for select count(*) from (select * from mytab union all select * from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1229214271
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 313 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | | 115K| 313 (1)| 00:00:04 |
| 3 | UNION-ALL PARTITION| | | | |
| 4 | TABLE ACCESS FULL | MYTAB | 46942 | 159 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL | MYTAB| 46942 | 159 (2)| 00:00:02 |
----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
16 rows selected.
SQL> explain plan for select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3291583229
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 316 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | | 2 | 26 | 316 (1)| 00:00:04 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT AGGREGATE | | 1 | | | |
| 5 | TABLE ACCESS FULL| MYTAB| 46942 | | 158 (1)| 00:00:02 |
| 6 | SORT AGGREGATE | | 1 | | | |
| 7 | TABLE ACCESS FULL| MYTAB | 46942 | | 158 (1)| 00:00:02 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> --now alter system flsuh buffer_cache from sys session
SQL> alter session set statistics_level=all;
Session altered.
You notice COST of both queries are very close. Now check actual execution time
SQL> set timing on
SQL> select count(*) from (select * from mytab union all select * from mytab);
COUNT(*)
----------
100192
Elapsed: 00:00:01.77
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 5wn0myv3kcsvs, child number 0
-------------------------------------
select count(*) from (select * from mytab union all select * from mytab)
Plan hash value: 1229214271
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.77 | 1386 |
| 2 | VIEW | | 1 | 115K| 100K|00:00:01.60 | 1386 |
| 3 | UNION-ALL PARTITION| | 1 | | 100K|00:00:01.10 | 1386 |
| 4 | TABLE ACCESS FULL | MYTAB | 1 | 46942 | 50096 |00:00:00.15 | 693 |
| 5 | TABLE ACCESS FULL | MYTAB| 1 | 46942 | 50096 |00:00:00.10 | 693 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
20 rows selected.
Elapsed: 00:00:00.09
SQL> --now alter system flsuh buffer_cache from sys session
SQL> select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab);
SUM(C)
----------
100192
Elapsed: 00:00:00.61
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gq7ca01amc88s, child number 0
-------------------------------------
select sum(c) from (select count(*) c from mytab union all select count(*) c from mytab)
Plan hash value: 3291583229
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.60 | 1386 | 689 |
| 2 | VIEW | | 1 | 2 | 2 |00:00:00.60 | 1386 | 689 |
| 3 | UNION-ALL | | 1 | | 2 |00:00:00.60 | 1386 | 689 |
| 4 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.35 | 693 | 689 |
| 5 | TABLE ACCESS FULL| MYTAB | 1 | 46942 | 50096 |00:00:00.27 | 693 | 689 |
| 6 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.25 | 693 | 0 |
| 7 | TABLE ACCESS FULL| MYTAB | 1 | 46942 | 50096 |00:00:00.15 | 693 | 0 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
22 rows selected.
Elapsed: 00:00:00.10
SQL> spool off
You see second query is more than twice faster than first query
Posts
