Encontrar a SQL_ID para uma Consulta do Oracle Analytics
Quando otimiza as consultas do Oracle Analytics, examina o modelo de dados, o design do relatório, o design do dashboard e a configuração da rede e, por vezes, precisa de investigar a base de dados.
A Tabela V$SQL
Para as investigações da base de dados, precisa de saber a SQL_ID da instrução de SQL para poder controlar a consulta específica através do Active Session History (ASH), do Automatic Workload Repository (AWR), e do Oracle SQLTXPLAIN (SQLT).
Este tópico mostra como encontrar a SQL_ID ao efetuar o mining das tabelas de controlo da utilização do Oracle Analytics e da tabela do sistema da base de dados Oracle denominada V$SQL (ou GV$SQL para uma base de dados RAC).
A tabela V$SQL é uma tabela do sistema da base de dados Oracle que controla as estatísticas de consultas de SQL individuais. Existe uma linha na tabela para cada instrução de SQL que é executada e cada linha é identificada de modo exclusivo pela coluna SQL_ID. Pode utilizar esta SQL_ID para controlar uma determinada instrução de SQL em toda a base de dados Oracle.
Encontrará muitas informações úteis sobre as suas consultas na tabela V$SQL (ou GV$SQL para uma base de dados RAC).
Tabelas de Controlo da Utilização
As tabelas de controlo da utilização não controlam diretamente a SQL_ID, mas pode facilmente rastrear a SQL_ID até à tabela V$SQL (ou GV$SQL para uma base de dados RAC).
Quando ativa o controlo da utilização no Oracle Analytics, todas as consultas são controladas em duas tabelas:
S_NQ_ACCT: Contém consultas lógicasS_NQ_DB_ACCT: Contém consultas físicas
A consulta lógica é registada na tabela de consultas lógicas e a consulta física (ou as consultas geradas pela consulta lógica) é registada na tabela de consultas físicas.
Pode juntar as tabelas lógicas e físicas na coluna de ID da consulta lógica. A coluna de ID da consulta lógica na tabela lógica é ID e na tabela física é LOGICAL_QUERY_ID.
A chave para encontrar a SQL_ID é a coluna PHYSICAL_HASH_ID na tabela física. O valor de PHYSICAL_HASH_ID também é escrito na tabela V$SQL (ou na tabela GV$SQL) na coluna ACTION.
O Oracle BI Server calcula um código hash a partir do texto da consulta de SQL lógico e do texto das consultas de SQL físico. O código hash de SQL físico de quaisquer consultas de SQL executadas a partir do Oracle BI Server é registado na coluna ACTION na tabela V$SQL. Para obter mais informações, consulte Associar o Registo S_NQ_ACCT ao Diário de Consultas do BI.
Para mais informações sobre as várias colunas de controlo da utilização, consulte Controlar a Utilização e Noções sobre as Tabelas de Controlo da Utilização.
Configuração
Agora que sabe onde encontrar a PHYSICAL_HASH_ID, pode escrever uma instrução de SQL que correlaciona a consulta de SQL em execução na base de dados com uma análise específica em execução no Oracle Analytics.
Quando as tabelas de controlo da utilização estão na mesma instância da base de dados que o data warehouse, pode escrever uma única consulta para encontrar a SQL_ID.
Se as tabelas de controlo da utilização e o seu data warehouse estiverem em instâncias da base de dados Oracle diferentes, é necessário executar duas consultas. Primeiro, execute uma consulta para extrair a PHYSICAL_HASH_ID da consulta que pretende investigar a partir das tabelas de controlo da utilização. Em segundo lugar, extraia a SQL_ID da tabela V$SQL utilizando o valor da PHYSICAL_HASH_ID para filtrar a coluna ACTION.
Exemplo 1
Neste exemplo, assuma que as tabelas de controlo da utilização e o data warehouse se encontram na mesma localização. Qualquer uma ou todas as colunas das tabelas de controlo da utilização e V$SQL podem ser utilizadas na consulta, mas, neste exemplo, selecione 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, assuma que as tabelas de controlo da utilização e o data warehouse estão localizados em bases de dados Oracle diferentes.
Primeiro, escreva uma consulta em relação às tabelas de controlo da utilização para obter a 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 em relação à tabela V$SQL para obter a SQL_ID, utilizando os valores de PHYSICAL_HASH_ID como 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 tiver uma base de dados RAC, em vez da tabela V$SQL, utilize a tabela GV$SQL para obter a 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 funciona se estiver a resolver problemas de blocos de inicialização, uma vez que a tabela de controlo da utilização de blocos de inicialização (S_NQ_INITBLOCK) não contém uma ID hash física.
Exemplo 3
Neste exemplo, utilize a área de atividade A - Sample Sales e selecione apenas algumas colunas das tabelas de controlo da utilização.
A partir de 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 ]]
Quando seleciona start_ts, id, hash_id, query_src_cd e query_text a partir da tabela lógica de controlo da utilização, obtém o valor de ID E841EBB79217270A660CDD3EFB5D986C.
Em seguida, selecione logical_query_id, hash_id, physical_hash_id e query_text a partir da tabela física de controlo da utilização, em que LOGICAL_QUERY_ID é E841EBB79217270A660CDD3EFB5D986C.
Quando observa o diário Gerir Sessões, a consulta lógica gera o código de 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
]]Por fim, selecione action, sql_id e sql_text a partir da tabela V$SQL, em que Action é bd6708b8.
A consulta na coluna sql_text é o mesmo código de SQL visto na tabela física de controlo da utilização. Pode ver que a sql_id para a consulta em questão é 1gxhc0acmztwk.
Se executar a mesma consulta novamente, é acrescentada outra linha à tabela lógica de controlo da utilização com uma nova ID, mas a hash_id é a mesma.
De forma semelhante, é acrescentada outra linha à tabela física de controlo da utilização com uma nova logical_query_id, mas a hash_id e a physical_hash_id permanecem as mesmas.
Exemplo 4
A physical_hash_id também é reutilizada se executar uma consulta lógica semelhante numa área de atividade que se baseia nos mesmos modelos lógicos e físicos. Por exemplo, se executar uma consulta semelhante em relação a C - Sample Costs:
[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 de SQL visto na consulta que executou relativamente a 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, utilize a tabela lógica de controlo da utilização para encontrar a ID. Pode ver que a hash_id lógica é diferente da consulta executada em relação a A – Sample Sales:
Na tabela física de controlo da utilização, pode ver que, embora a hash_id lógica seja diferente, a physical_hash_id é a mesma.
Exemplo 5
Neste exemplo, execute as mesmas consultas, mas desta vez clique em Renovar. Repare na variável OBIS_REFRESH_CACHE=1 para indicar a renovação. Como esperado, é inserida outra linha na tabela lógica de controlo da utilização, mas tem uma logical hash_id diferente.
No entanto, quando consulta a tabela física de controlo da utilização, pode ver que todas as consultas têm a mesma physical_hash_id.
Como estas quatro consultas lógicas têm todas a mesma physical_hash_id, vê apenas um único registo na tabela V$SQL para esta consulta.
Exemplo 6
Pode utilizar o mesmo método para resolver problemas de consultas de visualização de dados com base numa área de atividade (RPD) ou numa ligação à base de dados. Para ligações à base de dados, isto inclui as visualizações de dados e as consultas utilizadas para preencher os conjuntos de dados.
Pode determinar o tipo de consulta, ou seja, visualização de dados, conjunto de dados, análise, ODBC, etc., a partir do valor da coluna QUERY_SRC_CD. Por exemplo, as consultas de visualização de dados têm um valor Visual Analyzer, uma consulta utilizada para preencher um conjunto de dados tem o valor data-prep, as análises têm o valor Report e as consultas para algumas chamadas de procedimento ODBC internas têm o valor Soap.
Para gerar uma instrução de SQL para uma visualização de dados, deve alterar o Acesso aos Dados para Interativo para o conjunto de dados subjacente.
Neste exemplo, é criada uma consulta de visualização de dados (semelhante às consultas anteriores) em relação a um conjunto de dados com base numa ligação a uma base 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 de 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 <= 125001Na tabela lógica de controlo da utilização, pode encontrar a hash_id que é utilizada como um filtro na tabela física de controlo da utilização.
Na tabela física de controlo da utilização, pode encontrar a physical_hash_id.
Ao consultar a tabela V$SQL, encontra novamente a SQL_ID.
Também pode encontrar a hash_id lógica e a physical_hash_id no diário Gerir Sessões (consulte as secções destacadas nos exemplos de diário anteriores). Isto significa que pode encontrar a SQL_ID a partir do próprio diário. A vantagem de utilizar as tabelas de controlo da utilização é que as entradas do diário em Gerir Sessões são temporárias e, por isso, perdem-se, a menos que esteja a recolher IDs hash ao mesmo tempo que executa a consulta.
No entanto, os dados só são escritos nas tabelas de controlo da utilização quando uma consulta é concluída. Assim, se estiver a resolver problemas de uma consulta de execução longa que ainda não foi concluída e pretender determinar a sql_id, pode obter a hash_id lógica e a physical_hash_id a partir do diário Gerir Sessões.
Se estiver a resolver problemas de uma instrução de SQL e precisar de investigar a base de dados, pode juntar as informações nas tabelas de controlo da utilização e na tabela do sistema V$SQL (ou GV$SQL) para encontrar rapidamente a SQL_ID para a instrução de SQL que está a investigar.