oracle 收集对象统计信息 处理全局临时表

2023年 10月 8日 46.3k 0

处理全局临时表

直到11.2版本为止(包括11.2版本在内),对于全局临时表,dbms_stats包仅对gather_database_stats和gather_schema_stats存储过程提供gather_temp参数的支持。通过这个参数,仅能够控制是否处理全局临时表。收集的执行过程与“普通”表没有区别。

 

结果,在大多数时间里,抛开对象统计信息是如何被收集的不说,全局临时表上没有可以使用的对象统计信息。原因有两个。第一,dbms_stats 在处理过程开始会执行一个COMMIT操作,因此,通过on
commit delete rows (也就是默认选项)选项创建的临时表永远是空的。第二,如果收集过程与往常一样,发生在一个像默认收集任务这样的任务中,全局临时表也是空的。

 

总之,获取有意义的对象统计信息的唯一方式就是手工设置它们。但是,即使你手工设置了它们,也没有办法找到一组适合所有人的对象统计信息。实际上,每个会话都有可能在这些表中存储一组不同数量的数据。

 

最终,12.1版本引入了一个新特性来正确地处理全局临时表。其思路是你可以在共享统计信息(在11.2及之前的版本中唯一可用的选项)和会话统计信息之间进行选择。如果使用了会话统计信息(全局临时表的默认选项),每个会话都可以单独收集一组对其他会话并不可见的对象统计信息。

 

收集的过程本身与往常一样,通过dbms_stats包的gather_table_stats存储过程来执行。这意味着要想从这个特性中获益,应用程序必须进行修改,以在全局临时表数据加载完毕后立刻执行对gather_table_stats过程的调用。注意,为了使这个特性发挥作用,dbms_stats包处理流程开始的COMMIT操作被移除了。下面的例子(来自于gtt.sql脚本)说明了这个特性是如何运作的:

CREATE GLOBAL TEMPORARY TABLE t (id NUMBER,
pad VARCHAR2(1000));

INSERT INTO t SELECT rownum,
rpad('*',1000,'*') FROM dual CONNECT BY level user, tabname => 't')

 

SELECT num_rows, blocks, avg_row_len, scope

FROM user_tab_statistics

WHERE table_name = 'T';

 

select count(*) FROM t WHERE id BETWEEN 10
AND 100;

select * FROM
table(dbms_xplan.display_cursor);

 

- Global temporay table session private
staistics used

 

要控制是使用共享统计信息还是使用会话统计信息,可以设置global_temp_table_stats首选项。受支持的值有两个:shared和session。 默认值是session。

处理挂起的对象统计信息

通常,一旦收集过程结束,就会将对象统计信息发布到查询优化器(也就是说,使其可访问)。这意味着无法在不覆盖当前对象统计信息的情况下(例如,基于测试目的)收集统计信息。当然了,用于测试用途的应该是测试数据库,但有时候测试环境并不总是那么理想;你可能想在生产环境中做这样的测试。测试数据库中存储的数据与生产数据库中存储的数据不一致,就是这样的一个例子。

 

自11.1版本起,就可以将收集统计信息与发布它们的过程分隔开来,这样便可以使用未发布的对象统计信息,也就是所说的挂起的统计信息(pending statistics),将其用作测试用途。下面是处理过程(完整的例子在pending_object_statistics.sql脚本中提供)。

 

(1)通过将publish首选项设置为FALSE来禁用自动发布(默认值是TRUE)。正如上一节所描述的,你可以通过全局、数据库、模式或者表级别来完成设置。下面的例子展示了如何为属于当前用户的一张表设置该首选项:

dbms_stats.set_table_prefs(

 
ownname => user,

 
tabname => 't',

 
pname   => 'publish',

 
pvalue  => 'false'

);

 

(2)收集对象统计信息。因为这张表的publish首选项被设置成FALSE,最近收集的对象统计信息没有被发布,而是创建了一组挂起的统计信息。这意味着查询优化器仍然使用此次收集之前可用的统计信息。同时,依赖于这张表的游标并没有失效:

dbms_stats.gather_table_stats(ownname =>
user,tabname => 'T');

 

(3)要测试挂起的统计信息对一个应用程序或者一组SQL语句的影响,既可以通过在会话级别将初始化参数optimizer_use_pending_statistics设置为TRUE,也可以通过在SQL语句级别使用opt_param('optimizer_use_pending_statistics'
'true')这个hint。

 

(4)如果测试成功,可以通过调用publish_pending_stats存储过程来发布挂起的统计信息(换句话说,使其对所有会话可用)。下面的例子会展示如何为单张表发布挂起的统计信息。如果将tabname参数设置为NULL,指定模式下所有挂起的统计信息都将被发布。这个过程还有两个额外的参数。如前所述,第三个参数no_invalidate控制依赖于修改的对象统计信息的游标是否失效。第四个参数force用于解开对象统计信息上潜在的锁。其默认值为FALSE,意思是对锁的处理遵守默认值:

dbms_stats.publish_pending_stats(ownname=>user,tabname=>'T')

 

(5)如果测试不成功,可以通过调用delete_pending_stats存储过程删除挂起的统计信息。如果没有指定tabname参数的值或将其设置为NULL,通过ownname参数指定的整个模式下挂起的统计信息都会被删除:

dbms_stats.delete_pending_stats(ownname=>user,tabname=>'T')

 

(6)通过将publish首选项设置为TRUE来启用自动发布。需要执行这一步来恢复第1步中执行的更改:

dbms_stats.set_table_prefs(

 
ownname => user,

 
tabname => 't',

 
pname   => 'publish',

 
pvalue  => 'true'

);

要执行存储过程publish_pending_stats和delete_pending_stats,需要以所有者身份连接或者具有analyzeany系统权限。

 

如果你有兴趣了解这些挂起的统计信息的值,下面的数据字典视图提供了所有必要的信息。对于每个视图,都有dba、all,以及在12.1多租户环境下的cdb版本。

Ø  user_tab_pending_stats 显示挂起的表统计信息。

Ø  user_ind_pending_stats 显示挂起的索引统计信息。

Ø  user_col_pending_stats显示挂起的列统计信息。

Ø  user_tab_histgrm_pending_stats 显示挂起的直方图。

 

这些数据字典视图的结构分别类似于user_tab_statistics、user_ind_statistics、user_tab_col_statistics以及user_tab_histograms。

 

 

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论