上了10G和准备上10G的朋友小心点

lfree · 发表于 2007-10-24 15:25

sys_extract_utc
http://download-uk.oracle.com/do ... 59/functions152.htm

SYS_EXTRACT_UTC extracts the UTC (Coordinated Universal Time—formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name.

这么看，你所说的结果不对一定有时区引起的。

lfree · 发表于 2007-10-24 15:27

关于10g的性能问题，也许是一些执行计划改变。

lfree · 发表于 2007-10-24 15:35

顺便帖一个
9i 下的执行计划：

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

5 rows selected.

lfree · 发表于 2007-10-24 15:36

正如lZ说的，执行计划会使用索引。
并且没有函数转换。

lfree · 发表于 2007-10-24 16:13

奇怪我执行LZ写的sql没有结果。

如果改写成这样在9i下是有结果的。
SELECT * FROM TT WHERE X=TO_TIMESTAMP_TZ(trunc(SYSTIMESTAMP)+10, 'DD-Mon-RR HH24:MI:SS.FF')
union all
SELECT * FROM TT WHERE X=TO_TIMESTAMP(trunc(SYSTIMESTAMP)+10, 'DD-Mon-RR HH24:MI:SS.FF')
union all
SELECT /*+ index(tt,TT_IND)*/ * FROM TT WHERE X=TO_TIMESTAMP_tz(trunc(SYSTIMESTAMP)+10, 'DD-Mon-RR HH24:MI:SS.FF')

lfree · 发表于 2007-10-24 16:14

10g也一样，也许与时间的格式有关。

henrybai · 发表于 2007-10-24 16:14

我脆弱系统的patch情况：
$ opatch lsinventory
Invoking OPatch 10.2.0.3.0

Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..

Oracle Home    : /opt/app/oracle/product/10.2.0/db_1
Central Inventory : /opt/app/oracle/oraInventory
from          : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version    : 10.2.0.3.0
OUI location    : /opt/app/oracle/product/10.2.0/db_1/oui
Log file location : /opt/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2007-10-24_04-13-02AM.log

Lsinventory Output file location : /opt/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2007-10-24_04-13-02AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 10g                                                 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                         10.2.0.3.0
There are 2 products installed in this Oracle Home.

Interim patches (9) :

Patch  6075430    : applied on Fri Sep 21 14:45:19 EDT 2007
Created on 20 Sep 2007, 00:21:00 hrs PST8PDT
Bugs fixed:
   6075430

Patch  6327692    : applied on Fri Sep 21 14:39:13 EDT 2007
Created on 12 Sep 2007, 02:52:46 hrs PST8PDT
Bugs fixed:
   6327692

Patch  5759075    : applied on Thu Aug 16 22:21:00 EDT 2007
Created on 12 Jun 2007, 15:22:25 hrs PST8PDT
Bugs fixed:
   5759075

Patch  6044413    : applied on Thu Aug 16 22:12:29 EDT 2007
Created on 8 Aug 2007, 12:14:19 hrs PST8PDT
Bugs fixed:
   6044413, 5893779

Patch  4537267    : applied on Thu Aug 16 04:31:19 EDT 2007
Created on 9 Aug 2007, 07:31:11 hrs PST8PDT
Bugs fixed:
   4537267

Patch  6000740    : applied on Thu Aug 09 22:45:15 EDT 2007
Created on 30 May 2007, 22:51:27 hrs US/Pacific
Bugs fixed:
   5929208, 4598992, 5916387, 5192194, 5980800, 5735768, 5651085, 5862719
   5853909, 6056680, 5872905, 5698321, 5980915, 5951885, 5568399, 5709504
   5376408, 5101517, 5334170, 5895421, 5485242, 6000740, 5769259, 5679560
   5567154, 5365683, 5861703, 5467456, 5188493, 5696668, 5503111, 5843485, 4930431

Patch  5749953    : applied on Tue Jul 10 01:14:55 EDT 2007
Created on 24 Apr 2007, 15:50:09 hrs US/Pacific
Bugs fixed:
   5749953

Patch  5556081    : applied on Mon Jul 09 21:57:08 EDT 2007
Created on 9 Nov 2006, 22:20:50 hrs PST8PDT
Bugs fixed:
   5556081

Patch  5557962    : applied on Mon Jul 09 21:56:19 EDT 2007
Created on 9 Nov 2006, 23:23:06 hrs PST8PDT
Bugs fixed:
   4269423, 5557962, 5528974

Rac system comprising of multiple nodes
  Local node = rac01-ud-us-eqx
  Remote node = rac02-ud-us-eqx
  Remote node = rac03-ud-us-eqx

--------------------------------------------------------------------------------

OPatch succeeded.
$

lfree · 发表于 2007-10-24 16:20

是linux平台吗？

lfree · 发表于 2007-10-24 16:21

试一下建立索引看看：
CREATE INDEX TT_F_IND ON TT
(sys_extract_utc(X) )

lfree · 发表于 2007-10-24 16:27

能告知使用是什么unix吗?
感觉不是linux。