Сервер БД Oracle

[Andy_VRN]

Доброе время суток всем!
Возникла следующая проблема. Есть сервер на платформе Intel SC5300. Плата SE7520BD2, OS SLES 9 SP 3, 2xIntel® Xeon® Processor 3 GHz, LSI 53C1030 Dual Channel U320 SCSI, 4x2GB Mem. Система установленна на винте SATA. В 10 RAID-е 4 винта SCSI на которых собственно и лежит база Oracle. Порядка 3 лет всё это хозяйство работало без нареканий, до недавнего времени. В последнее время повысилось количество запросов к базе, вырос объем (порядка 70Гб), и количество пользователей (порядка 40 стали появлятся затыки. Сервер на какое то время вообще перестает откликатся. Вопрос в том, что никак не могу понять в каком месте затык происходит и чего нам не хватает. Бюджет не очень большой, не хотелось бы покупать то чего не особо нужно.
Есть подозрение на производительность RAID-а. Вот что выдает iostat

Код: Выделить всё

srv-ora:/ # iostat -x 1 10
Linux 2.6.5-7.244-smp (srv-ora) 	11/20/09 	_x86_64_	(4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          41.05    0.03    3.83    6.45    0.00   48.64

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda              11.02    14.45    8.25    3.94   184.35   147.23    27.21     0.85   69.51   5.04   6.14
sdb               0.25    19.46  103.24    6.56  3988.29   208.19    38.22     0.84    7.68   3.06  33.59

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          66.58    0.00    2.26   19.60    0.00   11.56

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00     0.00  307.00    1.00  4888.00     8.00    15.90     2.82    9.19   3.23  99.40

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          85.79    0.25    4.24    3.99    0.00    5.74

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    61.00    0.00    3.00     0.00   512.00   170.67     0.04   12.67  12.33   3.70
sdb               0.00     8.00  157.00    4.00  2440.00    96.00    15.75     1.07    6.69   4.99  80.40

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          97.00    0.50    2.50    0.00    0.00    0.00

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               3.00     0.00    1.00    0.00    32.00     0.00    32.00     0.01   13.00  13.00   1.30
sdb               0.00     9.00   94.00    5.00  1496.00   112.00    16.24     0.47    4.71   4.08  40.40

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          94.28    0.50    4.23    0.50    0.00    0.50

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda              19.00     0.00   13.00    0.00   256.00     0.00    19.69     0.09    6.92   6.92   9.00
sdb               0.00     1.00  292.00    1.00  4656.00    16.00    15.95     2.23    7.58   3.35  98.30

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          94.00    0.00    5.75    0.25    0.00    0.00

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    1.00    0.00     8.00     0.00     8.00     0.01   11.00  11.00   1.10
sdb               7.00     3.00 1523.00    2.00 25512.00    40.00    16.76     9.25    6.07   0.64  97.90

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          91.77    0.75    4.49    2.74    0.00    0.25

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               0.00    11.00  242.00    6.00  3856.00   136.00    16.10     1.21    4.88   3.80  94.30

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          79.50    0.25    3.00   16.75    0.00    0.50

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    71.00    0.00    3.00     0.00   592.00   197.33     0.08    1.67  25.33   7.60
sdb               0.00     1.00  244.00    1.00  3904.00    16.00    16.00     1.30    5.34   3.89  95.20

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          64.32    0.00    2.51   20.10    0.00   13.07

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda              14.00    15.00    7.00   16.00   168.00   248.00    18.09     0.14    9.52   6.22  14.30
sdb               0.00    18.00  282.00   13.00  5200.00   248.00    18.47     1.97    6.68   3.33  98.30

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          58.35    0.00    3.24   20.70    0.00   17.71

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sdb               1.00     9.00  556.00    3.00  9232.00    96.00    16.69     8.66   15.48   1.53  85.60

Если это так то встает вопрос о том, что при увеличение производительности RAID-а не упремся ли мы в процессор или память?
Буду признателен за любые мысли и советы. Спасибо

[and3008]

Есть подозрение, что надо тюнить Оракл или саму СУБД. Там много чего можно по-тюнить.
Банальный iostat мало что скажет вразумительного по части проблем с СУБД.

Пока можно констатировать, что да, iowait просаживается. Но отчего это происходит - загадка. Например анализ используемых запросов может дать больше пользы, чем просто заменить сервер "на по-больше".

[hitower]

В 10 RAID-е 4 винта SCSI на которых собственно и лежит база Oracle. Порядка 3 лет всё это хозяйство работало без нареканий, до недавнего времени. В последнее время повысилось количество запросов к базе, вырос объем (порядка 70Гб), и количество пользователей (порядка 40 стали появлятся затыки. Сервер на какое то время вообще перестает откликатся. Вопрос в том, что никак не могу понять в каком месте затык происходит и чего нам не хватает. Бюджет не очень большой, не хотелось бы покупать то чего не особо нужно.

Версия Оракла какая?
В любом случае, гораздо продуктивнее смотреть на производительность оракла его же средствами (statspack, awr), чем утилитами из ОС.

[Andy_VRN]

Версия Оракла какая?
В любом случае, гораздо продуктивнее смотреть на производительность оракла его же средствами (statspack, awr), чем утилитами из ОС.

Oracle 10.2.0.2.0
Что смотреть в этих утилитах не подскажите? К сожалению опыта с Oracle считай нету

[Andy_VRN]

Есть подозрение, что надо тюнить Оракл или саму СУБД. Там много чего можно по-тюнить.
Банальный iostat мало что скажет вразумительного по части проблем с СУБД.

Пока можно констатировать, что да, iowait просаживается. Но отчего это происходит - загадка. Например анализ используемых запросов может дать больше пользы, чем просто заменить сервер "на по-больше".

Вопрос скорее стоит так: реально ли на данном железе добиться стабильной и приемлемой по скорости работы базы такого объема и с таким количеством пользователей? Если в принципе да это одно, если нет то чего наращивать в первую очередь.

[hitower]

Ответил в почте

[and3008]

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

В первом приближении у вас наверняка все уперлось в шпиндели. 4 диска в RAID-10 это начальный уровень. Я бы число дисков увеличил. Но только после доп.анализа где тормоза в самой СУБД и нельзя ли это устранить средствами самой СУБД.

По ОЗУ и процессорным ресурсам вряд ли вы достигли своего предела.

Советую либо разбираться с Ораклом, либо найти хорошего знатока по месту. Желательно за бабки.

[ArtKuznetsov]

Приветствую всех! Мы с Andy_VRN в одной команде.

Прикладываю отчет statspack, сделан в один из пиковых часов
Вот некоторые части:

Код: Выделить всё

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:   99.99
            Buffer  Hit   %:  101.32    In-memory Sort %:  100.00
            Library Hit   %:   99.92        Soft Parse %:   99.81
         Execute to Parse %:   78.98         Latch Hit %:   99.58
Parse CPU to Parse Elapsd %:    6.01     % Non-Parse CPU:   99.59

 Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   91.82   91.04
    % SQL with executions>1:   70.35   72.42
  % Memory for SQL w/exec>1:   72.15   72.91

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time                                         12,596          22.0
db file sequential read             888,006       8,557     10   14.9   User I/O
latch: row cache objects             11,756       5,044    429    8.8 Concurrenc
latch: cache buffers chains          16,461       4,065    247    7.1 Concurrenc
cursor: pin S wait on X             134,136       2,044     15    3.6 Concurrenc
          -------------------------------------------------------------

Time Model Statistics                    DB/Inst: CAM/cam  Snaps: 33874-33875
Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                             54,571.2         95.1
DB CPU                                               12,596.3         22.0
parse time elapsed                                    2,497.4          4.4
hard parse elapsed time                               1,761.4          3.1
inbound PL/SQL rpc elapsed time                       1,707.5          3.0
hard parse (sharing criteria) elapsed time              922.0          1.6
hard parse (bind mismatch) elapsed time                 874.7          1.5
Java execution elapsed time                             839.6          1.5
PL/SQL execution elapsed time                           449.8           .8
connection management call elapsed time                  93.1           .2
PL/SQL compilation elapsed time                          37.6           .1
sequence load elapsed time                                9.2           .0
failed parse elapsed time                                 0.6           .0
repeated bind elapsed time                                0.6           .0
DB time                                              57,385.9          N/A
background elapsed time                                 867.1          N/A
background cpu time                                      14.7          N/A
          -------------------------------------------------------------

[Black-Dragon]

Andy_VRN
Почти полностью согласен с and3008.
Добавлю только, что даже дисковая может не быть реальным тормозом. У меня на данный момент база примерно такая же по размеру, пользователей - около 70-ти, конфиг почти такой же (1x E5420\ 8GB RAM\ RAID10 4x SAS, на новые сервера пока не перешел), и ничего, тормозов нет. Но это потому, что отчеты делаются на втором сервере!
В общем, и такое железо вполне может потянуть такую базу, лишь бы отчетами не задушить.

Правильно ли я понял, что ОС Linux 64-х битный и сама СУБД 64-х битная?
Кто, когда и сколь часто генерирует отчеты?

[ArtKuznetsov]

Black-Dragon,

Да, Linux x86_64 и Oracle 64-bit. Отчеты генерируются каждый час AWR'ом, но не думаю, чтобы они так нагружали базу... Или вы про другие отчеты?

[hitower]

Приветствую всех! Мы с Andy_VRN в одной команде.

Прикладываю отчет statspack, сделан в один из пиковых часов

Что можно сказать, чтение притормаживает

Код: Выделить всё

                                                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
db file sequential read             888,006     .0       8,557      10      14.2

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

Код: Выделить всё

 Elapsed      CPU                  Elap per  % Total
  Time (s)   Time (s)  Executions   Exec (s)  DB Time    SQL Id
---------- ---------- ------------ ---------- ------- -------------
     5,645      1,063          457       12.4     9.8 34vwgqpx2z0kn
select * from ( select row_.*, rownum rownum_ from ( select searchobje0_.ID as I
D35_, searchobje0_.ТЕКСТ as ТЕКСТ35_, searchobje0_.ПУБЛИКАЦИЯ_ID as ПУБЛИКАЦИЯ4_
35_, searchobje0_.ОБЪЯВЛЕНИЕ_FAPIA_ID as ОБЪЯВЛЕНИЕ5_35_, searchobje0_.ИНТ_ИНФОР
МАЦИОННАЯ_ЧАСТЬ_ID as ИНТ6_35_, searchobje0_.ОБЪЯВЛЕНИЕ_ID as ОБЪЯВЛЕНИЕ7_35_, s

     5,629        617          248       22.7     9.8 51jr1nrwd3cqc
select searchobje0_.ТИП_ИНФОРМАЦИИ as col_0_0_, count(searchobje0_.ID) as col_1_
0_ from INFORM.ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ searchobje0_ where (searchobje0_.ТИП_И
НФОРМАЦИИ='2' or (searchobje0_.СОСТАВ_РУБРИК_ID in (:1 , :2 , :3 , :4 , :5 , :6
, :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20

     5,256      1,105          905        5.8     9.2 cbtsf3wq94b9a
select searchobje0_.ТИП_ИНФОРМАЦИИ as col_0_0_, count(searchobje0_.ID) as col_1_
0_ from INFORM.ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ searchobje0_ where ((searchobje0_.ВЫПУ
СК_ID in (:1)) and searchobje0_.ПОСЛЕДНЯЯ_В_НОМЕРЕ=:2 or ((searchobje0_.ВЫПУСК_I
D in (:3)) and (searchobje0_.ТИП_ID in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11

     4,197      1,285          401       10.5     7.3 1unpdh0wxua0b
select distinct telecast0_.id as id5_, telecast0_.НАИМЕНОВАНИЕ as НАИМЕНОВ2_5_,
telecast0_.СУБКАНАЛ_ID as СУБКАНАЛ3_5_, telecast0_.ВРЕМЯ_НАЧАЛА as ВРЕМЯ4_5_, te
lecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ as ВРЕМЯ5_5_, telecast0_.КАНАЛ_ID as КАНАЛ6_5_, t
elecast0_.ТИП_ПРОГРАММЫ_ID as ТИП7_5_, telecast0_.ОЦЕНКА_ID as ОЦЕНКА8_5_, telec

     2,881        706          311        9.3     5.0 5u2djd3fkyw4j
select * from ( select row_.*, rownum rownum_ from ( select searchobje0_.ID as I
D35_, searchobje0_.ТЕКСТ as ТЕКСТ35_, searchobje0_.ПУБЛИКАЦИЯ_ID as ПУБЛИКАЦИЯ4_
35_, searchobje0_.ОБЪЯВЛЕНИЕ_FAPIA_ID as ОБЪЯВЛЕНИЕ5_35_, searchobje0_.ИНТ_ИНФОР
МАЦИОННАЯ_ЧАСТЬ_ID as ИНТ6_35_, searchobje0_.ОБЪЯВЛЕНИЕ_ID as ОБЪЯВЛЕНИЕ7_35_, s

     2,718        760          235       11.6     4.7 1s0w17qqajan6
select distinct telecast0_.id as id5_, telecast0_.НАИМЕНОВАНИЕ as НАИМЕНОВ2_5_,
telecast0_.СУБКАНАЛ_ID as СУБКАНАЛ3_5_, telecast0_.ВРЕМЯ_НАЧАЛА as ВРЕМЯ4_5_, te
lecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ as ВРЕМЯ5_5_, telecast0_.КАНАЛ_ID as КАНАЛ6_5_, t
elecast0_.ТИП_ПРОГРАММЫ_ID as ТИП7_5_, telecast0_.ОЦЕНКА_ID as ОЦЕНКА8_5_, telec

     2,005         22           10      200.5     3.5 2cxhpu9x0wg69
select * from ( select row_.*, rownum rownum_ from ( select searchobje0_.ID as I
D35_, searchobje0_.ТЕКСТ as ТЕКСТ35_, searchobje0_.ПУБЛИКАЦИЯ_ID as ПУБЛИКАЦИЯ4_
35_, searchobje0_.ОБЪЯВЛЕНИЕ_FAPIA_ID as ОБЪЯВЛЕНИЕ5_35_, searchobje0_.ИНТ_ИНФОР
МАЦИОННАЯ_ЧАСТЬ_ID as ИНТ6_35_, searchobje0_.ОБЪЯВЛЕНИЕ_ID as ОБЪЯВЛЕНИЕ7_35_, s

     1,980        568          176       11.2     3.4 8srqaq1s4juvb
select distinct telecast0_.id as id5_, telecast0_.НАИМЕНОВАНИЕ as НАИМЕНОВ2_5_,
telecast0_.СУБКАНАЛ_ID as СУБКАНАЛ3_5_, telecast0_.ВРЕМЯ_НАЧАЛА as ВРЕМЯ4_5_, te
lecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ as ВРЕМЯ5_5_, telecast0_.КАНАЛ_ID as КАНАЛ6_5_, t
elecast0_.ТИП_ПРОГРАММЫ_ID as ТИП7_5_, telecast0_.ОЦЕНКА_ID as ОЦЕНКА8_5_, telec

[ArtKuznetsov]

hitower,

Первый:

Код: Выделить всё

SELECT   *
  FROM   (SELECT   row_.*, ROWNUM rownum_
            FROM   (  SELECT   searchobje0_.ID AS ID35_,
                               searchobje0_.ТЕКСТ AS ТЕКСТ35_,
                               searchobje0_.ПУБЛИКАЦИЯ_ID AS ПУБЛИКАЦИЯ4_35_,
                               searchobje0_.ОБЪЯВЛЕНИЕ_FAPIA_ID
                                  AS ОБЪЯВЛЕНИЕ5_35_,
                               searchobje0_.ИНТ_ИНФОРМАЦИОННАЯ_ЧАСТЬ_ID
                                  AS ИНТ6_35_,
                               searchobje0_.ОБЪЯВЛЕНИЕ_ID AS ОБЪЯВЛЕНИЕ7_35_,
                               searchobje0_.ВЫПУСК_ID AS ВЫПУСК8_35_,
                               searchobje0_.ДАТА_ОТГРУЗКИ AS ДАТА9_35_,
                               searchobje0_.НОМЕНКЛАТУРА_ID AS НОМЕНКЛ10_35_,
                               searchobje0_.ВЫДЕЛЕННОЕ AS ВЫДЕЛЕННОЕ35_,
                               searchobje0_.ПЕРЕВОД AS ПЕРЕВОД35_,
                               searchobje0_.ТЕКСТ_СС AS ТЕКСТ13_35_,
                               searchobje0_.ДЕЯТЕЛЬНОСТЬ_ID AS ДЕЯТЕЛЬ14_35_,
                               searchobje0_.СОСТАВ_РУБРИК_ID AS СОСТАВ15_35_,
                               searchobje0_.ТИП_ID AS ТИП16_35_,
                               searchobje0_.ПОСЛЕДНЯЯ AS ПОСЛЕДНЯЯ35_,
                               searchobje0_.ПОСЛЕДНЯЯ_В_НОМЕРЕ
                                  AS ПОСЛЕДНЯЯ18_35_,
                               searchobje0_.ВОЗМОЖНО_НОВОЕ AS ВОЗМОЖНО19_35_,
                               TRIM(   searchobje0_.ВЫДЕЛЕННОЕ
                                    || ' '
                                    || searchobje0_.ТЕКСТ
                                    || ' '
                                    || searchobje0_.ТЕКСТ_СС
                                    || ' '
                                    || searchobje0_.ПЕРЕВОД)
                                  AS formula5_,
                               searchobje0_.ТИП_ИНФОРМАЦИИ AS ТИП2_35_
                        FROM   INFORM.ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ searchobje0_
                       WHERE   (searchobje0_.СОСТАВ_РУБРИК_ID IN
                                      (:1,
                                       :2,
                                       :3,
                                       ...
                                       :33,
                                       :34))
                               AND (searchobje0_.СОСТАВ_РУБРИК_ID IN (:35))
                               AND (searchobje0_.ВЫПУСК_ID IN
                                          (:36, :37, :38, :39, :40, :41))
                               AND searchobje0_.ПОСЛЕДНЯЯ = :42
                    ORDER BY   TRIM(   searchobje0_.ВЫДЕЛЕННОЕ
                                    || ' '
                                    || searchobje0_.ТЕКСТ
                                    || ' '
                                    || searchobje0_.ТЕКСТ_СС
                                    || ' '
                                    || searchobje0_.ПЕРЕВОД) ASC) row_
           WHERE   ROWNUM <= :43)
 WHERE   rownum_ > :44


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.04          0          0          0           0
Fetch        1      0.05       0.05          0       5092          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.07       0.10          0       5092          0          10

Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 71  

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  VIEW  (cr=5092 pr=0 pw=0 time=56720 us)
    340   COUNT STOPKEY (cr=5092 pr=0 pw=0 time=58070 us)
    340    VIEW  (cr=5092 pr=0 pw=0 time=57726 us)
    340     SORT ORDER BY STOPKEY (cr=5092 pr=0 pw=0 time=57042 us)
    586      FILTER  (cr=5092 pr=0 pw=0 time=8077 us)
    586       TABLE ACCESS BY INDEX ROWID ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ (cr=5092 pr=0 pw=0 time=6900 us)
   6275        INDEX RANGE SCAN IND_SOSTINT1 (cr=21 pr=0 pw=0 time=12692 us)(object id 58645)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
  SQL*Net more data to client                     2        0.00          0.00
********************************************************************************

Второй:
(Таблица "ПЕРЕДАЧИ" находится в KEEP пуле)

Код: Выделить всё

  SELECT   DISTINCT
           telecast0_.id AS id5_,
           telecast0_.НАИМЕНОВАНИЕ AS НАИМЕНОВ2_5_,
           telecast0_.СУБКАНАЛ_ID AS СУБКАНАЛ3_5_,
           telecast0_.ВРЕМЯ_НАЧАЛА AS ВРЕМЯ4_5_,
           telecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ AS ВРЕМЯ5_5_,
           telecast0_.КАНАЛ_ID AS КАНАЛ6_5_,
           telecast0_.ТИП_ПРОГРАММЫ_ID AS ТИП7_5_,
           telecast0_.ОЦЕНКА_ID AS ОЦЕНКА8_5_,
           telecast0_.ПЕРЕДАЧА_АНОНСА_ID AS ПЕРЕДАЧА9_5_,
           telecast0_.ПЕРЕДАЧА_ID AS ПЕРЕДАЧА10_5_,
           (Get_full_program (NVL (telecast0_.ПЕРЕДАЧА_ID, telecast0_.id)))
              AS formula1_,
           (TRUNC (telecast0_.ВРЕМЯ_НАЧАЛА)) AS formula2_
    FROM   Передачи telecast0_
   WHERE   (telecast0_.СУБКАНАЛ_ID IS NULL
            OR telecast0_.СУБКАНАЛ_ID IN (SELECT   subchannel1_.id
                                            FROM   субканалы subchannel1_
                                           WHERE   subchannel1_.ГОРОД_ID = :1))
           AND (telecast0_.КАНАЛ_ID IN
                      (:2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13))
           AND telecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ >= :14
           AND telecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ < :15
ORDER BY   telecast0_.КАНАЛ_ID ASC, telecast0_.ВРЕМЯ_НАЧАЛА_РЕАЛЬНОЕ ASC


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       11      2.57       2.53          0      63134          0         102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      2.57       2.53          0      63134          0         102

Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: 614  

Rows     Row Source Operation
-------  ---------------------------------------------------
    102  SORT UNIQUE (cr=63134 pr=0 pw=0 time=2533482 us)
    102   FILTER  (cr=61630 pr=0 pw=0 time=2461682 us)
    114    TABLE ACCESS FULL ПЕРЕДАЧИ (cr=61624 pr=0 pw=0 time=2461558 us)
      0    TABLE ACCESS BY INDEX ROWID СУБКАНАЛЫ (cr=6 pr=0 pw=0 time=61 us)
      3     INDEX UNIQUE SCAN СУБКАНАЛ_PK (cr=3 pr=0 pw=0 time=29 us)(object id 48602)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      12        0.00          0.00
  SQL*Net message from client                    12        0.00          0.01
********************************************************************************

И третий:

Код: Выделить всё

/* Formatted on 24.11.2009 11:39:14 (QP5 v5.114.809.3010) */
  SELECT   searchobje0_.ТИП_ИНФОРМАЦИИ AS col_0_0_,
           COUNT (searchobje0_.ID) AS col_1_0_
    FROM   INFORM.ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ searchobje0_
   WHERE   (searchobje0_.ТИП_ИНФОРМАЦИИ = '2'
            OR (searchobje0_.СОСТАВ_РУБРИК_ID IN
                      (:1,
                       :2,
                       :3,
                       ...
                       :817,
                       :818,
                       :819))
              AND ( (searchobje0_.ВЫПУСК_ID IN (:820))
                   AND searchobje0_.ПОСЛЕДНЯЯ_В_НОМЕРЕ = :821
                   OR (searchobje0_.ВЫПУСК_ID IN (:822))
                     AND searchobje0_.ПОСЛЕДНЯЯ_В_НОМЕРЕ = :823))
           AND contains (searchobje0_.ТЕКСТ, :824) > 0
GROUP BY   searchobje0_.ТИП_ИНФОРМАЦИИ


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.30       0.31          0         42          0           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.32       0.33          0         42          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: 71  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  HASH GROUP BY (cr=336 pr=0 pw=0 time=56237 us)
      0   TABLE ACCESS BY INDEX ROWID ИНТ_ПУБЛИКУЕМАЯ_ИНФОРМАЦИЯ (cr=336 pr=0 pw=0 time=56064 us)
      0    DOMAIN INDEX  ИНТ_ПУБЛ_ИНФ_CTXSYS (cr=336 pr=0 pw=0 time=56055 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net more data from client                   3        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.02
********************************************************************************

[hitower]

Думается, планы далеко не лучшие, особенно второй запрос.

по первому - можно попробовать сделать индекс по
TRIM( searchobje0_.ВЫДЕЛЕННОЕ || ' ' || searchobje0_.ТЕКСТ || ' ' || searchobje0_.ТЕКСТ_СС
|| ' ' || searchobje0_.ПЕРЕВОД)
и хинт first_rows

и зачем вот это в конфиге:
optimizer_mode RULE?

что, cost based оптимизатор совсем плох?

[ArtKuznetsov]

hitower,
Так сложилось, что наши приложения были заточены под rule based оптимизацию, и перевести это дело на cost based быстро не получится к сожалению

В планах имеется новый более мощный сервер, но хотелось бы узнать мнение уважаемых спецов, соизмеримы ли вообще размер и нагрузка нашей БД с имеющимся оборудованием. В среднем у нас ~400 сессий, из них 80-90% неактивных. Основная нагрузка приходится на обработку запросов с веб-сайтов, все эти тяжелые запросы как раз оттуда. Около 100 пользователей работают в офисе, но запросы от них редко нагружают базу. Можно ли подтюнить саму БД для оптимальной производительности?

Было подозрение на Buffer Cache - очень много логических чтений, причем в основном на несколько таблиц. Отсюда и latch: cache buffer chains waits. Также есть идея вынести датафайл с индексами на отдельный диск. Что скажете по этому поводу?

[hitower]

hitower,
Так сложилось, что наши приложения были заточены под rule based оптимизацию, и перевести это дело на cost based быстро не получится к сожалению

Надеюсь, вы знаете, что rule based optimizer в 10м оракле уже не поддерживается?
Что мешает на тестовой системе включить CBO и сымитировать нагрузку со стороны веба?

В планах имеется новый более мощный сервер, но хотелось бы узнать мнение уважаемых спецов, соизмеримы ли вообще размер и нагрузка нашей БД с имеющимся оборудованием. В среднем у нас ~400 сессий, из них 80-90% неактивных. Основная нагрузка приходится на обработку запросов с веб-сайтов, все эти тяжелые запросы как раз оттуда. Около 100 пользователей работают в офисе, но запросы от них редко нагружают базу. Можно ли подтюнить саму БД для оптимальной производительности?

включить CBO для начала
Дисковую не мешало бы пошустрее (дисков SAS 15k 10-12 для начала)
Под веб я бы попробовал закэшировать результаты запросов в сервере приложений.

Было подозрение на Buffer Cache - очень много логических чтений, причем в основном на несколько таблиц. Отсюда и latch: cache buffer chains waits. Также есть идея вынести датафайл с индексами на отдельный диск. Что скажете по этому поводу?

Похоже, это из-за фулл сканов таблиц, которые в keep pool