DBMS_STATS.GATHEHighlander_TABLE_STATS

 

是因为Oracle的优化器是CBO,所以目的的总括数据对推行安顿的变型至关心器重要!

作用:DBMS_STATS.GATHER_TABLE_STATS总结表,列,索引的总计音信(私下认可参数下是对表举行直方图音信收集,包括该表的自个儿-表的行数、数据块数、行长等音信;列的辨析–列值的重复数、列上的空值、数据在列上的分布情况;索引的剖析-索引页块的多寡、索引的纵深、索引聚合因子).

DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS (   ownname          VARCHAR2,    
tabname          VARCHAR2,     partname         VARCHAR2,  
estimate_percent NUMBER,     block_sample     BOOLEAN,  
method_opt       VARCHAR2,   degree           NUMBER,  
granularity      VARCHAR2,     cascade          BOOLEAN,  
stattab          VARCHAR2,     statid           VARCHAR2,  
statown          VARCHAR2,   no_invalidate    BOOLEAN,  
force            BOOLEAN);
参数表明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采集样品行的比重,取值范围[0.000001,100],null为一体分析,不采集样品.
常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默许值,由oracle决定最好取采集样品值.
block_sapmple:是或不是用块采集样品代替行采集样品.
method_opt:决定histograms音信是何许被总括的.method_opt的取值如下(私下认可值为FOR
ALL COLUMNS SIZE AUTO):
for all columns:总计全体列的histograms.
for all indexed columns:总计全部indexed列的histograms.
for all hidden columns:计算你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO |
SKEWONLY:计算钦命列的histograms.N的取值范围[1,254];
REPEAT上次总计过的histograms;AUTO由oracle决定N的轻重;SKEWONLY multiple
end-points with the same value which is what we define by “there is skew
in thedata
degree:决定并行度.暗许值为null.
granularity:Granularity of statistics to collect ,only pertinent if the
table is partitioned.
cascade:是采集索引的新闻.默许为FALSE.
stattab:钦赐要存款和储蓄总结消息的表,statid如若多少个表的总结信息存款和储蓄在同2个stattab中用来开始展览区分.statown存款和储蓄计算新闻表的拥有者.以上八个参数若不钦赐,总计新闻会一直更新到多少字典.
no_invalidate: Does not invalidate the dependent cursors if set to
TRUE. The procedure invalidates the dependent cursors immediately if set
to FALSE.
force:即便表锁住了也采集总括音信.
例子:
wwwlehu6.vip乐虎官网,execute dbms_stats.gather_table_stats(ownname => ‘owner’,tabname
=> ‘table_name’ ,estimate_percent => null ,method_opt =>
‘for all indexed columns’ ,cascade => true);

http://blog.sina.com.cn/s/blog_679e928c0100yi15.html

例如:

在使用DBMS_STATS分析表的时候,我们平日要保存此前的剖析,避防分析后导致系统品质低下然后开始展览高效苏醒。

壹、首先成立2个解析表,该表是用来保存在此以前的解析值:

SQL> begin
 dbms_stats.create_stat_table(ownname=>’TEST’,stattab=>’STAT_TABLE’);
 end;
 /

PL/SQL 进程已成功完毕。

SQL> begin
 dbms_stats.gather_table_stats(ownname=>’TEST’,tabname=>’T1′);
 end;
 /

PL/SQL 进度已成功达成。

二、导出表分析新闻到stat_table中

SQL> begin
 dbms_stats.export_table_stats(ownname=>’TEST’,tabname=>’T1′,stattab=>’STAT_TABLE’);
 end;
 /

PL/SQL 进度已成功完毕。

SQL> select count(*) from TEST.STAT_TABLE;

COUNT(*)

4

4、删除分析音讯

SQL> begin
 dbms_stats.delete_table_stats(ownname=>’TEST’,tabname=>’T1′);
 end;
 /

PL/SQL 进度已成功实现。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space,
chain_cnt, avg_row_len FROM dba_tables WHERE owner = ‘TEST’
AND table_name = ‘T1’;

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN


尚未查到分析数据

伍、导入分析消息

SQL> begin
 dbms_stats.import_table_stats(ownname=>’TEST’,tabname=>’T1′,stattab=>’STAT_TABLE’);
 end;
 /

PL/SQL 进度已成功实现。

SQL> SELECT num_rows,blocks,empty_blocks as empty, avg_space,
chain_cnt, avg_row_len FROM dba_tables WHERE owner = ‘TEST’
AND table_name = ‘T1’;

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN


1000 5 0 0 0 16
能够查到剖析数据

补充:

EXPORT_COLUMN_STATS:导出列的解析消息
EXPORT_INDEX_STATS:导出索引分析消息
EXPORT_SYSTEM_STATS:导出系统分析音信
EXPORT_TABLE_STATS:导出表分析音信
EXPORT_SCHEMA_STATS:导出方案分析音信
EXPORT_DATABASE_STATS:导出数据库分析音讯
IMPORT_COLUMN_STATS:导入列分析音信
IMPORT_INDEX_STATS:导入索引分析音讯
IMPORT_SYSTEM_STATS:导入系统一分配析信息
IMPORT_TABLE_STATS:导入表分析消息
IMPORT_SCHEMA_STATS:导入方案分析音信
IMPORT_DATABASE_STATS:导入数据库分析新闻
GATHER_INDEX_STATS:分析索引消息
GATHER_TABLE_STATS:分析表音信,当cascade为true时,分析表、列(索引)新闻
GATHER_SCHEMA_STATS:分析方案音讯
GATHER_DATABASE_STATS:分析数据库音讯
GATHER_SYSTEM_STATS:分析连串消息

注: 转自 http://cjjwzs.iteye.com/blog/1143893

http://blog.csdn.net/mercenarylin/article/details/8559832 

 

 http://www.cnblogs.com/suredandan/p/3200157.html

 

 

I encountered this question in an interview and had no clue how to
answer:

There is a table which has a index on a column, and you query:

select * from table_name where column_having_index="some value";

The query takes too long, and you find out that the index is not being
used. If you think the performance of the query will be better using the
index, how could you force the query to use the index?

You can use optimizer hints

select /*+ INDEX(table_name index_name) */ from table etc…

More on using optimizer hints:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

There could be many reasons for Index not being used. Even after you
specify
hints
,
there are chances Oracle optimizer thinks otherwise and decide not to
use Index
. You need to go through the EXPLAIN PLAN part and see what
is the cost of the statement with INDEX and without INDEX.

Assuming the Oracle uses
CBO
.
Most often, if the optimizer thinks the cost is high with INDEX, even
though you specify it in hints, the optimizer will ignore and continue
for full table scan. Your first action should be checking DBA_INDEXES
to know when the statistics are LAST_ANALYZED. If not analyzed, you can
set table, index for
analyze
.

begin 
   DBMS_STATS.GATHER_INDEX_STATS ( OWNNAME=>user
                                 , INDNAME=>IndexName);
end;

For table.

begin 
   DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME=>user
                                 , TABNAME=>TableName);
end;

In extreme cases, you can try setting up the
statistics

on your own.

http://stackoverflow.com/questions/1838094/force-index-use-in-oracle/1838216#1838216

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图