Localizar o SQL_ID para uma Consulta do Oracle Analytics
Quando você ajusta consultas para o Oracle Analytics, observa o modelo de dados, o design do relatório e do painel de controle e a configuração da rede, e às vezes é necessário investigar o banco de dados.
A Tabela V$SQL
Para investigações de banco de dados, é necessário saber o SQL_ID da instrução SQL para que você possa rastrear a consulta específica por meio do ASH (histórico de sessões ativas), do AWR (Automatic Workload Repository), e do Oracle SQLTXPLAIN (SQLT).
Este tópico mostra como localizar o SQL_ID selecionando as tabelas de rastreamento de uso do Oracle Analytics e a tabela do sistema de banco de dados Oracle chamada V$SQL (ou GV$SQL para um banco de dados RAC).
A tabela V$SQL é uma tabela do sistema de banco de dados Oracle que rastreia estatísticas para consultas SQL individuais. Há uma linha na tabela para cada instrução SQL que você executa, e cada linha é identificada de forma exclusiva pela coluna SQL_ID . Você pode usar este SQL_ID para rastrear uma determinada instrução SQL em todo o banco de dados Oracle.
Você encontrará muitas informações úteis sobre suas consultas na tabela V$SQL (ou GV$SQL de um banco de dados RAC).
Tabelas de Rastreamento de Uso
As tabelas de rastreamento de uso não rastreiam diretamente o SQL_ID, mas você pode facilmente rastrear o SQL_ID de volta à tabela V$SQL (ou GV$SQL para um banco de dados RAC).
Quando você ativa o rastreamento de uso no Oracle Analytics, todas as consultas são rastreadas em duas tabelas:
S_NQ_ACCT: Contém consultas lógicasS_NQ_DB_ACCT: Contém consultas físicas
A consulta lógica é registrada em log na tabela de consulta lógica e a consulta física (ou consultas geradas pela consulta lógica) são registradas em log na tabela da consulta física.
Você pode juntar as tabelas lógica e física na coluna ID da consulta lógica. A coluna ID da consulta lógica na tabela lógica é ID e na tabela física é LOGICAL_QUERY_ID.
A chave para localizar o SQL_ID é a coluna PHYSICAL_HASH_ID na tabela física. O valor PHYSICAL_HASH_ID também é gravado na tabela V$SQL (ou na tabela GV$SQL) na coluna ACTION.
O Oracle BI Server calcula um código hash com base no texto da consulta SQL lógica e no texto das consultas SQL físicas. O código hash SQL físico de qualquer consulta SQL executada pelo Oracle BI Server é registrado na coluna ACTION na tabela V$SQL. Para obter mais informações, consulte Associar o Registro S_NQ_ACCT com o Log de Consulta do BI.
Para obter mais informações sobre as diversas colunas de rastreamento de uso, consulte Rastrear Uso e Noções Básicas de Tabelas de Rastreamento de Uso.
Configuração
Agora que você sabe onde localizar o PHYSICAL_HASH_ID, poderá escrever uma instrução SQL que correlacione a consulta SQL em execução no banco de dados com uma análise específica em execução no Oracle Analytics.
Quando as tabelas de rastreamento de uso estão na mesma instância de banco de dados que o data warehouse, você pode gravar uma consulta simples para localizar o SQL_ID.
Se as tabelas de rastreamento de uso e seu data warehouse estiverem em instâncias distintas do banco de dados Oracle, será necessário executar duas consultas. Primeiro, você executa uma consulta para extrair das tabelas de rastreamento de uso o PHYSICAL_HASH_ID da consulta que você deseja investigar. Depois, você extrai o SQL_ID da tabela V$SQL usando o valor do PHYSICAL_HASH_ID para filtrar a coluna ACTION.
Exemplo 1
Neste exemplo, pressuponha que as tabelas de rastreamento de uso e o data warehouse estão co-localizados. Qualquer uma ou todas as colunas das tabelas de rastreamento de uso V$SQL podem ser usadas na consulta, mas neste exemplo você seleciona o seguinte subconjunto:
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;
Exemplo 2
Neste exemplo, suponha que as tabelas de rastreamento de uso e o data warehouse estejam localizados em bancos de dados Oracle distintos.
Primeiro, grave uma consulta com base nas tabelas de rastreamento de uso para obter o 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;
Em seguida, escreva uma consulta na tabela V$SQL para obter o SQL_ID, usando os valores de PHYSICAL_HASH_ID como um filtro:
select o.action, o.sql_id, o.sql_text from v$sql o where o.action = ‘<physical_hash_id>’ order by o.sql_id;
Se você tiver um banco de dados RAC, em vez da tabela V$SQL, use a tabela GV$SQL para obter o 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;
Este método não funcionará se você estiver diagnosticando blocos de inicialização, pois a tabela de rastreamento de uso do bloco de inicialização (S_NQ_INITBLOCK) não contém um ID de hash físico.
Exemplo 3
Neste exemplo, você usa a área de assunto A - Sample Sales e seleciona apenas algumas colunas das tabelas de rastreamento de uso.
Em A - Sample Sales, execute a seguinte consulta:
[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 ]]
Ao selecionar start_ts, id, hash_id, query_src_cd e query_text da tabela lógica de rastreamento de uso, você obtém o valor ID E841EBB79217270A660CDD3EFB5D986C.
Em seguida, selecione logical_query_id, hash_id, physical_hash_id e query_text na tabela física de rastreamento de uso, em que LOGICAL_QUERY_ID é E841EBB79217270A660CDD3EFB5D986C.
Quando você observa o log Gerenciar Sessões, a consulta lógica gera o código SQL que está na coluna 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
]]Finalmente, selecione action, sql_id e sql_text na tabela V$SQL em queAction é bd6708b8.
A consulta na coluna sql_text é o mesmo código SQL visto na tabela física de rastreamento de uso. A partir desse ponto, você vê que o sql_id para a consulta em questão é 1gxhc0acmztwk.
Se você executar a mesma consulta novamente, outra linha será adicionada à tabela lógica de rastreamento de uso com um novo ID, mas o hash_id será o mesmo.
De forma semelhante, outra linha é adicionada à tabela física de rastreamento de uso com um novo logical_query_id, mas tanto hash_id quanto physical_hash_id permanecem os mesmos.
Exemplo 4
O physical_hash_id também será reutilizado se você executar uma consulta lógica semelhante com relação a uma área de assunto que se baseia nos mesmos modelos físico e lógico. Por exemplo, se você executar uma consulta semelhante em C - Amostra de Custos:
[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 ]]
Esta consulta lógica gera o mesmo código SQL visto na consulta que você executou, executado em A - Sample Sales:
[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
]]Primeiro, use a tabela lógica de rastreamento de uso para localizar o ID. Você pode ver que o hash_id lógico é diferente da consulta executada em A – Sample Sales.
Na tabela física de rastreamento de uso, você vê que, embora o hash_id lógico seja diferente, o physical_hash_id é o mesmo.
Exemplo 5
Neste exemplo, você executa as mesmas consultas, mas desta vez clica em Atualizar. Observe a variável OBIS_REFRESH_CACHE=1 para denotar a atualização. Conforme esperado, outra linha é inserida na tabela lógica de rastreamento de uso, mas ela tem outro logical hash_id.
Todavia, quando você consulta a tabela física de rastreamento de uso, pode ver que todas as consultas têm o mesmo physical_hash_id.
Como essas quatro consultas lógicas têm o mesmo physical_hash_id, você vê apenas um único registro na tabela V$SQL para essa consulta.
Exemplo 6
Você pode usar o mesmo método para diagnosticar e solucionar problemas de consultas de visualização de dados com base em uma área de assunto (RPD) ou uma conexão de banco de dados. Para conexões de banco de dados, isso inclui tanto visualizações de dados quanto consultas usadas para preencher conjuntos de dados.
Você pode determinar o tipo de consulta, ou seja, visualização de dados, conjunto de dados, análise, ODBC etc., com base no valor da coluna QUERY_SRC_CD. Por exemplo, consultas de visualização de dados têm um valor Visual Analyzer, uma consulta usada para preencher um conjunto de dados tem o valor de data-prep, análises têm o valor Report e consultas para algumas chamadas internas de procedimento ODBC têm o valor Soap.
Para gerar uma instrução SQL para uma visualização de dados, você deve alterar o Acesso a Dados para Ativo para o conjunto de dados subjacente.
Neste exemplo, você cria uma consulta de visualização de dados (semelhante às consultas anteriores) com relação a um conjunto de dados baseado em uma conexão com um banco de dados 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' */
Que gera o seguinte código 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 <= 125001Com base na tabela lógica de rastreamento de uso, você pode localizar o hash_id que é usado como um filtro com relação à tabela física de rastreamento de uso.
Com base na tabela física de rastreamento de uso, você pode localizar o physical_hash_id.
Quando você consultar a tabela V$SQL, encontrará o SQL_ID novamente.
Você também pode localizar o hash_id lógico e o physical_hash_id no log Gerenciar Sessões (veja as seções destacadas nos exemplos de log anteriores). Isso significa que você pode encontrar o SQL_ID no log propriamente dito. O benefício de usar as tabelas de rastreamento de uso é que as entradas de log em Gerenciar Sessões são temporárias, portanto, a menos que você esteja coletando IDs de hash ao mesmo tempo que executa a consulta, elas serão perdidas.
Contudo, os dados só são gravados nas tabelas de rastreamento de uso quando uma consulta é concluída. Portanto, se você estiver diagnosticando uma consulta de execução longa que ainda não tenha sido concluída e queira determinar o sql_id, você poderá obter o hash_id e physical_hash_id lógico no log Gerenciar Sessões.
Caso esteja diagnosticando uma instrução SQL e precise investigar o banco de dados, você poderá juntar informações nas tabelas de rastreamento de uso e na tabela de sistema V$SQL (ou GV$SQL) para encontrar rapidamente o SQL_ID para a instrução SQL que você está investigando.