分类目录归档：Oracle

当一个数据块读入sga区，相应的buffer header会被放置到hash列表上,我们称其这hash chains,chain在中文的意为链条或串的意思，表达就是关连性.如果一个进程想访问或修改hash chain上的block,它首先要获得”cache buffers chains” latch。

原因一：低效率的SQL语句（主要体现在逻辑读过高）
cache buffers chains latch很大程度与逻辑读有关，所以要观注v$sql中BUFFER_GETS/EXECUTIONS大的语句。
同时每一个逻辑读需要一个latch get 操作及一个cpu操作，这样的sql也会很耗cpu资源。

原因二：热块（访问过于频繁）
找出热点块方法一：

--找出p1raw
select p1,p1raw from v$session_wait where event='latch: cache buffers chains';

--找到对象
SELECT /*+ RULE */
 E.OWNER || '.' || E.SEGMENT_NAME SEGMENT_NAME,
 E.PARTITION_NAME,
 E.EXTENT_ID EXTENT#,
 X.DBABLK - E.BLOCK_ID + 1 BLOCK#,
 X.TCH,
 L.CHILD#
  FROM SYS.V$LATCH_CHILDREN L, SYS.X$BH X, SYS.DBA_EXTENTS E
 WHERE X.HLADDR = '00000002576EE018'--p1raw
   AND E.FILE_ID = X.FILE#
   AND X.HLADDR = L.ADDR
   AND X.DBABLK BETWEEN E.BLOCK_ID AND E.BLOCK_ID + E.BLOCKS - 1
 ORDER BY X.TCH DESC;

找出热点块方法二：

--直接找出热点块
SELECT OBJECT_NAME, SUBOBJECT_NAME
  FROM DBA_OBJECTS
 WHERE DATA_OBJECT_ID IN
       (SELECT DATA_OBJECT_ID
          FROM (SELECT OBJ DATA_OBJECT_ID, FILE#, DBABLK, CLASS, STATE, TCH
                  FROM X$BH
                 WHERE HLADDR IN (SELECT ADDR
                                    FROM (SELECT ADDR
                                            FROM V$LATCH_CHILDREN
                                           ORDER BY (GETS + MISSES + SLEEPS) DESC)
                                   WHERE ROWNUM < 10)
                 ORDER BY TCH DESC)
         WHERE ROWNUM < 10);

LSNRCTL> change_password
Old password:
New password:
Reenter new password:
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
Password changed for LISTENER
The command completed successfully
LSNRCTL> set password
Password:
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
Saved LISTENER configuration parameters.
Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Old Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.bak
The command completed successfully

[oracle@ecp-uc-db1 admin]$ cat listener.ora
#—-ADDED BY TNSLSNR 10-JUN-2011 18:13:24—
PASSWORDS_LISTENER = 6D7AA003392C436A
#——————————————–

note：10g数据库上需要上添加（重启监听）
LOCAL_OS_AUTHENTICATION_LISTENER = OFF
1、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之前
Security ON: Password or Local OS Authentication
2、添加LOCAL_OS_AUTHENTICATION_LISTENER = OFF之后
Security ON: Password

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL> set password 123456
The command completed successfully
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecp-uc-db1)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.4.0 – Production
Start Date 10-JUN-2011 18:15:49
Uptime 0 days 0 hr. 1 min. 16 sec
Trace Level off
Security ON: Password
SNMP OFF
Listener Parameter File /opt/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /opt/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ECP-UC-DB1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “ecp” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
Service “ecpXDB” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
Service “ecp_XPT” has 1 instance(s).
Instance “ecp”, status READY, has 1 handler(s) for this service…
The command completed successfully