Борьба с ORA-04030

В системе при удалении строк из огромной вьюхи с триггерами раз в 2 часа выскакивала ошибка

ORA-04030: out of process memory when trying to allocate 32780 bytes (kxs-heap-b,bind var buf)

Параметр в скобках периодически менялся, сообщая о невозможности выделить памяти то в одной, то в другой области.

Исследование ошибки начал с

select * from v$process order by pga_used_mem DESC

В чемпионах сессия, которая удаляет данные.
Далее делаю

SELECT * FROM v$process_memory ORDER BY max_allocated DESC NULLS LAST;

получаю, что больше всего памяти выделено в сессии под CATEGORY SQL
Выполняю

SELECT * FROM v$process_memory_detail;

Пусто :(.
В ходе поисков решения натыкаюсь на отличную статью
Делаю

SELECT * FROM V$SQL_WORKAREA_ACTIVE;

Пусто, не мой вариант. У меня нет ни сортировок, ни хешей, ни битовых индексов.
Но далее в статье решение. Для того, что бы увидеть данные в v$process_memory_detail, нужно сказать ораклу, чтобы он их заполнил

SQL> ORADEBUG SETMYPID
Statement processed.
SQL> ORADEBUG DUMP PGA_DETAIL_GET 24
Statement processed.

и в v$process_memory_detail нахожу, что больше всего места занимает heap kxt.c: PL/SQL pgadef и ноту на метлинке PLSQL performance on 11.1.x much slower than previous DB versions (Doc ID 739064.1)
На лицо мой баг в Oracle 11.1.0.6
После применения рекомендованых

ALTER SYSTEM SET session_cached_cursors=0 SCOPE=SPFILE;
ALTER SYSTEM SET cursor_space_for_time=FALSE SCOPE=SPFILE;

все заработало как надо

Clear empty partitions

Clear empty partitions

DECLARE
    g_owner      VARCHAR2(30) := USER;
    g_table_name VARCHAR2(30) := 'PART_TEST';

    PROCEDURE sp_execute(p_str VARCHAR2) IS
    BEGIN
        dbms_output.put_line(p_str);
        EXECUTE IMMEDIATE p_str;
    END sp_execute;

    FUNCTION is_segment_empty(p_table_owner    VARCHAR2,
                              p_table_name     VARCHAR2,
                              p_partition_name VARCHAR2) RETURN BOOLEAN IS
        l_tmp  NUMBER;
        l_stmt VARCHAR2(32767);
    BEGIN
        l_stmt := 'SELECT count(*) from ' || p_table_owner || '.' || p_table_name || CASE
                      WHEN p_partition_name IS NOT NULL THEN
                       ' partition (' || p_partition_name || ') '
                  END || ' where rownum = 1';
        EXECUTE IMMEDIATE l_stmt
            INTO l_tmp;
        IF l_tmp <> 0
        THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;

    END is_segment_empty;

BEGIN
    FOR rec IN (SELECT * FROM all_tab_partitions p WHERE p.table_owner = g_owner AND table_name = g_table_name)
    LOOP
        IF is_segment_empty(rec.table_owner,
                            rec.table_name,
                            rec.partition_name)
        THEN
            sp_execute('ALTER TABLE ' || rec.table_owner || '.' || rec.table_name || ' TRUNCATE PARTITION ' ||
                       rec.partition_name || ' DROP ALL STORAGE UPDATE INDEXES');
        END IF;
    END LOOP;
END;
/

Get index ddl

Извлечение кода создания индексов без мусора.Не подойдет для global партиционированных индексов

BEGIN
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE); 
END;
/  

with ind AS (
SELECT owner owner, index_name name FROM all_indexes WHERE table_name = '[table_name]'
UNION ALL
SELECT '[index_owner]' owner, '[index_name]' NAME FROM dual
)
SELECT to_char(
  dbms_metadata.get_ddl(object_type => 'INDEX', name => ind.NAME, schema => ind.owner)
  || CASE WHEN i.partitioned = 'YES' THEN ' LOCAL' END
  )
  , ind.*
