Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
 		Есть функция ldr.add_to_collection, которая выполняется в цикле от 40 до 60 тыс раз. Запуск цикла циклический - раз в полчаса

Промежуток времени 1:
SQL ordered by Executions
Total Executions: 307,993 
Captured SQL account for 61.4% of Total 
Executions  Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s)  SQL Id SQL Module SQL Text 
56,443 56,443 1.00 0.00 0.00 0ha3h1gy3703v ********* (TNS V1-V3)  begin :result := ldr.add_to_collection(:f1,:f2,f3); end;
.......
Промежуток времени 2: строки с этим стэйтментом нет и в помине. Хотя заведомо известно, что запуск был - есть лог, где указывается начало отработки цикла, и его конец. Есть результат отработки этого цикла: слитая на диск коллекция (явно те же 40 до 60 тыс элементов) - это я к тому, что 100%но этот стейтмент выполнялся в этот промежуток времени - и очень много раз. Но тем не менее его в топе по Executions его нет. Почему?

Смоделировать ситуацию еще раз не могу, так как не понимаю что происходит. Даже пробовал расширить промежуток 2 - на случай если где то прогнал и цикл в него не попал, но нету..

P.S. 
select last_active_time from v$sql where sql_text like '%add_to_collection%'
тоже показывает, что add_to_collection выполнялась
20 авг 10, 14:37    [9298759]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
 		?
23 авг 10, 07:19    [9306361]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 14517
 		Есть определенное количество операторов, которые записываются при SNAPSHOT-е
См. DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)
topnsql
If NUMBER: Top N SQL size. The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. The setting will have a minimum value of 30 and a maximum value of 100000000. Specifying NULL will keep the current setting.

If VARCHAR2: Users are allowed to specify the following values: (DEFAULT, MAXIMUM, N), where N is the number of Top SQL to flush for each SQL criteria. Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL. Specifying MAXIMUM will cause the system to capture the complete set of SQL in the cursor cache. Specifying the number N is equivalent to setting the Top N SQL with the NUMBER type. Specifying NULL for this argument will keep the current setting.
23 авг 10, 07:50    [9306388]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
 		Вячеслав Любомудров,

Видимо я не достаточно точно объяснил... Проблема в том, что этот statement заведомо будет в топ30. А вернее даже он будет на вершине топа - ибо других так часто выполняющихся statement-ов просто нету
23 авг 10, 08:06    [9306423]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 14517
 
select * from (select sql_text from v$sql order by executions desc) where rownum <= 30
Ну и посмотри, будет он там?

И еще, если ты считаешь, что, например в цикле
for r in (select ...) loop
запрос выполняется на каждую строку, то ты ошибаешься. Более того, по умолчанию с 10g даже fetch в этом случае не выполняется на каждую строку (в отличии от более ранних версий), выполняется BULK COLLECT по 100 строк сразу
23 авг 10, 08:13    [9306439]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 14517
 		Ну и, как вариант, динамическое составление запроса без использования биндов, в результате чего это тысячи разных операторов
23 авг 10, 08:15    [9306446]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
Вячеслав Любомудров
select * from (select sql_text from v$sql order by executions desc) where rownum <= 30
Ну и посмотри, будет он там?

Да конечно - там он сверху и есть

Вячеслав Любомудров

И еще, если ты считаешь, что, например в цикле
for r in (select ...) loop
запрос выполняется на каждую строку, то ты ошибаешься. Более того, по умолчанию с 10g даже fetch в этом случае не выполняется на каждую строку (в отличии от более ранних версий), выполняется BULK COLLECT по 100 строк сразу

Это я в курсе. Но данная вещь дергается хоть и в цикле, но с клиента: это перловый скрипт, который в цикле заполняет через PL/SQL-ное API коллекцию. Которая в конце сливается на диск. Альтернатива построчным инсертам...

Вячеслав Любомудров
Ну и, как вариант, динамическое составление запроса без использования биндов, в результате чего это тысячи разных операторов

Запрос не динамический. Бинды используются (в первоначальном посте я специально это и указал: :f1,:f2,f3)
23 авг 10, 08:24    [9306466]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
 		То есть вопрос состоит не в том, как уменьшить кол-во запусков этого statement или soft parse связанных с ним, а в том - почему его нет в отчете: хотя точно известно, что он выполнялся и кол-во executions заведомо превышало кол-во executions любого statement, которые в топе таки показываются
23 авг 10, 08:31    [9306480]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 14517
 		В DBA_HIST_SQLSTAT за тот промежуток есть этот запрос?
23 авг 10, 08:36    [9306487]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
Вячеслав Любомудров
В DBA_HIST_SQLSTAT за тот промежуток есть этот запрос?

Поискал в принципе этот запрос в DBA_HIST_SQLSTAT. Просмотрел 10 часовой промежуток (примерно +-5 часов от исследуемого времени) - этот запрос есть только в двух снимках из 60 (забыл сказать - промежуток снимков установлен не дефолтовый 1 час, а минимальный возможный - 10 минут). В обоих этих снимках запрос с большим запасом на 1м месте в топе по executions. Цикл, запускающий кучу таких запросов, выполняется (как я уже говорил с интервалом в полчаса) - то есть за 10 часов должно было быть не менее 20 снимков таких. А их всего 2
23 авг 10, 08:54    [9306538]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 3752
 		Возможно в момент создания снимка запрос был вытеснен из shared pool.
23 авг 10, 09:35    [9306644]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
wurdu
Возможно в момент создания снимка запрос был вытеснен из shared pool.


Как бы не похоже:

select first_load_time, last_load_time, last_active_time, invalidations, executions  
from v$sql where sql_text like '%ldr.add_to_collectionn%' and sql_id='0ha3h1gy3703v'

FIRST_LOAD_TIME	LAST_LOAD_TIME	LAST_ACTIVE_TIME	INVALIDATIONS	EXECUTIONS
2010-08-05/17:04:46	2010-08-13/11:34:38	23.08.2010 14:05	1	47493832
Да и что он каждые полчаса вытесняется? С чего бы? База не сильно нагружена. Пул большой
23 авг 10, 11:13    [9307224]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
 		Собственно для чего это все нужно.. Я и до этого замечал, что AWR например в Parse Calls какие то запросы показывает, другие аналогичные (в основном именно массовые вставки с биндами) нет. Хотя ясно, что Soft Parse должен быть в обоих случаях. Вопрос: нету в отчетах - потому что по каким то причинам мне непонятным не происходит парсинга, или все таки какие то вещи в AWR не попадают (напр при маленьком размере снимка??? в 10 минут). Насколько можно доверять AWR-у вообще??

Executions выбрал для примера - потому что exeсute то явно не может не происходить
23 авг 10, 12:36    [9307984]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 3752
stil
Хотя ясно, что Soft Parse должен быть в обоих случаях
...
Вопрос: нету в отчетах - потому что по каким то причинам мне непонятным не происходит парсинга, или все таки какие то вещи в AWR не попадают
Не видя кода трудно сказать почему это ясно. Нормально ведь парсить один раз и выполнять запрос сколько надо, избегая как soft, так и hard парса. Если разработчик явно не заботится об этом, ему на помощь может прийти pl/sql cursor cache и session_cached_cursors.
23 авг 10, 13:10    [9308229]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
stil
Member

Откуда: Кемерово
Сообщений: 1082
wurdu
Не видя кода трудно сказать почему это ясно. Нормально ведь парсить один раз и выполнять запрос сколько надо, избегая как soft, так и hard парса. Если разработчик явно не заботится об этом, ему на помощь может прийти pl/sql cursor cache и session_cached_cursors.


CREATE OR REPLACE PACKAGE DEVDB.pkg AS

type тип_таблица is table of таблица%rowtype not null;
var_таблица тип_таблица:= тип_таблица(); -- Создание и инициализация коллекции.

PROCEDURE add_to_coll(p_поле_таблицы IN таблица.поле_таблицы%type );
PROCEDURE fill_table;
END pkg;

CREATE OR REPLACE PACKAGE BODY DEVDB.pkg_1C AS
PROCEDURE add_to_coll(p_поле_таблицы IN таблица.поле_таблицы%type )
is
begin
    var_таблица.extend;
    var_таблица(var_таблица.last).поле_таблицы   := p_поле_таблицы;    
end;

procedure fill_table 
is
begin
  forall a in var_таблица.first..var_таблица.last
    insert into таблица
    values var_таблица(a);
  var_таблица.trim(var_таблица.count);  
end;
END pkg;
/
Далее где то с клиента (разных клиентов - написанных возможно даже на разных языках и вообще не знающих о batch -вставках)

for i in (1..много_много)
   begin 
      DEVDB.PKG.add_to_coll(:p_поле_таблицы); 
   end;

fill_table;
Необходимо это чтоб избежать много_много построчных insert (или merge)..

Соответственно в общем случае может быть и не одно поле - а вставляться строка с несколькими полями. Под каждый случай сделано свое "API"

Вопрос: почему для некоторых случаев в AWR - в секции parse call есть много_много 
begin PKG.add_to_coll(:p_поле_таблицы); end;

а в некоторых нет - просто ли они есть, но не попали в отчет.. Или по каким то причинам в некоторых случаях их просто нет
23 авг 10, 13:59    [9308575]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 3752
 		Раз клиенты разные и языки разные, то и код они могут генерировать разный.
К примеру, если цикл for i in (1..много_много) будет в анонимном PL/SQL блоке, то, естественно, курсоров begin PKG.add_to_coll(:p_поле_таблицы); end; мы в v$sql и соответственно в AWR не увидим. Я бы предложил потрассировать клиентов и посмотреть на цифры парсинга и курсоры.
23 авг 10, 14:59    [9309196]     Ответить | Цитировать    Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 3752
 		Подниму тему, т.к. сам не понимаю, по какому принципу AWR формирует top запросов. У меня также, как у автора, есть запросы, которые выполняются относительно часто, несколько миллионов раз в час и являются явными кандидатами в top по Executions (они же являются кандидатами и по Gets, что еще важнее). Я вижу их в top в v$sqlstats, но в dba_hist_sqlstat они не попадают. Один из таких запросов я добавил через DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL и он сразу стал в AWR №1 по Executions и в top по Gets. Я всегда думал, что это баг в документации "The number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count)" и в этом top не хватает Buffer Gets, Physical Reads, Executions, но чего-то я начинаю сомневаться.
16 янв 12, 09:53    [11908829]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 457
 		Данные для статистики берутся напрямую из x$kewrsqlidtab, x$kglcursor_child_sqlidph:
+
insert into wrh$_sqlstat
    (snap_id
    ,dbid
    ,instance_number
    ,sql_id
    ,plan_hash_value
    ,optimizer_cost
    ,optimizer_mode
    ,optimizer_env_hash_value
    ,sharable_mem
    ,loaded_versions
    ,version_count
    ,module
    ,action
    ,sql_profile
    ,force_matching_signature
    ,parsing_schema_id
    ,parsing_schema_name
    ,parsing_user_id
    ,fetches_total
    ,fetches_delta
    ,end_of_fetch_count_total
    ,end_of_fetch_count_delta
    ,sorts_total
    ,sorts_delta
    ,executions_total
    ,executions_delta
    ,px_servers_execs_total
    ,px_servers_execs_delta
    ,loads_total
    ,loads_delta
    ,invalidations_total
    ,invalidations_delta
    ,parse_calls_total
    ,parse_calls_delta
    ,disk_reads_total
    ,disk_reads_delta
    ,buffer_gets_total
    ,buffer_gets_delta
    ,rows_processed_total
    ,rows_processed_delta
    ,cpu_time_total
    ,cpu_time_delta
    ,elapsed_time_total
    ,elapsed_time_delta
    ,iowait_total
    ,iowait_delta
    ,clwait_total
    ,clwait_delta
    ,apwait_total
    ,apwait_delta
    ,ccwait_total
    ,ccwait_delta
    ,direct_writes_total
    ,direct_writes_delta
    ,plsexec_time_total
    ,plsexec_time_delta
    ,javexec_time_total
    ,javexec_time_delta
    ,io_offload_elig_bytes_total
    ,io_offload_elig_bytes_delta
    ,io_interconnect_bytes_total
    ,io_interconnect_bytes_delta
    ,physical_read_requests_total
    ,physical_read_requests_delta
    ,physical_read_bytes_total
    ,physical_read_bytes_delta
    ,physical_write_requests_total
    ,physical_write_requests_delta
    ,physical_write_bytes_total
    ,physical_write_bytes_delta
    ,optimized_physical_reads_total
    ,optimized_physical_reads_delta
    ,cell_uncompressed_bytes_total
    ,cell_uncompressed_bytes_delta
    ,io_offload_return_bytes_total
    ,io_offload_return_bytes_delta
    ,bind_data
    ,flag)
    select /*+ ordered use_nl(sql) index(sql kglobt03)            param('_module_action_old_length',0) */
     :snap_id
    ,:dbid
    ,:instance_number
    ,kglobt03
    ,kglobt30
    ,kglobtn0
    ,decode(kglobt32, 0, 'NONE', 1, 'ALL_ROWS', 2, 'FIRST_ROWS', 3, 'RULE', 4, 'CHOOSE', 'UNKNOWN')
    ,kglobcceh
    ,kglobhs0 + kglobhs1 + kglobhs2 + kglobhs3 + kglobhs4 + kglobhs5 + kglobhs6
    ,kglobclc
    ,kglobccc
    ,kglobts0
    ,kglobts1
    ,kglobts3
    ,kglobt49
    ,kglobt18
    ,kglobts4
    ,kglobt17
    ,kglobt04
    ,kglobdft
    ,kglobt35
    ,kglobdef
    ,kglobt01
    ,kglobdso
    ,kglobt05
    ,kglobdex
    ,kglobt48
    ,kglobdpx
    ,kglhdldc
    ,kglobdld
    ,kglhdivc
    ,kglobdiv
    ,kglobt12
    ,kglobdps
    ,kglobt13
    ,kglobddr
    ,kglobt14
    ,kglobdbf
    ,kglobt15
    ,kglobdro
    ,kglobt06
    ,kglobdcp
    ,kglobt07
    ,kglobdel
    ,kglobwui
    ,kglobdui
    ,kglobwcl
    ,kglobdcl
    ,kglobwap
    ,kglobdap
    ,kglobwcc
    ,kglobdcc
    ,kglobwdw
    ,kglobddw
    ,kglobt42
    ,kglobdpl
    ,kglobt43
    ,kglobdjv
    ,kglobt52
    ,kglobdco
    ,kglobt53
    ,kglobdci
    ,kglobt54
    ,kglobdrr
    ,kglobt55
    ,kglobdrb
    ,kglobt56
    ,kglobdwr
    ,kglobt57
    ,kglobdwb
    ,kglobt58
    ,kglobdor
    ,kglobt59
    ,kglobdcu
    ,kglobt53 - kglobt55 - kglobt57 + kglobt52
    ,kglobdci - kglobdrb - kglobdwb + kglobdco
    ,(case
         when vp.value in ('off', 'memory') then
          null
         else
          kglobcbca
     end)
    ,null
      from x$kewrsqlidtab sie
          ,x$kglcursor_child_sqlidph sql
          ,(select value from v$parameter where name = 'cursor_bind_capture_destination') vp
     where sie.sqlid_kewrsie = sql.kglobt03

