Level 2: Aggregate

Given a time period, output an aggregate of top “consumer”

  1. Top SQL query output - top sql for given time period
  2. Top Session query output - top session for given time period
  3. Top Package and Procedure query output - top package and procedure for given time period (10.2.0.3 and above)

2 Responses to “Level 2: Aggregate”  

Feed for this Entry Trackback Address
  1. 1 Martin Jul 16th, 2008 at 12:18 pm

    Hi

    On an Oracle 9 machine this script doesn’t work because all_procedures does not contain a column SUBPROGRAM_ID:

    SQL> @ashpl2.sql
    Enter value for minutes: 3
    old 20: and ash.sample_time > sysdate - &minutes/(60*24)
    new 20: and ash.sample_time > sysdate - 3/(60*24)
    and ash.PLSQL_SUBPROGRAM_ID = procs2.SUBPROGRAM_ID ( )
    *
    ERROR at line 19:
    ORA-00904: “PROCS2″.”SUBPROGRAM_ID”: invalid identifier

    SQL> desc all_procedures
    Name Null? Type
    —————————————————————– ——– ——————————————–
    OWNER NOT NULL VARCHAR2(30)
    OBJECT_NAME NOT NULL VARCHAR2(30)
    PROCEDURE_NAME VARCHAR2(30)
    AGGREGATE VARCHAR2(3)
    PIPELINED VARCHAR2(3)
    IMPLTYPEOWNER VARCHAR2(30)
    IMPLTYPENAME VARCHAR2(30)
    PARALLEL VARCHAR2(3)
    INTERFACE VARCHAR2(3)
    DETERMINISTIC VARCHAR2(3)
    AUTHID VARCHAR2(12)

    SQL>

  2. 2 khailey Jul 16th, 2008 at 1:52 pm

    Hi Martin,

    yes, the script for “Top Package and Procedure” only works from 10.2.0.3 ASH data onwords. The information about package and procedure is not available in v$session and all_procedures until that version. If you are on Oracle before 10.2.0.3 and want similar functionality you’d have to instrament your code with module and action.

    Kyle

Leave a Reply

You must log in to post a comment.