Search Results for: cost

Index block split等待事件

When a request for rows from a table is issued, Oracle may determine through the cost-based optimizer, which index access path is best for finding rows in a table. During this index lookup process, if another session is inserting or updating data, which causes updates to that index and an index block split, the first session must wait on that index block split until finished. After which the first session must retry the index lookup request again to get the appropriate index keys for the rows required… more …


Finding the splitting index:

When an index block split causes a session to wait, an event will be seen in the V$SESSION_WAIT view. The wait time associated with the event that holds up the session from selecting a row is important, but often just determining the splitting index object is key. The block splitting can then be limited by modifying the structure of the index. There are essentially three steps to this process of finding the offending index:

Find the data block addresses (dba) of the splitting index from the V$SESSION_WAIT view. Two different dbas are given plus the level of the index block:

P1: rootdba: The root of the index
P2: level: The level of the index where the block is being split
P3: childdba: The actual block of the index being split

SELECT sid, event, p1, p2, p3 FROM v$session_wait;

Find the physical location of the splitting index by using the DBMS_UTILITY package. Two functions will help zero in on the physical location of the index block using the rootdba value from step 1:

DATA_BLOCK_ADDRESS_FILE: Returns the file number of the dba
DATA_BLOCK_ADDRESS_BLOCK: Returns the block number the dba

FROM dual;

Find the offending index object from DBA_EXTENTS using the FILE_ID and BLOCK_ID values determined from step 2:

SELECT owner, segment_name
FROM dba_extents
WHERE file_id = <FILE_ID>
AND <BLOCK_ID> BETWEEN block_id AND block_id + blocks -1;


Re-evaluate the setting of PCTFREE for problematic indexes. Giving a higher PCTFREE will allow more index entries to be inserted into existing index blocks and thus prolong the need for an index block split.

Check the indexed columns to make sure they are valid. An improperly formed index key can cause excessive splitting by the nature and order of the columns it contains.

Check application logic. Many ill-formed applications have been known to perform excessive updates to indexes when not required. Review application code to verify all data manipulations are required, especially if some tables are treated as temporary objects when in fact they are permanent tables.

Expanded Definition

Indexes are made up of a root block, branch blocks, and leaf blocks. Each of which can go through a block split. As index entries are created, and because index structures are inherently ordered, if the block required to hold the new index key is full, room must be made by performing a block split. These block splits can occur in two different flavors.

The first case splitting the block 50/50 where a new block is created and half the entries are contained in each of the blocks after the split. The second case is a 99/1 split that accommodates indexes where there are ever increasing values and the new key value is the highest key. In this case the original block is left intact and the new block contains only the new entry.

db file scattered read等待事件

Waits on this event indicate the statement is performing a full table scan or an index range scan. This is often reduced by adding an index or making the index more efficient.


Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small, a full table scan could be more efficient that using an index so test the differences. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query.

Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.

Slow disks could be causing Oracle to spend time reading the data into the buffer cache. Review the ‘DB Multi Block Disk Read Time’ metric in Ignite to determine disk speeds from Oracle’s perspective. If the time to read data is above 30ms, that could indicate slow disks.

Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.

db file parallel read等待事件

The process has issued multiple I/O requests in parallel to read blocks from data files into memory and is waiting for all requests to complete. This occurs during regular activity when a session batches many single block I/O requests together and issues them in parallel. This is also occurs during recovery. This wait event does not apply to parallel query or parallel DML.


Block reads are necessary in a database, but it is important to limit unnecessary I/O. The best way to do this is by making the application as efficient as possible in regard to its data access requirements. Also, creating efficient SQL can produce large gains in performance. In contrast, changes to the RDBMS itself may produce smaller performance improvements.

Identify and resolve any SQL using unselective index scans. Use Ignite to find SQL with a large “db file parallel read” wait time — indicating a long index scan. Look at the explain plan to see if the index scan is high cost with low cardinality.

Try increasing the size of the buffer cache with DB_BLOCK_BUFFERS if enough memory is available on the server. This should reduce the cost of the I/O, since the necessary data is more likely to be in memory, but it won’t reduce the amount of I/O.

