connect user1/secret@//bakura.swgalaxy:1521/WOMBAT

create table tpl1 as select * from dba_extents;
create table tpl2 as (select * from tpl1 union all select * from tpl1);
create table tpl3 as (select * from tpl2 union all select * from tpl2);


select /* MYQ1 */
	count(*)
from 
	tpl1
	join tpl2 on tpl1.bytes=tpl2.bytes
	join tpl3 on tpl1.segment_name=tpl3.segment_name
/


--------------------------------------------------------
--  DDL for Package PACKAGE1
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE "USER1"."PACKAGE1" AS 

PROCEDURE PROC1;
PROCEDURE PROC2;
PROCEDURE PROC3;

END PACKAGE1;

/


--------------------------------------------------------
--  DDL for Package Body PACKAGE1
--------------------------------------------------------

  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "USER1"."PACKAGE1" AS

    PROCEDURE proc1 AS
        rr NUMBER;
    BEGIN
        SELECT /* MYQ2 */
            COUNT(*)
        INTO rr
        FROM
                 tpl1
            JOIN tpl2 ON tpl1.bytes = tpl2.bytes
            JOIN tpl3 ON tpl1.segment_name = tpl3.segment_name;

    END;

    PROCEDURE proc2 AS
        z NUMBER;
    BEGIN
        SELECT /* MYQ3 */
            COUNT(*)
        INTO z
        FROM
                 tpl1
            JOIN tpl2 ON tpl1.bytes = tpl2.bytes
            JOIN tpl3 ON tpl1.segment_name = tpl3.segment_name;

    END;


    PROCEDURE proc3 AS
        v NUMBER;
    BEGIN
        SELECT /* MYQ4 */
            COUNT(*)
        INTO v
        FROM
                 tpl1
            JOIN tpl2 ON tpl1.bytes = tpl2.bytes
            JOIN tpl3 ON tpl1.segment_name = tpl3.segment_name;

    END;


END package1;

/


SQL> @ash/ashtop sql_id,TOP_LEVEL_SQL_ID,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID "username='USER1'"  sysdate-1/24 sysdate

    Total                                                                                                                                       Distinct Distinct
  Seconds     AAS %This   SQL_ID        TOP_LEVEL_SQL PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------- ------------- --------------------- ------------------------- ------------------- ------------------- ---------- --------
      105      .0   41% | a0dhc0nj62mk1 8ybf2rvtac57c                 33008                         3 2023-07-19 20:45:23 2023-07-19 20:47:07          1      105
      104      .0   41% | a0dhc0nj62mk1 25ju18ztqn751                 33008                         1 2023-07-19 20:34:23 2023-07-19 20:36:06          1      104
       42      .0   16% | a0dhc0nj62mk1 cum98j5xfkk62                 33008                         2 2023-07-19 20:44:37 2023-07-19 20:45:18          1       42