Поиск идентификатора SQL_ID для запроса Oracle Analytics

При настройке запросов для Oracle Analytics пользователь обращается к модели данных, дизайну отчетов и инфопанели, конфигурации сети, а иногда ему необходимо проанализировать базу данных.

Таблица V$SQL

Для анализа базы данных необходимо знать SQL_ID инструкции SQL, чтобы можно было отслеживать конкретный запрос с помощью истории активных сеансов (ASH), автоматического репозитория рабочей нагрузки (AWR), и Oracle SQLTXPLAIN (SQLT).

В этом разделе рассказывается, как найти SQL_ID с помощью таблиц отслеживания использования Oracle Analytics и системной таблицы базы данных Oracle V$SQL (или GV$SQL для базы данных RAC).

V$SQL – это системная таблица базы данных Oracle для отслеживания статистики по отдельным запросам SQL. В таблице есть одна строка для каждой запущенной инструкции SQL, и каждая строка уникально определяется с помощью столбца SQL_ID . Можно использовать этот идентификатор SQL_ID для отслеживания данной инструкции SQL во всей базе данных Oracle.

Много полезной информации о запросах можно найти в таблице V$SQL (или GV$SQL для базы данных RAC).

Таблицы отслеживания использования

Таблицы отслеживания использования не отслеживают напрямую идентификатор SQL_ID, но можно легко отследить SQL_ID до таблицы V$SQL (или GV$SQL для базы данных RAC).

При включении параметра "Отслеживание использования" в Oracle Analytics все запросы отслеживаются в двух таблицах:

  • S_NQ_ACCT – содержит логические запросы
  • S_NQ_DB_ACCT – содержит физические запросы

Логический запрос регистрируется в логической таблице, а физический запрос (или запросы, порожденные логическим запросом) регистрируются в таблице физических запросов.

Можно объединить логические и физические таблицы в столбце идентификатора логического запроса. Столбец логического идентификатора запроса в логической таблице – это ID, а в физической таблице – LOGICAL_QUERY_ID.

Ключом к поиску идентификатора SQL_ID является столбец PHYSICAL_HASH_ID в физической таблице. Значение PHYSICAL_HASH_ID также записывается в таблице V$SQL (или в таблице GV$SQL) в столбце ACTION.

Сервер Oracle BI Server вычисляет хеш-код на основе текста логического запроса SQL и текста физических запросов SQL. Хеш-код физического запросов SQL, которые выполняются с сервера Oracle BI, записывается в столбце ACTION в таблице V$SQL. Чтобы узнать больше, см. Связывание записи S_NQ_ACCT с журналом запросов BI

Подробнее о различных столбцах отслеживания использования см. в разделе Отслеживание использования and Общие сведения о таблицах отслеживания использования.

Настройка

Зная, где найти PHYSICAL_HASH_ID, будет не сложно написать инструкцию SQL, которая сопоставляет запрос SQL, выполняющийся в базе данных, с конкретным анализом, выполняемым в Oracle Analytics.

Если таблицы отслеживания использования находятся в том же экземпляре базы данных, что и хранилище данных, можно написать один запрос, чтобы найти SQL_ID.

Если таблицы отслеживания использования и хранилище данных находятся в разных экземплярах базы данных Oracle, необходимо выполнить два запроса. Сначала следует запустить запрос, чтобы получить идентификатор PHYSICAL_HASH_ID запроса, который требуется проанализировать, из таблиц отслеживания использования. Во-вторых, необходимо извлечь идентификатор SQL_ID из таблицы V$SQL, используя значение PHYSICAL_HASH_ID для фильтрации столбца ACTION.

Пример 1

В этом примере предположим, что таблицы отслеживания использования и хранилище данных расположены в одном месте. В запросе можно использовать любые или все столбцы из таблиц отслеживания использования и V$SQL, но в этом примере выбрано следующее подмножество:

select

o.sql_id,

to_char(l.start_ts, 'YYYY-MM-DD HH24:MI:SS'),

l.id as l_id,

p.logical_query_id,

l.hash_id,

l.saw_src_path,

l.query_src_cd,

l.success_flg,

l.num_db_query,

l.query_text,

p.query_text,

o.sql_text,

p.physical_hash_id,

o.action

from

usage_tracking.s_nq_acct l,

usage_tracking.s_nq_db_acct p,

v$sql o

where

l.id = p.logical_query_id and o.action = p.physical_hash_id and l.start_dt > trunc(sysdate - 1) and l.end_dt < trunc(sysdate)

order by l.start_ts, l.id, l.hash_id, p.physical_hash_id, o.sql_id;

Пример 2

В этом примере предположим, что таблицы отслеживания использования и хранилище данных расположены в разных базах данных Oracle.

Сначала напишите запрос к таблицам отслеживания использования, чтобы получить PHYSICAL_HASH_ID:

select

to_char(l.start_ts, 'YYYY-MM-DD HH24:MI:SS'),

l.id as l_id,

p.logical_query_id,

l.hash_id,

l.saw_src_path,

l.query_src_cd,

l.success_flg,

l.num_db_query,

l.query_text,

p.query_text,

p.physical_hash_id

from

usage_tracking.s_nq_acct l,

usage_tracking.s_nq_db_acct p

where

l.id = p.logical_query_id and l.start_dt > trunc(sysdate - 1) and l.end_dt < trunc(sysdate)

order by l.start_ts, l.id,l.hash_id, p.physical_hash_id;

Далее напишите запрос к таблице V$SQL , чтобы получить идентификатор SQL_ID, используя значения PHYSICAL_HASH_ID в качестве фильтра.

select

o.action,

o.sql_id,

o.sql_text

from

v$sql o

where

o.action = ‘<physical_hash_id>’

order by o.sql_id;

Если используется база данных RAC, то вместо V$SQL следует обратиться к таблице GV$SQL для получения идентификатора SQL_ID.

select

o.action,

o.sql_id,

o.sql_text

from

gv$sql o

where

o.action = '<physical_hash_id>'

order by o.sql_id;

Этот метод не работает при устранении неполадок блоков инициализации, так как таблица отслеживания использования для блоков инициализации (S_NQ_INITBLOCK) не содержит физический хеш-идентификатор.

Пример 3

В следующем примере используется предметная область "A – примеры продаж"и выбирается только несколько столбцов из таблиц отслеживания использования.

Из предметной области "A – примеры продаж" запускается следующий запрос:

[2022-02-04T15:11:17.629+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242531,0:1:38:3] [sik: bootstrap] [tid: dd1bc700] [messageId: USER-0] [requestid: 33e30020] [sessionid: 33e30000] [username: oacadmin] ############################################## [[

-------------------- SQL Request, logical request hash:

cee7ec94

SET VARIABLE QUERY_SRC_CD='Report';SELECT

   0 s_0,

   "A - Sample Sales"."Offices"."D1  Office" s_1,

   "A - Sample Sales"."Base Facts"."10- Variable Costs" s_2,

   "A - Sample Sales"."Base Facts"."11- Fixed Costs" s_3

FROM "A - Sample Sales"

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

]]

При выборе start_ts, id, hash_id, query_src_cd и query_text из логической таблицы отслеживания использования будет получено значение ID E841EBB79217270A660CDD3EFB5D986C.

Описание GUID-5FA7BA53-6767-4839-9341-A6AC0AACDFEB-default.jpg ниже
.jpg

Затем выберите logical_query_id, hash_id, physical_hash_id и query_text из физической таблицы отслеживания использования, где LOGICAL_QUERY_IDE841EBB79217270A660CDD3EFB5D986C.

Описание GUID-6842B138-0FD5-4DA9-A092-B423EB7459C2-default.jpg ниже
.jpg

При просмотре журнала сеансов управления логический запрос порождает код SQL, который находится в столбце QUERY_TEXT:

[2022-02-04T15:11:17.637+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242531,0:1:38:5] [sik: bootstrap] [tid: dd1bc700] [messageId: USER-18] [requestid: 33e30020] [sessionid: 33e30000] [username: oacadmin] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<62275>>), client type Oracle Call Interface (OCI), connection pool named Sample Relational Connection, logical request hash cee7ec94, physical request hash bd6708b8: [[

WITH

SAWITH0 AS (select sum(T5398.Cost_Fixed) as c1,

     sum(T5398.Cost_Variable) as c2,

     T5257.Office_Dsc as c3,

     T5257.Office_Key as c4

from

     BISAMPLE.SAMP_OFFICES_D T5257 /* D30 Offices */ ,

     BISAMPLE.SAMP_REVENUE_F T5398 /* F10 Billed Rev */

where  ( T5257.Office_Key = T5398.Office_Key )

group by T5257.Office_Dsc, T5257.Office_Key),

SAWITH1 AS (select 0 as c1,

     D1.c3 as c2,

     D1.c2 as c3,

     D1.c1 as c4,

     D1.c4 as c5

from

     SAWITH0 D1)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3,

     D1.c4 as c4

from

     SAWITH1 D1

order by c2 ) D1 where rownum <= 125001

]]

Наконец, выберите action, sql_id и sql_text из таблицы V$SQL где Action‘bd6708b8'

Описание GUID-094521FA-317B-430D-9644-3C3E44D91900-default.jpg ниже
.jpg

Запрос в столбце sql_text  – это тот же код SQL, что и в физической таблице отслеживания использования. Таким образом, идентификатор sql_id для рассматриваемого запроса – это 1gxhc0acmztwk.

Если снова запустить тот же запрос, в логическую таблицу отслеживания использования будет добавлена еще одна строка с новым идентификатором, но hash_id останется прежним.

Описание GUID-82CD3499-E550-4DD4-8C96-E3DB3CE3232E-default.jpg ниже
.jpg

Аналогично: еще одна строка добавляется в логическую таблицу отслеживания использования с новым идентификатором logical_query_id, но оба идентификатора hash_id и physical_hash_id остаются прежними.

Описание GUID-BF05E975-45FB-4F40-9AE4-9E40A1124F35-default.jpg ниже
.jpg

Пример 4

Идентификатор physical_hash_id также используется повторно, если выполняется аналогичный логический запрос к предметной области на основе на тех же логических и физических моделей. Например, если запускается аналогичный запрос к "C – пример затрат":

[2022-02-04T16:10:17.862+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242ce7,0:1:15:3] [sik: bootstrap] [tid: 58504700] [messageId: USER-0] [requestid: 13c9003c] [sessionid: 13c90000] [username: oacadmin] ############################################## [[

-------------------- SQL Request, logical request hash:

7b5ea9b1

SET VARIABLE QUERY_SRC_CD='Report';SELECT

   0 s_0,

   "C - Sample Costs"."Offices"."D1  Office" s_1,

   "C - Sample Costs"."Base Facts"."10- Variable Costs" s_2,

   "C - Sample Costs"."Base Facts"."11- Fixed Costs" s_3

FROM "C - Sample Costs"

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

]]

Этот логический запрос порождает тот же код SQL, что и в запросе, который выполнялся для "A –примеры продаж":

