Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
Всем привет!

Немного не могу вникнуть в результаты статистики по запросу.

Имеется две таблицы T и I у обоих структура полей одинакова и выглядит следующим образом:

id_dt_field_1...field_10


необходимо в таблице T проапдейтить те записи, которые имеют значения полей id_,dt_ аналогичные некоторой строке из I, но сами эти строки не совпадают, проапдейтить всмысле изменить эти строчки, так чтобы они были как из I. В таблице T 100k строк, при апдейте изменяются около 2k.

Итак сначала я делаю наивный апдейт и собираю статистику при помощи автотрассировки

   Statistics
-----------------------------------------------------------
               3  user calls
              35  physical read total multi block requests
        17195008  physical read total bytes
        17195008  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               1  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
   Statistics
-----------------------------------------------------------
               1  user calls
               0  physical read total multi block requests
               0  physical read total bytes
               0  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
commited.
   Statistics
-----------------------------------------------------------
               2  user calls
               0  physical read total multi block requests
               0  physical read total bytes
               0  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
Autotrace Disabled


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

   Statistics
-----------------------------------------------------------
               3  user calls
              39  physical read total multi block requests
        17702912  physical read total bytes
        17702912  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               1  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
   Statistics
-----------------------------------------------------------
               1  user calls
               0  physical read total multi block requests
               0  physical read total bytes
               0  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
commited.
   Statistics
-----------------------------------------------------------
               2  user calls
               0  physical read total multi block requests
               0  physical read total bytes
               0  cell physical IO interconnect bytes
               0  commit cleanout failures: block lost
               0  IMU commits
               0  IMU Flushes
               0  IMU contention
               0  IMU bind flushes
               0  IMU mbu flush
Autotrace Disabled


в будущем я обязательно разберусь за что отвечает каждая из этих строчек, но сейчас я бы хотел просто узнать по факту (вынь да полож короче), можно ли из сравнения этих двух статистик сделать вывод, что второй запрос выполнялся в разы быстрее? ибо интуитивно хочется видеть во второй статистике "циферки поменьше" чем в первой "хотя бы на порядочек". Вобщем всех кто может ответить на вопрос прошу это сделать :)

Заранее спасибо.
9 сен 12, 14:05    [13135995]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Для начала, интересно как получен этот autotrace, в котором собственно нет самой полезной информации - логических чтений. При условии, что autotrace выглядит так: Tuning SQL*Plus
       0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        706  bytes sent via SQL*Net to client
        496  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
Также надо отталкиваться от текста запросов и их планов. Не факт, что индексы нужны. Возможно, надо было воспользоваться merge или updatable view.
9 сен 12, 14:31    [13136065]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

SET AUTOTRACE ON STATISTIC


если писать TRACEONLY, то получаю ошибку типа "lol.. not supported".

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

из вашего комментария я могу заключить, что необходима статистика иного рода нежели эта, так?
9 сен 12, 14:38    [13136090]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

у вас покруче структура статистики и выглядит понятнее.
9 сен 12, 14:40    [13136099]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ivanmipt
wurdu,

SET AUTOTRACE ON STATISTIC


если писать TRACEONLY, то получаю ошибку типа "lol.. not supported".

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

из вашего комментария я могу заключить, что необходима статистика иного рода нежели эта, так?
Странно
SQL>SQL> SET AUTOTRACE ON STATISTIC
SQL> select count(1) from tst;

  COUNT(1)
----------
      9991


Statistics
----------------------------------------------------------
         62  recursive calls
          0  db block gets
         66  consistent gets
          6  physical reads
          0  redo size
        423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
Ты точно в SQL Plus делаешь?
9 сен 12, 14:42    [13136110]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Вообще, на данном этапе, я бы советовал начать с хинта gather_plan_statistics и плана через dbms_xplan. dbms_xplan in 10g
9 сен 12, 14:46    [13136122]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

хм, я делаю это в SQL Developer наверное дело в этом.
9 сен 12, 14:47    [13136124]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

сейчас попробую.
9 сен 12, 14:48    [13136130]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ivanmipt
wurdu,

хм, я делаю это в SQL Developer наверное дело в этом.
Делай в SQL Plus и смотри на consistent gets.
9 сен 12, 14:50    [13136138]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

чем меньше тем быстрее??
9 сен 12, 14:53    [13136148]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ivanmipt
wurdu,

чем меньше тем быстрее??
Не всегда. Чем меньше времени выполняется запрос, тем быстрее :) Но кол-во логических чтений - очень важная статистика и производительность в очень многих случаях зависит от них. По крайней мере твой запрос, я думаю, точно. Но важнее всего - план. И давай, делай вариант с merge.
9 сен 12, 15:00    [13136168]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

сразу так по ходу дела вопрос пока пирожки еще не остыли. dbms_sqltune поддерживается в express edition?
9 сен 12, 15:06    [13136186]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ivanmipt
wurdu,

сразу так по ходу дела вопрос пока пирожки еще не остыли. dbms_sqltune поддерживается в express edition?
Не знаю. Никогда не работал с express.
9 сен 12, 15:10    [13136206]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
Спасибо за содержательные комментарии.
9 сен 12, 15:14    [13136222]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
Error starting at line 1 in command:
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext   CLOB;

BEGIN
my_sqltext :=
   'UPDATE T SET field_1=(SELECT DISTINCT field_1 FROM I WHERE (T.id_=I.id_) AND (T.dt_ = I.dt_) AND (T.field_1 != I.field_1))'
|| 'WHERE exists (SELECT 1 FROM I WHERE (T.id_ = I.id_) AND (T.dt_ = I.dt_) AND (T.field_1 != I.field_1))'
;

my_task_name :=
DBMS_SQLTUNE.CREATE_TUNING_TASK (
  sql_text    => my_sqltext
, user_name   => 'LABS'
, task_name   => 'my_sql_tuning_task'
);
END;
Error report:
ORA-13717: Tuning Package License is needed for using this feature.
ORA-06512: at "SYS.PRVT_SMGUTIL", line 52
ORA-06512: at "SYS.PRVT_SMGUTIL", line 37
ORA-06512: at "SYS.DBMS_MANAGEMENT_PACKS", line 26
ORA-06512: at "SYS.DBMS_SQLTUNE", line 625
ORA-06512: at line 11


>.<
9 сен 12, 16:01    [13136383]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
Вобщем я погулял сюда и сюда и еще кое-куда но вообще говоря остались маленькие вопросики. Действительно если использовать индекс, количество consistent gets уменьшается в разы (417540 против 5989), ну и логично связать это с тем, что мы используя такую чудесную структуру данных как сбалансированное дерево просто не делаем undo для ненужных нам полей, а без индекса приходится делать undo для каждого блока чтобы понять он ли тот единственный и неповторимый, которого наш запрос искал всю свою тридцатисекундную жизнь? Я правильно понимаю? И значит ли это что индексы могут распространять свои ограничения (немного некорректно написал, но главное суть понятна) лишь на те блоки что в буфере (хотя это логично, но лучше уточнить)??
9 сен 12, 19:21    [13136937]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
ivanmipt
Вобщем я погулял сюда и сюда и еще кое-куда но вообще говоря остались маленькие вопросики. Действительно если использовать индекс, количество consistent gets уменьшается в разы (417540 против 5989), ну и логично связать это с тем, что мы используя такую чудесную структуру данных как сбалансированное дерево просто не делаем undo для ненужных нам полей, а без индекса приходится делать undo для каждого блока чтобы понять он ли тот единственный и неповторимый, которого наш запрос искал всю свою тридцатисекундную жизнь? Я правильно понимаю? И значит ли это что индексы могут распространять свои ограничения (немного некорректно написал, но главное суть понятна) лишь на те блоки что в буфере (хотя это логично, но лучше уточнить)??
Не, в твоем случае undo вообще не при чем. Говорю же, покажи планы, а лучше планы с gather_plan_statistics. Сразу станет понятней. И, еще раз, не факт что индекс нужен.
10 сен 12, 00:21    [13137716]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

просто дело тут не конкретно в запросе, а именно в индексе, я хочу понять, как он оптимизирует запрос и понять это из статистики, а не из плана, в результате некоторых переговоров мы выяснили, что дело в consistent gets, и побегав по форуму я попытался осознать как оптимизация непосредственно завязана на на этих самых gets, в одной теме (там ссылка есть в предыдущем посте) вроде более менее написано, но выходит либо я понял не так, либо неверно написали. Вобщем мне бы хотелось понять оптимизацию на уровне именно consistent gets :)

хотя с планами тоже хочется разобраться, сейчас скину их.
10 сен 12, 00:31    [13137737]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
до индексов:

Execution Plan
----------------------------------------------------------
Plan hash value: 2882673032

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |      | 31248 |  1830K|  7531K  (2)| 25:06:20 |
|   1 |  UPDATE               | T    |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|      | 31248 |  1830K|   826   (1)| 00:00:10 |
|   3 |    TABLE ACCESS FULL  | I    | 28846 |   450K|   239   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL  | T    |   117K|  5058K|   585   (1)| 00:00:08 |
|   5 |   SORT UNIQUE         |      |     1 |    16 |   240   (1)| 00:00:03 |
|*  6 |    TABLE ACCESS FULL  | I    |     1 |    16 |   239   (1)| 00:00:03 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."ID_"="I"."ID_" AND "T"."DT_"="I"."DT_")
       filter("T"."FIELD_1"<>"I"."FIELD_1")
   6 - filter("I"."ID_"=:B1 AND "I"."DT_"=:B2 AND "I"."FIELD_1"<>:B3)

Note
-----
   - dynamic sampling used for this statement (level=2)


я так понимаю что в любом случае TABLE ACCESS FULL это плохо, после создания индекса по полю id_ один из TAF меняется на
INDEX RANGE SCAN, тем не менее я немного не понимаю кусок с HASH JOIN RIGHT SEMI (совсем не понимаю всмысле)

Execution Plan
----------------------------------------------------------
Plan hash value: 90912050

------------------------------------------------------------------------------------------

| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT              |          | 31520 |  1846K|   189K (34)|00:38:00 |

|   1 |  UPDATE                       | T        |       |       |            |         |

|*  2 |   HASH JOIN RIGHT SEMI        |          | 31520 |  1846K|   826   (1)|00:00:10 |

|   3 |    TABLE ACCESS FULL          | I        | 28846 |   450K|   239   (1)|00:00:03 |

|   4 |    TABLE ACCESS FULL          | T        |   117K|  5056K|   585   (1)|00:00:08 |

|   5 |   SORT UNIQUE                 |          |     1 |    16 |     5  (20)|00:00:01 |

|*  6 |    TABLE ACCESS BY INDEX ROWID| I        |     1 |    16 |     4   (0)|00:00:01 |

|*  7 |     INDEX RANGE SCAN          | INDX_I_1 |     2 |       |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."ID_"="I"."ID_" AND "T"."DT_"="I"."DT_")
       filter("T"."FIELD_1"<>"I"."FIELD_1")
   6 - filter("I"."DT_"=:B1 AND "I"."FIELD_1"<>:B2)
   7 - access("I"."ID_"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)
10 сен 12, 00:40    [13137762]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
кстати с gather plan statistic у меня тоже небольшие траблы ибо сервер требует прав доступа к динамическим представлениям v$lol (всмысле функция display_cursor требует) , а на попытку их предоставить от имени dba вылетает ошибка ORA-02030, но это уже отдельная песня :) (которую я пою в недалеко лежащей теме семигодичной давности).
10 сен 12, 01:03    [13137788]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Для начала, выставляй linesize в sql pllus, чтобы строки не "ехали". Затем почитай статью про gather_plan_statistics, тогда ты сможешь посмотреть сколько логических чтений относится к каждому шагу плана, а также там будут важные статистики по executions. Также почитай про сбор статистики, а то судя по dynamic sampling у тебя ее нет. Судя по плану, могу предположить что запрос у тебя типа update t set ... = (select ... from i ) where ... in (select ... from i where ...). Т.е. в начале выполняется соединение t и i для условия in, затем на каждую строчку соединения вызывается в медленном случае TABLE ACCESS FULL, в быстром INDEX RANGE SCAN. Смысл индекса как раз в том, чтобы уменьшать логические чтения. Почитай все-таки теорию. Как работает TABLE ACCESS FULL, сколько блоков при этом читается и как работает индексный доступ. Если перепишешь через merge, то, возможно, не надо будет n раз вызывать INDEX RANGE SCAN, что будет еще быстрее (но, возможно, эффектив
10 сен 12, 01:06    [13137792]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

ммм, а продолжение?
10 сен 12, 01:08    [13137795]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
(но, возможно, эффективнее будет через nested loop все-таки вызывать, надо проверять ).
10 сен 12, 01:10    [13137800]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
ivanmipt
Member

Откуда: МФТИ
Сообщений: 101
wurdu,

вот апдейт мой

UPDATE T SET field_1=(SELECT DISTINCT field_1 FROM I WHERE (T.id_=I.id_) AND (T.dt_ = I.dt_) AND (T.field_1 != I.field_1))
WHERE exists (SELECT 1 FROM I WHERE (T.id_ = I.id_) AND (T.dt_ = I.dt_) AND (T.field_1 != I.field_1));


считай угадал, ну на опыте затащил что еще сказать :)
10 сен 12, 01:11    [13137802]     Ответить | Цитировать Сообщить модератору
 Re: statistic  [new]
Откушу руку
Guest
wurdu...

Дайте только палец.
10 сен 12, 01:12    [13137803]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить