No_merge is used to solve inefficient access to data dictionary views

Transferred from:

Access to the data dictionary view of the database is usually relatively fast, because Oracle has made special optimization work, but sometimes, when several data dictionaries are associated, CBO is not so intelligent, and sometimes it provides some very inefficient execution plans.

For example, when trying to construct a large table, we want to construct the dba_objects and the dba_sequences product of another table with a small amount of data:

[Oracle @ localhost ~] $ Sqlplus test/test

SQL * Plus: Release 9.2.0.4.0-production on
Thursday June 4 16:39:58 2009

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connect:

Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production

SQL> set pages 100 lines 128
SQL> Col plan_plus_exp format a115
SQL> set timing on
SQL> select count (*) from dba_sequences, dba_objects;

Count (*)
----------
4585276

Used time: 00: 31: 19.33

This query has been running for half an hour.

SQL> select count (*) from dba_sequences;

Count (*)
----------
146

Used time: 00: 00: 00.08
SQL> select count (*) from dba_objects;

Count (*)
----------
31406

Used time: 00: 00: 00.08

The data volume of both views is not very large, and the result of the flute product is nothing more than 4 million. How can it be executed for such a long time:

SQL> explain Plan
2 select count (*) from dba_sequences, dba_objects;

Explained.

Used time: 00: 00: 00.00
SQL> select * from table (dbms_xplan.display );

Plan_table_output
Bytes ------------------------------------------------------------------------------------

Bytes -----------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost |
Bytes -----------------------------------------------------------------------------------
| 0 | SELECT statement | ||||
| 1 | sort aggregate |
| 2 | nested loops |
| 3 | nested loops |
| 4 | nested loops |
| 5 | View | dba_objects |
| 6 | Union-all |
| * 7 | filter |
| 8 | table access by index rowid | OBJ $ |
| 9 | nested loops |
| 10 | table access full | user $ |
| * 11 | index range scan | I _obj2 |
| * 12 | table access by index rowid | ind $ |
| * 13 | index unique scan | I _ind1 |
| 14 | nested loops |
| 15 | table access full | user $ |
| * 16 | index range scan | I _link1 |
| 17 | table access full | user $ |
| 18 | table access by index rowid | OBJ $ |
| * 19 | index range scan | I _obj2 |
| * 20 | index unique scan | I _seq1 |
Bytes -----------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

7-filter ("sys_alias_1". "Type #" <> 1 and "sys_alias_1". "Type #" <> 10 or
"Sys_alias_1". "Type #" = 1 and (select 1 from "sys". "IND $" I "where" I "." OBJ # "=: B1
And ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or
"I". "Type #" = 6 or "I". "Type #" = 7 or "I". "Type #" = 9) = 1)
11-access ("sys_alias_1". "Owner #" = "U". "user #" and "sys_alias_1". "linkname" is
Null)
Filter ("sys_alias_1". "linkname" is null and
"Sys_alias_1". "name" <> '_ default_auditing_options _' and
"Sys_alias_1". "name" <> '_ next_object ')
12-filter ("I ". "Type #" = 1 or "I ". "Type #" = 2 or "I ". "Type #" = 3 or "I ". "Type #" = 4 or
"I". "Type #" = 6 or "I". "Type #" = 7 or "I". "Type #" = 9)
13-access ("I". "OBJ #" =: B1)
16-access ("L". "Owner #" = "U". "user #")
19-access ("U". "user #" = "o". "Owner #")
20-access ("o". "OBJ #" = "S". "OBJ #")

Note: Rule Based Optimization

47 rows have been selected.

Used time: 00: 00: 00.33

Check the execution plan and find that, because some data in the two views comes from the same internal cluster Table, CBO intelligently modifies the execution plan and splits the queries in the two views, re-constructed the execution plan.

It turns out that this execution plan is very bad. Since a single view is optimized by Oracle, there is no performance problem when accessing any view separately. Therefore, the no_merge prompt is used to prevent oracle from breaking up View queries:

SQL> set autot on
SQL> select/* + no_merge (a) no_merge (B) */count (*)
2 from dba_sequences A, dba_objects B;

Count (*)
----------
4585276

Used time: 00: 00: 00.60

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = choose (cost = 204251 card = 1)
1 0 sort (aggregate)
2 1 merge join (Cartesian) (cost = 204251 card = 66724392)
3 2 view of 'dba _ objects' (cost = 26 card = 8169)
4 3 Union-all
5 4 Filter
6 5 nested loops (cost = 12 card = 1 bytes = 135)
7 6 Table Access (full) of 'obj $ '(cost = 11 card = 1 bytes = 122)
8 6 Table Access (cluster) of 'User $ '(cost = 1 card = 1 bytes = 13)
9 8 index (unique scan) of 'I _ user #' (NON-UNIQUE)
10 5 Table Access (by index rowid) of 'ind $ '(cost = 2 card = 1 bytes = 26)
11 10 index (unique scan) of 'I _ ind1' (unique) (cost = 1 card = 8168)
12 4 hash join (cost = 14 card = 8168 bytes = 212368)
13 12 Table Access (full) of 'link $ '(cost = 2 card = 82 bytes = 1066)
14 12 Table Access (full) of 'User $ '(cost = 11 card = 8168 bytes = 106184)
15 2 buffer (SORT) (cost = 204251 card = 8168)
16 15 views of 'dba _ sequences '(cost = 25 card = 8168)
17 16 nested loops (cost = 25 card = 8168 bytes = 424736)
18 17 hash join (cost = 25 card = 8168 bytes = 318552)
19 18 Table Access (full) of 'User $ '(cost = 11 card = 8168 bytes = 106184)
20 18 Table Access (full) of 'obj $ '(cost = 11 card = 8168 bytes = 212368)
21 17 index (unique scan) of 'I _ seq1' (unique)

Statistics
----------------------------------------------------------
14 recursive CILS
0 dB block gets
68435 consistent gets
0 physical reads
0 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
1 rows processed

It can be seen that the efficiency of the two is quite different. It takes more than 30 minutes for one instance and less than 1 second for the other instance to be executed. From the execution plan point of view, after the no_merge prompt is used, Oracle does not scatter the execution plans of the two views, but directly performs the flute product on the access results of the two views, this is what we hope to achieve.

This method is not a special case. It can be used to solve many performance problems in accessing the data dictionary view.

Some people may doubt that from the execution plan, it is obvious that the first SQL is RBO, while the second SQL is added with hint, And the optimizer uses CBO, whether the efficiency is improved because CBO is superior to RBO.

In fact, using CBO can indeed improve access efficiency. At least the execution plan is not so outrageous, but it is inferior to the no_merge prompt:

SQL> select/* + all_rows */count (*) from dba_sequences, dba_objects;

Count (*)
----------
4585276

Used time: 00: 00: 02.78

Execution Plan
----------------------------------------------------------
0 SELECT statement optimizer = hint: all_rows (cost = 50919 card = 1 bytes = 52)
1 0 sort (aggregate)
2 1 hash join (cost = 50919 card = 66724392 bytes = 3469668384)
3 2 Table Access (full) of 'User $ '(cost = 11 card = 8168 bytes = 106184)
4 2 hash join (cost = 40483 card = 66724392 bytes = 2602251288)
5 4 Table Access (full) of 'obj $ '(cost = 11 card = 8168 bytes = 212368)
6 4 merge join (Cartesian) (cost = 32702 card = 66724392 bytes = 867417096)
7 6 views of 'dba _ objects' (cost = 26 card = 8169)
8 7 Union-all
9 8 filter
10 9 nested loops (cost = 12 card = 1 bytes = 135)
11 10 Table Access (full) of 'obj $ '(cost = 11 card = 1 bytes = 122)
12 10 Table Access (cluster) of 'User $ '(cost = 1 card = 1 bytes = 13)
13 12 index (unique scan) of 'I _ user #' (NON-UNIQUE)
14 9 Table Access (by index rowid) of 'ind $ '(cost = 2 card = 1 bytes = 26)
15 14 index (unique scan) of 'I _ ind1' (unique) (cost = 1 card = 8168)
16 8 hash join (cost = 14 card = 8168 bytes = 212368)
17 16 Table Access (full) of 'link $ '(cost = 2 card = 82 bytes = 1066)
18 16 Table Access (full) of 'User $ '(cost = 11 card = 8168 bytes = 106184)
19 6 buffer (SORT) (cost = 32702 card = 8168 bytes = 106184)
20 19 index (Fast full scan) of 'I _ seq1' (unique) (cost = 4 card = 8168 bytes = 106184)

Statistics
----------------------------------------------------------
14 recursive CILS
0 dB block gets
68436 consistent gets
1 physical reads
0 redo size
379 bytes sent via SQL * Net to client
503 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
1 rows processed

It can be seen that the use of CBO is much better than that of RBO, but the execution time is still more than 4 times that of the use of no_merge.