讲稿教学讲稿

1、Profiling and Tracing PL/SQL CodeObjectivesAfter completing this lesson, you should be able to do the following:Trace PL/SQL program executionProfile PL/SQL applicationsLesson AgendaTracing PL/SQL program executionProfiling PL/SQL applicationsEnable specific subprograms for tracing (optional)Start t

2、racing sessionTracing PL/SQL ExecutionTracing PL/SQL execution provides you with a better understanding of the program execution path, and is possible by using the dbms_trace package.Trace dataTrace dataRun application to be tracedStop tracing sessionTracing PL/SQL ExecutionThe dbms_trace package co

3、ntains:set_plsql_trace (trace_level INTEGER)clear_plsql_traceplsql_trace_versionTracing PL/SQL ExecutionUsing set_plsql_trace, select a trace level to identify how to trace calls, exceptions, SQL, and lines of code.Trace-level constants:trace_all_calls trace_enabled_calls trace_all_sqltrace_enabled_

4、sql trace_all_exceptions trace_enabled_exceptions trace_enabled_linestrace_all_lines trace_stoptrace_pausetrace_resumeTracing PL/SQL: StepsEnable specific program units for trace data collection.Use dbms_trace.set_plsql_trace to identify a trace level.Start tracing by running your PL/SQL code.Use db

5、ms_trace.clear_plsql_trace to stop tracing data.Read and interpret the trace information.12345ALTER PROCEDURE | FUNCTION | PACKAGE subprogram-name COMPILE DEBUG BODY;Step 1: Enable Specific SubprogramsEnable specific subprograms with one of the two methods:Enable a subprogram by compiling it with th

6、e debug option: pile a specific subprogram with the debug option:ALTER SESSION SET PLSQL_DEBUG=true;CREATE OR REPLACE .Steps 2 and 3: Identify a Trace Level and Start TracingSpecify the trace level by using dbms_trace.set_plsql_trace:Execute the code that is to be traced:EXECUTE my_programEXECUTE DB

7、MS_TRACE.SET_PLSQL_TRACE - (tracelevel1 + tracelevel2 .)EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACEStep 4: Turn Off Tracing Remember to turn tracing off by using the dbms_trace.clear_plsql_trace procedure.Step 5: Examine the Trace InformationExamine the trace information:Call tracing writes out the program

8、 unit type, name, and stack depth.Exception tracing writes out the line number.plsql_trace_runs and plsql_trace_eventsTrace information is written to the following dictionary views:plsql_trace_runs dictionary viewplsql_trace_events dictionary viewRun the tracetab.sql script to create the dictionary

9、views.You need privileges to view the trace information in the dictionary views.PROC_NAME PROC_LINE EVENT_PROC_NAME MENT- - - -P5 1 Procedure CallP4 1 P5 Procedure Call 2 rows selected.plsql_trace_runs and plsql_trace_eventsALTER SESSION SET PLSQL_DEBUG=TRUE;ALTER PROCEDURE P5 COMPILE DEBUG;EXECUTE

10、DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.trace_all_calls)EXECUTE p5EXECUTE DBMS_TRACE.CLEAR_PLSQL_TRACESELECT proc_name, proc_line, event_proc_name, ment FROM sys.plsql_trace_eventsWHERE event_proc_name = P5OR PROC_NAME = P5;Lesson AgendaTracing PL/SQL program executionProfiling PL/SQL applicationsHier

11、archical Profiling ConceptsDefinition:Used to identify hotspots and performance tuning opportunities in PL/SQL applicationsReports the dynamic execution profile of a PL/SQL program organized by function callsReports SQL and PL/SQL execution times separatelyProvides function level summariesBenefits:P

12、rovides more information than a flat profilerCan be used to understand the structure and control flow of complex programsHierarchical Profiling ConceptsThe PL/SQL hierarchical profiler consists of the:Data collection componentAnalyzer componentUsing the PL/SQL ProfilerBy using the PL/SQL profiler, y

13、ou can find:The number of calls to a functionThe function time, not including descendantsThe subtree time, including descendantsParent-children information for each functionWho were the callers of a given function?What functions were called from a particular function?How much time was spent in funct

14、ion X when called from function Y?How many calls to function X came from function Y?How many times did X call Y?Using the PL/SQL ProfilerSample data for profiling:CREATE OR REPLACE PACKAGE BODY credit_card_pkgIS PROCEDURE update_card_info (p_cust_id NUMBER, p_card_type VARCHAR2, p_card_no VARCHAR2)

15、IS v_card_info typ_cr_card_nst; i INTEGER; BEGIN SELECT credit_cards . END update_card_info; PROCEDURE display_card_info (p_cust_id NUMBER) IS v_card_info typ_cr_card_nst; i INTEGER; BEGIN SELECT credit_cards . END display_card_info; END credit_card_pkg; - package bodyUsing the PL/SQL ProfilerBEGIN-

