[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