??????????????? ?????????? ?????? DBMS_PROFILER ??? ???????????? ?????? ??????????. ????????, ??? ????? ???????????? ??????? ??? ??????????? ????, ????? ?????? ?????????? ???????? ???????????, ??????? ???, ? ??????? ?? ?? ?????????? ???? ???????.

????????

? ?????????? ? ? ???????????? ?????????? ?????? ????????? ??????? ?????????, ??? ?????? ?????????? ? ??????? ??? ?????????? ??????????? ???????? ????. ??? ?????????? ??? ???? ???? ????? ?????????? ?????????? ???????? ?? ????? ?????????? ?????????? (SQL Developer, TOAD ? ??.). ?? ???? ???? ??? ??????? ?????????? ??? ????????? ?????????????, ?????????? ???????? ?? ???????. ??? ????? ??????? ??????? ???? ???????: ????????????? ????????? ??????? ?? ??????? ?? Oracle, DBMS_PROFILER ? DBMS_TRACE.

???????? ??????? ????? ?????????? ????? ? ???, ??? ????? DBMS_PROFILER ???????? ? ?????????? ???????? ????????? ? ???????? ??, ? ????? DBMS_TRACE ? ? ?????????????? ????? ??????. ????, ???????, ? ???????????? ?????????????? ????????.

????? ?????????????? ? ???????? ?? ??????????? ?????????? ? ??????? ?????? DBMS_PROFILER. ????????, ??? ?? ????? ???????????? ???????????????, ?????? ?????? ????????, ????????, ? ???? ??????? ??? ??????: ????????? ? ???? ??????? ???????? ? ??????? ?????????? ?????????? ??? PL/SQL.

????? DBMS_PROFILER

??? ??????? ?????? (???????????? ????? ? ???????? ????????), ????? ??????? ????????????? ?????? ?? ????????????:

??????? ??????????? ? ?????????? ????? ?? ???????? ??????????? ????????????? ??????????? ? ?????? ????????? ????????. ???????? ???????? ? ????? ???????????? ?????? ???? ??????.

???????????? ?????? ?????????????? ?????????? ???????:

[rdbms] ???? ????????? ? %ORACLE_HOME%\rdbms\admin.

[pls] ???? ????????? ? %ORACLE_HOME%\plsql\demo.

???? ??????? ???????? ? SQL*Plus, ??????????? ?? ??????? (????????? ??? ????????? ?? Oracle ? ????????????? ? ??????? ??????????). ?????????? ????????????? ??????, ???????????, ???????? ? ?? ???????.

?????????? ? ?????? ? ???????

????????? ?????? DBMS_PROFILER ?? ????? SYS ? ??????????? ??????????? ????????:

CONNECT / AS SYSDBA
@?/rdbms/admin/profload

???????? ?????? ??? ???????? ????????? ?????? ? ???????? ????????:

 CONNECT scott/tiger
@?/rdbms/admin/proftab

?????? ????????????

???????? ???? ??????? ????????:

CREATE OR REPLACE PROCEDURE first 
AS
n NUMBER := 1;

BEGIN
FOR i IN 1 .. 1000 LOOP
   n := n + 1;
END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE second 
AS

BEGIN
DBMS_OUTPUT.PUT_LINE ( SIN ( 1 ) );
first;
END;
/

???????? ????????:

• ????????? SECOND ?????????? ? FIRST
• ? ??????? ??????? ?????? ??????
• ? ????????? SECOND ???? ????????? ? ??????????? ??????? SIN ? ? ????????????? ?????? DBMS_OUTPUT.
apply for jobs online

???????? ??????? ?????? (????????? ?? ???????????? ?????????? ???? ????????):

EXECUTE dbms_profiler.start_profiler ( 'Run@ ' || SYSTIMESTAMP )
EXECUTE first
EXECUTE second
EXECUTE dbms_profiler.stop_profiler

??????? ?????? ??????? ??????????? ?????. ??? ????? ??????? ????????:

COLUMN?run_comment?FORMAT?A60  WORD

COLUMN?runid?      FORMAT?9999
SELECT
 ?runid
,?run_comment
, run_date?
FROM
  plsql_profiler_runs
ORDER BY
  runid
, run_date
;

???? ????? ?????? ?????????? ? ?????, ???????? seeprofiles.sql ? ??????? ????????.

