oracle 表分析（如何在ORACLE系统中对表进行统计分析）

本文目录

• 如何在ORACLE系统中对表进行统计分析
• oracle如何判断需要做表分析
• oracle 是否可以同时进行多个表分析
• Oracle的表分析是做什么的
• Oracle数据库表做表分析、索引分析的命令是什么
• 如何正确地分析Oracle数据库模式，表及索引
• 如何分析Oracle
• 我要对oracle 数据库 中的表 进行表分析
• oracle表分析命令dbms_stats分析表使用问题

如何在ORACLE系统中对表进行统计分析

begindbms_stats.gather_table_stats( ownname=》’XXXX’,tabname =》 ’XXXX’ ,ESTIMATE_PERCENT=》100,degree=》8, CASCADE=》true); end;

oracle如何判断需要做表分析

首先要了解为什么要进行表分析，表分析的结果被Oracle用于基于成本的优化生成更好的查询计划。所以，一般发现如有个表的查询性能比较差，或者原本有索引但是没有走，那就可以考虑对表进行分析。

oracle 是否可以同时进行多个表分析

可以并行分析

dbms_stats可以统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。

下边是一个例子：

exec dbms_stats.gather_schema_stats( -ownname =》 ’SCOTT’, -options =》 ’GATHER AUTO’, -estimate_percent =》 dbms_stats.auto_sample_size,-method_opt =》 ’for all columns size repeat’, -degree =》 15 -)

其中，degree参数用于指定统计收集的并行度

Oracle推荐设置DBMS_STATS的DEGREE参数为DBMS_STATS.AUTO_DEGREE，该参数允许Oracle根据对象的大小和并行性初始化参数的设置选择恰当的并行度。聚簇索引，域索引，位图连接索引不能并行收集。

Oracle的表分析是做什么的

分析的结果被Oracle用于基于成本的优化生成更好的查询计划。那么，问题在于：Oracle的表分析需要经常进行吗？还是只要跑一回就可以一直有效了？答：遇到当前表的查询或其他性能不好时，就可以对相应的表进行一次分析。１。经常对表做dml就需要分析，现在推荐使用dbms_stats包。

Oracle数据库表做表分析、索引分析的命令是什么

analyze table 表名 compute statisticsanalyze index 索引ID compute statistics如果想分析所有的表名和index名可以从视图user_tables，user_indexes取得相关的信息，自动生成SQL命令

如何正确地分析Oracle数据库模式，表及索引

在Oracle 数据库中，我们经常需要去手工分析表和索引，还有某个模式下所有的表和索引等等。oracle 提供了两种分析方法，分别是dbms_stats 包和analyze 命令。这些是我们在日常项目管理中的真实使用的方法，供参考。在10g 中分析某一个用户下所有的对象，如user_miki 用户Sql代码 收藏代码sys.dbms_stats.gather_schema_stats(ownname =》 ’user_miki’,estimate_percent =》 30,method_opt =》 ’FOR ALL INDEXED COLUMNS SIZE AUTO’,cascade =》 true,options =》 ’GATHER’);这是我在项目中分析一个用户所有对象的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。***隐藏网址***在10g 中分析某一个用户下某一个表及其索引，如user_miki 用户下info 表和它的索引Sql代码 收藏代码execute dbms_stats.gather_table_stats(ownname =》 ’user_miki’,tabname =》 ’info’,estimate_percent =》 30,method_opt =》 ’for all indexed columns size auto’);这是我在项目中单独分析一个用户中一张表的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。我们在google 时，还能看到使用analzye 命令分析表的方法。Sql代码 收藏代码analyze table table_name compute statistics;这个会将索引和表一起分析；Sql代码 收藏代码analyze table table_name compute statistics for table;这个仅分析表；analyze table table_name compute statistics for all columns;这个仅分析表，和上面所有的字段，生成柱形图analyze table table_name compute statistics for all indexed columns;这个仅分析表，和上面所有建立索引的字段，生成柱形图在9i 中，我们常常使用analyze 方法，在10g 以后，就改为dbms_stats.gather_schema_stats 类似的包来分析数据库对象了。10g 的分析函数包比9i 的analyze 方法更灵活一些。在10g 中，会有个系统的计划任务，每天10 点和周末两天都自动分析新的数据库对象和它认为统计信息已经过期的数据库对象。这个分析结果有时候不靠谱。因此，我都采用10g 中提供的dbms_stats.gather_schema_stats 方法作为补充，设置一个计划任务定期去分析业务用户下所有数据库对象。Sql代码 收藏代码variable job number;beginsys.dbms_job.submit(job =》 :job,what =》 ’sys.dbms_stats.gather_schema_stats(ownname =》 ’’USR_MIKI’’,estimate_percent =》 30,method_opt =》 ’’FOR ALL INDEXED COLUMNS SIZE AUTO’’,cascade =》 true,options =》 ’’GATHER’’);’,next_date =》 to_date(’16-12-2012 00:56:24’, ’dd-mm-yyyy hh34:mi:ss’),interval =》 ’sysdate+7’);commit;end;/例如，设置为每周六运行一次。

