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

В высоконагруженных 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