Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 39: оптимизация производительности с помощью хранимых процедур
MS SQL Решение 5.2.2.b (получение данных по всем индексам)
1 |
|
SELECT |
[tables].[name] |
AS [table_name], |
|
2 |
|
|
[indexes].[name] |
AS [index_name], |
|
3 |
|
|
[indexes].[type] |
AS [index_type], |
|
4 |
|
|
[stats].[index_type_desc] |
AS [index_type_desc], |
|
5 |
|
|
[indexes].[object_id] |
AS [index_object_id], |
|
6 |
|
|
[columns].[name] |
AS [column_name], |
|
7 |
|
|
[stats].[avg_fragmentation_in_percent] |
AS [avg_fragm_perc], |
|
8 |
|
|
[stats].[avg_page_space_used_in_percent] |
AS [avg_space_perc] |
|
9 |
|
FROM |
sys.indexes AS [indexes] |
|
|
10 |
|
|
INNER JOIN |
sys.index_columns AS [index_columns] |
|
11 |
|
|
ON |
[indexes].[object_id] = [index_columns].[object_id] |
|
12 |
|
|
|
AND [indexes].[index_id] = [index_columns].[index_id] |
|
13 |
|
|
INNER JOIN |
sys.columns AS [columns] |
|
14 |
|
|
ON |
[index_columns].[object_id] = |
[columns].[object_id] |
15 |
|
|
|
AND [index_columns].[column_id] = [columns].[column_id] |
|
16 |
|
|
INNER JOIN |
sys.tables AS [tables] |
|
17 |
|
|
ON |
[indexes].[object_id] = [tables].[object_id] |
|
18 |
|
|
INNER JOIN |
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), |
|
19 |
|
|
|
|
NULL, NULL, NULL, |
20 |
|
|
|
|
'SAMPLED') AS [stats] |
21 |
|
|
ON |
[indexes].[object_id] = [stats].[object_id] |
|
22 |
|
|
|
AND [indexes].[index_id] = [stats].[index_id] |
|
|
|
|
|
|
|
23ORDER BY [tables].[name],
24[indexes].[name],
25[indexes].[index_id],
26[index_columns].[index_column_id]
Врезультате выполнение такого запроса мы получим следующие данные.
table_name |
index_name |
in- |
index_type_desc |
index_ob- |
col- |
avg_fragm_ |
avg_space_perc |
|
|
|
dex_type |
|
ject_id |
umn_name |
|
perc |
|
authors |
PK_authors |
1 |
CLUSTERED |
245575913 |
a_id |
0 |
|
3.22461082283173 |
|
|
|
INDEX |
|
|
|
|
|
books |
PK_books |
1 |
CLUSTERED |
277576027 |
b_id |
0 |
|
5.72028663207314 |
|
|
|
INDEX |
|
|
|
|
|
genres |
PK_genres |
1 |
CLUSTERED |
309576141 |
g_id |
0 |
|
2.59451445515196 |
|
|
|
INDEX |
|
|
|
|
|
genres |
UQ_gen- |
2 |
NONCLUS- |
309576141 |
g_name |
0 |
|
2.37212750185322 |
|
res_g_name |
|
TERED INDEX |
|
|
|
|
|
m2m_books |
PK_m2m_boo |
1 |
CLUSTERED |
341576255 |
b_id |
0 |
|
1.86557944156165 |
_authors |
ks_authors |
|
INDEX |
|
|
|
|
|
m2m_books |
PK_m2m_boo |
1 |
CLUSTERED |
341576255 |
a_id |
0 |
|
1.86557944156165 |
_authors |
ks_authors |
|
INDEX |
|
|
|
|
|
m2m_books |
PK_m2m_boo |
1 |
CLUSTERED |
373576369 |
b_id |
0 |
|
2.28564368668149 |
_genres |
ks_genres |
|
INDEX |
|
|
|
|
|
m2m_books |
PK_m2m_boo |
1 |
CLUSTERED |
373576369 |
g_id |
0 |
|
2.28564368668149 |
_genres |
ks_genres |
|
INDEX |
|
|
|
|
|
subscribers |
PK_subscrib- |
1 |
CLUSTERED |
405576483 |
s_id |
0 |
|
1.95206325673338 |
|
ers |
|
INDEX |
|
|
|
|
|
subscrip- |
PK_subscrip- |
1 |
CLUSTERED |
437576597 |
sb_id |
0 |
|
5.16431924882629 |
tions |
tions |
|
INDEX |
|
|
|
|
|
Этот результат хорош тем, что удобен для просмотра человеком, но внутри хранимой процедуры нам будут нужны только три поля: имя таблицы, имя индекса, процент фрагментации. К тому же составные кластерные индексы (например, PK_m2m_books_authors) должны быть представлены только один раз (а не по разу на каждое из входящих в состав индекса полей, как это есть сейчас).
Упростим запрос так, чтобы оставить только необходимые данные.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 395/545
Пример 39: оптимизация производительности с помощью хранимых процедур
|
MS SQL |
|
Решение 5.2.2.b (получение краткого набора данных по всем индексам) |
|||
|
1 |
|
SELECT |
DISTINCT |
|
|
|
2 |
|
|
|
[tables].[name] |
AS [table_name], |
|
3 |
|
|
|
[indexes].[name] |
AS [index_name], |
|
4 |
|
|
|
[stats].[avg_fragmentation_in_percent] |
AS [avg_fragm_perc] |
|
5 |
|
FROM |
sys.indexes AS [indexes] |
|
|
|
6 |
|
|
|
INNER JOIN sys.tables AS [tables] |
|
|
7 |
|
|
|
ON [indexes].[object_id] = [tables].[object_id] |
|
|
8 |
|
|
|
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), |
|
|
9 |
|
|
|
|
NULL, NULL, NULL, |
|
10 |
|
|
|
|
'SAMPLED') AS [stats] |
|
11 |
|
|
|
ON [indexes].[object_id] = [stats].[object_id] |
|
12 |
|
|
|
AND [indexes].[index_id] = [stats].[index_id] |
||
13WHERE [indexes].[type] = 1
14ORDER BY [tables].[name],
15[indexes].[name]
Врезультате выполнение такого запроса мы получим следующие данные.
table_name |
index_name |
avg_fragm_perc |
authors |
PK_authors |
0 |
books |
PK_books |
0 |
genres |
PK_genres |
0 |
m2m_books_authors |
PK_m2m_books_authors |
0 |
m2m_books_genres |
PK_m2m_books_genres |
0 |
subscribers |
PK_subscribers |
0 |
subscriptions |
PK_subscriptions |
0 |
Теперь используем полученный запрос в теле хранимой процедуры. Проходя по всем рядам, возвращаемым курсором (цикл в строках 29-56), мы будем анализировать значение avg_fragm_perc и либо выполнять одно из двух действий по оптимизации, либо не выполнять никаких действий.
MS SQL Решение 5.2.2.b (код процедуры)
1CREATE PROCEDURE OPTIMIZE_ALL_TABLES
2AS
3BEGIN
4DECLARE @table_name NVARCHAR(200);
5DECLARE @index_name NVARCHAR(200);
6DECLARE @avg_fragm_perc DOUBLE PRECISION;
7DECLARE @query_text NVARCHAR(2000);
8DECLARE indexes_cursor CURSOR LOCAL FAST_FORWARD FOR
9SELECT DISTINCT
10 |
|
|
[tables].[name] |
AS [table_name], |
|
11 |
|
|
[indexes].[name] |
AS [index_name], |
|
12 |
|
|
[stats].[avg_fragmentation_in_percent] |
AS [avg_fragm_perc] |
|
13 |
|
FROM |
sys.indexes AS [indexes] |
|
|
14 |
|
|
INNER JOIN |
sys.tables AS [tables] |
|
15 |
|
|
ON |
[indexes].[object_id] = [tables].[object_id] |
|
16 |
|
|
INNER JOIN |
sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), |
|
17 |
|
|
|
|
NULL, NULL, NULL, |
18 |
|
|
|
|
'SAMPLED') AS [stats] |
19 |
|
|
ON |
[indexes].[object_id] = [stats].[object_id] |
|
20 |
|
|
|
AND [indexes].[index_id] = [stats].[index_id] |
|
|
|
|
|
|
|
21WHERE [indexes].[type] = 1
22ORDER BY [tables].[name],
23 |
|
[indexes].[name]; |
24 |
|
|
25OPEN indexes_cursor;
26FETCH NEXT FROM indexes_cursor INTO @table_name,
|
27 |
|
|
@index_name, |
|
|
28 |
|
|
@avg_fragm_perc; |
|
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах |
© EPAM Systems, RD Dep, 2016–2018 Стр: 396/545 |
||||
Пример 39: оптимизация производительности с помощью хранимых процедур
MS SQL Решение 5.2.2.b (код процедуры) (продолжение)
29WHILE @@FETCH_STATUS = 0
30BEGIN
31IF (@avg_fragm_perc >= 5.0) AND (@avg_fragm_perc <= 30.0)
32BEGIN
33SET @query_text = CONCAT('ALTER INDEX [', @index_name,
34 |
|
|
'] ON [', @table_name, '] REORGANIZE'); |
|
35 |
|
PRINT CONCAT('Index [', |
@index_name,'] on |
[', @table_name, |
36 |
|
'] will be |
REORGANIZED...'); |
|
|
|
|
|
|
37EXECUTE sp_executesql @query_text;
38END;
39IF (@avg_fragm_perc > 30.0)
40BEGIN
41SET @query_text = CONCAT('ALTER INDEX [', @index_name,'] ON [',
42 |
|
|
@table_name, |
'] REBUILD'); |
43 |
|
PRINT CONCAT('Index [', |
@index_name,'] on [', @table_name, |
|
44 |
|
'] will be |
REBUILT...'); |
|
45EXECUTE sp_executesql @query_text;
46END;
47IF (@avg_fragm_perc < 5.0)
48BEGIN
49PRINT CONCAT('Index [', @index_name,'] on [', @table_name,
50 |
|
'] needs no optimization...'); |
51 |
|
END; |
52 |
|
|
53 |
|
FETCH NEXT FROM indexes_cursor INTO @table_name, |
54 |
|
@index_name, |
55 |
|
@avg_fragm_perc; |
56END;
57CLOSE indexes_cursor;
58DEALLOCATE indexes_cursor;
59END;
60GO
Запустить полученную хранимую процедуру можно следующим образом.
MS SQL Решение 5.2.2.b (запуск и проверка работоспособности)
1 EXECUTE OPTIMIZE_ALL_TABLES
Логика установки запуска задачи по расписанию была рассмотрена в решении{388} задачи 5.2.2.a{388}, потому здесь просто приведём код, с помощью которого выполняется эта операция.
MS SQL |
Решение 5.2.2.b (установка запуска по расписанию) |
1USE msdb ;
2GO
3-- https://msdn.microsoft.com/en-us/library/ms182079.aspx
4EXEC dbo.sp_add_job
5@job_name = N'Hourly [books_statistics] update';
6GO
7-- https://msdn.microsoft.com/en-us/library/ms187358.aspx
8EXEC sp_add_jobstep
9@job_name = N'Hourly [books_statistics] update',
10@step_name = N'Execute UPDATE_BOOKS_STATISTICS stored procedure',
11@subsystem = N'TSQL',
12@command = N'EXECUTE UPDATE_BOOKS_STATISTICS',
13@database_name = N'library_ex_2015_mod';
14GO
15-- https://msdn.microsoft.com/en-us/library/ms187320.aspx
16EXEC dbo.sp_add_schedule
17@schedule_name = N'UpdateBooksStatistics',
18@freq_type = 4,
19@freq_interval = 4,
20@freq_subday_type = 8,
21@freq_subday_interval = 1,
22@active_start_time = 000105 ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 397/545
Пример 39: оптимизация производительности с помощью хранимых процедур
MS SQL |
Решение 5.2.2.b (установка запуска по расписанию) |
23USE msdb ;
24GO
25-- https://msdn.microsoft.com/en-us/library/ms186766.aspx
26EXEC sp_attach_schedule
27@job_name = N'Hourly [books_statistics] update',
28@schedule_name = N'UpdateBooksStatistics';
29GO
30-- https://msdn.microsoft.com/en-us/library/ms178625.aspx
31EXEC dbo.sp_add_jobserver
32@job_name = N'Hourly [books_statistics] update';
33GO
Убедиться, что событие добавлено, можно следующим запросом.
MS SQL Решение 5.2.2.b (просмотр расписания)
1 SELECT * FROM msdb.dbo.sysschedules
Если рассмотреть избранные поля результата такого запроса, получается следующая картина. Здесь также представлено событие, активирующее раз в час хранимую процедуру, созданную в процессе решения{388} задачи 5.2.2.a{388}.
Name |
enabled |
freq_type |
freq_inter- |
freq_sub- |
freq_subday_in- |
ac- |
ac- |
|
|
|
val |
day_type |
terval |
tive_start_date |
tive_start_time |
UpdateBooksStatistics |
1 |
4 |
4 |
8 |
1 |
20160427 |
000105 |
DailyOptimizeAllTables |
1 |
4 |
4 |
1 |
1 |
20160427 |
000100 |
На этом решение для MS SQL Server завершено.
Переходим к Oracle. Код создания нужной нам хранимой процедуры выглядит следующим образом.
Oracle Решение 5.2.2.b (код процедуры)
1CREATE OR REPLACE PROCEDURE OPTIMIZE_ALL_TABLES
2AS
3table_name VARCHAR(150) := '';
4query_text VARCHAR(1000) := '';
5CURSOR tables_cursor IS
6SELECT TABLE_NAME AS "table_name"
7 FROM ALL_TABLES
8WHERE OWNER=USER;
9BEGIN
10FOR one_row IN tables_cursor
11LOOP
12query_text := 'ALTER TABLE "' || one_row."table_name" ||
13 |
|
'" ENABLE ROW MOVEMENT'; |
14 |
|
DBMS_OUTPUT.PUT_LINE('Enabling row movement for "' || |
15 |
|
one_row."table_name" || '"...'); |
16 |
|
EXECUTE IMMEDIATE query_text; |
17 |
|
|
18 |
|
query_text := 'ALTER TABLE "' || one_row."table_name" || |
19 |
|
'" SHRINK SPACE COMPACT CASCADE'; |
|
|
|
20 |
|
DBMS_OUTPUT.PUT_LINE('Performing SHRINK SPACE COMPACT CASCADE on "' || |
21 |
|
one_row."table_name" || '"...'); |
22 |
|
EXECUTE IMMEDIATE query_text; |
23 |
|
|
24 |
|
query_text := 'ALTER TABLE "' || one_row."table_name" || |
25 |
|
'" DISABLE ROW MOVEMENT'; |
26 |
|
DBMS_OUTPUT.PUT_LINE('Disabling row movement for "' || |
27 |
|
one_row."table_name" || '"...'); |
28EXECUTE IMMEDIATE query_text;
29END LOOP;
30END;
31/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 398/545
Пример 39: оптимизация производительности с помощью хранимых процедур
Концепция представленного решения основана на этой рекомендации26. Итак, мы должны будем получить список всех таблиц, а затем для каждой из них сначала разрешить перемещение рядов, потом выполнить компактификацию и, наконец, снова запретить перемещение рядов. В отличие от MS SQL Server все запросы здесь совершенно тривиальны.
Запустить полученную хранимую процедуру можно следующим образом.
Oracle |
Решение 5.2.2.b (запуск и проверка работоспособности) |
1SET SERVEROUTPUT ON;
2EXECUTE OPTIMIZE_ALL_TABLES;
Теперь остаётся создать событие, запускаемое планировщиком задач Oracle по определённому расписанию. Это реализуется следующим кодом.
Oracle |
|
Решение 5.2.2.b (установка запуска по расписанию) |
||
1 |
BEGIN |
|
|
|
2 |
|
DBMS_SCHEDULER.CREATE_JOB ( |
||
3 |
|
job_name |
=> 'daily_optimize_all_tables', |
|
4 |
|
job_type |
=> 'STORED_PROCEDURE', |
|
5 |
|
job_action |
=> 'OPTIMIZE_ALL_TABLES', |
|
6 |
|
start_date |
=> |
'25-APR-16 4.00.00 PM', |
7 |
|
repeat_interval |
=> |
'FREQ=DAILY;INTERVAL=1', |
8 |
|
auto_drop |
=> |
FALSE, |
9 |
|
enabled |
=> |
TRUE); |
10 |
END; |
|
|
|
|
|
|
|
|
Убедиться, что событие добавлено, можно следующим запросом.
Oracle Решение 5.2.2.b (просмотр расписания)
1 SELECT * FROM ALL_SCHEDULER_JOBS WHERE OWNER=USER
Если рассмотреть избранные поля результата такого запроса, получается следующая картина. Здесь также представлено событие, активирующее раз в час хранимую процедуру, созданную в процессе решения{388} задачи 5.2.2.a{388}.
JOB_NAME |
JOB_STY |
JOB_TYPE |
JOB_ACTION |
START_DATE |
REPEAT_INTER- |
ENA- |
STATE |
|
LE |
|
|
|
VAL |
BLED |
|
DAILY_OPTI- |
REGU- |
STORED_PR |
OPTI- |
25-APR-27 |
FREQ=DAILY;IN |
TRU |
SCHE |
MIZE_ALL_TA- |
LAR |
OCEDURE |
MIZE_ALL_TA- |
04.00.00.000000 |
TERVAL=1 |
E |
DULE |
BLES |
|
|
BLES |
000 PM -03:00 |
|
|
D |
HOURLY_UP- |
REGU- |
STORED_PR |
UP- |
22-APR-27 |
FREQ=HOURLY |
TRU |
SCHE |
DATE_BOOKS_S |
LAR |
OCEDURE |
DATE_BOOKS_S |
04.00.00.000000 |
;INTERVAL=1 |
E |
DULE |
TATISTICS |
|
|
TATISTICS |
000 PM -03:00 |
|
|
D |
На этом решение данной задачи завершено.
Задание 5.2.2.TSK.A: создать хранимую процедуру, запускаемую по расписанию каждые 12 часов и обновляющую данные в агрегирующей таб-
лице subscriptions_ready (см. задачу 3.1.2.b{215}).
Задание 5.2.2.TSK.B: создать хранимую процедуру, запускаемую по расписанию раз в неделю и оптимизирующую (дефрагментирующую, компактифицирующую) все таблицы базы данных, в которых находится не менее одного миллиона записей.
26 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17312316112393#1765387500346472492
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 399/545