ORA-00600 qosdExpStatRead expcnt mismatch

Oracle 12.2.0.1最近出现了ORA***211;0600错误,其详细信息如下:

ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168
ORA-00600: 内部错误代码, 参数: [qosdExpStatRead: expcnt mismatch], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: 在 "SYS.DBMS_STATS", line 1976
ORA-06512: 在 "SYS.DBMS_STATS", line 46935
ORA-06512: 在 "SYS.DBMS_STATS", line 47168

根据Oracle官方给出的检查SQL语句,其执行结果如下:

引起访问题的原因是由于sys.exp_obj$.exp_cnt与sys.exp_stat$的行数据不匹配所造成的

解决方案
1.备份数据库涉及到修改数据字典

2. 修复问题

SQL>update sys.exp_obj$ a set exp_cnt=(select count(*) from sys.exp_stat$ b where
a.objn=b.objn and a.snapshot_id=b.snapshot_id ) where a.objn=124789;

SQL>commit;

3. 修改后,执行如下SQL确认是否还有记录,如果没有记录,说明已经修改成功,提交。

SQL>With b as (
select count(*) cnt,objn,snapshot_id from sys.exp_stat$ es group by objn,snapshot_id)
select * from sys.exp_obj$ a, b where a.objn=b.objn and a.snapshot_id=b.snapshot_id
and a.EXP_CNT<>b.CNT;

修复后,通过一天的观察没有出现该问题了。

oracle rac 单个实例不能生成awr报告的问题

同事对rac集群生成性能报告时发现rac集群有一个实例没有生成awr快照,另一个实例快照正常。下面是具体处理步骤。
1号实例没有生成awr快照

SQL> select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=1;

no rows selected

2号实快照正常

SQL> set long 200   
SQL> set linesize 200
SQL> select * from ( select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=2 order by SNAP_ID desc) where rownum < =10;

   SNAP_ID END_INTERVAL_TIME                                                           INSTANCE_NUMBER
---------- --------------------------------------------------------------------------- ---------------
     24405 17-AUG-19 07.00.47.595 PM                                                                 2
     24404 17-AUG-19 06.00.42.150 PM                                                                 2
     24403 17-AUG-19 05.00.37.041 PM                                                                 2
     24402 17-AUG-19 04.00.31.774 PM                                                                 2
     24401 17-AUG-19 03.00.26.414 PM                                                                 2
     24400 17-AUG-19 02.00.21.176 PM                                                                 2
     24399 17-AUG-19 01.00.16.316 PM                                                                 2
     24398 17-AUG-19 12.00.10.997 PM                                                                 2
     24397 17-AUG-19 11.00.05.446 AM                                                                 2
     24396 17-AUG-19 10.00.59.801 AM                                                                 2

10 rows selected.

mmon进程与awr快照相关,mmnl与ash相关,如是查看两个实例的mmon与mmnl进程
2号实例

[root@db2 ~]# ps -ef | grep mmon
root     128329 127956  0 18:11 pts/2    00:00:00 grep mmon
oracle   201527      1  0  2018 ?        17:17:11 ora_mmon_RLZY2
[root@db2 ~]# ps -ef | grep mmnl
root     131772 127956  0 18:17 pts/2    00:00:00 grep mmnl
oracle   201531      1  0  2018 ?        1-06:06:24 ora_mmnl_RLZY2

1号实例

[root@db1 ~]# ps -ef | grep mmon
root     239020 238963  0 18:52 pts/2    00:00:00 grep mmon
[root@db1 ~]# ps -ef | grep mmnl
root     239052 238963  0 18:52 pts/2    00:00:00 grep mmnl

可以看到1号实例没有mmon与mmnl进程了。

如是查看1号实例的mmon进程的跟踪文件

[root@db1 trace]# ls -lrt *mmon*.trc
-rw-r----- 1 oracle asmadmin 1351052 Jan 19  2018 RLZY1_mmon_20073.trc
-rw-r----- 1 oracle asmadmin  173031 Jan 22  2018 RLZY1_mmon_49119.trc
[root@db1 trace]# more RLZY1_mmon_49119.trc
Trace file /u01/app/oracle/diag/rdbms/rlzy/RLZY1/trace/RLZY1_mmon_49119.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      db1
Release:        3.8.13-68.3.4.el6uek.x86_64
Version:        #2 SMP Tue Jul 14 15:03:36 PDT 2015
Machine:        x86_64
Instance name: RLZY1
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 49119, image: oracle@db1 (MMON)


*** 2018-01-19 13:55:20.030
*** SESSION ID:(1369.1) 2018-01-19 13:55:20.030
*** CLIENT ID:() 2018-01-19 13:55:20.030
*** SERVICE NAME:() 2018-01-19 13:55:20.030
*** MODULE NAME:() 2018-01-19 13:55:20.030
*** ACTION NAME:() 2018-01-19 13:55:20.030
 
minact-scn slave-status: grec-scn:0x0000.00000000 gmin-scn:0x0000.00000000 gcalc-scn:0x0000.00000000

*** 2018-01-19 14:00:20.643
minact-scn master-status: grec-scn:0x0e0e.55ad96ef gmin-scn:0x0e0e.55abf256 gcalc-scn:0x0e0e.55ac2a0a

..........
KEBM: MMON action policy violation. 'Block Cleanout Optim, Undo Segment Scan' viol=1; err=12751
minact-scn master-status: grec-scn:0x0e0e.5f0ebf8c gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91
DDE rules only execution for: ORA 12751

*** 2018-01-22 07:06:04.060
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
Executing ASYNC actions
----- START DDE Action: 'ORA_12751_DUMP' (Sync) -----
Runtime exceeded 300 seconds
Time limit violation detected at:
ksedsts()+465< -kspol_12751_dump()+145<-dbgdaExecuteAction()+1065<-dbgerRunAction()+109<-dbgerRunActions()+4134<-dbgexPhaseII()+1873<-dbgexProcessError()+2680<-dbgeExecuteForError()+88<-dbgePostErrorKGE()+2136<-dbkePostKGE_kgsf()+71<-kge
selv()+276<-ksesecl0()+162
<-ksucin()+147<-kcbzwb()+2727<-kcbgtcr()+31325<-ktucloUsMinScn()+539<-ktucloUsegScan()+992<-ksb_run_managed_action()+384<-ksbcti()+2490<-ksbabs()+1735<-kebm_mmon_main()+209<-ksbrdp()+1045<-opirip()+623<-opidrv()+603<-sou2o()+103<-opimai
_real()+250<-ssthrdmain()+265
<-main()+201<-__libc_start_main()+253Current Wait Stack:
 0: waiting for 'gc buffer busy acquire'
    file#=0x5, block#=0x278, class#=0x49
    wait_id=255378 seq_num=59358 snap_id=1
    wait times: snap=5 min 5 sec, exc=5 min 5 sec, total=5 min 5 sec
    wait times: max=infinite, heur=5 min 5 sec
    wait counts: calls=358 os=358
    in_wait=1 iflags=0x15a2
There is at least one session blocking this session.
  Dumping 1 direct blocker(s):
    inst: 1, sid: 990, ser: 1
  Dumping final blocker:
    inst: 1, sid: 990, ser: 1
Wait State:
  fixed_waits=0 flags=0x22 boundary=(nil)/-1
Session Wait History:
    elapsed time of 0.000061 sec since current wait
 0: waited for 'gc cr block 2-way'
    =0x5, =0x258, =0x47
    wait_id=255377 seq_num=59357 snap_id=1
    wait times: snap=0.000478 sec, exc=0.000478 sec, total=0.000478 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000122 sec of elapsed time
 1: waited for 'gc cr block 2-way'
    =0x5, =0x228, =0x45
    wait_id=255376 seq_num=59356 snap_id=1
    wait times: snap=0.000741 sec, exc=0.000741 sec, total=0.000741 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000120 sec of elapsed time
 2: waited for 'gc cr block 2-way'
    =0x5, =0x138, =0x43
    wait_id=255375 seq_num=59355 snap_id=1
    wait times: snap=0.000528 sec, exc=0.000528 sec, total=0.000528 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000111 sec of elapsed time
 3: waited for 'gc cr block 2-way'
    =0x5, =0xb8, =0x41
    wait_id=255374 seq_num=59354 snap_id=1
    wait times: snap=0.000583 sec, exc=0.000583 sec, total=0.000583 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000139 sec of elapsed time
 4: waited for 'gc cr block 2-way'
    =0x5, =0x110, =0x37
    wait_id=255373 seq_num=59353 snap_id=1
    wait times: snap=0.000541 sec, exc=0.000541 sec, total=0.000541 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000110 sec of elapsed time
 5: waited for 'gc cr block 2-way'
    =0x5, =0x100, =0x35
    wait_id=255372 seq_num=59352 snap_id=1
    wait times: snap=0.000629 sec, exc=0.000629 sec, total=0.000629 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000158 sec of elapsed time
 6: waited for 'gc cr block 2-way'
    =0x5, =0xf0, =0x33
    wait_id=255371 seq_num=59351 snap_id=1
    wait times: snap=0.000617 sec, exc=0.000617 sec, total=0.000617 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000128 sec of elapsed time
 7: waited for 'gc cr block 2-way'
    =0x5, =0xe0, =0x31
    wait_id=255370 seq_num=59350 snap_id=1
    wait times: snap=0.000561 sec, exc=0.000561 sec, total=0.000561 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000124 sec of elapsed time
 8: waited for 'gc cr block 2-way'
    =0x5, =0xd0, =0x2f
    wait_id=255369 seq_num=59349 snap_id=1
    wait times: snap=0.000565 sec, exc=0.000565 sec, total=0.000565 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000128 sec of elapsed time
 9: waited for 'gc cr block 2-way'
    =0x5, =0xc0, =0x2d
    wait_id=255368 seq_num=59348 snap_id=1
    wait times: snap=0.000555 sec, exc=0.000555 sec, total=0.000555 sec
    wait times: max=infinite
    wait counts: calls=1 os=1
    occurred after 0.000125 sec of elapsed time
Sampled Session History of session 1369 serial 1
---------------------------------------------------
The sampled session history is constructed by sampling
the target session every 1 second. The sampling process
captures at each sample if the session is in a non-idle wait,
an idle wait, or not in a wait. If the session is in a
non-idle wait then one interval is shown for all the samples
the session was in the same non-idle wait. If the
session is in an idle wait or not in a wait for
consecutive samples then one interval is shown for all
the consecutive samples. Though we display these consecutive
samples  in a single interval the session may NOT be continuously
idle or not in a wait (the sampling process does not know).
 
The history is displayed in reverse chronological order.
 
sample interval: 1 sec, max history 120 sec
---------------------------------------------------
  [121 samples,                                            07:04:03 - 07:06:03]
    waited for 'gc buffer busy acquire', seq_num: 59358
      p1: 'file#'=0x5
      p2: 'block#'=0x278
      p3: 'class#'=0x49
      time_waited: >= 120 sec (still in wait)
---------------------------------------------------
Sampled Session History Summary:
  longest_non_idle_wait: 'gc buffer busy acquire'
  [121 samples, 07:04:03 - 07:06:03]
      time_waited: >= 120 sec (still in wait)
---------------------------------------------------
----- END DDE Action: 'ORA_12751_DUMP' (SUCCESS, 1 csec) -----
----- END DDE Actions Dump (total 1 csec) -----
KEBM: MMON action policy violation. 'Block Cleanout Optim, Undo Segment Scan' viol=1; err=12751
minact-scn master-status: grec-scn:0x0e0e.5f0ec4ce gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91

*** 2018-01-22 07:11:11.071
DDE rules only execution for: ORA 12751
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----

ORA12751的错误原因是陈旧的SYS对象统计数据会导致生成次优执行计划,从而使AWR自动刷新从操作的语句运行更长时间和超时。

解决方法就是收集新的SYS对象统计信息,为优化器提供更好的统计信息,并生成更高效的执行计划

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

PL/SQL procedure successfully completed.

下面就是重启mmon和mmnl进程

SQL> alter system enable restricted session;

System altered.

SQL> alter system disable restricted session;

System altered.

查看alert日志可以看到mmon和mmnl进程已经重启了

Sat Aug 17 19:18:22 2019
Starting background process MMON
Sat Aug 17 19:18:22 2019
Starting background process MMNL
MMON started with pid=399, OS id=10373 
Sat Aug 17 19:18:22 2019
MMNL started with pid=405, OS id=10375 
ALTER SYSTEM enable restricted session;
Sat Aug 17 19:18:25 2019
Some DDE async actions failed or were cancelled
Sat Aug 17 19:18:25 2019
Sweep [inc][48021]: completed
Sweep [inc][48011]: completed
Sweep [inc][48002]: completed
Sweep [inc][35010]: completed
Sweep [inc][34706]: completed
Sweep [inc][34242]: completed
Sweep [inc][33546]: completed
Sweep [inc][33394]: completed
Sweep [inc2][48021]: completed
Sweep [inc2][48011]: completed
Sweep [inc2][48002]: completed
Sweep [inc2][35010]: completed
Sweep [inc2][34706]: completed
Sweep [inc2][34242]: completed
Sweep [inc2][33546]: completed
Sweep [inc2][33394]: completed
minact-scn: Inst 1 is a slave inc#:30 mmon proc-id:10373 status:0x2
minact-scn status: grec-scn:0x0e0e.61cb2e9c gmin-scn:0x0e0e.5f0eac2e gcalc-scn:0x0e0e.5f0ead91
Sat Aug 17 19:18:29 2019
db_recovery_file_dest_size of 10240 MB is 20.87% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat Aug 17 19:18:42 2019
ALTER SYSTEM disable restricted session;

再查看1号实例的mmon与mmnl进程状态

[root@db1 ~]# ps -ef | grep mmnl
oracle    10375      1  0 19:18 ?        00:00:00 ora_mmnl_RLZY1
root      10611 238963  0 19:18 pts/2    00:00:00 grep mmnl

[root@db1 ~]# ps -ef | grep mmon
oracle    10373      1  7 19:18 ?        00:00:02 ora_mmon_RLZY1
root      10630 238963  0 19:18 pts/2    00:00:00 grep mmon
过了两个小时去查看1号实例已经生成了两条快照信息
SQL> set long 200
SQL> set linesize 200
SQL> select * from ( select SNAP_ID,END_INTERVAL_TIME,instance_number from dba_hist_snapshot where instance_number=1 order by SNAP_ID desc) where rownum < =10;

   SNAP_ID END_INTERVAL_TIME                                                           INSTANCE_NUMBER
---------- --------------------------------------------------------------------------- ---------------
     24407 17-AUG-19 09.00.58.595 PM                                                                 1
     24406 17-AUG-19 08.00.40.244 PM                                                                 1

到此问题解决了。

当用户无限制使用表空间配额且表空间有足够空间时在执行DML操作时出现超出表空间的空间限额

朋友生产数据库在向特定的一张表插入数据时报超出表空间data的空间限额如是执行下查看用户所用的表空间配额信息,查看用户所使用表空间的配额发现没有限制,因为max_bytes为 -1

SQL>  select * from dba_ts_quotas where username='data';

TABLESPACE_NAME                USERNAME                            BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS DROPPED
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- -------
data                           data                           8825732464         -1  107735992         -1 NO

 1 rows selected

SQL>
SQL> select
  2    fs.tablespace_name            "Tablespace",
  3    (df.totalspace-fs.freespace)  "Used MB",
  4    fs.freespace                  "Free MB",
  5    df.totalspace                 "Total MB",
  6    round(100*(fs.freespace/df.totalspace)) "Pct. Free"
  7  from
  8    (select tablespace_name,round(sum(bytes)/1024/1024) TotalSpace
  9    from dba_data_files group by  tablespace_name) df,
 10    (select tablespace_name,round(sum(bytes)/1024/1024) FreeSpace
 11    from dba_free_space group by tablespace_name) fs
 12  where df.tablespace_name=fs.tablespace_name;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
SYSTEM                               7207       3033      10240         30
TEST                                 6790      34170      40960         83
USERS                                 173      25427      25600         99
UNDOTBS2                              227      24013      24240         99
DATA                               990119     176281    1166400         15
SYSAUX                               3925       1195       5120         23
UNDOTBS1                            12898      28062      40960         69

7 rows selected

查看表lv_data的依赖对象

SQL> select NAME,TYPE from dba_dependencies where REFERENCED_NAME='LV_DATA';

NAME                           TYPE
------------------------------ ------------------
LV_DATA                        VIEW
FC_UPDATE_CORPFUND             PROCEDURE
FC_UPDATE_MY                   PROCEDURE
FC_UPDATE_KY                   PROCEDURE
FC_UPDATE_FACTPAY              PROCEDURE
FC_UPDATE_CALCPAY              PROCEDURE
FC_UPDATE_KY                   PROCEDURE
......
LV_DATA                        SYNONYM
LV_DATA                        VIEW
LV_DATA                        SYNONYM
LV_DATA                        SYNONYM

139 rows selected

查看所有依赖对象的所有者

SQL> select  distinct owner from dba_objects where OBJECT_NAME in(select NAME from dba_dependencies where REFERENCED_NAME='LV_DATA');

OWNER
------------------------------
SY
SY_BK
WEB
CX
DATA
OLD
TEST
XC
CZ
OSY
BACKUP
TJ

12 rows selected

对所有依赖对象所有者授权可以无限制使用表空间

SQL> grant unlimited tablespace to OSY;

Grant succeeded

SQL> grant unlimited tablespace to SBK;

Grant succeeded

SQL> grant unlimited tablespace to WEB;

Grant succeeded

SQL> grant unlimited tablespace to CX;

Grant succeeded

SQL> grant unlimited tablespace to DATA;

Grant succeeded

SQL> grant unlimited tablespace to OLD;

Grant succeeded

SQL> grant unlimited tablespace to TEST;

Grant succeeded

SQL> grant unlimited tablespace to XC;

Grant succeeded

SQL> grant unlimited tablespace to CZ;

Grant succeeded

SQL> grant unlimited tablespace to SY;

Grant succeeded

SQL> grant unlimited tablespace to BACKUP;

Grant succeeded

SQL> grant unlimited tablespace to TJ;

Grant succeeded

再向表lv_data插入数据时恢复正常

Oracle Shared Pool Memory Management

Oracle在管理共享池内存方面面临着难以置信的挑战。多年来的所有改变、bug、补丁和各种性能问题都说明了这一点。虽然这可能会引起一些同情,但当面对与内存管理相关的棘手问题时,同情很快就会转化为愤怒。在本节中,我将解释如何管理共享池内存、多年来的管理进展、如何分配和释放内存、如何处理可能出现的4031错误,以及最后如何解决共享池锁存器争用。

From Hashing to Subpools
在Oracle 7和Oracle 8i中,共享池管理是在一种有趣的哈希结构帮助下执行的。如果还记得我们关于cache buffer 哈希链与library cache哈希链,那么这将非常有意义,但这里存在一种苦恼。当一个进程需要共享池中的内存时,它产生的哈希和链与所请求的内存大小相关。链也通常被称作heap,它是可用内存块链表。因此,从概念上讲,前几个链与大约1KB的内存块相关,后几个链与大约2KB的内存块相关,以此类推。虽然这确实很巧妙,但是经过一段时间对大小不一致的内存进行分配和释放之后,链实际上可以变成几千个节点长。请记住,哈希缓冲区链的大小平均在0到1之间。所以一个由几千个节点组成的链是巨大的。更糟的是,只有一个共享池latch锁来覆盖所有哈希链!清洗共享池帮助很大,因为链将减少到一个可观的规模。但这无法操作大型生产数据库,因此Oracle不得不进行更改。

Oracle9i引入了子池,这自然会导致多个共享池锁存器。基于哈希的策略被多个子池替换,每个子池包含一个在标准LRU策略上操作的堆。Oracle也开始标准化内存需求大小,这增加了找到可接受内存大小块的可能性。子池、多个共享池锁latch和LRU策略极大地减少了共享池内存管理问题。如果您同时管理过Oracle8i和Oracle9i系统,您可能会经历这种变化,并注意到有很大的不同。

数据库系统中共享池子池的数量可以通过查看实例参数_kghdsidx_count或通过计算x$kghlu视图中的行数来判断。

下面的查询显示了与共享池子池相关的一系列SQL语句。在这个例子中,一个大小为800MB的共享池存在三个子池。x$ksmss查询对于每个子池返回一行记录并且如果存在java pool还会另外加一行记录。设置子池数量的实例参数_kghdsidx_count不能被动态修改。如果你想影响Oracle调用一个子池号发生改变,你必须设置实例参数并回收实例。

SQL> @spspinfo
SQL> select sum(bytes/1024/1024) sp_size
2 from v$sgastat
3 where pool='shared pool';
SP Size (MB)
------------
         800
SQL> select count(*) no_sp from x$kghlu;
Num of SPs
----------
         4
SQL> select INST_ID, KSMDSIDX, KSMSSLEN
2 from x$ksmss
3 where ksmssnam='free memory';
INST_ID    KSMDSIDX   KSMSSLEN
---------- ---------- -----------
1          0          301989888
1          1          18818468
1          2          12659340
1          3          7697300
1          4          20482152
SQL> select i.ksppinm param,v.ksppstvl value
2 from x$ksppi i, x$ksppcv v
3 where v.indx=i.indx
4 and v.inst_id=i.inst_id
5 and i.ksppinm='_kghdsidx_count';
PARAM                VALUE
-------------------- -----
_kghdsidx_count      4

Oracle对子池的数量设置了严格的限制。在Oracle 11g中,可以使用7个共享池子池来启动实例,但有8个子池,该实例没有启动——实际上,在重新启动之前需要关闭实例。

有趣的是,Oracle不必遵从子池号的意愿。实际上,在一个类似于上面查询结果的Oracle数据库11.1g的示例中,实例参数被设置为2,实例重新启动,但是Oracle创建了三个子池。在Oracle数据库11.2g中,实例参数再次被设置为2,实例重新启动,并且按照指定的Oracle创建了两个子池。在没有手动设置实例参数的情况下运行Oracle数据库11.1g和11.2g, Oracle只创建了一个子池。因此,尽管你可以影响甲骨文,它仍然保留做出改变的权利。

内存分配与回收
内存分配是相当简单的。它遵循标准的LRU算法并与pinning与locking一起使用。当一个Oracle进程(服务器或后台进程)请求内存时,Oracle内核中的一部分称作为heap manager(堆管理器)的会被执行。虽然细节不断变化,但概念算法基本相同。

Oracle进程需要特定数量的内存,这些内存被转换为多个特定大小内存块的请求。堆管理器搜索与每个请求匹配的单个大小的内存块。多个内存块(认为是非连续的)是不行的。如果进程请求4KB内存,堆管理器必须从共享池内存中返回4KB内存块的地址。

在Oracle9i中,Oracle进程获得子池latch,并将在放弃之前搜索子池至多5次。因为内存的情况可能会发生急剧且快速的变化,允许多次传递会增加找到内存的可能性。然而在五次搜索之后,当持有各自共享池latch时,如果合适的大小的内存块没有找到,Oracle将会放弃,并posts错误代码4031,“out of mmemory”信息,并且会话将会停止处理。对于每个Oracle DBA来说,这在生产系统中将是不可接受的。

在Oracle Database 10g中,Oracle进程对内存的要求更加强烈。如果在五次搜索之后在当前共享池中没有找到合适的内存,进程将移动到另一个子池。这一过程将继续进行到所有定义的子池被搜索完为止。如果在这时,没有找到合适的内存,就像以前一样,Oracle将会放弃并posts 4031错误并且停止处理。Oracle在这个版本中所做的是消耗更多CPU和更长时间地持有共享池latch来减少返回错误消息的机会。从数据库操作的角度来看,性能较慢总比没有性能好。在我们解决性能问题时,至少可以执行工作。

当内存不足时,Oracle将回收不被频繁访问的内存块。可能在尝试检索SQL语句的文本时遇到过这种情况,并且它不再缓存在共享池中。幸运地是,Oracle不会回收内存供其它对象使用。例如,如果一个游标被pinned(固定)了,Oracle将不会回收相关的内存,不管该内存是不是被频繁访问。事实上,即使清除共享池也不会删除被固定的游标。如果真的想清空共享池并且想从头开始,可以重启实例。

共享池latch竞争识别与解决
共享池latch被用来序列化共享池内存的管理。这意味着像搜索内存,LRU活动,分配内存与回收内存请求共享池latch这样的操作。因为从Oracle 9i开始存在多个子池,并且每个子池有它自己的共享池latch,只要使用这个版本或之后的版本就可以大大减少共享池lat这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利ch竞争的可能性。但有些时候这些仍然不够。下面有些方案可能减少latch获取时间latch持有时间或者两者都减少。

固定大且频繁使用的对象
此策略用来确保对象成功进入缓存,不用管内存活动或对象大小。任何包第一次被调用时,整个包被加载到内存中。操作中在激活共享池后如果需要触发,将强制执行大量的内存管理活动,这将导致对象不能被加载而触发4031错误。即使如果对象被成功加载,用户可能会注意到应用程序的延迟。

有些时候可能想要固定小对象。例如,假设一个对象有一种高强度活动模式,长时间的暂停导致对象的内存被释放,然后是另一段高强度活动。为了确保没有应用程序延迟且为了减少内存管理,我们可以简单固定对象。

大多数大型Oracle应用程序都提供一个脚本,其中包含要固定在共享池中的对象,并且它们将建议在实例启动后立即运行该脚本。重要的是要知道,即使您的应用程序供应商提供了这样一个列表,您也可以通过了解您的组织实际使用对象的方式来细化这个列表。供应商应用程序开发人员通常会创建固定列表。然而大多数应用程序开发商认为他们的对象是最重要的并且应该总是被固定。但实际上,很多时候,在应用程序在生产环境中运行之前,没有人真正知道您的组织将如何使用它。因此如果出现4031错误,这对于修改固定列表来说是一个好消息。

想要确保对象总是固定在共享池中有四个简单步骤要操作。关键词pin常被使用,dbms_shared_pool包的keep函数被用来确保对象保留在共享池中。缺省情况下当创建数据库时这个包不会被加载,因此第一步就是要加载它。下面的代码就是用来创建这个过程。

[oracle@jytest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 6 14:30:28 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql

Session altered.


Package created.


Grant succeeded.


Session altered.

下一步骤是找到大的或频繁的对象。Oracle保持对共享池对象使用进行跟踪并且可以通过v$db_object_cache视图来查看这些信息。下面是使用OSM脚本dboc.sql来识别潜在的对象。您可能会看到一组比其他包大得多的包,以及执行得比其他包频繁得多的包。还可能有一些对象,您个人知道它们具有不同寻常的执行配置文件,而您希望缓存它们。

一旦有了要保存的对象列表,下一步就是确定如何将它们放入缓存中。keep函数用于固定对象,或者更好地说,用于将对象保存在共享池中。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10

DB/Inst: jy/jy2                                                   07-May 08:26am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
SYS          STANDARD                            PBDY     0     3    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0     1   122 NO
SYS          DBMS_SQLDIAG                        PBDY     0     1    40 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_AUTO_TASK                      PBDY     0     0    24 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0    28 NO
SYS          DBMS_STANDARD                       PKG      0     0    44 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_STATS_ADVISOR                  PBDY     0     0   167 NO
SYS          DBMS_SYS_ERROR                      PBDY     0     0     8 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_PDB                            PBDY     0     0    12 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO

22 rows selected.


SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name

为了将一个游标保存在共享池中,从v$sql,v$sqlarea或者v$open_cursor中收集它的地址与哈希值。下面的代码显示地址(6877c238)和哈希值(1356456286)在它们之间使用逗号进行连接作为一个参数输入,第二个参数是C,因为我们要保存一个游标。对于保存触发器参数为T,对于序列,使用Q,对于包,过程与函数,参数为P。

SQL> exec dbms_shared_pool.keep('6877C238,1356456286','C');
PL/SQL procedure successfully completed.

上面的代码片段可以用于编程结构,但是大多数人发现下面的选项最容易使用。下面的代码用来保存jy方案中的TuoMi过程。

SQL> exec dbms_shared_pool.keep('jy.TuoMi');

PL/SQL procedure successfully completed.

最后,在发出上述代码片段之后,您可以轻松地进行检查,以确保确实保存了对象。从下面的输出结果可以看到jy.TUOMI过程对象的Kept列被设置为YES。

SQL> @dboc 0 0
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 0
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 0

DB/Inst: jy/jy2                                                   07-May 08:48am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0    32   492 NO
SYS          PLITBLM                             PKG      0     8     8 NO
SYS          DBMS_ASSERT                         PBDY     0     6    16 NO
..............
SYS          DBMS_SQLTUNE_INTERNAL               PKG      0     0    71 NO
JY           TUOMI                               PRC      0     0    36 YES
SYS          DBMS_SMB_INTERNAL                   PBDY     0     0    32 NO
SYS          DBMS_SQLTUNE                        PKG      0     0    32 NO
..............
99 rows selected.

经常有人问我,多长时间修改一次固定列表。就我个人而言,除非有很好的理由,否则我不喜欢调用任何数据库更改。改进固定列表的一个很好的理由是,如果系统突然开始出现共享池latch争用,或者遇到4031个错误。这一点非常重要:如果添加了应用程序功能、发生了应用程序升级或应用程序使用发生了显著变化,则从更主动的角度细化固定列表。

清空共享池
虽然不在任何列表的最上面,但是只要刷新共享池就可以立即缓解共享池latch争用。对于oracle9i之前的系统尤其如此,那时还不存在子池。这很明显不是一个最优解决方案,因为每个对象都没有固定在共享池中将被删除并且它们的内存会被回收。初始结果可能会适得其反,因为它可能会导致立即进行大量的硬解析,正如我们所知,这会消耗大量CPU资源,并强制执行非自然数量的锁。然而,这种不幸的情况很快就会平息下来。

有时,共享池大小的组合,数据库版本(Oracle 9i)与应用程序的使用将使DBA别无选择,只能计划定期共享池刷新。这就是现实情况。

如下面的代码片段所示,刷新共享池非常简单,但效果确实显著

SQL> alter system flush shared_pool;

System altered.

增加子池数量
最简单、最强大和最合适的共享池latch解决方案之一是简单地添加子池,增加子池也将增加共享池latch。前面的“从哈希到子池”小节详细介绍了这个过程。这个特别的解决方案非常简洁,因为它只需要很少的工作,而且我们不是在玩弄Oracle的共享池LRU算法。但是,请记住,更多的子池可能需要更多的共享池内存,需要重新启动实例才能使实例参数更改生效,Oracle保留不尊重您的建议的权利。

减少共享池大小
这听起来可能很奇怪,在子池存在之前,增加共享池大小最终可能导致共享池latch争用。每一种算法的性能都是有限的,都是针对特定情况而设计的。当情况发生变化时,算法可能无法按预期执行。不要忘记,增加缓存来支持更多的活动几乎总是需要更多的CPU资源来管理。因此,可能会有一个收益递减点。Oracle最初的共享池内存管理算法在大约600MB的共享池中运行得相当好,但是当它达到750MB左右时,dba开始看到大量的共享池latch争用是非常常见的。一旦引入了子池,特别是与我概述的其他解决方案相结合,共享池latch争用就可以成功地解决。

4031错误解决方案
Oracle在决定什么时候放弃,什么时候继续使用CPU和保持latches之间有一个微妙的平衡。多年来,Oracle耗尽共享池内存的可能性已经降低,但是4031错误的几率仍然高度依赖于Oracle共享池内存的数量和应用程序。下面是一个实际的4031错误消息

ORA-04031: unable to allocate 4192 bytes of shared memory ("shared
pool","SELECT * F...","sql area (6,0)","kafco :
qkacol"):4031:375:2008:ocicon.c

上面的信息显示,4KB内存正尝试在子池6中进行分配,但是由于某些原因,不能完成分配。幸运地是有一些方法来减少收到4031错误的机会。

清空共享池
与解决共享池latch争用一样,4031错误的一个解决方案也是清空共享池。虽然没有DBA愿意承认定期清空共享池,但这仍然有效。根据Oracle版本、分配的共享池内存的数量以及应用程序独特的内存使用模式,这可能是您的最佳选择。对于oracle 9i之前的系统尤其如此。

增加共享池大小
从概念上讲,增加共享池内存为Oracle提供了更大的灵活性来满足内存请求。然而,除了好处之外,在转移计算资源时也总是有成本的。在大多数情况下,收益实际上大于成本,因此,如果操作系统有可用内存,或者可以将内存从其他Oracle缓存转移到共享池,增加共享池内存很可能减少4031个错误。

请记住,每当您要求Oracle管理更多内存时,都需要更多的CPU来管理这些内存。在oracle9i之前的系统中尤其如此,因为可能存在非常长的内存链堆。如果链有数千个块长,而4031个错误可能会消失,那么在试图获取共享池latch和扫描长链时,这种情况可能会表现为严重的共享池latch争用和大量CPU消耗——所以要小心。

如Oracle文档所述,如果您通过自动内存管理获得解放,您可能需要设置最小的共享池大小。在增加缓冲区缓存的过程中,Oracle会自动减少共享池的大小,以至于开始出现4031个错误。

增加共享池保留大小
当一个较大的包最初被加载到一个已经非常活跃的共享池中时,就会出现一个常见的内存分配挑战。共享池越活跃,特别是当它很小并且对象大小非常不同时,就越有可能找不到所需的内存。

假设我们的服务器进程需要内存来存储一个大游标。当Oracle搜索共享池内存时,如果对象大小大于阈值,Oracle首先搜索保留区域。如果在保留区域中没有找到内存,Oracle将到非保留区域搜索。这种策略有助于将较小的对象排除在保留区域之外,从而将其保留给较大的对象。

有三个实例参数可以组合使用:
.shared_pool_reserved_size被用来直接设置共享池保留大小以字节为单位
.隐藏参数_shared_pool_reserved_pct,它的缺省值为5(5%),可以被用来代替shared_pool_reserved_size。
.一个相对大的对象是由实例参数_shared_pool_reserved_min_alloc来定义的,它的缺省值为4400字节。有趣的是缺省值4400字节仅仅比常见的单个块请求4096字节大。因此,在默认情况下,Oracle表示任何大于一个典型大小的内存块请求都被认为是大的,因此应该从保留的大小中获得内存。

前两个参数中的任何一个都可以用来为相对较大的对象设置共享池保留内存大小。如果您设置其中一个参数,Oracle将计算另一个参数。通过仔细调整这些参数,性能分析人员可以增加进程找到大量内存的可能性,同时仍然维护大量内存给相对较小对象使用。虽然这些参数通常不会调整,但如果发生4031错误,它们的小心调整可能会修复问题。

最小化游标固定时间
当执行游标时,游标也被固定。毕竟,您不希望SQL语句在执行期间突然消失!这是好消息。潜在的坏消息是,当执行完成时,固定游标被释放。如果没有其他进程固定游标,Oracle可以随意销毁,即释放关联的内存。现在假设有人想重新执行游标。如果它已被释放,将执行硬解析,因为整个游标将被重建!每个应用程序使用模式都是独特的;因此,当与更小的共享池或许多独特的SQL语句(或者两者都有)结合使用时,内存管理和库缓存活动可能会变得异常紧张。减少硬解析的一种方法是固定游标,使它们不能被释放。

Oracle提供了一个特殊的实例参数,该参数将保持所有会话的所有游标固定到关闭游标为止。但是,这种好处是以增加共享池内存消耗为代价的,因此,增加了接收4031错误的可能性。Oracle非常清楚这一点,所以为了鼓励回收释放内存并降低发生4031个错误的可能性,cursor_space_for_time实例参数默认设置为false。

如果系统正经历4031错误,你应该要检查cursor_space_for_time参数值。如果你的系统在过去某个时间点已经经历了严重的共享池latch急用,那么可以理解有人将cursor_space_for_time设置为true了。虽然你可能不会决定设置cursor_space_for_time参数为false。但这是一个有效选项应该被考虑。

减小保留对象的内存消耗
如果有太多对象通过执行dbms_shared_pool.keep过程被强制保留在共享池中,它们可能会消耗大量的内存Oracle可能无法成功地管理剩下的内存。此外,如果没有将大型对象保存在共享池中,则实例已经运行了一段时间,然后引用该对象,当强制加载该对象时,内存可能不可用。关键是不要随意地将对象保存在共享池中。

升级数据库版本到10gr2
当然,4031个错误不是升级的惟一原因,而是从Oracle数据库10gr2开始将内存标准化为4KB块。虽然我永远不会仅仅因为这个改进就建议升级到这个版本,但是这可能是升级的一部分原因。

就像对段区大小进行标准化一样,拥有标准的内存块大小可以提高快速找到合适内存的可能性。可以找到的内存越快,消耗的CPU周期就越少,必须持有共享池latch的时间就越短,存在大量浪费的小内存块的可能性就越小(增加4031错误的可能性)。

Oracle Library cache

Library cache
在提出好的库缓存性能解决方案之前,您需要对库缓存的体系结构有一个充分的了解。有了这些知识,以及前几章介绍的背景知识,您就能够理解为什么一个解决方案是有意义的,以及它可能产生的影响。

Library Cache架构
library cache架构是非常复杂的。并发性、对象之间的关系和快速搜索的组合需求确实会对体系结构造成压力。我将从这个体系结构的一个非常概念性的层次开始,然后有条不紊地深入到越来越多的细节。当细节对性能救火没有特别帮助时,我将停止。

一个很好的library cache模型是传统library。假设你想找雷·布拉德伯里的书《蒲公英酒》。因为图书馆是一个巨大的图书仓库(想想所有的缓存对象),顺序或随机搜索都是徒劳的。因此,您进入卡片目录区域(哈希结构),并直接访问包含以字母A到D开头的作者的书籍的卡片目录(考虑哈希到一个特定的桶)。在你前面有人排队,因此你必须等待(这就好比获取相关哈希桶的latch一样)。最后你站在了适当的卡片目录前面(就好像你获得了latch)并开始序列化搜索图书(就好像序列化搜索一个哈希chain)。最终你找到了卡片并看到了图书的地址为813.54(就好像library cache handle)。你走到图书应该存放的位置,找到它并开始阅读(就好像访问游标一样)。如果您能够在脑海中描绘这个故事,那么您就已经很好地理解了Oracle的库缓存。

Library Cache Conceptual Model
与buffer cache一样,library cache对象使用一种哈希结构来进行定位。这将调用哈希函数,桶,链表与latches或mutexes。一个关键的不同点是哈希链节点不是由buffer headers组成的,而是称为句柄(handles)的简单指针节点。

句柄是内存地址指针的常用术语,这就是library cache的情况。在一个handle与一个library cache内存对象之间是一对一的关系。所以引用句柄与引用它的关联对象是同义词操作。当mutexes被用来替代library cache latches时,每个单独的handle都有一个相关的mutex。每个library cache对象引用一个特定类型的对象,有时叫命名空间,比如一个游标,一个子游标,一个表或一个程序结构。

下图抽象了library cache对mutexes的实现以及突显了各种架构组件但没有指定对象名称。Library cache对象使用一种哈希结构来进行搜索,因此可以看到桶,比如bucket BKT200。当实同mutexes时,对于每个handle都有一个相关的mutex,因此每个内存chunk有一个相关的mutex。每个哈希链可能包含零个或多个handles,它与零个或多个library cache对象相关,比如游标 CSR500与表TBL 400。每个父游标将至少有一个子游标。一个父游标比如CSR 500可以与多个子游标,比如CCSR 600和CCSR 610相关联。

一种关键的library cache特点就是对象关系。在上图中,注意表TBL 400与三个子游标CCSR 600,CCSR 610和CCSR 620相关联,如果表TBL 400被修改,Oracle知道那些library cache对象将会失效。例如,如果表TBL 400被修改了并且Oracle认为这种修改非常严重足以使用library cache条目失效,然后所有相关的library cache对象也将失效。当然,必须维护序列化,这样您就可以看到,即使是相对较小的库缓存也会变得非常紧张。

使用mutexes代替latches的影响。因此一个mutex与每个library cache对象相关联,因此不会使整个哈希链不可用,从而显著减少了错误争用和获取CPU消耗,从而提高了响应时间。

Library Cache Object References
现在,让我们将概念模型提升到更实际的层次,以阐明库缓存对象关系。

SQL> oradebug setmypid
Statement processed.
SQL> alter session set MAX_DUMP_FILE_SIZE=unlimited;

Session altered.

SQL> create table findme as select * from dual;

Table created.

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> select * from findme;

D
-
X

SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select * from findme;

D
-
X

SQL> select dummy from findme;

D
-
X

SQL> alter session set events 'immediate trace name library_cache level 10';

Session altered.

SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jy/jy1/trace/jy1_ora_13777.trc

从跟踪文件中可以看到Bucket 12771与一个对象相关联,表findme的handle为0x8501f820,Bucket 14778相关的handle,mutex,名称和两个子游标。

Bucket: #=12771 Mutex=0xc5b46150(3298534883328, 31, 0, 6)
  LibraryHandle:  Address=0x8501f820 Hash=7c1631e3 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.SYS.FINDME
      FullHashValue=29918f78d6b184afaf81fd2b7c1631e3 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) ContainerId=1 ContainerUid=1 Identifier=246951 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=9 TotalPinCount=9
    Counters:  BrokenCount=2 RevocablePointer=2 KeepDependency=0 Version=0 BucketInUse=6 HandleInUse=6 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x8501f8d0(0, 4, 0, 0) Mutex=0x8501f970(768, 96, 0, 6)
    Flags=PIN/TIM/[00002801] Flags2=[0000]
    WaitersLists:
      Lock=0x8501f8b0[0x8501f8b0,0x8501f8b0]
      Pin=0x8501f890[0x8501f890,0x8501f890]
      LoadLock=0x8501f908[0x8501f908,0x8501f908]
    Timestamp:  Current=04-23-2019 09:19:22
    HandleReference:  Address=0x8501fa18 Handle=0xcff01220 Flags=OWN[200]
    LockInstance:  id='LB29918f78d6b184af' GlobalEnqueue=(nil) ReleaseCount=0
    PinInstance:  id='NB29918f78d6b184af' GlobalEnqueue=(nil)
    ReferenceList:
      Reference:  Address=0x7f4fa5f0 Handle=0x7f72fd58 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0xbcc24930 Handle=0xd6d3f620 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0xddfa2a28 Handle=0x7fb24eb8 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
      Reference:  Address=0x800e41e8 Handle=0x83a9c4e8 Flags=DEP[01]
        Timestamp=04-23-2019 09:19:22 InvalidatedFrom=0
    LibraryObject:  Address=0xdf389690 HeapMask=0000-0701-0001-0000 Flags=EXS/LOC[0004] Flags2=[8000000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^7c1631e3 pins=0 Change=NONE
          Heap=0xb387c0e8 Pointer=0xdf389760 Extent=0xdf3895e8 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=1.304688 Size=3.976562 LoadTime=4629524905
        Block:  #='8' name=KGLS^7c1631e3 pins=0 Change=NONE
          Heap=0xdf389b18 Pointer=0xdb10a550 Extent=0xdb109870 Flags=I/-/-/A/-/-/-
          FreedLocation=0 Alloc=1.125000 Size=3.976562 LoadTime=0






Bucket: #=14778 Mutex=0xc5b59ae8(3298534883328, 43, 0, 6)
  LibraryHandle:  Address=0x818b62f8 Hash=ebf439ba LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select * from findme
      FullHashValue=57c14570e98dc8b98fe8a5a2ebf439ba Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=3958651322 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=2 Version=0 BucketInUse=1 HandleInUse=1 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x818b63a8(0, 2, 0, 0) Mutex=0x818b6448(768, 37, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0x818b6388[0x818b6388,0x818b6388]
      Pin=0x818b6368[0x818b6368,0x818b6368]
      LoadLock=0x818b63e0[0x818b63e0,0x818b63e0]
    Timestamp:  Current=04-23-2019 09:19:37
    HandleReference:  Address=0x818b64d0 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x8031bbf0 Handle=0x8054ae68 Flags=ROD[21]
      Reference:  Address=0x80f11e30 Handle=0xdd9f6df8 Flags=ROD[21]
    LibraryObject:  Address=0x84edddb0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^ebf439ba pins=0 Change=NONE
          Heap=0xd2f65218 Pointer=0x84edde80 Extent=0x84eddd08 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=3.390625 Size=3.976562 LoadTime=4629539589
      ChildTable:  size='16'
        Child:  id='0' Table=0x84edec30 Reference=0x84ede700 Handle=0x83a9c4e8
        Child:  id='1' Table=0x84edec30 Reference=0x84edea50 Handle=0xd6d3f620
    NamespaceDump:
      Parent Cursor:  sql_id=8zu55nbpz8fdu parent=0x84edde80 maxchild=2 plk=n ppn=n prsfcnt=0 obscnt=0
        CursorDiagnosticsNodes:
          ChildNode:  ChildNumber=0 ID=3 reason=Optimizer mismatch(10) size=3x4 optimizer_mode_hinted_cursor=0 optimizer_mode_cursor=1 optimizer_mode_current=2



Bucket: #=67700 Mutex=0xc5d5e7f8(3298534883328, 125, 0, 6)
  LibraryHandle:  Address=0xbd8f26d0 Hash=93850874 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=select dummy from findme
      FullHashValue=70b1c44268eb8c9d2860b06f93850874 Namespace=SQL AREA(00) Type=CURSOR(00) ContainerId=1 ContainerUid=1 Identifier=2474969204 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xbd8f2780(0, 1, 0, 0) Mutex=0xbd8f2820(768, 23, 0, 6)
    Flags=RON/PIN/TIM/PN0/DBN/[10012841] Flags2=[0000]
    WaitersLists:
      Lock=0xbd8f2760[0xbd8f2760,0xbd8f2760]
      Pin=0xbd8f2740[0xbd8f2740,0xbd8f2740]
      LoadLock=0xbd8f27b8[0xbd8f27b8,0xbd8f27b8]
    Timestamp:  Current=04-23-2019 09:20:01
    HandleReference:  Address=0xbd8f28b0 Handle=(nil) Flags=[00]
    ReferenceList:
      Reference:  Address=0x8574abf8 Handle=0x7f4cc5c0 Flags=ROD[21]
    LibraryObject:  Address=0x86991c70 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      DataBlocks:
        Block:  #='0' name=KGLH0^93850874 pins=0 Change=NONE
          Heap=0xbc1721d8 Pointer=0x86991d40 Extent=0x86991bc8 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=2.546875 Size=3.976562 LoadTime=4629563404
      ChildTable:  size='16'
        Child:  id='0' Table=0x86992af0 Reference=0x869925c0 Handle=0x7f72fd58
    NamespaceDump:
      Parent Cursor:  sql_id=2hs5hdy9sa23n parent=0x86991d40 maxchild=1 plk=n ppn=n prsfcnt=0 obscnt=0

从下面的内容可以看出子游标之间的关系。

Bucket: #=103006 Mutex=0xc5eb7488(3298534883328, 304, 0, 6)
  LibraryHandle:  Address=0xdd9f6df8 Hash=2ab9925e LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.57c14570e98dc8b98fe8a5a2ebf439ba Child:0
      FullHashValue=2ccbd3fc5f92a1798e3cc3a22ab9925e Namespace=SQL AREA STATS(75) Type=CURSOR STATS(102) ContainerId=1 ContainerUid=1 Identifier=716804702 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0xdd9f6ea8(0, 1, 0, 0) Mutex=0xdd9f6f48(768, 9, 0, 6)
    Flags=RON/PIN/TIM/KEP/KPR/[00012805] Flags2=[0000]
    WaitersLists:
      Lock=0xdd9f6e88[0xdd9f6e88,0xdd9f6e88]
      Pin=0xdd9f6e68[0xdd9f6e68,0xdd9f6e68]
      LoadLock=0xdd9f6ee0[0xdd9f6ee0,0xdd9f6ee0]
    Timestamp:  Current=04-23-2019 09:19:37
    ReferenceList:
      Reference:  Address=0x800e40e0 Handle=0x83a9c4e8 Flags=ROD/KPP[61]
    LibraryObject:  Address=0x80f119f0 HeapMask=0001-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      ReadOnlyDependencies:  count='1' size='16'
        ReadDependency:  num='0' Table=0x80f12898 Reference=0x80f11e30 Handle=0x818b62f8 HandleFlag=0x10012841 RefFlags=DEP/ROD[21]
      DataBlocks:
        Block:  #='0' name=KGLH0^2ab9925e pins=0 Change=NONE
          Heap=0xd2468460 Pointer=0x80f11ac0 Extent=0x80f11948 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.265625 Size=3.976562 LoadTime=4629539590
    NamespaceDump:
      STATS:  phd=0x818b62f8 chd=0x83a9c4e8 planhsh=5111da46 flg=1 Parse Count=1 Disk Reads=2 Disk Writes (Direct)=0 Disk Reads (Direct)=0 Physical read requests=2 Physical read bytes=16384 Physical write requests=0 Physical write bytes=0 IO Interconnect bytes=16384 Buffer Gets=27 Rows Processed=1 Serializable Aborts=0 Fetches=2 Execution count=1 PX Server Execution Count=0 Full Execution Count=1 CPU time=15000 Elapsed time=433961 Avg Hard Parse Time=420034 Application time=0 Concurrency time=985 Cluster/RAC time=496 User I/O time=407471 Plsql Interpretor time=0 JVM time=0 Sorts=0


Bucket: #=128596 Mutex=0xc5fb12f8(3298534883328, 137, 0, 6)
  LibraryHandle:  Address=0x8054ae68 Hash=efe9f654 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
    ObjectName:  Name=CDB$ROOT.57c14570e98dc8b98fe8a5a2ebf439ba Child:1
      FullHashValue=35e6477c4d445fa62356ff83efe9f654 Namespace=SQL AREA STATS(75) Type=CURSOR STATS(102) ContainerId=1 ContainerUid=1 Identifier=4025087572 OwnerIdn=0
    Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
    Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
    Concurrency:  DependencyMutex=0x8054af18(0, 1, 0, 0) Mutex=0x8054afb8(768, 9, 0, 6)
    Flags=RON/PIN/TIM/KEP/KPR/[00012805] Flags2=[0000]
    WaitersLists:
      Lock=0x8054aef8[0x8054aef8,0x8054aef8]
      Pin=0x8054aed8[0x8054aed8,0x8054aed8]
      LoadLock=0x8054af50[0x8054af50,0x8054af50]
    Timestamp:  Current=04-23-2019 09:19:53
    ReferenceList:
      Reference:  Address=0xbcc24828 Handle=0xd6d3f620 Flags=ROD/KPP[61]
    LibraryObject:  Address=0x8031b7b0 HeapMask=0001-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
      ReadOnlyDependencies:  count='1' size='16'
        ReadDependency:  num='0' Table=0x8031c658 Reference=0x8031bbf0 Handle=0x818b62f8 HandleFlag=0x10012841 RefFlags=DEP/ROD[21]
      DataBlocks:
        Block:  #='0' name=KGLH0^efe9f654 pins=0 Change=NONE
          Heap=0xd26770b8 Pointer=0x8031b880 Extent=0x8031b708 Flags=I/-/P/A/-/-/-
          FreedLocation=0 Alloc=1.265625 Size=3.976562 LoadTime=4629556046
    NamespaceDump:
      STATS:  phd=0x818b62f8 chd=0xd6d3f620 planhsh=5111da46 flg=1 Parse Count=1 Disk Reads=0 Disk Writes (Direct)=0 Disk Reads (Direct)=0 Physical read requests=0 Physical read bytes=0 Physical write requests=0 Physical write bytes=0 IO Interconnect bytes=0 Buffer Gets=22 Rows Processed=1 Serializable Aborts=0 Fetches=2 Execution count=1 PX Server Execution Count=0 Full Execution Count=1 CPU time=7000 Elapsed time=6158 Avg Hard Parse Time=5220 Application time=0 Concurrency time=0 Cluster/RAC time=0 User I/O time=0 Plsql Interpretor time=0 JVM time=0 Sorts=0

Keeping Cursor in the Cache
构建一个游标是相对昂贵的操作。CPU消耗和将对象放入库缓存的IO可能会显著降低性能。这通常表现为解析CPU消耗的增加,特别是库缓存latch或互斥锁成为最主要的等待事件。因此,一个明显的目标是将游标保存在库缓存中。但是,必须保持平衡,否则会出现其他性能限制问题。共享池必须包含许多类型的对象,而库缓存对象只是这些类型之一。另外,内存是有限的资源。下面的小节将讨论影响Oracle在缓存中保存游标的各种方法。

Increase the Likelihood of Caching
Oracle无法释放打开的游标。即使共享池被刷新,打开的游标也被固定,因此无法释放。通常,当游标执行完成时,将关闭游标,游标固定被删除,如果没有其他会话固定游标,Oracle可以释放关联的内存。这允许新的和活动的游标保留在内存中,而较不活动的游标则自然释放。但是,如果解析成为一个重要的性能问题,作为性能分析人员,我们就会有动机影响Oracle将游标保存在内存中,一种方式是保持游标为打开状态。

Oracle允许我们保持游标比通常打开的时间更长。实例参数cursor_space_for_time当设置为true(缺省值为false)时,将所有游标固定,直到它们被特别关闭。即使在游标执行完成之后,Oracle也会保持游标固定,直到游标关闭为止。

但是,与所有调优更改一样,也有一个权衡。此实例参数影响整个Oracle实例中的所有游标。此外,它不是特定于会话的,参数更改需要实例重启才能生效。真正的含义是,现在需要更多共享池内存来缓存库缓存对象。实际上,这种影响可能非常显著,以至于共享池可能会有效地耗尽内存,从而导致可怕的4031“共享池内存耗尽”错误。所以在设置这个参数时必须小心。

就我个人而言,除非存在明显的解析问题(至少三种情况中的两种),否则我不会启用此选项:CPU消耗由解析时间和共享池latch争用或库缓存latch争用或互斥锁争用控制。相反,如果出现“out of shared pool memory”错误,请确保cursor_space_for_time被设置为false。

Force Caching
大多数dba都知道,确保大型包成功加载到共享池的一种方法是使用dbms_shared_pool.keep过程。当实例启动后立即将关键包加载到内存中时,收到“out of shared pool memory”错误的几率将显著降低。尤其是在早期版本的Oracle中,特别是在Oracle 8i中,这可以显著降低耗尽共享池内存的可能性。

下面是一个基于v$db_object_cache视图的OSM报告并且显示了在Oracle实例启动后被初始加载的对象。注意,生成报表时,共享池中没有强制保存符合报表选择标准的对象。

SQL> @dboc 10 20
old   9: where  a.sharable_mem >= &min_size
new   9: where  a.sharable_mem >= 20
old  10:   and  a.executions >= &min_exec
new  10:   and  a.executions >= 10

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         1
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_STATS_INTERNAL                 PBDY     0   386   492 NO
SYS          PLITBLM                             PKG      0   166     8 NO
SYS          DBMS_ASSERT                         PBDY     0    49    16 NO
SYS          STANDARD                            PBDY     0    27    32 NO
SYS          DBMS_STATS_INTERNAL                 PKG      0    24   622 NO
SYS          DBMS_SQLDIAG_INTERNAL               PKG      0    18    12 NO
SYS          DBMS_LOB                            PBDY     0    15    32 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         2
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLDIAG                        PBDY     0     8    40 NO
SYS          DBMS_SQL                            PBDY     0     3    74 NO
SYS          DBMS_STANDARD                       PKG      0     1    48 NO
SYS          DBMS_STATS                          PBDY     0     1  1213 NO
SYS          DBMS_SQLTUNE_UTIL0                  PBDY     0     1    16 NO
SYS          DBMS_STATS_ADVISOR                  PKG      0     0    24 NO
SYS          DBMS_SPACE_ADMIN                    PBDY     0     0    44 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         3
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DICTIONARY_OBJ_NAME                 FNC      0     0     8 NO
SYS          DICTIONARY_OBJ_OWNER                FNC      0     0     8 NO
SYS          DBMS_UTILITY                        PKG      0     0    12 NO
SYS          DBMS_UTILITY                        PBDY     0     0    57 NO
SYS          DBMS_APPLICATION_INFO               PBDY     0     0     8 NO
SYS          IS_VPD_ENABLED                      FNC      0     0     8 NO
SYS          DBMS_SPACE_ADMIN                    PKG      0     0    60 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         4
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLTUNE_INTERNAL               PBDY     0     0   532 NO
SYS          DBMS_AUTO_TASK                      PKG      0     0     8 NO
SYS          DICTIONARY_OBJ_TYPE                 FNC      0     0     8 NO
SYS          PRVT_ADVISOR                        PBDY     0     0   176 NO
SYS          AW_TRUNC_PROC                       PRC      0     0     8 NO
SYS          DBMS_ADVISOR                        PBDY     0     0    69 NO
SYS          DBMS_SQLTUNE_UTIL2                  PBDY     0     0    20 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         5
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SQLTUNE_UTIL1                  PBDY     0     0    57 NO
SYS          DBMS_OUTPUT                         PBDY     0     0    12 NO
SYS          DBMS_STATS                          PKG      0     0   252 NO
SYS          DBMS_PRIV_CAPTURE                   PBDY     0     0    12 NO
SYS          DBMS_SPACE                          PKG      0     0    20 NO
SYS          AW_DROP_PROC                        PRC      0     0    12 NO
SYS          DBMS_ISCHED                         PBDY     0     0   387 NO

DB/Inst: jy/jy1                                                   24-Apr 09:37am
Report:   dboc.sql             OSM by OraPub, Inc.                Page         6
                      Oracle Database Object Cache Summary

                                                 Obj          Exe  Size
Owner        Obj Name                            Type Loads   (k)  (KB) Kept?
------------ ----------------------------------- ---- ----- ----- ----- -----
SYS          DBMS_SESSION                        PBDY     0     0    20 NO

36 rows selected.

SQL> l
  1  select a.owner ownerx,
  2         a.name  namex,
  3         decode(a.type,'PACKAGE','PKG','PACKAGE BODY','PBDY','FUNCTION','FNC','PROCEDURE','PRC') typex,
  4         a.loads/1000 loadsx,
  5         a.executions/1000 execsx,
  6         a.sharable_mem/1024 sizex,
  7         a.kept keptx
  8  from   v$db_object_cache a
  9  where  a.sharable_mem >= &min_size
 10    and  a.executions >= &min_exec
 11    and  a.type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
 12* order by executions desc, sharable_mem desc, name
SQL>

当强制对象保存在共享池中时,请注意,Oracle最近使用最少的(LRU)共享池内存管理算法的影响。我们说的是我们比Oracle更了解。实际上可能就是这样,因为大多数dba都非常了解他们的应用程序。但是,在您这样做之前,将共享池装满诸如圣诞袜之类的包实际上会增加内存溢出错误的可能性,因为留给数百个(如果不是数千个)其他共享池对象的空间很小。所以,在使用这个程序之前要仔细考虑。

Private Cursor Caches
问题是:由于库缓存在所有会话之间共享,因此必须运行某种类型的序列化控制机制。无论机制是latches还是mutexes,这意味着获取控制结构与访问内存结构都是要消耗CPU资源的。如果访问变得紧张,可能触发大量的竞争,导致严重的性能下降。因此,就会问一个看似愚蠢但又合乎逻辑的问题:“我们能不能简单地不使用控制结构?”。

当然可以,如果序列化不是问题的话。Oracle所做的是通过为每个会话提供自己的私有库缓存结构来降低需要序列化库缓存访问的可能性,该结构只包含会话的常用游标(实际上只是指向游标的指针,这是它们的句柄)。因为游标缓存是私有的,序列化被保证,因此不需要控制结构!这确实是一个优雅的解决方案

这种私有库缓存结构也叫作会话游标缓存,缺省情况下,每个会话有一个游标缓存包含指向常用游标的指针。缺省情况下,Oracle 10gr2缓存20个游标指针。Oracle 11gr1是50个游标指针。不管缺省值是多少,缓存大小可以在系统级(不是会话级)通过修改session_cached_cursors实例参数来进行修改。

其过程如下:当运行一个SQL语句时,会话创建语句的哈值,然后检查句柄是否存在于自己的游标缓存中。因为没有其它进程能访问会话的游标缓存,不需要请求控制结构。如果句柄被找到,会话知道游标存在于缓存中。如果游标没有在会话游标缓存中找到,哈希值将被哈希到一个库缓存哈希桶中,获得合适的控制结构,然后序列化扫描链表,查找游标。如果句柄在会话的游标缓存中找到,虽然花费了一些精力进行解析,但它与硬解析是不一样的(语句没有在库缓存中找到)或者甚至与软解析也不一样(语句在库缓存中找到),因此术语软软解析(softer parse)用来描述这种方
法。

好消息就是库缓存(library cache)竞争可以通过增加每个会话的游标缓存来显著减少。坏消息是每个会话的游标缓存确实增加了。如果Oracle实例有几百个会话,所有会话游标缓存可能请求大量的内存进行导致共享池内存可用性的问题。当做得太过火时就会知道,因为将收到4031“out of memory”错误。在这时可以减小会话缓存大小或者如果有内存可用,增加共享池大小。因此,与几乎所有调优工作和参数一样,都要付出代价。作为性能分析师,我们希望成本小于性能收益。

Library Cache Latch/Mutex Contention Identification and Resolution
随着库缓存变得越来越活跃,对控制结构和控制结构占用时间的竞争可能会增加很多,从而成为一个严重的性能问题。当这种情况发生时,它将变得很明显,因为我们的响应时间分析将清楚地指向库缓存latch或与互斥锁相关的等待事件。此外,Oracle的CPU消耗将非常大,递归SQL或解析相关的时间将非常之多。操作系统将经受CPU瓶颈。幸运的是,有几个非常好的解决方案可以解决这个问题。

下面的脚本输出结果中可以看到几个library cache latch竞争,几乎100%的latch竞争是与library cache相关的。

SQL> @swpctx
Remember: This report must be run twice so both the initial and
final values are available. If no output, press ENTER twice.

DB/Inst: RLZY/RLZY1                                               26-Apr 08:52am
Report:   swpctx.sql           OSM by OraPub, Inc.                Page         1
            System Event CHANGE (5 sec interval) Activity By PERCENT

                                       Time Waited  % Time    Avg Time     Wait
Wait Event Display Name                      (sec)  Waited Waited (ms) Count(k)
-------------------------------------- ----------- ------- ----------- --------
latch: library cache                         3.580    55.50       41.1        0
latch: library cache pin                     2.830    43.88       23.2        0
control file parallel write                  0.030     0.47        1.5        0
direct path write                            0.000     0.00        0.0        0
log file sync                                0.000     0.00        0.0        0
log file parallel write                      0.000     0.00        0.0        0
db file sequential read                      0.000     0.00        0.0        0

启用Mutexes
下面的脚本输出结果与之前的唯一差别是通过设置实例参数_kks_use_mutex_pin为true(缺省值为true)来启用了library cache mutexes。注意top等待事件是cursor: pin S。结果就是游标正被重复地密集地打开与关闭。尽管在启用与禁用mutexes时递归SQL的百分比是相同的,但当使用latches时,总CPU消耗几乎是使用mutexes时的两倍。

SQL> @swpctx
Remember: This report must be run twice so both the initial and
final values are available. If no output, press ENTER twice.

DB/Inst: RLZY/RLZY1                                               26-Apr 08:54am
Report:   swpctx.sql           OSM by OraPub, Inc.                Page         1
            System Event CHANGE (5 sec interval) Activity By PERCENT

                                       Time Waited  % Time    Avg Time     Wait
Wait Event Display Name                      (sec)  Waited Waited (ms) Count(k)
-------------------------------------- ----------- ------- ----------- --------
cursor: pin S                                2.630   94.27        47.0        0
control file parallel write                  0.030    1.08         1.5        0
direct path write                            0.000    0.00         0.0        0
db file sequential read                      0.000    0.00         0.0        0
log file parallel write                      0.000    0.00         0.0        0
log file sync                                0.000    0.00         0.0        0


使用绑定变量来创建类似SQL
Oracle对于它认为的类似SQL语句是非常讲究的。每个语句必须被解析,并且如果游标在library cache中没有找到,游标必须被完全构建(硬解析)。硬解析需要使用与库缓存相关的latches与锁,因此,如果硬解析变得如此强烈,相关的等待事件将被出现在报告的顶部,我们将寻找创建类似SQL语句的方法。Oracle提供了两种强大的方法来实现这一点。

第一方法是简单使用绑定变量来代替文本字。例如,语句select * from employee where emp_no=100使用文本值。如果语句select * from employee where emp_no=200被执行,因为Oracle的哈希算法,两个语句有不同的哈希值,将存放在不同的哈希桶中,并且有不同的handle。正如你所想的一样,当有密集的联机事务活动时,这将导致大量的硬解析。如果应用程序开发者可以提交这样的语句select * from employee where emp_no=:b1,使用绑定变量,游标将不会包含雇员号,并且游标可以高度重用(因为没有雇员号,相同的游标可以被重用)。这将显著减少硬解析。查看语句是否使用绑定变量非常简单。查看Oracle所存储的SQL,在v$sqltext中。如果使用绑定变量,您将看到它们。发现几个library cache相关的竞争可能导致你认识到绑定变量没有使用。应用程序开发者将非常不高兴,因为这需要大量的返工。

使用游标共享
另一种快速实现使用绑定变量的方法是让Oracle自动转换SQL语句。Oracle将有效地将没有使用绑定变量的SQL语句转换为使用绑定变量的语句。如果看到类似下面的语句就是Oracle自动转换的使用绑定变量的SQL:

select count(*)
from customers
where status != :"SYS_B_0"
and org_id != :"SYS_B_1"

如果您非常了解应用程序SQL,那么您可能会意识到这个确切的SQL实际上并不存在于应用程序的任何地方。实际上,如果您检查了应用程序提交给Oracle的SQL,它可能是这样的。

select count(*)
from customers
where status != 'ACTIVE'
and org_id != '15043'

结果就是你看到的Oracle自动转换SQL了使用它变得更容易共享。Oracle叫这个功能为cursor sharing(游标共享)。相关的实例参数为cursor_sharing,它有三个选项并且可以在会话级与系统级进行修改。当使用exact时,不会出现自动转换。当使用similar时,Oracle将寻找绑定变量并进行自动转换。当使用force时,Oracle会自动转换任何与每个文本值为绑定变量。

如果您向一组性能分析人员询问他们在游标共享方面的经验,您将立即得到一个看似矛盾而又充满激情的讨论。有些人,像我自己在使用similar选项时有美好的经历,其它人有各种各样的问题。有些人使用force选项后看到他们的SQL语句发生了巨大的变化并且SQL语句的结果集也不一样了。例如,原来返回10行记录的,现在只返回2行记录,有效的破坏了应用程序。

显然,您需要与您的同事交谈,与Oracle support进行检查,并测试特定环境中的各种选项。如果物理上无法更改SQL以使用绑定变量,或者非常痛苦,那么游标共享可以非常有效地工作。但是在生产环境中使用该选项之前,您必须非常勤奋地进行严格的测试。

利用哈希结构
从搜索角度来说,library cache是采用哈希结构来构建的。因此就像buffer cache chains一样,我们可以修改哈希桶的数量和latches的数量。当使用mutexes时,Oracle设置mutex内存结构关系。例如,每个library cache对象有属于自己的mutex。

根据Oracle版本的不同,Oracle实际上可能不会透露库缓存桶或锁存器的数量。例如Oracle 10gr2可能显示的library cache buckets的数量为9,library cache latches的数量为零。

Oracle允许通过实例参数_kgl_bucket_count来查看哈希桶的数量。library cache latches的数量是由实例参数_kgl_latch_count来控制的。现实中没有一个人通过增加生产系统中library cache哈希桶的数量并成功减少library cache latch竞争的。然而,就像cache buffer chain latches一样,library cache latch竞争可以通过增加library cache latches的数量来减少。

Try Mutex-Focused Solutions
当mutexes可用时,启用它们。可以通过将实例参数_kks_use_mutex_pin设置为false来禁用mutexes。如果你的系统正在遭受严重的mutex(互斥锁)问题,Oracle技术支持工程师可能会建议你关闭mutexes直到应用补丁为止。

大多数Oracle站点永远不会发生mutex(互斥)争用,如果发生mutex争用,那么压力可能与将游标固定在共享或独占模式有关。

有趣的是,要让互斥锁运行,操作系统必须支持比较和交换(CAS)操作。减少指令集计算机(RISC)操作系统,比如AIX或PA-RISC,可能选择通过消除比较和交换(CAS)操作来减少它们的指令集。在这种情况下,Oracle将通过使用一个latches池(在oracle 11gr1中缺省是有1024)池来模拟比较和交换(CAS)操作。latches被命名为KGX,并且可以通过修改实例参数_kgx_latches来改变它的数量,显然,这对于性能来说不是最优的,但是我们希望最终的结果是有益的。

SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
  2  from x$ksppi x, x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%_kgx_latch%';

NAME              VALUE   DESCRIB
----------------- ------- -------------------------------------------------
_kgx_latches      1024    # of mutex latches if CAS is not supported.

实际上有许多与mutex(互斥)锁相关的等待事件。虽然我希望所有与mutex(互斥)锁相关的等待事件都是以mutex(互斥)锁开始,但是Oracle采取了不同的方法。与库缓存关联的mutex(互斥)对象都以单词cursor开头。这是有意义的,因为库缓存中充满了游标,但是它使性能分析人员更难发现新的mutex(互斥)对象的使用情况。

Mutex等待事件如下:
cursor:mutex X
当一个会话以排他模式请求一个mutex时,通过spinning不能获得因此进入休眠时将会post这个等待事件。只需要一个会话以共享模式持有mutex(互斥)锁,就可以防止排他性获取。构建一个子游标,捕获SQL绑定变量数据,构建或更新游标相关统计信息都需要以排他模式来请求mutex。

cursor: mutex S
当一个会话以共享模式请求一个mutex时,通过spinning不能获得因此进入休眠时将会post这个等待事件。多个会话可以以共享模式来持有一个mutex。如果一个mutex被另一个会话以排他模式所持有那么它将不能以共享模式被持有。当扫描引用计数时,一个会话以共享模式持有mutex,而不是排他模式。因此另外的会话可能正在更改引用计数。当出现这种情况时,mutex会被称为***221;正在变化中***221;。要看到这个事件是非常困难的,因为更改引用计数的速度非常快(有人告诉我,算法也建议这样做)。因此当多个会话以共享模式持有mutex时,更改引用计数实际上是一个串行操作。

cursor: pin S
当一个会话以共享模式请求pin(固定)一个游标时,通过spinning(自旋)不能完成因此而休眠时就会posts这个等待事件。多个会话可以以共享模式来pin(固定)一个相同的游标,但只能有一个会话以排他模式来持有一个mutex。Pinning将增加mutex的引用计数,这是一种序列化操作。因为一个会话必须pin(固定)一个游标才能执行游标(游标在执行期间不会被回收),当一个被频繁访问的游标被多个会话重复执行时可以在系统中看到这个等待事件。

cursor: pin X
当一个会话以排他模式请求pin(固定)一个游标时,通过spinning(自旋)不能完成因此而休眠时就会posts这个等待事件。只需要一个会话以共享模式固定mutex(互斥)锁,就可以防止排他性占有。当创建游标时必须以排他模式来固定。你不想在同一时刻其它的会庆创建或修改相同的游标。

cursor: pin S wait on X
当一个会话以共享模式来pin(固定)游标时,因为另外的会话以排他模式持有mutex而必须等待时就会posts这个等待事件。例如,如果一个会话只想简单地执行游标,它必须以共享模式来请求mutex。然而,当会话正在执行游标时,如果另外的会话正在构建或修改游标(请求以排他模式来固定),将会post这个等待事件。当多个会话想执行这个游标时而游标正在被重建(可能基表已经被修改了)时就会看到这个等待事件。

解决互斥锁相关争用的关键是同时理解等待事件和应用程序中正在发生的事情。例如,如果等待事件是cursor:pin S(最可能的),可能是相同的cursor被一些用户重复执行,几个游标被许多用户执行,甚至一个简单的SQL语句是由数百个用户并发执行。理解了这一点之后,您将寻找执行频率相对较高的SQL语句,并尽一切可能降低其执行频率。使用等待事件让你了解与游标相关的特殊情况,并了解应用程序的性质,这是最佳的解决方案路径。

同样,互斥锁等待不太可能是最重要的等待事件(当没有相关的互斥锁错误时),但它偶尔会发生。因此,理解互斥锁序列化控制以及库缓存内部结构和诊断是非常重要的。

Proudly powered by WordPress | Indrajeet by Sus Hill.