FROM ind, all_indexes i
WHERE ind.owner = i.owner AND ind.name = i.index_name
;

ORA-01031: insufficient privileges

Приключилась тут у меня на одной из баз ошибка ORA-01031: insufficient privileges
Произошла она в ходе экспериментов с клонированием баз с одного сервера на другой (печальный опыт показал, что в Oracle 11.1 SE не работает Active Database Duplication и что в статье Тима Холла кучка ошибок, а индусы на металинке дают воркэраунды с опечатками в командах). Для Active Database Duplication нужно явно прописывать экземпляр в listener.ll

Я мог подключится к базе через os authentication (т.е. при помощи sqlplus / as sysdba), но никак не мог при помощи LISTENER (т.е. sqlplus sys/password@db1 as sysdba). Поэтому варианты с
невключением в группу dba и SQLNET.AUTHENTICATION_SERVICES были отброшены сразу.

Проверив параметры REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, наличие самого password file в $ORACLE_HOME/dbs

ll $ORACLE_HOME/dbs/ora*
-rw-r----- 1 oracle oinstall 2560 2015-06-15 20:58 /opt/oracle/product/11.1/db/dbs/orapwdb1

и даже на всякий случай его пересоздав password file я уже было совсем расстроился, пока не сравнил имя password file с тем, что прописано в listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = DB1)
    )
  )

Передирая у Тима Холла, я не исправил регистр символов!!! При этом в переменной ORACLE_SID у меня имя в нижнем регистре

echo $ORACLE_SID
db1

Исправив listener.ora на нижний регистр

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db1)
      (ORACLE_HOME = /opt/oracle/product/11.1/db)
      (SID_NAME = db1)
    )
  )

получил нормальное подключение к базе.
Товарищи, проверяйте регистр символов!!!

dba_tab_modifications + merge

Небольшой тест для определения, как в dba_tab_modifications отображается результат оператора MERGE
1. Для того, что бы результаты оказались в dba_tab_modifications нужно вызвать DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
2. Сбор статистики по таблице убирает (удаляет) строчку из dba_tab_modifications.
3. MERGE учитывается по отдельности, как будто это 2 оператора
Пример

DROP TABLE a PURGE;
CREATE TABLE a(a NUMBER, val VARCHAR2(10));

INSERT INTO a 
SELECT LEVEL, LPAD('x', 10, 'x')
FROM dual
CONNECT BY LEVEL <= 100;

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

MERGE INTO a
USING (
SELECT LEVEL + 75 a,  LPAD('Z', 10, 'Z') val
FROM dual
CONNECT BY LEVEL <= 75
) t
ON (a.a = t.a)
WHEN MATCHED THEN UPDATE SET a.val = t.val
WHEN NOT MATCHED THEN INSERT VALUES (t.a, t.val);

BEGIN
  DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

UPDATE a SET val =  LPAD('x', 10, 'x') WHERE ROWNUM <=22;
BEGIN
  DBMS_STATS.gather_table_stats(NULL, 'A');
END;
/
SELECT table_name, inserts, updates, deletes FROM dba_tab_modifications t WHERE table_name = 'A';

Результаты

SQL> 
Table dropped
Table created
100 rows inserted
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     100          0          0
75 rows merged
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------
A                                     150         25          0
22 rows updated
PL/SQL procedure successfully completed
TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ---------- ---------- ----------

SQL> 

Partition pruning monitoring

Зачастую partition pruning не так просто увидеть в плане запроса: если используются bind переменные или подзапрос, то в плане будет стоять pstart и pstop KEY. Для этих случев Oracle сделал отдельный эвент 10128. Для его использования необходимо создать таблицу kkpap_pruning. Результаты можно просматривать в файле, а можно и в таблице. К сожалению внятной информации, как интерпретировать трейсы найти не удалось, так что ниже небольшое исследование:

Ниже приведены примеры для 4 запросов:
* без PP
* PP для одной партиции по равенству
* PP для 2 партиций для случая >=
* отсутствие PP для !=
* пример с bind-переменными, для которого по event мы увидим, какой PP имел место.
* пример с join таблиц
Инициализируем окружение и создаем объекты при помощи скрипта

SET SERVEROUTPUT OFF
SET PAGESIZE 0 FEEDBACK OFF
SET LINESIZE 32000
COL plan_table_output FORMAT a300


drop table kkpap_pruning;
DROP TABLE part_test;

create table kkpap_pruning 
(partition_count  NUMBER
,iterator         VARCHAR2(32)
,partition_level  VARCHAR2(32)
,order_pt         VARCHAR2(12)
,call_time        VARCHAR2(12)
,part#            NUMBER
,subp#            NUMBER
,abs#             NUMBER
);


CREATE TABLE part_test (
  id1 NUMBER NOT NULL,
  pad VARCHAR2(1000),
  val NUMBER)
PARTITION BY RANGE (id1) (
  PARTITION p1 VALUES LESS THAN (1),
  PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN (3),
  PARTITION p4 VALUES LESS THAN (4)
);

INSERT INTO part_test
SELECT MOD(ROWNUM, 4) , LPAD('x', 1000, 'x'), ROWNUM
FROM dual
CONNECT BY LEVEL <= 1000;  

BEGIN dbms_stats.gather_table_stats(USER, 'PART_TEST'); END;
/

NB: SET SERVEROUTPUT OFF необходим для того, что бы dbms_xplan.display_cursor работал правильно.

Пример 1. Нет PP, т.к. нет фильтрации

-- Без предикатов
alter session set events '10128 trace name context forever, level 2';
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS FULL | PART_TEST |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
------------------------------------------------------------------------------------------
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

здесь и далее приведены части трейса, которые относятся к стадии выполнения call time = RUN. Как видно, в первом случае мы посещаем все 4 партиции нашей таблицы, что видно и из плана выполнения и из трейса

Пример 2. Равенство

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1=1
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |     9 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|           |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | PART_TEST |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

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

   3 - filter("ID1"=1)
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1   

Пример 3. Неравенство с 2 партициями

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1>=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |       |       |    17 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|           |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
|   3 |    TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
-------------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 4. Отсутвие PP для !

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1!=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL | PART_TEST |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 6. PP с bind-переменными для 2 партиций

VAR pstart NUMBER
VAR pstop NUMBER
EXEC :pstart := 2;
EXEC :pstop := 3;
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 BETWEEN :pstart AND :pstop
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE            |           |     1 |     3 |            |          |       |       |
|*  2 |   FILTER                   |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|           |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

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

   2 - filter(:PSTOP>=:PSTART)
   4 - filter(("ID1">=:PSTART AND "ID1"<=:PSTOP))

Partition Iterator Information:
  partition level = PARTITION
  call time = COMPILE
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3   

Для этого случая я оставил в трейсе часть от call time = COMPILE. Как видно, из нее вообще ничего не понятно.

Результаты запроса

select * from KKPAP_PRUNING t;

PARTITION_COUNT ITERATOR                         PARTITION_LEVEL                  ORDER_PT     CALL_TIME         PART#      SUBP#       ABS#
--------------- -------------------------------- -------------------------------- ------------ ------------ ---------- ---------- ----------
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              3 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              3 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3

Пример 5. join

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 IN (
      SELECT ROWNUM FROM dual CONNECT BY LEVEL <=2)
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |       |       |    11 (100)|          |       |       |
|   1 |  SORT AGGREGATE                   |           |     1 |    16 |            |          |       |       |
|   2 |   NESTED LOOPS                    |           |   250 |  4000 |    11  (10)| 00:00:01 |       |       |
|   3 |    VIEW                           | VW_NSO_1  |     1 |    13 |     3  (34)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                   |           |     1 |       |     3  (34)| 00:00:01 |       |       |
|   5 |      COUNT                        |           |       |       |            |          |       |       |
|   6 |       CONNECT BY WITHOUT FILTERING|           |       |       |            |          |       |       |
|   7 |        FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |       |       |
|   8 |    PARTITION RANGE ITERATOR       |           |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL             | PART_TEST |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

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

   9 - filter("ID1"="ROWNUM")

Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 2]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2

