Cost Calculation Method in execution plan, execution plan cost

Cost Calculation Method in execution plan, execution plan cost

Concept:

Blevel: Binary Height = index height-1

Clustering_factor: Cluster factor, number of blocks in the table to be scanned after index scan, clustering_factor <= table blocks

Index scan calculation formula:

Cost =

Blevel +

Ceil (leaf_blocks * Valid tive index selecti.pdf) +

Ceil (clustering_factor * Valid tive table selecti.pdf)

The test is performed under the rule based optimizer used condition.

SQL> select leaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
        112          1               776

SELECT B. num_rows, a. num_distinct, a. num_nulls, utl_raw.cast_to_number (high_value) AS high_value, utl_raw.cast_to_number (low_value) AS low_value
, B. num_rows-a. num_nulls AS "NUM_ROWS-NUM_NULLS", utl_raw.cast_to_number (high_value)-utl_raw.cast_to_number (low_value) AS "HIGH_VALUE-LOW_VALUE"
FROM dba_tab_col_statistics a, dba_tables B
WHERE a. owner = B. owner
AND a. table_name = B. table_name
AND a. owner = 'Scott'
AND a. table_name = upper ('test ')
AND a. column_name = 'object _ id'

NUM_ROWS NUM_DISTINCT NUM_NULLSHIGH_VALUELOW_VALUENUM_ROWS-NUM_NULLSHIGH_VALUE-LOW_VALUE

50736 5073515382025073553818

Valid tive index selectivity = (limit-low_value)/(high_value-low_value)

SQL> select (1000-2) / (53820-2) selectivity from dual;

SELECTIVITY
-----------
0.018543982
SQL> SELECT OWNER FROM TEST WHERE OBJECT_ID <1000;

953 rows have been selected.


Implementation plan
-------------------------------------------------- --------
Plan hash value: 1810195980

-------------------------------------------------- -------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------- -------------------
| 0 | SELECT STATEMENT | | 941 | 10351 | 19 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 941 | 10351 | 19 |
| * 2 | INDEX RANGE SCAN | IDX_T | 941 | | 4 |
-------------------------------------------------- -------------------

Predicate Information (identified by operation id):
-------------------------------------------------- -

    2-access ("OBJECT_ID" <1000) 

1. Back table io = ceil (clustering_factor * Valid tive table selecti.pdf) = 19-4 = 15

2.blevel +ceil(leaf_blocks *effective index selectivity)

The larger the cost value of Oracle, is the execution plan of this SQL statement worse?

In theory, the larger the cost value, the SQL Execution Plan is not good.

However, there is another premise that the analysis data of your table should be correct.
The cost value is calculated based on the statistical information of the database table.
For example, you have a table ABC with 1 million rows. There is an index on column.

You
Select sum (B) FROM ABC WHERE A = 100
If the database does not have statistical information related to tables/indexes, the cost is indeed an approximate value. The deviation may be related to the number of A = 100 in this table.
For example, in the first 1 million records, only one/A = 100 of data records contains only 100. The execution time is much shorter.
However, if the table/index has not been analyzed
Select sum (B) FROM ABC WHERE A = 100
Or
Select sum (B) FROM ABC WHERE A = 1000
The query plan is the same.

However, if your table/index has been analyzed, the cost value may be more accurate.
During the analysis, we can know that only one data entry with A = 100 still has 100,000 records.
You can select the best query solution for the database as needed.
Assume that only one of the 1 million pieces of data is A = 100, and 1000 pieces of data are A = 0.8 million.
It is very likely that
Select sum (B) FROM ABC WHERE A = 100
Query plan using Indexes

While
Select sum (B) FROM ABC WHERE A = 1000
Use the query plan for full table scan.

What does plsql execution plan cost mean?

Unit for measuring the execution plan given by Oracle
The lower the Cost, the more reasonable the execution plan is.