среда, 3 июня 2015 г.

How to profile PL/SQL procedure


BEGIN
  sys.DBMS_PROFILER.start_profiler('MAZDENYSH');
  PR_FORM_0503169_2015_BASE(343162, int2date(1, 4, 2015), null, 0, null, 1, 1, null, null);
  sys.DBMS_PROFILER.stop_profiler;
  commit;
END;
/

select RUNID, RUN_DATE, RUN_COMMENT from PLSQL_PROFILER_RUNS;

     RUNID RUN_DATE RUN_COMMENT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
---------- -------- ----------------------
        25 01.06.15 MAZDENYSH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        26 01.06.15 MAZDENYSH                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        27 02.06.15 MAZDENYSH


select * from plsql_profiler_units where runid = 27;

     RUNID UNIT_NUMBER UNIT_TYPE                        UNIT_OWNER                       UNIT_NAME                        UNIT_TIMESTAMP TOTAL_TIME     SPARE1     SPARE2
---------- ----------- -------------------------------- -------------------------------- -------------------------------- -------------- ---------- ---------- ----------
        27           1 ANONYMOUS BLOCK                                                              29.11.01                0                       
        27           2 FUNCTION                         PARUS                            INT2DATE                         19.02.13                0                       
        27           3 FUNCTION                         PARUS                            S2D                              19.02.13                0                       
        27           4 PACKAGE BODY                     PARUS                            PKG_STD                          19.02.13                0                       
        27           5 PROCEDURE                        PARUS                            PR_FORM_0503169_2015_BASE        02.06.15                0                       
        27           6 PACKAGE BODY                     PARUS                            PKG_OPTIONS                      24.04.15                0                       
        27           7 PACKAGE BODY                     PARUS                            PKG_ALERT                        24.04.15                0                       
        27           8 FUNCTION                         PARUS                            UTILIZER                         20.03.14                0                       
        27           9 FUNCTION                         PARUS                            GET_OPTIONS_NUM                  19.02.13                0                       
        27          10 PACKAGE BODY                     PARUS                            PKG_SESSION                      24.04.15                0                       
        27          11 PROCEDURE                        PARUS                            FIND_VERSION_BY_COMPANY          24.04.15                0                       
        27          12 PROCEDURE                        PARUS                            FIND_VERSION_BY_COMPANY_EX       24.04.15                0                       
        27          13 PROCEDURE                        PARUS                            FIND_JURPERSONS_MAIN             19.02.13                0                       
        27          14 FUNCTION                         PARUS                            CR                               19.02.13                0                       
        27          15 PACKAGE BODY                     PARUS                            PKG_CONDITIONS                   24.04.15                0                       
        27          16 FUNCTION                         PARUS                            STRTOK                           19.02.13                0                       
        27          17 FUNCTION                         PARUS                            F_QUOTED_STR                     19.02.13                0                       
        27          18 FUNCTION                         PARUS                            STRCNT                           19.02.13                0                       
        27          19 FUNCTION                         PARUS                            F_ESCAPE_STR                     19.02.13                0                       
        27          20 FUNCTION                         PARUS                            D2S                              19.02.13                0                       
        27          21 FUNCTION                         PARUS                            GEN_IDENT                        19.02.13                0                       

 21 rows selected 


select pu.unit_name, pd.line#, pd.total_occur passes,
       round(pd.total_time / 1000000000,5) total_time, us.text text, max(round(pd.total_time / 1000000000,5)) over() mxx
from plsql_profiler_data pd, plsql_profiler_units pu, user_source us
where pd.runid = &rpt_runid
and pd.unit_number = &rpt_unitid
and pd.runid = pu.runid
and pd.unit_number = pu.unit_number
and us.name = pu.unit_name
and us.line = pd.line#
and us.type in ('PACKAGE BODY','PROCEDURE','FUNCTION');

rpt_runid = 27
rpt_unitid = 5

Комментариев нет:

Отправить комментарий