Dbms Profiler 2sj5a

International Oracle s Group Live 2004 Quick Tip #10:

Tuning PL/SQL procedures using DBMS_PROFILER Tim Gorman Principal - SageLogix, Inc. Email: [email protected] www.SageLogix.Com

Agenda • Overview of tuning tools in Oracle – Tuning SQL – SQL Trace –TKPROF –Oracle Trace Analyzer –Hotsos Profiler (www.hotsos.com) –UB Tools Analyzer (www.ubtools.com) – Tuning PL/SQL – DBMS_PROFILER

Overview of performance tuning • In order to improve anything, you have to measure it

– Is it processing? – If so, what exactly is it doing? – Is it waiting on something? – If so, what exactly is it waiting for?

• Performance tuning is not an art, but a science

– The right tool for the job is the key

Understanding where time is spent

Web server

App server

DB Server

Wait Event

SQL*Net message from client (wait)

Response Time

U (service) db file sequential read (wait) U (service) SQL*Net message from client (wait)

Measuring time • Tracing – Tracing is the recording of actions performed by a program, as they are performed

• Oracle SQL tracing – Event 10046 – alter session set events ‘10046 trace name context forever, level 8’ – exec dbms_.start_trace – New book “Optimizing Oracle Performance” by Cary Millsap and Jeff Holt (O’Reilly & Associates, Sep 2003) – ISBN #059600527X

Summarizing trace data • Recording every action performed by a program generates huge volumes of data to analyze – Forest and trees – Look at the trees for diagnosing failures – Look at the forest to tune performance

• Application profiling – “C” programs: UNIX “prof” – Java programs: EJP on http://www.sourceforge.net – Commercial products from http://www.semdesigns.com – profilers for Java, C#, C++, C, COBOL, other languages – TKPROF for SQL trace data

• Profiling is the summarization of trace data

Profiling • Using the UNIX “prof” utility as an example: – Compile a “C” program using the “-p” option to the “cc” compiler command – When compiled/linked program is executed, trace information is output to a file specified by $PROFDIR environment variable (default is “./mon.out”) – UNIX “prof” utility then reads trace information and produces a summarized “profile report” which summarizes: – Number of calls to functions – Amount of time spent in each function

Profiling • SQL Trace is another example – SQL trace is enabled on a session – All SQL statements executed in that session dump trace information to a “.trc” file in _DUMP_DEST – TKPROF program simply reads information in the “.trc” file and summarizes it, displaying: – Execution counts – U and elapsed times – Number of physical, logical I/O – Number of rows fetched

What if the problem is not SQL? • SQL statements perform work in the database – Summarizing SQL trace info is the best tool for tuning SQL

• What if the performance problem was in: – The network? Operating system? DB instance? – Database wait events can help – An application program, such as: – Java, C#, C++, C, COBOL, Fortran, Lisp? – PL/SQL?

0

DBMS_PROFILER • Introduced with Oracle8i • Not installed with typical installation – Some files in “$ORACLE_HOME/rdbms/” – Documented in MetaLink note #243755.1 – “prof.zip” from MetaLink – “Oracle PL/SQL Supplied Packages” reference on “DBMS_PROFILER”

• Generates trace data in PL/SQL programs – Trace data saved to tables in database – SQL*Plus script produces summarized “profile report” as HTML

1

DBMS_PROFILER • Files found in “$ORACLE_HOME/rdbms/” • SQL*Plus script “profload.sql” creates the package DBMS_PROFILER

– Run once for global setup on database – Must be performed by SYS – Also validates package after creation

• SQL*Plus script “proftab.sql” creates tables to store trace data

– Create private sets of tables for each – Each has the three tables and single sequence – Create a “global set” of tables for all s – DBA is responsible for granting appropriate permissions and creating synonyms for the three tables and the single sequence

2

DBMS_PROFILER • Important extra files provided in “prof.zip” – SQL*Plus script “profiler.sql” – Queries data generated by runs of the DBMS_PROFILER package – Generates HTML profiler report as output – SQL*Plus script “profgsrc.sql” – Extracts the source of a stored package, procedure, or function to an ASCII text file for editing –So that DBMS_PROFILER procedures can be added to that source code…

3

Using DBMS_PROFILER • Initial setup: – DBMS_PROFILER package has been created by the DBA using “profload.sql”… – developer/ has created the tables using “proftab.sql”

• Add profiler procedures to PL/SQL source – Procedure START_PROFILER(‘run-comment’) – Procedure STOP_PROFILER

• Recompile and run the procedure

4

Using DBMS_PROFILER • After running an instrumented PL/SQL program: – Execute the SQL*Plus script “profiler.sql” – It will display each session’s RUN-ID, a timestamp, and the RUN-COMMENT – Choose the appropriate RUN-ID – Produces spooled output to a file named “profiler_ .html”

5

Case Study #1 • A PL/SQL procedure named PROFTEST1 – It queries data from a table – Stores the data in a comma-separated string – Then, repeatedly parses and displays each item in the string

• There are (at least) two ways to call the INSTR() function: – instr(string, ‘,’, 1, n) – instr(string, ‘,’, x, 1) – Which is faster??? Is there a difference?

6

Case Study #1 • Files: – proftest1.sql – DDL to create stored procedure and table –Including use of DBMS_PROFILER – run_proftest1.sql – SQL*Plus script to run the test – proftest1.tkp – Output from tkprof sort=prsela,exeela,fchela explain= / – proftest1_18.html – proftest1_19.html

7

Case Study #1 • No real surprise when you think about it – But using “instr(string, pattern, m, n)” is faster – But THAT MUCH FASTER???

8

Case Study #2 • Script to estimate the number of rows per block – Originally written for Oracle v6 – Adapted to Oracle7 without modification – New ROWID formats in v8+ forced changes to the script

• Started using DBMS_ROWID package in query – Extremely slow

9

Case Study #2 • DBMS_PROFILER does not affect PL/SQL modules called within SQL statements – Unless they are instrumented themselves with START/STOP_PROFILER

• The resolution of replacing DBMS_ROWID package calls with SUBSTR was easily determined using SQL Trace and TKPROF – But DBMS_PROFILER provided some corroboration, at least… :-)

0

Quick Tip #Q10

Q&A

Slides and scripts will be posted on http://www.SageLogix.com and http://www.EvDBT.com Email: [email protected]