[20231114]如何知道一条sql语句涉及到那些表.txt

来源：这里教程网时间：2026-03-03 19:01:33 作者：

[20231114]如何知道一条sql语句涉及到那些表.txt --//别人问的问题，开始想看执行计划不就可以吗？当然一些计划可能仅仅涉及到索引。还有join elimination可能仅仅看到1个表。 --//对方的目的就是获取这条sql语句相关表，重新分析表看看。 --//我想起查询表获得对应sql_id的脚本，脚本如下，参数5，6对应owner，table_namne. SELECT /*+ MATERIALIZE ordered use_hash(d) use_hash(c) */ DISTINCT c.kglobt03 sql_id FROM sys.x$kglob o ,sys.x$kgldp d ,sys.x$kglcursor c WHERE o.inst_id = USERENV ('Instance') AND d.inst_id = USERENV ('Instance') AND c.inst_id = USERENV ('Instance') AND o.kglnaown = upper(nvl('&5',user)) AND o.kglnaobj = upper('&6') AND d.kglrfhdl = o.kglhdadr AND c.kglhdadr = d.kglhdadr; --//按照该脚本修改一下就可以实现该功能。 $ cat sqlt.sql column owner format a20 column table_name format a30 column ot format a50 with sqla as ( SELECT /*+ MATERIALIZE leading(c d o ) use_nl(d) use_nl(o) */ DISTINCT o.kglnaown owner, o.kglnaobj table_name FROM sys.x$kglob o ,sys.x$kgldp d ,sys.x$kglcursor c WHERE o.inst_id = USERENV ('Instance') AND d.inst_id = USERENV ('Instance') AND c.inst_id = USERENV ('Instance') AND d.inst_id=o.inst_id AND c.inst_id=d.inst_id and c.kglobt03 = '&1' AND d.kglrfhdl = o.kglhdadr AND c.kglhdadr = d.kglhdadr) select owner,table_name,owner||'.'||table_name ot from dba_tables where (owner,table_name) in (select * from sqla); --//验证看看： 1.环境: SCOTT@book> @ver1 PORT_STRING VERSION BANNER ------------------- ---------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试1: SCOTT@book> select emp.* from emp,dept where emp.deptno=dept.deptno and empno=7369; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 --//多执行几次.执行计划如下: Plan hash value: 2949544139 --------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | --------------------------------------------------------------------------------------- --//由于join elimination原因,仅仅看到使用emp的pk_emp索引. SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 3279263698 6a0as8b1rb5yk 0 104402 2949544139 c37597d2 2023-11-14 09:16:09 16777218 SYS@book> @ sqlt 6a0as8b1rb5yk OWNER TABLE_NAME OT ----- ---------- ----------- SCOTT DEPT SCOTT.DEPT SCOTT EMP SCOTT.EMP 3.测试2: $ cat aa.txt SELECT SYS.all_cons_columns.column_name, SYS.all_constraints.constraint_name FROM SYS.all_constraints, SYS.all_cons_columns WHERE SYS.all_constraints.constraint_type = 'P' AND SYS.all_constraints.table_name = 'EMP' AND SYS.all_constraints.owner = 'SCOTT' AND SYS.all_constraints.constraint_name = SYS.all_cons_columns.constraint_name AND SYS.all_constraints.table_name = SYS.all_cons_columns.table_name AND SYS.all_constraints.owner = SYS.all_cons_columns.owner ORDER BY SYS.all_constraints.constraint_name, SYS.all_cons_columns.POSITION; SCOTT@book> @ aa.txt COLUMN_NAME CONSTRAINT_NAME ----------- --------------- EMPNO PK_EMP SCOTT@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 3901825224 bt65mz7n92868 0 73928 1868126782 e89120c8 2023-11-14 09:21:03 16777222 SYS@book> @ sqlt bt65mz7n92868 OWNER TABLE_NAME OT ----- ---------- --------------- SYS CON$ SYS.CON$ SYS COL$ SYS.COL$ SYS CCOL$ SYS.CCOL$ SYS USER$ SYS.USER$ SYS OBJ$ SYS.OBJ$ SYS CDEF$ SYS.CDEF$ SYS OBJAUTH$ SYS.OBJAUTH$ SYS ATTRCOL$ SYS.ATTRCOL$ 8 rows selected. 4.测试3: SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name in ('X$KGLOB','X$KGLDP','X$KGLCURSOR'); TABLE_NAME INDEX_NUMBER COLUMN_NAME COLUMN_POSITION ---------- ------------ -------------------- --------------- X$KGLOB 2 KGLOBT03 0 X$KGLOB 1 KGLNAHSH 0 X$KGLDP 1 KGLNAHSH 0 SYS@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 42c7rtyakuuc0, child number 0 ------------------------------------- select * from V$INDEXED_FIXED_COLUMN where table_name in ('X$KGLOB','X$KGLDP','X$KGLCURSOR') Plan hash value: 2260767298 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | | | |* 1 | HASH JOIN | | 6 | 594 | 1 (100)| 00:00:01 | 1393K| 1393K| 1266K (0)| |* 2 | FIXED TABLE FULL| X$KQFCO | 6 | 414 | 1 (100)| 00:00:01 | | | | |* 3 | FIXED TABLE FULL| X$KQFTA | 10 | 300 | 0 (0)| | | | | --------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5C160134 2 - SEL$5C160134 / C@SEL$3 3 - SEL$5C160134 / T@SEL$3 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."INDX"="C"."KQFCOTAB") 2 - filter(("KQFCOIDX"<>0 AND "C"."INST_ID"=USERENV('INSTANCE'))) 3 - filter(("KQFTANAM"='X$KGLCURSOR' OR "KQFTANAM"='X$KGLDP' OR "KQFTANAM"='X$KGLOB')) SYS@book> @ sqlt 42c7rtyakuuc0 no rows selected --//一些X$表查询不到.这些是一些内存结构,不是真正意义上的表. SYS@book> select count(*) from v$session; COUNT(*) ---------- 27 SYS@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 6d3y2ug8byd5j, child number 0 ------------------------------------- select count(*) from v$session Plan hash value: 3931255564 ------------------------------------------------------------------------------------ | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | SORT AGGREGATE | | 1 | 91 | | | 2 | NESTED LOOPS | | 1 | 91 | 0 (0)| | 3 | NESTED LOOPS | | 1 | 78 | 0 (0)| |* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0)| |* 5 | FIXED TABLE FIXED INDEX| X$KSLWT (ind:1) | 1 | 26 | 0 (0)| |* 6 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| ------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5C160134 4 - SEL$5C160134 / S@SEL$3 5 - SEL$5C160134 / W@SEL$3 6 - SEL$5C160134 / E@SEL$3 Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)) 5 - filter("S"."INDX"="W"."KSLWTSID") 6 - filter("W"."KSLWTEVT"="E"."INDX") SYS@book> @ sqlt 6d3y2ug8byd5j no rows selected SYS@book> select count(*) from v$session,scott.dept; COUNT(*) ---------- 100 SYS@book> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2763321059 5n2nw9kkb9vr3 0 61155 3295531564 a4b4eee3 2023-11-16 16:05:00 16777216 SYS@book> @ sqlt 5n2nw9kkb9vr3 OWNER TABLE_NAME OT -------------------- ------------------------------ -------------------------------------------------- SCOTT DEPT SCOTT.DEPT

编辑推荐：

返回列表

[20231114]如何知道一条sql语句涉及到那些表.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推