[20240830]V$ACTIVE_SESSION_HISTORY.TOP_LEVEL_SQL_ID.TXT

[20240830]V$ACTIVE_SESSION_HISTORY.TOP_LEVEL_SQL_ID.TXT --//别人问的问题，试图V$ACTIVE_SESSION_HISTORY的TOP_LEVEL_SQL_ID字段含义。 --//https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-ACTIVE_SESSION_HISTORY.html Column                      Datatype       Description --------------------------------------------------------------------------------------------------------------- TOP_LEVEL_SQL_ID            VARCHAR2(13)   SQL identifier of the top level SQL statement TOP_LEVEL_SQL_OPCODE        NUMBER         Indicates what phase of operation the top level SQL statement was in --//可以猜测如果比如PL/SQL调用某些sql语句,TOP_LEVEL_SQL_ID就是PL/SQL的程序块,或者是一些递归执行的上级sql语句,简单验证看 --//看. 1.环境: SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立测试脚本: $ cat mn.txt DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..&&1     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END; / 3.测试: SCOTT@book01p> @ mn.txt 1e6 --//打开另外会话: SYS@book> @ ashtop sql_id,TOP_LEVEL_SQL_ID,TOP_LEVEL_CALL_NAME,TOP_LEVEL_CALL#,TOP_LEVEL_SQL_OPCODE    1=1 &1min     Total                                                                                                                                                Distinct Distinct    Distinct   Seconds     AAS %This   SQL_ID        TOP_LEVEL_SQL TOP_LEVEL_CALL_NAME TOP_LEVEL_CALL# TOP_LEVEL_SQL_OPCODE FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1 --------- ------- ------- ------------- ------------- ------------------- --------------- -------------------- ------------------- ------------------- ---------- -------- -----------        26      .4   65% |                                                               0                    0 2024-08-30 16:02:56 2024-08-30 16:03:52          1       24          24        12      .2   30% | b7zpx6jm6mdpy 3p6wgbccxrvrw V8 Bundled Exec                  94                   47 2024-08-30 16:02:55 2024-08-30 16:03:07          9       12          12         1      .0    3% | 0k8w2jn66tktu 0k8w2jn66tktu V8 Bundled Exec                  94                    3 2024-08-30 16:03:17 2024-08-30 16:03:17          1        1           1         1      .0    3% |               3p6wgbccxrvrw V8 Bundled Exec                  94                   47 2024-08-30 16:03:01 2024-08-30 16:03:01          1        1           1 SYS@book> @ sql_id 3p6wgbccxrvrw --SQL_ID = 3p6wgbccxrvrw DECLARE     l_count PLS_INTEGER; BEGIN     FOR i IN 1..1e6     LOOP        EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;     END LOOP; END;; SYS@book> @ sql_id b7zpx6jm6mdpy --SQL_ID = b7zpx6jm6mdpy SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno; SYS@book> select * from V$SQLCOMMAND  where COMMAND_TYPE=47; COMMAND_TYPE COMMAND_NAME                                                         CON_ID ------------ ---------------------------------------------------------------- ----------           47 PL/SQL EXECUTE                                                            0 SYS@book> select sql_text c70, PROGRAM_LINE#  from v$sqlarea where sql_id='b7zpx6jm6mdpy'; C70                                                                    PROGRAM_LINE# ---------------------------------------------------------------------- ------------- SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno                     6