timestamp类型与时区的关系及时区给数据库能带来的影响.docx

刚才在写timestamp类型的一个测试方法，想到timestamp跟时区的关系。因为平时基本不用这种类型，还真不知道。我们直接实验：SQL DROP TABLE TIMESTAMP_TEST;Table droppedSQL CREATE TABLE TIMESTAMP_TEST( 2 TIME DATE,TIMESTP TIMESTAMP(3), 3 TIMESTP_TZ TIMESTAMP(3) WITH TIME ZONE, 4 TIMESTP_LTZ TIMESTAMP(3) WITH LOCAL TIME ZONE);Table createdSQL SELECT DBTIMEZONE,SESSIONTIMEZONE FROM DUAL;DBTIMEZONE SESSIONTIMEZONE- -+00:00 +08:00SQL INSERT INTO TIMESTAMP_TEST VALUES(SYSDATE,SYSDATE,SYSDATE,SYSDATE);1 row insertedSQL commit;Commit completeSQL SELECT * FROM TIMESTAMP_TEST;TIME TIMESTP TIMESTP_TZ TIMESTP_LTZ- - - -5/6/2013 2: 06-MAY-13 02.22.37.000 PM 06-MAY-13 02.22.37.000 PM +08:00 06-MAY-13 02.22.37.000 PMSQL ALTER SESSION SET TIME_ZONE=+10:00;Session alteredSQL SELECT * FROM TIMESTAMP_TEST;TIME TIMESTP TIMESTP_TZ TIMESTP_LTZ- - - -5/6/2013 2: 06-MAY-13 02.22.37.000 PM 06-MAY-13 02.22.37.000 PM +08:00 06-MAY-13 04.22.37.000 PM我们看到，with local time zone的timestamp字段会随时着时区的变化而变化，由东八变东十，时间提前了2小时。说明TIMESTAMP WITH LOCAL TIME ZONE会随着用户所在时区(SESSIONTIMEZONE)而变化，而TIMESTAMP WITH TIME ZONE则不随用户所在时区的变化而变，简单的说，这两个时间类型的参照时间不同，一个是参照用户的时区，一个是参照数据库的时区。不过时区对数据库的影响还是挺大的，顺手发下我2011年遇到的一个问题。以前某省的国网数据库白天的时候非常卡，查找原因。最后真没怀疑到时区导致的数据库超卡。下面是当时报告的一部分：经检查，发现数据库时区为西5区，即纽约时区：SQL SELECT DBTIMEZONE FROM DUAL;DBTIMEZONE-05:00SQL SELECT SESSIONTIMEZONE FROM DUAL;SESSIONTIMEZONE-+08:00虽然时区的设置有误不影响管控日常的运作，但却严重影响ORACLE自身的优化器统计信息收集功能。我们查找下dba_scheduler_job_log表（但dba_scheduler_jobs表报ORA-01882错误，需要修改日期格式），搜寻ORACLE以执行过的JOB信息，结果如下（7月21-7月31日）：LOG_DATEJOB_CLASSOWNERJOB_NAME2011-07-21 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-21 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-21 11:08:44AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-21 19:00:02AUTO_TASKS_JOB_CLASSSTOPPEDGATHER_STATS_JOB2011-07-22 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-22 11:08:23AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-22 11:41:17AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-22 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-23 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-23 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-23 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-23 19:00:08DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-24 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-25 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-23 11:00:06DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-23 11:24:28AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-23 11:27:43AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-23 19:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-23 19:00:04DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-23 19:01:18AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-23 19:02:08AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-26 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-26 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-26 11:11:30AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-26 11:59:21AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-26 11:00:10DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-26 16:00:05DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-27 11:00:09DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-27 16:00:01DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-27 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-27 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-27 11:56:45AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-28 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-28 11:00:03DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-28 11:10:05AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-28 11:30:40AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-28 11:00:05DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-28 16:00:01DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-29 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-29 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-29 11:12:51AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-29 11:33:43AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-29 11:00:04DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-29 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-30 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-30 11:00:02DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-30 11:07:59AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-30 11:31:15AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-30 16:00:00DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG2011-07-30 19:00:04DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB2011-07-30 19:00:04DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_12011-07-30 19:02:26AUTO_TASKS_JOB_CLASSSUCCEEDEDAUTO_SPACE_ADVISOR_JOB2011-07-30 21:55:45AUTO_TASKS_JOB_CLASSSUCCEEDEDGATHER_STATS_JOB2011-07-30 11:00:07DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-30 19:00:05DEFAULT_JOB_CLASSSUCCEEDEDMGMT_CONFIG_JOB_22011-07-31 16:00:03DEFAULT_JOB_CLASSSUCCEEDEDPURGE_LOG注：红字字体为：任务开始时间 绿色字体为：回收日志时间 蓝色字体为：任务正常结束时间 紫色字体为：任务非正常结束时间 褐色字体为：其他手动调动任务时间我们惊讶地发现，本来应该晚上22：00至第二天06：00（周末全天）执行的ORACLE优化器统计信息收集竟然在白天执行。再计算下上面所查询的时区问题，数据库服务器时区为西5区，即纽约时区，我们所在的时区为东8区，即北京时区，两者按世界时区计算规则，东8区早于西5区13个小时。这样我们再来查看优化器统计信息收集的执行时间，晚22：00加13小时为白天11：00，早上06：00加13小时为晚19：00，这与我们上面所查询的数据完全吻合。即优化器统计信息收集从下午11：00开始执行，正常结束于下午19：00（非正常结束即强行STOP，即2011-07-21 19:00:02的情况）。我们知道，ORACLE优化器统计信息收集是为了提供给ORACLE查询时更好的执行计划，但同时也会由于其全库扫描带来大量的物理读（取7月25-27日的AWR采样）：（出自2011/7/25 10：00-12：00 AWR报告）（出自2011/7/26 10：00-12：00 AWR报告）（出自2011/7/26 12：00-14：00 AWR报告）（出自2011/7/26 14：00-16：00 AWR报告）（出