[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2995775/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181114]一條sql語句的優化.txtSQL優化
- [20220331]如何調整sql語句.txtSQL
- 一條SQL更新語句是如何執行的？SQL
- 一條SQL更新語句是如何執行的SQL
- 一條update SQL語句是如何執行的SQL
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- 一條 SQL 查詢語句是如何執行的？SQL
- 一條更新的SQL語句是如何執行的？SQL
- 一條SQL語句在MySQL中如何執行的MySql
- [20190125]簡單快速檢視那些sql語句正在執行.txtSQL
- [20231116]如何知道X表存在那些索引.txt索引
- 一條 SQL 語句在 MySQL 中是如何執行的？MySql
- 一條sql語句在mysql中是如何執行的MySql
- [20220117]超長sql語句.txtSQL
- [20201105]再分析sql語句.txtSQL
- [20201210]sql語句優化.txtSQL優化
- 一條sql語句的執行過程SQL
- [20210113]ashtop查詢特定表的SQL語句2.txtSQL
- MySQL日誌（一條sql更新語句是如何執行的）MySql
- PL/SQL 條件控制語句SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- [20240607]PL/SQL中sql語句的註解.txtSQL
- [20240320]空格與sqlpus的sql語句.txtSQL
- [20200320]SQL語句優化的困惑.txtSQL優化
- 一文讀懂一條 SQL 查詢語句是如何執行的SQL
- [20220111]該語句的sql_id如何計算的.txtSQL
- [20240409]為什麼一條sql語句在例項2執行要慢的分析.txtSQL
- 02 | 日誌系統：一條SQL更新語句是如何執行的？SQL
- [20210923]sql語句佔用Sharable Memory分析.txtSQL
- [20220329]是否開發寫錯sql語句.txtSQL
- [20211221]分析sql語句遇到的問題.txtSQL
- [20181119]sql語句執行緩慢分析.txtSQL
- DBeaver如何一次性執行多條sql語句，原來和單條不一樣！SQL
- 01 | 基礎架構：一條SQL查詢語句是如何執行的？架構SQL
- 執行一條 SQL 語句，期間發生了什麼？SQL
- 執行一條sql語句都經歷了什麼？SQL
- [20211009]使用bash計算sql語句的sql_id.txtSQL
- Java面試需要知道的SQL語句語法Java面試SQL