2015-08-31

Index monitoring и сбор статистики

В продолжении темы мониторинга индексов провел небольшое исследование про index monitoring + v$object_usage
Когда анализировал использование индексов по dba_hist_sql_plan запросы, которые собирают статистику приходилось отфильтровывать руками.
**Для index monitoring написал небольшой тест, который показал, что
index monitoring показывает только select по индексу и не показывает сбор статистики и insert в таблицу**

DROP TABLE t PURGE;
Table dropped
CREATE TABLE t(ix NUMBER);
Table created
CREATE INDEX ix_t ON t(ix);
Index created
ALTER INDEX ix_t MONITORING USAGE;
Index altered
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
INSERT INTO t
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000;
1000 rows inserted
COMMIT;
Commit complete
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
BEGIN
  dbms_stats.gather_table_stats(USER, 'T', CASCADE => TRUE);
END;
/
PL/SQL procedure successfully completed
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        NO   08/31/2015 19:18:23 
SELECT COUNT(*) FROM t WHERE ix = 10;
  COUNT(*)
----------
         1
SELECT * FROM v$object_usage;
INDEX_NAME                     TABLE_NAME                     MONITORING USED START_MONITORING    END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T                           T                              YES        YES  08/31/2015 19:18:23 

По теме удаления неиспользуемых индексов в последнюю неделю начали писать все.
Статья Льюиса https://jonathanlewis.wordpress.com/2015/08/17/index-usage/ (обещал продолжение)
Вторая статья Льюиса немного не по теме https://jonathanlewis.wordpress.com/2015/08/29/index-usage-2/ – про то, что с 11.2.0.2 не надо делать индексы по trunc(datetime column),Oracle сам научился добавлять предикаты
Статья на форуме https://jonathanlewis.wordpress.com/2015/08/17/index-usage/
Старая статья Тима Холла про monitoring usage https://oracle-base.com/articles/10g/index-monitoring

Parallel hint

В статье https://blogs.oracle.com/datawarehousing/entry/parallel_execution_precedence_of_hints
интересные результаты получились для строчек, в которых используются хинты PARALLEL, PARALLEL(degree) и PARALLEL(auto) (строки 9, 10 и 11). Получается, что если установлен такой хинт, то делать

ALTER SESSION ENABLE PARALLEL DML

не надо?
Надо провести тесты.

2015-08-25

Unusable index monitoring

Типичная задача об удалении неиспользуемых индексов решается индивидуально для каждой системы.
Можно выбрать такой алгоритм:
выбрать индексы, которые реально необходимо оптимизировать, например
* большие по размеру
* на поддержку которых уходит много времени: много операций записи

индексы не используются
* проверяем по dba_hist_sql_plan без учета сбора статистики
* проверяем по dba_hist_seg_stat

Главная идея такая: собираем все подознительное и из этого откидываем, что есть в планах или читается.

Мой запрос такой, но его можно подкорректировать в зависимости от нужд (в части bad_idx + поиграться с количеством чтений и записей + еще что-нибудь дописать)

