Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 39: оптимизация производительности с помощью хранимых процедур
Запрос в строках 7-10 позволяет получить список таблиц текущей базы данных (часть условия в строке 10 позволяет отличить таблицы от представлений), после чего:
•для полученного списка таблиц открывается курсор (строка 13);
•для всех строк курсора выполняется цикл (строки 15-26);
•в теле цикла формируется (строка 21) и выполняется (строки 22-23) запрос, реализующий оптимизацию таблицы.
Запустить полученную хранимую процедуру можно следующим образом.
MySQL |
Решение 5.2.2.b (запуск и проверка работоспособности) |
1 |
CALL OPTIMIZE ALL TABLES |
Теперь остаётся создать событие, запускаемое планировщиком задач MySQL по определённому расписанию. Это реализуется следующим кодом.
запуска по
1 SET GLOBAL event_scheduler = ON;
CREATE EVENT 'optimize_all_tables_daily'
4ON SCHEDULE
5EVERY 1 DAY
6STARTS DATE(NOW()) + INTERVAL 1 HOUR
7ON COMPLETION PRESERVE
8DO
9CALL OPTIMIZE ALLTABLES;
Убедиться, что событие добавлено, можно следующим запросом.
MySQL і Решение 5.2.2.b (просмотр расписания)
1 |
SELECT * FROM 'information_schema' 'events' |
| |
Если рассмотреть избранные поля результата такого запроса, получается следующая картина. Здесь также представлено событие, активирующее раз в час хранимую процедуру, созданную в процессе решения{388} задачи 5.2.2.a{388}.
EVENT_NAME |
EVENT_TYPE |
INTER- |
INTER- |
STARTS |
ENDS |
|
ON_COM- |
|
|
|
|
|
|
|
|
|
|
VAL_VALUE |
VAL_FIELD |
|
|
STATUS |
PLETION |
update_books_statis- |
RECURRING |
1 |
HOUR |
2016-04-27 |
NULL |
ENA |
PRESERVE |
|
|
|
|
|
|
||
tics hourly |
|
|
|
14:01:00 |
|
BLED |
|
|
|
|
|
|
|
|
|
optimize_all_tables |
RECURRING |
1 |
DAY |
2016-04-27 |
NULL |
ENA |
PRESERVE |
|
|
|
|
|
|
|
|
daily |
|
|
|
01:00:00 |
|
BLED |
|
|
|
|
|
|
|
|
|
На этом решение для MySQL завершено.
Переходим к MS SQL Server. Концепция представленного ниже решения основана на этой рекомендации25.
Итак, мы должны будем получить список кластерных индексов текущей базы данных, выяснить степень их фрагментации (avg_fragmentation_in_percent) и,
в зависимости от этого значения, выполнить реорганизацию (REORGANIZE) или перестроение (REBUILD) индекса.
Для начала напишем запрос, предоставляющий информацию по всем индексам, соответствующим таблицам, полям и т.д. К сожалению, готового решения MS SQL Server не предоставляет, потому придётся всё делать «вручную».
25 http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 420/545
Пример 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 |
|
[s tats] |
|
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 [s tats] |
21 |
|
ON [indexes] [object id] = [stats] [object id] |
||
22 |
|
|
AND [indexes] [indexid] = [stats] [index id] |
|
23 |
ORDER |
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_typ |
|
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_genres g |
2 |
NONCLUS |
309576141 |
g_name |
0 |
|
2.37212750185322 |
|
|
|
|
|
|
|
|
|
|
name |
|
TERED INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
m2m_books |
PK_m2m_boo ks |
1 |
CLUSTERED |
341576255 |
b_id |
0 |
|
1.86557944156165 |
authors |
authors |
|
INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
m2m_books |
PK_m2m_boo ks |
1 |
CLUSTERED |
341576255 |
a_id |
0 |
|
1.86557944156165 |
authors |
authors |
|
INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
m2m_books |
PK_m2m_boo ks |
1 |
CLUSTERED |
373576369 |
b_id |
0 |
|
2.28564368668149 |
genres |
genres |
|
INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
m2m_books |
PK_m2m_boo ks |
1 |
CLUSTERED |
373576369 |
g_id |
0 |
|
2.28564368668149 |
genres |
genres |
|
INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
subscribers |
PK_subscribers |
1 |
CLUSTERED |
405576483 |
s_id |
0 |
|
1.95206325673338 |
|
INDEX |
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
subscriptions |
PK_subscrip- |
1 |
CLUSTERED |
437576597 |
sb_id |
0 |
|
5.16431924882629 |
|
|
|
|
|
|
|
||
tions |
|
INDEX |
|
|
|
|
|
|
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
Этот результат хорош тем, что удобен для просмотра человеком, но внутри хранимой процедуры нам будут нужны только три поля: имя таблицы, имя индекса, процент фрагментации. К тому же составные кластерные индексы (например, PK_m2m_books_authors) должны быть представлены только один раз (а не по разу на каждое из входящих в состав индекса полей, как это есть сейчас).
Упростим запрос так, чтобы оставить только необходимые данные.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 421/545
Пример 39: оптимизация производительности с помощью хранимых процедур
MS SQL і |
Решение 5.2.2.b (получение краткого набора данных по всем индексам) |
| |
|
|||||
1 |
SELECT DISTINCT |
|
|
|
|
|
|
|
2 |
|
[tables] [name] |
AS [table_name], |
|
|
|
|
|
3 |
|
[indexes].[name] |
AS [index_nams], |
|
|
|
|
|
4 |
|
[stats] |
[avg_fragmentation_in_percent] |
AS |
||||
5 |
FROM |
[avg_fragm_perc] |
|
|
|
|
|
|
6 |
|
sys.indexes AS [indexes] 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, 'SAMPLED') AS [s tats] |
|||||
10 |
|
ON [indexes] [object_id] |
= [stats] |
[object_id] |
|
|||
11 |
|
AND [indexes] |
[index_id] |
= [stats] |
[index_id] |
|
||
12 |
|
[indexes].[type] = 1 BY [tables].[name], |
|
|
|
|
||
13 |
WHERE |
[indexes].[name] |
|
|
|
|
|
|
14 |
ORDER |
В результате |
выполнение |
такого запроса мы |
получим |
|||
15 |
|
|||||||
следующие данные.
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 I |
Решение 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] |
|
21 |
WHERE |
[indexes] [type] = 1 |
|
22 |
ORDER 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 Стр: 422/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_j obstep
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 Стр: 423/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 |
enable |
freq_type |
freq_inter- |
freq_subday |
freq_subday_in- |
ac |
ac |
||
|
d |
|
|
|
|
|
|
||
|
|
val |
|
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"
7FROM 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 Стр: 424/545