Стратегия настройки SQL запросов. Новые возможности на основе пакета dbms_sqltune Борчук Леонид

RuOUG:Стратегия настройки SQL запросов Задача: Уменьшить время ответа

RuOUG:Стратегия настройки SQL запросов Методы решения задачи настройки Время ответа = Время обработки + Время ожидания 100% = 75% + 25% Модель времени ответа в системе с разделением ресурсов: ЛокальныеГлобальные Частота и количество процессоров Размер буфера Скорость дисковой подсистемы Увеличение не более 10% Уменьшить количество итераций при выполнении запроса или количество затрат ресурсов за счет изменения способа обработки Уменьшить затраты ресурсов в разы

RuOUG:Стратегия настройки SQL запросов Проблемы локальных методов настройки Методы настройки По классификации К. Миллсапа: Метод R Метод Время ответа Метод C Метод гипотез Как строить временную диаграмму, если отчет выполняется 5 суток? Зачем строить диаграмму, если проблемный запрос итак ясен? Что делать с 25% неизмеренного времени? Как правильно выбрать метрику для настройки? Когда следует прекратить настройку по метрике? Как оценить результаты настройки?

RuOUG:Стратегия настройки SQL запросов Пример. Метрика время выполнения SELECT /*+ INDEX(arc_portfolio ARC_PRT_FINE_IN_CNT_IDX) */ * FROM arc_portfolio WHERE prt_fine_in_vir = ' ' AND prt_fine_res_vir = ' ' SELECT /*+ INDEX(arc_portfolio ARC_PRT_FINE_RES_CNT_IDX) */ * FROM arc_portfolio WHERE prt_fine_in_vir = ' ' AND prt_fine_res_vir = ' ' | Id | Operation | Name | Rows | Cost (%CPU)| | 0 | SELECT STATEMENT | | | 459 (2)| |* 1 | TABLE ACCESS BY INDEX ROWID| ARC_PORTFOLIO | | 459 (2)| |* 2 | INDEX FULL SCAN | ARC_PRT_FINE_RES_CNT_IDX | 469 | 7 (0)| | Id | Operation | Name | Rows | Cost (%CPU)| | 0 | SELECT STATEMENT | | | 354 (2)| |* 1 | TABLE ACCESS BY INDEX ROWID| ARC_PORTFOLIO | | 354 (2)| |* 2 | INDEX SKIP SCAN | ARC_PRT_FINE_IN_CNT_IDX | 622 | 30 (0)| Elapsed: 00:00: consistent gets Elapsed: 00:00: consistent gets 1. Какой из вариантов запроса использовать, если их время выполнения одинаково? 2. Как не учитывать время ожидания на исполнение?

RuOUG:Стратегия настройки SQL запросов Пример. Стоимость SELECT a.*,fin_system.get_value (dpt_bonus, 'dpr_type') AS prt_dpr_type FROM arc_portfolio a, dic_portfolio_type WHERE prt_dt_buh fin_system.get_buh_date AND prt_dpt_id = dpt_id AND prt_deleted = | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | 0 | SELECT STATEMENT | | 2 | 538 | 73 (16)| 00:00:01 | | 1 | NESTED LOOPS | | 2 | 538 | 73 (16)| 00:00:01 | |* 2 | TABLE ACCESS FULL | ARC_PORTFOLIO | 2 | 426 | 71 (16)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| DIC_PORTFOLIO_TYPE | 1 | 56 | 1 (0)| 00:00:01 | |* 4 | INDEX UNIQUE SCAN | DPT_ID | 1 | | 0 (0)| 00:00:01 | RowsRow Source Operation 160 NESTED LOOPS (cr=281 pr=35 pw=0 time=0.0399s) TABLE ACCESS FULL ARC_PORTFOLIO (cr=109 pr=31 pw=0 time=0.0378s) TABLE ACCESS BY INDEX ROWID DIC_PORTFOLIO_TYPE (cr=172 pr=4 pw=0 time=0.0018s) INDEX UNIQUE SCAN DPT_ID (cr=12 pr=0 pw=0 time=0.0006s)

RuOUG:Стратегия настройки SQL запросов Стратегия Метрики Способы настройки Методы оценки результатов Неоднозначности Универсального метода настройки не существует Стратегия настройки – общий, недетализированный план настройки, охватывающий длительный период времени, способ достижения сложной цели, являющейся неопределённой и в дальнейшем корректируемой под изменившиеся условия.

RuOUG:Стратегия настройки SQL запросов Проблемы стратегий настройки Проблема: В большинстве случаев стратегия настройки определена неявно, в результате в процессе настройки происходит подмена цели. Примеры: Метод время ответа: Цель - построить временную диаграмму. Средство – трассировка Недостатки – требует полного выполнения операции, занимает большие объемы данных, содержит ошибки измерения. Настройка на основе правил: Цель – добиться красивого плана выполнения. Средство – избавиться от полного сканирования таблиц, hash и merge join. Настройка на основе метрик. Цель – минимизировать количество логических чтений. Средство – анализ статистики выполнения.

RuOUG:Стратегия настройки SQL запросов Стратегия локальной настройки Частная задача – настройка плана выполнения запроса. Стратегия: Локальная цель – получить любой план выполнения запроса, отвечающий требованиям производительности. Средство – добиться адекватности оценок стоимостного оптимизатора. Получить требуемое время выполнения не всегда возможно. Адекватные оценки – необходимое условие выбора оптимального способа выполнения. Преимущества предлагаемой стратегии – минимизация времени выполнения настройки. Wolfgang Breitling SQL Tuning with Statistics

RuOUG:Стратегия настройки SQL запросов План действий локальной настройки Определить текущий план выполнения Получить актуальную статистику плана выполнения Определить момент возникновения ошибки оценки Определить предикаты, вызывающие неправильную оценку Исправить статистику

RuOUG:Стратегия настройки SQL запросов Пример. История изменений SELECT a.*,fin_system.get_value (dpt_bonus, 'dpr_type') AS prt_dpr_type FROM arc_portfolio a, dic_portfolio_type WHERE prt_dt_buh fin_system.get_buh_date AND prt_dpt_id = dpt_id AND prt_deleted = 0 SELECT * FROM curr_portfolio

RuOUG:Стратегия настройки SQL запросов Пример. Представление с версионностью SELECT * FROM curr_portfolio СоотношениеСтрокОценкаОперация SELECT STATEMENT NESTED LOOPS TABLE ACCESS FULL ARC_PORTFOLIO TABLE ACCESS BY INDEX ROWID DIC_PORTFOLIO_TYPE INDEX UNIQUE SCAN DPT_ID PRT_DELETED=0 AND PRT_DT_BUH FIN_SYSTEM.GET_BUH_DATE() ПредикатСелективность PRT_DT_BUH 0,0500 PRT_DT_NEXT 0,0500 PRT_DELETED 0,9764

RuOUG:Стратегия настройки SQL запросов Пример. Настройка представления SELECT * FROM v$sql WHERE UPPER (sql_text) LIKE '%CURR_PORTFOLIO%'; variable stmt_task VARCHAR2(64); EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id =>'94j7a7adr62jd'); EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task); EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task); OperationNameRowsCost SELECT STATEMENT MERGE JOIN TABLE ACCESS BY INDEX ROWIDDIC_PORTFOLIO_TYPE 148 INDEX FULL SCANDPT_ID 141 SORT JOIN TABLE ACCESS FULLARC_PORTFOLIO Note - SQL profile "SYS_SQLPROF_01492e8d033f0001" used for this statement

RuOUG:Стратегия настройки SQL запросов SQL Profile Tom Kyte Jonathan Lewis Wolfgang Breitling Christian Antognini Деев Илья

RuOUG:Стратегия настройки SQL запросов Уточнение статистики по таблице TABLE, SCALE_ROWS= ) Table: ARC_PORTFOLIO Alias: A Card: Original: 934 >> Single Tab Card adjusted from: 2.28 to: Rounded: 160 Computed: Non Adjusted: 2.28 EXEC DBMS_STATS.SET_TABLE_STATS(ownname=>'ABSMAIN',tabname => 'ARC_PORTFOLIO', numrows => 18000); OperationNameRowsCost SELECT STATEMENT HASH JOIN TABLE ACCESS FULL DIC_PORTFOLIO_TYPE1415 TABLE ACCESS FULL ARC_PORTFOLIO

RuOUG:Стратегия настройки SQL запросов Уточнение статистики по соединению JOIN, SCALE_ROWS= ) Join order[294]: ARC_PORTFOLIO[A]#1 ARC_CREDIT[A]#4 DIC_PORTFOLIO_TYPE[DIC_PORTFOLIO_TYPE]#0 ARC_CONTRACT[T]#2 DAT_CREDITCOUNTS[DAT_CREDITCOUNTS]#5 ARC_COUNT3[A]#3 ARC_COUNT1[A]#6 *************** Now joining: ARC_CREDIT[A]#4 *************** NL Join Outer table: Card: Cost: Resp: Degree: 1 Bytes: 57 Inner table: ARC_CREDIT Alias: A Join Card: = outer (161.58) * inner ( ) * sel ( ) >> Join Card adjusted from to: , prelen=2 Adjusted Join Cards: adjRatio=0.27 cardHjSmj= cardHjSmjNPF= cardNlj= cardNSQ= cardNSQ_na= Join Card - Rounded: Computed:

RuOUG:Стратегия настройки SQL запросов Другие способы уточнения статистики OPT_ESTIMATE(, INDEX_FILTER,,, SCALE_ROWS= ) Уточнение статистики по индексу: Уточнение отсутствующей или устаревшей статистики по объектам : TABLE_STATS(, scale, blocks=405 rows=6116) COLUMN_STATS(,, scale, length=8) INDEX_STATS(,, scale, blocks=56 index_rows=6116)

