[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
--//别人问的问题，开始想看执行计划不就可以吗？当然一些计划可能仅仅涉及到索引。还有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
