执行计划的cardinality基数（rows）评估

最新推荐文章于 2021-04-03 19:02:51 发布
最新推荐文章于 2021-04-03 19:02:51 发布
阅读量1.3k 收藏
点赞数
文章标签： 数据库 开发工具

执行计划的cardinality基数（rows）评估：


         执行计划的cardinality列（基数）是说从数据表结果集或者索引返回多少行数据。

基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！

         Cardinality的值对于CBO做出正确的执行计划来说至关重要。 如果CBO获得的Cardinality值不够准确（通常是没有做过分析或者分析数据过旧造成），在执行计划成本计算上就会出现偏差，从而导致CBO错误的制定出执行计划。

        

         如果表没有做过分析，那么CBO可以通过动态采样的方式来获取分析数据，执行获取的执行计划可能会出现偏差，可能是正确的执行计划也可能是错误的执行计划。

 

         如果表分析过，但是分析信息过旧，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而也可能导致错误的执行计划。

        

         查询sql访问的谓词如果超出了数据库中统计信息记录的值限，比如，某个数值范围是1~100，当访问200的取值，超出了这个范围，由于统计信息收集通常是通过采样，定时来实现的，所以真实的数据中可能仍然存在满足条件的数据，这时数据库会估算指定一个density（比重）用于cardinality的计算。

 

 

----查询sql访问的谓词超出了数据库中统计信息记录的值限分析实验：

1.环境准备

sys@ORCL>create user shall identified by shall;

User created.

 

sys@ORCL>grant connect,resource to shall;

sys@ORCL>grant dba to shall;

Grant succeeded.

 

shall@ORCL>create table zhong as select rownum numid from dba_objects where rownum<20001;

 

shall@ORCL>insert into zhong select * from zhong;

20000 rows created.

shall@ORCL>insert into zhong select * from zhong;

40000 rows created.

shall@ORCL>insert into zhong select * from zhong;

80000 rows created.

shall@ORCL>commit;

Commit complete.

 

2.收集该表统计信息

shall@ORCL>exec dbms_stats.gather_table_stats(user,'ZHONG');

PL/SQL procedure successfully completed.

 

----查询记录一下相关的统计信息

shall@ORCL>select table_name,num_rows,blocks,avg_row_len,sample_size from user_tables where table_name='ZHONG';

 

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE

------------------------------ ---------- ---------- ----------- -----------

ZHONG                              160000        249           4      160000

 

shall@ORCL>select column_name,data_type,num_distinct,density,1/num_distinct,last_analyzed,sample_size,histogram from user_tab_cols where table_name='ZHONG';

COLUMN_NAME DATA_TYPE   NUM_DISTINCT    DENSITY 1/NUM_DISTINCT LAST_ANALYZED       SAMPLE_SIZE HISTOGRAM

----------- ----------- ------------ ---------- -------------- ------------------- ----------- ---------------

NUMID       NUMBER             20128 .000049682     .000049682 2016-05-28 16:25:44      160000 NONE

 

------注意以上的density（比重）计算，在不存在柱状图时，density=1/num_distinct，此时计算cardinality（基数）时，会选择使用公式：

cardinalily=num_rows/num_distinct=num_rows*(1/num_distinct)

 

3.数据库中的基本信息如下

shall@ORCL>select max(numid) from zhong;

MAX(NUMID)

----------

     20000

shall@ORCL>select count(*) from zhong;

  COUNT(*)

----------

    160000

shall@ORCL>select count(*) from zhong where numid=1000;

  COUNT(*)

----------

         8

 

4.开启10053事件跟踪查询

shall@ORCL>alter session set events '10053 trace name context forever,level 1';

Session altered.

 

shall@ORCL>explain plan for select count(*) from zhong where numid=1000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=10000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=25000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=30000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=40000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=50000;

Explained.

 

shall@ORCL>select value from v$diag_info where name = 'Default Trace File';

VALUE

-----------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22824.trc

 

shall@ORCL> alter session set events '10053 trace name context off';

Session altered.

 

5.分析跟踪文件

shall@ORCL>ho vim /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22824.trc

5.1 numid为1000

QUERY BLOCK TEXT

****************

select count(*) from zhong where numid=1000

---------------------

 

----首先基于统计信息部分，我们可以看到关于表中行数、数据块数、平均行长等信息：

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: ZHONG  Alias: ZHONG

    #Rows: 160000  #Blks:  249  AvgRowLen:  4.00

 

----在单表访问路径部分，紧接着的信息是cardinality（基数）的评估，评估的结果有：

Access path analysis for ZHONG

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for ZHONG[ZHONG]

  Table: ZHONG  Alias: ZHONG

    Card: Original: 160000.000000  Rounded: 8  Computed: 7.95  Non Adjusted: 7.

95

 

----全表访问路径的成本是70.04

  Access Path: TableScan

    Cost:  70.04  Resp: 70.04  Degree: 0

      Cost_io: 69.00  Cost_cpu: 33773239

      Resp_io: 69.00  Resp_cpu: 33773239

  Best:: AccessPath: TableScan

         Cost: 70.04  Degree: 1  Resp: 70.04  Card: 7.95  Bytes: 0

***************************************

 

----这个查询最后的执行计划，其rows就是评估的8，完全符合真实的数据情况：

Starting SQL statement dump

user_id=91 user_name=SHALL module=SQL*Plus action=

sql_id=b33sadfyttasm plan_hash_value=-411406617 problem_type=3

----- Current SQL Statement for this session (sql_id=b33sadfyttasm) -----

explain plan for select count(*) from zhong where numid=1000

sql_text_length=61

sql=explain plan for select count(*) from zhong where numid=1000

----- Explain Plan Dump -----

----- Plan Table -----

============

Plan Table

============

--------------------------------------+-----------------------------------+

| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |

--------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |         |       |       |    70 |           |

| 1   |  SORT AGGREGATE     |         |     1 |     4 |       |           |

| 2   |   TABLE ACCESS FULL | ZHONG   |     8 |    32 |    70 |  00:00:01 |

--------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - filter("NUMID"=1000)

Content of other_xml column

===========================

  db_version     : 11.2.0.1

  parse_schema   : SHALL

  plan_hash      : 3883560679

  plan_hash_2    : 2551558790

 

5.2 numid为25000时

----当查询块访问numid=25000时，超出了最大数据范围：

****************

QUERY BLOCK TEXT

****************

select count(*) from zhong where numid=25000

---------------------

 

---在统计信息中显示。注意，此时oracle分配了一个估算密度，0.000037为out-of-range的计算，对于此次查询，估算的cardinality是6行

Access path analysis for ZHONG

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for ZHONG[ZHONG]

  Using prorated density: 0.000037 of col #1 as selectvity of out-of-range/non-

existent value pred

  Table: ZHONG  Alias: ZHONG

    Card: Original: 160000.000000  Rounded: 6  Computed: 5.96  Non Adjusted: 5.

96

  Access Path: TableScan

    Cost:  70.04  Resp: 70.04  Degree: 0

      Cost_io: 69.00  Cost_cpu: 33773239

      Resp_io: 69.00  Resp_cpu: 33773239

  Best:: AccessPath: TableScan

         Cost: 70.04  Degree: 1  Resp: 70.04  Card: 5.96  Bytes: 0

***************************************

 

----其执行计划如下

Starting SQL statement dump

user_id=91 user_name=SHALL module=SQL*Plus action=

sql_id=cha9b2hzv327m plan_hash_value=-411406617 problem_type=3

----- Current SQL Statement for this session (sql_id=cha9b2hzv327m) -----

explain plan for select count(*) from zhong where numid=25000

sql_text_length=62

sql=explain plan for select count(*) from zhong where numid=25000

----- Explain Plan Dump -----

----- Plan Table -----

============

Plan Table

============

--------------------------------------+-----------------------------------+

| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |

--------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |         |       |       |    70 |           |

| 1   |  SORT AGGREGATE     |         |     1 |     4 |       |           |

| 2   |   TABLE ACCESS FULL | ZHONG   |     6 |    24 |    70 |  00:00:01 |

--------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - filter("NUMID"=25000)

Content of other_xml column

===========================

  db_version     : 11.2.0.1

  parse_schema   : SHALL

  plan_hash      : 3883560679

  plan_hash_2    : 2551558790

 

----当numid更加远离实际值时，则prorated density趋向于一个确定的值：

Using prorated density: 0.000003 of col #1 as selectvity of out-of-range/non-

existent value pred

 

----此时的cardinality估算为1

Table: ZHONG  Alias: ZHONG

    Card: Original: 160000.000000  Rounded: 1  Computed: 0.50  Non Adjusted: 0.

50

 

----执行计划显示如下：

============

Plan Table

============

--------------------------------------+-----------------------------------+

| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |

--------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |         |       |       |    70 |           |

| 1   |  SORT AGGREGATE     |         |     1 |     4 |       |           |

| 2   |   TABLE ACCESS FULL | ZHONG   |     1 |     4 |    70 |  00:00:01 |

--------------------------------------+-----------------------------------+

 

 

6.当统计信息过旧时，如下

----环境准备，接上

shall@ORCL>insert into zhong select * from zhong;

160000 rows created.

shall@ORCL>commit;

Commit complete.

shall@ORCL>select count(*) from zhong;

  COUNT(*)

----------

    320000

shall@ORCL>select count(*) from zhong where numid=2000;

  COUNT(*)

----------

        16

shall@ORCL>select max(numid) from zhong ;

MAX(NUMID)

----------

     20000

 

----此时，不再做统计信息收集，然后开启10053事件跟踪查询

shall@ORCL>alter session set events '10053 trace name context forever,level 1';

Session altered.

 

shall@ORCL>explain plan for select count(*) from zhong where numid=1000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=10000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=20000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=30000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=40000;

Explained.

shall@ORCL>explain plan for select count(*) from zhong where numid=50000;

Explained.

 

shall@ORCL>alter session set events '10053 trace name context off';

Session altered.

 

----再次分析下跟踪文件，当numid小于20000时，cardinality（基数）的评估结果还是8行

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for ZHONG[ZHONG]

  Table: ZHONG  Alias: ZHONG

    Card: Original: 160000.000000  Rounded: 8  Computed: 7.95  Non Adjusted: 7.

95

  Access Path: TableScan

    Cost:  70.04  Resp: 70.04  Degree: 0

      Cost_io: 69.00  Cost_cpu: 33773239

      Resp_io: 69.00  Resp_cpu: 33773239

  Best:: AccessPath: TableScan

         Cost: 70.04  Degree: 1  Resp: 70.04  Card: 7.95  Bytes: 0

***************************************

 

----当numid大于20000时，oracle分配了一个估算密度，0.000025为out-of-range的计算，对于此次查询，估算的cardinality是4行

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for ZHONG[ZHONG]

  Using prorated density: 0.000025 of col #1 as selectvity of out-of-range/non-

existent value pred

  Table: ZHONG  Alias: ZHONG

    Card: Original: 160000.000000  Rounded: 4  Computed: 3.97  Non Adjusted: 3.

97

  Access Path: TableScan

    Cost:  70.04  Resp: 70.04  Degree: 0

      Cost_io: 69.00  Cost_cpu: 33773239

      Resp_io: 69.00  Resp_cpu: 33773239

  Best:: AccessPath: TableScan

         Cost: 70.04  Degree: 1  Resp: 70.04  Card: 3.97  Bytes: 0

***************************************

 

----所以如果表分析过，但是分析信息过旧时，这时CBO就不会在使用动态采样，而是使用这些旧的分析数据，从而也可能导致错误的执行计划。

 

 

7.如果表未做过分析，那么在执行时会进行动态采样，如下：

----首先基于统计信息部分，可以看到关于表中行数、数据块数、平均行长等信息：（注意此时，这里的rows为2941是采样估算值，实际表中的记录为160000条）

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: ZHONG  Alias: ZHONG  (NOT ANALYZED)

    #Rows: 2941  #Blks:  36  AvgRowLen:  100.00

 

----紧接着的信息是cardinality（基数）的评估，但是因为之前表并未进行分析，所以这里通过动态采样的方式来获取分析数据，动态采样level为2：

