Home Database Mysql Tutorial 使用dbms_stat采集统计信息时estimate_percent和cascade的默认值

使用dbms_stat采集统计信息时estimate_percent和cascade的默认值

Jun 07, 2016 pm 03:28 PM
dbms stat use information statistics collection

收集统计信息可以用dbms_stats包,通常用这样的语法:exec dbms_stat.gather_table_stats(ownname='xxx', tabname='xxx', estimate_percent=xxx, method_opt='xxx', cascade=xxx); 其中estimate_percent表示选择的采样比例,如果太低,收集速度会快,但可能

收集统计信息可以用dbms_stats包,通常用这样的语法:exec dbms_stat.gather_table_stats(ownname=>'xxx', tabname=>'xxx', estimate_percent=>xxx, method_opt=>'xxx', cascade=>xxx);

其中estimate_percent表示选择的采样比例,如果太低,收集速度会快,但可能不会很准确,如果太高,收集速度会慢,但比较准确,各有利弊。但同时也支持默认方式:exec dbms_stat.gather_table_stats(ownname=>'xxx', tabname=>'xxx');,那这里收集的采样比例是多少呢?

做个实验:

SQL> create table t2 as select * from dba_objects;
Table created.

SQL> select count(*) from t2;
COUNT(*)
----------
11218

SQL> create index idx_t2 on t2(object_id);
Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'T2');
PL/SQL procedure successfully completed.

查询dba_tables表,看到NUM_ROWS值是11218,说明此处采样比例是100%。

再查询dba_indexes表,看到索引IDX_T2的相关统计列已经有值了,说明索引也进行了分析,即CASCADE默认值是TRUE。

再次查询dba_ind_columns表,看到列已经有了值,例如:COLUMN_POSITION、COLUMN_LENGTH等,也证明了CASCADE默认值是TRUE。

总结:

使用dbms_stats.gather_table_stats(ownname=>'SYS', tabname=>'T2');方式采集统计信息,

1、estimate_percent值默认是100%(其实estimate_percent可以设置为NULL,和100%作用相同,我理解NULL和不设这个值也相同,根据三段论传递,那么不设estimate_percent即默认是100%)。

2、CASCADE默认值是TRUE,即会对表、索引和列都会进行分析采集统计信息。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to use magnet links How to use magnet links Feb 18, 2024 am 10:02 AM

How to use magnet links

How to use mdf and mds files How to use mdf and mds files Feb 19, 2024 pm 05:36 PM

How to use mdf and mds files

What software is crystaldiskmark? -How to use crystaldiskmark? What software is crystaldiskmark? -How to use crystaldiskmark? Mar 18, 2024 pm 02:58 PM

What software is crystaldiskmark? -How to use crystaldiskmark?

How to download foobar2000? -How to use foobar2000 How to download foobar2000? -How to use foobar2000 Mar 18, 2024 am 10:58 AM

How to download foobar2000? -How to use foobar2000

How to use NetEase Mailbox Master How to use NetEase Mailbox Master Mar 27, 2024 pm 05:32 PM

How to use NetEase Mailbox Master

How to use Xiaoai Speaker How to connect Xiaoai Speaker to mobile phone How to use Xiaoai Speaker How to connect Xiaoai Speaker to mobile phone Feb 22, 2024 pm 05:19 PM

How to use Xiaoai Speaker How to connect Xiaoai Speaker to mobile phone

How to use Baidu Netdisk app How to use Baidu Netdisk app Mar 27, 2024 pm 06:46 PM

How to use Baidu Netdisk app

Simple guide to pip mirror source: easily master how to use it Simple guide to pip mirror source: easily master how to use it Jan 16, 2024 am 10:18 AM

Simple guide to pip mirror source: easily master how to use it

See all articles