ค้นหา 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 fสำหรับฐานข้อมูล RAC) ได้อย่างง่ายดาย
เมื่อคุณใช้งานการติดตามการใช้งานใน Oracle Analytics ระบบจะติดตามการสืบค้นทั้งหมดในสองตารางดังนี้
S_NQ_ACCT: ประกอบด้วยการสืบค้นแบบลอจิคัลS_NQ_DB_ACCT: ประกอบด้วยการสืบค้นแบบฟิสิคัล
ระบบล็อกการสืบค้นแบบลอจอคัลไว้ในตารางการสืบค้นแบบลอจิคัล และล็อกการสืบค้นแบบฟิสิคัล (หรือการสืบค้นที่สร้างขึ้นโดยการสืบค้นแบบลอจิคัล) are ไว้ในตารางการสืบค้นแบบฟิสิคัล
คุณสามารถรวมตารางแบบลอจิคัลและฟิสิคัลไว้ในคอลัมน์ ID การสืบค้นแบบลอจิคัลได้ คอลัมน์ ID การสืบค้นแบบลอจิคัลในตารางแบบลอจิคัล คือ ID และในตารางแบบฟิสิคัล คือ LOGICAL_QUERY_ID
ความสำคัญในการค้นหา SQL_ID คือ คอลัมน์ PHYSICAL_HASH_ID ในตารางแบบฟิสิคัล ค่า PHYSICAL_HASH_ID จะถูกเขียนไปยังตาราง V$SQL (หรือตาราง GV$SQL ) ในคอลัมน์ ACTION
เซิร์ฟเวอร์ Oracle BI จะคำนวณรหัสแฮชจากข้อความของการสืบค้น SQL แบบลอจิคัลและข้อความของการสืบค้น SQL แบบฟิสิคัล รหัสแฮช SQL แบบฟิสิคัลของการสืบค้น SQL ใดๆ ที่รันจากเซิร์ฟเวอร์ Oracle BI จะได้รับการบันทึกไว้ในคอลัมน์ ACTION ในตาราง V$SQL โปรดดูข้อมูลเพิ่มเติมที่ เชื่อมโยงเรคคอร์ด S_NQ_ACCT กับล็อกการสืบค้น BI
โปรดดูข้อมูลเพิ่มเติมเกี่ยวกับคอลัมน์การติดตามการใช้งานต่างๆ ที่ ติดตามการใช้งาน และ ทำความเข้าใจตารางการติดตามการใช้งาน
การตั้งค่า
ถึงตอนนี้คุณก็ทราบแล้วว่าจะค้นหา 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) ไม่มี ID แฮชแบบฟิสิคัล
ตัวอย่าง 3
ในตัวอย่างนี้ คุณใช้หัวเรื่อง 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, hash_id, physical_hash_id และ query_text จากตารางแบบฟิสิคัลของการติดตามการใช้งาน โดยที่ LOGICAL_QUERY_ID คือ E841EBB79217270A660CDD3EFB5D986C
เมื่อคุณดูที่ล็อกการจัดการเซสชัน การสืบค้นแบบลอจิคัลจะสร้าง 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
การสืบค้นในคอลัมน์ sql_text เป็นรหัส SQL เดียวกับที่เห็นในตารางแบบฟิสิคัลของการติดตามการใช้งาน จากขั้นตอนนี้ คุณจะเห็นว่า sql_id สำหรับการสืบค้นในคำถาม คือ 1gxhc0acmztwk
หากคุณรันการสืบค้นเดิมอีกครั้ง ระบบจะเพิ่มแถวในตารางแบบลอจิคัลของการติดตามการใช้งานด้วย ID ใหม่ แต่ hash_id เหมือนเดิม
เช่นเดียวกัน ระบบจะเพิ่มแถวในตารางแบบฟิสิคัลของการติดตามการใช้งานด้วย logical_query_id ใหม่ แต่ทั้ง hash_id และ physical_hash_id ยังคงเหมือนเดิม
ตัวอย่าง 4
นอกจากนี้ ระบบจะใช้ 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 ]]
การสืบค้นแบบลอจิคัลนี้แสดงรหัส SQL เดียวกับที่เห็นในการสืบค้นที่คุณรันกับ 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
]]อันดับแรก ใช้ตารางแบบลอจิคัลของการติดตามการใช้งานเพื่อค้นหา ID คุณสามารถเห็น hash_id แบบลอจิคัลแตกต่างจากการสืบค้นที่รันกับ A – Sample Sales
ในตารางแบบฟิสิคัลของการติดตามการใช้งาน คุณจะเห็นว่าถึงแม้ hash_id แบบลอจิคัลจะแตกต่างกัน แต่ physical_hash_id ยังคงเหมือนเดิม
ตัวอย่าง 5
ในตัวอย่างนี้ คุณรันการสืบค้นเดียวกัน แต่ครั้งนี้คุณคลิก รีเฟรช โปรดสังเกตตัวแปร OBIS_REFRESH_CACHE=1 เพื่อแสดงถึงการรีเฟรช ตามที่คาดไว้ ระบบจะเพิ่มอีกแถวหนึ่งในตารางแบบลอจิคัลของการติดตามการใช้ แต่มี logical hash_id. ที่ต่างกัน
อย่างไรก็ตาม เมื่อคุณสืบค้นตารางแบบฟิสิคัลของการติดตามการใช้งาน คุณจะเห็นว่าการสืบค้นทั้งหมดมี physical_hash_id. เหมือนกัน
เนื่องจากการสืบค้นแบบลอจิคัลทั้งสี่รายการนี้มี physical_hash_id เหมือนกัน คุณจึงเห็นเพียงเรคคอร์ดเดียวในตาราง V$SQL สำหรับการสืบค้นนี้
ตัวอย่าง 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 ที่ใช้เป็นฟิลเตอร์ของตารางแบบฟิสิคัลของการติดตามการใช้งาน
จากตารางแบบฟิสิคัลของการติดตามการใช้งาน คุณจะพบ 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_ID สำหรับคำสั่ง SQL ที่คุณกำลังตรวจสอบ