Oracle 12cR1&11gR2-Concurrent statistics gathering

oracle 11.2统计信息推出了新的参数”CONCURRENT”,但是在11g的官方文档中没有列出来,只到12.1才被列入,所以很多人认为这个是12c的新特性,但是11.2.0.2版本之后就可以使用了,该参数的主要作用是同时收集多个表和表分区的统计信息。它使用的主要是job scheduler, advanced queuing and resource manager来结合操作。系统如果资源很空闲,并发收集统计信息可以减少收集的时间。同时还有一个很好的使用场景,假设你有一个很大的分区表,你需要收集所有该分区上的统计信息,DBA一般会编写脚,使用dbms_stats.gather_table_stats()中的tabname和partname参数并行执行针对单个分区统计信息收集作业。这是人为的并行的方式。而12.1直接推出了并行收集,那么我们可以使用这种新选项来操作。
首先你需要启动并行收集,通过SET_GLOBAL_PREFS方法对全局进行设置。接下来我们创建一个较大的分区表,插入大量数据。然后观察表的CONCURRENT属性是否为True,如果为True就代表这个表可以进行并发收集。

begin
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');
end;
/
create table test1
(
id1 number,
id2 varchar2(9)
)
partition by range(id1)
(
partition p1 values less than(10000000),
partition p2 values less than(20000000),
partition p3 values less than(30000000),
partition p4 values less than(40000001)
);

begin
for i in 0..40000000
loop
insert into test1 values(i, 'a'||i);
end loop;
commit;
end;
/

SQL> insert into test1 select /*+parallel(a,4) */ * from test1 a;
40000001 rows created.

SQL> commit;
Commit complete.

这里我们创建了test1表,一共有四个分区,然后我们造了80000000行数据。因为全局设置了CONCURRENT为true,所以我单独的查这张表的CONCURRENT属性也是为true的。

SQL> select dbms_stats.get_prefs('CONCURRENT','SYS','TEST1') from dual;

DBMS_STATS.GET_PREFS('CONCURRENT','SYS','TEST1')
--------------------------------------------------------------------------------
TRUE

SQL> select partition_name, num_rows, sample_size, last_analyzed from user_tab_partitions where table_name = 'TEST1';

PARTITION_NAME NUM_ROWS SAMPLE_SIZE LAST_ANAL
---------------------------------------- ---------- ----------- ---------
P1
P2
P3
P4

SQL> select partition_name, bytes/1024/1024 MB from user_segments where segment_name = 'TEST1';

PARTITION_NAME MB
---------------------------------------- ----------
P1 456
P2 432
P3 456
P4 456

然后开始收集统计信息,这里我开了4个并行。这里需要注意的是,前面我们说过,我们开启用CONCURRENT属性,Oracle是通过job scheduler, advanced queuing and resource manager来结合实现的,所以这里我们的job_queue_processes参数至少是要大于4才行。这里我们还需要搞清楚一个概念,现在我们做的是并发收集统计信息。而不是并行收集统计信息,具体的意思就是,我开了四个并发job在同时进行收集,如果我的这些分区下面还有很多子分区,那么我就可以在并发里面开并行。那么要实现在并发里面在开并行就需要将参数parallel_adaptive_multi_user设置成flase。默认这个参数在11g中就是flase的。这个参数的含义是启用自适应算法,提高使用并行执行的多用户环境中的性能。
关于这个参数的相关设置可以参考Oracle官方的博客,作者是:Guest Author,在并发job任务里面开并行就得关闭这个参数。
If you plan to execute the concurrent statistics gathering jobs in parallel you should disable the parallel adaptive multi-user initialization parameter. That is;
Alter system set parallel_adaptive_multi_user=false;当然这篇文章的博主还建议设置资源管理器,使用并行语句排队。我们这里先不设置resource manager实验并行语句排队,我们直接开始收集,并通过另外一个窗口进行观察。

dbms_stats.gather_table_stats('SYS', 'TEST1', degree=>4)

SQL> select OWNER,JOB_NAME,STATE,SCHEDULE_NAME,COMMENTS from dba_scheduler_jobs where job_class like 'CONC%';

OWNER JOB_NAME STATE SCHEDULE_NAME COMMENTS
------------------------------ ------------------------------ --------------- -------------------- ----------------------------------------
SYS ST$T342_5 RUNNING SYS.TEST1.
SYS ST$T342_4 RUNNING SYS.TEST1.P4
SYS ST$T342_3 RUNNING SYS.TEST1.P3
SYS ST$T342_2 RUNNING SYS.TEST1.P2
SYS ST$T342_1 RUNNING SYS.TEST1.P1

可以看到开启了五个任务在执行,一个是收集表,四个是在收集分区的。同时为了进一步看清楚它做了什么,我们可以通过DBMS_METADATA.GET_DDL来获取DDL。不过很奇怪,我的数据库版本死活都没办法在SYS用户下取出DDL。一直都会报ORA-31603错误,按照MOS上的文档做也解决不了。

Enter value for 1: ST$T339_3
old 1: SELECT DBMS_METADATA.GET_DDL('PROCOBJ', '&1') from dual
new 1: SELECT DBMS_METADATA.GET_DDL('PROCOBJ', 'ST$T339_3') from dual
ERROR:
ORA-31603: object "ST$T339_3" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1

为了解决这个问题,查了好久,终于发现可以使用dbms_scheduler.copy_job的办法解决。复制到其他的用户下面,然后再执行GET_DDL就彻底的弄出来dbms_scheduler创建的JOB代码。

SQL> exec dbms_scheduler.copy_job('SYS.ST$T342_1','AAA.ST$T342_1');
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_METADATA.GET_DDL('PROCOBJ', '&1','AAA') from dual;
Enter value for 1: ST$T342_1
old 1: SELECT DBMS_METADATA.GET_DDL('PROCOBJ', '&1','AAA') from dual
new 1: SELECT DBMS_METADATA.GET_DDL('PROCOBJ', 'ST$T342_1','AAA') from dual

DBMS_METADATA.GET_DDL('PROCOBJ','ST$T342_1','AAA')
--------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"ST$T342_1"',
job_type=>'PLSQL_BLOCK', job_action=>
'declare context dbms_stats.CContext := dbms_stats.CContext(); begin context
.extend(10); context(1) := ''GLOBAL AND PARTITION''; context(2) := ''TRUE'';
context(3) := ''0''; context(4) := ''''; context(5) := ''FALSE''; conte
xt(6) := ''FALSE''; context(7) := ''4''; context(8) := ''ST$T342''; contex
t(9) := ''TRUE''; context(10) := ''FALSE''; dbms_stats.gather_table_stats(q'
'#"SYS"#'', q''#"TEST1"#'', q''#"P1"#'', 0, FALSE, q''#FOR ALL COLUMNS SIZE AUTO
#'', 4, q''#PARTITION#'', TRUE, NULL, NULL, NULL, NULL, q''#DATA#'', FALSE, cont
ext); end; '
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
NULL
, end_date=>NULL,
job_class=>'"CONC_ST$T342"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'SYS.TEST1.P1'
);
dbms_scheduler.set_attribute('"ST$T342_1"','raise_events',38);
COMMIT;
END;

而这里我们具体在看,就是下面这个方法,采用的是收集PARTITION的方式,收集的是TEST1表下的P1分区,然后是FOR ALL COLUMNS SIZE AUTO收集列的信息。

declare
context
dbms_stats.CContext := dbms_stats.CContext();
begin
context.extend(10);
context(1) := ''GLOBAL AND PARTITION'';
context(2) := ''TRUE'';
context(3) := ''0'';
context(4) := '''';
context(5) := ''FALSE'';
context(6) := ''FALSE'';
context(7) := ''4'';
context(8) := ''ST$T342'';
context(9) := ''TRUE'';
context(10) := ''FALSE'';
dbms_stats.gather_table_stats(q''#"SYS"#'', q''#"TEST1"#'', q''#"P1"#'', 0, FALSE, q''#FOR ALL COLUMNS SIZE AUTO#'', 4, q''#PARTITION#'', TRUE, NULL, NULL, NULL, NULL, q''#DATA#'', FALSE, context);
end;

最终收集结果如下。如果分区很小,则不会启动job同时并发收集统计信息,建议做实验的时候把数据量设大一些。

SQL> select partition_name, num_rows, sample_size, last_analyzed from user_tab_partitions where table_name = 'TEST1';

PARTITION_NAME NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
P1 20000000 20000000 2018-06-08 01:40:42
P2 20000000 20000000 2018-06-08 01:40:43
P3 20000000 20000000 2018-06-08 01:40:43
P4 20000002 20000002 2018-06-08 01:40:44

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569