OracleBlog – 第 21 页 – 笑看数据库江湖的那些事儿……

收集统计信息degree不生效的问题

2014-04-21 没有评论

在10.2以上的版本，收集统计信息，如使用dbms_stats.gather_table_stats，还是在ebs中调用fnd_stats.GATHER_TABLE_STATS，有的时候虽然用了degree的参数，如degree=8，但是会发现仍然不使用并行。在10046的trace中，我们可以看到 […]

..experience, Working case

ORA-00000 normal, successful completion

2014-04-21 没有评论

当hosts文件配置错误时，用sqlplus登录后startup nomount，就会报错ORA-00000

[oracle11g@testdb2 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 21 13:48:32 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
SQL> startup nomount
ORA-00000: normal, successful completion
SQL> alter database mount;
ERROR:
ORA-01012: not logged on

SQL> 

[root@testdb2 etc]# cat hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.250 wrong_server
#192.168.56.132 testdb2

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

[oracle11g@testdb2 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 21 13:48:32 2014

Connected.

SQL> startup nomount

ORA-00000: normal, successful completion

SQL> alter database mount;

ERROR:

ORA-01012: not logged on

SQL>

[root@testdb2 etc]# cat hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.56.250 wrong_server

#192.168.56.132 testdb2

修改host文件后：

[root@testdb2 etc]# cat hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
#192.168.56.250 wrong_server
192.168.56.132 testdb2

再次启动：
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1052274688 bytes
Fixed Size                  2259840 bytes
Variable Size            1023411328 bytes
Database Buffers           20971520 bytes
Redo Buffers                5632000 bytes
SQL> 

正常。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

[root@testdb2 etc]# cat hosts

# Do not remove the following line, or various programs

# that require network functionality will fail.

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

#192.168.56.250 wrong_server

192.168.56.132 testdb2

再次启动：

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1052274688 bytes

Fixed Size 2259840 bytes

Variable Size 1023411328 bytes

Database Buffers 20971520 bytes

Redo Buffers 5632000 bytes

SQL>

正常。

另外，除了写错，如果丢失hosts文 […]

It's my life

台湾之行（DAY ONE and DAY TWO）

2014-03-19 4条评论

这个月休了2周的年假，去了一趟台湾。在饱览台湾的人文之美后，我首先要感谢一下我的公司，甲骨文中国，如果放在别的单位，我估计我休假两周回来后，是否还留有我的岗位……有朋友说，回来之后写个攻略吧，嗯……怎么说呢，如果你按照我的攻略走，你也许不能完全体会到台湾的人文之美，台湾的美，要靠你搭乘它的捷运、扶梯 […]

Study note

如何还原virtualbox导出的RAC镜像文件

2014-03-18 2条评论

在virtualbox中建立虚拟机，往往是3个文件夹：节点1（ol6-121-rac1），节点2（ol6-121-rac2），共享存储文件夹（ol6-121-rac）。当我们选择“管理”-“导出虚拟电脑”，导出成ova文件备份，就能看到2个主机，每个主机一个ova文件。另外，细心的你可能会发现，在备 […]

..experience, Working case

当Parallel DML遇到分布式事务、分区表和LOB时

2014-02-28 没有评论

今天遇到一个问题，某sql的并发度虽然已经全部获得但是只有少数几个slave干活。

alter  session enable parallel dml;
insert /*+ parallel(t 16)*/一个本地分区表t  
select /*+ full(a parallel(a 32))*/* from 远程分区表a@dblink 
where 时间>=xxxx and 时间<xxxxx;

1

2

3

4

alter session enable parallel dml;

insert /*+ parallel(t 16)*/一个本地分区表t

select /*+ full(a parallel(a 32))*/* from 远程分区表a@dblink

where 时间>=xxxx and 时间<xxxxx;

insert要求有16个并发，虽然在v$PX_SESSION看到其获得的degree是16，但是并不是这16个slave都在干活，从sqlmonito […]

It's my life

latch:transaction allocation

2014-02-28 没有评论

今天遇到一个sql，发生了大量的134的latch，134的latch是transaction allocation。数据库版本是9208. 该sql是：

SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,
       s.SQL_HASH_VALUE,
       l.id1,
       l.id2,
       l.lmode,
       l.request,
       l.type
  FROM V$LOCK l, v$session s
WHERE (l.id1, l.id2, l.type) IN
       (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   and l.SID = s.SID
   and exists (select sw.seq#,
               sw.sid || ',' || s.serial# sids,
               s.username,
               s.SQL_HASH_VALUE
          from v$session_wait sw, v$session s, dba_objects a
         where sw.sid = s.sid
           and sw.event in ('latch free', 'enqueue')
           and s.status = 'ACTIVE'
           and sw.STATE = 'WAITING'
           and s.username is not null
           and sw.seconds_in_wait > 30
           and (a.object_id = s.row_wait_obj# or
               a.data_object_id = s.row_wait_obj#))
ORDER BY l.id1, l.request;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

SELECT DECODE(l.request, 0, 'Holder: ', 'Waiter: ') || l.sid sess,

s.SQL_HASH_VALUE,

l.id1,

l.id2,

l.lmode,

l.request,

l.type

FROM V$LOCK l, v$session s

WHERE (l.id1, l.id2, l.type) IN

(SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

and l.SID = s.SID

and exists (select sw.seq#,

sw.sid || ',' || s.serial# sids,

s.username,

s.SQL_HASH_VALUE

from v$session_wait sw, v$session s, dba_objects a

where sw.sid = s.sid

and sw.event in ('latch free', 'enqueue')

and s.status = 'ACTIVE'

and sw.STATE = 'WAITING'

and s.username is not null

and sw.seconds_in_wait > 30

and (a.object_id = s.row_wait_obj# or

a.data_object_id = s.row_wait_obj#))

ORDER BY l.id1, l.request;

据客户反映，这个sql之前没有出现问题，只是昨天做了修改，增加了对v$sess […]

..experience, Working case

current_timestamp在job中不准的问题

2014-02-21 没有评论

有些job中跑应用程序，希望记下时间戳，但是取了current_timestamp之后，发现时间和实际的时间不准，查了几个小时。这是因为在job中跑的时候，current_timestamp是GMT格林尼治时间。见下面的testcase，我们设置数据库服务器的时区是东八区的北京时间；客户端plsq […]

Study note

小谈12c的in memory option

2014-02-03 一条评论

（1） in memory option（以下简称imo）将随着12.1.0.2发布（2）in memory option不会取代TimesTen（以下简称TT），因为这是2种层面的产品，TT还是会架在DB层之前，和应用紧密相连，为应用提供缓存，imo在DB层，可以做到高可用如RAC，DG等一些T […]

..experience, Working case

在跨年时出现执行计划的突变

2014-02-02 没有评论

遇到这样一个问题，客户的某系统在进入2014年后，某sql执行就非常慢，看到这个sql的执行计划发生了改变，原来很好的走hash连接的执行计划变成了走索引的nest loop的执行计划。在收集统计信息后，恢复正常。这个故障的测试案例，我们可以这样模拟： [crayon-690a5ad21dfff0 […]

..experience, Working case

增量分区统计信息收集的一点研究

2014-01-30 没有评论

Oracle在11g中能增量收集分区表的统计信息。在11g之前，收集global的统计信息时，oracle需要扫描每个分区的统计信息，才能得出global的统计信息。而在11g中，Oracle会简单记录分区的概要信息，（synopsis，记录在WRI$_OPTSTAT_SYNOPSIS_HEAD$ […]

..experience, Working case

11g的reference partition的一个bug

2013-12-21 一条评论

在11g上有个新特性，叫reference partition table，但是有时候在查询reference partition的时候，会报错ORA-00600 [6305] 我们可以看下面的testcase：

TestCase:
SQL> CREATE TABLE BILL_DATA_T
("BILLID"    CHAR(10 BYTE) NOT NULL ENABLE, -----<<<<<<< 4.BILLID是char 10
  "COGCODE"   CHAR(7 BYTE),
  CONSTRAINT "PK_BILL_DATA_T" PRIMARY KEY ("BILLID"))
PARTITION BY RANGE
  ("COGCODE")
  (PARTITION "BILLS_2012" VALUES LESS THAN ('X'),
   PARTITION "BILLS_OPEN" VALUES LESS THAN (MAXVALUE));  2    3    4    5    6    7    8  

Table created.

SQL> 
SQL> CREATE TABLE FREIGHT_ITEM_T
("BILLID"    CHAR(10 BYTE) NOT NULL ENABLE,
  "FRTITEMID" CHAR(3 BYTE) NOT NULL ENABLE,
  CONSTRAINT PK_FREIGHT_ITEM_T PRIMARY KEY ("BILLID"),
  CONSTRAINT FK_FREIGHT_ITEM_T FOREIGN KEY ("BILLID") REFERENCES ---<<<<<<< 3.外键是参考BILL_DATA_T表的BILLED字段
    BILL_DATA_T ("BILLID"))
PARTITION BY REFERENCE   ----<<<<<<<<<<<<< 1.是partition by reference
  ("FK_FREIGHT_ITEM_T")  ----<<<<<<<<<<< 2.参考了FK_FREIGHT_ITEM_T
  (PARTITION "BILLS_2012",
   PARTITION "BILLS_OPEN");  2    3    4    5    6    7    8    9   10  

Table created.

SQL> 
SQL> 
SQL> select * from freight_item_t where billid='123456789000000';  ----<<<<< 5.超过char 10，就报错
select * from freight_item_t where billid='123456789000000'
              *
ERROR at line 1:
ORA-00600: internal error code, arguments: [6305], [16], [12], [1], [], [], [],
[], [], [], [], []


SQL> 
SQL> 
SQL> 
SQL> c/123456789000000/123456789
  1* select * from freight_item_t where billid='123456789' -----<<<<<< 6.不超过char 10，就不会报错。
SQL> /

no rows selected

SQL>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

TestCase:

SQL> CREATE TABLE BILL_DATA_T

("BILLID" CHAR(10 BYTE) NOT NULL ENABLE, -----<<<<<<< 4.BILLID是char 10

"COGCODE" CHAR(7 BYTE),

CONSTRAINT "PK_BILL_DATA_T" PRIMARY KEY ("BILLID"))

PARTITION BY RANGE

("COGCODE")

(PARTITION "BILLS_2012" VALUES LESS THAN ('X'),

PARTITION "BILLS_OPEN" VALUES LESS THAN (MAXVALUE)); 2 3 4 5 6 7 8

Table created.

SQL>

SQL> CREATE TABLE FREIGHT_ITEM_T

("BILLID" CHAR(10 BYTE) NOT NULL ENABLE,

"FRTITEMID" CHAR(3 BYTE) NOT NULL ENABLE,

CONSTRAINT PK_FREIGHT_ITEM_T PRIMARY KEY ("BILLID"),

CONSTRAINT FK_FREIGHT_ITEM_T FOREIGN KEY ("BILLID") REFERENCES ---<<<<<<< 3.外键是参考BILL_DATA_T表的BILLED字段

BILL_DATA_T ("BILLID"))

PARTITION BY REFERENCE ----<<<<<<<<<<<<< 1.是partition by reference

("FK_FREIGHT_ITEM_T") ----<<<<<<<<<<< 2.参考了FK_FREIGHT_ITEM_T

(PARTITION "BILLS_2012",

PARTITION "BILLS_OPEN"); 2 3 4 5 6 7 8 9 10

Table created.

SQL>

SQL> select * from freight_item_t where billid='123456789000000'; ----<<<<< 5.超过char 10，就报错

select * from freight_item_t where billid='123456789000000'

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [6305], [16], [12], [1], [], [], [],

[], [], [], [], []

SQL>

SQL> c/123456789000000/123456789

1* select * from freight_item_t where billid='123456789' -----<<<<<< 6.不超过char 10，就不会报错。

SQL> /

no rows selected

SQL>

这其实是bug 13 […]

..experience, Working case

SQL执行时间受游标影响不准

2013-11-26 没有评论

sql的执行时间，我们往往可以通过ash中的sample时间，减去sql exec start，得出该sql执行的时间。有一个很不错的sql，可以看某个sql的历次执行时间，历次执行计划。

SELECT T.SQL_ID, T.SQL_EXEC_ID,
       CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
       T.SQL_EXEC_START EXEC_START_TIME,
       ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,
       T.SQL_PLAN_HASH_VALUE, T.MODULE
  FROM v$active_session_history T
 WHERE T.SQL_ID = '&SQL_ID'
   AND T.SAMPLE_TIME > SYSDATE - 36
 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
 ORDER BY EXEC_END_TIME DESC;

或者

SELECT T.SQL_ID, T.SQL_EXEC_ID,
       CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
       T.SQL_EXEC_START EXEC_START_TIME,
       ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,
       T.SQL_PLAN_HASH_VALUE, T.MODULE
  FROM sys.WRH$_ACTIVE_SESSION_HISTORY T
 WHERE T.SQL_ID = '&SQL_ID'
   AND T.SAMPLE_TIME > SYSDATE - 36
 GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
 ORDER BY EXEC_END_TIME DESC;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

SELECT T.SQL_ID, T.SQL_EXEC_ID,

CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,

T.SQL_EXEC_START EXEC_START_TIME,

ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,

T.SQL_PLAN_HASH_VALUE, T.MODULE

FROM v$active_session_history T

WHERE T.SQL_ID = '&SQL_ID'

AND T.SAMPLE_TIME > SYSDATE - 36

GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE

ORDER BY EXEC_END_TIME DESC;

或者

SELECT T.SQL_ID, T.SQL_EXEC_ID,

CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,

T.SQL_EXEC_START EXEC_START_TIME,

ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440, 2) RUN_MINS,

T.SQL_PLAN_HASH_VALUE, T.MODULE

FROM sys.WRH$_ACTIVE_SESSION_HISTORY T

WHERE T.SQL_ID = '&SQL_ID'

AND T.SAMPLE_TIME > SYSDATE - 36

GROUP BY T.SQL_ID, T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE

ORDER BY EXEC_END_TIME DESC;

但是在使用游标的时候，如，在某个proced […]