12.1.0.2
Есть запрос
1.
select * FROM sys."_ALL_SYNONYMS_TREE" WHERE SYN_SYNONYM_NAME = :b1 AND SYN_OWNER = :b2;
_ALL_SYNONYMS_TREE source
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYS"."_ALL_SYNONYMS_TREE" ("SYN_ID", "SYN_OWNER", "SYN_SYNONYM_NAME", "SYN_TABLE_OWNER", "SYN_TABLE_NAME", "SYN_DB_LINK", "ORIGIN_CON_ID")
AS
SELECT s.syn_id,
s.syn_owner,
s.syn_synonym_name,
s.syn_table_owner,
s.syn_table_name,
s.syn_db_link,
s.origin_con_id
FROM sys."_ALL_SYNONYMS_FOR_SYNONYMS" s
START WITH EXISTS
(SELECT
NULL
FROM sys."_ALL_SYNONYMS_FOR_AUTH_OBJECTS" sa
WHERE s.base_syn_id = sa.syn_id
AND s.origin_con_id = sa.origin_con_id
)
CONNECT BY nocycle prior s.syn_id = s.base_syn_id
AND prior s.origin_con_id = s.origin_con_id;
внутри плана есть два FILTER
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
--------------------------------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | _ALL_SYNONYMS_TREE |
| 2 | CONNECT BY NO FILTERING WITH START-WITH | |
| 3 | FILTER | |
| 4 | HASH JOIN | |
| 5 | TABLE ACCESS FULL | USER$ |
| 6 | HASH JOIN | |
| 7 | INDEX FULL SCAN | I_USER2 |
| 8 | HASH JOIN | |
| 9 | HASH JOIN | |
| 10 | INDEX FULL SCAN | I_USER2 |
| 11 | HASH JOIN | |
| 12 | TABLE ACCESS FULL | OBJ$ |
| 13 | HASH JOIN | |
| 14 | TABLE ACCESS FULL | USER$ |
| 15 | TABLE ACCESS FULL | SYN$ |
| 16 | TABLE ACCESS FULL | OBJ$ |
| 17 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 18 | NESTED LOOPS SEMI | |
| 19 | INDEX SKIP SCAN | I_USER2 |
| 20 | INDEX RANGE SCAN | I_OBJ4 |
| 21 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 22 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 23 | NESTED LOOPS SEMI | |
| 24 | INDEX SKIP SCAN | I_USER2 |
| 25 | INDEX RANGE SCAN | I_OBJ4 |
| 26 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 27 | FILTER | |
| 28 | FILTER | |
| 29 | NESTED LOOPS | |
| 30 | NESTED LOOPS | |
| 31 | NESTED LOOPS | |
| 32 | TABLE ACCESS BY INDEX ROWID | SYN$ |
| 33 | INDEX UNIQUE SCAN | I_SYN1 |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ |
| 35 | INDEX RANGE SCAN | I_OBJ1 |
| 36 | INDEX RANGE SCAN | I_USER2 |
| 37 | INDEX RANGE SCAN | I_USER2 |
| 38 | FILTER | |
| 39 | FILTER | |
| 40 | NESTED LOOPS | |
| 41 | NESTED LOOPS | |
| 42 | NESTED LOOPS | |
| 43 | TABLE ACCESS BY INDEX ROWID | USER$ |
| 44 | INDEX UNIQUE SCAN | I_USER1 |
| 45 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ |
| 46 | INDEX RANGE SCAN | I_OBJ5 |
| 47 | INDEX RANGE SCAN | I_USER2 |
| 48 | INDEX RANGE SCAN | I_OBJAUTH1 |
| 49 | FIXED TABLE FULL | X$KZSRO |
| 50 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 51 | NESTED LOOPS SEMI | |
| 52 | INDEX SKIP SCAN | I_USER2 |
| 53 | INDEX RANGE SCAN | I_OBJ4 |
| 54 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 55 | FIXED TABLE FULL | X$KZSPR |
| 56 | TABLE ACCESS FULL | USER_EDITIONING$ |
| 57 | NESTED LOOPS SEMI | |
| 58 | INDEX SKIP SCAN | I_USER2 |
| 59 | INDEX RANGE SCAN | I_OBJ4 |
| 60 | TABLE ACCESS FULL | USER_EDITIONING$ |
--------------------------------------------------------------------------
Задача такая - создать синонимы (в любом виде) так, чтобы эти два шага вызывались около 100 раз.
С первым FILTER достаточно просто, достаточно создать 100 синонимов для синонимов.
1.
2.
3.
4.
5.
6.
begin
for x in 1..100 loop
execute immediate 'create synonym testsyn' || x || ' for sys.dual';
execute immediate 'create synonym syn4syn' || x || ' for testsyn' || x;
end loop;
end;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
exec :b1 := 'MGMT_BSLN'
exec :b2 := 'DBSNMP'
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.09 | 3697 |
| 1 | VIEW | _ALL_SYNONYMS_TREE | 1 | 2 | 0 |00:00:00.09 | 3697 |
| 2 | CONNECT BY NO FILTERING WITH START-WITH | | 1 | | 100 |00:00:00.09 | 3697 |
| 3 | FILTER | | 1 | | 100 |00:00:00.08 | 3066 |
| 4 | HASH JOIN | | 1 | 106 | 100 |00:00:00.08 | 3060 |
| 5 | TABLE ACCESS FULL | USER$ | 1 | 63 | 124 |00:00:00.01 | 9 |
| 6 | HASH JOIN | | 1 | 106 | 100 |00:00:00.08 | 3051 |
| 7 | INDEX FULL SCAN | I_USER2 | 1 | 63 | 124 |00:00:00.01 | 1 |
| 8 | HASH JOIN | | 1 | 106 | 100 |00:00:00.08 | 3050 |
| 9 | HASH JOIN | | 1 | 106 | 100 |00:00:00.04 | 1638 |
| 10 | INDEX FULL SCAN | I_USER2 | 1 | 63 | 124 |00:00:00.01 | 1 |
| 11 | HASH JOIN | | 1 | 106 | 100 |00:00:00.03 | 1637 |
| 12 | TABLE ACCESS FULL | OBJ$ | 1 | 5101 | 37282 |00:00:00.01 | 1412 |
| 13 | HASH JOIN | | 1 | 5101 | 37278 |00:00:00.01 | 225 |
| 14 | TABLE ACCESS FULL | USER$ | 1 | 63 | 124 |00:00:00.01 | 9 |
| 15 | TABLE ACCESS FULL | SYN$ | 1 | 5101 | 37282 |00:00:00.01 | 216 |
| 16 | TABLE ACCESS FULL | OBJ$ | 1 | 5101 | 37282 |00:00:00.02 | 1412 |
| 17 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 1 | 0 |00:00:00.01 | 3 |
| 18 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 19 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 20 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 21 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 |
| 22 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 1 | 0 |00:00:00.01 | 3 |
| 23 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 24 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 25 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 |
| 27 | FILTER | | 100 | | 100 |00:00:00.01 | 631 |
| 28 | FILTER | | 100 | | 100 |00:00:00.01 | 616 |
| 29 | NESTED LOOPS | | 100 | 1 | 100 |00:00:00.01 | 616 |
| 30 | NESTED LOOPS | | 100 | 1 | 100 |00:00:00.01 | 516 |
| 31 | NESTED LOOPS | | 100 | 1 | 100 |00:00:00.01 | 416 |
| 32 | TABLE ACCESS BY INDEX ROWID | SYN$ | 100 | 1 | 100 |00:00:00.01 | 205 |
| 33 | INDEX UNIQUE SCAN | I_SYN1 | 100 | 1 | 100 |00:00:00.01 | 105 |
| 34 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 100 | 1 | 100 |00:00:00.01 | 211 |
| 35 | INDEX RANGE SCAN | I_OBJ1 | 100 | 1 | 100 |00:00:00.01 | 111 |
| 36 | INDEX RANGE SCAN | I_USER2 | 100 | 1 | 100 |00:00:00.01 | 100 |
| 37 | INDEX RANGE SCAN | I_USER2 | 100 | 1 | 100 |00:00:00.01 | 100 |
| 38 | FILTER | | 1 | | 1 |00:00:00.01 | 12 |
| 39 | FILTER | | 1 | | 1 |00:00:00.01 | 9 |
| 40 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 9 |
| 41 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 42 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 |
| 43 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 1 | 1 |00:00:00.01 | 2 |
| 44 | INDEX UNIQUE SCAN | I_USER1 | 1 | 1 | 1 |00:00:00.01 | 1 |
| 45 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 1 | 1 |00:00:00.01 | 4 |
| 46 | INDEX RANGE SCAN | I_OBJ5 | 1 | 1 | 1 |00:00:00.01 | 3 |
| 47 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | 1 |00:00:00.01 | 1 |
| 48 | INDEX RANGE SCAN | I_OBJAUTH1 | 1 | 1 | 1 |00:00:00.01 | 2 |
| 49 | FIXED TABLE FULL | X$KZSRO | 1 | 1 | 1 |00:00:00.01 | 0 |
| 50 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 1 | 0 |00:00:00.01 | 3 |
| 51 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 52 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 53 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 54 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 |
| 55 | FIXED TABLE FULL | X$KZSPR | 0 | 1 | 0 |00:00:00.01 | 0 |
| 56 | TABLE ACCESS FULL | USER_EDITIONING$ | 1 | 1 | 0 |00:00:00.01 | 3 |
| 57 | NESTED LOOPS SEMI | | 0 | 1 | 0 |00:00:00.01 | 0 |
| 58 | INDEX SKIP SCAN | I_USER2 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 59 | INDEX RANGE SCAN | I_OBJ4 | 0 | 1 | 0 |00:00:00.01 | 0 |
| 60 | TABLE ACCESS FULL | USER_EDITIONING$ | 0 | 1 | 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------------------------------------
Со вторым фильтром застрял, голова не варит. Если кто хочет размять мозги - милости просим.