RuOUG:Стратегия настройки SQL запросов Принцип бритвы Оккама Когда ученики Платона попросили дать определение человека, философ сказал: «Человек есть животное о двух ногах, лишённое перьев». Услышав это, Диоген Синопский поймал петуха, ощипал его и, принеся в Академию, объявил: «Вот платоновский человек!». После чего Платон вынужден был добавить к своему определению: «И с плоскими ногтями». Бритва (лезвие) Оккама методологический принцип. В упрощенном виде он гласит: «Не следует множить сущее без необходимости» (либо «Не следует привлекать новые сущности без самой крайней на то необходимости»). Аналогично, в профиле должен присутствовать минимальный набор статистик, используемый для получения адекватных оценок. По мере необходимости набор статистик следует уточнять.

RuOUG:Стратегия настройки SQL запросов Модернизация статистик профиля EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name,schema_name); EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name, staging_schema_owner, profile_name); EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE, staging_table_name, staging_schema_owner); OperationNameRowsCost SELECT STATEMENT MERGE JOIN TABLE ACCESS BY INDEX ROWIDDIC_PORTFOLIO_TYPE 148 INDEX FULL SCANDPT_ID 141 SORT JOIN TABLE ACCESS FULLARC_PORTFOLIO 11471

RuOUG:Стратегия настройки SQL запросов Cost-based transformation Разбор грамматики Эвристическое преобразование Преобразование, основанное на стоимости Физическая оптимизация VLDB Sep 06 Cost-based query transformation in Oracle ahmed.pdf?key1= &key2= &coll=&dl= ACM&CFID= &CFTOKEN= VLDB 1994 Query Optimization by Predicate Move-Around

RuOUG:Стратегия настройки SQL запросов Query block SELECT e1.employee_name, j.job_title FROM employees e1, job_history j WHERE e1.emp_id = j.emp_id and j.start_date > ' ' and e1.salary > (SELECT AVG (e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) and e1.dept_id IN (SELECT dept_id FROM departments d, locations l WHERE d.loc_id = l.loc_id and l.country_id = 'US'); SELECT e1.employee_name, j.job_title FROM employees e1, job_history j, (SELECT AVG(e2.salary) avg_sal, dept_id FROM employees e2 GROUP BY dept_id) V WHERE e1.emp_id = j.emp_id and j.start_date > ' ' and e1.dept_id = V.dept_id and e1.salary > V.avg_sal and e1.dept_id IN (SELECT dept_id FROM departments d, locations l WHERE d.loc_id = l.loc_id and l.country_id = 'US'); SUBQUERY UNNESTING SELECT e1.employee_name, j.job_title FROM employees e1, job_history j, (SELECT DISTINCT dept_id FROM departments d, locations l WHERE d.loc_id = l.loc_id and l.country_id IN ('US')) VD WHERE e1.emp_id = j.emp_id and j.start_date > ' ' and e1.salary > (SELECT AVG (e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) and e1.dept_id = VD.dept_id; DISTINCT VIEW СостояниеQuery blocks "(0,0)"Qs1 Qs2 Qo "(1,0)"T(Qs1) Qs2 Qo "(0,1)"Qs1 T(Qs2) Qo "(1,1)"T(Qs1) T(Qs2) Qo

RuOUG:Стратегия настройки SQL запросов QB_NAME SELECT /*+ QB_NAME(qb) e) */ employee_id, last_name FROM employees e WHERE last_name = 'Smith'; Query block name изменяется при трансформации запроса: JPPD - join predicate push-down FPD - filter push-down PM - predicate move-around CVM - complex view merging SPJ - select-project-join SJC - set join conversion SU - subquery unnesting OBYE - order by elimination ST - star transformation EVENT 10053: ************************** Predicate Move-Around (PM) ************************** PM: Considering predicate move-around in SEL$15 (#0). Query block (c a75658) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT … Query block (c a75658) unchanged

RuOUG:Стратегия настройки SQL запросов Пример. Запрос Количество строк запроса: 124 Количество шагов плана выполнения до применения профиля: 82 Количество шагов плана выполнения после применения профиля: 74 Время выполнения до применения профиля: 14 с Время выполнения после применения профиля: 8 c Время построения профиля: 20 мин Процент адекватных оценок кардинальности: 70% Количество блоков запроса; 15 Количество хинтов QB_NAME: 6 Количество оставшихся названий Query Block: 1

RuOUG:Стратегия настройки SQL запросов Резюме 1.Для настройки требуется стратегия. 2.Для задачи настройки плана выполнения конкретного запроса одна из выигрышных стратегий состоит в обеспечении адекватности оценок стоимостного оптимизатора. 3.В последнее время появляются новые средства для реализации стратегии – например, пакет dbms_sqltune. 4.Адекватность оценок стоимостного оптимизатора требуется для реализации других стратегий.

RuOUG:Стратегия настройки SQL запросов Вопросы и ответы ?.?.?. Борчук Леонид Администратор БД, г. Череповец