Oracle performance analysis - Tkprof workshop

Recommend

Contact

Menu
∙ Home ∙ Index (All Topics) ∙ About Me ∙ About This Blog ∙ Favourite Links ∙ RSS Feed

Categories
∙ ASP & PHP (1) ∙ HTML, XML and CSS (2) ∙ C / C++ (5) ∙ Java, JSP and Servlet (0) ∙ SQL-Oracle-PL/SQL (28) ∙ Operating Systems (1) ∙ OFF-Topic (8)

Popular Topics (Top 10)
∙ Decode Function in Oracle SQL (10431) ∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (9614) ∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (8683) ∙ Oracle performance analysis - Tracing and performance evaluation (7822) ∙ Implicit vs. Explicit cursors - Performance analysis (5222) ∙ Turkcell Staj Günlüğü - 4: Transaction Management (4268) ∙ Differences between C and C++ (3983) ∙ Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files (3779) ∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (3607) ∙ Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments (3506)

Most Recent (Last 10)
∙ Matematik Asla Yalan Söylemez! ∙ Finding and Removing Loop on a Singly-Linked List ∙ Obfuscated C ∙ Is C a Vitamin? Yes, of course... ∙ Differences between C and C++ ∙ Whence C? Why C? Whither C? ∙ Türkçe Karakterli Domain'lerin İç Yüzü ∙ Windows Source Codes ∙ Decode Function in Oracle SQL ∙ Hello World!

Recent Comments (Last 10)
∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez! ∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı? ∙ "bilgi paylaşıldıkça deger kazanır." By zülfü yıldırım on Turkcell Staj Günlüğü - 12: Partitioning ∙ "Teşekkürler" By serdar on Turkcell Staj Günlüğü - 12: Partitioning ∙ By Gokhan on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "The Power of Attitude" By Tonguç Yılmaz on Matematik Asla Yalan Söylemez! ∙ ":)" By Ozay Akdora on Obfuscated C ∙ By Ömer YURDUSEVEN on Windows Source Codes ∙ By Özgür Macit on Türkçe Karakterli Domain'lerin İç Yüzü ∙ "Thanks" By Ganesh on Difference between db block gets and consistent gets

Archive (Last 12 Months)
∙ Feb, 2008 (4) ∙ Jan, 2008 (2) ∙ Dec, 2007 (1) ∙ Sep, 2007 (4) ∙ Aug, 2007 (9) ∙ Jul, 2007 (22) ∙ Jun, 2007 (3) ∙ Index (All Records)

Other Related Blogs
∙ Tom Kyte’s Blog ∙ Steven Feuerstein’s Blog ∙ Jonathan Lewis’s Blog ∙ H.Tonguç Yılmaz Oracle Blog ∙ Mennan Tekbir's Blog ∙ Hakkı Oktay’s Blog ∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors

: 45
: 120095
: 36

Oracle performance analysis - Tkprof workshop

Category: SQL-Oracle-PL/SQL
Date: 17.07.2007 15:54:23

This entry has examples about how to gather SQL statement statistics with 10046 SQL Trace and TKPROF. Please first read the entry about the SQL Performance analysis: Oracle performance analysis - Tracing and performance evaluation

Note: I have written the code on a fresh install Oracle 10gR2 XE, so that you can follow step by step and see the errors and solutions. You may get different errors for other versions.

Version: Oracle XE Release 10.2.0.1.0
Server: Suse Linux 9.2 on VMWare Workstation 5.5.3
Client: SQL*Plus: Release 10.2.0.1.0 Console

Some parameters about code:

SYSDBA user & password: sys/1234
Test user & password: test/test
Server machine name: linux

SQL> conn sys/1234@XE as sysdba
Connected.
SQL> drop user test cascade;

User dropped.

SQL> create user test identified by test;

User created.

SQL> grant connect, resource, plustrace to test;

Grant succeeded.

SQL> grant alter session to test;

Grant succeeded.

SQL> grant all on plan_table to test;

Grant succeeded.

SQL> grant select on v_$session to test;

Grant succeeded.

SQL> grant select on v_$process to test;

Grant succeeded.

SQL> grant select on v_$parameter to test;

Grant succeeded.

SQL> conn test/test@XE
Connected.
SQL> alter session set timed_statistics = true;

Session altered.

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> alter session set tracefile_identifier = 'test_1';

Session altered.

SQL> CREATE TABLE sql_trace_test (
  2    id  NUMBER,
  3    description  VARCHAR2(50)
  4  );

Table created.

SQL> CREATE OR REPLACE PROCEDURE populate_sql_trace_test (p_loops  IN  NUMBER) AS
  2    l_number  NUMBER;
  3  BEGIN
  4    FOR i IN 1 .. p_loops LOOP
  5      INSERT INTO sql_trace_test (id, description)
  6      VALUES (i, 'Description for ' || i);
  7    END LOOP;
  8  
  9    SELECT COUNT(*)
 10    INTO   l_number
 11    FROM   sql_trace_test;
 12  
 13    COMMIT;
 14  
 15    DBMS_OUTPUT.put_line(l_number || ' rows inserted.');
 16  END;
 17  /

