Мой блог: 2009

вторник, 8 декабря 2009 г.

Подготовка RHEL6/7 Для Oracle 12c

Note 1961997.1

Установка пакетов:

yum -y install binutils.x86_64 \
compat-libcap1.x86_64 \
compat-libstdc++-33.i686 \
compat-libstdc++-33.x86_64 \
gcc.x86_64 \
gcc-c++.x86_64 \
glibc.i686 \
glibc.x86_64 \
glibc-devel.i686 \
glibc-devel.x86_64 \
ksh.x86_64 \
libaio.i686 \
libaio.x86_64 \
libaio-devel.i686 \
libaio-devel.x86_64 \
libgcc.i686 \
libgcc.x86_64 \
libstdc++.i686 \
libstdc++.x86_64 \
libstdc++-devel.i686 \
libstdc++-devel.x86_64 \
libXi.i686 \
libXi.x86_64 \
libXtst.i686 \
libXtst.x86_64 \
make.x86_64 \
sysstat.x86_64 \

Для устранения ошибки Bug 21151912 -> Doc ID 2062336.1

Installation of 12.1.0.2 Oracle Database Server/Client on RHEL 7/OL7
reports pre-requisite package 'compat-libstdc++' missing. (Doc ID 2062336.1)

yum -y install compat-libstdc++-33.x86_64 \
compat-libstdc++-33.i686

ALERT: Setting RemoveIPC=yes on Redhat 7.2 Crashes ASM and Database Instances

as Well as Any Application That Uses a Shared Memory Segment (SHM) or Semaphores (SEM) (Doc ID 2081410.1)

Set RemoveIPC=no in /etc/systemd/logind.conf

Set RemoveIPC=no in /etc/systemd/logind.conf
Reboot the server or restart systemd-logind as follows:

$ su - root
$ systemctl daemon-reload
$ systemctl restart systemd-logind

Смотрим размеры физической памяти и Swap

# grep MemTotal /proc/meminfo
# grep SwapTotal /proc/meminfo

Установка параметров ядра

Общий объём доступной разделяемой памяти (в страницах)

kernel.shmall

Max shared memory = shmall * page_size (так будет в байтах)

Размер страницы:

$ getconf PAGE_SIZE
4096

Например, мы хотим под SGA выделить 10GB

10*1024*1024*1024=107374182240 (10Gb in bytes)
107374182240/4096=2621440
kernel.shmall = 2621440

kernel.shmall рекомендуется выставлять в максимальное значение
(т.е. равным размеру физической памяти в системе в страницах)
Минимальное рекомендованное значение kernel.shmall = 6291456

Например, в системе 64GB RAM, выделяем всю под shared memory
Если размер страницы равен 4096 Bytes, то kernel.shmall вычисляется так:

64 Gb
64 * 262144 = 16777216

65536 Mb
65536 * 256 = 16777216

67108864 Kb
67108864 / 4 = 16777216.0

68719476736 Bytes
68719476736 / 4096 = 16777216.0

kernel.shmall = 16777216

Закомментируем существующий параметр:
sed -ie 's/^kernel.shmall/#kernel.shmall/g' /etc/sysctl.conf

Вычисляем и устанавливаем новое значение:
MemTotal Kb / 4
echo "kernel.shmall = $(awk '$1 == "MemTotal:"{tmp=$2/4; printf"%0.0f\n", tmp}' /proc/meminfo)" >> /etc/sysctl.conf

Применить параметры ядра

$ sysctl -p

Максимальный размер сегмента разделяемой памяти (в байтах)

kernel.shmmax

Рекомендуется выставлять 1/2 от размера физической памяти в системе (в байтах)

К примеру для 64 Гб это будет:

64*1024*1024*1024=68719476736 (64Gb in bytes)
68719476736/2=34359738368
kernel.shmmax = 34359738368

64 Gb
64 * 536870912 = 34359738368

65536 Mb
65536 * 524288 = 34359738368

67108864 Kb
67108864 * 512 = 34359738368

68719476736 Bytes
68719476736 / 2 = 34359738368

kernel.shmmax = 34359738368

Закомментируем существующий параметр:
sed -ie 's/^kernel.shmmax/#kernel.shmmax/g' /etc/sysctl.conf

Вычисляем и устанавливаем новое значение:
MemTotal Kb * 512
echo "kernel.shmmax = $(awk '$1 == "MemTotal:"{tmp=$2*512; printf"%0.0f\n", tmp}' /proc/meminfo)" >> /etc/sysctl.conf

Применить параметры ядра

$ sysctl -p

Максимальное число сегментов разделяемой памяти для всей системы

kernel.shmmni

Рекомендуется установить kernel.shmmni = 4096

Применить параметры ядра

$ sysctl -p

Сетевые параметры:

Устранить обрывы связи для долго работающих запросов
при использовании фаерволов между БД и клиентом

Добавить в /etc/sysctl.conf

cat >> /etc/sysctl.conf << EOF
###########################################
### For CheckPoint Timeout ################
###########################################
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_time = 2700
###########################################
EOF

Применить параметры ядра

$ sysctl -p

Настройка Shell Limits:

Убедитесь, что последняя версия PAM загружена в
/etc/pam.d/login file

cat > /etc/pam.d/login << EOF
#+-------------------------------+
#| Setting parameters for Oracle |
#+-------------------------------+
session required pam_limits.so
EOF

Set Shell limits for oracle users:

cat > /etc/security/limits.d/70-oracle.conf << EOF
#+-------------------------------+
#| Setting parameters for Oracle |
#+-------------------------------+
oracle soft nofile 131072
oracle hard nofile 131072
oracle soft nproc 131072
oracle hard nproc 131072
# For 64 Gb Installed : 67108864=64*1024*1024 (размер в Kb)
oracle soft memlock 67108864
oracle hard memlock 67108864
#
EOF

Set Shell limits for root users:

cat > /etc/security/limits.d/70-root.conf << EOF
#+-------------------------------------+
#| Setting limits root [for Oracle DB] |
#+-------------------------------------+
root soft nofile 131072
root hard nofile 131072
root soft nproc 131072
root hard nproc 131072
# For 64 Gb Installed : 67108864=64*1024*1024 (размер в Kb)
root soft memlock 67108864
root hard memlock 67108864
#
EOF

Проверяем:

su - oracle -c 'id; ulimit -a'
su - root -c 'id; ulimit -a'

Настройка /dev/shm

Для сервера с RAM=64 GB

su - root
df -h /dev/shm/
umount /dev/shm
mount -t tmpfs shmfs -o size=64g /dev/shm

Добавить строчку в /etc/fstab следующего содержания:

cat >> /etc/fstab <
tmpfs /dev/shm tmpfs size=64g 0 0
EOF

Требование к размеру файловых систем

/tmp -> требуется минимум 1 GB

Установить точное время

ntpdate omega.local
Настройка NTP daemon
Настроить запуск демона с параметром -x :
ntpd -u ntp:ntp -p /var/run/ntpd.pid -x

Настройка разрешения имен

В файле /etc/resolv.conf добавить domain omega.local

Installing the cvuqdisk Package for Linux (RAC only)

rpm -qi cvuqdisk
rpm -iv cvuqdisk-1.0.9-1.rpm

Disabling Transparent HugePages

Необходимо выбрать наиболее удобный вариант

Настройка загрузчика

в /boot/grub/grub.conf -> добавить опцию transparent_hugepage=never

kernel /vmlinuz-2.6.32-504.1.3.el6.x86_64 ro root=LABEL=/ transparent_hugepage=never

Скрипт автозапуска

В файле /etc/rc.local добавить скрипт отключение Transparent HugePages

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

Начинася с Oracle Linux 7 в ядер UEK, transparent hugepages отключены по умолчанию,
никаких действий принимать не надо!

Настройка Hugepages

Oracle может использовать Hugepages для размещения SGA.
Hugepages не вытесняются в swap.
Процессор уменьшит число трансляций виртуальных адресов памяти в физические.
Кеш CPU Translation Lookaside Buffers (TLB) будет реже устаревать.

При использовании больших страниц памяти shared memory для СУБД настраивать не нужно.
Но если используется ASM, то для его экземпляра зарезервируем 1024 МБ shared memory,
поэтому размер /dev/shm быть не менее 1024 МБ

$ sqlplus / as sysasm

Для версии 11.2.0.3 и ниже (Note 265633.1) минимально необходимые параметры:
alter system set memory_target    = 320M scope=spfile sid='*';
alter system set shared_pool_size = 128M scope=spfile sid='*';
alter system set large_pool_size = 64M scope=spfile sid='*';

Для версии 11.2.0.4 и выше (Note 1536039.1 и Note 1982132.1):
alter system set memory_target    = 1024M scope=spfile sid='*';
alter system set shared_pool_size = 128M scope=spfile sid='*';
alter system set large_pool_size =   64M scope=spfile sid='*';

Для высоконагруженных многопроцессорных систем (Note 437924.1)
Для версии 11.2.0.3 и выше:
alter system set memory_max_target= 2048M scope=spfile sid='*';
alter system set memory_target    = 1536M scope=spfile sid='*';
alter system set shared_pool_size = 128M   scope=spfile sid='*';
alter system set large_pool_size = 64M    scope=spfile sid='*';

Необходимо отключить автоматическое управление памятью AMM
alter system set memory_target=0

Количество больших страниц памяти задаётся параметром ядра vm.nr_hugepages.
Можно динамически изменять количество hugepages, но лучше резервировать их при загрузке ОС.

Информацию об использовании hugepages можно узнать из /proc/meminfo:

bash-4.2$ grep Huge /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
bash-4.2$

Покажет
- сколько всего выделено страниц
- сколько свободных страниц
- сколько зарезервированных страниц
- размер одной страницы памяти

Рассчитаем необходимое количество страниц:

На сервере 64 Gb
Параметры экземпляра такие:

memory_max_target    = 61440M
memory_target        = 61440M
sga_max_size         = 36864M
sga_target           = 36864M
pga_aggregate_target = 24576M

Если размер страницы равен 2 Mb (2048 Kb)
то количество страниц расчитываем так:

для sga_max_size = 36 Gb

36 Gb
36 * 512               = 18432

36864 Mb
36864 * 0.5            = 18432

37748736 Kb
37748736 / 2048        = 18432

38654705664 Bytes
38654705664 / 2097152 = 18432

Необходимо выделить небольшой запас свободных страниц hugepages

18432 увеличим до 18450

vm.nr_hugepages = 18450

Проверьте настройку лимитов

/etc/security/limits.conf

Пользователю, от которого запускается СУБД разрешено блокировать память такого размера

oracle soft memlock 67108864
oracle hard memlock 67108864

$ ulimit -l

Отключите AMM

SQL> alter system set memory_target=0;
System altered.

SQL> alter system set memory_max_target=0;
System altered.

Установите рассчитанное значение параметру vm.nr_hugepages

Под root

# vi /etc/sysctl.conf
vm.nr_hugepages = 18450

# sysctl -p
# sysctl vm.nr_hugepages=18450

shutdown immediate;

# reboot

Проверяем:

# grep ^Huge /proc/meminfo

Примечание:

Есть такой bash-скрипт (MOS) note 401749.1
Который рассчитывает рекомендуемое значение vm.nr_hugepages.

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk {'print $2'}`
# Start from 1 pages to be on the safe side and guarantee 1 free HugePage
NUM_PG=1
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk {'print $5'} | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

$ chmod u+x ./hugepages_setting.sh
$ ./hugepages_settings.sh
.
.
.
Recommended setting: vm.nr_hugepages = nnnn

среда, 2 декабря 2009 г.

Основные команды FTP

1. ascii
2. bin
3. bye, exit -завершение
4. ls, dir
5. pwd, lpwd -смотрим где находимся на удален или локальн хосте
6. !<локальная команда>
7. close, disconnect -закрытие
8. prompt -интерактивный ввод комманд
9. get <файл> -загрузка с удаленного на локальный
10. put <файл> -пересылка на удаленный
12. mget <файл> -загрузка нескольких файлов с удаленного
13. mput <файл> -пересылка нескольких файлов на удаленный
14. cd <каталог> -смена текущего каталога на удаленном
15. lcd <каталог> -смена текущего каталога на локальном

Примеры:

ftp ftp.example.org
>ls
>dir
>lpwd
>get имя_удаленного_файла имя_локального_файла

>get notes notes2

>bin
>get win.exe

>ascii
>mget s*
чтобы постоянно не подтверждать используйте prompt

>prompt
>mget ora*

>mput dd*

>prompt
>mput k*

среда, 11 ноября 2009 г.

Monitor the progress of the transaction recovery: Oracle


V$FAST_START_TRANSACTIONS
V$FAST_START_SERVERS
V$SESSION_LONGOPS


SELECT USED_UREC
FROM V$TRANSACTION;


To estimate when the monitored rollback process will finish

SELECT TIME_REMAINING, SOFAR / TOTALWORK * 100 PCT
  FROM V$SESSION_LONGOPS
 WHERE SID = :sid
   AND OPNAME = 'Transaction Rollback' ;


set linesize 100 
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; 
select usn, 
       state, 
       undoblockstotal "Total", 
       undoblocksdone "Done", 
       undoblockstotal-undoblocksdone "ToDo", 
       decode(cputime,0,'unknown',sysdate+(((undoblockstotal- undoblocksdone)/
       (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" 
from v$fast_start_transactions; 


select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", 
       ktuxesiz, 
       ktuxesta 
from   x$ktuxe 
where  ktuxecfl = 'DEAD'; 


SELECT T.USN
    , R.NAME
    , USERNAME
    , SERIAL#
    , TERMINAL
    , PROGRAM
    , T.STATE
    , ROUND (UNDOBLOCKSDONE / UNDOBLOCKSTOTAL  *  100, 1 ) PCT_DONE
    , T.UNDOBLOCKSDONE
    , T.UNDOBLOCKSTOTAL
    , T.SLT
    , T.SEQ
    , T.PID
    , T.CPUTIME
    , T.PARENTUSN
    , T.PARENTSLT
    , T.PARENTSEQ
 from v$fast_start_transactions T
    , v$TRANSACTION R
    , v$process     p
WHERE  T.PARENTUSN  = R.XIDUSN
 and  T.PARENTSLT  = R.XIDSLOT
 and  T.PARENTSEQ  = R.XIDSQN
 and  t.pid        = p.pid

вторник, 10 ноября 2009 г.

Управление памятью (AMM, ASMM)

AMM (Automatic Memory Management)

В 12с можно установить значение параметру инициализации:

MEMORY_TARGET

Тогда экземпляр при запуске затребует у ОС это количество памяти и автоматически выделит размеры различным пулам.

Это динамический параметр и его можно изменять без перезапуска экземпляра.

alter system set memory_target     = 13120M     scope=spfile sid='*';

Чтобы случайно не получилось установить memory_target слишком большим, можно для него установить верхний предел:

alter system set memory_max_target = 13120M     scope=spfile sid='*';

Это статический параметр и его изменения потребуют остановки экземпляра.

При использовании AMM экземпляр перераспределяет память между SGA и PGA автоматически по мере изменения нагрузки.
При создании БД с помощью DBCA по умолчанию БД будет использовать AMM.

Руками включить использование AMM можно так:

alter system set memory_target          = 13120M scope=spfile sid='*';
alter system set sga_target             = 0      scope=spfile sid='*';
alter system set pga_aggregate_target = 0      scope=spfile sid='*';

И дополнительно можно установить ограничение сверху:

alter system set memory_max_target = 13120M     scope=spfile sid='*';

Если MEMORY_TARGET не задан, то AMM не будет использоваться, даже если будет установлен MEMORY_MAX_TARGET.

Если параметр MEMORY_MAX_TARGET не задан то считается что он равен значению MEMORY_TARGET.

Нижние границы областей памяти:

Параметр SGA_TARGET необязателен при использовании AMM.

Если значение SGA_TARGET задано и значение MEMORY_TARGET также задано,
то величина SGA_TARGET становится минимально возможной величиной памяти, которая может быть выделена SGA автоматически.

Параметр PGA_AGGREGATE_TARGET также необязателен при использовании AMM.

Если значение PGA_AGGREGATE_TARGET задано и значение MEMORY_TARGET также задано,
то величина PGA_AGGREGATE_TARGET становится минимально возможной величиной памяти, которая может быть выделена PGA автоматически.

MEMORY_TARGET и SGA_TARGET - нижние границы областей памяти в режиме AMM

MEMORY_MAX_TARGET и SGA_MAX_SIZE - это верхние границы.

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

SGA_TARGET должно быть равно SGA_MAX_SIZE

Допускается установка нижних границ (минимально необходимых) общих буферных областей SGA:

alter system set db_cache_size     = 512M     scope=spfile sid='*';
alter system set shared_pool_size = 512M     scope=spfile sid='*';
alter system set java_pool_size    = 128M     scope=spfile sid='*';
alter system set large_pool_size   = 64M     scope=spfile sid='*';
alter system set stream_pool_size = 64M     scope=spfile sid='*';

ASMM (Automatic Shared Memory Management)
С помощью параметра инициализации SGA_TARGET задается общий объем памяти SGA.
Экземпляр автоматически распределит эту память между различными частями SGA.
При использовании ASMM некоторые компоненты SGA нужно задавать вручную:

LOG_BUFFER
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE

Вся память, выделенная вручную этим компонентам, вычитается из величины SGA_TARGET,
а остаток распределяется между компонентами, размер которых управляется автоматически:

SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
DB_CACHE_SIZE
STREAM_POOL_SIZE

Механизмы AMM и ASMM используют статистики, поэтому параметр инициализации STATISTIC_LEVEL должен быть установлен в значение TYPICAL или ALL.

Распределение памяти SGA и PGA (С использованием технологии AMM)
Note: 1392549.1 и 443746.1

Для OLTP приложения:

60% SGA_TARGET
40% PGA_AGGREGATE_TARGET

Для DWH приложения:

40% SGA_TARGET
60% PGA_AGGREGATE_TARGET

Пример установки параметров памяти:

Текущие параметры такие:

select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'memory_max_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'memory_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'sga_max_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'sga_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'pga_aggregate_target'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'db_cache_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'shared_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'java_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'large_pool_size'
union all
select 'alter system set '|| name ||' = '||value/1024/1024||'M scope=spfile sid=''*'''||';' from SYS.V_$PARAMETER where name = 'streams_pool_size';

'ALTERSYSTEMSET'||NAME||'='||VALUE/1024/1024||'MSCOPE=SPFILESID=''*'''||';'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter system set memory_max_target = 0M scope=spfile sid='*';
alter system set memory_target = 0M scope=spfile sid='*';
alter system set sga_max_size = 7376M scope=spfile sid='*';
alter system set sga_target = 7376M scope=spfile sid='*';
alter system set pga_aggregate_target = 2458M scope=spfile sid='*';
alter system set db_cache_size = 0M scope=spfile sid='*';
alter system set shared_pool_size = 0M scope=spfile sid='*';
alter system set java_pool_size = 0M scope=spfile sid='*';
alter system set large_pool_size = 0M scope=spfile sid='*';
alter system set streams_pool_size = 0M scope=spfile sid='*';

10 rows selected.

Скрипт для расчета параметров памяти в зависимости от размера OS Memory на сервере.

Расчёт производится для OLTP приложения:
60% SGA_TARGET
40% PGA_AGGREGATE_TARGET

Для DWH приложения меняем местами значения sga_target (sga_max_size) и значение pga_aggregate_target

DECLARE
    os_memory            number;
    granule_size         number;
    memory_target        number;
    sga_target           number;
    pga_aggregate_target number;
    iname                varchar2(16);
BEGIN

    SELECT VALUE/1024/1024/1024 into os_memory
    FROM   v$osstat
    WHERE stat_name = 'PHYSICAL_MEMORY_BYTES';
    os_memory := ceil(os_memory) * 1024;

    -- for os_memory < 10GB set only memory_target
    -- os_memory := 10*1024;   -- 10GB
    -- os_memory := 12*1024;   -- 12GB
    -- os_memory := 16*1024;   -- 16GB
    -- os_memory := 18*1024;   -- 18GB
    -- os_memory := 20*1024;   -- 20GB
    -- os_memory := 24*1024;   -- 24GB
    -- os_memory := 32*1024;   -- 32GB
    -- os_memory := 48*1024;   -- 48GB
    -- os_memory := 64*1024;   -- 64GB
    -- os_memory := 100*1024;   -- 100GB
    -- os_memory := 128*1024;   -- 128GB
    -- os_memory := 192*1024;   -- 192GB
    -- os_memory := 256*1024;   -- 256GB

    select granule_size/1024/1024 into granule_size
    from v$sga_dynamic_components
    where component='DEFAULT buffer cache';

    select instance_name into iname from v$instance;

    if os_memory > 16384 then
       memory_target := os_memory - 4096;
    else
       memory_target := os_memory * 0.8;
    end if;

    sga_target := (memory_target * 0.6);
    sga_target := ceil(sga_target/granule_size)*granule_size;

    memory_target := ceil(memory_target/granule_size)*granule_size;

    pga_aggregate_target := memory_target - sga_target;

    DBMS_OUTPUT.PUT_LINE( '--'||os_memory/1024||'GB' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__db_cache_size"         scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__java_pool_size"        scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__large_pool_size"       scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__pga_aggregate_target" scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__sga_target"            scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__shared_io_pool_size"   scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__shared_pool_size"      scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system reset "__streams_pool_size"     scope=spfile sid='''||iname||''';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set memory_max_target    = ' ||memory_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set memory_target        = ' ||memory_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set sga_max_size         = ' ||sga_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set sga_target           = ' ||sga_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( 'alter system set pga_aggregate_target = ' ||pga_aggregate_target||'M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system reset pga_aggregate_target scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set db_cache_size      = 512M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set shared_pool_size   = 512M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set java_pool_size     = 128M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set large_pool_size    = 64M scope=spfile sid=''*'';' );
    DBMS_OUTPUT.PUT_LINE( '--alter system set streams_pool_size = 64M scope=spfile sid=''*'';' );
END;
/

--Для 24GB OS Memory на сервере будет предложено установить следующие значения:

alter system reset "__db_cache_size"         scope=spfile sid='testdb';
alter system reset "__java_pool_size"        scope=spfile sid='testdb';
alter system reset "__large_pool_size"       scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target" scope=spfile sid='testdb';
alter system reset "__sga_target"            scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size"   scope=spfile sid='testdb';
alter system reset "__shared_pool_size"      scope=spfile sid='testdb';
alter system reset "__streams_pool_size"     scope=spfile sid='testdb';
alter system set memory_max_target    = 20480M scope=spfile sid='*';
alter system set memory_target        = 20480M scope=spfile sid='*';
alter system set sga_max_size         = 12288M scope=spfile sid='*';
alter system set sga_target           = 12288M scope=spfile sid='*';
alter system set pga_aggregate_target = 8192M scope=spfile sid='*';
--alter system reset pga_aggregate_target scope=spfile sid='*';
--alter system set db_cache_size      = 512M scope=spfile sid='*';
--alter system set shared_pool_size   = 512M scope=spfile sid='*';
--alter system set java_pool_size     = 128M scope=spfile sid='*';
--alter system set large_pool_size    = 64M scope=spfile sid='*';
--alter system set streams_pool_size = 64M scope=spfile sid='*';

--А для 256GB OS Memory на сервере будет предложено установить такие значения:

alter system reset "__db_cache_size"         scope=spfile sid='testdb';
alter system reset "__java_pool_size"        scope=spfile sid='testdb';
alter system reset "__large_pool_size"       scope=spfile sid='testdb';
alter system reset "__pga_aggregate_target" scope=spfile sid='testdb';
alter system reset "__sga_target"            scope=spfile sid='testdb';
alter system reset "__shared_io_pool_size"   scope=spfile sid='testdb';
alter system reset "__shared_pool_size"      scope=spfile sid='testdb';
alter system reset "__streams_pool_size"     scope=spfile sid='testdb';
alter system set memory_max_target    = 258048M scope=spfile sid='*';
alter system set memory_target        = 258048M scope=spfile sid='*';
alter system set sga_max_size         = 154832M scope=spfile sid='*';
alter system set sga_target           = 154832M scope=spfile sid='*';
alter system set pga_aggregate_target = 103216M scope=spfile sid='*';
--alter system reset pga_aggregate_target scope=spfile sid='*';
--alter system set db_cache_size      = 512M scope=spfile sid='*';
--alter system set shared_pool_size   = 512M scope=spfile sid='*';
--alter system set java_pool_size     = 128M scope=spfile sid='*';
--alter system set large_pool_size    = 64M scope=spfile sid='*';
--alter system set streams_pool_size = 64M scope=spfile sid='*';

Смотрим значения параметров:

select     name || '=' || decode(type, 2, '''') || value
    || decode(type, 2, '''') parameter
from    v$parameter
where    isdefault = 'FALSE'
and    value is not null
order    by name
/

Распределение памяти экземпляра в режиме AMM:

col component format a20
break on report
compute sum of current_size_mb on report
select component, round(current_size/1024/1024,1) as current_size_mb
from v$memory_dynamic_components
where component like '%Target%';

Потребление памяти на уровне сессии

select *
from (
select
    p.spid,
    p.pid,
    s.sid,
    s.serial#,
    substr(trim(s.machine),1,32) machine,
    s.username,
    s.osuser,
    round(p.pga_used_mem/1024/1024,2) pga_used_mb,
    round(p.pga_alloc_mem/1024/1024,2) pga_alloc_mb,
    round(p.pga_max_mem/1024/1024,2) pga_max_mb,
    row_number() over (order by round(p.pga_max_mem/1024/1024,2) desc) as rn
from
   v$process p,
   v$session s
where p.addr = s.paddr
) where rn < 21
order by pga_max_mb desc;

Чтобы понять, является ли текущий размер SGA оптимальным, изучите следующие представления:

select * from V$SGA_DYNAMIC_COMPONENTS
select * from V$SGA_CURRENT_RESIZE_OPS
select * from V$SGA_RESIZE_OPS
select * from V$SGA_DYNAMIC_FREE_MEMORY

SELECT component,
        current_size/1024/1024,
        min_size/1024/1024,
        max_size/1024/1024
FROM    v$memory_dynamic_components
WHERE   current_size != 0;

Для точной настройки значений механизма AMM можно использовать:

select * from V$MEMORY_TARGET_ADVICE

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

Для настройки значений параметров SGA и PGA:

select * from V$SGA_TARGET_ADVICE
select * from V$PGA_TARGET_ADVICE

Примечание для OS Solaris:

Настройка AMM или ASMM на ОС Solaris заставит ОС использовать Dynamic Intimate Shared Memory (DISM).
Это позволяет динамически изменять размер shared memory segments.
Динамические свойства SGA используют DISM при включенном AMM или ASMM.
Начиная с Oracle 9i и выше, если SGA_MAX_SIZE > SGA_TARGET (или суммы sga compenents), тогда используется DISM.
В 11g DISM также используется, если установлен MEMORY_TARGET или MEMORY_MAX_TARGET.
В противном случае используется ISM (если DISM не включен).
DISM (в отличие от ISM) требует резервирования swap для всех страниц, независимо от того, выделены они или нет.
Это означает, что вам нужно будет настроить swap размером не менее чем сумма размеров SGA для экземпляров, использующих DISM.
Иначе получим memory errors при старте экземпляра.

Используется ли DISM для экземпляра Oracle, можно узнать так:

$ ps -aef | grep dis

должен быть процесс:
ora_dism_$ORACLE_SID

Избежать использования DISM и большого размера swap можно используя manual memory management.

суббота, 7 ноября 2009 г.

Configuring sendmail on Solaris 10 to use a smart host

Solaris 10 comes with sendmail, a very powerful but also very complex mail server.
This tutorial covers only a very specific scenario, for which I was unable to find an example: I wanted sendmail to accept mail from any computer in my LAN and relay (forward) all of those mails to a "smart host", i.e. an external mail server provided by my ISP.

Wondering what I need this setup for? I have a couple of web servers which generate mail to external addresses, and my ISP blocks port 25 - except to its own mail server. I don't have a need to process internal mails in the LAN or incoming mail from the internet, as mails for me and my users are hosted externally.

If that's your scenario too, here's what you have to do:

* Check that the mail server machine has a valid hostname.
Do this by running:
/usr/sbin/check-hostname
If it tells you the machine has a valid FQDN, everything's fine. Otherwise, just follow the suggestion to edit /etc/hosts (instructions are given by the program, if applicable).
* Edit /etc/mail/cf/cf/local.mc, e. g. by running:
vi /etc/mail/cf/cf/local.mc
Replace the line define(`confFALLBACK_SMARTHOST', `mailhost$?m.$m$.')dnl with:
define(`SMART_HOST', `YOUR_ISP_MAIL_SERVER')dnl
Make sure not to change the apostrophs - the opening one always points to the upper left, while the closing one is a vertical one.
Remove the line MAILER(`local')dnl.
Remove the line LOCAL_NET_CONFIG.
Remove the last line, which says something like R$* < @ $* .$m. > $* $#esmtp $@ $2.$m $: $1 < @ $2.$m. > $3.
* We want to listen to any IP address, so remove the following lines too:
DAEMON_OPTIONS(`NAME=NoMTA4, Family=inet, Addr=127.0.0.1')dnl
DAEMON_OPTIONS(`Name=MSA4, Family=inet, Addr=127.0.0.1, Port=587, M=E')dnl
If you did this step, make sure to protect your mail server from abuse by configuring your firewall appropriately.
* Change directory to /etc/mail/cf/cf, e. g. by running:
cd /etc/mail/cf/cf
* Compile and deploy the changed configuration file by running:
/usr/ccs/bin/m4 ../m4/cf.m4 local.mc > /etc/mail/sendmail.cf
* If you made sendmail listen to any IP address above, you probably want to allow your other servers in the LAN to relay mails through it. Enable this by editing /etc/mail/relay-domains, e. g. by running:
vi /etc/mail/relay-domains
The file might not exist yet.
I'm not sure of the correct syntax for this file, so I entered two lines like this:
192.168.101.0/24
192.168.101.
I don't know which one is correct and don't really care since it works. Of course use the correct address and subnetmask for your LAN here!
* Finally, make sendmail aware of the configuration changes by running:
svcadm restart sendmail

вторник, 3 ноября 2009 г.

Vi: Search and Replace

Change to normal mode with .

Search (Wraped around at end of file):

Search STRING forward :   / STRING.
Search STRING backward:   ? STRING.
Repeat search:   n
Repeat search in opposite direction:  N  (SHIFT-n)

Replace: Same as with sed, Replace OLD with NEW:

First occurrence on current line:      :s/OLD/NEW
Globally (all) on current line:        :s/OLD/NEW/g
Between two lines #,#:                 :#,#s/OLD/NEW/g
Every occurrence in file:              :%s/OLD/NEW/g

пятница, 9 октября 2009 г.

Oracle: Troubleshoot Database Control startup



Common startup issues:
Environment variables;  
SYSMAN/DBSNMP issues  
Timezone  
Network  
Configuration (wrong port assignment, wrong connection string)
Troubleshooting steps:

1. Check if environment variables are set correctly. Mainly, check, ORACLE_HOME, PATH, LD_LIBRARY_PATH, LANG.

2. Check if the SYSMAN/DBSNMP account is open. To check, connect to database as SYS and run:

SQL> select username, account_status from dba_users where username in ('SYSMAN','DBSNMP');

and the output:

USERNAME  ACCOUNT_STATUS
--------------  ------------------------
DBSNMP       OPEN
SYSMAN       OPEN

emagent.trc errors

2008-01-19 11:20:21,231 [HttpRequestHandler-28730188] ERROR conn.ConnectionService verifyRepositoryEx.433 - Invalid Connection Pool. ERROR = ORA-28000: the account is locked

3. Check the timezone set in the environment. If the timezone does not match one of the values in ORACLE_HOME/sysman/admin/supportedtzs.lst the dbcontrol agent will not start. When checking dbcontrol status returns: "EM Deamon is not running".

emdb.nohup errors

----- Wed Jul 25 22:31:53 2007::property 'agentTZregion' in '/usr/pkg/oracle/product/10.2.0/db//sysman/config/emd.properties' contains an invalid value of 'TZ set to '.Agent start up can not proceed.This value might have been manually modified to be an incorrect value.This value needs to be set to one of the values listed in '/usr/pkg/oracle/product/10.2.0/db/sysman/admin/supportedtzs.lst'. Execute 'emctl config agent getTZ' and see if this is an appropriate value. -----

4. Check the OS network configuration:
static IP (no DHCP generated IP);  
hostname must not contain "_" (underscore character);  
nslookup, ping must resolve the fully qualified name;  
"hosts" file entries pattern:
             
 lookup and reverse lookup must work;  
IPv6 is not supported;
5. Check the database network configuration:
check "lsnrctl status" if shows same listener details as ORACLE_HOME/network/admin/listener.ora
check TNS status with tnsping utility
emoms.trc errors

ORA-2005-07-04 12:23:08,120 [XMLLoader0] ERROR conn.ConnectionService verifyRepositoryEx.418 - Invalid Connection Pool. ERROR = Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor The Connection descriptor used by the client was: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=)))(CONNECT_DATA=(SERVIC_NAME=)))

6. Check the configuration files are correct.
ORACLE_HOME//sysman/emd.properties. Check properties:
 REPOSITORY_URL=http://:/em/upload/

EMD_URL=http://:/emd/main
 ORACLE_HOME//sysman/emoms.properties. Check properties:
 oracle.sysman.eml.mntr.emdRepConnectDescriptor - must have a valid connection string
oracle.sysman.eml.mntr.emdRepPort=
oracle.sysman.eml.mntr.emdRepDBName=
oracle.sysman.emSDK.svlt.ConsoleServerPort=
oracle.sysman.emSDK.svlt.ConsoleServerHost=
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=

If none of the above helps check the Known Issues list below.
Known issues
Generic platform

1. EM Deamon is not running. Database Control starts successfully, however checking the status shows "EM Deamon is not running". Also checking the agent status shows that agent is not running.
//sysman/log/emdctl.trc shows:

2005-11-06 18:04:40 Thread-3840 ERROR main: nmectl.c: nmectl_validateTZRegion, agentTZoffset=120, and testTZoffset for GMT:0 do not match

Solution:

a) Set the desired time zone at the OS level:
    Windows: Control Panel->Date&Time->Time Zone
    Linux/Unix: export TZ=
the timezone select must correspond to one of the timzones in ORACLE_HOME/sysman/admin/supportedtzs.lst

b) Stop the dbconsole
    ORACLE_HOME/bin/emctl stop dbconsole

c) Run:
    ORACLE_HOME\bin\emctl config agent getTZ
This may return a diffrent timezone than the one set in step 1.

    ORACLE_HOME\bin\emctl config agent updateTZ
This will update the ORACLE_HOME\\sysman\config\emd.properties file with the correct timezone.

d) Start dbconsole
ORACLE_HOME/bin/emctl start dbconsole

2. GIM-00104: Health check failed to connect to instance.
ORACLE_HOME//sysman/log/emagent.trc shows:

2006-05-04 13:17:29 Thread-2206875655 ERROR fetchlets.healthCheck: GIM-00104:Health check failed to connect to instance.
GIM-00090: OS-dependent operation:open failed with status: 24
GIM-00091: OS failure message: Too many open files
GIM-00092: OS failure occurred at: sskgmsmr_7
2006-05-04 13:17:29 Thread-2206875655 ERROR engine: [oracle_database,tmprod_tmprod2,health_check] : nmeegd_GetMetricData failed : Instance HealthCheck initialization failed due to one of the following causes: the owner of the EM agent process is not same as the owner of the Oracle instance processes; the owner of the EM agent process is not part of the dba group; or the database version is not 10g (10.1.0.2) and above.
2006-05-04 13:17:30 Thread-2206892039 ERROR http: snmehl_connect: failed to create socket: Too many open files (error = 24)

Solution:

Check Note 368612.1

3. Error starting ORMI-Server. Unable to bind socket: Address already in use. Trying to start db control fails without an obvious reason.
ORACLE_HOME//sysman/log/emdb.nohup shows:

----- Mon Nov 6 10:34:13 2006::Console Launched with PID 3441 at time Mon Nov 6 10:34:13 2006
06/11/06 10:34:16 Error starting ORMI-Server. Unable to bind socket: Address already in use

Solution:

Check Note 398499.1, Note 419586.1, Note 438504.1, Note 358961.1

4. Unable to determine local host from URL.
emctl start dbconsole shows:

EMD_URL=http://:/emd/main

Solution:

Check A HREF="/metalink/plsql/showdoc?db=NOT&id=266027.1&blackframe=1" >Note 266027.1, Note 343748.1

5. OC4J Configuration Issue: $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid not found

emctl start dbconsole fails with the following error:
OC4J Configuration Issue: $ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_host_sid not found

There are three cases for this issue:

a) ORACLE_HOME variable needed to run emctl is set to the wrong database home. Set the right value and retry the process.
b) Network changes. If the hostname where Database Control was created in the first place is not resolvable anymore startup will fail with above error.

Example:

Database configuration folders:
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_myhost_sid
ORACLE_HOME/myhost_sid

network changes made hostname "myhost" to be unresolvable. Instead of "myhost" the hostname resolves to "myhost.mydomain.com" emctl will not find:
ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_myhost.mydomain.com_sid
ORACLE_HOME/myhost.mydomain.com_sid

To resolve the issue database control needs to be recreated using the correct hostname.
Check Note 278100.1 for steps to create db control.

c) Database Control was not configured. Check Note 278100.1 for steps to create db control.

6. Perl errors. Getting the following errors when starting the dbcontrol:

perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LC_ALL = (unset),
LC__FASTMSG = "true",
LANG = "En_US"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C")

Solution

This is a label view environment problem. In the local environment do the following:

Unset the LANG variable.
Stop and restart the database.

7. Start Dbconsole Shows Errors With Wrong Ps Option on Unix AIX, HP, SOLARIS

> emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://:1158/em/console/aboutApplication
ps: unknown output format: -o cmd
ps: illegal option -- -
ps: unknown output format: -o ls
usage: ps [ -aAdeflcjLPy ] [ -o format ] [ -t termlist ]
[ -u userlist ] [ -U userlist ] [ -G grouplist ]
[ -p proclist ] [ -g pgrplist ] [ -s sidlist ]
'format' is one or more of:
user ruser group rgroup uid ruid gid rgid pid ppid pgid sid taskid pri opri pcpu pmem vsz rss osz nice class time etime stime f s c lwp nlwp psr tty addr wchan fname comm args projid project pset
Starting Oracle Enterprise Manager 10g Database Control ..................... started.

Solution

Check Note 358479.1

8. Starting dbcontrol fails. emdb.nohup shows:

----- ::Console Launched with PID 12031 at time  -----
Exception in thread "main" java.util.zip.ZipException: No such file or directory
at java.util.zip.ZipFile.open(Native Method)
at java.util.zip.ZipFile.(ZipFile.java:112)
at java.util.jar.JarFile.(JarFile.java:127)

Solution

Check Note 312652.1

9. Startup dbcontrol is making too many "__JDBC__" entries in LISTENER log:

26-SEP-2005 12:09:00 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta.query))* (ADDRESS=(PROTO =tcp)(HOST=hostname)(PORT=52163)) * establish * beta.query * 0
26-SEP-2005 12:09:00 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=beta.query))* (ADDRESS=(PROTO =tcp)(HOST=hostname)(PORT=52164)) * establish * beta.query * 0

Solution

Check Note 336177.1

10. 'emctl start dbconsole' takes ages to start. Emagent process is actually running, although one is unable to access dbcontrol from the browser. emagent.trc shows:

2007-09-16 10:48:16 Thread-1290 WARN vpxoci: OCI Error -- ErrorCode(6550): ORA-06550: line 1, column 65: PLS-00201: identifier 'DBMS_AQADM' must be declared ORA-06550: line 1, column 65:
PL/SQL: Statement ignored
SQL = "/* OracleOEM */ BEGN :succ_sub := 0; dbms_aqadm.creat"...
LOGIN = dbsnmp/@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521))(CONNECT_DATA=(SID=FSSYS)))

Solution

Check Note 458834.1

11. dbconsole having hostname starting with letter "u" fails. This is because of  DBCONSOLE DOES NOT WORK HAVING A HOSTNAME STARTING WITH "U". Bug is fixed in 10.2.0.2 patchset.

Apply patchset 10.2.0.2 or if not possible:

a) Save the file $ORACLE_HOME\host_SID\sysman\config\emd.properties to emd.properties.orig
b) Update the file $ORACLE_HOME\host_SID\sysman\config\emd.properties, replacing \ with / in the
following line:

For example change:
omsRecvDir=d:\oracle\product\10.2.0\db_1\ukp001_db0\sysman\recv
to
omsRecvDir=d:/oracle/product/10.2.0/db_1/ukp001_db0l/sysman/recv

c) Bounce DB Control

12. SEVERE: Cannot start Database Control. The following ports are already in use: [EM agent port:3938]

a) Check which ports are available for use for this dbcontrol
b) Run the creation of the dbcontrol with using this port numbers:

ORACLE_HOME\bin\emca -config all db -repos recreate -AGENT_PORT  -DBCONTROL_HTTP_PORT  -RMI_PORT  -JMS_PORT 

The default port numbers are:

AGENT_PORT: 3938
DBCONTROL_HTTP_PORT: 5500 or 1158
RMI_PORT: 5520
JMS_PORT: 5540

13. The dbconsole cannot be initialized correctly and the logfile $ORACLE_HOME/_/sysman/log/emoms.trc shows the following error:

ORA-12516, TNS:listener could not find available handler with matching protocol stack
The Connection descriptor used by the client was:
<>_/sysman/config/emoms.properties>

Solution

Check Note 458308.1

14. Generic Time zone issues.
Check Note 338556.1, Note 304585.1, Note 332123.1, Note 461918.1.

Platform specific

AIX5L

1. dbcontrol start fails with:

./emctl start dbconsole
bin/emctl[336]: unlimited: 0403-009 The specified number is not valid for this command.

This happens because AIX 5L O/S itself as documented in AIX Version 4.3 to 5L Migration Guide:
http://www.redbooks.ibm.com/redbooks/pdfs/sg246924.pdf on Page 195.
When the emctl script try to see the value of ulimit. If it's set to unlimited, it throws this exception and continue starting the DBConsole.

Solution

a) You can simply ignore the warning
b) Set the value of the ulimit to a value either than unlimited.

2. unable to star dbcontrol. Main error in emdb.nohup:

+12139 [ Unable to alloc heap of requested size, perhaps the maxdata value is too small - see
README.HTML for more information. ]
+12140 [ **Out of memory, aborting** ]
+12141 [ ]
+12142 [ ]
+12142 [ *** panic: JVMST017: Cannot allocate memory in initializeMarkAndAllocBits(markbits1) ]
+12143 /u01/app/oracle/CPSS/10.2.0/jdk/bin/java[3]: 1253762 IOT/Abort trap(coredump)
+12144 ----- Thu Jul 19 18:27:17 2007::DBConsole exited at Thu Jul 19 18:27:17 2007 with return
value 134. -----
+12145 ----- Thu Jul 19 18:27:17 2007::DBConsole has exited due to an internal error -----
+12146 ----- Thu Jul 19 18:27:17 2007:: - checking for corefile at
/u01/app/oracle/CPSS/10.2.0/abc.xyz.com_sid/sysman/emd -----
+12147 ----- Thu Jul 19 18:27:17 2007::Restarting DBConsole. -----
+12148 ----- Thu Jul 19 18:27:17 2007::Console Launched with PID 1015900 at time Thu Jul 19
18:27:17 2007 -----
+12149 [ Unable to alloc heap of requested size, perhaps the maxdata value is too small - see
README.HTML for more information. ]
+12150 [ **Out of memory, aborting** ]

Solution

Increase ulimit resources for the user starting the dbcontrol.

3. dbcontrol fails to start. emagent.trc shows:

2006-06-14 14:06:01 Thread-1872 ERROR engine: [oracle_database,,health_check] : nmeegd_GetMetricData failed :
2006-06-14 14:06:11 Thread-1562 ERROR pingManager: nmepm_pingReposURL: Error in request response. code = 400. text = 2006-06-14 14:06:16 Thread-1634 ERROR fetchlets: Could not load library '/u01/app/oracle/product/10.2.0/db/lib32/libnmcfhc.so' for reason 'rtld: 0712-001 Symbol main was referenced from module /u01/app/oracle/product/10.2.0/db/lib32/libnmcfhc.so(), but a runtime definition of the symbol was not found.
rtld: 0712-001 Symbol nmeusb_StringBuffer_new was referenced from module /u01/app/oracle/product/10.2.0/db/lib32/libnmcfhc.so(), but a runtime definition of the symbol was not found.

Solution

Check Note 378104.1

4. The Refresh time on the database control home page shows two hours behind the standard time for
Europe/Copenhagen on AIX platform. How to change the time to correct value? (this w/a can be implemented for other timezones regions as long as the corresponding AIX timezone is known).

Check Note 860955.1

Windows Server 2003

1. Starting the dbcontrol fails with:

The OracleDBConsoleCIMISYU service terminated with service-specific error 1 (0x1)
An error occured while trying to initialize the service.

Solution

a) Apply the latest Patch 6012744 - 10.2.0.3.0 Patch6 for Microsoft Windows (x64).
b) All bugs included in Patch 5846378 are also included in Patch 6012744, since these are cumulative patches.

2. dbcontrol fails to start. emagent.trc shows:

2005-08-26 11:53:56 Thread-544 ERROR pingManager: nmepm_pingReposURL: Cannot connect to
http://:5501/em/upload/: retStatus=-1
2005-08-26 11:53:57 Thread-544 WARN http: snmehl_connect: connect failed to (:5501): No connection could be made because the target machine actively refused it.

Solution

This is an installation issue. During the installation there are some files missing.

The files
oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\lib\uix2.jar
oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\lib\ohw.jar
oc4j\j2ee\oc4j_applications\applications\em\em\WEB-INF\lib\share.jar

are missing. In the most cases you needs to create the directory WEB-INF\lib manually.

As a workaround, copy the above 3 files to '...\WEB-INF\lib' and restarted dbconsole. The files are located in $ORACLE_HOME\jlib.
References
Note 266027.1 - Problem: Startup: Emctl Start Dbconsole Fails with Agent port missing in EMD_URL
Note 278100.1 - How To Drop, Create And Recreate DB Control In A 10g Database
Note 343748.1 - Problem: Startup: Error starting Database Control, dbconsole - Unable to determine local host from URL
Note 358961.1 - Problem: Startup OMS: Oms Startup Fails With Integration Class Not Found
Note 368612.1 - Problem: Startup: DB Control Agent Crashes: Gim-00091 OS failure Message: Too Many Open Files
Note 398499.1 - Problem: Startup: EM Database Control Has Stopped Working and Unable to Start Again
Note 403928.1 - How to cycle the DB Control emdb.nohup file in $ORACLE_HOME/host_sid/sysman/log
Note 419586.1 - Problem: Startup: Cannot Start dbconsole and log Shows 'ORMI-Server address is already being used'
Note 438504.1 - EMCA or DB Control (DBConsole) Fails with Error starting ORMI-Server
Note 452284.1 - How to manage DB Console Log and Trace files

четверг, 8 октября 2009 г.

Oracle: Top SQL

Количество сессий на схему:

SELECT schemaname, 
       COUNT(*) sessions  
 FROM V$SESSION 
WHERE schemaname <> 'SYS' 
GROUP BY schemaname 
ORDER BY COUNT(*) DESC;



Resource utilization history

select to_char(BEGIN_INTERVAL_TIME,'dd-mm-yyyy HH24:MI'), 
       to_char(END_INTERVAL_TIME,'dd-mm-yyyy HH24:MI'), 
       ss.SNAP_ID, 
       l.CURRENT_UTILIZATION, 
       l.MAX_UTILIZATION 
  from DBA_HIST_RESOURCE_LIMIT l, 
       DBA_HIST_SNAPSHOT ss
 where ss.SNAP_ID = l.SNAP_ID
   and upper(l.RESOURCE_NAME) like upper('%&resource_name%')
order by BEGIN_INTERVAL_TIME;
/

Top SQL by Buffer Gets

select substr(sql_text,1,500) "SQL",
      (cpu_time/1000000) "CPU_Seconds",
      disk_reads "Disk_Reads",
      buffer_gets "Buffer_Gets",
      executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from v$sql s
order by buffer_gets desc nulls last;

Top SQL by Buffer Gets / Rows Proc

select substr(sql_text,1,500) "SQL",
      round((cpu_time/1000000),3) "CPU_Seconds",
      disk_reads "Disk_Reads",
      buffer_gets "Buffer_Gets",
      executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from v$sql s
order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last;

Top SQL by Disk Reads

select substr(sql_text,1,500) "SQL",
      (cpu_time/1000000) "CPU_Seconds",
      disk_reads "Disk_Reads",
      buffer_gets "Buffer_Gets",
      executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from v$sql s
order by disk_reads desc nulls last;

Top SQL by CPU

select substr(sql_text,1,500) "SQL",
      (cpu_time/1000000) "CPU_Seconds",
      disk_reads "Disk_Reads",
      buffer_gets "Buffer_Gets",
      executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from v$sql s
order by cpu_time desc nulls last;

Top SQL by Executions

select substr(sql_text,1,500) "SQL",
      (cpu_time/1000000) "CPU_Seconds",
      disk_reads "Disk_Reads",
      buffer_gets "Buffer_Gets",
      executions "Executions",
      case when rows_processed = 0 then null
           else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
           end "Buffer_gets/rows_proc",
      round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
      (elapsed_time/1000000) "Elapsed_Seconds",
      module "Module"
 from v$sql s
order by executions desc nulls last;

Top SQL by Waits
select INST_ID,
     (cpu_time/1000000) "CPU_Seconds",
     disk_reads "Disk_Reads",
     buffer_gets "Buffer_Gets",
     executions "Executions",
     case when rows_processed = 0 then null
          else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
          end "Buffer_gets/rows_proc",
     round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
     (elapsed_time/1000000) "Elapsed_Seconds",
     --round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
     substr(sql_text,1,500) "SQL",
     module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
  (select   sql_id
  ,  CASE  WHEN session_state = 'ON CPU' THEN 'CPU'      
           WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
           ELSE 'WAIT' END state           
    from gv$active_session_history            
    where   session_type IN ( 'FOREGROUND')       
    and sample_time  between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
    group by sql_id, state),
     ranked_sqls AS
(select sql_id,  sum(occur) sql_occur  , rank () over (order by sum(occur)desc) xrank
from sql_class          
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
--and rs.xrank <= 15

order by xrank, sql_id, state ))

order by elapsed_time desc nulls last;

select s.parsing_schema_name,
       inst_id,
       sql_id,
       plan_hash_value,
       child_number,
       round(nullif(s.ELAPSED_TIME, 0) / nullif(s.EXECUTIONS, 0) / 1000000, 4) elap_per_exec,
       round(s.USER_IO_WAIT_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) io_wait_pct,
       round(s.CLUSTER_WAIT_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) cluster_wait_pct,
       round(s.application_wait_time / nullif(s.ELAPSED_TIME, 0) * 100, 2) app_wait_pct,
       round(s.CPU_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) cpu_time_pct,
       round(s.PHYSICAL_READ_BYTES / nullif(s.EXECUTIONS, 0) / 1024 / 1024, 2) pio_per_exec_mb,
       round(s.PHYSICAL_READ_BYTES / nullif(s.PHYSICAL_READ_REQUESTS, 0), 2) / 1024 read_per_request_kbytes,
       round(s.buffer_gets /  nullif(s.executions, 0), 4) BufferGets_per_Exec,
       s.executions,
       to_char(s.last_active_time,'dd/mm/yyyy hh24:mi:ss') last_act_time,
       s.first_load_time,
       s.sql_fulltext,             
       s.sql_profile,
       s.sql_patch,
       s.sql_plan_baseline
FROM   gv$sql s
WHERE  1=1
and    s.parsing_schema_name in ('ANGOR')
order  by s.last_active_time desc;

Используем табличную функцию DBMS_SQLTUNE.SELECT_CURSOR_CACHE:

SELECT buffer_gets, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY buffer_gets desc;



SELECT disk_reads, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
ORDER BY disk_reads desc;



SELECT cpu_time / 1000000 as cpu_time_secs, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY cpu_time desc;



SELECT elapsed_time / 1000000 as elapsed_time_secs, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY elapsed_time desc;



SELECT executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY executions desc;



SELECT direct_writes, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 1000'))
ORDER BY direct_writes desc;



SELECT rows_processed, 
       executions, 
       last_exec_start_time, 
       parsing_schema_name, 
       module, 
       action, 
       sql_id, 
       plan_hash_value, 
       sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 10000'))
ORDER BY rows_processed desc;





List out SQL Tuning Set contents to check we got what we wanted

 
SELECT
  first_load_time,
  executions as execs,
  parsing_schema_name,
  elapsed_time  / 1000000 as elapsed_time_secs,
  cpu_time / 1000000 as cpu_time_secs,
  buffer_gets,
  disk_reads,
  direct_writes,
  rows_processed,
  fetches,
  optimizer_cost,
  sql_plan,
  plan_hash_value,
  sql_id,
  sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'SAMPLE_TUNING_SET'));

Database Time and Average Active Sessions

Active Session History (ASH) 

Oracle для выборочных активных сеансов каждые 1 секунду в кольцевом буфере в SGA.


Можно представить ASH как историю DB time. 

Так как ASH связан с активными сеансами, то мы можем извлечь DB time из ASH: 
ASH COUNT (*) = DB time.


select count(*) DB_TIME_SEC
  from  v$active_session_history
where session_type = 'FOREGROUND';

DB_TIME_SEC
-----------   
        993 


Формула для Average Active Sessions:
DB Time / Elapsed Time = Average Active Sessions

Данные для активных сеансов доступны из представления V$ACTIVE_SESSION_HISTORY.
Поскольку данные хранятся в памяти (SGA) в круговом буфере, если у вас очень загруженная система, 
то возможно, что эти данные будут сброшены на диск в таблицу SYS.DBA_HIST_ACTIVE_SESS_HISTORY, 
но в неё попадет только 1 из 10 образцов из V$ACTIVE_SESSION_HISTORY.

Так что DB_TIME из DBA_HIST_ACTIVE_SESS_HISTORY, будет ASH COUNT (*) * 10 = DB TIME.


Average Active Sessions = DB Time/Elapsed Time
Если мы хотим рассчитать Average Active Sessions между 09:05 и 09:30 
т.е. в течение заданного периода в 25 минут (1500 секунд).

DB Time = 3144
Elapsed Time = 25 minutes = (25 * 60) = 1500 seconds
Average Active Sessions would be: 3144 / 1500 = 2.096


select count(*)  DB_TIME,
count(*) / (25 * 60) AAS
from       v$active_session_history
where      session_type = 'FOREGROUND'
and sample_time between to_date('08012015 09:05:00','ddmmyyyy hh24:mi:ss') 
                    and to_date('08012015 09:30:00','ddmmyyyy hh24:mi:ss')
/


   DB_TIME        AAS
---------- ----------
      3144      2.096


Например, мы можем найти TOP активных sql_id, упорядоченных по DB time:


select count(*), sql_id
from   v$active_session_history
where  session_type = 'FOREGROUND'
       and sql_id is not null
group by sql_id 
order by 1 desc
/


  COUNT(*) SQL_ID
---------- -------------
     29274 3t431hgtbs8t7
        11 4ztz048yfq32s
         5 0uuczutvk6jqj
         3 f5yun5dynkskv
         2 4mua4wc69sxyf
         1 2ft0vv13g0xkd
         1 3y5p8203p74hn
         1 934ur8r7tqbjx
         1 459f3z9u4fb3u
         1 72cjd89q7d2s5
         1 65gavqa851ymj

11 rows selected.

To find the session ids that executed sql with high DB time we can use the following.
SQL> select count(*), sql_id, session_id
  2  from   v$active_session_history
  3  where  session_type = 'FOREGROUND'
  4         and sql_id is not null
  5  group by sql_id,session_id 
  6  order by 1 desc
  7  /

  COUNT(*) SQL_ID        SESSION_ID
---------- ------------- ----------
     14637 3t431hgtbs8t7        135
     14637 3t431hgtbs8t7        373
         5 0uuczutvk6jqj        125
         4 4ztz048yfq32s          5
         3 4ztz048yfq32s         19
         3 4ztz048yfq32s          7
         3 f5yun5dynkskv          7
         2 4mua4wc69sxyf        125
         2 65gavqa851ymj          7
         1 72cjd89q7d2s5        125
         1 3y5p8203p74hn        374
         1 934ur8r7tqbjx        125
         1 459f3z9u4fb3u          5
         1 2ft0vv13g0xkd        125
         1 4ztz048yfq32s         14

15 rows selected.

DB time

col name format a10
select statistic#, name, class, value
from v$sysstat
where name = 'DB time'
/


STATISTIC# NAME            CLASS      VALUE
---------- ---------- ---------- ----------
        20 DB time             1   13828791




select to_char(begin_time,'dd.mm.yyy hh24:mi:ss') begin_time,
        to_char(end_time,'dd.mm.yyy hh24:mi:ss') end_time,
        intsize_csec interval_size,
        group_id,
        metric_name,
        value
 from   v$sysmetric
where  metric_name = 'Database Time Per Sec'
/

BEGIN_TIME         END_TIME           INTERVAL_SIZE   GROUP_ID METRIC_NAME                    VALUE
------------------ ------------------ ------------- ---------- ------------------------- ----------
09.01.015 10:25:48 09.01.015 10:26:48          6001          2 Database Time Per Sec     .296650558
09.01.015 10:27:03 09.01.015 10:27:18          1500          3 Database Time Per Sec     .300146667

There are 2 records for two different groups because of short duration and long duration intervals.

System Metrics Long Duration (group_id= 2) : 60 second interval
System Metrics Short Duration (group_id = 3) : 15 second interval

V$SYSMETRIC_SUMMARY (with appropriate DBA_HIST_SYSMETRIC_SUMMARY for on disk snapshots) contains long duration metric 
(1 hour interval) information such as avg,min,max.

SQL> select maxval,minval,average,standard_deviation
  2  from v$sysmetric_summary
  3  where metric_name = 'Database Time Per Sec'
  4  /

    MAXVAL     MINVAL    AVERAGE STANDARD_DEVIATION
---------- ---------- ---------- ------------------
3.12933844          0 .282698591         .595884377

V$SYSMETRIC_HISTORY (with appropriate DBA_HIST_SYSMETRIC_SUMMARY on disk snapshots) contains short duration and long duration metrics.

Примеры использования V$ACTIVE_SESSION_HISTORY:

SELECT *
  FROM V$ACTIVE_SESSION_HISTORY
 WHERE sample_time > sysdate - 1 
   AND session_type <> 'BACKGROUND';

SELECT *
  FROM V$ACTIVE_SESSION_HISTORY
 WHERE sample_time BETWEEN TO_DATE('2018-09-03 00:00:00','YYYY-MM-DD HH24:MI:SS') 
                       AND TO_DATE('2018-09-03 23:59:59','YYYY-MM-DD HH24:MI:SS')
   AND session_type <> 'BACKGROUND';

SELECT sql_id
      ,session_id,session_serial#
      ,COUNT(*)  seconds
 FROM  v$active_session_history
WHERE sample_time  > SYSDATE-1/24/12
AND sql_id  = '2vp4k2kgy2wm4'
GROUP BY sql_id, session_id,session_serial#
ORDER BY seconds  DESC;
/

SELECT sql_id, 
       session_id,
       session_serial#, 
       COUNT(*) seconds
FROM   v$active_session_history
WHERE  sample_time > SYSDATE-1/24/12
AND sql_id = '2vp4k2kgy2wm4'
GROUP BY sql_id, session_id, session_serial#
ORDER BY seconds DESC;

SELECT * FROM DBA_HIST_SQLTEXT HIST_SQLTEXT 
WHERE sql_id IN 
(
 SELECT sql_id
  FROM V$ACTIVE_SESSION_HISTORY
 WHERE sample_time > sysdate - 1 
   AND session_type <> 'BACKGROUND' 
   AND sql_id is not NULL
);

select NVL(sql_id,'NULL') as sql_id
      ,count(*)           as DB_time
      ,ROUND(100*count(*)/SUM(count(*)) OVER (), 2) as Pct_load
  from v$active_session_history 
 where sample_time > sysdate - 5/24/60
   and session_type <> 'BACKGROUND'
 group by sql_id
 order by count(*) desc;
/

select NVL(event,'NULL') as event
       ,count(*)         as samples
       ,ROUND(count(*)/600,4) as AvgActiveSess
  from v$active_session_history
 where sql_id IS NULL
   and sample_time > sysdate - 10/24/60
   and session_type <> 'BACKGROUND'
group by event;

select inst_id as instance
      ,event
      ,COUNT(distinct current_obj#) as objcount
      ,SUM(1) as dbtime_secs
 from gv$active_session_history
where sample_time > sysdate - 1/24/60 -- pick up last minute only
  and wait_class = 'User I/O' 
group by inst_id,event
order by event,instance;

SELECT sql_id, 
  count(*), 
  round(count(*) / sum(count(*)) over(), 2) pctload
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1/ (24 * 60)
  AND session_type <> 'BACKGROUND'
  AND session_state = 'ON CPU'
GROUP BY sql_id
ORDER BY count(*) desc;

SELECT ash.sql_id,
  count(*)
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
WHERE ash.sample_time > sysdate -1/(24*60)
  AND ash.session_state = 'WAITING'
  AND ash.event_id = evt.event_id
  AND evt.wait_class = 'USER I/O'
GROUP BY ash.sql_id
ORDER BY count(*) desc;

SELECT session_id,
  count(*)
  FROM V$ACTIVE_SESSION_HISTORY
WHERE session_state = 'ON CPU'
  AND sample_time > sysdate -1/(24*60)
GROUP BY session_id
ORDER BY count(*) desc;

Current CPU usage by this instance
select round(100*num_cpus_in_use/num_cpus_total,1) cpu_usage_pct
from
(
    select count(*) num_cpus_in_use
    from v$active_session_history
    where sample_time =
        (select max(sample_time)
         from v$active_session_history
        )
    and session_state = 'ON CPU'
),
(select value num_cpus_total
 from v$osstat
 where stat_name = 'NUM_CPUS'
) num_cpus;

Top 5 events for an arbitrary interval

select *
from
(
    select nvl(event, session_state) event,
           round(100*count(*)/
              (sum(count(1)) over ()), 2) "DB time (%)"
    from v$active_session_history
    where sample_time between to_date(:start_time, 'dd-mon-yyyy hh24:mi:ss')
                      and to_date(:end_time, 'dd-mon-yyyy hh24:mi:ss')
    group by nvl(event, session_state)
    order by count(*) desc
)
where rownum<=5;

Database activity as a function of time
select sample_time time,
       count(*) activity
from v$active_session_history
group by sample_time
order by sample_time;

Wait profile for a single SQL statement
select nvl(event, session_state) event,
       round(100*count(*)/
         (sum(count(1)) over ()), 2) "% query time"
from dba_hist_active_sess_history
where sql_id = :sql_id
group by nvl(event, session_state)
order by count(*) desc;

Top SQL for a module

select nvl(ash.sql_id, 'N/A') sql_id,
       replace(nvl(txt.sql_text_short, 'N/A'), chr(10), '\n') sql_text,
       count(*) "DB time(s)",
       round(100*count(*)/
         sum(count(1)) over(), 2) "DB time(%)"
from v$active_session_history ASH,
     (select sql_id,
             dbms_lob.substr(sql_text, :len) sql_text_short
      from v$sql
      union all
      select sql_id,
             dbms_lob.substr(sql_text, :len) sql_text_short
      from dba_hist_sqltext      ) txt
where ash.sql_id = txt.sql_id (+)
and module = :module
group by ash.sql_id, txt.sql_text_short
order by count(*) desc;

Profiling PL/SQL calls in a module

select P.OBJECT_NAME ||
          case when p.object_name is not null
                      and p.procedure_name is not null
               then '.'
          end ||
          P.PROCEDURE_NAME top_plsql_object_name,
          count(*) "DB time (s)"
from v$active_session_history ash,
dba_procedures p
where ASH.PLSQL_ENTRY_OBJECT_ID = P.OBJECT_ID (+)
and ASH.PLSQL_ENTRY_SUBPROGRAM_ID = P.SUBPROGRAM_ID (+)
and module = :module
group by p.object_name, p.procedure_name
order by count(*) desc;

Accessed objects
This query gives the breakdown of DB time within the interval of interest by database objects accessed.
select nvl(o.owner ||
         case when o.object_name is not null
              then '.'
         end                 ||
        o.object_name, 'N/A') object_name,
        round(100*count(*)/
           sum(count(1)) over (), 2) "DB time (%)"
from dba_hist_active_SESS_HISTORY ash,
     dba_objects o
where ash.CURRENT_OBJ# = o.object_id (+)
and ash.sample_time between to_date(:start_period, 'dd-mon-yyyy hh24:mi:ss')
                    and to_date(:end_period, 'dd-mon-yyyy hh24:mi:ss')
group by o.owner || case when o.object_name is not null
                    then '.' end
                 || o.object_name
order by count(*) desc;

Datafile access during last N hours

select nvl(f.file_name, 'not in I/O waits'),
       10*count(*) "DB time (s)",
       round(100*count(*)/
         sum(count(1)) over (), 2) "DB time (%)"
from DBA_HIST_ACTIVE_SESS_HISTORY ash,
     DBA_DATA_FILES f where ash.current_file# = f.file_id (+)
and ash.sample_time > sysdate - :hours/24
group by f.file_name
order by count(*) desc;

Breakdown of query DB time by plan operations

SELECT ash.sql_plan_line_id,
       ash.sql_plan_operation,
       ash.sql_plan_options,
       p.object_name,
       round(100*COUNT(*)/
          sum(count(1)) over(), 2) "% time"
FROM v$active_session_history ash,
        v$sql_plan p
WHERE ash.sql_id = p.sql_id
AND ash.sql_plan_hash_value = p.plan_hash_value
AND ash.sql_plan_line_id = P.id
AND ash.sql_id = :sql_id
AND ash.sql_plan_hash_value = :plan_hash_value
GROUP BY ASH.SQL_PLAN_LINE_ID,
         ASH.SQL_PLAN_OPERATION,
         ASH.SQL_PLAN_OPTIONS,
         p.object_name
ORDER BY count(*) DESC;

Long-running queries at a specific moment in time
select distinct sql_ID,
       24*60*60*(sysdate - sql_exec_start) seconds_running
FROM v$active_session_history
where sample_time =  (select max(sample_time)
                      from v$active_session_history
                      where sample_time < to_date(:time,
                           'dd-mon-yyyy hh24:mi'))
and sql_id is not null
order by 24*60*60*(sysdate - sql_exec_start) desc;

Time model statistics query

This query gives breakdown of DB time by session state, similar to the time model statistics section of the AWR report.
select status,
        round(100*count(*)/
                   sum(count(1)) over(), 2) "% DB time"
from
(
    select case when in_connection_mgmt='Y'
                  then 'IN_CONNECTION_MGMT'
                when in_parse = 'Y'
                  then 'IN_PARSE'
                when in_hard_parse = 'Y'
                  then 'IN_HARD_PARSE'
                when in_sql_execution = 'Y'
                  then 'IN_sql_execution'
                when in_plsql_execution = 'Y'
                  then 'IN_plsql_execution'
                when in_plsql_compilation = 'Y'
                  then 'IN_plsql_compilation'
                when in_plsql_rpc = 'Y'
                  then 'IN_plsql_rpc'
                when in_java_execution = 'Y'
                  then 'IN_JAVA_EXECUTION'
                when in_bind = 'Y'
                  then 'IN_BIND'
                when in_cursor_close = 'Y'
                  then 'IN_CURSOR_CLOSE'
                when in_sequence_load = 'Y'
                  then 'IN_SEQUENCE_LOAD'
                else 'N/A'
           end status
    FROM dba_hist_active_sess_history
)
group by status
order by count(*) desc;

SQL statements consuming most PGA at a specific time

select ash.sql_id,
       replace(nvl(txt.sql_text_short, 'N/A'),
               chr(10), '\n'
               ) sql_text,
       round(ASH.PGA_ALLOCATED/1024/1024) pga_mb
from dba_hist_active_sess_history ash,
     (
        select sql_id, dbms_lob.substr(sql_text, :len) sql_text_short
        from v$sql
        union all
        select sql_id, dbms_lob.substr(sql_text, :len) sql_text_short
        from dba_hist_sqltext
     ) txt
where ash.sql_id = txt.sql_id (+)
and ash.sql_id is not null
and ash.sample_time = (select max(sample_time)
                     from dba_hist_active_sess_history
                     where sample_time < to_date(:time, 'dd-mon-yyyy hh24:mi:ss'))
order by ash.pga_allocated desc;

SELECT ash.sql_id,
  sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
  sum(decode(ash.session_state,'WAITING',1,0)) -
  sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
  sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",
  sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
  FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.sql_id
ORDER BY sum(decode(ash.session_state,'ON CPU',1,1)) desc;


SELECT ash.session_id,   
ash.session_serial#,   
ash.user_id,   
ash.program,   
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",   
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",   
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",   
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL" 
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN 
WHERE en.event# = ash.event# 
and ash.sample_time > sysdate -1/(24*60) 
GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program 
ORDER BY sum(decode(ash.session_state,'ON CPU',1,1));

TOP SQL:

SET LONG 2000;

SELECT ALL_USERS.username AS user_name,
       V$SQL.module,
       V$SQL.sql_text,
       V$SQL.sql_fulltext,
       NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
       ROUND(pct_load, 1) AS pct_load
FROM(
    SELECT user_id,
           sql_id, 
           count(*),
           count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
     FROM V$ACTIVE_SESSION_HISTORY     
    WHERE sample_time > sysdate - 1/24
    GROUP BY user_id, sql_id
    )TOP_SQLS,
     V$SQL,
     ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
  AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
  AND pct_load > 1
  AND username <> 'SYS'
ORDER BY pct_load DESC;

select SQL_ID,round(PGA_MB,1) PGA_MB,
       percent,
       rpad('*',percent*10/100,'*') star
from (select SQL_ID,
             sum(DELTA_PGA_MB) PGA_MB ,
             (ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,
             rank() over(order by sum(DELTA_PGA_MB) desc) rank
      from (select SESSION_ID,
                   SESSION_SERIAL#,
                   sample_id,
                   SQL_ID,
                   SAMPLE_TIME,
                   IS_SQLID_CURRENT,
                   SQL_CHILD_NUMBER,
                   PGA_ALLOCATED,
                   greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
            from  v$active_session_history
            where IS_SQLID_CURRENT='Y'
              and sample_time > sysdate-60/86400
              order by 1,2,3,4
           )
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < 11
order by rank;
/

select SQL_ID,
       TEMP_MB,
       percent,
       rpad('*',percent*10/100,'*') star
from(select SQL_ID,
            sum(DELTA_TEMP_MB) TEMP_MB ,
            (ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
     from(select SESSION_ID,
                 SESSION_SERIAL#,
                 sample_id,
                 SQL_ID,
                 SAMPLE_TIME,
                 IS_SQLID_CURRENT,
                 SQL_CHILD_NUMBER,
                 temp_space_allocated,
                 greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
          from v$active_session_history
         where IS_SQLID_CURRENT='Y'
           and sample_time > sysdate-1
         order by 1,2,3,4
         )
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < 11
order by rank;
/

select ash.sql_id,
       ash.event,
       trunc(sum(TM_DELTA_TIME) / 1000000,2) TM_DELTA_TIME,
       trunc(sum(TM_DELTA_CPU_TIME) / 1000000,2) TM_DELTA_CPU_TIME,
       trunc(sum(TM_DELTA_DB_TIME) / 1000000,2) TM_DELTA_DB_TIME,
       SUM(DELTA_READ_IO_REQUESTS) DELTA_READ_IO_REQUESTS,
       SUM(DELTA_WRITE_IO_REQUESTS),
       trunc(SUM(DELTA_READ_IO_BYTES)/1024/1024,2) DELTA_READ_IO_M,
       trunc(SUM(DELTA_WRITE_IO_BYTES)/1024/1024,2) DELTA_WRITE_IO_M,
       trunc(SUM(DELTA_INTERCONNECT_IO_BYTES)/1024/1024,2) DELTA_INTERCONNECT_IO_M,
       sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", 
       trunc(sum(decode(ash.session_state,'WAITING',1,0)) -
       trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))),2) "WAITING",
       trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)),2) "IO",
       trunc(sum(decode(ash.session_state,'ON CPU',1,1)),2) "TOTAL"
  from v$active_session_history ash,V$EVENT_NAME EN
 where ash.sample_id > 190681
   and ash.sample_id < (190681 + 2527)
   AND ash.event#=en.event#(+)
 group by ash.sql_id, ash.event
 order by 2 desc;

SQL execution history (ASH)
set linesize 230
set pagesize 9999

select cpu_time_delta,
       elapsed_time_delta,
       iowait_delta,
       plan_hash_value, 
       EXECUTIONS_DELTA, 
       round(elapsed_time_delta/EXECUTIONS_DELTA/1000/1000, 4) avg_exec_time_sec,
       (select to_char(end_interval_time, 'dd/mm/yyyy hh24:mi') 
          from dba_hist_snapshot hs 
         where hs.snap_id = a.snap_id) end_date 
from  dba_hist_sqlstat a, 
      dba_hist_sqltext b 
where a.sql_id=b.sql_id and 
 snap_id in (select distinct snap_id from dba_hist_snapshot where end_interval_time>=sysdate-&days_back)
 and a.sql_id = '&sql_id'
 and EXECUTIONS_DELTA != 0
 order by snap_id;

SELECT
  sysmetric_history.sample_time,
  cpu,
  bcpu,
  DECODE(SIGN((cpu+bcpu)-cpu_ora_consumed), -1, 0, ((cpu+bcpu)-cpu_ora_consumed)) AS cpu_ora_wait,
  scheduler,
  uio,
  sio,
  concurrency,
  application,
  COMMIT,
  configuration,
  administrative,
  network,
  queueing,
  clust,
  other
FROM
  (SELECT
     TRUNC(sample_time,'MI') AS sample_time,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',0,1),0))/60 AS cpu,
     SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',1,0),0))/60 AS bcpu,
     SUM(DECODE(wait_class,'Scheduler',1,0))/60 AS scheduler,
     SUM(DECODE(wait_class,'User I/O',1,0))/60 AS uio,
     SUM(DECODE(wait_class,'System I/O',1,0))/60 AS sio,
     SUM(DECODE(wait_class,'Concurrency',1,0))/60 AS concurrency,
     SUM(DECODE(wait_class,'Application',1,0))/60 AS application,
     SUM(DECODE(wait_class,'Commit',1,0))/60 AS COMMIT,
     SUM(DECODE(wait_class,'Configuration',1,0))/60 AS configuration,
     SUM(DECODE(wait_class,'Administrative',1,0))/60 AS administrative,
     SUM(DECODE(wait_class,'Network',1,0))/60 AS network,
     SUM(DECODE(wait_class,'Queueing',1,0))/60 AS queueing,
     SUM(DECODE(wait_class,'Cluster',1,0))/60 AS clust,
     SUM(DECODE(wait_class,'Other',1,0))/60 AS other
   FROM v$active_session_history
   WHERE sample_time>sysdate- INTERVAL '1' HOUR
   AND sample_time<=TRUNC(SYSDATE,'MI')
   GROUP BY TRUNC(sample_time,'MI')) ash,
  (SELECT
     TRUNC(begin_time,'MI') AS sample_time,
     VALUE/100 AS cpu_ora_consumed
   FROM v$sysmetric_history
   WHERE GROUP_ID=2
   AND metric_name='CPU Usage Per Sec') sysmetric_history
WHERE ash.sample_time (+)=sysmetric_history.sample_time
ORDER BY sample_time;

Top 20 resource intensive SQLs for today

 

SELECT * 
FROM 
   (SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"+"Disk Reads"+"Buffer Gets"+"Writes"+"Sorts"+"Parses" DESC) AS "Rank", 
           i1.* 
    FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day", 
                 shs.sql_id "Sql id", 
                 REPLACE(CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)),CHR (10),'') "Sql", 
                 SUM(shs.executions_delta) "Execs",ROUND((SUM(shs.elapsed_time_delta)/1000000)/SUM (shs.executions_delta),1) "Time Ea Sec", 
                 ROUND((SUM(shs.cpu_time_delta)/1000000)/SUM (shs.executions_delta),1) "CPU Ea Sec", 
                 ROUND((SUM(shs.iowait_delta)/1000000)/SUM (shs.executions_delta),1) "IO/Wait Ea Sec", 
                 SUM(shs.cpu_time_delta) "CPU Time", 
                 SUM(shs.disk_reads_delta) "Disk Reads", 
                 SUM(shs.buffer_gets_delta) "Buffer Gets", 
                 SUM(shs.direct_writes_delta) "Writes", 
                 SUM(shs.parse_calls_delta) "Parses", 
                 SUM(shs.sorts_delta) "Sorts", 
                 SUM(shs.elapsed_time_delta) "Elapsed" 
            FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht 
                 ON (sht.sql_id = shs.sql_id) 
                 INNER JOIN dba_hist_snapshot hs 
                 ON (shs.snap_id = hs.snap_id) 
            HAVING SUM (shs.executions_delta) > 0 
            GROUP BY shs.sql_id,TO_CHAR(hs.begin_interval_time,'MM/DD/YY'),CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)) 
            ORDER BY "Snap Day" DESC) i1 
ORDER BY "Snap Day" DESC) 
WHERE "Rank" <= 20 
      AND "Snap Day"=TO_CHAR(SYSDATE-1,'MM/DD/YY');

History of running sessions between 2 dates (dd/mm/yyyy hh24:mi)

 

set linesize 250
set verify off
set pagesize 9999

COLUMN Time      FORMAT a20            HEADING 'Sample time'
COLUMN Username       FORMAT a12            HEADING 'User name'
COLUMN Session_id    FORMAT 99999          HEADING 'Sess ID'
COLUMN sql_id    FORMAT a14            HEADING 'SQL ID'
COLUMN program   FORMAT a33            HEADING 'Program'
COLUMN module    FORMAT a20            HEADING 'Module'
COLUMN event    FORMAT a25            HEADING 'Event name'
COLUMN time_waited   FORMAT 999,999,999    HEADING 'Time waited'
COLUMN owner1  FORMAT a15            HEADING 'Owner 1'
COLUMN object_name FORMAT a15            HEADING 'Object name 1'
COLUMN owner1  FORMAT a15            HEADING 'Owner 2'
COLUMN p2  FORMAT a15            HEADING 'Object name 2'

SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time, 
       u.username, 
       h.blocking_session block_sid, 
       h.session_id, 
       h.sql_id, 
       h.program, 
       h.module, 
       h.event, 
       h.time_waited, 
       o.owner, 
       o.object_name, 
       o1.owner, 
       o1.object_name p2
FROM  DBA_HIST_ACTIVE_SESS_HISTORY h, 
      DBA_OBJECTS o, 
      dba_users u, 
      dba_objects o1
where sample_time between to_date('&begin_date', 'dd/mm/yyyy hh24:mi') and to_date('&end_date', 'dd/mm/yyyy hh24:mi')
  and session_type != 'BACKGROUND'
  and o.object_id (+) = h.CURRENT_OBJ#
  and h.p2 = o1.object_id (+)
  and time_waited > 0
  and u.user_id = h.user_id
  and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time;
/





Sessions history for last NNN minutes

set linesize 250
set verify off
set pagesize 9999

COLUMN Time        FORMAT a24            HEADING 'Sample time'
COLUMN User_id            FORMAT 99999          HEADING 'User ID'
COLUMN Session_id            FORMAT 99999          HEADING 'Sess ID'
COLUMN sql_id     FORMAT a14            HEADING 'SQL ID'
COLUMN program     FORMAT a33            HEADING 'Program'
COLUMN module     FORMAT a33            HEADING 'Module'
COLUMN event      FORMAT a25            HEADING 'Event name'
COLUMN time_waited           FORMAT 999,999,999    HEADING 'Time waited'
COLUMN owner   FORMAT a15            HEADING 'Owner'
COLUMN object_name  FORMAT a35            HEADING 'Object name'

SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time, 
       h.user_id, 
       h.session_id, 
       h.sql_id, 
       h.program, 
       h.module, 
       h.event, 
       h.time_waited, 
       o.owner, 
       o.object_name
FROM  v$ACTIVE_SESSION_HISTORY h, 
      DBA_OBJECTS o
where sample_time between sysdate-1/(24*60/&MINUTES_BACK) and sysdate
  and session_type != 'BACKGROUND'
  and o.object_id (+) = h.CURRENT_OBJ#
  and time_waited > 0
  and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time;
/

Blocking history:

SELECT  distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM  V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi') 
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');

Top 10 SQL_ID's for the last 7 days as identified by ADDM

col SQL_ID form a16
col Benefit form 9999999999999

select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

alter session set nls_date_format='YYYY:MM:DD:HH24:MI:SS';
select M.end_time
      ,ROUND(M.value / 100,3) as Metric_AAS
      ,ROUND(SUM(DECODE(A.session_type,'FOREGROUND',1,0))/((M.end_time - M.begin_time) * 86400 ),3) as ASH_AAS
      ,COUNT(1)   as ASH_count
  from v$active_session_history  A
      ,v$sysmetric_history       M
 where A.sample_time between M.begin_time and M.end_time
   and M.metric_name = 'Database Time Per Sec'  -- 10g metric
   and M.group_id = 2
 group by M.end_time,M.begin_time, M.value
 order by M.end_time;
/

Какие объекты ожидались дольше вмего за последние 15 минут

select o.owner, o.object_name, o.object_type, a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, dba_objects o
where a.sample_time between sysdate-30/2880 and sysdate
and a.current_obj# = o.object_id
group by o.owner, o.object_name, o.object_type, a.event
order by total_wait_time desc;

Какие события ожидания экземпляра были в топе за последние 15 минут
select a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a
where a.sample_time between sysdate-30/2880 and sysdate
group by a.event
order by total_wait_time desc;

Какие пользователи дольше всего ждали за последние 15 мин

select s.sid, s.username,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, v$session s
where a.sample_time between sysdate-30/2880 and sysdate
and a.session_id = s.sid
group by s.sid, s.username
order by total_wait_time desc;

Какие SQL- запросы выполнялись дольше всего за последние 15 мин

select a.user_id, d.username, s.sql_text,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, v$sqlarea s, dba_users d
where a.sample_time between sysdate-30/2880 and sysdate
and a.sql_id = s.sql_id
and a.user_id = d.user_id
group by a.user_id, s.sql_text, d.username
order by total_wait_time desc;

В EM GRID CONTROL

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

select ... count(*)   

where sample_time between ...

group by sample_time

group by wait_class

group by sql_id

group by session_id, username

Можно написать такой скрипт:

ashtop.sql


SELECT
&1
, COUNT(*)  "TotalSeconds"
, SUM(CASE WHEN wait_class IS NULL           THEN 1 ELSE 0 END) "CPU"
, SUM(CASE WHEN wait_class ='User I/O'       THEN 1 ELSE 0 END) "User I/O"
, SUM(CASE WHEN wait_class ='Application'    THEN 1 ELSE 0 END) "Application"
, SUM(CASE WHEN wait_class ='Concurrency'    THEN 1 ELSE 0 END) "Concurrency"
, SUM(CASE WHEN wait_class ='Commit'         THEN 1 ELSE 0 END) "Commit"
...  ...
, SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, SUM(CASE WHEN wait_class ='Other'  THEN 1 ELSE 0 END) "Other"
FROM v$active_session_history a , dba_users u
WHERE a.user_id = u.user_id(+)
AND &2
AND sample_time BETWEEN &3 AND &4
GROUP BY &1 
ORDER BY "TotalSeconds" DESC, &1


SQL> @ashtop    

SQL> @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate

SQL> @ashtop session_state,event sql_id='c13sma6rkr27c' sysdate-1/24 sysdate

SQL> @ashtop event,blocking_session_status,blocking_inst_id,blocking_session,blocking_session_serial# "wait_class in ('Concurrency','Cluster')"  sysdate-1/24  sysdate

SQL> @ashtop sql_id  "session_id=201  and session_serial#=2045"  sysdate-1/24 sysdate

SQL> @ashtop sql_plan_hash_value  "session_id=201  and session_serial#=2045"  sysdate-1/24 sysdate

SQL> @ash/time_modelusername  "session_id=201  and session_serial#=2045"  sysdate-1/24 sysdate

SQL> @ash/time_model_phases sql_id  "session_id=201 and session_serial#=2045" sysdate-1/24/60  sysdate

SQL> @ash/time_model_phases sql_id,session_state,event "session_id=201 and session_serial#=2045" sysdate-1/24/60 sysdate

SQL> @ashtop username,program "event='log file sync'" sysdate-1/24 sysdate

SQL> @ashtop event,blocking_session_status,blocking_session,blocking_session_serial# wait_class='Application' sysdate-1/24 sysdate

SQL> @ashtop event,blocking_session_status,blocking_session wait_class='Concurrency' sysdate-1/24 sysdate

SQL> @ashtop p1text,p1,p2text,p2,p3text,p3 "event='buffer  busy waits'" sysdate-1/24 sysdate

SQL> @ashtop p1text,p1,p2text,TO_NUMBER(TRIM(SUBSTR(TO_CHAR(p2,'0XXXXXXXXXXXXXXX'),1,8)),'XXXXXXXXXXXXXXXX') "event='library cache: mutex X'" sysdate-1/24 sysdate

TOP_LEVEL_SQL_ID


select  top_level_Sql_id, module, action, sum(10) ash_Secs
from dba_hist_Active_Sess_history h
where sql_id != top_level_sql_id
group by top_level_sql_id, module, action
order by ash_Secs desc; 
/


with x as (
select sql_id, sum(10) ash_Secs
from dba_hist_active_sess_history
where top_level_sql_id = 'b6usrg82hwsa3'
group by sql_id
)
select x.* 
, (select sql_text from dba_hist_sqltext where sql_id = x.sql_id and rownum = 1) sql_text
from x order by ash_Secs desc;

Количество процессоров и памяти на сервере:

SET SQLFORMAT ANSICONSOLE
SET PAGESIZE 10000

SELECT
  STAT_NAME,
  DECODE(STAT_NAME,'PHYSICAL_MEMORY_BYTES',(ROUND(VALUE/1024/1024/1024,2))
  || ' GB','FREE_MEMORY_BYTES',(ROUND(VALUE            /1024/1024/1024,2))
  || ' GB',VALUE ) VALUE
FROM
  v$osstat
WHERE
  stat_name IN ( 'FREE_MEMORY_BYTES', 'LOAD', 'NUM_CPUS', 'NUM_CPU_CORES',
  'NUM_CPU_SOCKETS', 'PHYSICAL_MEMORY_BYTES' );

STAT_NAME               VALUE

--------------------------------- 
NUM_CPUS                8         
NUM_CPU_CORES           4         
NUM_CPU_SOCKETS         1         
PHYSICAL_MEMORY_BYTES   24 GB     
FREE_MEMORY_BYTES       16.8 GB

Использование cpu на данный момент:

 
select  sum(sm.cpu) "CPU", 
        to_char(sm.session_id) "SID", 
        to_char(sm.session_serial_num) "SERIAL", 
        p.spid "OS_PID", s.username "DB_User",
        s.osuser "OS_User", 
        s.machine "Machine", 
        substr(s.module,1,20) "Module"
   from v$sessmetric sm, v$session s, v$process p
  where begin_time>sysdate-0.0035 and
        s.paddr = p.addr(+) 
    and sm.session_id = s.sid
  group by sm.session_id, 
        sm.session_serial_num, 
        p.spid, 
        s.username, 
        s.osuser, 
        s.machine, 
        s.module
 order by cpu desc;



статистически

 

select st.value "DB_CPU_value", 
       to_char(s.sid) "Sid", 
       to_char(s.serial#) "Serial", 
       p.spid "OS_PID", 
       s.username "DB_User",
       s.osuser "OS_User", 
       s.machine "Machine", 
       s.module "Module", 
       s.logon_time
  from v$sess_time_model st, 
       v$session s, v$process p
 where stat_name = 'DB CPU' and st.sid = s.sid 
   and s.paddr = p.addr(+) order by value desc;

вторник, 8 декабря 2009 г.

Установка пакетов:

ALERT: Setting RemoveIPC=yes on Redhat 7.2 Crashes ASM and Database Instances

Смотрим размеры физической памяти и Swap

Установка параметров ядра

Общий объём доступной разделяемой памяти (в страницах)

Максимальный размер сегмента разделяемой памяти (в байтах)

Максимальное число сегментов разделяемой памяти для всей системы

Рекомендуемые значения других параметров ядра следующие:

Сетевые параметры:

Настройка Shell Limits:

Настройка /dev/shm

Требование к размеру файловых систем

Установить точное время

Настройка разрешения имен

Installing the cvuqdisk Package for Linux (RAC only)

Disabling Transparent HugePages

Настройка Hugepages

Проверьте настройку лимитов

Отключите AMM

Установите рассчитанное значение параметру vm.nr_hugepages

Примечание:

среда, 2 декабря 2009 г.

среда, 11 ноября 2009 г.

вторник, 10 ноября 2009 г.

Пример установки параметров памяти:

Примечание для OS Solaris:

суббота, 7 ноября 2009 г.

вторник, 3 ноября 2009 г.

пятница, 9 октября 2009 г.

Known issues

References

четверг, 8 октября 2009 г.

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне