Стоимость SAMPLE

Многие используют SAMPLE думая, что он выполняется быстрее и с меньшей нагрузкой на ЦП. Для тестирования этого утверждения, создадим тестовую таблицу Х

create table X
  as
select *
  from all_objects
/

После этого, выполним запрос на выборку всех данных

set autotrace traceonly explain




После этого, запустим запрос с SAMPLE




Как вы видите, мы хоть и выбираем 1% данных, но стоимость осталась практически таже. Это происходит из-за того, что идет сканирование всей таблицы случайным образом и выбирается только 1% данных.
Для уменьшения стоимости, укажем, что сканирование должно происходить только по блоку




Как вы можете видеть, стоимость запроса и время выполнения существенно уменьшилось.

Создание партиционированного индекса

В высоконагруженных OLTP базах данных, буферное ожидание может создать реальную проблему производительности системы. Например, если идет вставка данных из многих сессий в таблицу, то может оказаться что они конкурируют за один и тот же листовой блок индекса и из-за этого появляются ожидания. Увидеть эти ожидания, можно с помощью запроса:

select owner
     , object_name
     , subobject_name
     , value
     , tablespace_name
  from v$segment_statistics
 where statistic_name='buffer busy waits'
   and value > 0
 order by value desc
/
OWNER     OBJECT_NAME SUBOBJECT_NAME VALUE TABLESPACE_NAME
STUDENT    IDX_TEST                                          467305    USERS

В приведенном примере, мы видим, что ожидание было 467305 для объекта IDX_TEST. Это индекс на таблице TAB_TEST. Если мы пересоздадим индекс с использованием 64 партиций:

create index idx_test on tab_test(id) global
     partition by hash(id) partitions 64
/

то запустив запрос:

select sum(value) sm
  from v$segment_statistics
 where statistic_name='buffer busy waits'
   and object_name = 'IDX_TEST'

мы увидем, что ожидания снизились до 3311, что меньше в 140 раз предыдущего результата.

Нагрузочное тестирование

Иногда приходится проверять, как поведет себя система, при изменении данных несколькими сессиями одновременно. Для такого тестирования, я создал процедуру run_jobs:

create or replace procedure run_jobs(p_count in number
                                                     ,p_cmd   in varchar2)
as
 l_jobnum number:=0;
begin
  for i in 1..p_count
  loop
    dbms_job.submit(l_jobnum, p_cmd, sysdate);
  end loop;
  commit;
end;
/

в которую передаю следующие параметры:

p_count- количество потоков
p_cmd - то что нужно выполнить в несколько потоков

Пример вызова:

exec run_jobs(10000, 'insert into test values(1, ''z'');');
exec run_jobs(10000, 'begin ..... end;');
exec run_jobs(10000, 'do_something;');

В первом примере вы создаем 10000 джобов для инсерта данных в таблицу test. Второй пример - это вызов PL/SQL блока и третий пример, это вызов заранее подготовленной процедуры.

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

Очень часто, нужно соединить значения из одной колонки, в одну строку с разделителями. Для этого используется функция LISTAGG.

Например:

select listagg(s, ',') within group(order by s) res

  from (select 'a' s from dual union all

        select 'd' s from dual union all

        select 'b' s from dual union all

        select 'c' s from dual union all

        select 'a' s from dual union all

        select 'd' s from dual)

/

RES

---------

a,a,b,c,d,d

Но что делать, если нам нужно удалить дубликаты. К сожалению, LISTAGG не поддерживает команду DISTINCT. Для удаления дубликатов, нужно воспользоваться регулярным выражением  ([^,]+)(,\1)+', '\1:

select regexp_replace(listagg(s, ',') within group(order by s), '([^,]+)(,\1)+', '\1') res

  from (select 'a' s from dual union all

        select 'd' s from dual union all

        select 'b' s from dual union all

        select 'c' s from dual union all

        select 'a' s from dual union all

        select 'd' s from dual)

/

RES

---------

a,b,c,d

Случайные значения в SQL

Для поучения случайных чисел/строк в SQL, можно воспользоваться следующими функциями:

select dbms_random.string('X', 10) random_string
     , trunc(dbms_random.value(0, 100)) random_number
  from dual
connect by rownum < 6
/

Партиционированые таблицы

Для упрощения/оптимизации работы с большими таблицами, их партиционируют. При этом партиции могут находится в разных табличных пространствах. Для примера, создадим два табличных пространства

create tablespace p1 datafile 'p1.dbf' size 1m autoextend on next 1m;
create tablespace p2 datafile 'p2.dbf' size 1m autoextend on next 1m;

После этого, создадим партиционированную таблицу по хеш ключу

create table emp_part
(empno int,
 ename varchar2(20)
)
partition by hash(empno)
(partition part_1 tablespace p1,
 partition part_2 tablespace p2)
/

и добавим в нее пару записей

insert into emp_part values(1, 'aaa')
/
insert into emp_part values(2, 'bbb')
/
commit
/

Для того чтобы узнать, какие записи попали в первую партицую, выполним запрос

select *
  from emp_part partition(part_1)
/

 

Переменна TWO_TASK и LOCAL

Иногда нужно настроить подключение к базе по умолчанию, в виде

sqlplus hr/hr

без указания, к какой базе нужно подключиться.

Для этого нужно установить значение переменной:

TWO_TASK - для Linux систем export TWO_TASK=db

LOCAL - для Windows set LOCAL=db

Полезные команды Linux

cat /etc/issue -- выяснение версии ОС
uname -r -- определение версии ядра
cat /proc/version -- выяснение версии ядра
grep MemTotal /proc/meminfo -- определение общего размера памяти
grep SwapTotal /proc/meminfo -- размер пространства подкачки
df -h -- размер дискового пространства
df -m /tmp -- выяснение доступного объема дискового пространства в каталоге /tmp
cat /etc/sysctl.conf -- отображение настроек ядра
cat /etc/security/limits.conf -- список ограничений командной оболочки
cat /etc/group -- список групп
passwd -- изменение пароля для текущего пользователя
passwd oracle -- изменение пароля для пользователя oracle
umask -- проверка прав по умолчанию, должно быть 0022
cat /etc/oratab -- список каталогов ORACLE_HOME

Таблица только для чтения

В 11 Оракле появилась возможность перевода таблицы в режим READ ONLY. Это делается следующим способом:

ALTER TABLE my_tab READ ONLY;

Чтобы перевести обратно используется команда:

ALTER TABLE my_tab READ WRITE;

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

CREATE TRIGGER trgiud_my_tab
BEFORE
INSERT OR UPDATE OR DELETE
ON
my_tab
BEGIN
RAISE_APPLICATION_ERROR (-99999, 'The table is read only');
END;
/

Проверка настроек отображения даты

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

SELECT *
   FROM nls_session_parameters
 WHERE parameter like '%DATE%';

Чтобы установить формат отображения даты вместе с временем, нужно выполнить:

ALTER SESSION SET nls_date_format='dd.mm.yyyy hh24:mi:ss';

Поиск блокирующих сессий

Для отображения сессий, которые блокируют другие, нужно выполнить запрос:

select a.username
     , a.program
     , a.sid
     , a.serial#
  from v$session a
  join dba_blockers b
    on a.sid = b.holding_session
/

Аудит коннекшенов

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

SELECT CASE
         WHEN dasn.returncode=1017 THEN 'invalid password'
         ELSE 'the account is locked'
       END reason
     , dasn.*
  FROM DBA_AUDIT_SESSION dasn
 WHERE dasn.username='GUBITST'
   AND dasn.returncode IN (1017, 28000)
 ORDER BY dasn.timestamp DESC;

Настройка SSH на Linux

Для проверки, запущен у вас сервер SSH, нужно выполнить:

service sshd status

Если сервер запущен, то появится сообщение:

openssh-daemon (pid 3874) is running

Если серкер не установлен, то это можно сделать с помощью команды:

yum install openssh-server

Для подключения из iOS нужно выполнить следующую командy:

ssh user_name@host_name

или если нам нужно указать специфический потр, то пишем:

ssh -p 22 user_name@host_name

Иногда, нужно узнать, какие же порты открыты. Для этого устанавливаем NMAP:

yum nmap instal

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

nmap -sT -O localhost
которая выводит список открытых портов и кем они используются.

Запись образа Федоры на флешку

Я столкнулся с проблемой необходимости записи загрузочного образа Федоры на флешку.
Для этого я использовал следующее:

Сначала определил куда примонтирована флешка, с помощью команды:

fdisk -l

Это оказалось /dev/sdс, после этого, ввел следующую команду для записи образа:

dd if=Fedora_24.iso of=/dev/sdс bs=8M

где

Fedora_24.iso - имя образа в текущей папке

/dev/sdс - точка монтирования флешки.

Логирование работы программы

Следующий скрипт создает пакет и таблицы для записи логов.

Логи пишутся в двух режимах:
1. логи которые откатываются при откате транзакции
2. логи которые коммитятся в автономной транзакции и которые невозможно откатить.

DROP SEQUENCE seq_tbl_system_log
/
DROP SEQUENCE seq_tbl_log
/
DROP TABLE tbl_log
/
DROP TABLE tbl_system_log
/

CREATE SEQUENCE seq_tbl_system_log
/
CREATE TABLE tbl_system_log(
  id                 INTEGER CONSTRAINT pk_tbl_system_log PRIMARY KEY,
  name               VARCHAR2(30) NOT NULL CONSTRAINT un_tbl_system_log UNIQUE
)
/

CREATE SEQUENCE seq_tbl_log
/
CREATE TABLE tbl_log(
  id                 INTEGER CONSTRAINT pk_tbl_log PRIMARY KEY,
  system_log_id      INTEGER        NOT NULL,
  message            VARCHAR2(4000) NOT NULL,
  log_type_id        INTEGER        NOT NULL,
  ts                 TIMESTAMP DEFAULT SYSDATE NOT NULL,
  CONSTRAINT fk_tbl_log_tbl_system_log FOREIGN KEY (system_log_id) REFERENCES tbl_system_log (id)
)
/

CREATE OR REPLACE FORCE VIEW v_log
AS
SELECT tllg.id
     , tslg.name
     , tllg.message
     , DECODE(tllg.log_type_id, 1, 'INFO', 2, 'WARNING', 3, 'ERROR') log_type
     , tllg.ts
  FROM tbl_log tllg
       JOIN
       tbl_system_log tslg
         ON tllg.system_log_id = tslg.id
/
CREATE OR REPLACE PACKAGE pkg_log IS

PROCEDURE reg_system_log
  (p_name               IN VARCHAR2);

FUNCTION get_system_log_id
  (p_name               IN VARCHAR2)
 RETURN NUMBER;

PROCEDURE ins_log_info
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE ins_log_info_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE ins_log_warning
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE ins_log_warning_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE ins_log_error
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE ins_log_error_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2);

PROCEDURE del_log
  (p_id                 IN NUMBER);
PROCEDURE del_log_autonomous
  (p_id                 IN NUMBER);

END pkg_log;
/

CREATE OR REPLACE PACKAGE BODY pkg_log
IS
PROCEDURE reg_system_log
  (p_name               IN VARCHAR2)
IS
BEGIN
  INSERT INTO tbl_system_log
    (id,
     name)
  VALUES(
   seq_tbl_system_log.NEXTVAL,
   p_name);
END;

FUNCTION get_system_log_id
  (p_name               IN VARCHAR2)
 RETURN NUMBER
IS
  l_id NUMBER;
BEGIN
  SELECT id
    INTO l_id
    FROM tbl_system_log
   WHERE name = p_name;

  RETURN l_id;
END;

PROCEDURE ins_log
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2,
   p_log_type_id        IN NUMBER)
IS
BEGIN
  INSERT INTO tbl_log
    (id,
     system_log_id,
     message,
     log_type_id)
  VALUES(
     seq_tbl_log.NEXTVAL,
     p_system_log_id,
     p_message,
     p_log_type_id);
END;

--- INFO -----------------------------------
PROCEDURE ins_log_info
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
BEGIN
  ins_log(p_system_log_id, p_message, 1);
END;

PROCEDURE ins_log_info_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ins_log(p_system_log_id, p_message, 1);
  COMMIT;
END;

--- WARNING -------------------------------
PROCEDURE ins_log_warning
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
BEGIN
  ins_log(p_system_log_id, p_message, 2);
END;

PROCEDURE ins_log_warning_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ins_log(p_system_log_id, p_message, 2);
  COMMIT;
END;

--- ERROR ---------------------------------
PROCEDURE ins_log_error
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
BEGIN
  ins_log(p_system_log_id, p_message, 3);
END;

PROCEDURE ins_log_error_autonomous
  (p_system_log_id      IN NUMBER,
   p_message            IN VARCHAR2)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ins_log(p_system_log_id, p_message, 3);
  COMMIT;