Procedure created.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> EXEC populate_sql_trace_test(p_loops => 5);

PL/SQL procedure successfully completed.

SQL> select * from dual;

D                                                                               
-                                                                               
X                                                                               

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> 
SQL> SELECT s.sid,
  2         s.serial#,
  3         pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
  4         '_ora_' || p.spid || '.trc' AS trace_file
  5  FROM   v$session s,
  6         v$process p,
  7         v$parameter pa
  8  WHERE  pa.name = 'user_dump_dest'
  9  AND    s.paddr = p.addr
 10  AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

       SID    SERIAL#                                                           
---------- ----------                                                           
TRACE_FILE                                                                      
--------------------------------------------------------------------------------
        25        214                                                           
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP/xe_ora_3384.trc                           
                                                                                

SQL> 
SQL> quit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

C:\Documents and Settings\stbhatipoglu>cd C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP/

C:\oraclexe\app\oracle\admin\XE\udump>dir
 Volume in drive C is System
 Volume Serial Number is 0482-2B10

 Directory of C:\oraclexe\app\oracle\admin\XE\udump

17.07.2007  11:51              .
17.07.2007  11:51              ..
11.07.2007  13:22               725 xe_ora_1116.trc
11.07.2007  13:20               638 xe_ora_1624.trc
11.07.2007  13:21             2.218 xe_ora_2108.trc
13.07.2007  09:36               855 xe_ora_236.trc
14.07.2007  13:13               667 xe_ora_2540.trc
13.07.2007  09:29               857 xe_ora_2588.trc
13.07.2007  09:49               638 xe_ora_2632.trc
17.07.2007  10:26             1.784 xe_ora_2680.trc
11.07.2007  13:22               638 xe_ora_2736.trc
13.07.2007  09:30               638 xe_ora_2756.trc
13.07.2007  09:24               754 xe_ora_2828.trc
14.07.2007  13:13               725 xe_ora_2860.trc
17.07.2007  11:52           202.448 xe_ora_3384_test_1.trc
13.07.2007  09:49               857 xe_ora_3460.trc
11.07.2007  13:22             1.852 xe_ora_3512.trc
13.07.2007  09:29               638 xe_ora_3636.trc
17.07.2007  11:50               735 xe_ora_3760.trc
11.07.2007  15:47                47 xe_ora_3796_test_1.trc
16.07.2007  16:39             2.103 xe_ora_3964.trc
13.07.2007  09:30               725 xe_ora_4580.trc
11.07.2007  13:21               990 xe_ora_4632.trc
11.07.2007  15:43             1.668 xe_ora_4888.trc
13.07.2007  09:21               667 xe_ora_5352.trc
13.07.2007  09:36               638 xe_ora_5696.trc
11.07.2007  15:50           115.593 xe_ora_5700.trc
11.07.2007  13:21               638 xe_ora_5808.trc
13.07.2007  09:28             4.682 xe_ora_5848.trc
13.07.2007  09:49               725 xe_ora_6016.trc
16.07.2007  11:26             1.772 xe_ora_6248.trc
16.07.2007  12:01               741 xe_ora_6680.trc
13.07.2007  09:18               858 xe_ora_840.trc
16.07.2007  16:39               666 xe_ora_908.trc
              32 File(s)        350.180 bytes
               2 Dir(s)   7.933.313.024 bytes free

C:\oraclexe\app\oracle\admin\XE\udump>tkprof xe_ora_3384_test_1.trc d:\trcout.txt explai
n=test/test waits=yes sys=yes

TKPROF: Release 10.2.0.1.0 - Production on Sal Tem 17 11:54:24 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.



C:\oraclexe\app\oracle\admin\XE\udump>

Download Code

You can download my tkprof output file from here. Summary

First, we have to create "plustrace" role and "plan_table" table, with their scripts, so I have run "@$ORACLE_HOME/sqlplus/admin/plustrce.sql;" and "@$ORACLE_HOME/rdbms/admin/utlxplan.sql" scripts on server. If you are using Oracle XE on local windows machine, instead, you must use: "@C:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\plustrce.sql" and "@C:\oraclexe\app\oracle\product\10.2.0\server\rdbms\admin\utlxplan.sql".

Then, we created our test user with required priviliges that are, plustrace role and grant to plan_table, v_$session, v_$process and v_$parameter. Then, we login from our user and start coding.

We created a table called "sql_trace_test" and a procedure called "populate_sql_trace_test" which simply inserts some values to our table and runs a SELECT query. We trace the execution of this procedure.

We set the dump file specifications, then enable 10046 Event, that outputs the trace with wait event statistics. Then we located the dump file and name, and we used tkprof with some options to get tkprof output.

You can find the details of tkprof output and how to read and interpret the output. Also follow the topics on the blog, also I will post more topics about this subject.