????????? ??? ????? ???????????? ? ??????? ?????????? ???????, ???????? ? ?????:

SET VERIFY OFF
COLUMN owner   FORMAT A10
COLUMN name    FORMAT A10
COLUMN text    FORMAT A45   WORD
COLUMN line    FORMAT 999
COLUMN occured FORMAT 99999
SELECT
  u.unit_owner                     AS owner
, u.unit_name                      AS name
, s.line
, total_occur occured
, TRUNC ( d.total_time / 1000000 ) AS "TIME(ms)"
, s.text
FROM
  all_source           s
, plsql_profiler_data  d
, plsql_profiler_units u
WHERE
    u.runid       = &1
AND u.runid       = d.runid
AND u.unit_number = d.unit_number
AND s.name        = u.unit_name 
AND s.type        = u.unit_type
AND s.line        = d.line# 
ORDER BY
  unit_owner
, name
, line
;
SET VERIFY ON

? ???? ??????? SQL*Plus ???????? ????? ? ???????.

??????????? ????? ?????? ?????????? ? ?????, ???????? seeprofile.sql ? ??????? ????????.

?????? ???????????? ???????? ? ??????? ????? ????????? ???:

SQL> @seeprofiles

RUNID RUN_COMMENT                                          RUN_DATE
----- ---------------------------------------------------- ---------
   10 Run@ 17-JAN-07 03.56.55.613000000 PM +03:00          17-JAN-07

1 rows selected.

SQL> @seeprofile  10

OWNER  NAME   LINE OCCURED TIME(ms) TEXT
------ ------ ---- ------- -------- -----------------------------------
SCOTT  FIRST     1       0        3 PROCEDURE first
SCOTT  FIRST     3       2        0 n NUMBER := 1;
SCOTT  FIRST     6    2002      105 FOR i IN 1 .. 1000 LOOP
SCOTT  FIRST     7    2000      194 n := n + 1;
SCOTT  FIRST     9       2       17 END;
SCOTT  SECOND    1       0        3 PROCEDURE second
SCOTT  SECOND    5       1     6108 DBMS_OUTPUT.PUT_LINE ( SIN ( 1 ) );
SCOTT  SECOND    6       2        3 first;
SCOTT  SECOND    7       1        0 END;

9 rows selected.

?????? ???????????

???? ?????????? ??????? ?? ???????? ???????, ?????????? ?????-?????????? ??????? ???? ????? ????????? ??????. ? ????? ??????? ????? ????????? ?????? ??????, ??????? ?????? ???????? ? ??????? ???????????, ????????????? ???? ?????????, ???? ?? ?????? ???? ????????? ?????????????.

??????? ?????? ? ??????? ???????? (PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA) ???????? ? ?????? ???????? ??????????, ????????:

??? ???? ????? ????? ???????????? ? ???????? ??? ????????? ????? ????? ??? ????? ????????? ????????.

?????? ?????????????? ???????? ???????????? ????? ??????? ??????????????, ???? ???????? ????????? ? DBMS_PROFILER.START_PROFILER ? ???? ?????????? ????????? AFTER LOGON.

????????????

????? ????, ??? ??????? ? ????????? ?????????????? ???????? ????? ????????? ??????????? ?????????? ?????????? ????????? ?? ??????.

?????? ??????, ????? ????????? ??????, ??????????? ???????????? ???????:

DELETE FROM plsql_profiler_data;
DELETE FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;

??? ?????????????????? ???? ?????? ???????? ? ???? ????????, ???? ?????????.

??????? ????????? ? ????????????? ????????? ???????????? ????????????. ???????? ?? ???????? ????????? ?? ? ???? ?????.

???????, ??? ?????? ? ??????? ???????? ? ?????????? ??????? ????? ??????? ?????? ?????, ???? ?? ??? ??, ??????????? ????????????? ????????? ? ??? ?????? ??? ?????? ????????? ?????????. ?????? ? ?????? ????????????? ??????? ? ????? ???????? PLSQL_PROFILER_* ??? ??????? ????? ?????????? ?????????? ???????????? ??????? ??? (????????? ?????????????? ??????? ? ?????? ??????).

???? ?? ?????? ???????? ???????????? ?????????? ??????????, ?? ??? ??? ??????????????? ?????? ????????.

?c??????: www.ccas.ru/prz

Readers Comments (Комментариев нет)