Access path analysis for ZHONG

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for ZHONG[ZHONG]

 

*** 2016-05-28 17:07:28.850

** Performing dynamic sampling initial checks. **

** Dynamic sampling initial checks returning TRUE (level = 2).

** Dynamic sampling updated table stats.: blocks=249

 

----评估的结果大约4行：

Table: ZHONG  Alias: ZHONG

    Card: Original: 148957.333333  Rounded: 4  Computed: 3.95  Non Adjusted: 3.

95

 

----全表访问路径成本是69.98

  Access Path: TableScan

    Cost:  69.98  Resp: 69.98  Degree: 0

      Cost_io: 69.00  Cost_cpu: 31564805

      Resp_io: 69.00  Resp_cpu: 31564805

  Best:: AccessPath: TableScan

         Cost: 69.98  Degree: 1  Resp: 69.98  Card: 3.95  Bytes: 0

***************************************

 

----最后的执行计划如下：

============

Plan Table

============

--------------------------------------+-----------------------------------+

| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |

--------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT    |         |       |       |    70 |           |

| 1   |  SORT AGGREGATE     |         |     1 |    13 |       |           |

| 2   |   TABLE ACCESS FULL | ZHONG   |     4 |    52 |    70 |  00:00:01 |

--------------------------------------+-----------------------------------+

Predicate Information:

----------------------

2 - filter("NUMID"=1000)

Content of other_xml column

===========================

  db_version     : 11.2.0.1

  parse_schema   : SHALL

  dynamic_sampling: 2

  plan_hash      : 3883560679

  plan_hash_2    : 2551558790

 

 

 

--------------------------------------reference  eygle  DBA手记2

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2108486/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2108486/