Можно попробовать тем же запросом дергать данные самостоятельно и сравнивать с данными из v$sql (все таки retention у v$sqlstats больше).
16 янв 12, 12:20    [11909841]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 14517
 		STATISTICS_LEVEL не может влиять?
17 янв 12, 02:24    [11915158]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 3752
Timur Akhmadeev
Данные для статистики берутся напрямую из x$kewrsqlidtab, x$kglcursor_child_sqlidph:
Можно попробовать тем же запросом дергать данные самостоятельно и сравнивать с данными из v$sql (все таки retention у v$sqlstats больше).
Этим запросом не получится, т.к. в x$kewrsqlidtab пусто.
Вячеслав Любомудров
STATISTICS_LEVEL не может влиять?
Он влияет по крайней мере в части:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
topnsql
...
Specifying DEFAULT will revert the system back to the default behavior of Top 30 for statistics level TYPICAL and Top 100 for statistics level ALL.
Я попытался смоделировать на 11.2.0.3 заполнение wrh$_sqlstat вручную на базе v$sqlstats и максимально точное соответствие достигается через выбор top 30 по Elapsed Time, CPU Time, Parse Calls, Shareable Memory, Version Count. Top по Buffer Gets, Physical Reads, Executions берутся уже из этой выборки и поэтому не соответствуют действительности. Возможно есть какие-то исключения. Пока я думаю что алгоритм такой.
17 янв 12, 08:11    [11915297]     Ответить | Цитировать    Сообщить модератору
 Re: Отчеты AWR "SQL ordered by Executions" могут показывать не всю информацию?  [new]
Timur Akhmadeev
Member

Откуда:
Сообщений: 457
wurdu
Timur Akhmadeev
Данные для статистики берутся напрямую из x$kewrsqlidtab, x$kglcursor_child_sqlidph:
Можно попробовать тем же запросом дергать данные самостоятельно и сравнивать с данными из v$sql (все таки retention у v$sqlstats больше).
Этим запросом не получится, т.к. в x$kewrsqlidtab пусто.

Да, странно. У dbms_workload_repository это как-то получается:
STAT #140376495894456 id=5 cnt=91 pid=4 pos=1 obj=0 op='FIXED TABLE FULL X$KEWRSQLIDTAB (cr=0 pr=0 pw=0 time=93 us cost=0 size=800 card=100)'
17 янв 12, 15:04    [11918286]     Ответить | Цитировать    Сообщить модератору
Все форумы / Oracle Ответить