1 / 25

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

Борчук Леонид. Стратегия настройки SQL запросов. Новые возможности на основе пакета dbms_sqltune. Задача : Уменьшить время ответа. Методы решения задачи настройки. Модель времени ответа в системе с разделением ресурсов :. Время ответа = Время обработки + Время ожидания

tal
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

  6. Пример. Стоимость SELECT a.*,fin_system.get_value (dpt_bonus,'dpr_type')AS prt_dpr_type FROMarc_portfolio a,dic_portfolio_type WHERE prt_dt_buh <=fin_system.get_buh_date AND prt_dt_next >fin_system.get_buh_date AND prt_dpt_id = dpt_id AND prt_deleted =0 --------------------------------------------------------------------------------------------------- | 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 | --------------------------------------------------------------------------------------------------- RuOUG:Стратегия настройки SQL запросов. 11.11.2009

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

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

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

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

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

  12. Пример. Представление с версионностью SELECT*FROM curr_portfolio PRT_DELETED=0 AND PRT_DT_BUH<=FIN_SYSTEM.GET_BUH_DATE() AND PRT_DT_NEXT>FIN_SYSTEM.GET_BUH_DATE() RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  13. Пример. Настройка представления 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'); EXECDBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task); EXECDBMS_SQLTUNE.ACCEPT_SQL_PROFILE(:stmt_task); Note - SQL profile "SYS_SQLPROF_01492e8d033f0001" used for this statement RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  14. SQL Profile Tom Kyte http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:61313086268493 Jonathan Lewis http://jonathanlewis.wordpress.com/2007/02/11/profiles/ Wolfgang Breitling http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf Christian Antognini http://antognini.ch/papers/SQLProfiles_20060622.pdf Деев Илья http://www.ruoug.org/library/2/index.html RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  15. Уточнение статистики по таблице OPT_ESTIMATE(@"SEL$F5BB74E1", TABLE, "A"@"SEL$2", SCALE_ROWS=70.09857612) Table: ARC_PORTFOLIO Alias: A Card: Original: 934 >> Single Tab Card adjusted from: 2.28 to: 160.00 Rounded: 160 Computed: 160.00 Non Adjusted: 2.28 EXECDBMS_STATS.SET_TABLE_STATS(ownname=>'ABSMAIN',tabname =>'ARC_PORTFOLIO', numrows =>18000); RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  16. Уточнение статистики по соединению OPT_ESTIMATE(@"SEL$40F01EED", JOIN, ("A"@"SEL$3", "A"@"SEL$2"), SCALE_ROWS=0.2652425153) 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: 161.58 Cost: 70.89 Resp: 70.89 Degree: 1 Bytes: 57 Inner table: ARC_CREDIT Alias: A Join Card: 63089.65 = outer (161.58) * inner (20836.88) * sel (0.018739) >> Join Card adjusted from 63089.65 to: 16734.06, prelen=2 Adjusted Join Cards: adjRatio=0.27 cardHjSmj=16734.06 cardHjSmjNPF=16734.06 cardNlj=16734.06 cardNSQ=16734.06 cardNSQ_na=63089.65 Join Card - Rounded: 16734 Computed: 16734.06 RuOUG:Стратегия настройки SQL запросов. 11.11.2009

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

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

  19. Модернизация статистик профиля EXECDBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name,schema_name); EXECDBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name,staging_schema_owner,profile_name); EXECDBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace=>TRUE, staging_table_name,staging_schema_owner); RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  20. Cost-based transformation Разбор грамматики VLDB Sep 06 Cost-based query transformation in Oracle http://delivery.acm.org/10.1145/1170000/1164215/p1026-ahmed.pdf?key1=1164215&key2=7529733711&coll=&dl=ACM&CFID=15151515&CFTOKEN=6184618 VLDB 1994 Query Optimization by Predicate Move-Around http://www.sigmod.org/vldb/conf/1994/P096.PDF Эвристическое преобразование Преобразование, основанное на стоимости Физическая оптимизация RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  21. 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 >'19980101'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'); SUBQUERY UNNESTING SELECT e1.employee_name, j.job_title FROM employees e1, job_history j, (SELECT AVG(e2.salary) avg_sal, dept_id FROM employees e2 GROUPBY dept_id) V WHERE e1.emp_id = j.emp_id and j.start_date >'19980101'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'); DISTINCT VIEW SELECT e1.employee_name, j.job_title FROM employees e1, job_history j, (SELECTDISTINCT 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 >'19980101'and e1.salary > (SELECT AVG (e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) and e1.dept_id = VD.dept_id; RuOUG:Стратегия настройки SQL запросов. 11.11.2009

  22. QB_NAME SELECT/*+ QB_NAME(qb) FULL(@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 (c000000093a75658) before join elimination: SQL:******* UNPARSED QUERY IS ******* SELECT … Query block (c000000093a75658) unchanged RuOUG:Стратегия настройки SQL запросов. 11.11.2009

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

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

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

More Related