Выводы

Кратко можно заключить следующее:
1. В трейсе смотреть на блоки, относящиеся к call time = RUN
2. Какие партиции посещались видно с строке iterator = RANGE [2, 3] и в строках current partition: part# = 2, subp# = 1048576, abs# = 2.
3. Если нет доступа к файловой системе сервера, то результаты можно посмотреть и в таблице KKPAP_PRUNING.
4. К сожалению, ни названий таблиц, ни номеров объектов в файле и таблице нет, поэтому разобраться со сложным запросом будет очень тяжело. Наверное придется ориентироваться на количество партиций и на порядок операций

Flashback features mindmap

Подборка материалов по управлению планами запросов

Подборка статей про управление планами запросов: SQL profile, SQL Plan Baseline, SQL Patch, Stored Outlines:

1. https://iusoltsev.wordpress.com/2014/12/14/hardcoded-hints-query-plan-spm/
2. http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html
3. https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=108825519711641&id=1931944.1&_afrWindowMode=0&_adf.ctrl-state=y0xdrlgpx_4

dbms_xplan.display

Для тех у кого нет прав вызывать паклеты на prod базе, но есть права на чтение таблиц ниже приведен способ, как с использованием вспомогательной базы данных вывести план запроса в привычном виде.
Основные мысли навеяны статьей http://douggault.blogspot.ru/2009/04/trouble-with-dbmsxplan.html

Итак, план запроса мы будем строть при помощи функции dbms_xplan.display. Описание параметров этой функции такое

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Теперь вместо первого параметра мы передадим имя представления/материализованного представления/таблицы. Представление строим над копией таблицы v$sql_plan с продакшена. Это позволяет обойти следующие неприятности:

• в dba_hist_sql_plan не копируются колонки filter_predicates и access_predicates
• ошибку ORA-22992: cannot use LOB locators selected from remote tables (немного нечестную, т.к. я не тяну LOB колонок в запросах. Второй вариант ее обхода – с использованием материализованного представления см. ниже).

Текст представления:

CREATE OR REPLACE VIEW vw_plan_table_prod AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan_prod

Для получения плана запроса использовать

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => '0jhz0hkckw4q4',
                                format       => 'ALL',
                                filter_preds => 'plan_id=1275605462 and child_number=0'));

Дополнительный фильтр в параметре filter_preds опционален и используется, если для запроса построено несколько планов.
Объеснение как работает фильтр и как он трансформирует запрос к таблице с планами в статье, указанной выше.

Вариант 2.

Используем материализованное представление

CREATE MATERIALIZED VIEW vw_plan_table_prod 
REFRESH ON DEMAND 
AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan@loopback
;

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT * FROM v$diag_info WHERE NAME = 'Default Trace File';
--ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
BEGIN dbms_mview.refresh(list => 'vw_plan_table_prod', method => 'C', atomic_refresh => FALSE); END;
/

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => 'gd90ygn1j4026',
                                format       => 'ALL',
                                filter_preds => ''));

Аналогично можно построить запрос для dba_hist_sql_plan

Настройки плана счетов в OEBS

Запрос для получения настроек плана счетов в OEBS для набора книг (или всех наборов книг)

select b.set_of_books_id, b.name, b.chart_of_accounts_id, s.application_column_name, s.segment_name, t.flex_value_set_name
from gl_sets_of_books b, 
  FND_ID_FLEX_SEGMENTS s, 
  FND_FLEX_VALUE_SETS t
WHERE b.chart_of_accounts_id = s.id_flex_num
  AND b.set_of_books_id IN (...)
  AND t.flex_value_set_id = s.flex_value_set_id
ORDER BY b.set_of_books_id, segment_num;

Сохраняем html-список в файл при помощи jquery

Для удобства нужно было выкачать список книжек с сайта orelly.
Список представлял из себя следующую html-структуру:

<select class="ebook-select" size="15" name="book">

    <option value=""></option>
    <option data-date="Jan 2015" data-isbn="9781491909300" data-title=" Living Clojure" value="56358">

         Living Clojure 9781491909300 Jan 2015

    </option>

Для красоты нужно было выдернуть только data-title из каждого элемента списка.
Задача решилась простым jquery

$("[name]=books > option").each(function(){
    console.log($(this).attr('data-title'));
})

но вот проблема – ни Chrome (просто не нашел такого пункта), ни FF (почему-то фолдит вывод, предлагая его раскрыть) не дали сохранить вывод в файл.

Вывод получилось сделать при помощи тулзы debugout.js
Как пользоваться

• Заменяем в файле количество сохраняемых строк

self.maxLines = 5000; // if autoTrim is true, this many most recent lines are saved

• Прогоняем файл в консоли (тут файл приводить не буду, вдруг обновят и улучшат)
• Инициируем логгилку

var bugout = new debugout();

• Прогоняем скрипт, заменив с bugout

$("[name]=books > option").each(function(){
    bugout.log($(this).attr('data-title'));
})

• Сохраняем полученный файл

bugout.downloadLog()

Просто, понятно, быстро

Удаление CONSTRAINT и индексов

Помню несколько лет назад надо было почистить схему, удалив CONSTRAINTы из табличек.
При этом база вела себя как хотела: то сама удаляла индексы и ругалась при их повторном удалении, то оставляла их. Тогда дело решилось простой обработкой exception, сейчас пришла пора разобраться что тут к чему.
При удалении CONSTRAINT возможны следующие варианты:
Вариант 1: ALTER TABLE tbl DROP CONSTRAINT cons – базовый вариант, который все обычно и пишут
Вариант 2: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE – расширеный вариант, который на самом деле удаляет связанные foreign key
Вариант 3: ALTER TABLE tbl DROP CONSTRAINT cons CASCADE DROP INDEX – машина для убийств

Опция cascade управляет только foreign key, которые ссылаются на таблицы

-- Инициализация
DROP TABLE chi_tst PURGE;
DROP TABLE tst PURGE;

CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));

INSERT INTO tst VALUES (1);

CREATE TABLE chi_tst(fk NUMBER NOT NULL, CONSTRAINT fk_chi_tst FOREIGN KEY (fk) REFERENCES tst(pk));

INSERT INTO chi_tst VALUES(1);

ALTER TABLE tst DROP CONSTRAINT tst_pk
ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE tst DROP CONSTRAINT tst_pk CASCADE;
Table altered

Далее мы не будет использовать опцию cascade, т.к. у нас будет одна таблица

Когда нужно добавлять DROP INDEX, а когда индекс удалится сам? Ответ простой: DROP INDEX лучше добавлять всегда, когда хочется гарантировано удалить индекс.

Для того, что бы ответить на вопрос, когда индекс удалится сам, проведем следующий набор тестов:
Тест 1: primary key + атоматически создаваемый (уникальный индекс)
Тест 2: primary key + вручную созданный уникальный индекс
Тест 3: primary key + вручную созданый неуникальный индекс
Для каждого из тестов попробуем удалить индекс без опции (вариант 1) и с опцией (вариант 3)

-- индекс создан автоматически, удаляем без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

-- индекс создан автоматически, удаляем с DROP INDEX
CREATE TABLE tst (pk NUMBER NOT NULL, CONSTRAINT tst_pk PRIMARY KEY (pk));
Table created
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

как видно, если индекс создается автоматически, то он удаляется в любом случае

-- Уникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         UNIQUE

-- Уникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE UNIQUE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

Теперь сделаем constraint на обычном индексе. Этот вариант пробую из-за на достаточно подробном, но немного ошибочном посте Ричарда Фута

-- Неуникальный индекс вручную, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
PK_TST                         NONUNIQUE

-- Неуникальный индекс вручную, с DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
CREATE INDEX pk_tst ON tst(pk);
Index created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk);
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk  DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Как видно, если индекс создавался вручную, то он не удаляется, если не указать DROP INDEX

