Статьи

Понимание настройки производительности Oracle Query и использование индексов для оптимизации

Сентябрь 27, 2017

Некоторое время назад у нас была подозрительная проблема с производительностью запроса Oracle. Хотя выяснилось, что проблема не связана с подозрением, мы исследовали ее и смогли дополнительно улучшить производительность на основе нашего анализа.

Чтобы получить список индексов, уже существующих в таблице, вы можете войти в систему как SYSDBA и выполнить следующее. На рабочей станции Linux это будет означать:

[user@host ~]$ sudo su - oracle
-bash-4.1$ sqlplus / as sysdba

Из SQLPlus выполните:

set pages 999
set lin 999

break on table_name skip 2

column table_name  format a25
column index_name  format a25
column column_name format a25

select
   table_name,
   index_name,
   column_name
from
   dba_ind_columns
where
   table_owner='APS_ESB'
order by
   table_name,
   column_position;

Вывод будет перечислять каждую таблицу и индексы

TABLE_NAME          INDEX_NAME            COLUMN_NAME
------------------- --------------------- -------------------------
...
DELIVERY_INFO       DELIVERY_INFO_PK      MSG_REF
...

По умолчанию Oracle создает индекс только для первичного ключа таблицы.

Чтобы понять, как запрос анализируется и выполняется Oracle, используйте префикс «EXPLAIN PLAN FOR» перед анализом SQL-запроса. Затем после того, как SQLPlus выведет «Объяснено», введите запрос «SELECT PLAN_TABLE_OUTPUT FROM TABLE (DBMS_XPLAN.DISPLAY ());» как показано ниже

SQL> EXPLAIN PLAN FOR SELECT MSG_REF FROM SCHEMA.PERF_DELIVERY_INFO WHERE ST_STAGE IN (4, 6) AND ((ST_SUBSYS0_STATE  < 2 AND (ST_SUBSYS0_UPDATE_TIME  IS NULL OR ST_SUBSYS0_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS1_STATE  < 2 AND (ST_SUBSYS1_UPDATE_TIME  IS NULL OR ST_SUBSYS1_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS2_STATE  < 2 AND (ST_SUBSYS2_UPDATE_TIME  IS NULL OR ST_SUBSYS2_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS3_STATE  < 2 AND (ST_SUBSYS3_UPDATE_TIME  IS NULL OR ST_SUBSYS3_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS4_STATE  < 2 AND (ST_SUBSYS4_UPDATE_TIME  IS NULL OR ST_SUBSYS4_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS8_STATE   < 2 AND (ST_SUBSYS8_UPDATE_TIME   IS NULL OR ST_SUBSYS8_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS9_STATE   < 2 AND (ST_SUBSYS9_UPDATE_TIME   IS NULL OR ST_SUBSYS9_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS10_STATE   < 2 AND (ST_SUBSYS10_UPDATE_TIME   IS NULL OR ST_SUBSYS10_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS11_STATE   < 2 AND (ST_SUBSYS11_UPDATE_TIME   IS NULL OR ST_SUBSYS11_UPDATE_TIME   < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS5_STATE < 2 AND (ST_SUBSYS5_UPDATE_TIME IS NULL OR ST_SUBSYS5_UPDATE_TIME < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS6_STATE < 2 AND (ST_SUBSYS6_UPDATE_TIME IS NULL OR ST_SUBSYS6_UPDATE_TIME < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF'))) OR (ST_SUBSYS7_STATE  < 2 AND (ST_SUBSYS7_UPDATE_TIME  IS NULL OR ST_SUBSYS7_UPDATE_TIME  < TO_TIMESTAMP ('19-Mar-15 10:03:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')))) AND ROWNUM < 101 ORDER BY LAST_RECV_TIME;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1365086006

--------------------------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   100 |  6400 |     |  2622   (2)| 00:00:32 |
|   1 |  SORT ORDER BY        |             |   100 |  6400 |   968K|  2622   (2)| 00:00:32 |
|*  2 |   COUNT STOPKEY     |             |     |     |     |          |      |
|*  3 |    TABLE ACCESS FULL| PERF_DELIVERY_INFO |  7763 |   485K|     |  2499   (2)| 00:00:30 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<101)
   3 - filter(("ST_STAGE"=4 OR "ST_STAGE"=6) AND ("ST_SUBSYS0_STATE"<2 AND
          ("ST_SUBSYS0_UPDATE_TIME" IS NULL OR "ST_SUBSYS0_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS1_STATE"<2 AND
          ("ST_SUBSYS1_UPDATE_TIME" IS NULL OR "ST_SUBSYS1_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS2_STATE"<2 AND
          ("ST_SUBSYS2_UPDATE_TIME" IS NULL OR "ST_SUBSYS2_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS3_STATE"<2 AND
          ("ST_SUBSYS3_UPDATE_TIME" IS NULL OR "ST_SUBSYS3_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS4_STATE"<2 AND
          ("ST_SUBSYS4_UPDATE_TIME" IS NULL OR "ST_SUBSYS4_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS8_STATE"<2 AND
          ("ST_SUBSYS8_UPDATE_TIME" IS NULL OR "ST_SUBSYS8_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS9_STATE"<2 AND
          ("ST_SUBSYS9_UPDATE_TIME" IS NULL OR "ST_SUBSYS9_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS10_STATE"<2 AND
          ("ST_SUBSYS10_UPDATE_TIME" IS NULL OR "ST_SUBSYS10_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS11_STATE"<2 AND
          ("ST_SUBSYS11_UPDATE_TIME" IS NULL OR "ST_SUBSYS11_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS5_STATE"<2 AND
          ("ST_SUBSYS5_UPDATE_TIME" IS NULL OR "ST_SUBSYS5_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS6_STATE"<2 AND
          ("ST_SUBSYS6_UPDATE_TIME" IS NULL OR "ST_SUBSYS6_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF')) OR "ST_SUBSYS7_STATE"<2 AND
          ("ST_SUBSYS7_UPDATE_TIME" IS NULL OR "ST_SUBSYS7_UPDATE_TIME"<TO_TIMESTAMP('19-Mar-15
          10:03:10.123000','DD-Mon-RR HH24:MI:SS.FF'))))

Как мы видим выше, полное сканирование таблицы произойдет, если у нас нет индексов по столбцам, по которым будут фильтроваться строки. После создания индекса по значимым столбцам мы можем снова проанализировать производительность с помощью команды «EXPLAIN PLAN FOR …», и тот же запрос теперь выполняется с помощью:

-------------------------------------------------------------------------------------------
| Id  | Operation     | Name           | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   100 |  6400 |    10   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |              |      |      |           |      |
|*  2 |   INDEX FULL SCAN| PERF_DELIVERY_INFO_IDX |  7763 |   485K|    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Как мы видим, простой индекс, созданный для правильной поддержки запроса, может увеличить производительность во много раз. В приведенном выше примере сканирование таблицы было заменено сканированием только по индексу, что намного более эффективно с точки зрения использования процессора и времени.

34
Share: