Поскольку количество данных, которые система увеличилась в год, также удвоила сумму параллелизма, производительность SQL становится все чаще становится одним из основных рассмотрений в разработке и разработке ИТ-систем.Проблемы с производительностью SQL постепенно стали основной проблемой производительности базы данных, и 80% проблем с производительностью базы данных вызваны SQL.. Перед лицом растущих проблем с производительностью SQL вопрос о том, как начать и как заранее проверить, стал проблемой, которую должны учитывать все больше и больше программистов.
Сегодня я представлю «Основные идеи оптимизации SQL». Автор поделится с вами 8-летним опытом и опытом оптимизации SQL, чтобы раскрыть тайну оптимизации SQL, чтобы передовые инженеры не дольше спать и спать в реальной разработке. , и, наконец, с легкостью освоить навыки оптимизации SQL.Сначала введите тему оптимизации SQL должны понимать концепцию.
1.1 МОЩНОСТЬ
Номер уникального ключа столбца (Distinct_Keys) называется базовым. Например, гендерный столбец, столбец только между мужчинами и женщинами, поэтому кардинальность этого столбца равна 2. Кардинальность, равная столбцу первичного ключа, является количеством строк в таблице. Данные, влияющие на уровень распределения основания столбца.
Взяв в качестве примера тестовую таблицу test, кардинальность столбца владельца и столбца object_id выглядит следующим образом.
1SQL> select count(distinct owner),count(distinct object_id),count(*) from test;2COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) COUNT(*)3-------------------- ------------------------ ----------4 29 72462 72462скопировать кодОбщее количество строк в таблице TEST составляет 72 462, а мощность столбца владельца равна 29, что указывает на наличие большого количества повторяющихся значений в столбце владельца.Количество элементов столбца object_id равно общее количество строк, указывающее, что столбец object_id не имеет повторяющихся значений, что эквивалентно первичному ключу. Распределение данных столбца владельца выглядит следующим образом.
1SQL> select owner,count(*) from test group by owner order by 2 desc; 2OWNER COUNT(*) 3-------------------- ---------- 4SYS 30808 5PUBLIC 27699 6SYSMAN 3491 7ORDSYS 2532 8APEX_030200 2406 9MDSYS 150910XDB 84411OLAPSYS 71912SYSTEM 52913CTXSYS 36614WMSYS 31615EXFSYS 31016SH 30617ORDDATA 24818OE 12719DBSNMP 5720IX 5521HR 3422PM 2723FLOWS_FILES 1224OWBSYS_AUDIT 1225ORDPLUGINS 1026OUTLN 927BI 828SI_INFORMTN_SCHEMA 829ORACLE_OCM 830SCOTT 731APPQOSSYS 332OWBSYS 233скопировать кодРаспределение данных столбца владельца крайне неравномерно.Мы запускаем следующий SQL.
1select * from test where owner='SYS';скопировать кодSYS имеет 30 808 фрагментов данных, а 30 808 фрагментов данных запрашиваются из 72 462 фрагментов данных, что означает, что должно быть возвращено 42,5% данных в таблице.
1SQL> select 30808/72462*100 "Percent" from dual;2 Percent3----------442.5160774скопировать кодТак что подумайте об этом, как вы думаете, должен ли приведенный выше запрос использовать индекс? Теперь давайте изменим оператор запроса.
1select * from test where owner='SCOTT';скопировать кодSCOTT имеет 7 фрагментов данных, и 7 фрагментов данных запрашиваются из 72 462 фрагментов данных, что означает, что возвращается 0,009% данных в таблице.
1SQL> select 7/72462*100 "Percent" from dual;2 Percent3----------4.009660236скопировать кодПодумайте, нужно ли индексировать 0,009% данных в возвращаемой таблице?
Если вы еще не знакомы с индексированием, ничего страшного, мы рассмотрим его более подробно в следующих главах. Если вы не можете ответить на вышеуказанный вопрос, давайте сначала напомним вам. Когда результат запроса возвращает менее 5% данных в таблице, следует использовать индекс; когда результат запроса возвращает более 5% данных в таблице, следует использовать полное сканирование таблицы.
Поскольку вы не усвоили знания из следующих глав, просто запомните пока ограничение в 5%. Причина, по которой мы говорим здесь о 5%, заключается в том, что некоторые новички боятся, что некоторые новички не будут знать ответы на приведенные выше вопросы.
Теперь есть следующий оператор запроса.
1select * from test where owner=:B1;скопировать кодВ операторе ":B1" является переменной привязки, и любое значение может быть передано. Запрос может проходить через индекс или полное сканирование таблицы.
Теперь даем заключение: если база столбцов низкая, распределение данных будет очень несбалансированным, поскольку распределение данных несбалансированное, это приведет к тому, что SQL-запрос будет использовать индекс, или может потребоваться полное сканирование таблицы. При выполнении оптимизации SQL, если вы подозреваете, что распределение данных несбалансировано, мы можем использовать столбец SELECT, count (*) из таблицы Group By Order By 2 DESC, чтобы просмотреть распределение данных столбца.
Если оператор SQL представляет собой доступ к одной таблице, он может пройти через индекс, полное сканирование таблицы или сканирование материализованного представления.Без учета материализованных представлений доступ к одной таблице осуществляется либо через индекс, либо через полное сканирование таблицы. Теперь вспомним условия индексации: возвращать данные в пределах 5% от таблицы для индексации и выполнять полное сканирование таблицы, когда оно превышает 5%. Я полагаю, что все, кто это читал, поняли метод оптимизации доступа к одной таблице.
Давайте посмотрим на следующий запрос.
1select * from test where object_id=:B1;скопировать кодНезависимо от того, какое значение передается в object_id, индекс должен быть взят.
Давайте еще раз рассмотрим следующий оператор запроса.
1select * from test where object_name=:B1;скопировать кодНезависимо от того, какое значение передается в имя_объекта, следует ли индексировать запрос?
Пожалуйста, проверьте распределение данных имя_объекта. Написав сюда, я вообще-то хочу изменить название этого раздела на «Распределение данных». Мы должны обратить внимание на распределение данных столбцов в дальнейшей работе!
1.2 ИЗБИРАТЕЛЬНОСТЬ
Отношение кардинальности к общему количеству строк, умноженное на 100%, является селективностью столбца.
При выполнении SQL-оптимизации бессмысленно смотреть только на мощность столбца, мощность должна сравниваться с общим количеством строк, чтобы иметь практическое значение, поэтому мы вводим понятие селективности.
Ниже мы смотрим кардинальность и селективность каждого столбца в тестовой таблице, чтобы посмотреть селективность, нужно сначала собрать статистику. О статистике мы подробно поговорим в главе 2. Приведенный ниже скрипт используется для сбора статистики для тестовой таблицы.
1SQL> BEGIN 2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 3 tabname => 'TEST', 4 4 estimate_percent => 100, 5 5 method_opt => 'for all columns size 1', 6 6 no_invalidate => FALSE, 7 7 degree => 1, 8 8 cascade => TRUE); 9 9 END;10 10 /11PL/SQL procedure successfully completed.скопировать кодСледующий скрипт используется для просмотра кардинальности и селективности каждого столбца в тестовой таблице.
1SQL> select a.column_name, 2 2 b.num_rows, 3 3 a.num_distinct Cardinality, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 5 a.histogram, 6 6 a.num_buckets 7 7 from dba_tab_col_statistics a, dba_tables b 8 8 where a.owner = b.owner 9 9 and a.table_name = b.table_name10 10 and a.owner = 'SCOTT'11 11 and a.table_name = 'TEST';12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS13--------------- ---------- ----------- ----------- --------- -----------14OWNER 72462 29 .04 NONE 115OBJECT_NAME 72462 44236 61.05 NONE 116SUBOBJECT_NAME 72462 106 .15 NONE 117OBJECT_ID 72462 72462 100 NONE 118DATA_OBJECT_ID 72462 7608 10.5 NONE 119OBJECT_TYPE 72462 44 .06 NONE 120CREATED 72462 1366 1.89 NONE 121LAST_DDL_TIME 72462 1412 1.95 NONE 122TIMESTAMP 72462 1480 2.04 NONE 123STATUS 72462 1 0 NONE 124TEMPORARY 72462 2 0 NONE 125GENERATED 72462 2 0 NONE 126SECONDARY 72462 2 0 NONE 127NAMESPACE 72462 21 .03 NONE 128EDITION_NAME 72462 0 0 NONE 02915 rows selected.скопировать кодПожалуйста, подумайте: какие столбцы должны быть проиндексированы?
Некоторые говорят о столбцах с высокой кардинальностью, другие говорят о столбцах с условиями где. Эти ответы не идеальны. Насколько велико базовое число? Не сравнивая его с общим количеством строк, я все еще не знаю, насколько оно велико. Например, мощность столбца содержит десятки тысяч строк, но общее количество строк составляет миллиарды строк, так что мощность этого столбца по-прежнему высока? Это фундаментальная причина для выявления селективности.
Когда селективность колонки превышает 20 %, распределение данных в колонке относительно сбалансировано.Селективность object_name и object_id в тестовой таблице test превышает 20 %, а селективность столбца object_name составляет 61,05 %. Теперь смотрим на распределение данных этого столбца (для удобства отображения выводится распределение только первых 10 строк данных).
1SQL> select * 2 2 from (select object_name, count(*) 3 3 from test 4 4 group by object_name 5 5 order by 2 desc) 6 6 where rownum <= 10; 7OBJECT_NAME COUNT(*) 8------------------ ---------- 9COSTS 3010SALES 3011SALES_CHANNEL_BIX 2912COSTS_TIME_BIX 2913COSTS_PROD_BIX 2914SALES_TIME_BIX 2915SALES_PROMO_BIX 2916SALES_PROD_BIX 2917SALES_CUST_BIX 2918DBMS_REPCAT_AUTH 51910 rows selected.скопировать кодИз приведенных выше результатов запроса видно, что распределение данных столбца object_name очень сбалансировано. Мы запрашиваем следующий SQL.
1select * from test where object_name=:B1;скопировать кодНезависимо от любого значения, переданного в object_name, возвращается максимум 30 строк данных.
Какие столбцы должны быть проиндексированы? Когда столбец появляется в условии where, столбец не индексируется и селективность превышает 20 %, столбец необходимо проиндексировать, чтобы повысить производительность SQL-запросов. Конечно, если в таблице всего несколько сотен элементов данных, нам не нужно создавать индекс.
Ниже представлена первая точка зрения на основную идею оптимизации SQL: только большие таблицы будут вызывать проблемы с производительностью.
Некоторые люди могут сказать: «У меня очень маленькая таблица всего с несколькими сотнями записей, но эта таблица часто подвергается DML-обработке, что приводит к горячим блокам и проблемам с производительностью». относится к проблеме проектирования приложений, а не к категории оптимизации SQL.
Ниже мы поделимся с вами первым полностью автоматическим сценарием оптимизации этой статьи через эксперименты.
Выделите столбцы, которые должны быть проиндексированы (пожалуйста, измените сценарий соответствующим образом, чтобы его можно было использовать в производственной среде).
Во-первых, столбец должен фигурировать в условии where.Как узнать, какой столбец таблицы входит в условие where? Существует два метода: один — обход через V$SQL_PLAN, а другой — обход по следующему сценарию.
Сначала выполните следующую хранимую процедуру, чтобы обновить информацию мониторинга базы данных.
1begin2 dbms_stats.flush_database_monitoring_info;3end;скопировать кодПосле выполнения приведенной выше команды запустите следующую инструкцию запроса, чтобы узнать, какая таблица и какой столбец находятся в состоянии where.
1select r.name owner, 2 o.name table_name, 3 c.name column_name, 4 equality_preds, ---等值过滤 5 equijoin_preds, ---等值JOIN 比如where a.id=b.id 6 nonequijoin_preds, ----不等JOIN 7 range_preds, ----范围过滤次数 > >= < <= between and 8 like_preds, ----LIKE过滤 9 null_preds, ----NULL 过滤10 timestamp11 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r12 where o.obj# = u.obj#13 and c.obj# = u.obj#14 and c.col# = u.intcol#15 and r.name = 'SCOTT'16 and o.name = 'TEST';скопировать кодНиже приведены экспериментальные этапы.
Сначала мы запускаем запрос и позволяем столбцу object_id владельца появиться там, где условия.
1SQL> select object_id, owner, object_type 2 2 from test 3 3 where owner = 'SYS' 4 4 and object_id < 100 5 5 and rownum <= 10; 6 OBJECT_ID OWNER OBJECT_TYPE 7---------- -------------------- ----------- 8 20 SYS TABLE 9 46 SYS INDEX10 28 SYS TABLE11 15 SYS TABLE12 29 SYS CLUSTER13 3 SYS INDEX14 25 SYS TABLE15 41 SYS INDEX16 54 SYS INDEX17 40 SYS INDEX1810 rows selected.скопировать кодЗатем обновите информацию о мониторинге базы данных.
1SQL> begin2 2 dbms_stats.flush_database_monitoring_info;3 3 end;4 4 /5PL/SQL procedure successfully completed.скопировать кодЗатем мы видим, какие столбцы тестовой таблицы появляются в условии where.
1SQL> select r.name owner, o.name table_name, c.name column_name 2 2 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r 3 3 where o.obj# = u.obj# 4 4 and c.obj# = u.obj# 5 5 and c.col# = u.intcol# 6 6 and r.name = 'SCOTT' 7 7 and o.name = 'TEST'; 8OWNER TABLE_NAME COLUMN_NAME 9---------- ---------- ------------------------------10SCOTT TEST OWNER11SCOTT TEST OBJECT_IDскопировать кодДалее мы запрашиваем столбцы с помощью селективности, больше или равны 20%.
1SQL> select a.owner, 2 2 a.table_name, 3 3 a.column_name, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity 5 5 from dba_tab_col_statistics a, dba_tables b 6 6 where a.owner = b.owner 7 7 and a.table_name = b.table_name 8 8 and a.owner = 'SCOTT' 9 9 and a.table_name = 'TEST'10 10 and a.num_distinct / b.num_rows >= 0.2;11OWNER TABLE_NAME COLUMN_NAME SELECTIVITY12---------- ---------- ------------- -----------13SCOTT TEST OBJECT_NAME 61.0514SCOTT TEST OBJECT_ID 100скопировать кодНаконец, убедитесь, что эти столбцы не проиндексированы.
1SQL> select table_owner, table_name, column_name, index_name2 2 from dba_ind_columns3 3 where table_owner = 'SCOTT'4 4 and table_name = 'TEST';5未选定行скопировать кодКомбинируя вышеперечисленные скрипты, мы можем получить полностью автоматический скрипт оптимизации.
1SQL> select owner, 2 2 column_name, 3 3 num_rows, 4 4 Cardinality, 5 5 selectivity, 6 6 'Need index' as notice 7 7 from (select b.owner, 8 8 a.column_name, 9 9 b.num_rows,10 10 a.num_distinct Cardinality,11 11 round(a.num_distinct / b.num_rows * 100, 2) selectivity12 12 from dba_tab_col_statistics a, dba_tables b13 13 where a.owner = b.owner14 14 and a.table_name = b.table_name15 15 and a.owner = 'SCOTT'16 16 and a.table_name = 'TEST')17 17 where selectivity >= 2018 18 and column_name not in (select column_name19 19 from dba_ind_columns20 20 where table_owner = 'SCOTT'21 21 and table_name = 'TEST')22 22 and column_name in23 23 (select c.name24 24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r25 25 where o.obj# = u.obj#26 26 and c.obj# = u.obj#27 27 and c.col# = u.intcol#28 28 and r.name = 'SCOTT'29 29 and o.name = 'TEST');30OWNER COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NOTICE31---------- ------------- ---------- ----------- ----------- ----------32SCOTT OBJECT_ID 72462 72462 100 Need indexскопировать код
Как упоминалось ранее, когда столбец очень низкая база, распределение данных столбцов будет неровным.Несбалансированное распределение данных приведет либо к полному сканированию таблицы, либо к сканированию индекса при запросе столбца, и в это время легко составить неправильный план выполнения.
Если для столбца с низкой кардинальностью нет статистики гистограммы, оптимизатор на основе затрат (CBO) учитывает распределение данных столбца для сбалансированного.
Здесь мы еще тестировали табличный тест, например, для объяснения экспериментальных гистограмм.
Сначала собираем статистическую информацию по тестовой таблице test.При сборе статистической информации гистограмма столбца не собирается.Утверждение для всех столбцов размером 1 означает, что гистограмма собирается не для всех столбцов.
1SQL> BEGIN 2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 3 tabname => 'TEST', 4 4 estimate_percent => 100, 5 5 method_opt => 'for all columns size 1', 6 6 no_invalidate => FALSE, 7 7 degree => 1, 8 8 cascade => TRUE); 9 9 END;10 10 /11PL/SQL procedure successfully completed.скопировать кодHistogram указывает на отсутствие коллекции гистограмм для None.
1SQL> select a.column_name, 2 2 b.num_rows, 3 3 a.num_distinct Cardinality, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 5 a.histogram, 6 6 a.num_buckets 7 7 from dba_tab_col_statistics a, dba_tables b 8 8 where a.owner = b.owner 9 9 and a.table_name = b.table_name10 10 and a.owner = 'SCOTT'11 11 and a.table_name = 'TEST';12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS13--------------- ---------- ----------- ----------- --------- -----------14OWNER 72462 29 .04 NONE 115OBJECT_NAME 72462 44236 61.05 NONE 116SUBOBJECT_NAME 72462 106 .15 NONE 117OBJECT_ID 72462 72462 100 NONE 118DATA_OBJECT_ID 72462 7608 10.5 NONE 119OBJECT_TYPE 72462 44 .06 NONE 120CREATED 72462 1366 1.89 NONE 121LAST_DDL_TIME 72462 1412 1.95 NONE 122TIMESTAMP 72462 1480 2.04 NONE 123STATUS 72462 1 0 NONE 124TEMPORARY 72462 2 0 NONE 125GENERATED 72462 2 0 NONE 126SECONDARY 72462 2 0 NONE 127NAMESPACE 72462 21 .03 NONE 128EDITION_NAME 72462 0 0 NONE 02915 rows selected.скопировать кодстолбец владельца имеет очень низкую базу, и теперь мы запрашиваем столбцы владельца.
1SQL> set autot trace 2SQL> select * from test where owner='SCOTT'; 37 rows selected. 4Execution Plan 5---------------------------------------------------------- 6Plan hash value: 1357081020 7-------------------------------------------------------------------------- 8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9--------------------------------------------------------------------------10| 0 | SELECT STATEMENT | | 2499 | 236K| 289 (1)| 00:00:04 |11|* 1 | TABLE ACCESS FULL| TEST | 2499 | 236K| 289 (1)| 00:00:04 |12--------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 1 - filter("OWNER"='SCOTT')скопировать кодОбратите внимание на часть, выделенную жирным шрифтом. Запрос owner='SCOTT' возвращает 7 элементов данных, но когда CBO вычисляет количество строк, он считает, что owner='SCOTT' возвращает 2499 элементов данных, и оценка строк не особенно точна. . 7 фрагментов данных запрашиваются из 72 462 фрагментов данных, которые должны быть проиндексированы, поэтому теперь мы создаем индекс для столбца владельца.
1SQL> create index idx_owner on test(owner);2Index created.скопировать кодДавайте проверим еще раз.
1SQL> select * from test where owner='SCOTT'; 27 rows selected. 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3932013684 6------------------------------------------------------------------------------------- 7| Id |Operation |Name | Rows | Bytes | Cost(%CPU)| Time | 8------------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 2499 | 236K | 73 (0)| 00:00:01 |10| 1 | TABLE ACCESS BY INDEX ROWID |TEST | 2499 | 236K | 73 (0)| 00:00:01 |11|* 2 | INDEX RANGE SCAN |IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |12-------------------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 2 - access("OWNER"='SCOTT')скопировать кодТеперь мы запрашиваем `owner='SYS'.
1SQL> select * from test where owner='SYS'; 230808 rows selected. 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3932013684 6------------------------------------------------------------------------------------- 7| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time | 8------------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |12-------------------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 2 - access("OWNER"='SYS')скопировать кодОбратите внимание, что часть, выделенная жирным шрифтом, запрос owner='SYS' возвратил 30 808 элементов данных. Может ли возврат 30 808 фрагментов данных из 72 462 фрагментов данных пройти через индекс? Очевидно, что необходимо выполнить полное сканирование таблицы. То есть план выполнения неверный.
Почему план выполнения запроса owner='SYS' неверен?Потому что мощность столбца владельца очень мала, всего 29, а общее количество строк в таблице 72 462. Как указано выше, когда столбец не собирает статистику гистограммы, CBO будет считать распределение данных столбца сбалансированным. Именно потому, что CBO считает, что распределение данных столбца владельца сбалансировано, независимо от того, равен ли владелец какому-либо значению, число строк, оцененное CBO, всегда будет равно 2 499. А как появились 2 499? Ответ таков.
1SQL> select round(72462/29) from dual;2round(72462/29)3--------------4 2499скопировать кодТеперь все знают, что Ряды в плане выполнения фальшивые.Строки в плане выполнения рассчитываются на основе статистики и некоторых математических формул. Жаль, что многие администраторы баз данных до сих пор не знают правды о том, что Rows в плане выполнения — подделка.
При оптимизации SQL работа, которую часто необходимо выполнять, заключается в том, чтобы помочь CBO рассчитать более точные строки.Примечание. Мы говорим о строках, что является более точным. CBO не может получить точные строки, потому что при сборе статистической информации по таблице статистическая информация, как правило, не собирается в соответствии со 100% стандартной выборкой, даже если статистическая информация таблицы собирается в соответствии со 100% стандартной выборкой, данные в таблице Изменения также происходят в любое время. Кроме того, математическая формула для расчета строк в настоящее время несовершенна, и CBO никогда не сможет рассчитать точные строки.
Если CBO может вычислять точные строки каждый раз, то я считаю, что в настоящее время нам нужно заботиться только о бизнес-логике, дизайне таблиц, написании SQL и о том, как создавать индексы, и больше не нужно беспокоиться о неправильном плане выполнения SQL.
Новая функция SQL Plan Directives в Oracle12c в определенной степени решает проблему производительности SQL, вызванную неточной оценкой строк. Что касается директив плана SQL, в этой статье не обсуждается слишком много.
Для того, чтобы CBO выбрал правильный план выполнения, нам нужно собрать информацию гистограммы для столбца owner, чтобы сообщить CBO, что распределение данных этого столбца не сбалансировано, и позволить CBO ссылаться на статистику гистограммы при расчете строк. Теперь собираем гистограмму для столбца владельца.
1SQL> BEGIN 2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT', 3 3 tabname => 'TEST', 4 4 estimate_percent => 100, 5 5 method_opt => 'for columns owner size skewonly', 6 6 no_invalidate => FALSE, 7 7 degree => 1, 8 8 cascade => TRUE); 9 9 END;10 10 /11PL/SQL procedure successfully completed.скопировать кодПроверьте информацию о гистограмме для столбца владельца.
1SQL> select a.column_name, 2 2 b.num_rows, 3 3 a.num_distinct Cardinality, 4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity, 5 5 a.histogram, 6 6 a.num_buckets 7 7 from dba_tab_col_statistics a, dba_tables b 8 8 where a.owner = b.owner 9 9 and a.table_name = b.table_name10 10 and a.owner = 'SCOTT'11 11 and a.table_name = 'TEST';12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS13--------------- ---------- ----------- ----------- ---------- -----------14OWNER 72462 29 .04 FREQUENCY 2915OBJECT_NAME 72462 44236 61.05 NONE 116SUBOBJECT_NAME 72462 106 .15 NONE 117OBJECT_ID 72462 72462 100 NONE 118DATA_OBJECT_ID 72462 7608 10.5 NONE 119OBJECT_TYPE 72462 44 .06 NONE 120CREATED 72462 1366 1.89 NONE 121LAST_DDL_TIME 72462 1412 1.95 NONE 122TIMESTAMP 72462 1480 2.04 NONE 123STATUS 72462 1 0 NONE 124TEMPORARY 72462 2 0 NONE 125GENERATED 72462 2 0 NONE 126SECONDARY 72462 2 0 NONE 127NAMESPACE 72462 21 .03 NONE 128EDITION_NAME 72462 0 0 NONE 02915 rows selected.скопировать кодТеперь давайте снова запросим приведенный выше SQL, чтобы убедиться, что план выполнения по-прежнему неверен, и проверить, по-прежнему ли ошибается Rows.
1SQL> select * from test where owner='SCOTT'; 27 rows selected. 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3932013684 6------------------------------------------------------------------------------------- 7| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 8------------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 7 | 679 | 2 (0)| 00:00:01 |10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 679 | 2 (0)| 00:00:01 |11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 7 | | 1 (0)| 00:00:01 |12-------------------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 2 - access("OWNER"='SCOTT')16SQL> select * from test where owner='SYS';1730808 rows selected.18Execution Plan19----------------------------------------------------------20Plan hash value: 135708102021--------------------------------------------------------------------------22| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |23--------------------------------------------------------------------------24| 0 | SELECT STATEMENT | | 30808 | 2918K| 290 (1)| 00:00:04 |25|* 1 | TABLE ACCESS FULL| TEST | 30808 | 2918K| 290 (1)| 00:00:04 |26--------------------------------------------------------------------------27Predicate Information (identified by operation id):28---------------------------------------------------29 1 - filter("OWNER"='SYS')скопировать кодПосле сбора гистограммы для столбца владельца ряды, оцененные CBO, в основном точны.Как только ряды оценены правильно, план выполнения не будет ошибочным.
Вам интересно, почему Rows так точно вычисляет после сбора гистограммы, что именно делает сбор гистограммы?Сбор гистограммы для столбца владельца фактически эквивалентен выполнению следующего SQL.
1select owner,count(*) from test group by owner;скопировать кодИнформация гистограммы - это больше, чем результаты запроса SQL, эти результаты будут храниться в словаре данных. Так что, когда мы запрашиваем владелец любого значения, когда CBO всегда будет рассчитать правильные строки, потому что значение каждой гистограммы уже знают, сколько строк данных.
Если SQL использует переменные связывания, столбцы которых собирают гистограммы, то SQL может вызвать отслеживание переменных связывания.Отслеживание переменных привязки — это клише, которое здесь обсуждаться не будет. Oracle11g представил адаптивное совместное использование курсора (Adaptive Cursor Sharing), которое в основном решило проблему слежения за переменными привязки, но адаптивное совместное использование курсора также вызовет некоторые новые проблемы, которые не будут подробно обсуждаться.
Что мы делаем, когда сталкиваемся с SQL с переменными связывания? На самом деле это очень просто, нам просто нужно запустить следующий оператор.
1select 列, count(*) from test group by 列 order by 2 desc;скопировать код
Если данные столбца распределены равномерно, проблем с SQL в принципе нет; если данные столбца распределены неравномерно, нам нужно собрать статистику гистограммы для столбца.
Что касается гистограмм, то на самом деле существует множество тем, таких как типы гистограмм, количество сегментов гистограмм и т. д. В этой статье мы не будем обсуждать здесь слишком много.По нашему мнению, читатели должны знать только то, что гистограмма используется, чтобы помочь CBO получить более точные строки при оценке строк для столбцов с низкой кардинальностью и неравномерным распределением данных.
Какие столбцы нужны для построения гистограммы? Когда столбец находится в состоянии где, селективность столбца меньше 1% и для столбца не собрана гистограмма, такой столбец должен собрать гистограмму.Примечание. Никогда не собирайте гистограммы для столбцов, которые не отображаются в условии «где». Сбор гистограмм для столбцов, которые не отображаются в условии where, совершенно бесполезен и тратит впустую ресурсы базы данных.
Ниже мы делимся с вами вторым полностью автоматизированным скриптом оптимизации в этой статье.
Захватите столбцы, в которых должна быть создана гистограмма (вы можете изменить этот сценарий соответствующим образом для производственного использования).
1SQL> select a.owner, 2 2 a.table_name, 3 3 a.column_name, 4 4 b.num_rows, 5 5 a.num_distinct, 6 6 trunc(num_distinct / num_rows * 100,2) selectivity, 7 7 'Need Gather Histogram' notice 8 8 from dba_tab_col_statistics a, dba_tables b 9 9 where a.owner = 'SCOTT'10 10 and a.table_name = 'TEST'11 11 and a.owner = b.owner12 12 and a.table_name = b.table_name13 13 and num_distinct / num_rows<0.0114 14 and (a.owner, a.table_name, a.column_name) in15 15 (select r.name owner, o.name table_name, c.name column_name16 16 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r17 17 where o.obj# = u.obj#18 18 and c.obj# = u.obj#19 19 and c.col# = u.intcol#20 20 and r.name = 'SCOTT'21 21 and o.name = 'TEST')22 22 and a.histogram ='NONE';23OWNER TABLE COLUM NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE24----- ----- ----- ---------- ------------ ----------- ----------------------25SCOTT TEST OWNER 72462 29 .04 Need Gather Histogramскопировать код
Когда индекс создается для столбца, он содержит значение ключа столбца и идентификатор строки строки, соответствующей значению ключа. Доступ к данным в таблице через идентификатор строки, записанный в индексе, вызывается обратно в таблицу. Возвращаемая таблица, как правило, читается одним блоком. Слишком большое количество возвратов таблицы серьезно повлияет на производительность SQL. Если число возвратов таблицы слишком много, сканирование индекса выполнять не следует, а следует выполнить полное сканирование таблицы. выполняется напрямую.
При выполнении SQL-оптимизации обязательно обратите внимание на то, сколько раз возвращается таблица! В частности, обратите внимание на количество физических операций ввода-вывода, возвращенных в таблицу!
Вы помните неправильный план выполнения из раздела 1.3?
1SQL> select * from test where owner='SYS'; 230808 rows selected. 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3932013684 6------------------------------------------------------------------------------------- 7| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | 8------------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |12-------------------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 2 - access("OWNER"='SYS')скопировать кодЖирным шрифтом (TABLE ACCESS BY INDEX ROWID) в плане выполнения является возвращаемая таблица. Сколько строк данных возвращается индексом и сколько раз необходимо вернуться к таблице, каждый возврат в таблицу представляет собой чтение одного блока (поскольку один идентификатор строки соответствует одному блоку данных). SQL возвращает 30 808 строк данных, поэтому для возврата таблицы требуется 30 808 раз.
Подумайте об этом: потребляется ли производительность приведенного выше плана выполнения при сканировании индекса или в таблице возврата?
Чтобы получить ответ, поэкспериментируйте с SQLPLUS. Чтобы устранить влияние параметра arraysize на логическое чтение, установите arraysize=5000. arraysize указывает, сколько строк данных сервер Oracle каждый раз передает клиенту, по умолчанию 15. Если в блоке 150 строк данных, то блок будет прочитан 10 раз, так как клиенту каждый раз передается только 15 строк данных, логическое чтение будет усиливаться. После установки arraysize=5000 проблема чтения блока n раз не возникнет.
1SQL> set arraysize 5000 2SQL> set autot trace 3SQL> select owner from test where owner='SYS'; 430808 rows selected. 5Execution Plan 6---------------------------------------------------------- 7Plan hash value: 373050211 8------------------------------------------------------------------------------ 9| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |10------------------------------------------------------------------------------11| 0 | SELECT STATEMENT | | 2499 | 14994 | 6 (0)| 00:00:01 |12|* 1 | INDEX RANGE SCAN| IDX_OWNER | 2499 | 14994 | 6 (0)| 00:00:01 |13------------------------------------------------------------------------------14Predicate Information (identified by operation id):15---------------------------------------------------16 1 - access("OWNER"='SYS')17Statistics18----------------------------------------------------------19 0 recursive calls20 0 db block gets21 74 consistent gets22 0 physical reads23 0 redo size24 155251 bytes sent via SQL*Net to client25 486 bytes received via SQL*Net from client26 8 SQL*Net roundtrips to/from client27 0 sorts (memory)28 0 sorts (disk)29 30808 rows processedскопировать кодИз приведенного выше эксперимента видно, что сканирование индекса потребляет только 74 логических чтения.
1SQL> select * from test where owner='SYS'; 230808 rows selected. 3Execution Plan 4---------------------------------------------------------- 5Plan hash value: 3932013684 6------------------------------------------------------------------------------------- 7| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time | 8------------------------------------------------------------------------------------- 9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |12-------------------------------------------------------------------------------------13Predicate Information (identified by operation id):14---------------------------------------------------15 2 - access("OWNER"='SYS')16Statistics17----------------------------------------------------------18 0 recursive calls19 0 db block gets20 877 consistent gets21 0 physical reads22 0 redo size23 3120934 bytes sent via SQL*Net to client24 486 bytes received via SQL*Net from client25 8 SQL*Net roundtrips to/from client26 0 sorts (memory)27 0 sorts (disk)28 30808 rows processed29SQL> set autot off30SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks31 2 from test32 3 where owner = 'SYS';33 BLOCKS34----------35 796скопировать кодКогда в SQL есть таблица возврата, в общей сложности тратится 877 логических операций чтения, так откуда взялись эти 877 логических операций чтения?
30 808 фрагментов данных, возвращаемых SQL, хранятся в блоках данных в общей сложности 796. Для доступа к этим 796 блокам данных требуется 796 логических операций чтения, плюс 74 логических операций чтения для сканирования индекса, плюс 7 логических операций чтения [среди которых 7 = ROUND(30808/5000 )], поэтому накопление составляет ровно 877 логических операций чтения.
Таким образом, мы можем судить, что производительность SQL действительно в основном теряется в таблице возврата!
Что еще хуже: предположить, что 30,808 кусочков данных находятся в разных блоках данных, и таблица не кэшируется в буферном кеше, требуется всего 30 808 физических I / OS для возврата таблицы, которая является ужасной.
Мы видим здесь, если вы можете ответить, почему возвращаемые данные в таблице в пределах 5% от индекса, чтобы перейти более чем на 5% от полного сканирования таблицы данных, чтобы перейти? Основная причина в том, что обратно к столу.
В случае, когда возврат к таблице неизбежен, если индекс возвращает слишком много данных, это неизбежно приведет к слишком большому количеству возвратов к таблице, что приведет к серьезному снижению производительности.
Новая функция пакетной таблицы возврата Oracle12c (TABLE ACCESS BY INDEX ROWID BATCHED) в определенной степени улучшает производительность таблицы возврата одной строки (TABLE ACCESS BY INDEX ROWID). В этой статье не обсуждается форма пакетного возврата.
Какой SQL должен быть возвращен в таблицу?
1Select * from table where ...скопировать кодТакой SQL необходимо возвращать в таблицу, поэтому мы должны строго запретить использование Select *. Какой SQL не нужно возвращать в таблицу?
1Select count(*) from tableскопировать кодТакой SQL не требует таблицы возврата.
Когда запрашиваемый столбец также включен в индекс, в этот момент нет необходимости возвращать таблицу, поэтому мы часто создаем составной индекс, чтобы исключить возвращаемую таблицу, тем самым повышая производительность запроса.
Когда SQL имеет несколько условий фильтрации, но индексируется только один столбец или несколько столбцов, таблица будет возвращена в таблицу, а затем отфильтрована ("*" перед TABLE ACCESS BY INDEX ROWID), и комбинированный индекс должен быть создается для устранения возврата. Затем таблица фильтруется для повышения производительности запросов.
О том, как создать составной индекс, эта проблема слишком сложна, мы неоднократно будем упоминать, как создать составной индекс позже.
Эта статья взята из статьи «Основные идеи по оптимизации SQL».
"Основная идея оптимизации SQL》
Luobing Sen Huang Chao Zhong Jiao вперед
Нажмите на обложку, чтобы купить бумажную книгу
Язык структурированных запросов (SQL) — это мощный язык баз данных. Основанный на работе реляционной алгебры, он имеет богатые функции, лаконичный язык, удобное и гибкое использование и стал стандартным языком реляционных баз данных. Эта книга призвана помочь читателям овладеть навыками оптимизации SQL для повышения производительности базы данных. Эта книга написана на основе Oracle, и содержание объясняется от поверхностного к более глубокому, что подходит для изучения читателями всех уровней.
Эта книга предназначена для фронтовых инженеров, инженеров по эксплуатации, администраторов баз данных, системных проектировщиков и разработчиков., от этого выиграют как новички, так и читатели с определенной подготовкой.
Содержание книги
(Сдвиньте телефон для просмотра)
Глава 1 Основные понятия оптимизации SQL 1
1.1 МОЩНОСТЬ 1
1.2 ИЗБИРАТЕЛЬНОСТЬ 3
1.3 Гистограмма (HISTOGRAM) 7
1.4 Таблица ДОСТУП ПО INDEXROWID 13
1.5 ФАКТОР КЛАСТЕРИЗАЦИИ 15
1.6 Отношения между таблицами 19
Глава 2 Статистика 21
2.1 Что такое статистика 21
2.2 Настройка важных параметров статистической информации 24
2.3 Проверка актуальности статистики 32
2.4 Расширенная статистика 37
2.5 Динамическая выборка 42
2.6 Настройка стратегий сбора статистики 47
Глава 3 План реализации 49
3.1 Общие методы получения планов выполнения 49
3.1.1 Используйте AUTOTRACE для просмотра плана выполнения 49
3.1.2 Используйте EXPLAIN PLAN FOR для просмотра плана выполнения 52
3.1.3 просмотреть план выполнения с помощью A-TIME 54
3.1.4 Просмотр плана выполнения выполняемого SQL 56
3.2 Индивидуальный план выполнения 57
3.3 Как построить индекс, просмотрев план выполнения 59
3.4 Используйте курсор для перемещения Дафа, чтобы прочитать план выполнения 63
ГЛАВА 4 ПУТЬ ДОСТУПА 67
4.1 Общие пути доступа 67
4.1.1 ПОЛНЫЙ ДОСТУП К ТАБЛИЦАМ 67
4.1.2 ДОСТУП К ТАБЛИЦАМ ПОЛЬЗОВАТЕЛЯ ROWID 71
4.1.3 ДОСТУП К ТАБЛИЦАМ ПО ДИАПАЗОНУ ROWID 71
4.1.4 ДОСТУП К ТАБЛИЦАМ ПО INDEX ROWID 72
4.1.5 ИНДЕКС УНИКАЛЬНОЕ СКАНИРОВАНИЕ 72
4.1.6 ИНДЕКС ДИАПАЗОННОЕ СКАНИРОВАНИЕ 73
4.1.7 ПРОПУСК ИНДЕКСНОГО СКАНИРОВАНИЯ 74
4.1.8 ИНДЕКС ПОЛНОЕ СКАНИРОВАНИЕ 75
4.1.9 ИНДЕКС БЫСТРОЕ ПОЛНОЕ СКАНИРОВАНИЕ 77
4.1.10 ИНДЕКС ПОЛНОЕ СКАНИРОВАНИЕ (МИН./МАКС.) 80
4.1.11 MAT_VIEW ПЕРЕЗАПИСЬ ПОЛНЫЙ ДОСТУП 83
4.2 Одноблочное и многоблочное чтение 83
4.3 Почему сканирование индекса иногда выполняется медленнее, чем полное сканирование таблицы 84
4.4 Влияние DML на обслуживание индекса 84
Глава 5 Строки подключения таблицы 86
5.1 ВЛОЖЕННЫЕ ЦИКЛЫ 86
5.2 ХЭШ-соединение (ХЭШ-СОЕДИНЕНИЕ) 90
5.3 СОРТИРОВКА СЛИЯНИЕМ 93
5.4 ДЕКРАТОВОЕ СОЕДИНЕНИЕ 95
5.5 Скалярные подзапросы (SCALAR SUBQUERY) 98
5.6 Полусоединение (SEMI JOIN) 100
5.6.1 Переписывание эквивалента полусоединения 100
5.6.2 Управление планом выполнения полусоединения 101
5.6.3 Мысли читателя 103
5.7 Антиприсоединение (ANTI JOIN) 104
5.7.1 Переписывание эквивалентности против соединения 104
5.7.2 Управление планом выполнения анти-объединения 105
5.7.3 Мысли читателя 108
5,8 фильтр 108.
5,9 и существует, которые быстро, кто медленно 111
5.10 Природа операторов SQL 111
Глава 6 Стоимость 112
6.1 Нужно ли вам смотреть на СТОИМОСТЬ для оптимизации SQL?112
6.2 Расчет стоимости полного сканирования таблицы 112
6.3 Расчет стоимости сканирования диапазона индексов 116
6.4 Основная идея оптимизации SQL 119
Глава 7. Преобразования запросов, которыми вы должны овладеть 120
7.1 Подзапросы не являются вложенными 120
7.2 Слияние видов 125
7.3 Предикат push 129
Глава 8. Советы по настройке 133
8.1 Просмотр реальной базы (ROWS) 133
8.2 вместо или 134 союза
8.3 Идеи по оптимизации оператора пейджинга 135
8.3.1 Идеи по оптимизации однотабличного пейджинга 135
8.3.2 Идеи оптимизации страниц, связанных с несколькими таблицами 150
8.4 Оптимизация самосоединений с помощью аналитических функций 153
8.5 Метод оптимизации для связи между очень большими и очень маленькими таблицами 154
8.6 Метод оптимизации связи между очень большими таблицами и очень большими таблицами 155
8.7 Метод оптимизации оператора LIKE 159
8.8 Оптимизация DBLINK 161
8,9 Пары Таблица рядовых разделов 167
8.10. Метод трехсегментного разделения SQL 169
Глава 9 Оценка случаев оптимизации SQL 170
9.1 Случай оптимизации комбинированного индекса 170
9.2 Случай оптимизации гистограммы 173
9.3 Таблица, управляемая NL, не может пройти INDEX SKIP SCAN 177
9.4 Оптимизация SQL требует внимания к связи между таблицами 178
9.5 Пример оптимизации INDEX FAST FULL SCAN 179
9.6 Случай оптимизации оператора пейджинга 181
9.7 Случай оптимизации столбца ORDER BY alias 183
9.8 Полусоединенный корпус основного стола с задним приводом один 185
9.9 Полусоединенный корпус основного стола с задним приводом 2 187
9.10 Несбалансированное распределение данных столбцов соединения приводит к проблемам с производительностью 192
9.11 Классический случай оптимизации фильтра 198
9.12 Вариант оптимизации древовидного запроса 202
9.13 Случай оптимизации локального индекса 204
9.14 Случай оптимизации скалярного подзапроса 206
9.14.1 Случай 1 206
9.14.2 Случай 2 207
9.15 Случай оптимизации обновления ассоциации 211
9.16 Внешние соединения с условиями ассоциации OR могут быть только NL 213
9.17 Относитесь к своей голове как к CBO 217
9.18 Случай оптимизации расширенной статистики 221
9.19 Оптимизация WMSYS.WM_CONCAT с помощью аналитической функции LISGAGG 227
9.20 Вариант оптимизации ассоциации неэквивалентности INSTR 230
9.21 Regexp_ike Невидовимое невинное дело Соответствующий случай оптимизации 233
9.22 БЕЗОПАСНОСТЬ НА УРОВНЕ РЯД Вариант оптимизации 237
9.23 Невложенный вариант оптимизации подзапроса 1 240
9.24 Невложенный вариант оптимизации подзапроса 2 247
9.25 Неправильное использование внешних соединений предотвращает передачу предикатов 252
9.26. Случай оптимизации с отправкой предикатов 262
9.27 Оптимизация SQL с помощью CARDINALITY 268
9.28 ждать событий оптимизировать использование SQL 272
Глава 10. Полностью автоматизированный аудит SQL 281
10.1 Перехват таблиц с неиндексированными внешними ключами 281
10.2 Захватите столбцы, для которых необходимо собрать гистограмму 282
10.3 Выделение столбцов, которые должны быть проиндексированы 283
10.4 Перехват SQL для SELECT * 284
10.5 Перехват SQL с помощью скалярных подзапросов 285
10.6 поймали SQL 286 с пользовательскими функциями
10.7 Захваченная таблица повторно вызывается SQL 287.
10.8 SQL, который убрал ФИЛЬТР 288
10.9 Выявление вложенного цикла SQL, возвращающего большое количество строк 290
10.10 Перехват SQL 292 полного сканирования таблицы, управляемой NL
10.11 Перехватите SQL, который забрал TABLE ACCESS FULL 293
10.13. Перехват SQL 295 INDEX SKIP SCAN
10.14 Узнайте, для каких ссылок SQL используется индекс 297
10.15. Перехват SQL, который убрал декартово произведение 298
10.16 Перехват SQL, обнаружившего неправильное соединение сортировкой-слиянием 299
10.17 PSQL 301 для перехвата наборов LOOP LOOP
10.18 Обнаружение SQL 302 с индексами низкой селективности
10.19 Отловить SQL, который может создать составной индекс (вернуть таблицу и отфильтровать столбцы с высокой избирательностью) 304
10.20 Найдите SQL, который может создать составной индекс (таблица возврата имеет доступ только к нескольким полям) 306
Взаимодействовать сегодня
Какие статьи из Asynchronous Books вас интересуют? Зачем? Крайний срок в 17:00 27 апреля, оставьте сообщение + перешлите это событие в круг друзей, редакция проведет лотерею, чтобы выбрать 5 читателейОтдайте 2 бумажных книги и 3 E-чтение версии 100 юаней асинхронных сообществ ваучеров, (автоматически получит тот, у кого больше всего лайков).