Consider using the operating system’s data cache if available. For tables that are frequently accessed via index scans, placing their corresponding data files on buffered file systems can reduce the I/O to actual drives.

Evaluate Data Clustering.

Evaluate whether table partitioning can reduce the amount of data needed to navigate to satisfy your queries.

cell multiblock physical read等待事件

This is an Exadata wait event similar to ‘db file scattered read’ and typically indicates the statement is performing a full table scan or an index fast full scan. This wait event is not seen nearly as much as ‘db file scattered read’ is seen on non-Exadata platforms because many full scans are offloaded to the storage cell via smart scans. This wait event is more prevalent against tables that are smaller and below the threshold for serial direct scan, a requirement for smart scans.


Review the ‘Cell Multiblock Physical Read Latency’ metric (in Ignite Resources view on the Exadata tab) to determine latencies. If the time to read data is above 2ms, that could indicate configuration issues with the Exadata storage cell.

Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small or the query reads more than 7-10% of the rows in the table, a full table scan could be more efficient than using an index so test the differences. This is especially true in an Exadata system because of the storage cell efficiencies for reading large quantities of data. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query.

Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.

Reduce the Number of SQL Statements

Shareable SQL uses bind variables rather than literal values. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU and the number of times the library cache and shared pool latches may need to be acquired and released. Even parsing a simple SQL statement may need to acquire a library cache latch twenty or thirty times.


By looking at the V$SQLAREA view it is possible to see which literal statements are good candidates for converting to use bind variables. The following query shows SQL in the SGA where there are a large number of similar statements:


SELECT substr(sql_text,1,50) “SQL”,
sum(executions) “TotExecs”
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,50)
HAVING count(*) > 30


This query finds statements whose first 50 characters are the same and which have only been executed a few times each and have at least 30 different copies of this SQL in the shared pool. The query may need to be modified if the literals are in the first 50 characters.


There are numerous parameters in the INIT.ORA that can directly impact the efficiency of shared pool usage. For a full accounting of these, refer to MetaLink Note: 62143.1.


根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项,原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了,使用SIMILAR选项可能引发额外的version_count过多或cursor pin s on X等待事件。

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g. A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and ‘library cache lock’.
From Oracle versions 9.0 through 11.1, an oracle instance would limit the number child cursors in the shared pool associated with a single parent to 1024 before it would mark the parent OBSOLETE effectively invalidating it and all the children. Then a new parent with one child would be created and used going forward. But this would only limit the degradation of performance for some environments rather than fix something that could be addressed more effectively through improved application coding. (The attempt to address this from the database side also introduced other issues like bug 5177766). The child limit was removed by Oracle development because it was only masking an application problem at the expense of database performance for better designed applications. In addition, the obsolete code would not work in cases when SQL was wrapped within PL/SQL. The fundamental problem that obsolete code was masking is application code that was written incorrectly with regards to the ability to be shared. For example,  it is not written with user binds or the literal characteristics differ to a high degree.
Therefore, setting cursor_sharing = SIMILAR is highly discouraged in Oracle Database 11g Release 2 (and generally has not been recommended for most environments even in earlier versions) for several reasons:
1) This parameter is generally overly restrictive in what it actually allows to be shared. SIMILAR tells oracle to try and share cursors by replacing all literals with binds for legacy applications, but directs that sharing only be performed when all the replaced literal values were exactly the same (in the case of predicates referencing columns with histograms or using inequality operators such as BETWEEN, <, and !=)
2) This parameter seems to bypass a lot of the improvements made with Oracle Database 11g’s Adaptive Cursor Sharing feature and other abilities in the Cost Based Optimizer code to make better decisions on what execution plans should and should not be shared.
3) Having many child cursors all associated with 1 parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT (or FORCE). The scenario of many thousands of child cursors associated with 1 parent results in a potential bottleneck for searches for matching cursors (soft parsing) within the library cache.
The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary.

What do you need to do?

Change the cursor_sharing to either FORCE or EXACT, keeping in mind the effects of either.

this is due to the setting in the init.ora:

Please set immediately
alter system set cursor_sharing=’FORCE’ scope=both;

