|
кто использует SQL-Patches (dbms_sqldiag_internal.i_create_patch) в продакшене?
#39389987
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Новосибирск
Сообщения: 1 759
|
|
DBAshnik,
Сами загрузятся при hard parse.
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.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
SQL> create table t(x, y)
2 as
3 select object_id, object_id
4 from dba_objects
5 where object_id is not null;
Table created.
SQL>
SQL> create index t_x_i on t(x);
Index created.
SQL> alter table t modify x constraint t_x_nn not null;
Table altered.
SQL>
SQL> select count(y) from t;
COUNT(Y)
----------
97778
SQL>
SQL> col sql_patch for a30
SQL> col last_load_time for a26
SQL>
SQL> select child_number, sql_patch, last_load_time, invalidations
2 from v$sql
3 where sql_text = 'select count(y) from t'
4 and parsing_schema_name = user;
CHILD_NUMBER SQL_PATCH LAST_LOAD_TIME INVALIDATIONS
------------ ------------------------------ -------------------------- -------------
0 2017-01-24/08:48:38 0
SQL>
SQL> select pt.plan_table_output
2 from v$sql,
3 table(dbms_xplan.display_cursor( sql_id, child_number)) pt
4 where sql_text = 'select count(y) from t';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5sb5upa4a9nzu, child number 0
-------------------------------------
select count(y) from t
Plan hash value: 1842905362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 60 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 97778 | 477K| 60 (2)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
18 rows selected.
SQL>
SQL> exec sys.dbms_sqldiag_internal.i_create_patch( -
> sql_text => 'select count(y) from t', -
> hint_text => 'INDEX(@"SEL$1" "T"@"SEL$1")')
PL/SQL procedure successfully completed.
SQL>
SQL> select count(y) from t;
COUNT(Y)
----------
97778
SQL> select child_number, sql_patch, last_load_time, invalidations
2 from v$sql
3 where sql_text = 'select count(y) from t'
4 and parsing_schema_name = user;
CHILD_NUMBER SQL_PATCH LAST_LOAD_TIME INVALIDATIONS
------------ ------------------------------ -------------------------- -------------
0 SYS_SQLPTCH_0159ce292016000c 2017-01-24/08:48:38 1
SQL>
SQL> col address old_v addr
SQL> col hash_value old_v hv
SQL> select address, hash_value
2 from v$sqlarea
3 where sql_text = 'select count(y) from t';
ADDRESS HASH_VALUE
---------------- ----------
00000004B057A340 2292503546
SQL>
SQL> exec sys.dbms_shared_pool.purge( '&addr.,&hv.', 'c')
PL/SQL procedure successfully completed.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> select child_number, sql_patch, last_load_time, invalidations
2 from v$sql
3 where sql_text = 'select count(y) from t'
4 and parsing_schema_name = user;
no rows selected
SQL>
SQL> select count(y) from t;
COUNT(Y)
----------
97778
SQL>
SQL> select child_number, sql_patch, last_load_time, invalidations
2 from v$sql
3 where sql_text = 'select count(y) from t'
4 and parsing_schema_name = user;
CHILD_NUMBER SQL_PATCH LAST_LOAD_TIME INVALIDATIONS
------------ ------------------------------ -------------------------- -------------
0 SYS_SQLPTCH_0159ce292016000c 2017-01-24/08:48:39 0
SQL>
SQL> select pt.plan_table_output
2 from v$sql,
3 table(dbms_xplan.display_cursor( sql_id, child_number)) pt
4 where sql_text = 'select count(y) from t';
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5sb5upa4a9nzu, child number 0
-------------------------------------
select count(y) from t
Plan hash value: 2269985042
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 597 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 97778 | 477K| 597 (1)| 00:00:01 |
| 3 | INDEX FULL SCAN | T_X_I | 97778 | | 220 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- SQL patch "SYS_SQLPTCH_0159ce292016000c" used for this statement
20 rows selected.
|
|
|