И последний вариант, который узнал из вышеуказанного поста Фута, основанный на DEFFERABLE CONSTRAINT. Для его поддержки Oracle автоматически создает неуникальный индекс

-- DEFFERABLE, автоматически созданный, неуникальный, без drop index
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------
TST_PK                         NONUNIQUE

-- DEFFERABLE, автоматически созданный, неуникальный, c DROP INDEX
DROP TABLE tst PURGE;
Table dropped
CREATE TABLE tst (pk NUMBER NOT NULL);
Table created
ALTER TABLE tst ADD CONSTRAINT tst_pk PRIMARY KEY (pk) deferrable;
Table altered
INSERT INTO tst VALUES (1);
1 row inserted
ALTER TABLE tst DROP CONSTRAINT tst_pk DROP INDEX;
Table altered
SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'TST';
INDEX_NAME                     UNIQUENESS
------------------------------ ----------

Вывод: без DROP INDEX удаление CONSTRAINT не удаляет даже автоматически создаваемые индексы

Итого

Для того, что бы гарантировано удалить индекс – добавляйте DROP INDEX к команде. В противном случае индекс удаляется только если он был автоматически создан и является уникальным (т.е. в одном случае из четырех)

Скрипт для поиска бекапных таблиц

Ищем сегменты в имени которых есть заданная последовательность (TMP, BKP, 6 и более цифр подряд, …) для которых есть сегменты без этой последовательности.
Скрипт расширяется в подзапросе template, поэтому если при бекапе добавляется и число и суффикс TMP, то необходимо задать нужную последовательность для замены такого имени.

WITH templates AS (
  SELECT '_?TMP_?' t FROM dual UNION ALL
  SELECT '_?TEMP_?' t FROM dual UNION ALL
  SELECT '_?BKP_?' t FROM dual UNION ALL
  SELECT '_?BACKUP_?' t FROM dual UNION ALL
  SELECT '\d{6,}' t FROM dual
),
seg AS (
  SELECT owner, segment_name, segment_type, SUM(bytes)/1024/1024 mb
  FROM Dba_Segments_Prod_h 
  WHERE owner NOT IN ('SYS', 'SYSTEM', 'WMSYS', 'FLOWS_030100', 'APEX_030200')
  GROUP BY owner, segment_name, segment_type
),
bad_segments AS (
SELECT /*+ materialize*/DISTINCT * 
FROM (
  SELECT regexp_replace(s.segment_name, templates.t) new_name, owner, segment_name
  FROM seg s, templates
  )
WHERE new_name <> segment_name 
)
SELECT s.*, b.new_name "Exists"
FROM seg s, bad_segments b
WHERE s.segment_name = b.segment_name
  AND EXISTS (SELECT NULL FROM seg s2 WHERE s2.segment_name = b.new_name)
ORDER BY 4 DESC

Blogspot для разработчика

Замучился оформлять во встроенном редакторе blogspot статьи, в которых есть код. Пишешь в блокноте - пропадают переносы, пишешь в редакторе blospot – не вставляются теги. А скакать туда-сюда совершенно неинтересно.

И тут совершенно случайно в сткатье на хабре нашел упоминание о разметчике http://marxi.co/ – markdown editor для evernote.
Все отлично и писать удобно, и заголовочки, и жирненьким, и код вставляется оформленый сполпинка. Но синхронизация только с evernote. В blogspot только через export as html. Опять не очень удобно (конечно можно поискать средства для публикации заметок Evernote, но куда девать нажитое непосильным трудом :)).

После непродолжительных поисков нашел еще несколько markdown редакторов, в том чисдле и вот этот https://stackedit.io/
К счастью в нем оказался весь необходимый функционал + синхронизация с blogspot.

