|
|
看看我的这次的观察结果，分区表的
SESSION -1 执行分析脚本，执行后到会话2查询当前时间，之后作了一些查询操作（中途等了几回），我相信
这段时间肯定超过1 分钟．但PART_2005分区分析结束后，发现last_analyzed 时间却比一开始在会话2查询的
时间少不了多少，在1分钟内．这很让我纳闷，若说LAST_ANALYZED 是分析结束后的时间，其在1分钟内已经结束，
为什么分区PART_2006在21:52:58 还为分析结束？难道多出了这么多的数据（730-260=）470M就让系统多运行
那么长时间？
有两种解释：
1 对于分区表，LAST_ANALYZED 是分析起始时间．
2 若是分析结束时间，则ORACLE还要花很长的一段时间去干XXX？
[php]
session-1
begin
for i in (select table_name, partition_name from user_tab_partitions where table_name='IC_GENERAL_B' order by 2) loop
dbms_stats.gather_table_stats(ownname=>user,tabname=>i.table_name,partname=>i.partition_name,estimate_percent=>100,cascade=>false,method_opt=>'for all indexed columns size 254');
end loop;
for i in (select index_name, partition_name from user_ind_partitions where index_name in
(select index_name from user_indexes where table_name='IC_GENERAL_B') order by 1,2) loop
dbms_stats.gather_index_stats(ownname=>user,indname=>i.index_name,partname=>i.partition_name,estimate_percent=>100);
end loop;
end;
session-2
NCBI@REPORT>select sysdate from dual;
SYSDATE
-------------------
2008-03-13 21:23:09
Elapsed: 00:00:00.01
NCBI@REPORT>select partition_name, last_analyzed from user_tab_partitions where table_name='IC_GENERAL_B'
2 order by partition_name;
PARTITION_NAME LAST_ANALYZED
------------------------------ -------------------
PART_2005
PART_2006
PART_200706
PART_200707
PART_200708
PART_200709
PART_200710
PART_200711
PART_200712
PART_200801
PART_200802
PART_200803
PART_200804
PART_200805
PART_200806
PART_200807
PART_200808
PART_200809
PART_200810
PART_200811
PART_200812
PART_MAX
22 rows selected.
Elapsed: 00:00:00.09
NCBI@REPORT>set line 999;
NCBI@REPORT>set pagesize 203;
NCBI@REPORT>col owner for a10;
NCBI@REPORT>col job_name for a30;
NCBI@REPORT>col last_start_date for a20;
NCBI@REPORT>col next_run_date for a20;
NCBI@REPORT>col failure_count for 999999;
NCBI@REPORT>select owner,job_name,failure_count failure,last_start_date,next_run_date,state,run_count
2 from dba_scheduler_jobs where state<>'DISABLED' and state<>'COMPLETED'
3 and owner not in ('EXFSYS');
OWNER JOB_NAME FAILURE LAST_START_DATE NEXT_RUN_DATE STATE R
---------- ------------------------------ ---------- -------------------- -------------------- --------------- --
SYS PURGE_LOG 0 2008-03-13 03:00:00 2008-03-14 03:00:00 SCHEDULED
SYS GATHER_STATS_JOB 0 2008-03-12 22:00:01 SCHEDULED
SYS AUTO_SPACE_ADVISOR_JOB 0 2008-03-12 22:00:01 SCHEDULED
Elapsed: 00:00:00.01
NCBI@REPORT>set line 999;
NCBI@REPORT>set pagesize 203;
NCBI@REPORT>col owner for a10;
NCBI@REPORT>col job_name for a30;
NCBI@REPORT>col last_start_date for a20;
NCBI@REPORT>col next_run_date for a20;
NCBI@REPORT>col failure_count for 999999;
NCBI@REPORT>select owner,job_name,failure_count failure,last_start_date,next_run_date,state,run_count
2 from dba_scheduler_jobs where state<>'COMPLETED' and owner not in ('EXFSYS');
OWNER JOB_NAME FAILURE LAST_START_DATE NEXT_RUN_DATE STATE R
---------- ------------------------------ ---------- -------------------- -------------------- --------------- --
SYS PURGE_LOG 0 2008-03-13 03:00:00 2008-03-14 03:00:00 SCHEDULED
SYS FGR$AUTOPURGE_JOB 0 DISABLED
SYS GATHER_STATS_JOB 0 2008-03-12 22:00:01 SCHEDULED
SYS AUTO_SPACE_ADVISOR_JOB 0 2008-03-12 22:00:01 SCHEDULED
NCBI J_TYREPORTIC 0 2008-03-13 20:00:10 2008-03-13 20:10:10 DISABLED
NCBI J_ANALYZE_JXC 0 2008-03-13 01:00:00 2008-03-14 01:00:00 DISABLED
NCBI JOB_IMPORT_STAT_FROM_52 0 2008-02-29 06:30:00 2008-03-01 06:30:00 DISABLED
NCBI J_TYREPORT 1 2008-03-13 19:57:08 2008-03-13 20:07:08 DISABLED
NCBI J_TYREPORTBBC 1 2008-03-13 20:04:00 2008-03-13 20:14:00 DISABLED
9 rows selected.
Elapsed: 00:00:00.07
NCBI@REPORT>EXEC DBMS_SCHEDULER.ENABLE('J_TYREPORTIC');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.42
NCBI@REPORT>EXEC DBMS_SCHEDULER.ENABLE('J_TYREPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
NCBI@REPORT>EXEC DBMS_SCHEDULER.ENABLE('J_TYREPORTBBC');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
NCBI@REPORT>select partition_name, last_analyzed from user_tab_partitions where table_name='IC_GENERAL_B'
2 order by partition_name;
PARTITION_NAME LAST_ANALYZED
------------------------------ -------------------
PART_2005
PART_2006
PART_200706
PART_200707
PART_200708
PART_200709
PART_200710
PART_200711
PART_200712
PART_200801
PART_200802
PART_200803
PART_200804
PART_200805
PART_200806
PART_200807
PART_200808
PART_200809
PART_200810
PART_200811
PART_200812
PART_MAX
22 rows selected.
Elapsed: 00:00:00.07
NCBI@REPORT>/
PARTITION_NAME LAST_ANALYZED
------------------------------ -------------------
PART_2005
PART_2006
PART_200706
PART_200707
PART_200708
PART_200709
PART_200710
PART_200711
PART_200712
PART_200801
PART_200802
PART_200803
PART_200804
PART_200805
PART_200806
PART_200807
PART_200808
PART_200809
PART_200810
PART_200811
PART_200812
PART_MAX
22 rows selected.
Elapsed: 00:00:00.07
NCBI@REPORT>/
PARTITION_NAME LAST_ANALYZED
------------------------------ -------------------
PART_2005 2008-03-13 21:23:54
PART_2006
PART_200706
PART_200707
PART_200708
PART_200709
PART_200710
PART_200711
PART_200712
PART_200801
PART_200802
PART_200803
PART_200804
PART_200805
PART_200806
PART_200807
PART_200808
PART_200809
PART_200810
PART_200811
PART_200812
PART_MAX
22 rows selected.
Elapsed: 00:00:00.06
NCBI@REPORT>
NCBI@REPORT>select sysdate from dual;
SYSDATE
-------------------
2008-03-13 21:52:58
Elapsed: 00:00:00.01
NCBI@REPORT>select partition_name, bytes/1024/1024 from user_segments where segment_name='IC_GENERAL_B'
2 ORDER BY 1;
PARTITION_NAME BYTES/1024/1024
------------------------------ ---------------
PART_2005 260
PART_2006 730
PART_200706 380
PART_200707 60
PART_200708 80
PART_200709 80
PART_200710 50
PART_200711 60
PART_200712 80
PART_200801 160
PART_200802 130
PART_200803 70
PART_200804 10
PART_200805 10
PART_200806 10
PART_200807 10
PART_200808 10
PART_200809 10
PART_200810 10
PART_200811 10
PART_200812 10
PART_MAX 10
22 rows selected.
Elapsed: 00:00:00.12
[/php] |
|
楼主: ZALBB
IP卡
狗仔卡
发表于 2008-3-12 18:11
显身卡
发表于 2008-3-12 18:43
楼主