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

[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