Oracle Analytics 질의에 대한 SQL_ID 찾기
Oracle Analytics에 대한 질의를 조정할 때 데이터 모델, 보고서 디자인, 대시보드 설계, 네트워크 구성을 살펴보고 경우에 따라 데이터베이스를 조사해야 합니다.
V$SQL 테이블
데이터베이스 조사의 경우 ASH(활성 세션 내역), AWR(자동 작업 로드 저장소) 및 Oracle SQLT(SQLTXPLAIN)를 통해 특정 질의를 추적할 수 있도록 SQL 문의 SQL_ID를 알아야 합니다.
이 항목에서는 Oracle Analytics 사용 추적 테이블 및 Oracle 데이터베이스 시스템 테이블 V$SQL(또는 RAC 데이터베이스의 경우 GV$SQL)을 마이닝하여 SQL_ID를 찾는 방법을 보여줍니다.
V$SQL은 개별 SQL 질의에 대한 통계를 추적하는 Oracle 데이터베이스 시스템 테이블입니다. 실행하는 SQL 문마다 하나씩 테이블에 행이 있으며 각 행은 SQL_ID 열로 고유하게 식별됩니다. 이 SQL_ID를 사용하여 Oracle 데이터베이스 전체에서 특정 SQL 문을 추적할 수 있습니다.
V$SQL 테이블(또는 RAC 데이터베이스의 경우 GV$SQL)에서 질의에 대한 유용한 정보를 많이 찾을 수 있습니다.
사용 추적 테이블
사용 추적 테이블은 SQL_ID를 직접 추적하지 않지만 SQL_ID를 V$SQL 테이블(또는 RAC 데이터베이스의 경우 GV$SQL)로 돌려보내 추적할 수 있습니다.
Oracle Analytics에서 사용 추적을 사용으로 설정하면 모든 질의가 두 테이블에서 추적됩니다.
S_NQ_ACCT- 논리적 질의를 포함합니다.S_NQ_DB_ACCT- 물리적 질의를 포함합니다.
논리적 질의는 논리적 테이블에 기록되고 물리적 질의(또는 논리적 질의에 의해 생성된 질의)는 물리적 질의 테이블에 기록됩니다.
논리적 질의 ID 열에서 논리적 테이블과 물리적 테이블을 조인할 수 있습니다. 논리적 질의 ID 열은 논리적 테이블에서 ID이며 물리적 테이블에서 LOGICAL_QUERY_ID입니다.
SQL_ID를 찾으려면 물리적 테이블의 PHYSICAL_HASH_ID 열이 중요합니다. PHYSICAL_HASH_ID 값은 ACTION 열의 V$SQL(또는 GV$SQL)에도 기록됩니다.
Oracle BI Server는 논리적 SQL 질의의 텍스트와 물리적 SQL 질의의 텍스트에서 해시 코드를 계산합니다. Oracle BI Server에서 실행되는 모든 SQL 질의의 물리적 SQL 해시 코드는 V$SQL의 ACTION 열에 기록됩니다. 자세한 내용은 BI 질의 로그에 S_NQ_ACCT 레코드 연관을(를) 참조하십시오.
다양한 사용 추적 열에 대한 자세한 내용은 사용 추적 및 사용 추적 테이블 이해을(를) 참조하십시오.
설정
이제 PHYSICAL_HASH_ID를 찾을 수 있는 위치를 알았으므로 데이터베이스에서 실행되는 SQL과 Oracle Analytics에서 실행되는 특정 분석을 상관시키는 SQL 문을 작성하는 것은 비교적 사소한 태스크입니다.
사용 추적 테이블이 데이터 웨어하우스와 동일한 데이터베이스 인스턴스에 있는 경우 단일 질의를 작성하여 SQL_ID를 찾을 수 있습니다.
사용 추적 테이블과 데이터 웨어하우스가 다른 Oracle 데이터베이스 인스턴스에 있는 경우 두 개의 질의를 실행해야 합니다. 먼저 사용 추적 테이블에서 조사할 질의의 PHYSICAL_HASH_ID를 풀링할 질의를 실행합니다. 다음으로, PHYSICAL_HASH_ID 값으로 V$SQL에서 SQL_ID를 풀링하여 ACTION 열을 필터링합니다.
예
이 예에서는 사용 추적 테이블과 데이터 웨어하우스가 같은 위치에 있다고 가정합니다. 사용 추적 및 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;
두번째 예에서는 사용 추적 테이블과 데이터 웨어하우스가 다른 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;
다음으로, PHYSICAL_HASH_ID 값을 필터로 사용하여 V$SQL에 대해 SQL_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)에 물리적 해시 ID가 포함되어 있지 않으므로 초기화 블록 문제를 해결하는 경우에는 이 방법이 작동하지 않습니다.
다음 예에서는 'A - Sample Sales' 주제 영역을 사용하고 사용 추적 테이블에서 몇 개의 열만 선택합니다.
‘A - Sample Sales’에서 다음 질의를 실행합니다.
[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를 가져옵니다.
다음으로, logical_query_id = E841EBB79217270A660CDD3EFB5D986C인 사용 추적 물리적 테이블에서 logical_query_id, hash_id, physical_hash_id 및 query_text를 선택합니다.
관리 세션 로그를 확인하면 논리적 질의가 QUERY_TEXT 열에 있는 SQL을 생성합니다.
[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 = ‘bd6708b8'인 V$SQL에서 action, sql_id 및 sql_text를 선택합니다.
sql_text 열의 질의는 사용 추적 물리적 테이블에 표시된 것과 동일한 SQL입니다. 이를 통해 해당 질의에 대한 sql_id가 '1gxhc0acmztwk'임을 알 수 있습니다.
동일한 질의를 다시 실행하면 새 ID를 사용하여 사용 추적 논리적 테이블에 다른 행이 추가되지만 hash_id는 동일합니다.
마찬가지로, 새 logical_query_id를 사용하여 사용 추적 물리적 테이블에 다른 행이 추가되지만 hash_id와 physical_hash_id는 모두 동일하게 유지됩니다.
동일한 논리적 및 물리적 모델을 기반으로 하는 주제 영역에 대해 유사한 논리적 질의를 실행하는 경우에도 physical_hash_id가 재사용됩니다. 예를 들어, 여기서는 '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 ]]
이 논리적 질의는 'A – Sample Sales'에 대해 실행한 질의에 표시된 것과 동일한 SQL을 생성합니다.
[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
]]먼저 사용 추적 논리적 테이블을 사용하여 ID를 찾습니다. 논리적 hash_id는 ‘A – Sample Sales’에 대해 실행된 질의와 다릅니다.
사용 추적 물리적 테이블에서 논리적 hash_id는 다르지만 Physical_hash_id는 동일함을 알 수 있습니다.
다른 예에서는 위와 동일한 질의를 실행하지만 이번에는 [새로고침]을 누릅니다. 새로고침을 나타내는 변수 OBIS_REFRESH_CACHE=1를 확인합니다. 예상한 대로 또 다른 행이 사용 추적 논리적 테이블에 삽입되지만 logical hash_id가 다릅니다.
하지만 사용 추적 물리적 테이블을 질의하면 모든 질의의 physical_hash_id가 동일함을 알 수 있습니다.
이러한 네 가지 논리적 질의의 physical_hash_id는 모두 동일하므로 이 질의에 대해 V$SQL에는 단일 레코드만 표시됩니다.
동일한 방법을 사용하여 주제 영역(RPD) 또는 데이터베이스 접속을 기반으로 데이터 시각화 질의 문제를 해결할 수 있습니다. 데이터베이스 접속의 경우 여기에는 데이터 집합을 채우는 데 사용되는 데이터 시각화와 질의가 모두 포함됩니다.
QUERY_SRC_CD 열의 값으로 질의 유형, 즉 데이터 시각화, 데이터 집합, 분석, ODBC 등을 결정할 수 있습니다. 예를 들어, 데이터 시각화 질의에는 '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를 찾을 수 있습니다.
사용 추적 물리적 테이블에서 physical_hash_id를 찾을 수 있습니다.
V$SQL을 질의하면 SQL_ID를 다시 찾습니다.
세션 관리 로그에서 논리적 hash_id 및 physical_hash_id를 찾을 수도 있습니다(위 로그 조각에서 강조 표시된 섹션 참조). 이는 로그 자체에서 SQL_ID를 찾을 수 있음을 의미합니다. 사용 추적 테이블을 사용할 때의 이점은 세션 관리의 로그 항목이 일시적이므로 질의 실행과 동시에 해시 ID를 수집하지 않을 경우 해당 항목이 손실된다는 것입니다.
단, 데이터는 질의가 완료될 때만 사용 추적 테이블에 기록됩니다. 따라서 아직 완료되지 않은 장기 실행 질의 문제를 해결하고 sql_id를 확인하려는 경우 세션 관리 로그에서 논리적 hash_id 및 Physical_hash_id를 가져올 수 있습니다.
SQL 문 문제를 해결하고 데이터베이스를 조사해야 하는 경우 사용 추적 테이블과 V$SQL 시스템 테이블(또는 GV$SQL)의 정보를 조인하여 조사 중인 SQL 문의 SQL_ID를 빠르게 찾을 수 있습니다.