Следующим шагом встал вопрос о подсветке синтаксиса в коде. На текущий момент выбор сводится к
* HighLight JS – куча тем, языков, автоопределение языка. В итоге он и стал победлителем
* google-code-prettify – немного тем. Отказался, т.к. слишком долго переформатировать старые статьи. И номера строк почему-то не заработали
* SyntaxHighlighter – красивый, очень функциональный, но не нашел способ удобной разметки (тут можно прочитать про способы и подключение

В итогет победителем вышел HighLight JS. Он не только работает без лишних телодвижений после публикации из StackEdit, но и смог разметить (не очень поломав) весь существующий код, который был написан без тегов PRE
Что бы его подключить в панели управления необходимо открыть
Шаблон -> Изменить HTML
В конец секции HEAD добавить

<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js' type='text/javascript'/>
    <link href='http://yandex.st/highlightjs/8.2/styles/magula.min.css' rel='stylesheet'/>
    <script src='http://yandex.st/highlightjs/8.2/highlight.min.js'/>

<script type='text/javascript'>
  hljs.configure({
    languages: ["sql", "javascript", "bash"]}); 
</script>
</head>

Первая строка, если jquery еще не добавлен. В languages список языков, который необходим, или убрать этот блок, если необходимо автоопределение среди всех языков.
Для разметки уже написанных статей пришлось отказаться от стандартной инициализации

<script>hljs.initHighlightingOnLoad();</script>

заменив ее на

  <script type='text/javascript'>
$('code').each(function(i, block) {
  hljs.highlightBlock(block);
});
  </script>
</body>

который, как видно, необходимо поместить в конец секции BODY
Функционала копирования, номеров строк или разноцветных четных и нечетных строк не хватает, но надеюсь на развитие проекта.

Written with StackEdit.

Free space in tablespace

Script to calculate free space in oracle tablespace.

SELECT a.tablespace_name,
a.file_name,
a.file_id,
used_gb "Used Size GB",
a.max_gb "Max Size Gb",
a.max_gb - a.used_gb + NVL(c.free_gb, 0) free_space
FROM
(SELECT tablespace_name, file_name, file_id, a.bytes /1024/1024/1024 used_gb,
CASE WHEN a.maxbytes = 0 THEN bytes ELSE a.maxbytes END /1024/1024/1024 max_gb
FROM dba_data_files a) a,
(
SELECT tablespace_name, file_id, SUM(BYTES) / 1024/1024/1024 free_gb
FROM DBA_FREE_SPACE c
GROUP BY tablespace_name, file_id
) c
WHERE a.tablespace_name = c.tablespace_name(+)
AND a.file_id = c.file_id(+);


TNS-12535 on idle session

Столкнулся с проблемой, что через 10-20 минут неактивности пользовательской сессии (или слишком долгого выполнения процедуры) выкидывалась ошибка

Fatal NI connect error 12170.

  VERSION INFORMATION:
    TNS for Linux: Version 10.2.0.4.0 - Production
    Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
    TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 - Production
  Time: 06-FEB-2015 10:33:38
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0

Краткое исследование вопроса привело к следующим возможным причинам:
* firewall/антивирус на клиенте
* firewall/антивирус на сервере
* firewall/NAT/железка посередине между клиентом и сервером.
Что-то из этого обрывало сессию после нескольких минут в состоянии idle
Т.к. проблема появилась после переезда на новый сервер в новое облако, то первый вариант отпал.

Железками и софтом посередине мы не управляем.

Решил применить 2 способа:
1. Согласно документу Doc ID 1628949.1 установил SQLNET.EXPIRE_TIME=n Where <n> is a non-zero value set in minutes. Этот параметр задает пинговалку ораклом клиента пустыми пакетами в указанный промежуток времени.
2. Согласно доке Doc ID 257650.1 первый способ мог и не помочь, поэтому попробовал поковыряться с TCP KeepAlive. В линуксе проверил:

# sysctl -A | grep keep
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 7200

echo "net.ipv4.tcp_keepalive_time = 300" >> /etc/sysctl.conf

/sbin/sysctl -p

Но если верить разделу Enable Linux kernel keepalive support for TCP connections вот этой статьи, заставить этот параметр работать не так то просто.

В итоге комлекс из этих двух мер проблему решил.