Search Results for: cost

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”,
count(*),
sum(executions) “TotExecs”
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,50)
HAVING count(*) > 30
ORDER BY 2

 

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.

cursor_sharing=’SIMILAR’将被废弃

根据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:
cursor_sharing=SIMILAR

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.

CURSOR_SHARING=SIMILAR

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 ‘!=’
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 column.eg:

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.

CURSOR_SHARING = FORCE

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 11.1.0.7 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).

 

在11g中Oracle官方已经不再推荐使用SIMILAR选项,对于已经升级到11g的仍在使用cursor_sharing=’SIMILAR’的用户,建议尽早修改应用做到绑定变量,这样可以最稳妥的将cusror_sharing设置为EXACT,对于无法做到绑定变量的应用那么FORCE还会是一个和好的折中选择。

在版本12g中我们将不再看到SIMILAR选项。

 

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

解决UDE-31623错误一例

一套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 11.2.0.2.0 - 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 11.2.0.2.0 - 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: 11.2.0.0. and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
Goal

Data Pump Export does not start:

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

Export: Release 11.2.0.1.0 - 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 11.2.0.1.0 - 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

Solution

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.

显然上述Note在有一点上说错了,那就是设置memory_target参数无益于解决该UDE-31623问题;最好的做法还是显式地去设置streams_pool_size参数,但遗憾的是streams_pool_size虽然是一个可以动态修改的参数,但实际上是无法动态修改的:

SQL> col name for a20
SQL>
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 11.2.0.2.0 - 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 11.2.0.2.0 - 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...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type SCHEMA_EXPORT/MATERIALIZED_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:
  /s01/sh1.dmp
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;

GLOBAL_NAME
--------------------------------------------------------------------------------
oracledatabase12g.com

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

  COUNT(*)
----------
      9564

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

SQL_ID
-------------
8vff23q8qp9fj

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

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

PLAN_TABLE_OUTPUT
-------------------------------------
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 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

/* 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
     需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
 */

SQL> alter system flush shared_pool;
System altered.

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

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------
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 |
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

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

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
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

Note
-----
   - 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
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT                           AGGREGATE
TABLE ACCESS                   FULL                           MACLEAN

【11g新特性】SPM SQL PLAN MANAGEMENT执行计划管理流程图

Oracle SPM SQL PLAN MANAGEMENT执行计划管理流程图如下:

 

 

Plan Baseline Capture - Automatic Plan Baseline Selection1 Plan Baseline Evolution1 SQL Management Base (SMB)

SQL> create table mac_spm tablespace users as select * from dba_objects;

Table created.

SQL> analyze table mac_spm compute statistics;

Table analyzed.

SQL>    alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> 
SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

SQL>    select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

	alter session set optimizer_capture_sql_plan_baselines=false;

	SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace on;
SQL>  select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 874020942

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     4 |   301   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| MAC_SPM | 75535 |   295K|   301   (1)| 00:00:04 |
------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_cjd95ftv8ct90eca706bd" used for this statement

Statistics
----------------------------------------------------------
        240  recursive calls
          0  db block gets
       1289  consistent gets
          0  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         25  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index ind_objd on mac_spm(object_id);

Index created.

oradebug setmypid		  
oradebug tracefile_name

SQL> alter system flush shared_pool;

System altered.

alter system set events 'trace[SQL_Plan_Management] disk highest';

select sum(object_id) from mac_spm;

*** 2013-04-11 09:28:49.628
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 301.13, current accepted plan cost = 301.13
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f8e30491298, pmExCtx=0xc7463800, ciP=0xc99f7e78, dtCtx=0xbaf7c20

SQL> select  /*+ index( MAC_SPM ind_objd) */ sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    2911455720

Execution Plan
----------------------------------------------------------
Plan hash value: 45369511

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |   169   (1)| 00:00:03 |
|   1 |  SORT AGGREGATE  |          |     1 |     4 |            |          |
|   2 |   INDEX FULL SCAN| IND_OBJD | 75535 |   295K|   169   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        168  consistent gets
        168  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> insert into mac_spm select * from mac_spm;

75535 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(user,'MAC_SPM');

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> oradebug setmypid
Statement processed.
SQL> select sum(object_id) from mac_spm;

SUM(OBJECT_ID)
--------------
    5822911440

SQL> oradebug tracefile_name
/s01/diag/rdbms/prodb/PRODB/trace/PRODB_ora_11126.trc

*** 2013-04-11 09:45:24.248
SPM: statement found in SMB
SPM: planId's of plan baseline are: 3970369213
SPM: using qksan to reproduce, cost and select accepted plan, sig = 14462506969095103776
SPM: plan reproducibility round 1 (plan outline + session OFE)
SPM: using qksan to reproduce accepted plan, planId = 3970369213
SPM: planId in plan baseline = 3970369213, planId of reproduced plan = 3970369213
SPM: best cost so far = 623.28, current accepted plan cost = 623.28
SPM: re-parse to use selected accepted plan, planId = 3970369213
SPM: statement found in SMB
SPM: re-parsing to generate selected accepted plan,  planId = 3970369213
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f6066c949f8, pmExCtx=0xc8876e48, ciP=0xc86784c0, dtCtx=0xbaf7c20

 

 

 

 

qksan是负责SQL ANALYZE 的函数,如果看到 类似如下的信息 则说明 存在过 SQL ANALYZE

sql=/* SQL Analyze(168,0) */ select sum(object_id) from mac_spm
End parsing of cur#=4 sqlid=72ph25kpkkqhs
Semantic Analysis cur#=4 sqlid=72ph25kpkkqhs

Utilize Sql Tuning Advisor from Script

Sql Tuning Advisor是10g以后出现的一个十分有用的调优工具,大多数情况下我们可以通过dbconsole或者Grid Control的web界面调用SQL Advisor;但如果系统中没有配置dbconsole或者Grid Control的话,我们则需要通过手动调用DBMS_SQLTUNE PL/SQL程序包来使用该特性。这里我列出一个针对单个SQL语句Autotune的脚本,具体脚本:


begin
  DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/



DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  my_sqlid varchar2(30);
BEGIN
  my_sqlid := '&sqlid';
  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                  scope => 'COMPREHENSIVE',
                                                  time_limit => 300,
                                                  task_name => '&task_name',
                                                  description => 'comment'                               
                                                  );
END;
/


BEGIN
  dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';

SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;

具体使用示例:

SQL> begin
  DBMS_SQLTUNE.drop_tuning_task('&task_name');
end;
/
Enter value for task_name: newtask
old   3:   DBMS_SQLTUNE.drop_tuning_task('&task_name');
new   3:   DBMS_SQLTUNE.drop_tuning_task('newtask');
begin
*
ERROR at line 1:
ORA-13605: The specified task or object newtask does not exist for the current
user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2307
ORA-06512: at "SYS.DBMS_ADVISOR", line 172
ORA-06512: at "SYS.DBMS_SQLTUNE", line 751
ORA-06512: at line 3

SQL> DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext CLOB;
  my_sqlid varchar2(30);
BEGIN
  my_sqlid := '&sqlid';
  my_task_name := dbms_sqltune.create_tuning_task(sql_id => my_sqlid,
                                                  scope => 'COMPREHENSIVE',
                                                  time_limit => 300,
                                                  task_name => '&task_name',
                                                  description => 'comment'                               
                                                  );
END;
/ 
Enter value for sqlid: 17usubxchdf2w
old  11:   my_sqlid := '&sqlid';
new  11:   my_sqlid := '17usubxchdf2w';
Enter value for task_name: new_task 
old  19:						   task_name => '&task_name',
new  19:						   task_name => 'new_task',

SQL> BEGIN
  dbms_sqltune.execute_tuning_task(task_name => '&task_name');
END;
/  
Enter value for task_name: new_task
old   3:   dbms_sqltune.execute_tuning_task(task_name => '&task_name');
new   3:   dbms_sqltune.execute_tuning_task(task_name => 'new_task');

PL/SQL procedure successfully completed.

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name';
Enter value for task_name: new_task
old   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '&task_name'
new   1: SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'new_task'

STATUS
-----------
COMPLETED

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL;
Enter value for task_name: new_task
old   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&task_name') FROM DUAL
new   1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('new_task') FROM DUAL