如何分析Oracle

以oracle表分析为例：drop table test;select count(*) from test;--创建测试表create table test(id number(9),nick varchar2(30));--插入测试数据begin for i in 1..100000 loop insert into test(id) values(i); end loop; commit;end;select * from test;--更新nick字段，使数据发生严重倾斜update test set nick=’abc’ where rownum《99999;--创建索引create index idx_test_nick on test(nick);update test set nick=’def’ where nick is null;--只对索引进行分析analyze index idx_test_nick compute statistics;select * from user_indexes;--查看索引名,对应存储的数据块,不同的key数量,记录数（行数）的分析信息select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS from user_indexes where index_name = ’IDX_TEST_NICK’;--dba_tab_col_statistics--查看表的统计信息select COLUMN_NAME, NUM_BUCKETS, num_distinct from USER_tab_columns where table_name = ’TEST’;select * from test where nick =’abc’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE)select * from test where nick =’def’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE)--由上可以看到,对索引分析之后,sql的执行路径都是基于规则的,索引的字段的偏移--先根据索引找到rowid,然后再根据rowid读取记录,这个过程肯定比全表扫描读取记录要慢--user_part_col_statistics 分区分析信息--分析表的第二列nickanalyze table test compute statistics for columns size 2 nick;select * from test where nick =’abc’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE)--根据上面的执行计划，还是按照规则来执行的--分析表analyze table test compute statistics for table;select * from test where nick =’abc’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes= 1499970) 1 0 TABLE ACCESS (FULL) OF ’TEST’ (Cost=49 Card=99998 Bytes=14 99970)--分析表之后，完全按照成本来执行--删除所有的统计数据，并只对表与列进行分析，不分析索引，--ORACLE使用CBO的优化器，并产生了正确的执行计划analyze table test delete statistics;--分析列nickanalyze table test compute statistics for table for columns size 2 nick;select * from test where nick =’abc’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes= 1499970) 1 0 TABLE ACCESS (FULL) OF ’TEST’ (Cost=49 Card=99998 Bytes=14 99970) --select * from test where nick =’def’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=2 Byt es=30) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE) (Cost =1 Card=2)--创建TEST表ID列上的索引，但不对索引进行分析create index idx_test_id on test(id);Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15 000) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1000 Bytes=15000) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_ID’ (NON-UNIQUE) (Cost=1 Card=400)--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick=’abc’的值特别的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引select * from test where id=5 and nick=’abc’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15 000) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1000 Bytes=15000) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_ID’ (NON-UNIQUE) (Cost=1 Card=400)--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick=’def’的值特别的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引select * from test where id=5 and nick=’def’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1 Byt es=15) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE) (Cost =1 Card=2)select * from test where nick=’def’ and id=5;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1 Byt es=15) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_NICK’ (NON-UNIQUE) (Cost =1 Card=2)--在分析ID列后，ORACLE发现ID列的选择度更高，所以不再选择IDX_TEST_NICK索引，而是选择IDX_TEST_IDanalyze table test compute statistics for columns size 1 id;select * from test where id=5 and nick=’def’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1 Byt es=7) 2 1 INDEX (RANGE SCAN) OF ’IDX_TEST_ID’ (NON-UNIQUE) (Cost=1 Card=1) /*下面来看另外一种情况，我们删除所有的统计数据，然后在ID列上创建唯一索引,在此条件下，只分析表与分析列nick,我们看到ORACLE走了正确的执行计划，走了UK_TEST_ID,其实从这里也给我们带来很多的启示：在主键与唯一键约束的列上是否需要直方图的问题?如果在这些列上有像这样的查询where id 》 100 and id 《 1000,我们还是需要有直方图的，但除此之外，好像真的没有直方图的必要了!*/analyze table test delete statistics; drop index idx_test_id;create unique index uk_test_id on test(id);--分析表的第二列nickanalyze table test compute statistics for table for columns size 2 nick;select * from test where id=5 and nick=’def’;Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF ’TEST’ (Cost=2 Card=1 Byt es=15) 2 1 INDEX (UNIQUE SCAN) OF ’UK_TEST_ID’ (UNIQUE) (Cost=1 Car d=100000) 从以上一系列的实验可以看出，对ORACLE的优化器CBO来说，表的分析与列的分析才是最重要的，索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时产生执行计划的复杂度问题。

我要对oracle 数据库 中的表 进行表分析

analyze table 表名 compute statistics (sample 20%);后面括号的是抽样统计，是为了加快分析速度，也可以不用，做全表分析。

oracle表分析命令dbms_stats分析表使用问题

主要收集如行数，列的distince、表段大小等等如果做表关联查询，统计信息也会用到一般要分析，不然统计信息不准可能走错执行计划，比如一个分区原先1行，插入后变几亿行，这时统计信息不准确的话可能就走错误执行计划了。