Стоимость 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;