分类目录归档：Oracle

OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ参数说明

发表于 2012 年 6 月 14 日由惜分飞

OPTIMIZER_INDEX_COST_ADJ参数说明
OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
这个数反映执行多块IO（全表扫描）的成本与执行单个IO（索引读取）的成本。保持为100，则多块IO与单块IO成本相同。设为50优化程序认为访问单块IO的成本为多块IO的一半。

OPTIMIZER_INDEX_COST_ADJ参数试验

SQL> create table t_xifenfei
  2  as
  3   select object_id,object_name from dba_objects where rownum<101;

Table created.

SQL> create index ind_t_xifenfei on t_xifenfei(object_id);

Index created.

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>  show parameter OPTIMIZER_INDEX;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_index_caching              integer                0
optimizer_index_cost_adj             integer                100

SQL> set linesize 150
SQL> set autot trace ext
SQL> select object_name from t_xifenfei where object_id>100;


Execution Plan
----------------------------------------------------------
Plan hash value: 2444553208

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI     |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID">100)

SQL> alter session set OPTIMIZER_INDEX_COST_ADJ=1000;

Session altered.

SQL> select object_name from t_xifenfei where object_id>100;

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    11 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    11 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">100)

通过试验证明修改OPTIMIZER_INDEX_COST_ADJ会导致执行计划走index还是全表扫描

OPTIMIZER_INDEX_CACHING参数说明
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.You can modify the optimizer’s assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache. Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.
这个表明的是在nested loops joins and IN-list iterators的时候,如果使用了OPTIMIZER_INDEX_CACHING参数,表明两个表关联的时候优化器考虑index cache的比例,从而选择不同的执行计划.而不是网上所说的优化器考虑所有情况下的index的cache情况(这个参数只有在nested loops joins and IN-list iterators表关联的时候的index才会被优化器考虑[index cache的比例]).进一步说明:这个参数影响两个表关联的时候是选择hash jion还是nested loops joins/sort-merge joins

总结说明
1.关于OPTIMIZER_INDEX_CACHING的参数效果我未试验出来(可能方法不正确)
2.根据网上建议在oltp系统中设置
optimizer_index_caching = 0 optimizer_index_cost_adj = 100 的默认值，一般时候数据仓库报表系统。
optimizer_index_caching = 90 optimizer_index_cost_adj = 25-50 一般时候事务处理/OLTP系统
3.设置这些参数可能存在bug

发表在 Oracle | 一条评论

因IPC导致多个监听不能正常启动

发表于 2012 年 6 月 12 日由惜分飞

在一台机器上装了9.2.0.4和10.1.0.4数据库,启动监听时候发现有一个启动不了
9i监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1522))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

10g监听配置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u02/app/oracle/db10g)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xifenfei.com)(PORT = 1521))
      )
    )
  )

分别启动监听
启动10g监听

[oraem@xifenfei ~]$ lsnrctl start

LSNRCTL for Linux: Version 10.1.0.4.0 - Production on 12-JUN-2012 15:28:51

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Starting /u02/app/oracle/db10g/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.1.0.4.0 - Production
System parameter file is /u02/app/oracle/db10g/network/admin/listener.ora
Log messages written to /u02/app/oracle/db10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.1.0.4.0 - Production
Start Date                12-JUN-2012 15:28:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u02/app/oracle/db10g/network/admin/listener.ora
Listener Log File         /u02/app/oracle/db10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

启动9i监听

[oracle@xifenfei ~]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:01:44

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
  TNS-00512: Address already in use
   Linux Error: 98: Address already in use

Listener failed to start. See the error message(s) above...

这里很奇怪两个监听使用不同的端口,为什么不能提示Address被占用呢,难道9i的已经启动了

查看9i的监听状态

[oracle@xifenfei ~]$ lsnrctl status

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:02:23

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xifenfei)(PORT=1522)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12618: TNS:versions are incompatible

9i监听不能启动原因
这里有重大发现TNS:versions are incompatible.我这台服务器只有一个9i和一个10g的数据库出现版本不兼容,那就是说这个命令可能访问了10g的库中的每个监听信息.继续往上看,发现(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC))).
When a process is on the same machine as the server, use the IPC protocol for connectivity instead of TCP. Inner Process Communication on the same machine does not have the overhead of packet building and deciphering that TCP has.
通过这段话可以看出IPC(Inner Process Communication)是使用于本机的内部通讯,不用包的封装,可以很大程度上提高程序执行效率.看到上面的IPC提示,我们可以确定该错误是因为10g中已经启用了IPC,然后9i的监听也要来启动这个,从而导致该错误.

解决办法
因为在同一个机器上,只能其中的一个数据库启用IPC.修改9i的监听配置

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      )
    #  (ADDRESS_LIST =
    #    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    #  )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = xifenfei)
      (ORACLE_HOME = /u01/oracle/9.2.0/db_1)
      (SID_NAME = xff)
    )
  )

启动9i监听

[oracle@xifenfei ~]$ lsnrctl

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 12-JUN-2012 15:12:55

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start
Starting /u01/oracle/9.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                12-JUN-2012 15:12:57
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /u01/oracle/9.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/9.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xifenfei.com)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "xifenfei" has 1 instance(s).
  Instance "xff", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

查看两个监听进程

[oracle@xifenfei ~]$ ps -ef|grep tns|grep -v grep
oraem    18099     1  0 13:27 ?        00:00:00 /u02/app/oracle/db10g/bin/tnslsnr LISTENER -inherit
oracle   24312     1  0 15:12 pts/0    00:00:00 /u01/oracle/9.2.0/db_1/bin/tnslsnr LISTENER -inherit

发表在 Oracle 监听 | 一条评论

因使用OEM引起ORA-00600[12761]

发表于 2012 年 6 月 11 日由惜分飞

alert日志报ORA-00600[12761]错

Sun Jun 10 13:52:56 2012
Errors in file e:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_19840.trc:
ORA-04030: 在尝试分配 82444 字节 (pga heap,control file i/o buffer) 时进程内存不足
ORA-00600: 内部错误代码, 参数: [12761], [], [], [], [], [], [], []
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-04030: 在尝试分配 123404 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足

数据库版本信息

Sun Jun 10 13:52:56 2012
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1263M/4095M, Ph+PgF:2716M/5976M, VA:19M/2047M
Instance name: interlib

trace信息

*** 2012-06-10 13:52:56.763
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [12761], [], [], [], [], [], [], []
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-04030: 在尝试分配 123404 字节 (QERHJ hash-joi,kllcqas:kllsltba) 时进程内存不足
Current SQL statement for this session:
BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0BC35C44         1  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
00404686             CALLrel  00404694             0 1
0040307E             CALLrel  00404660             0
0043AB6A             CALLrel  00402CFC             3
603A816A             CALLreg  00000000             6190E2E0 3
603A8550             CALLrel  603A80D8             6190E2E0 5E340020 31D9 0
                                                   5ED6CDF8
031B7197             CALLrel  025FA21E             6190E2E0 5E340020 31D9 2 0
02C92859             CALLrel  02C92360             5ED6D2B4 5ED6D3CC 2 61BA71E4
                                                   0 5ED6CEFA
60BAD7C6             CALL???  00000000             5ED6D2B4 5ED6D3CC 3 61BA71E4
                                                   0 5ED6CEFA
60C41C40             CALLrel  60BAD758             5D9356A0 F1 3 5E344888
60C3C780             CALL???  00000000             5D9356A0 951F190 5D9356DC
60C3D1BD             CALLrel  60C3C748             5D9356A0 95A97F0 5D9356DC
60BB0392             CALLrel  60C3CEB0             5D9356A0
60B89393             CALLrel  60BB00B0             5D9356A0 1 0
02600CD9             CALLrel  0260F22C             
0140AF2C             CALLrel  02600B3C             4E8EC08
013CBFEC             CALLrel  01409984             49 3 5ED6DB14
0085174B             CALLreg  00000000             5E 17 5ED6F6F8
60FEFF8D             CALLreg  00000000             5E 17 5ED6F6F8 0
00850A69             CALL???  00000000             
0122134B             CALLrel  00850670             0 0
0085174B             CALLreg  00000000             3C 4 5ED6FC90
00420E53             CALLrel  00851300             3C 4 5ED6FC90 0
00421645             CALLrel  00420B20             3C 4 5ED6FC90
0040116C             CALLrel  00421618             5ED6FC84 3C 4 5ED6FC90
0040105C             CALLrel  004010FC             2 5ED6FCBC
00401900             CALLrel  00401000             
7C82482C             CALLreg  00000000             
 
--------------------- Binary Stack Dump ---------------------

--会话信息
    (session) sid: 525 trans: 00000000, creator: 7AE024D8, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-001B-00000004, short-term DID: 0000-0000-00000000
              txn branch: 00000000
              oct: 47, prv: 0, sql: 7A0F0A38, psql: 7A0A2430, user: 51/SYSMAN
    O/S info: user: , term: , ospid: 1234, machine: tushuguan01
              program: OMS
    client info: tushuguan01_Management_Service
    application name: OEM.SystemPool, hash value=2960518376

通过这里我们可以得到几个信息
1.数据库先发生了ORA-00600[12761],然后引发了ORA-04030
2.引发ORA-00600[12761]错误的原因是因为OEM的某种操作导致
3.未知因某种原因导致Call Stack Trace信息不完善,无法准确评估bug情况
4.查询数据库当前最大使用使用pga为250M,数据库配置pga为500M,原则上讲不是pga消耗完导致4030错误,可能是这个会话在执行某个基表的查询时候的hash-jion运算时pga不足导致.
5.查询dba_users发现EM_PING不是数据库用户,查询dba_source发现RECORD_BATCH_HEARTBEAT不是plsql名称,从这里可以看出OEM调用程序有一定特殊性

对于该问题的解决方案
1.因为OEM功能不太使用,建议直接关闭该进程,并设置为开机不自动启动
2.因为信息不完善,无法确定具体bug,但目前数据库版本为10.2.0.1,强烈建议升级到新版本

发表在 ORA-xxxxx | 标签为 ORA-600 12761, ORA-600[12761] | 一条评论

17813235971

QQ 咨询

分类目录归档：Oracle

OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ参数说明

因IPC导致多个监听不能正常启动

因使用OEM引起ORA-00600[12761]

文章分类

最近发表

分类目录归档：Oracle

OPTIMIZER_INDEX_CACHING和OPTIMIZER_INDEX_COST_ADJ参数说明

因IPC导致多个监听不能正常启动

因使用OEM引起ORA-00600[12761]

标签云

文章分类

最近发表