16、 start profiling DBMS_HPROF.START_PROFILING(PROFILE_DATA, pd_cc_pkg.txt);END;1BEGIN DBMS_HPROF.STOP_PROFILING;END;3DECLARE v_card_info typ_cr_card_nst;BEGIN- run application credit_card_pkg.update_card_info (154, Discover, 123456789);END;2Understanding Raw Profiler DataP#! PL/SQL Timer StartedP#C PL

17、SQL._plsql_vmP#X 3P#C PLSQL._anonymous_blockP#X 1634P#C PLSQL.OE.CREDIT_CARD_PKG:11.UPDATE_CARD_INFO#71749359b90ac246 #24P#X 7P#C PLSQL.OE.CREDIT_CARD_PKG:11.CUST_CARD_INFO#c2ad85321cb9b0ae #4P#X 11P#C SQL.OE.CREDIT_CARD_PKG:11._static_sql_exec_line10 #10P#X 1502P#R.P#C PLSQL._plsql_vmP#X 3P#C PLSQL

18、._anonymous_blockP#X 15P#C PLSQL.SYS.DBMS_HPROF:11.STOP_PROFILING#980980e97e42f8ec #53P#RP#RP#! PL/SQL Timer StoppedUsing the Hierarchical Profiler TablesUpload the raw profiler data into the database tables. Run the dbmshptab.sql script that is located in the /home/rdbms/admin folder to set up the

19、profiler tables.Creates these tables:CONNECT OE- run this only once per schema - under the schema where you want the profiler tables locatedD:appAdministratorproduct11.1.0db_1RDBMSADMINdbmshptab.sqlTableDescriptionDBMSHP_RUNSContains top-level information for each run commandDBMSHP_FUNCTION_INFOCont

20、ains information on each function profiledDBMSHP_PARENT_CHILD_INFOContains parent-child profiler informationUsing DBMS_HPROF.ANALYZEDBMS_HPROF.ANALYZE:Analyzes the raw profiler dataGenerates hierarchical profiler information in the profiler database tablesDefinition:DBMS_HPROF.ANALYZE( location IN V

21、ARCHAR2, filename IN VARCHAR2, summary_mode IN BOOLEAN DEFAULT FALSE, trace IN VARCHAR2 DEFAULT NULL, skip IN PLS_INTEGER DEFAULT 0, collect IN PLS_INTEGER DEFAULT NULL, ment IN VARCHAR2 DEFAULT NULL)RETURN NUMBER;Using DBMS_HPROF.ANALYZE to Write to Hierarchical Profiler TablesUse the DBMS_HPROF.AN

22、ALYZE function to upload the raw profiler results into the database tables.This function returns a unique run identifier for the run. You can use this identifier to look up results corresponding to this run from the hierarchical profiler tables.DECLARE v_runid NUMBER;BEGIN v_runid := DBMS_HPROF.ANAL

23、YZE (LOCATION = PROFILE_DATA, FILENAME = pd_cc_pkg.txt); DBMS_OUTPUT.PUT_LINE(Run ID: | v_runid);END;RUN_ID- 1 Sample Analyzer Output from the DBMSHP_RUNS Table Query the DBMSHP_RUNS table to find top-level information for each run:SELECT runid, run_timestamp, total_elapsed_timeFROM dbmshp_runs WHER

24、E runid = 1; RUNID RUN_TIMESTAMP TOTAL_ELAPSED_TIME - - - 2 12-DEC-07 84000 PM 122650Sample Analyzer Output from the DBMSHP_FUNCTION_INFO Table Query the DBMSHP_FUNCTION_INFO table to find information about each function profiled:SELECT owner, module, type, function line#, namespace, calls

25、, function_elapsed_timeFROM dbmshp_function_infoWHERE runid = 1;OWNER MODULE TYPE LINE# NAMESPACE CALLS TIME- - - - - - - _anonymous_block PLSQL 2 1650 _plsql_vm PLSQL 2 10OE CREDIT_CARD PACKAGE BODY CUST_CARD_INFO PLSQL 1 66 _PKGOE CREDIT_CARD PACKAGE BODY UPDATE_CARD_INFO PLSQL 1 38 _PKGSYS DBMS_HPROF PACKAGE BODY STOP_PROFILING PLSQL 1 0 _PKGOE CREDIT_CARD PACKAGE BODY _static_sql_exec_line10 SQL 1 1502 _PKGOE CREDIT_CARD PACKAGE BODY _static_sql_exec_line41SQL 1 8999 _PKGplshprof: A Simple HTML Report Generatorplshprof is a command-