WITH large_idx AS (
    SELECT owner, segment_name, segment_type, ROUND(sum(bytes)/1024/1024/1024, 2) size_gb
    FROM   dba_segments t
    WHERE  segment_type LIKE 'INDEX%'
    GROUP BY owner, segment_name, segment_type
    HAVING ROUND(sum(bytes)/1024/1024/1024, 2) > 5
),
seg_stat AS (
  SELECT o.object_name, o.owner, SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) READS,
    SUM(s.physical_write_requests_delta) + SUM(s.physical_writes_direct_delta) + sum(s.physical_writes_delta) writes
  FROM dba_hist_seg_stat s, dba_objects o
  WHERE s.obj# = o.object_id
    AND o.object_type LIKE 'INDEX%'
    AND o.owner LIKE 'OWNER%'
  GROUP BY o.object_name, o.owner
  HAVING SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) = 0
),
sql_plan AS (
SELECT /*+ MATERIALIZE*/
 p.*
FROM   (SELECT DISTINCT o.owner,
                        o.object_name,
                        sql_id
        FROM   dba_objects       o,
               dba_hist_sql_plan p
        WHERE  o.object_type LIKE 'INDEX%'
        AND    owner LIKE 'OWNER%'
        AND    p.object_owner = o.owner
        AND    p.object_name = o.object_name) p
WHERE  EXISTS (SELECT NULL
        FROM   dba_hist_sqltext t
        WHERE  p.sql_id = t.sql_id
        AND    t.sql_text NOT LIKE '%\*%dbms_stats%*\%')
),
bad_idx AS (
SELECT owner, segment_name object_name, 'LARGE' reason FROM large_idx
UNION
SELECT owner, object_name, 'NOT USED' reason FROM seg_stat WHERE READS = 0 AND writes > 10000
)
SELECT /*+ PARALLEL(8)*/i.owner, i.object_name, round((SELECT sum(bytes) FROM dba_segments s WHERE s.owner = i.owner 
  AND s.segment_name = i.object_name)/1024/1024, 2) mb, LISTAGG(reason, '; ') WITHIN GROUP (ORDER BY 1),
  'ALTER INDEX ' || i.owner || '.' || i.object_name || ' MONITORING USAGE;' monitiring_on,
    'ALTER INDEX ' || i.owner || '.' || i.object_name || ' NOMONITORING USAGE;' monitiring_off
FROM bad_idx i
WHERE (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM sql_plan)
  AND (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM seg_stat WHERE READS > 0)
GROUP BY i.owner, i.object_name
;

Пока сильные подозрения вызываем правильность заполнения dba_hist_seg_stat

2015-08-24

Initial extent

Таблица после MOVE не уменьшилась, хотя по оценкам должна быть в 15 раз меньше.
Выяснилось, что причина в неадекватном initial extents
Уменьшить его можно в самом move

ALTER TABLE test MOVE STORAGE (INITIAL 2097152) PARALLEL 8;

2015-08-09

Удаление индексов перед вставкой при наличии constraints

Один INSERT очень сильно тормозил из-за существующих на таблице индексов. Быстрее было их удалить, вставить и построить их заново.
Тут столкнулся с проблемой: на индексах были построены primary key и unique key, а на PK еще и ссылались FK с других таблиц.
Минимальный работающий алгоритм получился такой:
1. Удалить внешние ключи (именно DROP, DISABLE не работает)
2. Удалить или сделать DISABLED PK и UK
3. Удалить индексы. Те, которые поддерживают PK и UK именно DROP, а не UNUSABLE
4. INSERT
5. Создаем назад индексы и FK, делаем PK ENABLE (для скорости можно NOVALIDATE)

Выводы
1. Любой FK мешает удалить PK или UK на который он ссылается. Даже DISABLE.
2. Индекс, поддерживающий PK или UK можно сделать UNUSABLE, но вставить записи в таблицу потом нельзя.
3. Удалить индекс, который поддерживает PK или UK невозможно.
4. Но можно сделать PK UNUSABLE, удалить индекс и вставлять

Скрипт и результаты работы

DROP TABLE chi PURGE;
DROP TABLE par PURGE;

CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));

CREATE TABLE chi(par_id NUMBER);

ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с первичным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_pk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_pk; 

-- подготовка к следующему эксперементу
ALTER TABLE chi DROP CONSTRAINT chi_fk;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;

ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;

-- тест с уникальным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_uk; 

-- но можно сделать constraint DISABLE
ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
-- с UNUSABLE INDEX все равно нельзя вставить
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;

--а вот без индекса можно
DROP INDEX par_uk; 
INSERT INTO par SELECT ROWNUM FROM dual;

Скрипт с результатами

SQL> CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));
Table created
SQL> CREATE TABLE chi(par_id NUMBER);
Table created
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с первичным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_pk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_PK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_pk;
DROP INDEX par_pk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- подготовка к следующему эксперементу
SQL> ALTER TABLE chi DROP CONSTRAINT chi_fk;
Table altered
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
Table altered
SQL> ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
Table altered
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с уникальным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_uk;
DROP INDEX par_uk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- но можно сделать constraint DISABLE
SQL> ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
Table altered
SQL> -- с UNUSABLE INDEX все равно нельзя вставить
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> --а вот без индекса можно
SQL> DROP INDEX par_uk;
Index dropped
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
1 row inserted