cuimiwan3140
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL explain 中的 rows 究竟是如何计算的？
架构精进之路
08-05 3628
今天同事在处理系统慢SQL时遇到几个疑惑的问题，简单描述如下~【背景铺垫】相关表：CREATE TABLE test_table ( id bigint(20) UNSIGNED N...
oracle执行计划中的基数,oracle执行计划基数(cardinality)计算方式
weixin_32432759的博客
04-03 901
num_rows: dba_tables.num_rowsnum_nulls: dba_tab_cols.num_nullsnum_distinct:dba_tab_cols.num_distinctCard:cardinality基数oracle执行计划基数(cardinality)计算方式1.单列无直方图计算方式Card=(1/num_distinct)*(num_rows-num_nulls...
Oracle 执行计划（2）-基数 cardinality【即估算一个操作返回的行数】
haiross的专栏
12-10 9356
Oracle 执行计划（2）-基数 cardinality 执行计划的cardinality列。基数是说从数据表，结果集或者索引返回多少行数据。 基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！ 版本10G 默认 表空间信息： TABLESPACE_NAME USERS BLOCK_SIZE 8192
Cardinality 对执行计划的重要性
weixin_33877885的博客
05-04 223
　　执行计划分析中的Cardinality代表oracel优化器要创建执行计划时，认为某个表有多少条记录，当一个表曾经达到大量数据时并有做了表分析之后，然后后来delete掉了大量数据，却忘记了再做一个表分析时，Cardinality还是一样很大，那么这个时候oracle优化器做出了执行计划将会不准，影响查询的性能。 通常情况下当表创建后还没有做表分析时，oracle优化器创建查询的执...
DB优化小常识 - 执行计划中Cardinality的计算
cuixie2370的博客
07-18 417
DB中如果我们使用CBO作为优化器，一条SQl语句就会有多种执行路径，那么CBO会计算各个执行路径的访问代价，选择代价最小的作为我们的执行计划。CBO在做访问路径估算的时候有一个很重要的...
Oracle 执行计划（2）-基数 cardinality
热门推荐
海鲨MYSQL精英架构师
04-21 1万+
Oracle  执行计划（2）-基数执行计划的cardinality列。基数是说从数据表，结果集或者索引返回多少行数据。基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！版本10G 默认 表空间信息：TABLESPACE_NAMEUSERSBLOCK_SIZE8192EXTENT_MANAGEMENTLOCALALLOCATION_TYPESYSTEMSEGMENT_SPACE_MA
Oracle优化器 Cardinality基数与Selectivity选择性
12-14
CBO选择执行计划时，会评估每个候选计划的成本，选择成本最低的那个。成本是基于涉及的表、索引、列等的统计信息来计算的，包括IO、CPU和网络资源的消耗。在没有系统统计信息时，CBO主要基于IO成本进行计算。 在CBO...
ORACLE数据库查看执行计划的方法
09-11
在Oracle数据库中，执行计划是理解SQL语句执行效率的关键，因为它揭示了Oracle如何处理查询，包括表的访问方式、连接顺序、索引使用、排序和分组等操作。了解执行计划有助于诊断性能问题并进行优化。以下是关于...
Oracle中直方图对执行计划的影响详解
09-09
在Oracle数据库中，执行计划是由成本基础优化器（Cost-Based Optimizer, CBO）根据表的统计信息生成的。直方图是一种用于更精确地描述数据分布的统计工具，它对于CBO确定最优执行路径至关重要。当CBO假设数据均匀...
浅析MySQL的基数统计
01-19
MySQL执行计划也会基于Cardinality选择索引。 通过下面的方式可以看到表中各列的基数。 比如这个经典的例子： 有一列为sex，那对于sex列中存储的值来说 非男即女，它的基数最大就是2。 那也就完全没有必要为sex建立...
Oracle 执行计划——基数
weixin_44518404的博客
01-14 1601
所谓基数就是说Oracle中索引建立的基数是否得当，以及选择性的相关问题。
oracle中执行计划中的cardinality
IAMGOD11的专栏
07-30 2467
官网解释： The ratio of distinct values to the number of table rows. A column with only two distinct values in a million-row table would have low cardinality. 是所选择的列数的不同值在整合表中所占的比例。
oracle cardinality对于执行计划的影响
12-24 495
1,SQL> create table t1(a int,b int);表已创建。SQL> create table t2(a int,b int);表已创建。SQL> insert int...
ORACLE 执行计划中cost cardinality bytes cpu_cost io_cost解释
novelly的专栏
02-16 4847
ORACLE 10G R2_执行计划中cost cardinality bytes cpu_cost io_cost解释 2009-12-29 15:42 从网上找到的资料，加上我在文档中查到的内容： ■ Cost The cost assigned to each step of the query plan by the CBO. The CBO works by ge
Oracle 执行计划（3）-两表连接基数
海鲨MYSQL精英架构师
04-21 3557
Oracle  执行计划（3）-两表连接基数 1 公式：   基数= 连接选择率*过滤条件1基数+过滤条件2的基数连接选择率=((num_rows(表1)-num_nulls(表1连接字段))/num_rows(表1))*((num_rows(表2)-num_nulls(表2连接字段))/num_rows(表2)) /Greater(num_distinct(表1连接字段),num_distinc
oracle执行计划中的基数,一个真实的基数导致CBO选错执行计划！
weixin_39651488的博客
04-03 260
有很多情况，导致CBO选错执行计划，良好的统计信息在优化当中还是非常有必要的。在其他文章当中简单说过Oracle表之间的连接方式。如果一个表的基数算错，很可能就会走错执行计划。PgSQLSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------...
Oracle优化05-执行计划
小工匠
12-26 1万+
思维导图系列文章Oracle-SQL Explain Plan解读概述如果要分析某条SQL的性能问题，通常来讲，我们首先要看SQL的执行计划，看看SQL的每一步执行计划是否存在问题。 如果某一条SQL平常执行的都很好，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本上可以判断是执行计划出现了问题。 看懂执行计划变成了SQL优化（其实在大多数的情况下，SQL优化指的是SQL的性能问题定位
Oracle执行计划1.ppt
最新发布
11-21
这个表包含了执行计划中的各种元数据，如操作类型、对象信息、成本、基数等。创建`PLAN_TABLE`的SQL语句如下： ```sql CREATE TABLE plan_table ( statement_id VARCHAR2(30), timestamp DATE, remarks VARCHAR2...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值