[2022-02-04T16:10:17.866+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: e49b96a8-33c4-4ba7-a877-e564d207eca1-00242ce7,0:1:15:5] [sik: bootstrap] [tid: 58504700] [messageId: USER-18] [requestid: 13c9003c] [sessionid: 13c90000] [username: oacadmin] -------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<52912>>), client type Oracle Call Interface (OCI), connection pool named Sample Relational Connection, logical request hash 7b5ea9b1, physical request hash bd6708b8: [[

WITH

SAWITH0 AS (select sum(T5398.Cost_Fixed) as c1,

     sum(T5398.Cost_Variable) as c2,

     T5257.Office_Dsc as c3,

     T5257.Office_Key as c4

from

     BISAMPLE.SAMP_OFFICES_D T5257 /* D30 Offices */ ,

     BISAMPLE.SAMP_REVENUE_F T5398 /* F10 Billed Rev */

where  ( T5257.Office_Key = T5398.Office_Key )

group by T5257.Office_Dsc, T5257.Office_Key),

SAWITH1 AS (select 0 as c1,

     D1.c3 as c2,

     D1.c2 as c3,

     D1.c1 as c4,

     D1.c4 as c5

from

     SAWITH0 D1)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select D1.c1 as c1,

     D1.c2 as c2,

     D1.c3 as c3,

     D1.c4 as c4

from

     SAWITH1 D1

order by c2 ) D1 where rownum <= 125001

]]

Во-первых, используйте логическую таблицу отслеживания использования, чтобы найти идентификатор. Обратите внимание, что логический hash_id отличается от запроса, выполняемого для "А – примеры продаж".

Описание GUID-16D78632-88BA-446F-A6D7-496DA5662869-default.jpg ниже
.jpg

В физической таблице отслеживания использования видно, что хотя логический hash_id отличается, идентификатор physical_hash_id остается тем же.

Описание GUID-91049191-7E76-407D-B15B-A74160942148-default.jpg ниже
.jpg

Пример 5

В этом примере выполняются те же запросы (как указано выше), но на этот раз вы нажимаете кнопку Обновить. Обратите внимание на переменную OBIS_REFRESH_CACHE=1, которая указывает на обновление. Как и ожидалось, в логическую таблицу отслеживания использования вставляется еще одна строка, но она имеет другой logical hash_id.

Описание GUID-BFA0DEA8-423A-414C-8BC9-E1B8EA382689-default.jpg ниже
.jpg

Однако при запросе физической таблицы отслеживания использования можно увидеть, что все запросы имеют один и тот же physical_hash_id.

Описание GUID-D6CC7144-9177-4CDA-B57A-1A4E4A6615A5-default.jpg ниже
.jpg

Поскольку все эти четыре логических запроса имеют одинаковый physical_hash_id, в таблице V$SQL можно увидеть только одну запись для этого запроса.

Описание GUID-13AE24EE-9C17-48D9-976F-49FF882A5E42-default.jpg ниже
.jpg

Пример 6

Тот же метод можно использовать для устранения неполадок с запросами визуализации данных на основе предметной области (RPD) или подключения к базе данных. Для подключений к базам данных это включает как визуализацию данных, так и запросы, используемые для заполнения наборов данных.

Тип запроса, то есть визуализацию данных, набор данных, анализ, ODBC и т. д., можно определить по значению столбца QUERY_SRC_CD. Например, запросы визуализации данных имеют значение Visual Analyzer, запрос, используемый для заполнения набора данных, имеет значение data-prep, анализы имеют значение Report, а запросы для некоторых внутренних вызовов процедур ODBC имеют значение Soap.

Чтобы сгенерировать оператор SQL для визуализации данных, необходимо изменить доступ к данным на "Интерактивный" для базового набора данных.

В этом примере создается запрос визуализации данных (по аналогии с запросами выше) к набору данных на основе подключения к базе данных Oracle.

[2022-02-03T19:42:06.564+00:00] [OBIS] [TRACE:2] [USER-0] [] [ecid: f95b5f1b-1e5c-4604-b82a-3eb3717f3aa6-002aa26e,0:1:1:3] [sik: bootstrap] [tid: 58b0a700] [messageId: USER-0] [requestid: ed830023] [sessionid: ed830000] [username: oacadmin] ############################################## [[

-------------------- SQL Request, logical request hash:

3158b05

SET VARIABLE QUERY_SRC_CD='Visual Analyzer',SAW_SRC_PATH='{"viewID":"view!1","currentCanvas":"canvas!1"}',ENABLE_DIMENSIONALITY = 1; SELECT

   0 s_0,

   XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_OFFICES_D"."OFFICE_DSC" s_1,

   XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_REVENUE_F"."COST_FIXED" s_2,

   XSA('oacadmin'.'BISAMPLE_OfficeRevenue')."SAMP_REVENUE_F"."COST_VARIABLE" s_3

FROM XSA('oacadmin'.'BISAMPLE_OfficeRevenue')

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

/* AdditionalDetail='VisualAnalyzer' */

Это порождает следующий код SQL:

[2022-02-03T19:42:06.960+00:00] [OBIS] [TRACE:2] [USER-18] [] [ecid: f95b5f1b-1e5c-4604-b82a-3eb3717f3aa6-002aa26e,0:1:1:5] [sik: bootstrap] [tid: 58b0a700] [messageId: USER-18] [requestid: ed830023] [sessionid: ed830000] [username: oacadmin] -------------------- Sending query to database named 'oacadmin'.'BISAMPLE' (id: <<147945>>), client type OCI 10g/11g, connection pool named 'oacadmin'.'BISAMPLE', logical request hash 3158b05, physical request hash c48e8741: [[

WITH

SAWITH0 AS (select T1000005.OFFICE_KEY as c1,

     T1000005.OFFICE_DSC as c2

from

     BISAMPLE.SAMP_OFFICES_D T1000005),

SAWITH1 AS (select T1000008.OFFICE_KEY as c1,

     T1000008.COST_FIXED as c2,

     T1000008.COST_VARIABLE as c3

from

     BISAMPLE.SAMP_REVENUE_F T1000008),

SAWITH2 AS (select D1.c2 as c1,

     D2.c2 as c2,

     D2.c3 as c3

from

     SAWITH0 D1 inner join SAWITH1 D2 On D1.c1 = D2.c1),

SAWITH3 AS (select D102.c1 as c1,

     sum(D102.c2) as c2,

     sum(D102.c3) as c3

from

     SAWITH2 D102

group by D102.c1)

select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,

     D110.c1 as c2,

     D110.c2 as c3,

     D110.c3 as c4

from

     SAWITH3 D110

order by c2 ) D1 where rownum <= 125001

В логической таблице отслеживания использования можно найти идентификатор hash_id, который используется для фильтрации физической таблицы отслеживания использования.

Описание GUID-C053C7FB-251A-4021-A850-1F6006BEBFF1-default.jpg ниже
.jpg

В физической таблице отслеживания использования можно найти physical_hash_id.

Описание GUID-EB289F16-56B0-4AFB-8C96-2D7B55680B7B-default.jpg ниже
.jpg

При запросе к таблице V$SQL снова находится SQL_ID.

Описание GUID-CD805B03-A8AD-40F0-AE84-BD28AC8B6EF8-default.jpg ниже
.jpg

Также можно найти логические идентификаторы hash_id и physical_hash_id в журнале управления сеансами (см. выделенные разделы в предыдущих примерах журнала). Это означает, что можно найти SQL_ID в самом журнале. Преимущество применения таблиц отслеживания использования заключается в следующем: записи журнала в разделе "Управление сеансами" являются временными, поэтому, если хеш-идентификаторы не собираются одновременно с выполнением запроса, они теряются.

Однако данные записываются в таблицы отслеживания использования только после завершения запроса. Поэтому, если требуется устранить неполадки в продолжительном запросе, который еще не завершен, и определить идентификатор sql_id, можно получить логические идентификаторы hash_id и physical_hash_id из журнала управления сеансами.

Для устранения неполадок в операторе SQL и анализа базы данных можно объединить информацию в таблицах отслеживания использования и системной таблице V$SQL (или GV$SQL), чтобы быстро найти идентификатор SQL_ID для анализируемого оператора SQL.