to reduce the number of versions for a single sql statement whic is overloading your shared_pool.

377847.1 for SIMILA not FORCE are not the same

When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.


With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key

select … from orders where orderid=’literal';

If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.


If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.

From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

Background Information

This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).





Note #1: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Note #2: Setting CURSOR_SHARING to FORCE causes an increase in the
maximum lengths (as returned by DESCRIBE) of any selected
expressions that contain literals (in a SELECT statement).
However, the actual length of the data returned will not change.

Cursor_sharing 相关的BUG 列表:


NB Bug Fixed Description
14456124 Predicate push may not occur with cursor sharing
14053457, ORA-917 parsing SQL with indicator binds with CURSOR_SHARING
12723295, ORA-600 [qerixGetKey:optdesc] with function based index and CURSOR_SHARING
9877960, ORA-600 or similar using CURSOR_SHARING with HS connections
14087914 Wrong results from ExistsNode with CURSOR_SHARING
+ 13550185,,, Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools
13023854, Long parse time / hang for SQL with nested CASE expressions with CURSOR_SHARING enabled
12862170 INSERT ALL fails with ORA-600[kkslhsh1] with CURSOR_SHARING enabled / High Version Count on HASH_MATCH_FAILED
12797420,, “library cache: mutex X” waits on DB instance handle with CURSOR_SHARING
12649442,,, ORA-7445 [kxscod] with CURSOR_SHARING=FORCE or SIMILAR
12596444,,, Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
12534597 Bind Peeking is disabled for remote queries
12374212,,, Assorted dump , internal errors, memory corruptions with cursor_sharing = force
12345980, high parse time with cursor_sharing=force when session_cached_cursors set
11858021, ORA-600 [kpoal8-1] using DG4DRDA with CURSOR_SHARING=force
11806961, ORA-600 [kkspsc0: basehd] using CURSOR_SHARING
11738259,,, ORA-600 [kghssgfr2] using CURSOR_SHARING=FORCE
11714159, ORA-917 occurs with CURSOR_SHARING even if patch:9877980 is applied – superseded
11076030, Wrong results for XDB when CURSOR_SHARING enabled
11069199,,, ORA-600 [kksObsoleteCursor:invalid stub] with CURSOR_SHARING = SIMILAR | FORCE if fix 10187168 present
11063191,,,, ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10126094,,, ORA-600 [kglLockOwnersListAppend-ovf] from literal replacement on SQL issued from PLSQL
10013170, ORA-600 [736] from literal replacement with a “WAIT n” clause
* 9877980,,, ORA-7445[kkslMarkLiteralBinds] / Assorted Errors on if cursor sharing is enabled – Affects RMAN
9877964, ORA-600 [19003] raised by LIKE :BIND in query
9680430, High version count with CURSOR_SHARING = FORCE due to CBO transformation
9548104, OERI [qcsfbdnp:1] instead of ORA-1788 with cursor sharing
9413962, Many child cursors / ORA-600 [opixrb-3.0] [2005] [ORA-02005] binding literal for remote RPI
9411496, ORA-979 on GROUP BY query with CURSOR_SHARING set
9362218, Literals replaced by binds when CURSOR_SHARING=EXACT
9348402 OERI [kks-hash-collision] can occur with CURSOR_SHARING=FORCE|SIMILAR
9223586, Problems with variable length NCHAR literals with cursor sharing
9031183, ORA-1722 with CURSOR_SHARING=SIMILAR and with NCHAR
9008904, Dump (audTransFro) with CURSOR_SHARING
8545377, ORA-1780 with CURSOR_SHARING on XML queries
8246445, Query rewrite not working for multi-MV rewrite with literal replacement
5751866 Wrong Results with CASE and CURSOR_SHARING
9767674 Dump [kkslmtl] using CURSOR_SHARING – superceded
8794693 Dump [kkscsmtl] using literal replacement (CURSOR_SHARING)
8491399 Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
8453245 Many child cursors with CURSOR_SHARING = FORCE
8264642 ORA-600 [kkexbindopn0] with CURSOR_SHARING = SIMILAR
7651092 ORA-1000 with Literal Replacement, EXECUTE IMMEDIATE and CURSOR_SHARING_EXACT hint (affects DBMS_STATS)
7516867,, Intermittent Wrong results from literal replacement with fix for bug 6163785
7272297,,, Memory corruption / OERI[17114] / OERI[17125] with literal replacement
7212120, Session cursor cache not used properly when CURSOR_SHARING=force/similar
6337716,, Wrong max column size for NULL strings with literal replacement
5757106,, OERI[15851] selecting aggregate of a constant with literal replacement
4071519,, GROUP BY query with CURSOR_SHARING fails with ORA-1802
3461251, V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING
7296258, Intermittent Wrong results from literal replacement and remote objects
6163785, Intermittent Wrong Results with dblink and cursor_sharing
8202234 Intermittent Wrong Results with dblink and cursor_sharing
5863277, ORA-1008 from SQL on second run when cursor_sharing=similar/force
5762750, ORA-907 when cursor_sharing is enabled
5476507, OERI[15868] / OERI[15160] can occur with cursor sharing
5364819, OERI[kkslpbp:1] when using literal replacement
5254759, ORA-12801/ORA-1008 occurs on a parallel query with bind variables
5177766, OERI[17059] with SESSION_CACHED_CURSORS
5155885, OERI[kkslgbv0] with CURSOR_SHARING=similar
+ 5146740, Wrong results with bind variables/CURSOR_SHARING
5082178, Bind peeking may occur when it should not
5055175, Dump [kkslpkp] using literal replacement with timezone literals – superceded
4867724, Literal replacement limits column names to 30 characters
4698156, ORA-12850 querying GV$ views when CURSOR_SHARING=FORCE
4607460,, Dump [opipls] when CURSOR_SHARING = SIMILAR | FORCE
4513695, Poor performance for SELECT with ROWNUM=1 with literal replacement
4458226,,, High version count with cursor_sharing=force
4456646, Dump (kxscod/memcmp) using literal replacement
4451881,, OERI[kkslhsh1] from Insert as SELECT
4436832, False ORA-979 “not a group by expression” with literal replacement
4359367,, High version_count with cursor sharing
4254094, OERI[qerrmObnd1][932] possible for queries over DB links
4202503, Parse errors possible using CONTAINS with cursor_sharing=similar/force
4200541, Parse error when CURSOR_SHARING=FORCE|SIMILAR with user binds
3880881,, Dump (kkslMarkLiteralBinds) using cursor sharing
2837580, Dump if SQL has > ~35000 literals
13349665 ORA-600 [kkslmtl-valnotfound] with fix for bug 9767674 (eg After applying PSU
3980673,, Literal replacement may dump (kkslpbp) if user binds have names like SYS_B_xx
3842253,, Dump (kgghash) using literal replacement
3819834,, Dump (kkslflb) with literal replacement
3818541,, CURSOR_SHARING=force does not work if SQL has an NVL or DECODE predicate
3737955,, Long parse times for long inlists / many AND/OR terms
3668572,, ORA-979 when CURSOR_SHARING = force|similar with inline view and GROUP BY
3645694, Poor plan from SQL with literal replacement and LIKE .. ESCAPE ..
3406977,, High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
3148830,, Select over database link fails with OERI[opixmp-7] if CURSOR_SHARING=similar
3128363, OCIAttrGet returns wrong size when CURSOR_SHARING=SIMILAR or FORCE
3007574 OERI:15212 using CURSOR_SHARING=FORCE with database links
5526018 Dump [kkslpbp] / OERI[kkslpbp:1] with materialized view and literal replacement
3132071, Wrong results possible with CURSOR_SHARING=FORCE|SIMILAR
3045623, OERI[kkslhsh1] possible with CURSOR_SHARING=FORCE
2958096, Using XMLType / objects over DBLINKS with CURSOR_SHARING may dump
2843601, DML across a dblink fails with ORA-24370 if cursor_sharing is enabled
2643579, ORA-7445 [kxspoac] using CURSOR_SHARING=FORCE (or similar)
2620541, ORA-7445 [KXSPOAC] can occur on DML using CURSOR_SHARING
2508702, ORA-7445 [KKSLHIB] with CURSOR_SHARING=FORCE with INTO clause
2442125, Wrong results from PQ which compares a CHAR column to a BIND variable
2308292,, ORA-7445 [kkslhib] for INTO <bind> using CURSOR_SHARING and SESSION_CACHED_CURSORS
2273604,, CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
2052836,,, Client dump possible using CALL statement with CURSOR_SHARING=FORCE
4197915 CAST( x as number(n)) fails in PQ slaves with ORA-1727 with cursor_sharing
3614299 Dump adding a subscriber with a rule containing literals with CURSOR_SHARING = FORCE|SIMILAR
2442097 INSERTS into USER-DEFINED TYPES with CURSOR_SHARING=FORCE does not share cursors
2237610 OERI:[kgskbwt1] / OERI:[kskbind1] possible using CURSOR_SHARING
2300719, Spin possible in PRSGNT when CURSOR_SHARING=SIMILAR
2262665,, ORA-7445 [kkslpbp] possible with CURSOR_SHARING=FORCE
2259787, Dump possible in EXPCMPBND() with CURSOR_SHARING and GROUPING SETS
2154645, Spin in KKSFBC possible when CURSOR_SHARING=FORCE
1947974, False ORA-1008 possible using EXECUTE IMMEDIATE with CURSOR_SHARING=SIMILAR
1840199, OERI:EVAGGSID-1 possible using user defined aggregates (UDAG) with CURSOR_SHARING=FORCE|SIMILAR
1782025, Client may dump/error using DML RETURNING with CURSOR_SHARING=FORCE|SIMILAR
1777504,, Hang possible with CURSOR_SHARING=FORCE (or SIMILAR in 9i)
1764925, OERI:QCTSTC2O1 possible using CURSOR_SHARING = SIMILAR
2159152, Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
1538450, Dump in KXSPOAC possible CURSOR_SHARING=FORCE set
1365873, OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE
1358871, CURSOR_SHARING=FORCE may dump if first bind is a literal
984251, CURSOR_SHARING=FORCE can cause ORA-29909 when using an ANCILLARY OPERATOR
2485018 OERI:[KKSLGOP1] from SQL issued over a DBLINK with CURSOR_SHARING
2221407 ORA-3106 / OERI:15212 using BINDS over DBLINK with CURSOR_SHARING=FORCE
1545473, ORA-979 possible using CURSOR_SHARING=FORCE with GROUP BY & ORDER BY & LITERALS
1111796 Dump possible from CURSOR_SHARING=FORCE
984132 Literal in cursor expression can CORE DUMP with CURSOR_SHARING=FORCE


一套Linux x86-64上的11.2.0.2数据库在使用data pump数据泵expdp工具导出时遭遇了ORA-31623错误,详细日志如下:

[oracle@rh2 dbs]$ expdp system/D2nb51tz dumpfile=s01:sh.dmp schemas=sh

Export: Release - Production on Mon May 2 22:46:57 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

/* 伴随以上ORA-31623错误在告警日志中出现了以下记录 */

2011-05-02 22:47:05.792000 +08:00
Errors in file /s01/orabase/diag/rdbms/prod/PROD1/trace/PROD1_ora_22060.trc  (incident=54525):
ORA-04031: unable to allocate 56 bytes of shared memory 
("streams pool","unknown object","streams pool","fixed allocation callback")
Incident details in: /s01/orabase/diag/rdbms/prod/PROD1/incident/incdir_54525/PROD1_ora_22060_i54525.trc
2011-05-02 22:47:08.189000 +08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Dumping diagnostic data in directory=[cdmp_20110502224708], requested by (instance=1, osid=22060), summary=[incident=54525].
2011-05-02 22:47:10.502000 +08:00
Sweep [inc][54525]: completed
Sweep [inc2][54525]: completed

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 912M
memory_target                        big integer 912M
shared_memory_address                integer     0

从以上日志可以看到导出进程在为”fixed allocation callback”从streams pool流池中分配内存时遇到了ORA-04031错误,进而导致了UDE-31623错误。为什么expdp要从streams pool中分配内存呢?data pump数据泵在11g中开始Advanced Queue高级队列来控制其job作业的启动、停止和重启了。如果streams pool的当前size为0,那么显然无法分配到任何内存;而当前系统中就只设置了AMM特性的memory_target内存参数,并没有显式地设置streams_pool_size参数,这就导致了问题的发生。
我们可以通过上症状在MOS上搜索可以找到Note”UDE-31623 Error with Data Pump Export”:

Applies to:

Oracle Server - Enterprise Edition - Version: and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Data Pump Export does not start:

$ expdp system/ directory=test dumpfile=system.dmp logfile=abcd.log full=y

Export: Release - Production on Thu Mar 25 12:11:31 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1


For this Data Pump error, you will need to configure the database with some Streams Pool.

Perform the following query to find out how much STREAMS_POOL_SIZE has been allocated

 select * from v$sgainfo;
 Streams Pool Size                               0 Yes

Data Pump now uses Advanced Queuing (AQ) internally, so it can handle job stops/starts/re-starts.  
However, AQ using the Streams Pool, where the database currently has the STREAMS_POOL_SIZE set to ZERO.

Manually set the STREAMS_POOL_SIZE, re-start the database and re-attempt the Data Pump Export.

Alternately, turn on Automatic Shared Memory management (ASMM) by setting SGA_TARGET or MEMORY_TARGET.


SQL> col name for a20
SQL> select name,isinstance_modifiable from v$system_parameter where name='streams_pool_size';

NAME                 ISINS
-------------------- -----
streams_pool_size    TRUE

SQL> alter system set streams_pool_size=60M sid='PROD1';
alter system set streams_pool_size=60M sid='PROD1'
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> alter system set streams_pool_size=60M sid='PROD1' scope=spfile;
System altered.

Restart Instance .................

[oracle@rh2 dbs]$ expdp system/D2nb51tz dumpfile=s01:sh1.dmp schemas=sh

Export: Release - Production on Mon May 2 23:28:33 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=s01:sh1.dmp schemas=sh
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 273.8 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/DIMENSION
. . exported "SH"."CUSTOMERS"                            9.853 MB   55500 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998"                139.5 KB    4411 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999"                183.5 KB    5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000"                120.6 KB    3772 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001"                227.8 KB    7328 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998"                79.52 KB    2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999"                132.5 KB    4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000"                119.0 KB    3715 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001"                184.5 KB    5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998"                131.1 KB    4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999"                137.3 KB    4336 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000"                151.4 KB    4798 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001"                234.4 KB    7545 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998"                144.7 KB    4577 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999"                159.0 KB    5060 rows
. . exported "SH"."COSTS":"COSTS_Q4_2000"                160.2 KB    5088 rows
. . exported "SH"."COSTS":"COSTS_Q4_2001"                278.4 KB    9011 rows
. . exported "SH"."SALES":"SALES_Q1_1998"                1.412 MB   43687 rows
. . exported "SH"."SALES":"SALES_Q1_1999"                2.071 MB   64186 rows
. . exported "SH"."SALES":"SALES_Q1_2000"                2.012 MB   62197 rows
. . exported "SH"."SALES":"SALES_Q1_2001"                1.965 MB   60608 rows
. . exported "SH"."SALES":"SALES_Q2_1998"                1.160 MB   35758 rows
. . exported "SH"."SALES":"SALES_Q2_1999"                1.754 MB   54233 rows
. . exported "SH"."SALES":"SALES_Q2_2000"                1.802 MB   55515 rows
. . exported "SH"."SALES":"SALES_Q2_2001"                2.051 MB   63292 rows
. . exported "SH"."SALES":"SALES_Q3_1998"                1.633 MB   50515 rows
. . exported "SH"."SALES":"SALES_Q3_1999"                2.166 MB   67138 rows
. . exported "SH"."SALES":"SALES_Q3_2000"                1.909 MB   58950 rows
. . exported "SH"."SALES":"SALES_Q3_2001"                2.130 MB   65769 rows
. . exported "SH"."SALES":"SALES_Q4_1998"                1.581 MB   48874 rows
. . exported "SH"."SALES":"SALES_Q4_1999"                2.014 MB   62388 rows
. . exported "SH"."SALES":"SALES_Q4_2000"                1.814 MB   55984 rows
. . exported "SH"."SALES":"SALES_Q4_2001"                2.257 MB   69749 rows
. . exported "SH"."SUPPLEMENTARY_DEMOGRAPHICS"           697.3 KB    4500 rows
. . exported "SH"."FWEEK_PSCAT_SALES_MV"                 419.8 KB   11266 rows
. . exported "SH"."PROMOTIONS"                           58.89 KB     503 rows
. . exported "SH"."TIMES"                                380.8 KB    1826 rows
. . exported "SH"."CAL_MONTH_SALES_MV"                   6.312 KB      48 rows
. . exported "SH"."CHANNELS"                              7.25 KB       5 rows
. . exported "SH"."COUNTRIES"                            10.20 KB      23 rows
. . exported "SH"."PRODUCTS"                             26.17 KB      72 rows
. . exported "SH"."COSTS":"COSTS_1995"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_1996"                       0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H1_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_H2_1997"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q1_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q2_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q3_2003"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2002"                    0 KB       0 rows
. . exported "SH"."COSTS":"COSTS_Q4_2003"                    0 KB       0 rows
. . exported "SH"."DIMENSION_EXCEPTIONS"                     0 KB       0 rows
. . exported "SH"."SALES":"SALES_1995"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_1996"                       0 KB       0 rows
. . exported "SH"."SALES":"SALES_H1_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_H2_1997"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q1_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q2_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q3_2003"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2002"                    0 KB       0 rows
. . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:30:20

/* 11g中加强了data pump在告警日志中的记录信息,可以看到这里成功启动了DM进程*/

DM00 started with pid=55, OS id=25347, job SYSTEM.SYS_EXPORT_SCHEMA_01
2011-05-02 23:28:35.814000 +08:00
Starting background process SMCO
SMCO started with pid=56, OS id=25349
DW00 started with pid=57, OS id=25351, wid=1, job SYSTEM.SYS_EXPORT_SCHEMA_01
2011-05-02 23:28:54.029000 +08:00
XDB installed.
XDB initialized.

Extract SQL Plan from AWR

之前有用户在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。

这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:

SQL> set linesize 200 pagesize 2000;

SQL> select * from global_name;


SQL> select /* extract_me */ count(*) from maclean;


SQL> select sql_id from v$sql where sql_text like '%extract_me%' and sql_text not like '%like%';


SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

QL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));

