- 浏览: 4394631 次
- 性别:
- 来自: 厦门
文章分类
- 全部博客 (634)
- Oracle日常管理 (142)
- Oracle体系架构 (45)
- Oracle Tuning (52)
- Oracle故障诊断 (35)
- RAC/DG/OGG (64)
- Oracle11g New Features (48)
- DataWarehouse (15)
- SQL, PL/SQL (14)
- DB2日常管理 (9)
- Weblogic (11)
- Shell (19)
- AIX (12)
- Linux/Unix高可用性 (11)
- Linux/Unix日常管理 (66)
- Linux桌面应用 (37)
- Windows (2)
- 生活和工作 (13)
- 私人记事 (0)
- Python (9)
- CBO (15)
- Cognos (2)
- ORACLE 12c New Feature (2)
- PL/SQL (2)
- SQL (1)
- C++ (2)
- Hadoop大数据 (5)
- 机器学习 (3)
- 非技术 (1)
最新评论
-
di1984HIT:
xuexilee!!!
Oracle 11g R2 RAC高可用连接特性 – SCAN详解 -
aneyes123:
谢谢非常有用那
PL/SQL的存储过程和函数(原创) -
jcjcjc:
写的很详细
Oracle中Hint深入理解(原创) -
di1984HIT:
学习了,学习了
Linux NTP配置详解 (Network Time Protocol) -
avalonzst:
大写的赞..
AIX内存概述(原创)
This is the first part in a series of articles entitled "The right tool for the right job". In each article, I will use tools and ideas made available to us by oracle gurus like Tanel Poder, Jonathan Lewis, Tom Kyte, Kerry Osborne and many others. I will try to show you how to use those available free tools for troubleshooting real life problem I've encountered at client sites. You can use the scripts "as-is" (note that you should always test any script before using it on production systems) or modify them to suit your needs or even write your own based on ideas found on original scripts.
Some background first, database is 10.2.0.4 running in a SUN M9000 dynamic domain with 128Go of memory and 32 CPU (2520 MHz) allocated using fair share scheduler (FSS). We are experiencing high "latch: cache buffers chains" (CBC) contention issue. The problem occurs intermittently (several times a day) and also resolves by itself. Here is an overview of the issue with Quest Perfomance Analysis (as the client pays for it, let's use it to show a pretty graph ^_^) :
CBC latches are represented in pink color, I did the whole analysis during 10:00 and 10:15 AM, when the problem was currently happening.
What is a latch?
Latches are simple, low-level serialization mechanisms to protect shared SGA data structures and shared code segments (such as various internal linked list modifications, shared pool memory allocation, library cache object lookups and so on...) from simultaneous session access. They are designed to be very quickly acquired and freed. Latches are very low-level locks, inaccessible to users or applications as they cannot directly acquire nor release them. They are similar in purpose to locks: Latches protect internal memory structures while locks protect data structures.
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles so they can be accessed faster than by reading them off disks.
We can usually get more information about wait events by checking P1, P2 and P3. We will then look up on V$EVENT_NAME what P1, P2 and P3 mean for "latch: cache buffers chains".
SQL> select PARAMETER1 P1, PARAMETER2 P2, PARAMETER3 P3 2 from v$event_name 3 where name = 'latch: cache buffers chains'; P1 P2 P3 ---------- ---------- ---------- address number tries |
From the above query, P1 is the address of the latch for the cbc latch wait. Now, we can query ASH by grouping the CBC latch waits by the address and find out what address is experiencing the most contention (=waits).
SQL> select * from (
2 select
3 event,
4 trim(to_char(p1, 'XXXXXXXXXXXXXXXX')) latch_addr,
5 trim(round(ratio_to_report(count(*)) over () * 100, 1))||'%' pct,
6 count(*)
7 from
8 v$active_session_history
9 where
10 event = 'latch: cache buffers chains'
11 and session_state = 'WAITING'
12 group by event,p1
13 order by count(*) desc
14 )
15 where rownum <= 10;
EVENT LATCH_ADDR PCT COUNT(*)
-------------------------------------------------- ----------------- -------- ----------
latch: cache buffers chains 967757968 78.8% 178
latch: cache buffers chains 964F892C0 2.7% 6
latch: cache buffers chains CE2984B50 2.2% 5
latch: cache buffers chains 9676FC808 1.3% 3
latch: cache buffers chains 963019308 1.3% 3
latch: cache buffers chains 963DE63F0 .9% 2
latch: cache buffers chains 9655A1E38 .9% 2
latch: cache buffers chains 966F65E28 .9% 2
latch: cache buffers chains 963276CE8 .9% 2
latch: cache buffers chains CE1AD8E38 .9% 2
10 rows selected.
SQL>
|
Looking at the highlighted line, 78.8% of the waits on "latch: cache buffers chains" involves child latch at address 967757968.
Now that we know the actual CBC child latch address having the most waits, we can use Tanel Poder's latchprofx script (http://files.e2sn.com/scripts/latchprofx.sql) to find the root cause of the contention. We will monitor the holders (sid and sql_id) of this particular child latch. The column called "object", only available in x$ksuprlat shows information about the object protected by a given latch. For cache buffers chains latches, this column shows the Data Block Address (DBA) of the block that we accessed, causing the latch get.
SQL> @latchprofx sid,name,sqlid,object % 967757968 10000 -- LatchProfX 2.00 by Tanel Poder ( http://www.tanelpoder.com ) SID NAME SQLID OBJECT Held Gets Held % Held ms Avg hold ms ------ --------------------------- ------------- --------------- ---------- ---------- ------- ----------- ----------- 1258 cache buffers chains amnf5uaxyn12c 31B7E54 100 100 1.00 442.500 4.425 3077 cache buffers chains amnf5uaxyn12c 31B7E54 97 97 .97 429.225 4.425 3004 cache buffers chains amnf5uaxyn12c 31B7E54 88 88 .88 389.400 4.425 3082 cache buffers chains amnf5uaxyn12c 31B7E54 50 50 .50 221.250 4.425 2947 cache buffers chains amnf5uaxyn12c 31B7E54 13 13 .13 57.525 4.425 967 cache buffers chains 17qcz644sgfzk 31B7E54 4 4 .04 17.700 4.425 3176 cache buffers chains 17qcz644sgfzk 31B7E54 4 4 .04 17.700 4.425 3000 cache buffers chains 17qcz644sgfzk 31B7E54 2 2 .02 8.850 4.425 1165 cache buffers chains 17qcz644sgfzk 31B7E54 2 2 .02 8.850 4.425 1037 cache buffers chains 17qcz644sgfzk 31B7E54 2 2 .02 8.850 4.425 2950 cache buffers chains fb8phc7ms9s5u 31B7E54 2 2 .02 8.850 4.425 3077 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 2950 cache buffers chains 50p77b0zpx5cx 31B7E54 1 1 .01 4.425 4.425 2933 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 2633 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 2594 cache buffers chains 985rq8bkb254j 31B7E54 1 1 .01 4.425 4.425 2557 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 1284 cache buffers chains 985rq8bkb254j 31B7E54 1 1 .01 4.425 4.425 1240 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 1026 cache buffers chains 17qcz644sgfzk 31B7E54 1 1 .01 4.425 4.425 20 rows selected. |
The block address accessed is 31B7E54 and the sessions holding the child latch the most were executing the query with sql_id amnf5uaxyn12c.
Next, we will convert that data block address to file#, block# using dbms_utility and try to find to which segment it belongs querying x$bh (bh stands for blocks headers). Once again, Tanel wrote a script for that (dba.sql standing for Data Block Address^^). You will find it in tpt_public_unixmac.tar.gz available on Tanel's new website.
SQL> @dba 31B7E54 RFILE# BLOCK# ---------- ---------- 12 1801812 Press enter to find the segment using V$BH (this may take CPU time), CTRL+C to cancel: STATE BLOCK_CLASS OBJECT_TYPE object TCH MODE_HELD D T P S D FLG_LRUFLG DQ ---------- ------------------ ---------------- ------------------------------ ---------- ---------- - - - - - -------------- ------- xcur data block INDEX TAU$OWNER.ITRN2TAU 17 1 N N N N N 2000:8 0 Press enter to query what segment resides there using DBA_EXTENTS (this can be IO intensive), CTRL+C to cancel: ^C SQL> |
To solve the problem once and for all, we must find the SQL and why is application hitting the block so hard.
We can get sql_fulltext of a given sql_id either from V$SQL or V$SQLAREA or V$SQLSTATS. I personally prefer getting it from V$SQLSTATS because it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the shared pool).
I have a little script for that : sqltext.sql
SQL> @sqltext amnf5uaxyn12c Wrote file /tmp/sqlplus_settings SQL_FULLTEXT ------------------------------------------------------------------------------------------------------------------------------------------------------ SELECT DATEFT, CODAPPOPE, NUMTRAOPE, NUMTRN, MNEFLD, MNEPTF, INIRSPFRT, MNECPAFIN, NUMHDRPDT FROM TTRNTAU T1 WHERE T1.CODAPPOPE = :B2 AND T1.NUMTRAOPE = :B1 AND T1.DATEFT = (SELECT /*+ORDERED*/ MAX(T2.DATEFT) FROM TTRNTAU T2, THDRPDTTAU HDP WHERE T2.CODAPPOPE = T1.CODAPPOPE AND T2.NUMTRAOPE = T1.NUMTRAOPE AND T2.NUMHDRPDT = HDP.NUMHDRPDT AND ((HDP.TYPCPAREF != 'PF' AND T2.MNECPATRN = HDP.MNECPAREF) OR (HDP.TYPCPAREF = 'PF' AND T2.MNECPATRN = :B3 )) AND T2.CATTRN != 'ANNTEC' AND 0 = (SELECT MOD(COUNT(* ),2) FROM TTRNTAU T3 START WITH T3.CODAPPOPE = T2.CODAPPOPE AND T3.NUMTRAOPE = T2.NUMTRAOPE AND T3.CATTRN = 'ANNTEC' AND T3.NUMTRNANU = T2.NUMTRN CONNECT BY T3.CODAPPOPE = T2.CODAPPOPE AND T3.NUMTRAOPE = T2.NUMTRAOPE AND T3.CATTRN = 'ANNTEC' AND T3.NUMTRNANU = PRIOR T3.NUMTRN)) AND ((T1.NATTRN = 'CRE' AND NVL(T1.TYPSOUTRN,'XXX') NOT IN ('ANNPAR','ANNTOT')) OR (T1.NATTRN = 'ANN' AND (NVL(T1.TYPSOUTRN,'XXX') IN ('ANNPAR','ANNTOT') OR NOT EXISTS (SELECT NULL FROM TTRNTAU T3 WHERE T3.CODAPPOPE = T1.CODAPPOPE AND T3.NUMTRAOPE = T1.NUMTRAOPE AND T3.DATEFT = T1.DATEFT AND T3.NATTRN = 'CRE')))) SQL> |
If you have T.O.A.D, one useful feature is SQL formatting tool (don't get me wrong, I'm no big fan of GUI tools, I use SQL*PLUS 99% of the time ;-)
/* Formatted on 2010/04/16 16:17 (Formatter Plus v4.8.8) */ SELECT dateft, codappope, numtraope, numtrn, mnefld, mneptf, inirspfrt, mnecpafin, numhdrpdt FROM ttrntau t1 WHERE t1.codappope = :b2 AND t1.numtraope = :b1 AND t1.dateft = (SELECT /*+ORDERED*/ MAX (t2.dateft) FROM ttrntau t2, thdrpdttau hdp WHERE t2.codappope = t1.codappope AND t2.numtraope = t1.numtraope AND t2.numhdrpdt = hdp.numhdrpdt AND ( (hdp.typcparef != 'PF' AND t2.mnecpatrn = hdp.mnecparef ) OR (hdp.typcparef = 'PF' AND t2.mnecpatrn = :b3) ) AND t2.cattrn != 'ANNTEC' AND 0 = (SELECT MOD (COUNT (*), 2) FROM ttrntau t3 START WITH t3.codappope = t2.codappope AND t3.numtraope = t2.numtraope AND t3.cattrn = 'ANNTEC' AND t3.numtrnanu = t2.numtrn CONNECT BY t3.codappope = t2.codappope AND t3.numtraope = t2.numtraope AND t3.cattrn = 'ANNTEC' AND t3.numtrnanu = PRIOR t3.numtrn)) AND ( ( t1.nattrn = 'CRE' AND NVL (t1.typsoutrn, 'XXX') NOT IN ('ANNPAR', 'ANNTOT') ) OR ( t1.nattrn = 'ANN' AND ( NVL (t1.typsoutrn, 'XXX') IN ('ANNPAR', 'ANNTOT') OR NOT EXISTS ( SELECT NULL FROM ttrntau t3 WHERE t3.codappope = t1.codappope AND t3.numtraope = t1.numtraope AND t3.dateft = t1.dateft AND t3.nattrn = 'CRE') ) ) ) |
Let's have a look at the execution plan of this query using dbms_xplan.display_cursor:
SQL> @sqlplan amnf5uaxyn12c Wrote file /tmp/sqlplus_settings SQL_ID amnf5uaxyn12c, child number 0 ------------------------------------- SELECT DATEFT, CODAPPOPE, NUMTRAOPE, NUMTRN, MNEFLD, MNEPTF, INIRSPFRT, MNECPAFIN, NUMHDRPDT FROM TTRNTAU T1 WHERE T1.CODAPPOPE = :B2 AND T1.NUMTRAOPE = :B1 AND T1.DATEFT = (SELECT /*+ORDERED*/ MAX(T2.DATEFT) FROM TTRNTAU T2, THDRPDTTAU HDP WHERE T2.CODAPPOPE = T1.CODAPPOPE AND T2.NUMTRAOPE = T1.NUMTRAOPE AND T2.NUMHDRPDT = HDP.NUMHDRPDT AND ((HDP.TYPCPAREF != 'PF' AND T2.MNECPATRN = HDP.MNECPAREF) OR (HDP.TYPCPAREF = 'PF' AND T2.MNECPATRN = :B3 )) AND T2.CATTRN != 'ANNTEC' AND 0 = (SELECT MOD(COUNT(* ),2) FROM TTRNTAU T3 START WITH T3.CODAPPOPE = T2.CODAPPOPE AND T3.NUMTRAOPE = T2.NUMTRAOPE AND T3.CATTRN = 'ANNTEC' AND T3.NUMTRNANU = T2.NUMTRN CONNECT BY T3.CODAPPOPE = T2.CODAPPOPE AND T3.NUMTRAOPE = T2.NUMTRAOPE AND T3.CATTRN = 'ANNTEC' AND T3.NUMTRNANU = PRIOR T3.NUMTRN)) AND ((T1.NATTRN = 'CRE' AND NVL(T1.TYPSOUTRN,'XXX') NOT IN ('ANNPAR','ANNTOT')) OR (T1.NATTRN = 'ANN' AND (NVL(T1.TYPSOUTRN,'XXX') IN ('ANNPAR','ANNTOT') OR NOT EXISTS (SELECT NULL FROM TTRNTAU T3 WHERE T3.CODAPPOP Plan hash value: 311289460 -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------- |* 1 | FILTER | | | | | | | | | | 2 | HASH GROUP BY | | 1 | 144 | 17 (6)| 00:00:01 | 773K| 773K| 978K (0)| |* 3 | FILTER | | | | | | | | | | 4 | MERGE JOIN CARTESIAN | | 1 | 144 | 11 (0)| 00:00:01 | | | | | 5 | NESTED LOOPS | | 1 | 75 | 7 (0)| 00:00:01 | | | | |* 6 | TABLE ACCESS BY INDEX ROWID | TTRNTAU | 1 | 58 | 5 (0)| 00:00:01 | | | | |* 7 | INDEX RANGE SCAN | ITRN2TAU | 1 | | 4 (0)| 00:00:01 | | | | |* 8 | TABLE ACCESS BY INDEX ROWID | THDRPDTTAU | 1 | 17 | 2 (0)| 00:00:01 | | | | |* 9 | INDEX UNIQUE SCAN | PK_THDRPDTTAU | 1 | | 1 (0)| 00:00:01 | | | | | 10 | BUFFER SORT | | 1 | 69 | 10 (10)| 00:00:01 | 2048 | 2048 | 2048 (0)| | 11 | TABLE ACCESS BY INDEX ROWID | TTRNTAU | 1 | 69 | 4 (0)| 00:00:01 | | | | |* 12 | INDEX RANGE SCAN | ITRN2TAU | 1 | | 3 (0)| 00:00:01 | | | | |* 13 | TABLE ACCESS BY INDEX ROWID | TTRNTAU | 1 | 22 | 5 (0)| 00:00:01 | | | | |* 14 | INDEX RANGE SCAN | ITRN2TAU | 1 | | 4 (0)| 00:00:01 | | | | | 15 | SORT AGGREGATE | | 1 | 28 | | | | | | |* 16 | CONNECT BY WITH FILTERING | | | | | | 1024 | 1024 | | |* 17 | TABLE ACCESS BY INDEX ROWID | TTRNTAU | 1 | 50 | 5 (0)| 00:00:01 | | | | |* 18 | INDEX RANGE SCAN | ITRN2TAU | 1 | | 4 (0)| 00:00:01 | | | | | 19 | NESTED LOOPS | | | | | | | | | | 20 | CONNECT BY PUMP | | | | | | | | | |* 21 | TABLE ACCESS BY INDEX ROWID| TTRNTAU | 1 | 28 | 5 (0)| 00:00:01 | | | | |* 22 | INDEX RANGE SCAN | ITRN2TAU | 1 | | 4 (0)| 00:00:01 | | | | -------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DATEFT"=MAX("T2"."DATEFT")) 3 - filter(((("T1"."NATTRN"='CRE' AND NVL("T1"."TYPSOUTRN",'XXX')<>'ANNPAR' AND NVL("T1"."TYPSOUTRN",'XXX')<>'ANNTOT') OR ("T1"."NATTRN"='ANN' AND (NVL("T1"."TYPSOUTRN",'XXX')='ANNTOT' OR NVL("T1"."TYPSOUTRN",'XXX')='ANNPAR' OR IS NULL))) AND =0)) 6 - filter("T2"."CATTRN"<>'ANNTEC') 7 - access("T2"."CODAPPOPE"=:B2 AND "T2"."NUMTRAOPE"=:B1) 8 - filter((("T2"."MNECPATRN"="HDP"."MNECPAREF" AND "HDP"."TYPCPAREF"<>'PF') OR ("HDP"."TYPCPAREF"='PF' AND "T2"."MNECPATRN"=:B3))) 9 - access("T2"."NUMHDRPDT"="HDP"."NUMHDRPDT") 12 - access("T1"."CODAPPOPE"=:B2 AND "T1"."NUMTRAOPE"=:B1) 13 - filter("T3"."NATTRN"='CRE') 14 - access("T3"."CODAPPOPE"=:B1 AND "T3"."NUMTRAOPE"=:B2 AND "T3"."DATEFT"=:B3) filter("T3"."DATEFT"=:B1) 16 - access("T3"."NUMTRNANU"=PRIOR NULL) 17 - filter(("T3"."NUMTRNANU"=:B1 AND "T3"."CATTRN"='ANNTEC')) 18 - access("T3"."CODAPPOPE"=:B1 AND "T3"."NUMTRAOPE"=:B2) 21 - filter(("T3"."NUMTRNANU"=PRIOR NULL AND "T3"."CATTRN"='ANNTEC')) 22 - access("T3"."CODAPPOPE"=:B1 AND "T3"."NUMTRAOPE"=:B2) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level 72 rows selected. SQL> |
The operation connect by with filtering (lines 16 to 22 of the plan) is used to process hierarchical queries. It is characterized by two child operations. The first one is used to get the root of the hierarchy, and the second one is executed once for each level in the hierarchy. Each of those two child operations includes an index range scan of itrn2tau.
Looking at lines 4 to 12 of the plan, it is assuming that the result of the nested loops (lines 5 to 9) between ttrntau (accessing index itrn2tau) and thdrpdttau will return one row. It is also assuming that accessing table ttrntau by index itrn2tau will return 1 row as well (line 11-12). Then it just puts those two rows together with a cartesian product and joins that result to table ttrntau accessed by index itrn2tau (line 13-14) with a filter operation (line 3). It is because of the low cardinality expected that it went using a merge join cartesian. It is a perfectly valid approach and is very fast here.
The filter operation used for last join is very similar to a nested loop join - for each row in the driving table, Oracle operates the filter condition to determine whether or not to keep a row. However, filter can be much more efficient than nested loop, because it can remember results of previous probes into the second table - effectively making the probe an in-memory lookup.
As seen previously, for a single query execution, we access index itrn2tau several times. Furthermore, this query was executed concurrently by many sessions, that is why the latches were hammered so much. This is all about scalability, the query is running fine on a single user system but does not scale when run concurrently by hundreds of users.
Fortunately, this query was in-house developed so I asked the dev team to work on it.
如有错误,欢迎指正
邮箱:czmcj@163.com
发表评论
-
Oracle Redo 并行机制
2017-04-07 11:31 968Redo log 是用于恢复和一个高级特性的重要数据,一个r ... -
Append Values and how not to break the database
2015-09-29 20:12 732With the advent of the /*+ APP ... -
基于案例学习sql优化第6周脚本
2015-04-13 04:29 0===============BEGIN=========== ... -
Oracle表高水平位的优化与监控
2015-02-13 09:21 2195高水平位虚高的案例 --构造表drop table t p ... -
Oracle行迁移和行链接详解(原创)
2015-02-13 09:00 12066行迁移成 因:当发出u ... -
Parse CPU to Parse Elapsd%的理解
2015-01-19 13:59 1424Parse CPU to Parse Elapsd%是指sq ... -
ALTER INDEX COALESCE: 10g Improvements
2014-08-02 21:34 914I thought it might be worth me ... -
Differences and Similarities Between Index Coalesce and Shrink Space
2014-08-02 21:21 938As already discussed, ALTER IN ... -
Alter index coalesce VS shrink space
2014-08-02 17:56 99510g中引入了对索引的shrink功能,索引shrink操 ... -
SQL Plan Management Creating SQL plan baselines(原创)
2014-08-01 23:56 1346SQL Plan Management SQL Plan ... -
WITH Clause : Subquery Factoring
2014-07-23 08:43 1172Subquery Factoring The WIT ... -
Query Transformations : Subquery unnesting(原创)
2014-07-23 08:42 2880Subquery Unnesting Subqueries ... -
Automating Parallelism
2014-07-17 17:49 806Parallel query, the essence of ... -
Parallel Execution: Large/Shared Pool and ORA-4031 (文档 ID 238680.1)
2014-07-17 17:47 2085Applies toOracle Database - En ... -
Optimizer Transformations: Star Transformation
2014-06-30 07:32 769Star transformation was intro ... -
Star Transformation And Cardinality Estimates
2014-06-30 07:33 872If you want to make use of Orac ... -
Optimizer statistics-driven direct path read decision for full table scans
2014-06-06 16:09 1062Hello all fellow Oracle geeks ... -
Cut out from Ask Tom-- Thanks for the question regarding "10053", version 9.2.6
2014-03-09 23:38 1418You AskedDear tom,A. your new ... -
ORACLE SQL TUNING各种技巧及复杂实例
2014-02-25 23:17 6490一.优化器模式ORACLE的优化器共有3种:a. RULE ... -
Oracle Predicate Pushing(原创)
2014-02-22 21:17 4580IntroductionThe join predicate ...
相关推荐
等待事件row cache lock,latch row cache objects处理过程
增加`child cache buffers chains latch`的数量通常不会解决这个问题,因为数据块的分配是基于数据块地址和哈希链数量计算得出的,而不是根据`latch`的数量。 可以通过查看`v$session_wait`视图中的`p1raw`字段来...
### Oracle Library Cache Latch 竞争的解决 在Oracle数据库中,`Library Cache Latch`竞争是一个常见的性能瓶颈问题,通常会导致系统响应时间增加、性能下降等问题。本篇文章将详细探讨如何诊断并解决该问题。 ##...
这里以`latch: cache buffers chains`为例,它是一种常见的锁等待类型,通常与缓存块的访问有关。通过这种方式,我们可以快速定位到具体是哪些会话造成了这种类型的等待。 #### 根据hash_value查询具体SQL 有时候...
如果CPU利用率高达100%,并且等待事件显示为`latch: cache buffers chains`,这表明数据库可能需要优化其内存配置或SQL查询。解决这个问题的一种方法是通过删除并重新收集统计信息,特别是如果查询中使用了绑定变量...
例如,如果`latch: cache buffers chains`等待事件占据主导,说明可能存在缓存缓冲区链争用,影响了性能。而`CPU time`作为等待事件出现,表示Oracle进程在等待其他资源时,CPU并没有被有效利用。 通过对AWR中的...
gcc -Wall -O2 latch.c -o latch 示例用法 在端口 4443 上运行闩锁并启动 Python 服务器 ./testrun.sh 运行锁存器 ./latch ./latch > latch.log 运行 Python HTTP 服务器 cd www/ ./server.py 依赖关系 * ...
进一步地,为了确定Library cache争用是否确实为问题所在,作者又查看了v$latch_misses视图。这个视图记录了与latch相关的争用和睡眠信息。当进程无法获取某个latch时,就会记录下sleep_count,而wtr_slp_count则...
SELECT event, total_waits, time_waited FROM v$system_event WHERE event LIKE 'latch%'; ``` ### 八、所有Latch的列表 Oracle提供了多个不同类型的Latch,它们各自保护着不同的共享资源。以下是一些常见的Latch...
安装将Framework文件夹复制到您的项目中将Latch.LTPasscode.xib更改为<module>.LTPasscode.xib配置() 在视图控制器中创建闩锁self . latch = Latch ()self . latch . delegate = self // Make sure to add ...
流星闩锁它是一个将 Latch 服务与 Meteor 框架集成的软件包,让用户可以轻松保护自己的帐户。目录 描述Meteor 是用于构建现代 Web 应用程序的超简单环境。 它是一个允许以极快的速度创建实时应用程序的框架。 Latch ...
金丝雀闩锁基于#Latch 的#CanaryMod 访问控制插件版权所有 :copyright: 2015 Antonio Jesús Sánchez Padial执照Canary Latch是免费软件:您可以根据自由软件基金会发布的 GNU 宽松通用公共许可证(许可证的 2.1 版...
#LATCH 露天安装指南这是一个 beta 锁存插件,它实现了两个功能: 闩锁(Alfresco Explorer 和 Alfresco Share) 两因素身份验证(Alfresco Share) ##先决条件Alfresco 4.2 或更高版本使用 Alfresco Community 版本...
- **`cachebuffers chains`**: 如果 miss 数值较高,则可能表明 SGA 中的数据缓冲区不足或存在热点数据块。 - **`cachebuffers lru chain`**: 同样表示 SGA 中的数据缓冲区不足。 - **`shared pool`**: 关注共享...
帐户-ui-latch 一个将 Latch 服务与 Meteor 框架集成的软件包,让用户可以轻松保护自己的帐户。 这个包扩展了accounts-ui包的不同模板,以在登录下拉菜单中添加 Latch 选项。 第一次添加这个包时,会在下拉登录菜单...
例如,针对Buffer Cache的`cache buffer chains latch`和`cache buffer lru latch`,Shared Pool的`shared pool latch`和`library cache latch`,以及Log Buffer的`redo copy latch`、`redo allocation latch`和`...
例如,在访问缓冲区链(buffer chains)时,会涉及到`cache buffers chains` latch,用于检查缓冲区链的状态。本质上,Latch可以被视为一种锁(lock),用于控制并发访问。 #### 二、什么是Latch争用? Latch争用是指...
Emacs Lisp的承诺和闩锁 该软件包(ab)使用accept-process-output和进程提供异步阻塞,从而允许其他函数在当前执行上下文完成之前运行。 所有阻塞都会冻结Emacs的显示,但是计时器和I / O将继续运行。...
- 自Oracle 8.0起,可以实现共享Latch,用于某些AQ操作(如检查缓冲区链时获取的Cache Buffers Chains Latch)。 **1.2 Mutex简介** - **定义**:Mutex是一种更高层次的同步机制,用于保护更复杂的内存结构。 - *...
"Latch up 的原理分析" Latch up 是一种常见的集成电路故障,发生在 CMOS 芯片中,是由于寄生PNP和NPN双极性BJT之间的相互影响而产生的一低阻抗通路。Latch up 的出现可能会使 VDD 和 GND 之间产生大电流,导致芯片...