END;

--- DELETE -----------------------------
PROCEDURE del_log
  (p_id                 IN NUMBER)
IS
BEGIN

  DELETE FROM tbl_log
    WHERE id = p_id;

END;

PROCEDURE del_log_autonomous
  (p_id                 IN NUMBER)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

  del_log(p_id);
  COMMIT;

END;

END pkg_log;
/

Определение размера таблиц

Для определения размера таблиц, я использую следующий SQL

SELECT table_name

     , TRUNC(SUM(table_size)/1024/1024)      AS table_size_meg

     , TRUNC(SUM(index_size)/1024/1024)      AS index_size_meg

     , TRUNC(SUM(lobsegment_size)/1024/1024) AS lobsegment_size_meg

     , TRUNC(SUM(lobindex_size)/1024/1024)   AS lobindex_size_meg

     , TRUNC(SUM(table_size      +

                 index_size      +

                 lobsegment_size +

                 lobindex_size)/1024/1024)   AS sum_meg

  FROM (SELECT segment_name AS table_name

             , bytes as table_size

             , 0     as index_size

             , 0     as lobsegment_size

             , 0     as lobindex_size

          FROM user_segments

         WHERE segment_type = 'TABLE'

         UNION ALL

        SELECT i.table_name

             , 0       as table_size

             , s.bytes as index_size

             , 0       as lobsegment_size

             , 0       as lobindex_size

          FROM user_indexes i

             , user_segments s

         WHERE s.segment_name = i.index_name

           AND s.segment_type = 'INDEX'

         UNION ALL

        SELECT l.table_name

             , 0       as table_size

             , 0       as index_size

             , s.bytes as lobsegment_size

             , 0       as lobindex_size

          FROM user_lobs l

             , user_segments s

         WHERE s.segment_name = l.segment_name

           AND s.segment_type = 'LOBSEGMENT'

         UNION ALL

        SELECT l.table_name

             , 0       as table_size

             , 0       as index_size

             , 0       as lobsegment_size

             , s.bytes as lobindex_size

          FROM user_lobs l

             , user_segments s

         WHERE s.segment_name = l.index_name

           AND s.segment_type = 'LOBINDEX')

 GROUP BY table_name

 ORDER BY sum_meg DESC

файл table_size.sql

Удаление дубликатов с перебивкой айдишников

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

DECLARE
   l_id   NUMBER;

   PROCEDURE sp_merge_ids (p_old          IN INTEGER,
                           p_new          IN INTEGER,
                           p_table_name   IN VARCHAR2)
   IS
      v_str   VARCHAR2 (2000)
                 := 'update table set column = :new where column  = :old';
   BEGIN
      FOR rw
         IN (SELECT urcs2.table_name, uccs.column_name
               FROM user_constraints urcs1
               JOIN user_constraints urcs2
                 ON urcs1.constraint_name = urcs2.r_constraint_name
                AND urcs1.table_name = UPPER (p_table_name)
                AND urcs1.constraint_type = 'P'
               JOIN user_cons_columns uccs
                 ON uccs.constraint_name = urcs2.constraint_name)
      LOOP
         EXECUTE IMMEDIATE REPLACE (REPLACE (v_str, 'table', rw.table_name),
                                    'column',
                                    rw.column_name)
            USING p_new, p_old;
      END LOOP;
   END;
BEGIN
   FOR r IN (SELECT employee_id, employee_name
               FROM tbl_employee
              WHERE ROWID NOT IN (SELECT MAX (ROWID)
                                    FROM tbl_employee
                                   GROUP BY employee_name))
   LOOP
      SELECT employee_id
        INTO l_id
        FROM tbl_employee
       WHERE employee_name = r.employee_name
         AND employee_id <> r.employee_id
         AND ROWNUM = 1;

      sp_merge_ids (r.employee_id, l_id, 'tbl_employee');

      DELETE FROM tbl_employee
            WHERE employee_id = r.employee_id;
   END LOOP;
END;
/

В этом скрипте, удаляются дубликаты из таблицы tbl_employee. Сначала в цикле мы определяем записи для удаления. С помощью селекта находим дубликат, который должен остаться и при помощи процедуры sp_merge_ids перебиваем старые айдишники в подчиненных таблицах, на новые. И в заключении удаляем запись.