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

lfree發表於2023-11-16

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章