happydba

library cache 相关的latch 等待并都是因为sql语句没有绑定变量造成的！！！

也有很多种情况会造成相同的sql_id,相同的hash_value而不能share。
这通常都会造成很多的child cursor.

      
查询v$sqlarea中版本数大于10的所有sql的语句：
select sql_id,version_count, sql_text
from   v$sqlarea
where version_count > 10
order by version_count, hash_value;

通常有问题的时候某些sql_id 的 version_count 可能会大于1000，
如果对比的话，sql_text都是完全相同的，那么到底是什么造成如此多的version呢？

例如我们上面的查询找到了sql_id:10xj8nynmpqtq

则执行下面的查询：

select SQL_ID,CHILD_NUMBER,
UNBOUND_CURSOR,
SQL_TYPE_MISMATCH,
OPTIMIZER_MISMATCH,
OUTLINE_MISMATCH,
STATS_ROW_MISMATCH,
LITERAL_MISMATCH,
SEC_DEPTH_MISMATCH,
EXPLAIN_PLAN_CURSOR,
BUFFERED_DML_MISMATCH,
PDML_ENV_MISMATCH,
INST_DRTLD_MISMATCH,
SLAVE_QC_MISMATCH,
TYPECHECK_MISMATCH,
AUTH_CHECK_MISMATCH,
BIND_MISMATCH,
DESCRIBE_MISMATCH,
LANGUAGE_MISMATCH,
TRANSLATION_MISMATCH,
ROW_LEVEL_SEC_MISMATCH,
INSUFF_PRIVS,
INSUFF_PRIVS_REM,
REMOTE_TRANS_MISMATCH,
LOGMINER_SESSION_MISMATCH,
INCOMP_LTRL_MISMATCH
from v$sql_shared_cursor
where sql_id=’10xj8nynmpqtq’;

SQL_ID        CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I
————- ———— – - – - – - – - – - – - – - – - – - – - – - – -
10xj8nynmpqtq            0 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            1 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            2 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq            3 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            4 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            5 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq            6 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            7 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq            8 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq            9 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq           10 N N N N N N N N N N N N N N N N N N N N N N N N

SQL_ID        CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I
————- ———— – - – - – - – - – - – - – - – - – - – - – - – -
10xj8nynmpqtq           11 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq           12 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq           13 N N N N N N N N N N N N N N N N N N N N N N N N
10xj8nynmpqtq           14 N N N N N N N N N N N N N N Y N N N N N N N N N
10xj8nynmpqtq           15 N N N N N N N N N N N N N N N N N N N N N N N N

相应的 Y 列说明了该cursor不能共享的原因：

BIND_MISMATCH – The bind metadata does not match the existing child cursor

除了这些能够查询到的原因之外，还有可能所有的列都是 N ,但是仍然还是有很多version_count .

那么恭喜你中奖了，这种情况可能的BUG:

Bug 4458226 High version count with cursor_sharing=force

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected 10.1.0.4 ,10.2.0.1

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.8 (Server Patch Set) ,10.1.0.5 (Server Patch Set)
10.2.0.2 (Server Patch Set) ,11g (Future version)

Bug 4701527 Cursors not shared when executing procedures over a dblink

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11
Versions confirmed as being affected 9.2.0.5 ,9.2.0.7 ,10.1.0.5

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.8 (Server Patch Set) ,10.2.0.4 (Server Patch Set) ,11g (Future version)

Description
A cursor sharing problem exists for INSERT statements that insert into a remote table and are executed via a PLSQL module which itself is being executed remotely.

To be seeing this problem the following must be true:
1. Multiple child cursors are seen for an INSERT statement
2. V$SQL_SHARED_CURSOR will have the INST_DRTLD_MISMATCH column set to ‘Y’
   for the cursor
3. The insert statement is executed remotely via a PLSQL module that is
   executed remotely

Bug 3461251 V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING=FORCE

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2
Versions confirmed as being affected 9.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 11.2 (Future Release)

Description
New child cursors may be created but V$SQL_SHARED_CURSOR does not have a ‘Y’ in any of the columns that denote the reason why the cursor could not be shared for the cursor in question. This is common when literal replacement is enabled and histograms exist on the columns that are predicates of the SQL statement and have undergone literal replacement.

This fix adds a new column to V$SQL_SHARED_CURSOR called HASH_MATCH_FAILED which is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data.

Workaround:
Check if the unshared child cursors have histogram data on key columns.

Bug 5705795 Win/Linux: Many child cursors possible for SQL using BINDS

This bug is alerted in Note 403616.1
Affects:

    Product (Component)    Oracle Server (Rdbms)
    Range of versions believed to be affected    Versions >= 10.2.0.3 but < 10.2.0.4
    Versions confirmed as being affected   

        * 10.2.0.3

    Platforms affected   

        * Linux 32bit
        * Windows/NT/XP

     It is believed to be a regression in default behaviour thus:
       Regression introduced in 10.2.0.3

Fixed:

    This issue is fixed in   

        * 10.2.0.4 (Server Patch Set)

Symptoms:
   

Description

    This problem is introduced in 10.2.0.3 on Windows 32bit and Linux 32bit only by the fix for bug 4752541.

    10.2.0.3 on other platforms includes the fix for this bug# and so are not affected.

    SQL using bind variables with different bind sizes can lead to a large number of child cursors being
    created leading to excess shared pool usage and latch contention.

今天碰到的：

Rac其中一个节点sqlplus / as sysdba都会被挂起，另一个Node没有问题。

alertlog中有这样的信息：
Thread 2 advanced to log sequence 5327
Current log# 3 seq# 5327 mem# 0: /dev/rlvredo21
Mon Aug 4 15:39:00 2008
Thread 2 advanced to log sequence 5328
Current log# 4 seq# 5328 mem# 0: /dev/rlvredo22
Mon Aug 4 15:59:55 2008
GES: Potential blocker (pid=536584) on resource CI-0000001E-00000002;
enqueue info in file /oracle/app/oracle/admin/qasmdm/bdump/qasmdm2_lmd0_438324.trc and DIAG trace file

看trace文件：

[qasmdm2:oracle] /> more /oracle/app/oracle/admin/qasmdm/bdump/qasmdm2_lmd0_438324.trc
/oracle/app/oracle/admin/qasmdm/bdump/qasmdm2_lmd0_438324.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /oracle/app/oracle/product/10.2.0
System name:    AIX
Node name:      PEKAX132
Release:        3
Version:        5
Machine:        00002A27D100
Instance name: qasmdm2
Redo thread mounted by this instance: 2
Oracle process number: 6
Unix process pid: 438324, image: oracle@PEKAX132 (LMD0)

*** 2008-07-30 00:27:17.430
*** SERVICE NAME:() 2008-07-30 00:27:17.429
*** SESSION ID:(551.1) 2008-07-30 00:27:17.429
* kjdrchkdrm: found an RM request in the request queue
Transfer pkey 4294950913 to node 1
*** 2008-07-30 00:27:17.515
Single node DRM(1) – transfer pkey 4294950913 to 1 done
*** 2008-07-30 00:27:17.515
Single node DRM(1) – transfer pkey 4294950914 to 1 done
*** 2008-07-30 00:27:17.515
Single node DRM(1) – transfer pkey 4294950915 to 1 done
*** 2008-07-30 00:27:17.515
Single node DRM(1) – transfer pkey 4294950916 to 1 done
。。。
。。。
Single node DRM(7) – transfer pkey 4294950947 to 1 done
* kjdrchkdrm: found an RM request in the request queue
Transfer pkey 4294950948 to node 1
*** 2008-07-30 00:27:43.465
Single node DRM(9) – transfer pkey 4294950948 to 1 done
* kjdrchkdrm: found an RM request in the request queue
Transfer pkey 4294950956 to node 1
*** 2008-07-30 00:27:43.476
Single node DRM(11) – transfer pkey 4294950956 to 1 done
*** 2008-07-30 00:27:43.476
。。。
。。。
synca inc 4 lvl 1 from 0 rcvd (my inc,lvl: 0,0) (4/5.0.0)
m2s domain info received from 0 (4.6)
* m2s: domain 0 valid = 1 according to instance 0
received master 1 (4.6)
ftd received from node 0 (4/7.0.0)
all ftds received
synca inc 4 lvl 2 from 0 rcvd (my inc,lvl: 4,1) (4/7.0.0)
ftd received from node 0 (4/9.0.0)
all ftds received
synca inc 4 lvl 3 from 0 rcvd (my inc,lvl: 4,2) (4/13.0.0)
ftd received from node 0 (4/14.0.0)
all ftds received

PEKAX132:/>ps -ef|grep 536584
oracle 536584       1   0   Jul 30      – 7:11 ora_lck0_qasmdm2
    root 2064566 2101288   0 16:18:43 pts/6 0:00 grep 536584

来看看lck进程是干什么的：
我们知道：LCKn是一个可选进程，当在并行服务器模式可出现多个锁定进程以利于数据库通信，作用就是锁定当前Instan要使用的资源而避免冲突。

再看trace文件名对应的进程：qasmdm2_lmd0_438324.trc

Lock Manager Daemon ?LMDn
In parallel server environments Oracle synchronizes access to resources that are shared between instances using instance locks. Each instance manages a subset of the instance locks. The LMDn processes service requests for locally managed locks directly, and forward requests for remotely managed locks to the remote node. The LMDn processes are also responsible for the detection of distributed deadlocks. Multiple lock manager processes can be configured for each instance, but one is normally sufficient because the messages are passed asynchronously.

显然是由于Rac资源的锁造成的

因为时间比较紧，没有去细查究竟什么原因造成的这个锁，只是简单的Kill了进程.

可知的相关的bug可以参考：BUG 6271080

论坛上看到有人说高级复制遭遇：

ORA-1422, 00000, “exact fetch returns more than requested number of rows”
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

其实这个错误最好的找出问题的方法就是做trace，从而能找到引起这个问题的具体原因。

当然了，也有一些经验值：
转自metalink

Subject:	ORA-1422 WHEN DEFERRED TRANSACTIONS PUSHED TO THE REMOTE SITE
	Doc ID:	Note:1039878.6	Type:	PROBLEM
	Last Revision Date:	13-MAY-2002	Status:	PUBLISHED

PROBLEM DESCRIPTION:
=====================

You have a replication environment and receive the following error:

ORA-1422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested

PROBLEM EXPLANATION:
====================

There may be 2 entries in the SYS.DUAL table.
See Note 1067733.6 to resolve this issue.

--OR--

Your replicated tables may not have a primary key or
alternate primary key

SEARCH WORDS:
=============
ORA-1422 replication dbms_defer_sys deftran deferror ORA-01422

SOLUTION DESCRIPTION:
======================
Because two rows may have different row IDs at different replication sites,
Oracle uses the primary key for a table to determine which rows to
compare.  If you do not want to use the primary key for a table or if the
table does not have a primary key, you must use DBMS_REPCAT.SET_COLUMNS
procedure to define an alternate primary key.

SQL> describe sys.dbms_repcat.set_columns
PROCEDURE sys.dbms_repcat.set_columns
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SNAME                          VARCHAR2                IN
 ONAME                          VARCHAR2                IN
 COLUMN_LIST                    VARCHAR2                IN

Because this key is used to determine matching rows at different sites,
the columns that you specify in the COLUMN_LIST must result in a unique
identifier for the row.  The procedure SET_COLUMNS will NOT enforce
uniqueness.

加了一些说明

看看你碰到的都是哪种。

1.Bad Connection Management

The application connects and disconnects for each database interaction. This
problem is common with stateless middleware in application servers. It has over
two orders of magnitude impact on performance, and is totally unscalable.
1)每次数据库的交互都需要application连接和断开数据库，通常解决：在中间层建立长效连接(连接池)
2)程序问题，只有连接操作，没有断开操作。
可以通过

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                                     —————       —————
                                                             ………                 …..
                           Sorts:                       115.15                 30.31
                       Logons:                            0.01                  0.00
                    Executes:                        782.43                205.94

2.Bad Use of Cursors and the Shared Pool

Not using cursors results in repeated parses. If bind variables are not used,
then there is hard parsing of all SQL statements. This has an order of magnitude
impact in performance, and it is totally unscalable. Use cursors with bind
variables that open the cursor and execute it many times. Be suspicious of
applications generating dynamic SQL.

通过Report的Hard parses,以及Soft Parse来检查

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                                    —————       —————

                               Parses:                500.75                 29.85
                         Hard parses:                 36.57                  2.18
                                   Sorts:                847.34                 50.51

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %: 100.00
               Buffer Hit   %:   99.26    In-memory Sort %: 100.00
               Library Hit   %:   98.04           Soft Parse %:   92.70
       Execute to Parse %:   70.09              Latch Hit %:   99.39
Parse CPU to Parse Elapsd %:   25.35     % Non-Parse CPU:   77.11

3.Bad SQL

Bad SQL is SQL that uses more resources than appropriate for the application
requirement. This can be a decision support systems (DSS) query that runs for
more than 24 hours or a query from an online application that takes more than a
minute. SQL that consumes significant system resources should be investigated
for potential improvement. ADDM identifies high load SQL and the SQL tuning
advisor can be used to provide recommendations for improvement. See Chapter 6,
“Automatic Performance Diagnostics” and Chapter 12, “Automatic SQL Tuning”.

Statspack report中：
SQL ordered by Gets，SQL ordered by Reads部分
找到相应的top sql,看它们的执行计划是否合理，找出它们消耗资源的原因。

4.Use of Nonstandard Initialization Parameters

These might have been implemented based on poor advice or incorrect assumptions.
Most systems will give acceptable performance using only the set of basic
parameters. In particular, parameters associated with SPIN_COUNT on latches and
undocumented optimizer features can cause a great deal of problems that can
require considerable investigation.

Likewise, optimizer parameters set in the initialization parameter file can
override proven optimal execution plans. For these reasons, schemas, schema
statistics, and optimizer settings should be managed together as a group to
ensure consistency of performance.

不建议使用的参数：
1) 并不太确认参数的技术细节
2) 未经过测试
3) 未明确目的(即设定某个参数都要针对某个目的而设定)

5.Getting Database I/O Wrong

Many sites lay out their databases poorly over the available disks. Other sites
specify the number of disks incorrectly, because they configure disks by disk
space and not I/O bandwidth. See Chapter 8, “I/O Configuration and Design”.
系统I/O太差劲，iostat 可以看到磁盘的io,vmstat 可以看到cpu的使用是否是wait(iowait)
考虑分散io,优化存储(系统级或硬件级)

6.Redo Log Setup Problems

Many sites run with too few redo logs that are too small. Small redo logs cause
system checkpoints to continuously put a high load on the buffer cache and I/O
system. If there are too few redo logs, then the archive cannot keep up, and the
database will wait for the archive process to catch up. See Chapter 4,
“Configuring a Database for Performance” for information on sizing redo logs for
performance.

1)确定日志文件的大小，然后通过alert log来看日志切换是否太频繁
2)Logminer分析redo log,确定日志频繁的原因，通常可能为：小事务，系统Bug,系统负载确实比较重…
3)原因确定的话再考虑采取下一步骤

7.Serialization of data blocks in the buffer cache due to lack of free lists, free list groups, transaction slots (INITRANS), or shortage of rollback segments.

This is particularly common on INSERT-heavy applications, in applications that
have raised the block size above 8K, or in applications with large numbers of
active users and few rollback segments. Use automatic segment-space management
(ASSM) to and automatic undo management solve this problem.

有了10g ASSM这些就基本不用考虑了。

8.Long Full Table Scans

Long full table scans for high-volume or interactive online operations could
indicate poor transaction design, missing indexes, or poor SQL optimization.
Long table scans, by nature, are I/O intensive and unscalable.

可以通过Statspack report Top sql + explain plan

9.High Amounts of Recursive (SYS) SQL

Large amounts of recursive SQL executed by SYS could indicate space management
activities, such as extent allocations, taking place. This is unscalable and
impacts user response time. Use locally managed tablespaces to reduce recursive
SQL due to extent allocation. Recursive SQL executed under another user Id is
probably SQL and PL/SQL, and this is not a problem.

10.Deployment and Migration Errors

In many cases, an application uses too many resources because the schema owning
the tables has not been successfully migrated from the development environment
or from an older implementation. Examples of this are missing indexes or
incorrect statistics. These errors can lead to sub-optimal execution plans and
poor interactive user performance. When migrating applications of known
performance, export the schema statistics to maintain plan stability using the
DBMS_STATS package.
迁移问题，或者迁移没有完成，比方迁移后发现：
1) 某些索引不可用
2) 某些package，过程失效
3) 统计信息错误
4) 迁移后环境有变化

Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4        1024.00    983.30    4%     2908     2% /
/dev/hd2        3072.00   1211.87   61%    35675    12% /usr
/dev/hd9var     1024.00    926.40   10%      887     1% /var
/dev/hd3        4096.00   3415.79   17%     2314     1% /tmp
/dev/hd1      102400.00 20957.76   80%      260     1% /home
/proc                 -         -    -         -     – /proc
/dev/hd10opt    1280.00    930.20   28%     5618     3% /opt
/dev/lvoradb 153600.00 107092.39   31%    26951     1% /u01
/dev/fslv00    10240.00   7596.09   26%     1257     1% /oracle_patch
/dev/fslv01   122880.00 13452.45   90%     6837     1% /u02
/dev/perfmgrlv    256.00    249.97    3%       74     1% /var/adm/perfmgr

 

select a.file_id “FileNo”,a.tablespace_name “Tablespace_name”,
round(a.bytes/1024/1024,4) “Total MB”,
round((a.bytes-sum(nvl(b.bytes,0)))/1024/1024,1) “Used MB”,
round(sum(nvl(b.bytes,0))/1024/1024,1) “Free MB”,
round(sum(nvl(b.bytes,0))/a.bytes*100,1) “%Free”
from dba_temp_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name,
a.file_id,a.bytes order by a.tablespace_name;

   FileNo Tablespace_name   Total MB   Used MB   Free MB   %Free
———- ———————– ———-    ———-   ———-    ———-
         1   TEMP                     32767     32546.9     220.1        7

 

SQL> select tablespace_name, CURRENT_USERS, TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS
2 from v$sort_segment;

TABLESPACE_NAME      CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
——————————- ———————— ——————— ——————– ———–
TEMP                                        0                   2097024              0                         2097024

SQL> SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
2 FROM v$session a, v$tempseg_usage b, v$sqlarea c
3 WHERE a.saddr = b.session_addr
4 AND c.address= a.sql_address
5 AND c.hash_value = a.sql_hash_value
6 ORDER BY b.tablespace, b.blocks;

no rows selected

当前没有使用temp segment的

于是：resize,关autoextend

SQL> alter database tempfile 1 resize 3G;

Database altered.
SQL> alter database tempfile 1 autoextend off;

Database altered.

SQL> select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024
SQL> from dba_temp_files;

FILE_NAME                                              BYTES/1024/1024 AUT MAXBYTES/1024/1024
————————————————— ———————– —— ——————
/u02/oradata/lenodb/lenodbtemp01.dbf     3072                     NO     0