SQL_ID  8vff23q8qp9fj, child number 0
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |

   - dynamic sampling used for this statement

18 rows selected.

/* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,

SQL> alter system flush shared_pool;
System altered.

SQL> select * from table(dbms_xplan.display_cursor('8vff23q8qp9fj',0));

SQL_ID: 8vff23q8qp9fj, child number: 0 cannot be found

/* 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划 */

SQL>  select * from table(dbms_xplan.display_awr('8vff23q8qp9fj'));

SQL_ID 8vff23q8qp9fj
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |

   - dynamic sampling used for this statement

18 rows selected.

/* 这里可以代入'all'选项获取更细致的计划信息 */

SQL> select * from table(dbms_xplan.display_awr('8vff23q8qp9fj',null,null,'all'));

SQL_ID 8vff23q8qp9fj
select /* extract_me */ count(*) from maclean

Plan hash value: 1679547536

| Id  | Operation          | Name    | Rows  | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |         |       |    34 (100)|          |
|   1 |  SORT AGGREGATE    |         |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MACLEAN | 10438 |    34   (0)| 00:00:01 |

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$1
   2 - SEL$1 / MACLEAN@SEL$1

   - dynamic sampling used for this statement

24 rows selected.

/* 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句 */

SQL> select OPERATION,OPTIONS,OBJECT_NAME from dba_hist_sql_plan where sql_id='8vff23q8qp9fj';

OPERATION                      OPTIONS                        OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           MACLEAN