DBMS_SQLTUNE.REPORT_TUNING_TASK('NEW_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : new_task
Tuning Task Owner  : SYS
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 300
Completion Status  : COMPLETED
Started at	   : 03/25/2011 00:14:41
Completed at	   : 03/25/2011 00:14:45

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID	   : 17usubxchdf2w
SQL Text   : select count(t1) from hashtab

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "SYS"."HASHTAB" was not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
	    'HASHTAB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
	    method_opt => 'FOR ALL COLUMNS SIZE AUTO');

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 485915006

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	   1 |	  13 |	   2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	     |	   1 |	  13 |		  |	     |
|   2 |   TABLE ACCESS FULL| HASHTAB |	 102 |	1326 |	   2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Gather more plan statistics by gather_plan_statistics hint

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息,具体使用方法如下:

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp

SQL> select avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112

---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	    |	106 |  2438 |	  4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	107 |	749 |	  3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	  1 |	 16 |	  1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL> show parameter cursor_sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT

SQL>  show parameter statistics_level

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL

SQL> set autotrace off;

SQL> select /*+ gather_plan_statistics */   avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |    106 |00:00:00.01 |	  106 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

/* 可以从starts列看到某种操作执行了多少次,例如这里的INDEX UNIQUE SCAN为107次 */

/*也可以通过SQL_ID来定位计划信息 */


SQL> select t.* 
from v$sql s 
   , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
  

Enter value for sql_id: bctzu9xuxay18 

old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |  2438 |     4	(0)| 00:00:01 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    16 |     1	(0)| 00:00:01 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |       |     0	(0)|	      |    106 |00:00:00.01 |	  106 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
   2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - "DEPARTMENT_NAME"[VARCHAR2,30]
   5 - "D".ROWID[ROWID,10]

SQL> alter session set statistics_level=ALL;
Session altered.


/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */

利用DBMS_ADVISOR.TUNE_MVIEW包生成物化视图创建语句

不少人大概和我一样在创建物化视图的时候会犯头痛,怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后,极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义,修正物化视图日志的问题,此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新。


SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  2  USING INDEX REFRESH FAST ON DEMAND 
  3  ENABLE QUERY REWRITE 
  4  AS select distinct t1,t2 from MACLEAN.strb;
AS select distinct t1,t2 from MACLEAN.strb
                                      *
ERROR at line 4:
ORA-12015: cannot create a fast refresh materialized view from a complex query


/* 以select distinct查询语句为例,该语句本身不符合refresh fast的标准,但TUNE_MVIEW存储过程
    可以将这种查询变形使得满足快速刷新的条件 */

--    PROCEDURE DBMS_ADVISOR.TUNE_MVIEW
--    PURPOSE: Tune a Create Materialized View statement to
--    ADVISOR SUPPORT:        SQL Access Advisor
--    PARAMETERS:
--         TASK_NAME
--            The user can pass in a user-defined task name or
--            get a returned system-generated task name.
--         MV_CREATE_STMT
--            CREATE MATERIALIZED VIEW SQL statement to tune

procedure tune_mview (task_name      in out varchar2,
                      mv_create_stmt in     clob);

SQL> set serveroutput on;

SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTS 
  6                     USING INDEX REFRESH FAST ON DEMAND 
  7                     ENABLE QUERY REWRITE 
  8                     AS select distinct t1,t2 from MACLEAN.strb');
  9    dbms_output.put_line(tn);
 10  end;
 11  /
TASK_484

PL/SQL procedure successfully completed.

SQL> select script_type,statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_484'
  4   order by action_id;
 
SCRIPT_TYPE    STATEMENT

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1
UNDO           DROP MATERIALIZED VIEW MACLEAN.STRMTS

/* 可以看到TUNE_MVIEW存储过程将原查询变形为SELECT...GROUP BY的形式 */

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRB" WITH ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRB" ADD ROWID, SEQUENCE ("T1","T2")  INCLUDING NEW VALUES;
Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTS USING INDEX  REFRESH FAST WITH ROWID 
ENABLE QUERY REWRITE AS SELECT MACLEAN.STRB.T2 C1, MACLEAN.STRB.T1 C2, COUNT(*) M1 
FROM MACLEAN.STRB GROUP BY MACLEAN.STRB.T2, MACLEAN.STRB.T1;

Materialized view created.

针对那些确实无法快速刷新的复杂查询,TUNE_MVIEW过程也可能给出将一个查询分解为多个物化视图达到快速刷新和查询重写的目的:



SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTD 
  2  USING INDEX REFRESH FAST
  3  ON DEMAND ENABLE QUERY REWRITE AS 
  4  select t2,t3,count(*) from strc group by t2,t3 
  5  union all
  6  select t2,t3,count(*) from strd group by t2,t3;
select t2,t3,count(*) from strd group by t2,t3
                           *
ERROR at line 6:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> set serveroutput on;
SQL> declare
  2    tn varchar2(200);
  3  begin
  4    DBMS_ADVISOR.TUNE_MVIEW(tn,
  5                            mv_create_stmt => 'CREATE MATERIALIZED VIEW MACLEAN.STRMTC
  6                                                   USING INDEX REFRESH FAST
  7                                                   ON DEMAND ENABLE QUERY REWRITE AS
  8                                                   select t2,t3,count(*) from strc group by t2,t3
  9                                                   union all
 10                                                  select t2,t3,count(*) from strd group by t2,t3');
 11    dbms_output.put_line(tn);
 12  end;
 13  /
TASK_547

PL/SQL procedure successfully completed.

SQL> select statement
  2    from dba_tune_mview
  3   where task_name = 'TASK_547'
  4   order by action_id;

CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB1
CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2
DROP MATERIALIZED VIEW MACLEAN.STRMTC$SUB2
CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID ENABLE QUERY REWRITE AS  (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")
DROP MATERIALIZED VIEW MACLEAN.STRMTC
DBMS_ADVANCED_REWRITE.BUILD_SAFE_REWRITE_EQUIVALENCE ('MACLEAN.STRMTC$RWEQ','select t2,t3,count(*) from strc group by t2,t3
                                                 union all
                                                select t2,t3,count(*) from strd group by t2,t3',' (SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") UNION ALL  (SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2")',600916906)
 
DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('MACLEAN.STRMTC$RWEQ')

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRC" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRC" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW LOG ON "MACLEAN"."STRD" WITH ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log created.

SQL> ALTER MATERIALIZED VIEW LOG FORCE ON "MACLEAN"."STRD" ADD ROWID, SEQUENCE ("T2","T3")  INCLUDING NEW VALUES;

Materialized view log altered.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB1 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRC.T3 C1, MACLEAN.STRC.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRC GROUP BY MACLEAN.STRC.T3, MACLEAN.STRC.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC$SUB2 USING INDEX  REFRESH FAST WITH ROWID 
ON COMMIT ENABLE QUERY REWRITE AS SELECT MACLEAN.STRD.T3 C1, MACLEAN.STRD.T2 C2, COUNT(*) M1 
FROM MACLEAN.STRD GROUP BY MACLEAN.STRD.T3, MACLEAN.STRD.T2;

Materialized view created.

SQL> CREATE MATERIALIZED VIEW MACLEAN.STRMTC USING INDEX  REFRESH FORCE WITH ROWID 
ENABLE QUERY REWRITE AS  
(SELECT "STRMTC$SUB1"."C2" "T2","STRMTC$SUB1"."C1" "T3","STRMTC$SUB1"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB1" "STRMTC$SUB1") 
UNION ALL  
(SELECT "STRMTC$SUB2"."C2" "T2","STRMTC$SUB2"."C1" "T3","STRMTC$SUB2"."M1" "COUNT(*)" FROM "MACLEAN"."STRMTC$SUB2" "STRMTC$SUB2");

Materialized view created.

declare
  v_state varchar2(2000);
begin
  select statement
    into v_state
    from dba_tune_mview
   where task_name = 'TASK_547'
     and action_id = 15;
  v_state := 'begin ' || v_state || '; end;';
  dbms_output.put_line(v_state);
  execute immediate v_state;
end;
PL/SQL procedure successfully completed.

SQL> set linesize 200 pagesize 1400;
SQL> select t2,t3,count(*) from strc group by t2,t3 
  2  union all
  3  select t2,t3,count(*) from strd group by t2,t3;
no rows selected

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    74 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                    |             |       |       |            |          |
|   2 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB1 |     1 |    37 |     2   (0)| 00:00:01 |
|   3 |   MAT_VIEW REWRITE ACCESS FULL| STRMTC$SUB2 |     1 |    37 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

/* 可以看到查询成功被rewrite为对2个物化视图的扫描 */